how can I match column fields and group their values together?

user_6402640 0
Viewed 75

I am sorting some files that I've created using pdfgrep, to list page numbers of certain PDFs that I have. it produced the following output:

./Buddhism in the Shadow of Brahmanism.pdf:111:      Then, rising from his seat, covering one shoulder with his robe, the king
./Buddhism in the Shadow of Brahmanism.pdf:182:branch who has adopted the yellow robes of Buddhism; he is sur-
./Buddhism in the Shadow of Brahmanism.pdf:229:       resolve that his body, his bowl, and his monastic robe (which had been
./Buddhism in the Shadow of Brahmanism.pdf:230:robe. In this way, Mahākāśyapa (or at least his body) is to act as a sort
./Buddhism in the Shadow of Brahmanism.pdf:230:corpse to his disciples and displays to them the Buddha’s robe, and they
./Buddhism in the Shadow of Brahmanism.pdf:230:offer him the robe that the Buddha had confided to him. Only then will
./Introduction to the History of Indian Buddhism.pdf:31:the robes of a Buddhist monk in an effort to convert them, he was Sciequia. For
./Introduction to the History of Indian Buddhism.pdf:54:monks, and in particular on retreat, robes, and chastity, p. 308.—On the life of
./Introduction to the History of Indian Buddhism.pdf:97:are the Kat.hināvadāna, which deals with the bowl, the staff, and the robes of
./Introduction to the History of Indian Buddhism.pdf:111:of a sort of robe.
./Introduction to the History of Indian Buddhism.pdf:112:cover his nakedness, and who rejects all other robes as superfluous.
./Introduction to the History of Indian Buddhism.pdf:127:noon, after having taken his robe and his bowl,
./Introduction to the History of Indian Buddhism.pdf:127:bowl and his robe, he went to the place where the Cāpāla caitya6 was located,

What I'm trying to do is group together the page numbers on the second column that match the file name, I was expecting the output to look something like:

./Buddhism in the Shadow of Brahmanism.pdf:111, 182, 229, 230
./Introduction to the History of Indian Buddhism.pdf:31, 54, 97, 111, 112, 127

I've tried using awk to parse the first value and then use those results on the same file to print only the page numbers so I could grep the result and append after the file name later, like so:

awk -F : '{print $1}' parsing_file | uniq | while read line; do awk -v number="$line" -F : '$1 == "$number" { print $2 }' parsing_file; done

But that didn't go through and I am guessing uniq and while read could be cut out, and maybe use some array with awk only?

I've seen something similar done here:

https://unix.stackexchange.com/questions/167280/awk-group-by-and-sum-column-values

But instead of summing up the values on the columns, I just wanted to group them together.

Thanks

awk
2 Answers

With your shown samples, please try following. Written and tested in GNU awk.

awk  -v OFS=":" '
match($0,/^\.\/.*\.pdf:[0-9]+/){
  value=substr($0,RSTART,RLENGTH)
  split(value,arr,":")
  if(!seen[arr[1],arr[2]]++){
    name[arr[1]]=(name[arr[1]]?name[arr[1]]", ":"")arr[2]
  }
}
END{
  for(key in name){
    print key,name[key]
  }
}
'  Input_file

Output will be as follows with your shown samples:

./Buddhism in the Shadow of Brahmanism.pdf:111, 182, 229, 230
./Introduction to the History of Indian Buddhism.pdf:31, 54, 97, 111, 112, 127

Explanation: Adding detailed explanation for above.

awk  -v OFS=":" '                   ##Starting awk program from here.
match($0,/^\.\/.*\.pdf:[0-9]+/){    ##Using match function to match from starting ./ till .pdf : digits as per shown samples.
  value=substr($0,RSTART,RLENGTH)   ##Creating value with matched sub string here.
  split(value,arr,":")              ##Splitting value into array arr with : delimiter.
  if(!seen[arr[1],arr[2]]++){
     name[arr[1]]=(name[arr[1]]?name[arr[1]]", ":"")arr[2]  ##Creating name array with index of book name and its value it digits as per needed output.
  }
}
END{                                ##Starting END block of this program from here.
  for(key in name){                 ##Traversing through name here.
    print key,name[key]             ##Printing key and array value here.
  }
}
' Input_file                        ##Mentioning Input_file name here.

NOTE: previously above solution was not handling duplicate numbers coming for same paragraph so I had edited solution to take care of that case after Ed's answer.

user_58665800 
$ cat tst.awk
BEGIN { FS=OFS=":" }
$1 != title {
    if ( title != "" ) {
        print title, pages
    }
    title = $1
    pages = $2
    delete seen
    next
}
!seen[$2]++ {
    pages = pages ", " $2
}
END {
    print title, pages
}


$ awk -f tst.awk file
./Buddhism in the Shadow of Brahmanism.pdf:111, 182, 229, 230
./Introduction to the History of Indian Buddhism.pdf:31, 54, 97, 111, 112, 127

The differences between the above and @Ravinder's answer as I write this (which is after that answer was accepted) are:

  1. This relies on your input being sorted by title and page number as shown in the example in your question while Ravinders doesn't. If your real data isn't sorted then run sort -t':' -k1,1 -k2,2n file | awk ...'.
  2. This doesn't read all of the titles and page numbers into memory, it just reads the page numbers for 1 title at a time, so it'll work no matter how massive your input file is.
  3. This will output the titles in the same order they appear in the input instead of a random order.
  4. This produces the expected output by only outputting unique page numbers per title rather than the same page number being output every time it appears in the input (e.g. 111, 182, 229, 230 vs 111, 182, 229, 230, 230, 230.
  5. This produces the expected output by puting a : between the title and the first page number instead of a blank.
user_17450010
Related