statisticsstatareshapestata-macros

Reshaping when variables are not unique

I have a dataset below and I want to reshape it in Stata.

clear 
input int id str7 item float consumption 
1   "food123"   0.72
1   "food174"   0.51
1   "food167"   0.76
1   "food160"   0.02
1   "fruit240"  0.52
1   "fruit263"  0.06
1   "fruit254"  100.00
1   "drink345"  0.25
1   "drink340"  0.60
1   "vegtable466"   0.87
1   "vegtable440"   0.44
2   "food123"   0.26
2   "food167"   0.52
2   "food120"   0.44
2   "food160"   0.16
2   "fruit240"  0.09
2   "fruit242"  0.65
2   "fruit263"  0.80
2   "fruit230"  0.60
2   "fruit254"  0.32
2   "drink340"  0.90
2   "drink345"  0.05
2   "vegtable466"   0.24
2   "vegtable460"   0.78
end

The result is (the row is id and the columns are items):

Result

When I use reshape, it shows "values of variable item not unique within id". My code is:

reshape wide consumption, i(id) j(item) string

How can I do that?

Solution

  • Your reshape command works fine for your data example, so long as you use an appropriate type for item.

    input int id str11  item float consumption

    Using str7 just truncates the description and causes spurious duplicates.