Re: st: Re: reshaping key-value pair data

["Dimitriy V. Masterov" <dvmaster@gmail.com>]

I wound up using Joseph Coveney's solution, with a couple of tweaks
(no fillin and rename instead of renpfix).

Here's my code:

#delimit;
levelsof key, local(keys);

foreach key of local keys {;
    gen byte `key'=key=="`key'";
    bysort item `key' (value): replace key = key + string(sum(`key'))
if key=="`key'";
    drop `key';
};

reshape wide value, i(item) j(key) string;
rename value* *;



On Tue, Oct 1, 2013 at 7:07 PM, Joseph Coveney <stajc2@gmail.com> wrote:
> 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/
*
*   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/