Maximum values from column is not coming while using foreach loop

Question

I have two files, pins and Output.csv.

File "pins" contains the below information

A
A[0]
A[1]
B[0]
B[1]
C

File "Output.csv" contains the below information

A,10
A[0],9
A[0],11
A[1],8
B[0],19
B[1],2
B[1],60
C,12

I had written a shell script, to get the output like this (Final_output.csv)

A,10
A[0],11
A[1],8
B[0],19
B[1],60
C,12 

foreach line1 (`cat pins`)
set final_value = `grep -w $line1 Output.csv | cut -d, -f2 | sort | tail -1`
echo "$line1,$final_value" >> Final_output.csv
end 

But I am only getting the values of A and C. For A[0], A[1], B[0], B[1] values are not coming. What is wrong with the code?

Answer 1

You can use join to filter the Output file with only the keys in the pins file, then use awk to find the max values:

join -t, pins Output.csv | awk -F, '!($1 in max) || $2 > max[$1] {max[$1] = $2} END {for (key in max) print key FS max[key]}'

A,10
C,12
A[0],11
A[1],8
B[0],19
B[1],60

The output will be unordered.

Or do it all in awk

awk -F, '
    NR == FNR {pin[$1]; next}
    ($1 in pin) && (!($1 in max) || $2 > max[$1]) {max[$1] = $2}
    END {for (key in max) print key FS max[key]}
' pins Output.csv