Opposite of pivot?

[ondohotola]

I can’t seem to be able to find how to transform

A,B,C,D
a,1,2,
b,1,2,3
c,,,

with values in column A guaranteed to be unique and B, C, D being possibly empty to

A,E
a,1
a,2
b,1,
b,2
b,3
c,

the last row may not be present as the E cell would be empty, but I don’t mind.

I am not averse to RTFM so a pointer will do :-)-O

[datatraveller1]

I doubt this can be done with qsv (unless @jqnatividad has a solution).

If this can't be done with qsv, there is a AWK solution (for GAWK 5.3 or later with native CSV RFC 4180 support):

command:
gawk --csv -v OFS=, -f unpivot.awk data.csv

unpivot.awk:

BEGIN {
  getline # skip first line
  print "A,E" # header line
}

{
  for (i=2; i<=NF; i++)
    if (! ($i == "" && i > 2)) 
      print $1, ($i == "" ? "": $i)
}

result:

A,E
a,1
a,2
b,1
b,2
b,3
c,

The result is printed as wished, but you might want to check if I have correctly understood the logic.

[aborruso]

Since there is an off-topic response, I will add one as well :)

In miller you have the reshape verb:

mlr --csv reshape -r "[^A]" -o item,E then cut -x -f item then uniq -a input.csv

And you get

A,E
a,1
a,2
a,
b,1
b,2
b,3
c,

[ondohotola]

Thank you.

I used

 mlr -c reshape -r "[^A]" -o item,E input.csv \
   | qsv select 1,3 \
   | qsv search -v -s E '^$'

to get the expected result

A,E
a,1
a,2
b,1
b,2
b,3

It was actually something like 13 columns out of a file with 79 columns and semicolon separated but it worked as well.

But, I hate to go to the competition :-)-O

[jqnatividad]

I do want to create unpivot 😄 , once again, by leveraging Polars - https://docs.pola.rs/user-guide/transformations/unpivot/.

I'll do it the same time I switch pivot to use Polars' magical LazyFrame API - #2374