0

Hello all I'm trying to read data from file, and check for each state and increment total transactions counts from data:

REDACTED

The end result should be printed to a new file in this format Result

This is what I tried as a template, but I'm confused on how to increment for each state and how to exactly write in that format to the file?

awk -v _State=$State -v _TransactionCount=$transactionCount awk 'BEGIN{ FS=OFS="," }
  arr_state[$2]+=$11
END {
    print "%s\n", "Transaction Count Report"
    for (id in arr_state) {
        print "%-10s %s\n", state, arr_state[state]
    }
}' < "summary.csv" > "transaction-rpt.csv"
4
  • Apart from the missing $11 and the fact that you don't use the variables set by -v, the program looks good. You may also have to turn the state into all-caps. Is there a problem? Apr 27, 2021 at 15:39
  • What about, using 'awk -F ',' '{print $2}' summary.csv | sort | uniq -c | tr -s a-z A-Z', will convert state codes to upper case. One caveat -> the count comes before the state codes. Apr 27, 2021 at 15:43
  • If you have multiple issues, consider asking multiple separate questions. Do not add further issues to an existing question. I have rolled back your question's text to an earlier state.
    – Kusalananda
    Apr 28, 2021 at 9:39
  • 1
    We have redacted your example data as it looked like it contained personal information. Please update your question with fake data instead.
    – Jeff Schaller
    Apr 28, 2021 at 17:55

3 Answers 3

1
awk 'BEGIN{ FS=OFS="," }
    { arr_state[toupper($2)]+=$11 }
END {
    print "Transaction Count Report"
    for (id in arr_state) {
        printf "%-10s %d\n", id, arr_state[id]
    }
}' infile
9
  • How would I go about adding each transaction amount up to get $total?
    – puuyo
    Apr 27, 2021 at 16:00
  • @puuyo one way is using a temporary variable outside BEGIN/END, to sum up the transactions on 11th column then print within END block, you got how to do it by yourself? Apr 27, 2021 at 16:21
  • @awk -v _transaction=$TRANSACTION 'BEGIN {FS=OS=","} { arr_trans[$6] += $16} END (print "state\n" "gender\n" "transactionamount"} ' file (format is customer_id, fname, lname, email, gender, purchase_amount, state = $12) something like that? I'm confused
    – puuyo
    Apr 27, 2021 at 16:29
  • Something like that? I'm confused
    – puuyo
    Apr 27, 2021 at 16:39
  • @puuyo wait.... what is the purpose of the use of -v _transaction=$TRANSACTION? in what part of your script you use that, that I cannot see. why you have changed to arr_trans[$6] += $16? I cannot join unsorted things honestly, can you show the main issue and your final goal? as I see youbare trying to do something with that same input but in different questions. Apr 27, 2021 at 16:40
0

Assuming that the count that you want to accumulate for each state (two letter combination in the second column) is the last on each line of input:

$ awk -F, -v OFS='\t' '{ sum[$2] += $NF } END { print "State", "Transaction count"; for (i in sum) print toupper(i), sum[i] }' file.csv
State   Transaction count
OR      366.68
WV      531.13
AK      792.74
AL      477.18
TX      166.62
CA      781.65
FL      88.88
NV      858.58
MN      93.65
GA      171.06
LA      570.32
OK      921.6

This sets the output field separator, OFS, to a tab character on the command line, and also the input field delimiter, FS, to a comma character. This assumes that the input is a simple CSV file, i.e. that it's a file containing lines with comma-delimited fields, where no field contains embedded commas or newline characters.

The associative array sum uses the second field as its key, and for each line, the sum is incremented using the last field on the line.

At the end, a simple header is printed, and the collected data in the sum array is outputted.

As a stand-alone awk program, the code would be written as

BEGIN {
    FS = ","
    OFS = "\t"
}

{
    sum[$2] += $NF
} 

END {
    print "State", "Transaction count"

    for (i in sum)
        print toupper(i), sum[i]
}

This would be used as

$ awk -f script.awk file.csv

Would you want the sum rounded to the nearest integer, print int(sum[i]+0.5) instead of just sum[i].

0

Arriving late to the party, but for the sake of completing and improving a little bit on the accepted answer, I use your second example input csv file to distribute results on males and females in each state (PII in file was redacted by me):

$ cat infile
c77ea913-c4a2-4ff6-b615-26028b375fda,wye,nnnnnnnnn,[email protected],0,243.54,dddddddddddddddd,8a412563-2956-4bf3-9df3-54277a9799c7,yyyy-mm-dd,ss ssss sssssss, ccccccccc,ca,90000,ddd-ddd-2825
c117f14d-1322-45a9-9f9b-63db5105fd6c,melany,nnnnnnnnn,[email protected],0,23.26,4.dddddddddddddddd,a59201e8-9d3c-4f2b-b1fb-6914895fa5cd,yyyy-mm-dd,aa aaaaaaaaaaa aaa, ccccccccc,wv,25000,ddd-ddd-2869
3153178c-a3fb-44d3-896f-f39da69fe826,obed,nnnnnnnnnn,[email protected],1,477.18,dddddddddddddddd,4724a9c3-fc8a-49d3-956a-b0646364d3ef,yyyy-mm-dd,aaa aaaaa aaaaa,cccccccccccc,al,35000,ddd-ddd-8746
e4eb28e6-f960-49eb-8f5d-361df60f00c7,jerome,nnnnnnnnn,[email protected],1,236.99,dddddddddddddddd,af393a75-6298-4c82-84e0-a778ec9956a2,yyyy-mm-dd,aa aaa aaaaa aaaaaaa,cccccccccccccccc,wv,25000,ddd-ddd-3204
b5c32ded-e769-4c52-a553-e34b548280d0,damiano,nnnnnnnnn,[email protected],1,921.60,dddddddddddddddd,ff06abad-6787-4236-856d-74ec49df3d04,yyyy-mm-dd,aaaa aa aaaaaaaa,cccccccccccc,ok,73000,ddd-ddd-5361

where the 5th field's value is either "0" for females or "1" for males, following my (no-doubt questionable) gender attribution based on provided firstnames.

$ awk 'BEGIN {FS=","} 
       {assoc_arr[toupper($12)$5]+=$6} 
       END {for (id in assoc_arr) {
           printf "%-10s %-4s %8.2f\n", substr(id,1,2), substr(id,3), assoc_arr[id]
           }
       }' infile

CA         0      243.54
AL         1      477.18
WV         0       23.26
WV         1      236.99
OK         1      921.60 
  • The associate array assoc_arr can be made to hold any composite index values. Here, it's toupper($12)$5, but you could chain in any available awk field.
  • Use "float" number formatting (as %f) instead of "digit" (as %d) in the printf action inside the awk one liner to be able to print amounts with decimals.
0

You must log in to answer this question.

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