Get a list of file name, and compare to a csv file while adding 2nd and 3rd column of the same 1st column

Question

I have a csv file that looks like this:

user1,400,300
user2,250,250
user3,400,300
user1,400,300
user4,400,300
user2,250,250

and a directory that has a empty file named after the user

$ ls /dir/*
/dir/user1
/dir/user2

I want to look for the file and save its name into an array, then search that name through the csv record, add all of that name's 2nd and 3rd column, for all rows, and also shows if the user value exceed ex:400

Example results:

user1,1400
user1 has exceed 400
user2,1000
user2 has exceed 400

I'd really appreciate some help. Currently I'm trying with this, but no results:

shopt -s nullglob
cd /dir/
ARRAY=(*)

for ((i=0; i<${#ARRAY[@]}; i++)); do
    name=${ARRAY[i]}
    awk '
    BEGIN{FS=OFS=","} {$name += (($2+$3))} 
    END{for (j in a) printf "%s | %0.2f\n", j, a[j]}
    ' /dir.csv
done
cd

Or maybe is there a simpler method where no need to save filename into an array?

Answer 1

I suggest

for f in dir/*; do awk -F ',' '$1==f{s+=$2+$3}END{print f","s;if(s>400)print f" has exceeded 400"}' f="$(basename "$f")" dir.csv; done

Or in unwinded, equivalent form:

for f in dir/*; do 
    awk -F ',' '
        $1==f{s+=$2+$3}
        END{
            print f","s
            if(s>400)print f" has exceeded 400"
        }
    ' f="$(basename "$f")" dir.csv
done

For each file inside dir/, its basename (i.e., the name without the path) is passed to the awk script as the variable f.

In the awk script, the field separator is set to comma with -F ','. Then, if the first field of dir.csv is equal to f, the sum of 2nd and 3rd fields is added to s. If the sum exceeds 400, the message is included.