2

I am using bash to create a script to find, group and sum fields in a CSV file. Each row will have comma-separated fields with each field following a similar convention. For each comma-separated field, there is a numeric value, then an equal sign (=), then an alphanumeric value. The "(number)=" may or may not be present in a row and, if present, the field position may vary, but appear only once in the row. In addition, the value after the equal sign will vary in length.

An example of my objective will be best. CSV File:

35=D,11=ABCD1,1=ABC,55=XYZ,38=100,40=P,18=M,54=1,59=0,10=111
35=D,11=ABCD2,1=ABC,55=XYZ,40=P,18=M,38=200,54=1,44=10.00,59=0,10=133
35=D,11=ABCD3,1=ABC,55=XYZ,40=P,18=M B,54=1,38=300,44=10.00,59=0,110=200,10=113
35=D,11=ABCD4,1=ABC,55=XYZ,38=400,40=P,18=M B F,54=1,44=10.00,59=0,110=300,10=144
35=D,11=ABCD5,1=ABC,55=ZYX,38=300,40=2,54=1,44=10.00,59=3,10=132
35=D,11=ABCD6,1=ABC,55=ZYX,38=100,40=1,18=C,54=2,59=3,10=131

I would like to have a script that would identify each field that begins with "38=" and then sum each numeric value that follows the "=" and group by each "55=". There will be a "38=" and a "55=" in each row.

The output using the above file would be (sort is optional):

55=XYZ 38=1000
55=ZYX 38=400
2

using Miller http://johnkerl.org/miller/doc, starting from this input.csv

35=D,11=ABCD1,1=ABC,55=XYZ,38=100,40=P,18=M,54=1,59=0,10=111 35=D,11=ABCD2,1=ABC,55=XYZ,40=P,18=M,38=200,54=1,44=10.00,59=0,10=133 35=D,11=ABCD3,1=ABC,55=XYZ,40=P,18=M B,54=1,38=300,44=10.00,59=0,110=200,10=113 35=D,11=ABCD4,1=ABC,55=XYZ,38=400,40=P,18=M B F,54=1,44=10.00,59=0,110=300,10=144 35=D,11=ABCD5,1=ABC,55=ZYX,38=300,40=2,54=1,44=10.00,59=3,10=132 35=D,11=ABCD6,1=ABC,55=ZYX,38=100,40=1,18=C,54=2,59=3,10=131

and running

mlr --ofs " " unsparsify then stats1 -a sum -f 38 -g 55 then rename 38_sum,38 input.csv

You will have

55=XYZ 38=1000 55=ZYX 38=400

1

steeldriver kind of beat me to it, but I came up with

perl -F'[=,]' -lane '
        %row = @F;
        $sum{$row{55}} += $row{38};
    }{ 
        print "$_ = $sum{$_}" for keys %sum
' file.csv
XYZ = 1000
ZYX = 400
1

Here's an awk solution.

awk -F, '{for(a=1;a++<=NF;){
          if($a~/^55=/){l=$a}
          if($a~/^38=/){b[l]+=substr($a,4)}
         }}END{for(x in b){print x,"38="b[x]}}' inp
  • for(a=1;a++<=NF;){ - cycle through each comma-delimited field
  • if($a~/^55=/){l=$a} - if we find a field starting with 55= then store it in variable l
  • if($a~/^38=/){b[l]+=substr($a,4)} - if we find a field starting with 38= take the value after = and accumulate it within array b, using variable l as the key
  • }}END{for(x in b){print x,"38="b[x]}} - just print the contents of the array
0

How about

awk -F, '
        {for (i=1; i<=NF; i++)  {split ($i, T, "=")
                                 if (T[1] == 55) IX = T[2]
                                 if (T[1] == 38) NM = T[2]
                                }
         SUM[IX] += NM
        }
END     {for (s in SUM) print "55=" s, "38=" SUM[s]
        }
' file
55=ZYX 38=400
55=XYZ 38=1000

Run through all the fields to find the relevant ones, split into T array, if 55 found, extract the index, if 38 found, extract summand. After the loop, do the summation. In the END section, display all summed up values with their indices.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.