1

I've the input in test.txt as

month,var1,var2
June,10,a
Sep,20,b
Dec,30,c

I want the output as

month,variable,value
June,var1,10
June,var2,a
Sep,var1,20
Sep,var2,b
Dec,var1,30
Dec,var2,c

Note that the number of columns starting from second column is dynamic. It can have 2 columns or 100 columns. How can we transpose the data using awk?

1
  • If an answer provides a satisfactory answer (as seems to be the case), please mark it as accepted by checking the box next to it. A green check mark will tell others that the answer was accepted and can be further exploited.
    – Cbhihe
    Feb 16, 2020 at 17:01

2 Answers 2

2

Try

$ awk -F, '
    BEGIN{OFS=FS} 
    NR==1{n = split($0,a); print "month,variable,value"; next}
    {for(i=2;i<4;i++) print $1,a[i],$i}
' file
month,variable,value
June,var1,10
June,var2,a
Sep,var1,20
Sep,var2,b
Dec,var1,30
Dec,var2,c

(this is more like exploding than transposition).

3
  • Thank you so much for your response. The output is close but it doesn't give the exact output. The headers are incorrect. Also, note that I only want 3 columns in the output even if there are 100 columns in the input.
    – GiveSeek
    Feb 15, 2020 at 2:58
  • @SrikanthKondamadugula please see edited answer Feb 15, 2020 at 3:03
  • @steeldriver, perfect! Thanks so much!
    – GiveSeek
    Feb 15, 2020 at 3:05
0
awk -F, '
    BEGIN{OFS=FS} 
    NR==1{n = split($0,a); print "month,variable,value"; next}
    {for(i=2;i<NF;i++) if (a[i] !=""){ print $1,a[i],$i} }
' file
1
  • 1
    Welcome to the site, and thank you for your contibution. Your answer looks very much like the accepted answer, so you may want to add some explanation on how yours is different / how that difference improves on the accepted answer.
    – AdminBee
    Jun 2, 2020 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .