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
Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
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
Improve this answer
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. Commented Mar 9, 2021 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 ,. Commented Mar 9, 2021 at 11:00

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.