reshape with multiple values

[masgo]

I have data which is like this (whitespaces are just for readability)

id,item,value1,value2,value3
x, a,  1, 2, 3
x, b,  4, 5, 6
x, c,  7, 8, 9
y, a, 10,11,12
y, b, 13,14,15
y, c, 16,17,18

I would like to have it in this form:

id,a-value1,a-value2,a-value3,b-value1,b-value2,b-value3,c-value1,c-value2,c-value3
x,  1, 2, 3, 4, 5, 6, 7, 8, 9
y, 10,11,12,13,14,15,16,17,18

If there would be only one value, I could simply use reshape. But how can I do this with multiple values?

It would also be okay to have nested values, such as (using - as separator here)

id,value1,value2,value3
x,    1-4-7,    2-5-8,    3-6-9
y, 10-13-16, 11-14-17, 12-15-18

Any ideas?

[aborruso]

Hi @masgo ,
starting from

id,item,value1,value2,value3
x,a,1,2,3
x,b,4,5,6
x,c,7,8,9
y,a,10,11,12
y,b,13,14,15
y,c,16,17,18

and running

mlr --csv reshape -r "value" -o k,v then put '$j=$item."-".$k' then cut -x -f item,k then reshape -s j,v input.csv

you will have

id,a-value1,a-value2,a-value3,b-value1,b-value2,b-value3,c-value1,c-value2,c-value3
x,1,2,3,4,5,6,7,8,9
y,10,11,12,13,14,15,16,17,18

[aborruso]

The second way is

mlr --csv reshape -r "value" -o k,v then cut -x -f item then nest --implode --values --across-records -f v --nested-fs "-" then reshape -s k,v input.csv

You will have

id,value1,value2,value3
x,1-4-7,2-5-8,3-6-9
y,10-13-16,11-14-17,12-15-18

[masgo]

ok, wow, it works. It took me a while to understand why, but I got it.

Since my real data is not named value1,value2,value3, but each column has a different name, the first regex is rather ugly, but it works.

Thank you!
Now I only need to choose which solution works better for the downstream processing.

[masgo]

Oh, I just realized I can use -i instead of -r and just name the fields.

[aborruso]

Oh, I just realized I can use -i instead of -r and just name the fields.

Yes, but for your example input it was much more comfortable to use regex

[masgo]

Indeed it was. I just did not realize that straight away.

btw: in the end I used the non-nested version, since I had to also unsparsify the data. The result is also better readable since each column has it's own explicit name. With the nested version, the relationship between item and value would only be implicit. If I have to change something in a few months or years, having more verbose structures is nice.