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

user_14588240 0
Viewed 96

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
...
http://www.z.com/100
",flower
"http://www.z.com/101
http://www.z.com/102
http://www.z.com/103
http://www.z.com/104
...
http://www.z.com/200
",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(NR%100!=0)ORS="\t";else ORS="\n"}1' test.csv > result.csv
csv awk shell
3 Answers

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\"\n,%s\n",data,$1,$2)
    data=""
    next
}
{data=data sprintf("%s\n",$1)}' file file

If you want to print either at the change of the $2 value or at some fixed line interval (like 100) you can do:

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

Either 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
user_2986070

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
user_58665800 
$ awk -F, '$2!=p || n==100 {if(NR!=1) print "\"," p; printf "\""; p=$2; n=0}
    {print $1; n+=1} END {print "\"," p}' test.csv
"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

First set the field separator to the comma (-F,). Then:

  • If the second field changes ($2!=p) or if we already printed 100 lines in the current batch (n==100):
    • if it is not the first line, print a double quote, a comma, the previous second field and a newline,
    • print a double quote,
    • store the new second field in variable p for later comparisons,
    • reset line counter n.
  • For all lines print the first field and increment line counter n.
  • At the end print a double quote, a comma and the last value of second field.
user_17737980
Related