How to use bash to filter csv by column value and remove duplicates based on multiple columns

Question

I'm trying to filter my CSV by the value of one column, and then remove duplicate rows based on the values of 2 columns. For the sake of simplicity, here's an example. I would like to remove duplicate rows based on columns ID1, ID2 and Year. I would also like to filter my results by only pulling back rows with "3" in the VALUE column.

ID1,ID2,YEAR,LAT,LON,VALUE  
A,B,2016,123,456,3  
A,B,2016,133,466,3  
A,B,2016,122,446,3  
C,D,2015,223,456,3  
C,D,2015,241,455,3  
A,B,2016,123,456,2  
A,B,2016,133,466,2  
A,B,2016,122,446,2  
C,D,2015,223,456,2  
C,D,2015,241,455,2

RESULT:

ID1,ID2,YEAR,LAT,LON,VALUE  
A,B,2016,123,456,3  
C,D,2015,223,456,3

Either your description is wrong or your example is wrong as your description says your input is a CSV but your example contains no Commas. Fix your question to be accurate. — Ed Morton
– Ed Morton, Commented Oct 13, 2016 at 18:25

Artur Siara · Accepted Answer · 2016-10-17 20:36:41Z

1

You can use awk that uses an associative array with key as composite value commprising $1,$2,$3:

awk -F, '$NF==3 && !seen[$1,$2,$3]++' file.csv
ID1,ID2,YEAR,LAT,LON,VALUE
A,B,2016,123,456,3
C,D,2015,223,456,3

edited Oct 17, 2016 at 20:36

Artur Siara

1661 silver badge5 bronze badges

answered Oct 13, 2016 at 17:19

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MikeeeeeeeyC Over a year ago

Thanks so much for the response @anubhava! So that's the script I've been using, which does remove some of the duplicates, but I'm still seeing some it hasn't resolved in the CSV. It seems to work fine when I convert the csv to txt before running it though, so I figured I'd throw this question out there to try and identify why.

anubhava Over a year ago

Since your edited input file is comma separated, you just needed to have -F, in awk command. Try my updated command now.

Artur Siara · Accepted Answer · 2016-10-17 20:42:04Z

1

this solution assumes the same as was metioned above, but in more expanded version. Both awk solutions won't work if there is , in the values. Then insted of that you could use csvtool to separate values.

cat file1 |  awk -F, ' $NF==3 {unq[$1,$2,$3]=$0}  END{for (i in unq){print unq[i] }}'

answered Oct 17, 2016 at 20:42

Artur Siara

1661 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to use bash to filter csv by column value and remove duplicates based on multiple columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related