Adding values in multiple rows while combining the preceding data

user_10321115 0
Viewed 56

I am trying to add integers together from multiple rows while maintaining the data that comes before the number.

Here is my raw data:

"2020-06","28347","Afghanistan","791","anonymous","3128"
"2020-06","28347","Afghanistan","830","anonymous","402"
"2020-06","28347","Afghanistan","10019","anonymous","79"
"2020-06","28347","Afghanistan","10070","anonymous","829"
"2020-06","28347","Afghanistan","10604","anonymous","4319"
"2020-06","28347","Albania","266","anonymous","60"
"2020-06","28347","Albania","824","anonymous","23"
"2020-06","28347","Albania","10163","anonymous","166"
"2020-06","28347","Algeria","267","anonymous","11047"

And here is what I expect my output to be:

28347,Afghanistan,8757
28347,Albania,249
28347,Algeria,11047

So far what I have done is I'm extracting the second and third columns out of the data, then trying to loop through each one with a grep and add together the values. Unfortunately I get a total combined value instead of the values for each country.

COUNTRIES=$(awk -F\, '{OFS=",";}{print $2,$3}' file.dat | sort | uniq)

for COUNTRY in "${COUNTRIES[@]}"
do
  NUMBER=$(grep $COUNTRY file.dat | awk -F\, '{print $6}' | sed 's/\"//g' | awk '{s+=$1} END {print s}')
  echo "$COUNTRY,$NUMBER" | sed 's/\"//g'
done

Which gives me

28347,Afghanistan
28347,Albania
28347,Algeria,20053

I'm not quite sure why it is giving me the whole total instead of the total per country. Any ideas?

awk bash
2 Answers

You may use this awk:

awk -F'","' -v OFS=, '{sums[$2 OFS $3] += $NF} END {for (i in sums) print i, sums[i]}' file

28347,Albania,249
28347,Algeria,11047
28347,Afghanistan,8757

If you want to sort in alphabetical order of country names then use this gnu awk variant:

awk -F'","' -v OFS=, '
{sums[$2 OFS $3] += $NF}
END {
   PROCINFO["sorted_in"]="@ind_str_asc"
   for (i in sums)
      print i, sums[i]
}' file

28347,Afghanistan,8757
28347,Albania,249
28347,Algeria,11047
user_5482250

bash 5 comes with a loadable csv command, so you can do:

BASH_LOADABLES_PATH=${BASH%/bin/bash}/lib/bash
[[ -d $BASH_LOADABLES_PATH ]] || { echo "no $BASH_LOADABLES_PATH directory"; exit 1;}
enable -f csv csv

declare -A countries=()
while IFS= read -r line || [[ $line ]]; do 
    if csv -a fields "$line" ; then 
        key="${fields[1]},${fields[2]}"
        ((countries[$key] += fields[5]))
    fi
done < file.dat

for key in "${!countries[@]}"; do 
    printf "%s,%d\n" "$key" "${countries[$key]}"
done | sort

28347,Afghanistan,8757
28347,Albania,249
28347,Algeria,11047

Using miller

mlr --csv --implicit-csv-header put -q '@sum[$2][$3] += $6; end {emit @sum, "id","country"}' file.dat

id,country,sum
28347,Afghanistan,8757
28347,Albania,249
28347,Algeria,11047
user_75520
Related