awk print column data in rows based on matching key

Question

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

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.

I can't see how the input maps to the output. Can you explain it more clearly? — Tom Fenech
– Tom Fenech, Commented May 12, 2016 at 8:17
@fedorqui Please ignore "tttsmshub1" and consider "UJ" instead, my intension was to make the string small so... i hope you understand. I have updated my question. — linuxlearn
– linuxlearn, Commented May 12, 2016 at 12:44
@Tom Fenech input file 1.txt is treated as containing 3 columns based in spaces, so here space is used as a separator. — linuxlearn
– linuxlearn, Commented May 12, 2016 at 12:46

fedorqui · Accepted Answer · 2016-05-12 12:50:26Z

0

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

answered May 12, 2016 at 12:50

fedorqui

293k113 gold badges592 silver badges640 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

linuxlearn Over a year ago

awesome any clue how can you add a separator between both columns ? As you see output looks like a single row but i want both separated by a tab or comma.

fedorqui Over a year ago

@linuxlearn as you see, it prints a tab in between them. Test it adding | or something more obvious to confirm.

Sundeep · Accepted Answer · 2017-12-16 04:15:07Z

$ 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

Collectives™ on Stack Overflow

awk print column data in rows based on matching key

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related