how to merge rows ina csv file if if the first column is same in bash

Question

I have written a program to find the open ports of a given IP. since the print function is in a loop my output format is like this:

IP1,22
IP1,23
IP1,24
IP2,25
IP2,26
IP3,27
IP3,30
IP3,45

How do I get it in this format:

IP1,22,23,24
IP2,25,26
IP3,27,30,45

EDIT: this is what I have done so far

awk'{a[$1]=(a[$1])? a[$1]r : r }

but I dont know how to progress forward from here.

my main code is in python and I am making changes to the python code to print the output on this format.However on the output files that I already have i want to just convert them into that format so I dont have to run the scan again — Anuj Kulkarni, Jan 28 '19 at 08:23
@AnujKulkarni, it is always advised to add your effort in your post as we all are here to learn, kindly do so and let us know. — RavinderSingh13, Jan 28 '19 at 08:31
@RavinderSingh13 ok I have an incomplete code as I am not very fluent with awk i will edit my question with my efforts — Anuj Kulkarni, Jan 28 '19 at 08:40

RavinderSingh13 · Accepted Answer · 2019-01-29T02:31:26.097

3

Kindly always do add your efforts in your question in code tags. If you are not worried for order of output then try following.

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

In case you need to get output in same order in which 1st field of Input_file is coming then try following.

awk '
BEGIN{
  FS=OFS=","
}
!b[$1]++{
  c[++count]=$1
}
{
  a[$1]=($1 in a ? a[$1] OFS : "") $2
}
END{
  for(i=1;i<=count;i++){
    print c[i],a[c[i]]
  }
}'  Input_file

Since later OP found out control-m characters were found in Input_file(s) too so adding following:

tr -d '\r' < Input_file > temp && mv temp Input_file

edited Jan 29 '19 at 02:31

answered Jan 28 '19 at 08:25

RavinderSingh13

130,504
14
57
93

this prints the output as ip1,port1\n,port2\n,port3\n rather than printing it in one row – Anuj Kulkarni Jan 28 '19 at 08:41
@AnujKulkarni, could you please check if your Input_file have control M by doing `cat -v Input_file` if yes then removbe them by doing `tr -d '\r' < Input_file > temp && mv temp Input_file`. – RavinderSingh13 Jan 28 '19 at 08:44
thanks.My input file had control M.corrected it with your given code. Could you please shed some light upon why the control M character is introduced? thanks – Anuj Kulkarni Jan 28 '19 at 08:57
1

@AnujKulkarni, when we copy something from windows to unix then they appear usually. – RavinderSingh13 Jan 28 '19 at 09:06

stack0114106 · Answer 2 · 2019-01-28T11:18:56.787

Another awk..

$ cat anuj.txt
IP1,22
IP1,23
IP1,24
IP2,25
IP2,26
IP3,27
IP3,30
IP3,45
$ awk -F, ' { c=$1; if(c!=p) { printf("\n%s,%d",$1,$2)} else { printf(",%d",$2) } p=c } END { print ""} ' anuj.txt

IP1,22,23,24
IP2,25,26
IP3,27,30,45
$

There is an empty line in the above solution. To get rid of it, you can use below

$ awk -F, ' { c=$1; if(c!=p) { printf("%s%s,%d",sep,$1,$2)} else { printf(",%d",$2) } p=c;sep="\n" } END { print ""} ' anuj.txt
IP1,22,23,24
IP2,25,26
IP3,27,30,45
$

score 0 · Answer 3 · edited Mar 22 '19 at 19:59

0

sort anuj.txt | cut -d, -f1 | sort -u  | while read line; do echo $line,$(grep $line anuj.txt | cut -d, -f2 | paste -sd, -); done

Assumptions: Your file is comma separated and has the same format you show above.

Explanation (FWIW): First find the unique IP identifiers, then grep for that string in the file, for each line found get the second field and concatenate them.

edited Mar 22 '19 at 19:59

kalehmann

4,821
6
26
36

answered Mar 22 '19 at 19:50

Unix Lover

1

how to merge rows ina csv file if if the first column is same in bash

3 Answers3