How to print next nth column in current line after pattern match?

Question

If the first and second columns of the next row are the same as the current row, I want to print the last column of each row to the current row.

My input file is

 A 123 BC
 A 123 DD
 A 123 TT
 B 456 AA
 B 456 RR
 C 789 EE

Desired output is

 A 123 BC DD TT
 B 456 AA RR
 C 789 EE

Answer 1

awk:

awk '{a[$1FS$2]=a[$1FS$2]FS$NF} END {for(i in a) print i a[i]}' 

• a[$1FS$2]=a[$1FS$2]FS$NF sets keys of associative array as first two fields separated by the field delimiter, and values as the last field concatenated to the previous value by field delimiter

• END {for(i in a) print i a[i]} is executed at the end, it iterates over the keys of array a and print keys and respective values

Example:

% cat file.txt 
A 123 BC
A 123 DD
A 123 TT
B 456 AA
B 456 RR
C 789 EE

% awk '{a[$1FS$2]=a[$1FS$2]FS$NF} END {for(i in a) print i a[i]}' file.txt
A 123 BC DD TT
B 456 AA RR
C 789 EE

Answer 2

Here's one way, with GNU datamash

$ datamash -Ws groupby 1,2 collapse 3 < file | sed 's/[,\t]/ /g'
A 123 BC DD TT
B 456 AA RR
C 789 EE

The sed command replaces the default field and collapse separators with spaces.

Answer 3

First sort the input file and unique (-u) lines over the first two columns -k1,2 and cut the third column.

Then iterate over lines in pattern and collect the third column (sed) from input file. Finally remove line breaks tr and print the matches.

pattern=$(sort -k1,2 -u < file | cut -d' ' -f1-2) 
while read -r line
do
 collect=$(sed -n 's/^'"$line"'//p' file | tr '\n' ' ')
 echo "$line $collect"
done <<<"$pattern"

Answer 4

sed -E '
   :loop
      $!N
      s/^(((\S+\s+){2}).*)\n\2/\1 /
   tloop
   P;D
' yourfile

---

Results

A 123 BC DD TT
B 456 AA RR
C 789 EE

---

Explanation

We setup a do-while loop and append the next line to the pattern space and after that compare the first two fields with the same after the newline in the pattern space. If they are able to be removed from the pattern space then we repeat the loop and get out of the loop on the inability to do so. At which point, we print the pattern space upto the first newline. And remove this portion and go back for more.