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?

GiveSeek
  • 35
  • 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 '20 at 17:01

2 Answers2

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).

steeldriver
  • 81,074
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
pandiyaraj an
  • 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 02 '20 at 12:05