Calculating cumulative sum and percent of total for columns grouped by row

user_14220032 0
Viewed 167

I have a very large table of values that is formatted like this:

apple   1   1 
apple   2   1
apple   3   1
apple   4   1
banana  25  4
banana  35  10
banana  36  10
banana  37  10

Column 1 has many different fruit, with varying numbers of rows for each fruit.

I would like to calculate the cumulative sum of column 3 for each type of fruit in column 1, and the cumulative percentage of the total at each row, and add these as new columns. So the desired output would be this:

apple   1   1   1   25.00 
apple   2   1   2   50.00
apple   3   1   3   75.00
apple   4   1   4   100.00
banana  25  4   4   11.76   
banana  35  10  14  41.18
banana  36  10  24  70.59
banana  37  10  34  100.00

I can get part way there with awk, but I am struggling with how to get the cumulative sum to reset at each new fruit. Here is my horrendous awk attempt for your viewing pleasure:

#!/bin/bash

awk '{cumsum += $3; $3 = cumsum} 1' fruitfile > cumsum.tmp
total=$(awk '{total=total+$3}END{print total}' fruitfile)
awk -v total=$total '{ printf ("%s\t%s\t%s\t%.5f\n", $1, $2, $3, ($3/total)*100)}' cumsum.tmp > cumsum.txt
rm cumsum.tmp
awk
1 Answers

Could you please try following, written and tested with shown samples.

awk '
FNR==NR{
  a[$1]+=$NF
  next
}
{
  sum[$1]+=($NF/a[$1])*100
  print $0,++b[$1],sum[$1]
}
' Input_file Input_file | 
column -t

Output for shown samples will be as follows.

apple   1   1   1  25
apple   2   1   2  50
apple   3   1   3  75
apple   4   1   4  100
banana  25  4   1  11.7647
banana  35  10  2  41.1765
banana  36  10  3  70.5882
banana  37  10  4  100

Explanation: Adding detailed explanation for above.

awk '                           ##Starting awk program from here.
FNR==NR{                        ##Checking condition FNR==NR which will be TRUE when first time Input_file is being read.
  a[$1]+=$NF                    ##Creating array a with index $1 and keep adding its last field value to it.
  next                          ##next will skip all further statements from here.
}
{
  sum[$1]+=($NF/a[$1])*100      ##Creating sum with index 1st field and keep adding its value to it, each value will have last field/value of a[$1] and multiplying it with 100.
  print $0,++b[$1],sum[$1]      ##Printing current line, array b with 1st field with increasing value of 1 and sum with index of 1st field.
}
' Input_file Input_file |       ##Mentioning Input_file name here.
column -t                       ##Sending awk output to column command for better look.
user_58665800
Related