Pivot file values

Question

I have a file where the first column is the key. The rows can have up to 2800 delimiters. I need to pivot the data from rows to columns. Below is the sample input and required output.

Source File

123,A,B,,,,AC,DF,,,,,,,,,,,,n 
567,A,B,,C,D,,,,,,,,, 7
89,C,B

Output

123,A
123,B
123,
123,
.
.
123,AC
123,DF
567,A
567,B
567,C
567,D
567,7
89,C
89,B

Please advise.

Answer 1

Using Miller (http://johnkerl.org/miller/doc/) with

mlr --nidx --fs "," reshape -r '[^1]' -o item,value \
then filter -S -x '$value==""' \
then cut -f 1,value input.txt

you have

123,A
123,B
123,AC
123,DF
123,n
567,A
567,B
567,C
567,D
567, 7
89,C
89,B

If you want also null value is

mlr --nidx --fs "," reshape -r '[^1]' -o item,value \
then cut -f 1,value input.txt

And you will have

123,A
123,B
123,
123,
123,
123,AC
123,DF
123,
123,
123,
123,
123,
123,
123,
123,
123,
123,
123,n
567,A
567,B
567,
567,C
567,D
567,
567,
567,
567,
567,
567,
567,
567, 7
89,C
89,B

Answer 2

$ cat file
123,A,B,,,,AC,DF,,,,,,,,,,,,n
567,A,B,,C,D,,,,,,,,, 7
89,C,B

$ awk -F, 'BEGIN { OFS = FS } { for (i = 2; i <= NF; ++i) if ($i != "") print $1, $i }' file
123,A
123,B
123,AC
123,DF
123,n
567,A
567,B
567,C
567,D
567, 7
89,C
89,B

This assumes that the data is in a simple CSV format that does not require any quotes (no embedded commas or newlines in any field). The awk code simply iterates over the comma-delimited fields of each line, from field 2 onwards, and prints them along with the first field on new lines. Empty fields are ignored. If you don't want to ignore empty fields (it's unclear in the question), remove the if ($i != "") bit in the code.