CODEWITHSUNDEEP
bash
Pablo
Pablo

Reputation: 515

merge lines with identical column1 & column 2

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

Name,Num,Value 
ex1,10.20.30.40,val1
ex2,20.30.40.30,val2
ex3,10.45.60.20,val3
ex4,10.20.30.40,val1
ex5,20.30.40.30,val2

If the 2nd columns of 2 rows are identical, their third row is identical too. I want to merge the rows that their 2nd column (and consequently their 3rd 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,Value 
ex1 ex4,10.20.30.40,val1
ex2 ex5,20.30.40.30,val2
ex3,10.45.60.20,val3

Using this code I'm able to get the 1st and the 2nd as I wanted:

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

Here is the output:

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

but it doesn't display the 3rd column. I tried this:

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 "," a[i]}' file

but it didn't worked, it printed the 1st column again as the 3rd column. Like this:

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

How should I manipulate the code to get the desired result?

Upvotes: 2

Views: 31

Answers (1)

anubhava
anubhava

Reputation: 785176

You may use this awk:

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


Name,Num,Value
ex3,10.45.60.20,val3
ex2 ex5,20.30.40.30,val2
ex1 ex4,10.20.30.40,val1

If you want output in same order as shown in question then you may use this gnu-awk solution:

awk 'BEGIN{FS=OFS=","}
NR==1{print; next}
{k=$2 FS $3; a[k] = (k in a ? a[k] " ": "") $1}
END{PROCINFO["sorted_in"] = "@val_str_asc"; for (i in a) print a[i], i}' file.csv

Upvotes: 2

Related Questions