Breaking News: Grepper is joining You.com. Read the official announcement!

bash collapse duplicate values in a column while summing another column

Charles-Alexandre Roy answered on May 2, 2023 Popularity 1/10 Helpfulness 8/10

answer bash collapse duplicate values in a column while summing another column

bash collapse duplicate values in a column while summing another column

Comment

# Basic syntax:
# ChatGPT gave me the perfect answer to this on the first try (along with the
# corresponding explanation)!
awk -F'\t' '{sums[$1] += $2} END {for (key in sums) {print key "\t" sums[key]}}' filename.tsv
# Where:
#	- -F'\t': sets the field separator to tab.
#	- sums[$1] += $2: adds the value in the second column to an array called
#		sums, using the value in the first column as the key.
#	- END: indicates that the following actions should be performed after all
#		the lines in the input file have been processed.
#	- for (key in sums) {print key "\t" sums[key]}: loops through the keys in
#		the sums array and prints them along with their corresponding summed
#		values, separated by a tab character.
# Note, the columns don't need to be sorted first with this approach

# Example usage:
# Say you have a comma-separated file with these values and you want to sum the
# values in column 3 for repeated values in column 1:
A, col2, 3
B, which, 6
A, has, 2
A, info, 1
B, you, 1
A, dont, 12
B, care, 7
A, about, 4

awk -F',' '{sums[$1] += $3} END {for (key in sums) {print key "\t" sums[key]}}' filename.csv
--> A       22
	B       14

# Example with xargs:
# Say you have a folder full of files that you want to count in this way.
#	In addition you want to filter for column 1 values whose column 3 sum is
#	between 10 and 99. You could do that with this:
ls *cnt* | xargs -I {} sh -c 'echo {}; awk '\''{sums[$1] += $3} END {for (key in sums) {print key "\t" sums[key]}}'\'' {} | awk '\''$2 >= 10 && $2 <= 99'\'''

Popularity 1/10 Helpfulness 8/10 Language shell

Source: chat.openai.com

Tags: bash collapse shell sum

Link to this answer
Share Copy Link

Contributed on May 02 2023

Charles-Alexandre Roy

0 Answers Avg Quality 2/10

bash collapse duplicate values in a column while summing another column

Contents

More Related Answers

bash collapse duplicate values in a column while summing another column

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.