Merge rows with same value and every 100 lines in csv file using command-CodePudding

I have a csv file like below:

http://www.a.com/1,apple
http://www.a.com/2,apple
http://www.a.com/3,apple
http://www.a.com/4,apple
...
http://www.z.com/1,flower
http://www.z.com/2,flower
http://www.z.com/3,flower
...

I want combine the csv file to new csv file like below:

"http://www.a.com/1
http://www.a.com/2
http://www.a.com/3
http://www.a.com/4",apple
"http://www.z.com/1
http://www.z.com/2
http://www.z.com/3
http://www.z.com/4",flower

I want keep the first column every cell have max 100 lines http url. Column two same value will appear in corresponding cell.

Is there a very simple command pattern to achieve this idea ？

I used command below:

awk '{if(NR0!=0)ORS="\t";else ORS="\n"}1' test.csv > result.csv

CodePudding user response：

1st solution: With your shown samples, please try following awk code.

awk '
BEGIN{
  s1="\""
  FS=OFS=","
}
prev!=$2 && prev{
  print s1 val s1,prev
  val=""
}
{
  val=(val?val ORS:"")$1
  prev=$2
}
END{
  if(val){
    print s1 val s1,prev
  }
}
' Input_file

2nd solution: In case your Input_file is NOT sorted with 2nd column then try following sort awk code.

sort -t, -k2 Input_file | 
awk '
  BEGIN{
    s1="\""
    FS=OFS=","
  }
  prev!=$2 && prev{
    print s1 val s1,prev
    val=""
  }
  {
    val=(val?val ORS:"")$1
    prev=$2
  }
  END{
    if(val){
      print s1 val s1,prev
    }
  }
'

Output will be as follows:

"http://www.a.com/1
http://www.a.com/2
http://www.a.com/3
http://www.a.com/4",apple
"http://www.z.com/1
http://www.z.com/2
http://www.z.com/3",flower

CodePudding user response：

Given:

cat file
http://www.a.com/1,apple
http://www.a.com/2,apple
http://www.a.com/3,apple
http://www.a.com/4,apple
http://www.z.com/1,flower
http://www.z.com/2,flower
http://www.z.com/3,flower

Here is a two pass awk to do this:

awk -F, 'FNR==NR{seen[$2]=FNR; next}
seen[$2]==FNR{
    printf("\"%s%s\",%s\n",data,$1,$2)
    data=""
    next
}
{data=data sprintf("%s\n",$1)}' file file

Prints:

"http://www.a.com/1
http://www.a.com/2
http://www.a.com/3
http://www.a.com/4",apple
"http://www.z.com/1
http://www.z.com/2
http://www.z.com/3",flower