Reshape from Long to Wide - Multiple Observations per ID

[Paul Loprinzi]

I am interested/in the need of reshaping a unique data set from long to wide format. Example data set below:

ID Time Protein(gm)
1 900 1.7
1 900 1.3
1 1200 1.1
2 800 0.8
2 1500 2.2
3 900 1.1
3 900 1.8
3 1800 2.2

The Time variable is in military time with the Protein variable indicating the number of grams of protein consumed at that time period. As noted above, participant 1 consumed two different dietary items at 9:00 AM, totaling 3.0 grams of protein at 9:00 AM. I’ve read a similar post on this:

http://www.statalist.org/forums/foru...econd-variable

But my ultimate goal is to calculate a “summed” protein variable for each time period for each participant. For example, hoping to reformat the above data set into a wide format; the summed protein value for ID 1 for 900 would be 3.0 and 1.1 for 1200, and blank (.) for 800, 1500, and 1800. For ID 2, the summed protein value would be 0.8 for 800 and 2.2 for 1500, and blank for 900, 1200 and 1800. Then for ID 3, summed protein value would be 2.9 for 900 and 2.2 for 1800, with blank values 800, 1200 and 1500.

Any help that can be provided would be greatly appreciated.

Kind regards,

Paul Loprinzi

[Nick Cox]

A reshape won't make what you want easier.

collapse (sum) protein, by(ID Time)

is sample code. See also help fillin

[Carole J. Wilson]

Paul, I think this will do the trick. The key is to collapse the dataset to sum the proteins when there are multiple data points at the same time (per id).

input id time prot
1 900 1.7
1 900 1.3
1 1200 1.1
2 800 0.8
2 1500 2.2
3 900 1.1
3 900 1.8
3 1800 2.2
end

collapse (sum) prot, by(id time)

reshape wide prot, i(id) j(time)

list

[Paul Loprinzi]

Many thanks to both of you. It worked perfectly.