0

I have a file with four columns separated by ";" which looks exemplary like this:

Articles;Qty;Sales;Customers
ArticleA;2;6;Customer1
ArticleA;3;9;Customer2
ArticleA;5;15;Customer1
ArticleA;4;12;Customer1
ArticleB;1;2;Customer2
ArticleB;2;4;Customer1
ArticleC;3;3;Customer2

I would like make a sum per article for columns 2 and 3, I can do that with the following command:

awk -F ';' 'NR>1 {a[$1]+=$2; b[$1]+=$3} END {for (i in a) print i, a[i], b[i]}' File

But now I would also like to know how many different customers have bought this article. Unfortunately, I can't do that. Can someone tell me how my awk command should be so that I get the following result:

Articles;Qty;Sales;Count of different customers
ArticleA;14;42;2
ArticleB;3;6;2
ArticleC;3;3;1
0
0

Assuming you have GNU awk for true multidimensional arrays and for a length() function that works on arrays, the following will do:

gawk -F';' -v OFS=';' 'FNR==1{print "Articles;Qty;Sales;Count of different customers";next}
                       {qty[$1]+=$2;sls[$1]+=$3;cust[$1][$4]}
                       END{for (a in qty) {print a,qty[a],sls[a],length(cust[a])}}' input.ssv 

The result for your input file would be

Articles;Qty;Sales;Count of different customers
ArticleA;14;42;2
ArticleB;3;6;2
ArticleC;3;3;1

This program will

  • print the first line as-is and skip execution to the next line
  • sum the quantity and sales numbers in associative arrays qty and sls with the article as "array index"
  • register each customer in a two-dimensional array where the first index is the article and the second index the customer name

In the end, the program will iterate over all articles (taken from the indices of the qty array) and print article, summed quantity, summed sales, and the "length" of the customer array for that article, which amounts to the number of distinct customers.

0
0

With awk:

awk 'BEGIN{ FS=OFS=";" }
      NR>1{ s1[$1]+=$2; s2[$1]+=$3; c[$1, $4]=$1 }
END{
    print "Articles", "Qty", "Sales", "Count of different customers" 
    for(x in c) cs[c[x]]++; for(i in cs) print i, s1[i], s2[i], cs[i]
}' infile
2
  • Instead of c[$1 FS $4] it should be possible to use the c[$1,$4] syntax which automatically uses the non-printable SUBSEP character to form "fake multidimensional" array indices. – AdminBee Mar 9 at 10:38
  • @AdminBee correct, here we can use it as we don't print the keys from that array, but elsewhere that we need to preserve the FS, it should be FS instead of ,. – αғsнιη Mar 9 at 11:00

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.