1

I need to do multiple sums; my input file is:

DATE|NATION|CITY|FILES|REVENUE|FREQUENCY|INVESTMENT
20170807|USA|VIRGINIA|TIMES|1919150|1779|282075
20170807|USA|NYC|ROADS|92877|41|1599
20170808|USA|PENS|ROADS|133001|7|1
20170808|USA|NYC|TIMES|361625|1592|0
  1. Sum $5 in every uniq of $1 (date)
  2. sum $5 in every uniq where $4=="TIMES"
  3. sum $5 in every uniq where $4=="ROADS"
  4. sum $5 in every uniq where $4=="ROADS" and $3=="NYC"
  5. arrange based on column $1

my expected output

DATE|REV|TIMES|ROADS|ROADS&NYC
20170807|2012027|1919150|92877|92877
20170808|494626|361625|133001|0

I only know how to sum based on 1 column

awk -F"|" '{FS=OFS="|"}{col[$1]+=$5} END {for (i in col) print i, col[i]}'
0

Complex gawk solution to reach the goal:

awk 'BEGIN{ FS=OFS="|" }NR==1{ next }{ sum[$1]+=$5 }
     $4=="TIMES"{ d[$1]["t"]+=$5 }
     $4=="ROADS"{ d[$1]["r"]+=$5 }$3=="NYC" && $4=="ROADS"{ d[$1]["r&n"]+=$5 }
     END{ print "DATE|REV|TIMES|ROADS|ROADS&NYC"; 
     for(i in d) print i, sum[i], int(d[i]["t"]), int(d[i]["r"]), int(d[i]["r&n"]) }' file

The output:

DATE|REV|TIMES|ROADS|ROADS&NYC
20170807|2012027|1919150|92877|92877
20170808|494626|361625|133001|0
  • it works, thanks alot. I wish I can learn faster in this forum. Thanks alot – dars Aug 17 '17 at 12:45
0

Here is awk solution:

awk -F'|' 'NR>1{I[$1]+=$5}
           ($4=="TIMES"){T[$1]+=$5}
           ($4=="ROADS"){R[$1]+=$5}
           ($4=="ROADS" && $3=="NYC"){RN[$1]+=$5}
END{ print "DATE|REV|TIMES|ROADS|ROADS&NYC"; for (x in I)
     printf"%d|%d|%d|%d|%d\n", x, I[x], T[x], R[x], RN[x]}' infile.txt

The output is:

DATE|REV|TIMES|ROADS|ROADS&NYC
20170807|2012027|1919150|92877|92877
20170808|494626|361625|133001|0

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.