CODEWITHSUNDEEP
awk
linuxlearn
linuxlearn

Reputation: 3

awk print column data in rows based on matching key

I am trying to write an awk string to print column data in rows based on match.
My file is as below: 

$ cat 1.txt  
2016-05-10,UJ,ALL 1 7  
2016-05-10,UJ,ALL 1 10  
2016-05-10,UJ,ALL 1 9  
2016-05-10,UJ,ALL 1 8  
2016-05-10,UJ,ALL 1 14  
2016-05-10,UJ,ALL 1 8  
2016-05-10,UJ,ALL 1 12  
2016-05-10,UJ,ALL 2 11  
2016-05-10,UJ,ALL 1 10  
2016-05-10,UJ,ALL 2 12  
2016-05-10,UJ,ALL 2 9  
2016-05-10,UJ,ALL 1 13

expected output is as below (uniq key match is before first space i.e. 2016-05-10,UJ,ALL) 

2016-05-10,UJ,ALL<\tab>1 1 1 1 1 1 1 2 1 2 2 1<\tab>7 10 9 8 14 8 12 11 10 12 9 13

I am using below awk pattern matching

enter image description here

awk '$1 != prev{printf "%s%s",ors,$1; ors=ORS; ofs="\t"} {printf "%s%s",ofs,$2; ofs=OFS; prev=$1} END{print ""}' 1.txt

but it is not working on last coulmn, i tried all possible combinations but no success... please suggest.

Upvotes: 0

Views: 561

Answers (2)

Sundeep
Sundeep

Reputation: 23667

$ head -n1 1.txt | cut -d' ' -f1
2016-05-10,UJ,ALL
$ # transform multiple lines to single line with space as separator
$ cut -d' ' -f2 1.txt | paste -sd' '
1 1 1 1 1 1 1 2 1 2 2 1
$ cut -d' ' -f3 1.txt | paste -sd' '
7 10 9 8 14 8 12 11 10 12 9 13

$ # finally, combine the three results
$ # by default paste uses tab as delimiter
$ paste <(head -n1 1.txt | cut -d' ' -f1) <(cut -d' ' -f2 1.txt | paste -sd' ') <(cut -d' ' -f3 1.txt | paste -sd' ') 
2016-05-10,UJ,ALL   1 1 1 1 1 1 1 2 1 2 2 1 7 10 9 8 14 8 12 11 10 12 9 13

$ # to use a different delimiter
$ paste -d: <(head -n1 1.txt | cut -d' ' -f1) <(cut -d' ' -f2 1.txt | paste -sd' ') <(cut -d' ' -f3 1.txt | paste -sd' ')
2016-05-10,UJ,ALL:1 1 1 1 1 1 1 2 1 2 2 1:7 10 9 8 14 8 12 11 10 12 9 13


Another option is to use GNU datamash, however it will give comma separated values

$ datamash -t' ' -W -g1 collapse 2 -g1 collapse 3 <1.txt
2016-05-10,UJ,ALL   1,1,1,1,1,1,1,2,1,2,2,1 7,10,9,8,14,8,12,11,10,12,9,13
  • -t' ' input delimiter is space
  • -W whitespace as output delimiter
  • -g1 collapse 2 comma separated column 2 values using column 1 as key
  • -g1 collapse 3 comma separated column 3 values using column 1 as key

Upvotes: 0

fedorqui
fedorqui

Reputation: 289515

I would go for something like:

awk -v OFS="\t" '{
     cols[$1];
     col2[$1]=(length(col2[$1]) ? col2[$1] FS : "") $2;
     col3[$1]=(length(col3[$1]) ? col3[$1] FS : "") $3
     } END {for (i in cols) print i, col2[i], col3[i]}' file

See it in action:

$ awk -v OFS="\t" '{cols[$1]; col2[$1]=(length(col2[$1]) ? col2[$1] FS : "") $2; col3[$1]=(length(col3[$1]) ? col3[$1] FS : "") $3} END {for (i in cols) print i, col2[i], col3[i]}' a
2016-05-10,UJ,ALL   1 1 1 1 1 1 1 2 1 2 2 1 7 10 9 8 14 8 12 11 10 12 9 13
#                ^                         ^
#                tab                       tab

Upvotes: 0

Related Questions