Home / Questions / 398837
Accepted
Álvaro Rodríguez Pardo
Asked: 2021-10-17 11:43:29 +0800 CST 2021-10-17 11:43:29 +0800 CST

In the shell, how to get the sum of a variable from a CSV file based on two parameters?

  • 772

It turns out that I have a CSV file about covid and I want to find out who has received more PCR, if men and women. The SEX variable contains whether the individual is male or female, and the PCR variable contains the PCRs performed by each individual. However, I don't know how I could calculate this. I am thinking that I will still have to make a conditional if/elseto indicate that if the individual is a man, add the PCR performed and if it is a woman, add them on the other hand, to finally compare who has received more. If so, I have to investigate more about the use of the if conditional since I still don't know how to use it very well in the shell. If there is another way to do it, I can't think of anything.

This is what I have done so far to locate the data I need from the CSV file:

cat covid19.csv | cut -d "," -f4,8 | head -15

Where the first 15 results would be the following:

SEXE,PCR
Home,2
Dona,0
Dona,1
Dona,0
Dona,1
Dona,0
Dona,0
Home,0
Home,0
Home,0
Home,1
Home,0
Dona,1
Dona,5

Would anyone know how I could calculate the sum of the CRPs for men and for women? I don't know if in this case it can be used sedor awkbut I am not allowed to use them in this exercise, so I would look for another alternative .

linux

2 Answers

  1. Best Answer

    If you install datamash, you could do it with:

    $ datamash -Hst , -g 1 sum 2 < covid.csv

    Resulting, from a csv file type:

    $ cat covid.csv
SEXE,PCR
Home,2
Dona,0
Dona,1
Dona,0
Dona,1
Dona,0
Dona,0
Home,0
Home,0
Home,0
Home,1
Home,0
Dona,1
Dona,5

    So when you run the command datamash, you get:

    $ datamash -Hst , -g 1 sum 2 < covid.csv
GroupBy(SEXE),sum(PCR)
Dona,8
Home,3

    Where:

    • -H indicates that the first line is the headers
    • -s indicates that the file comes unordered
    • -t ,indicates that the separator is commas
    • -g 1indicates to group based on the first field
    • sum 2indicates that, of the grouped, the second column will be added

    This is assuming the file is as I described above. Regardless of the file, substitute the "1" for the column that contains the genus, and the "2" for the one that has the numerical data of the PCRs.

    Update

    I came up with another way with a script and using only bash:

    #!/bin/bash

archivo=./covid.csv
declare -A info=(
    [Home]=0
    [Dona]=0
)

shopt -s lastpipe
tail -n +2 $archivo \
  | \
while
    IFS=,
    read sexo numero
do
    curr_val=${info[$sexo]}
    info[$sexo]=$(( curr_val + numero ))
done

echo ${!info[@]}
echo ${info[@]}

    Resulting in:

    $ ./group_sum
Home Dona
3 8

    In the script, inside the while, I assumed that the first variable after the comma is the gender, and the second is the number. If not, assign the variable names according to their corresponding position in your csv file.

    • 1

  2. I would do something like this (ugly, but it works):

    temp=$( sed 's/,/ /' covid19.csv | while read blah numero; do temp=$(( $temp + $numero )); echo $temp; done | tail -n 1 )
echo salida: $temp

    Doing this kind of thing in bash is a bit tricky. I would recommend you to look at python.

    • 0