13

I have some output in the form of:

count  id     type
588    10 |    3
 10    12 |    3
883    14 |    3
 98    17 |    3
 17    18 |    1
77598    18 |    3
10000    21 |    3
17892     2 |    3
20000    23 |    3
 63    27 |    3
  6     3 |    3
 2446    35 |    3
 14    4 |    3
 15     4 |    1
253     4 |    2
19857     4 |    3
 1000     5 |    3
...

Which is pretty messy and needs to be cleaned up to a CSV so I can gift it to a Project Manager for them the spreadsheet the hell out of it.

The core of the problem is this: I need the output of this to be:

id, sum_of_type_1, sum_of_type_2, sum_of_type_3

An example of this is id "4":

14    4 |    3
 15     4 |    1
253     4 |    2
19857     4 |    3

This should instead be:

4,15,253,19871

Unfortunately I'm pretty rubbish at this sort of thing, I've managed to get all the lines cleaned up and into CSV but I haven't been able to deduplicate and group the rows. Right now I have this:

awk 'BEGIN{OFS=",";} {split($line, part, " "); print part[1],part[2],part[4]}' | awk '{ gsub (" ", "", $0); print}'

But all that does is clean up the rubbish characters and print the rows again.

What is the best way of getting massaging the rows into the above-mentioned output?

  • Do you even want to sum the counts together? – h.j.k. Mar 1 '17 at 14:04
12

A way to do it is to put everything in a hash.

# put values into a hash based on the id and tag
awk 'NR>1{n[$2","$4]+=$1}
END{
    # merge the same ids on the one line
    for(i in n){
        id=i;
        sub(/,.*/,"",id);
        a[id]=a[id]","n[i];
    }
    # print everyhing
    for(i in a){
        print i""a[i];
    }
}'

edit: my first answer didn't answer the question properly

  • Yep, this did the trick very nicely. Thanks! Only thing is I didn't account for some types from IDs to be empty and thus messing up the CSV, but I can work that little detail out – Paul Mar 1 '17 at 19:50
  • @Paul Maybe add NF<4{$4="no_type";} at the start – DarkHeart Mar 2 '17 at 0:20
11

Perl to the rescue:

#!/usr/bin/perl
use warnings;
use strict;
use feature qw{ say };

<>;  # Skip the header.

my %sum;
my %types;
while (<>) {
    my ($count, $id, $type) = grep length, split '[\s|]+';
    $sum{$id}{$type} += $count;
    $types{$type} = 1;
}

say join ',', 'id', sort keys %types;
for my $id (sort { $a <=> $b } keys %sum) {
    say join ',', $id, map $_ // q(), @{ $sum{$id} }{ sort keys %types };
}

It keeps two tables, table of types and table of ids. For each id, it stores the sum per type.

5

If GNU datamash is an option for you, then

awk 'NR>1 {print $1, $2, $4}' OFS=, file | datamash -t, -s --filler=0 crosstab 2,3 sum 1
,1,2,3
10,0,0,588
12,0,0,10
14,0,0,883
17,0,0,98
18,17,0,77598
2,0,0,17892
21,0,0,10000
23,0,0,20000
27,0,0,63
3,0,0,6
35,0,0,2446
4,15,253,19871
5,0,0,1000
4

Python (and the pandas library in particular is very suited for this kind of work

data = """count  id     type
588    10 |    3
 10    12 |    3
883    14 |    3
 98    17 |    3
 17    18 |    1
77598    18 |    3
10000    21 |    3
17892     2 |    3
20000    23 |    3
 63    27 |    3
  6     3 |    3
 2446    35 |    3
 14    4 |    3
 15     4 |    1
253     4 |    2
19857     4 |    3
 1000     5 |    3"""

import pandas as pd
from io import StringIO # to read from string, not needed to read from file

df = pd.read_csv(StringIO(data), sep=sep='\s+\|?\s*', index_col=None, engine='python')

This reads the csv data to a pandas DataFrame

    count  id  type
0     588  10     3
1      10  12     3
2     883  14     3
3      98  17     3
4      17  18     1
5   77598  18     3
6   10000  21     3
7   17892   2     3
8   20000  23     3
9      63  27     3
10      6   3     3
11   2446  35     3
12     14   4     3
13     15   4     1
14    253   4     2
15  19857   4     3
16   1000   5     3

Then we group this data by id, and take the sum of column count

df_sum = df.groupby(('type', 'id'))['count'].sum().unstack('type').fillna(0)

The unstack reshapes this to move the id's to the columns, and the fillna fills the empty fields with 0's

df_sum.to_csv()

This returns

id,1,2,3
2,0.0,0.0,17892.0
3,0.0,0.0,6.0
4,15.0,253.0,19871.0
5,0.0,0.0,1000.0
10,0.0,0.0,588.0
12,0.0,0.0,10.0
14,0.0,0.0,883.0
17,0.0,0.0,98.0
18,17.0,0.0,77598.0
21,0.0,0.0,10000.0
23,0.0,0.0,20000.0
27,0.0,0.0,63.0
35,0.0,0.0,2446.0

Because the dataframe contains missing data (empty id-type combinations), pandas transforms the ints to float (limitation of the internal workings) If you know the inputs wil be int only, you could change the next-to last line to df_sum = df.groupby(('type', 'id'))['count'].sum().unstack('type').fillna(0).astype(int)

  • 1
    You should explain what the code you've provided does, so it's helpful to everyone who sees this post, rather than this one specific person. – Nic Hartley Mar 1 '17 at 14:36
  • Is this clearer? I also corrected the regex for the seperator – Maarten Fabré Mar 2 '17 at 17:21
  • Looks good to me. Thanks for adding an explanation! – Nic Hartley Mar 2 '17 at 17:21
3

You can use Perl to loop over the CSV file and accumulating the sum of the appropriate types in a hash while on the way. And in the end, display the information collected for every ID.

Data Structure

%h = (
   ID1    =>  [ sum_of_type1, sum_of_type2, sum_of_type3 ],
   ...
)

This helps in making sense of the code below:

Perl

perl -wMstrict -Mvars='*h' -F'\s+|\|' -lane '
   $, = chr 44, next if $. == 1;

   my($count, $id, $type) = grep /./, @F;
   $h{ $id }[ $type-1 ] += $count}{
   print $_, map { $_ || 0 } @{ $h{$_} } for sort { $a <=> $b } keys %h
' yourcsvfile

Output

2,0,0,17892
3,0,0,6
4,15,253,19871
5,0,0,1000
...
1

my take, not too different from others. Uses GNU awk which has arrays of arrays

gawk '
    NR == 1 {next}
    {count[$2][$4] += $1}
    END {
        for (id in count) {
            printf "%d", id
            for (type=1; type<=3; type++) {
                # add zero to coerce possible empty string into a number 
                printf ",%d", 0 + count[id][type]
            }
            print ""        # adds the newline for this line
        }
    }
' file

outputs

2,0,0,17892
3,0,0,6
4,15,253,19871
5,0,0,1000
10,0,0,588
12,0,0,10
14,0,0,883
17,0,0,98
18,17,0,77598
21,0,0,10000
23,0,0,20000
27,0,0,63
35,0,0,2446
0

You can use this code to sum up values based upon your id column,

I have added one awk statement after your code

awk 'BEGIN{OFS=",";} {split($line, part, " "); print part[1],part[2],part[4]}' abcd | awk '{ gsub (" ", "", $0); print}' | awk 'BEGIN{FS=OFS=SUBSEP=","}{arr[$2,$3]+=$1;}END{for ( i in arr ) print i,arr[i];}'

Go ahead with this ...

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.