Invert unit of analysis of the database

[Bianca Onita]

Hi, as a beginner in STATA I need some help about commands that can change the unit of analysis of my database.

For exemple, the database has food items as the unit of analysis (row), but I want to be the ID of the individual, wich is a variable (column). Each ID has more than one food item attached.

[Clyde Schechter]

This is almost certainly a task for the -reshape- command. -reshape- is one of Stata's most important and useful commands, but it has something of a steep learning curve. You're welcome to read -help reshape- and the chapter of the PDF documentation that is linked therein. But most beginners need help applying it to a specific situation.

So if you want to post back for more specific advice, you will need to show example data. The way to do that is by using the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Bianca Onita]

Oh, Thank you so much for the advice and orientations. I used de command -dataex- with some of the variables to show how my database is structured. In the exemplo below I have for each line/row a food item, and each column I have the variables for them. What I need is to invert the unit of analysis to ID rather then the food item. Because I have to get the food consumption of each individual, such the mean consumption (kilograms and kcal) for each SEQ.

id_final SEQ PTN FIBRA LIP AGTRANS KCAL QTD
110000016211 72 6.881 1.827 1.484 .175 210 70
110000016211 72 0 0 9.9596 .497 89.6 14
110000016211 72 2.595 0 2.46 0 54 150
110000016211 72 .024 0 0 0 59.55 15
110000016211 72 2.16 1.143 1.431 .009 124.2 90
110000016211 72 19.376 .091 3.892 .056 114.1 70
110000016211 72 1.6415 2.394 .602 .0035 27.65 35
110000016211 72 6.993 17.253 21.978 0 572.4 270
110000016211 72 2.16 1.143 1.431 .009 124.2 90
110000016211 72 19.376 .091 3.892 .056 114.1 70
110000016211 72 1.6415 2.394 .602 .0035 27.65 35
110000016211 72 0 0 0 0 0 900
110000016211 73 6.881 1.827 1.484 .175 210 70
110000016211 73 0 0 9.9596 .497 89.6 14
110000016211 73 2.595 0 2.46 0 54 150
110000016211 73 .024 0 0 0 59.55 15
110000016211 73 2.16 1.143 1.431 .009 124.2 90
110000016211 73 9.688 .0455 1.946 .028 57.05 35
110000016211 73 1.6415 2.394 .602 .0035 27.65 35
110000016211 73 .21 .27 .09 0 111 300
110000016211 73 .048 0 0 0 119.1 30
110000016211 73 6.881 1.827 1.484 .175 210 70
110000016211 73 0 0 9.9596 .497 89.6 14
110000016211 73 0 0 0 0 0 900
110000016211 73 1.08 0 .12 0 16 200
110000016211 73 .032 0 0 0 79.4 20
110000016212 72 6.881 1.827 1.484 .175 210 70
110000016212 72 0 0 9.9596 .497 89.6 14
110000016212 72 2.595 0 2.46 0 54 150
110000016212 72 .024 0 0 0 59.55 15
110000016212 72 2.16 1.143 1.431 .009 124.2 90
110000016212 72 1.6415 2.394 .602 .0035 27.65 35
110000016212 72 9.688 .0455 1.946 .028 57.05 35
110000016212 72 5.8275 14.3775 18.315 0 477 225
110000016212 72 2.16 1.143 1.431 .009 124.2 90
110000016212 72 9.688 .0455 1.946 .028 57.05 35
110000016212 72 1.6415 2.394 .602 .0035 27.65 35
110000016212 72 0 0 0 0 0 900
110000016212 73 6.881 1.827 1.484 .175 210 70
110000016212 73 0 0 9.9596 .497 89.6 14
110000016212 73 .81 0 .09 0 12 150
110000016212 73 .024 0 0 0 59.55 15
110000016212 73 2.16 1.143 1.431 .009 124.2 90
110000016212 73 1.6415 2.394 .602 .0035 27.65 35
110000016212 73 9.688 .0455 1.946 .028 57.05 35
110000016212 73 1.08 0 .12 0 16 200
110000016212 73 .032 0 0 0 79.4 20
110000016212 73 4.395 0 4.845 0 96 150
110000016212 73 .024 0 0 0 59.55 15
110000016212 73 2.7175 .795 3.42 .45 109.75 25
110000016212 73 2.16 1.143 1.431 .009 124.2 90
110000016212 73 1.6415 2.394 .602 .0035 27.65 35
110000016212 73 9.688 .0455 1.946 .028 57.05 35
110000016212 73 0 0 0 0 0 1200
110000016213 72 6.881 1.827 1.484 .175 210 70
110000016213 72 0 0 9.9596 .497 89.6 14
110000016213 72 29.064 .1365 5.838 .084 171.15 105
110000016213 72 13.132 19.152 4.816 .028 221.2 280
110000016213 72 2.595 0 2.46 0 54 150
110000016213 72 .024 0 0 0 59.55 15
110000016213 72 3.24 1.7145 2.1465 .0135 186.3 135
110000016213 72 3.24 1.7145 2.1465 .0135 186.3 135
110000016213 72 3.283 4.788 1.204 .007 55.3 70
110000016213 72 29.064 .1365 5.838 .084 171.15 105
110000016213 73 6.881 1.827 1.484 .175 210 70
110000016213 73 0 0 9.9596 .497 89.6 14
110000016213 73 2.595 0 2.46 0 54 150
110000016213 73 .024 0 0 0 59.55 15
110000016213 73 2.07 .51 .33 0 111 300
110000016213 73 .048 0 0 0 119.1 30
110000016213 73 3.24 1.7145 2.1465 .0135 186.3 135
110000016213 73 1.6415 2.394 .602 .0035 27.65 35
110000016213 73 19.376 .091 3.892 .056 114.1 70
110000016213 73 3.24 1.7145 2.1465 .0135 186.3 135
110000016213 73 1.6415 2.394 .602 .0035 27.65 35
110000016213 73 19.376 .091 3.892 .056 114.1 70
110000016213 73 0 0 0 0 0 1200
110000016711 72 .865 0 .82 0 18 50
110000016711 72 .008 0 0 0 19.85 5
110000016711 72 4.284 1.116 15.03 .672 256.2 60
110000016711 72 2.16 1.143 1.431 .009 124.2 90
110000016711 72 1.5946 2.3256 .5848 .0034 26.86 34
110000016711 72 59.184 0 26.388 .27 473.4 180
110000016711 72 .904 1.344 3.912 .016 48.8 80
110000016711 72 0 0 0 0 52.5 150
110000016711 72 4.284 1.116 15.03 .672 256.2 60
110000016711 72 .865 0 .82 0 18 50
110000016711 72 .008 0 0 0 19.85 5
110000016711 72 28.12 6.98 25.7 .72 564 200
110000016711 72 .232 0 12.2 .068 122.4 40
110000016711 72 .348 .06 .062 0 25.6 20
110000016711 72 0 0 0 0 105 300
110000016711 73 8.568 2.232 30.06 1.344 512.4 120
110000016711 73 .865 0 .82 0 18 50
110000016711 73 .008 0 0 0 19.85 5
110000016711 73 0 0 0 0 0 300
110000016711 73 4.32 2.286 2.862 .018 248.4 180
110000016711 73 2.3919 3.4884 .8772 .0051 40.29 51
110000016711 73 14.86 .67 26.6 .06 325 100
110000016711 73 1.035 .255 .165 0 55.5 150

[Ken Chui]

What I need is to invert the unit of analysis to ID rather then the food item. Because I have to get the food consumption of each individual, such the mean consumption (kilograms and kcal) for each SEQ.

This statement says two things, if each row is a person then there will not be different SEQ. And if SEQ-level means are needed, then the level is not at ID-level. You may want to make a decision that works for your analysis.

If it's ID-SEQ level, and the nutrients are to be summed, then it's possible to do that using collapse:

collapse (sum) PTN FIBRA LIP AGTRANS KCAL QTD, by(id_final SEQ)

If it's ID level, then take away the "SEQ" from the by option.