2

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
2

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
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.

1

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"
0
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.

  • That would break if the input file is not sorted. I know the OP doesn't care about this case in his question. – Hölderlin May 10 '17 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.