0

I'm trying to pivot a file using awk. This is an example of my input File:

P2P,P2P,MP,DL,AIR,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2
P2P,P2P,MP,DL,AI,UP,HR,UPE,GSM,F,PP,4,NA,0.2,0.8
P2P,P2P,MP,DL,AIR,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2
P2P,P2P,MP,DL,AI,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2
P2P,P2P,MP,DL,AIR,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2
P2P,P2P,MP,DL,AIR,UP,HR,UPE,GSM,F,PP,1,NA,0.2,0.2
P2P,P2P,MP,DL,BIR,UP,HR,UPE,GSM,S,PP,10,NA,0.2,2
P2P,P2P,MP,KT,AIR,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2
P2P,P2P,MP,MM,AIR,UP,HR,UPE,GSM,S,PP,1,NA,0.2,0.2

Desired output should be following.

AIR,5,1  
AI,1,0.2  
BIR,10,2
  • Here the first field belongs to $5.
  • The Second field belogs to $12 (total of $12).
  • And the third field belongs to $15 (total of $15).

Condition should be following in awk:

$3=="MP" && $10=="S" && $5!="MP"
1
  • 2
    I am not sure "pivot" is the word to use here. "Aggregate" is more apt. In my understanding, to pivot means to change columns and rows.
    – joepd
    Jan 12, 2016 at 12:07

2 Answers 2

3

With awk:

awk -F, '$3=="MP"&&$10=="S"&&$5!="MP"{a[$5]+=$12;b[$5]+=$15}
  END{for(i in a){print i","a[i]","b[i]}}' file

  • -F, sets the delimiter to ,.
  • $3=="MP"&&$10=="S"&&$5!="MP"{...} this is the condition from the question
    • a[$5]+=$12;b[$5]+=$15 fills an array a with the total value of $12 and an array b with the total value of $15.
  • END{...} this block runs when awk has finished processing all lines.
    • for(i in a) loops trough the a array
    • print i","a[i]","b[i]} prints the values in the two arrays and its index.

The output:

AI,1,0.2
AIR,5,1
BIR,10,2
4
  • + add OFS="," and print i, a[i], b[i] will look better than print i","a[i]","b[i]
    – Costas
    Jan 12, 2016 at 12:04
  • This does not maintain the order, though it is not clear if that is needed.
    – joepd
    Jan 12, 2016 at 12:06
  • @joepd What kind of order would you expect?
    – Costas
    Jan 12, 2016 at 12:07
  • @Costas: The one stated as "Desired outcome". Not sure how that would relate to the data, though, except order of first appearance. Will make things a bit more complicated.
    – joepd
    Jan 12, 2016 at 12:09
0

Using awk for filtering and GNU Datamash for the pivoting you can do:

$ awk -F, '$3=="MP" && $10=="S" && $5!="MP"' FILE.TXT \
     | datamash --sort -t, --group 5 sum 12 sum 15
AI,1,0.2
AIR,5,1
BIR,10,2

You must log in to answer this question.

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