CODEWITHSUNDEEP
bash
Pablo
Pablo

Reputation: 515

merge rows with identical column values

I'm a beginner in bash. I have a .csv file. It contains 2 columns (Name and Num). Here is the content of columns:

Name,Num 
ex1,10.20.30.40 
ex2,20.30.40.30 
ex3,10.45.60.20 
ex4,10.20.30.40
ex5,20.30.40.30

I want to merge the rows that their 2nd column is identical. For example here I have 2 rows that their 2nd column is "10.20.30.40". I want the output to be like this:

Name,Num 
ex1 ex4,10.20.30.40 
ex2 ex5,20.30.40.30 
ex3,10.45.60.20

so the name column of the first row contains both ex1 and ex2. I searched a lot and all I find was how to sort lines based on their 2nd column:

echo $(awk -F ',' '{print $2}' name.csv | sort) >> sub2.csv

but it only sorts and prints the second column to "sub2.csv".

I also tried this script:

echo $(awk -F',' '{k=$2;if(a[k])a[k]=a[k] OFS $1;else{a[k]=$0;b[++i]=k}}
           END{for(x=1;x<=i;x++)print a[b[x]]}' name.csv) >> sub2.csv

but the output is confusing (for example rows are not separated).

Would you please guide me about how to do this?

Upvotes: 0

Views: 868

Answers (1)

Cyrus
Cyrus

Reputation: 88636

awk 'BEGIN{FS=","} NR==1{print;next} {a[$2]=$2 in a ? a[$2] " " $1 : $1} END{for(i in a) print a[i] "," i}' file

Output:

Name,Num 
ex1 ex4,10.20.30.40
ex2 ex5,20.30.40.30
ex3,10.45.60.20

Derived from: https://stackoverflow.com/a/31283693/3776858

See: 4 Awk If Statement Examples ( if, if else, if else if, :? )

Upvotes: 1

Related Questions