st: Re: reshaping key-value pair data

["Joseph Coveney" <stajc2@gmail.com>]

Dimitriy V. Masterov wrote:

I have some data in an awkward key-value pair format:

item key value
1 color blue
1 color red
1 size XL
2 color orange
2 size S

It is possible to reshape this data into something like this:

item color1 color2 size
1 blue red XL
2 orange S

The order for the values should be alphabetical,so blue before red.

I tried the following:

gen color = value if key=="color"
gen size = value if key=="size"

sort item key value
collapse (firstnm) color1=color (lastnm) color2=color (firstnm) size, by(item)

This mostly works, but it won't work for more than 2 values per key
and orange appears twice for item 2.

--------------------------------------------------------------------------------

In addition to Nick's approach, you could also use -fillin- to good effect.

Joseph Coveney

. input item str5 key str6 value

          item        key      value
  1. 1 color blue
  2. 1 color red
  3. 1 size XL
  4. 2 color orange
  5. 2 size S
  6. end

. 
. generate byte color = key == "color"

. quietly bysort item color: replace key = key + string(sum(color))

. fillin item key

. drop color _fillin

. quietly reshape wide value, i(item) j(key) string

. renpfix value

. rename size0 size

. 
. list, noobs separator(0)

  +-------------------------------+
  | item   color1   color2   size |
  |-------------------------------|
  |    1      red     blue     XL |
  |    2   orange               S |
  +-------------------------------+

. 
. exit

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/