using awk to lookup and insert data

Question

In my continuing crusade not to use MS Excel, I'd like to process some data, send it to a file, and then insert some records from a separate file into a third file using field $1 as the index. Is this possible?

I have data like this:

2600,foo,stack,1,04/02/2015,ACH Payment,ACH Settled,1500
2600,foo,stack,2,04/06/2015,Credit Card Sale,Settled,100
2600,foo,stack,3,04/07/2015,Credit Card Sale,Settled,157.13
2600,foo,stack,4,04/07/2015,ACH Credit,ACH Settled,.03

I have this to group it:

cat group.awk
#!/usr/bin/awk -f

BEGIN {
    OFS = FS = ","
}
NR > 1 {
    arr[$1 OFS $2 OFS $3]++
}
END {
    for (key in arr)
        print key, arr[key]
}

The group makes it like this:

2600,foo,stack,4

Simple multiplication is applied to fields 5, 6 and 7 where applicable--depends on fields 3.

In this example we can say the finished record looks like this:

2600,foo,stack,4,.2,19.8

Now in a separate file, I have this data:

2600,registered user,5hPASLJlHlgJR4AQc9sZQ==

basic flow is:

awk -f group.awk data.csv | awk -f math.awk > finished.csv

Then use awk (if it can do this) to look up field $1 in finished.csv and find corresponding record above in the separate file(bill.csv) and print to a third file or insert into bill.csv.

Expected output in third file(bill.csv):

x,y,,1111111,2600,,,,,,,19.8,,,registered user,,,,,,,,,,RS,,,N5hPASLJlHlgJR4AQc9sZQ==,z,a

x,y,,1111111,RS,z,a will be pre-populated to I only need to insert three new records.

Is this something awk can accomplish?

Edit

Field $3 is the accountID that sets the multiplication on 5, 6 and 7.

Here's the idea:

bill.awk:

NR>1{if($3=="stack" && $4>199) $5=$4*0.03; 
if($3=="stack" && $4<200) $5=$4*0.05
if($3=="user") $5=$4*.01
}1

total.awk:

    awk -F, -v OFS="," 'NR>1{if($3=="stack" && $5<20) $6=20-$5;
if($3=='stack && $5>20) $6=0;}1'

This part is working and final output is like above:

2600,foo,stack,4,.2,19.8

4*.05 = .2 & 20 - .2 = 19.8

But the minimium charge is $20

So we'll correct it:

4*.05 = .2 & 20 - .2 = 20

Extra populated fields came from a separate file (bill.csv) and I need to fill in 20 to the correct record on bill.csv

bill.csv contains everything needed except the 20

before:

x,y,,1111111,2600,,,,,,,,,,,registered user,,,,,,,,,,RS,,,N5hPASLJlHlgJR4AQc9sZQ==,z,a

after:

x,y,,1111111,2600,,,,,,,20,,,registered user,,,,,,,,,,RS,,,N5hPASLJlHlgJR4AQc9sZQ==,z,a

Is this a better explanaiton? Go on the assumption that group.awk, bill.awk and total.awk are working correctly. I just need to extract the correct total for field $1 and put it in bill.csv in the correct spot.

Answer 1

Is maybe this last awk what you need. I´ve tried to understand what you want and I think is just that merging awk way:

For explaininng: We first save the fileA in an array with the first key as the index. Then we search for each line o file B if the field1 is between the indexes of our array, and if it´s, we print all data from two files together

awk -F"," 'BEGIN {while (getline < "record.dat"){ a[$1]=$0; }} {if($1 in a){ print a[$1]","$0}}' file.dat
2600,foo,stack,4,10,10.4,2600,registered user,5hPASLJlHlgJR4AQc9sZQ==

Answer 2

This is the kind of solution you need:

$ cat fileA
2600,foo,stack,1,04/02/2015,ACH Payment,ACH Settled,1500
2600,foo,stack,2,04/06/2015,Credit Card Sale,Settled,100
2600,foo,stack,3,04/07/2015,Credit Card Sale,Settled,157.13
2600,foo,stack,4,04/07/2015,ACH Credit,ACH Settled,.03
2600,foo,stack,5,04/09/2015,ACH Payment,ACH Settled,147.10

$ cat fileB
2600,registered user,5hPASLJlHlgJR4AQc9sZQ==

$ cat tst.awk
BEGIN { FS=OFS="," }
NR==FNR{
    cnts[$1][$2FS$3]++
    next
}
{
    for (val in cnts[$1]) {
        cnt = cnts[$1][val]
        print $1, val, cnt, cnt*2.5, $2, $3
    }
}

$ awk -f tst.awk fileA fileB
2600,foo,stack,5,12.5,registered user,5hPASLJlHlgJR4AQc9sZQ==

but until you update your question we can't provide any more concrete help than that.

The above uses GNU awk 4.* for true 2D arrays.