0

I'm stuck with a particular requirement which requires the conversion of dynamic row data to column fields. Suppose I am having a file "output.csv" with the contents like:

TimeStamp,Block,No_of_requests
04:19:12,Block_1,4
04:19:12,Block_2,4
04:19:14,Block_1,3
04:19:15,Block_1,1
04:19:15,Block_2,2
04:19:16,Block_1,5
04:19:16,Block_2,1

What I need the output to look like:

TimeStamp,Block_1,Block_2
04:19:12,4,4
04:19:14,3,
04:19:15,1,2
04:19:16,5,1

The output.csv file is created dynamically using a script, so I don't have a restricted number of blocks (here I have Block_1 & Block_2, but the script can produce many more, eg. Block_3,Block_4,Block_5 and so on)

I need the Block numbers as column headings and the number of requests under each block wrt unique TimeStamp values.

Have been scratching my head around this for quite sometime now, still am nowhere to figuring how to create this pivot.

  • Does your CSV in and output really have empty lines between content? – Anthon Sep 18 '17 at 15:47
  • No sorry, it doesn't.... The edited one is correct.... – SohomB Sep 18 '17 at 15:49
0

Simply awk script assuming your input data are sorted.

awk -F',' 'NR>1 {a[$1]=a[$1]","$3}
    END{ print "Timestamp, Block_1, Block_2"; 
         for (i in a) print i""a[i]}' infile

else first sort it then pass it to above awk.

awk -F',' 'NR>1 {a[$1]=a[$1]","$3}
    END{ print "Timestamp, Block_1, Block_2"; 
         for (i in a) print i""a[i]}' <(sort infile) 
  • Thanks @Afshin, this runs perfectly for all records (NR>1) on the TimeStamp and No_of_requests columns... However, I do need the Block_Id's as headers against the request counts.... Any way around that? – SohomB Sep 19 '17 at 10:25
  • check my update, please – αғsнιη Sep 19 '17 at 10:35
0

GNU datamash gets you pretty close:

$ datamash -st, --header-in --filler='' crosstab 1,2 unique 3 < output.csv 
,Block_1,Block_2
04:19:12,4,4
04:19:14,3,
04:19:15,1,2
04:19:16,5,1

If you want to re-insert the TimeStamp heading, you could pipe through sed:

$ datamash -st, --header-in --filler='' crosstab 1,2 unique 3 < output.csv | 
    sed '1s/^/Timestamp/'
Timestamp,Block_1,Block_2
04:19:12,4,4
04:19:14,3,
04:19:15,1,2
04:19:16,5,1
  • Don't think I have the datamash package installed.... :( – SohomB Sep 19 '17 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.