Grouping multiple columns and concatenate


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Grouping multiple columns and concatenate
# 1  
Old 10-22-2013
Grouping multiple columns and concatenate

I have a CSV file that goes like this:
Code:
Name,Group,Email
Max,Group1,max@.com
Dan,Group2,dan@.com
Max,Group3,max@.com
Max,Group4,max@.com
Dan,Group5,dan@.com
Jim,Group6,jim@.com

Basically my desired output should be:
Code:
Name,Group,Email
Max,Group1|Group3|Group4,max@.com
Dan,Group2|Group5,dan@.com
Jim,Group6,jim@.com

I have tried using this code:
Code:
awk -F',' '{if(a[$1])a[$1]=a[$1]"|"$2","$3; else a[$1]=$2$3;}END{for (i in a)print i "," a[i];}' OFS=, FileInput

but I think this is only usable for a file with 2 columns, also is it possible to not include the headers in concatenating.

Thanks a lot!

Last edited by jeffreybsu; 10-22-2013 at 12:25 AM.. Reason: Use CODE tags not only for code, but also for input and output samples.
# 2  
Old 10-22-2013
Code:
awk -F',' 'NR==1{print;next} {a[$1] = a[$1]"|"$2; b[$1] = $3} END {for (x in a){print x,substr(a[x],2),b[x]}}' OFS=, FileInput

This User Gave Thanks to balajesuri For This Post:
# 3  
Old 10-22-2013
If a given name might have a different email address for some groups, you might want to try:
Code:
awk '
BEGIN { FS = OFS = "," }
NR == 1 {print
        next
}       
!(($1,$3) in n) {
        n[$1,$3] = $1
        g[$1,$3] = $2
        e[$1,$3] = $3 
        next
}
{       g[$1,$3] = g[$1,$3] "|" $2 }
END {   for(i in n)
                print n[i], g[i], e[i]
}' FileInput

As always, if you want to try this on a Solaris/SunOS system, replace awk in this script with /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk.

Last edited by Don Cragun; 10-22-2013 at 01:29 AM.. Reason: fix typo
This User Gave Thanks to Don Cragun For This Post:
# 4  
Old 10-22-2013
How about this

Code:
$ awk -F, '{_[$3]=(!(_[$3]))? $1 FS $2: _[$3] OFS $2}END{for(i in _)print _[i] FS i}' OFS="|" file

EDIT of _ to Arr : Easy to understand as suggested by Jotne in #5
Code:
$ awk -F, '{Arr[$3]=!Arr[$3]? $1 FS $2: Arr[$3] OFS $2}END{for(i in Arr)print Arr[i] FS i}' OFS="|" file

Resulting
Code:
Name,Group,Email
Dan,Group2|Group5,dan@.com
Jim,Group6,jim@.com
Max,Group1|Group3|Group4,max@.com


Last edited by Akshay Hegde; 10-22-2013 at 03:06 AM.. Reason: suggested by Jotne in #5
This User Gave Thanks to Akshay Hegde For This Post:
# 5  
Old 10-22-2013
Its not a good habit to use _ as an variable. New user would have problem understanding what is going on.
Change it to some like
Code:
awk -F, '{arr[$3]=arr[$3]?arr[$3] OFS $2:$1 FS $2} END {for(i in arr) print arr[i] FS i}' OFS=\| file

also removed some not needed parentheses around first test, and change the order of it to test for true.

Last edited by Jotne; 10-22-2013 at 02:59 AM..
This User Gave Thanks to Jotne For This Post:
# 6  
Old 10-22-2013
Sorry Jotne you told me before also to replace _ with some name as its difficult for new users to understand code, I am forgetting every time, I just modified #4 as you said.

Last edited by Akshay Hegde; 10-22-2013 at 03:02 AM..
This User Gave Thanks to Akshay Hegde For This Post:
# 7  
Old 10-22-2013
Thanks for the help guys. It's finally done!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to grouping time and based on value with multiple pattern?

Hi All, need help... I have some log below : ### {"request_id":"e8395eb0-a8bd-11e9-b77b-d507ea5312aa","message":"when inquiry paybill 628524871 prevalidation cause : Invalid Transaction"} ### {"request_id":"043f2310-a8be-11e9-b57b-f9c7344998d7","message":"when inquiry paybill 62821615... (2 Replies)
Discussion started by: fajar_3t3
2 Replies

2. Shell Programming and Scripting

Concatenate two columns in a file

Hi i am having one requirement like to concatenate two columns in to one columns, can any one help on this please sample scenario i am placing below COL1,COL2,COL3,COL4 1,A,B,C,D 2,e,f,g,h ouput should be 3 columns like below. COL1,COL2,newcolumns 1,A,B,CD 2,e,f,gh ... (9 Replies)
Discussion started by: bhaskar v
9 Replies

3. Shell Programming and Scripting

How to concatenate 2-columns by 2 -columns for a text file?

Hello, I want to concatenate 2-columns by 2-columns separated by colon. How can I do so? For example, I have a text file containing 6 columns separated by tab. I want to concatenate column 1 and 2; column 3 and 4; column 5 and 6, respectively, and put a colon in between. input file: 1 0 0 1... (10 Replies)
Discussion started by: huiyee1
10 Replies

4. UNIX for Dummies Questions & Answers

Concatenate two columns and separate by - (minus)

hi all could you please help me to concatenate two colomns and separate them by "-" the two colomns to concatenate are colomuns 1 and 3 of a very bif file clomn 1 is chr, 2 is snp and 3 is bp the new colomn is chr_B input file : 1 rs1111 10583 1 rs1891 10611 1 rs1807 ... (13 Replies)
Discussion started by: biopsy
13 Replies

5. Shell Programming and Scripting

regular expression grouping across multiple lines

cat book.txt book1 price 23 sku 1234 auth Bill book2 sku 1233 price 22 auth John book3 auth Frank price 24 book4 price 25 sku 129 auth Tod import re f = open('book.txt', 'r') text = f.read() f.close() m =... (2 Replies)
Discussion started by: chirish
2 Replies

6. Shell Programming and Scripting

concatenate multiple file

Hi Need some help to concatenate files I have multiple spool files nearlly 15 of them which I need to concatenate like as shown in the below example for ex. file1.txt aaaa|bbbbb|cccc| dddd|eeee|ffff| kkkkk|uuuuu|gggg| file2.txt xxxx|yyyy|zzzz| 1111||kkkk|lllll... (2 Replies)
Discussion started by: rashmisb
2 Replies

7. Shell Programming and Scripting

Concatenate columns from multiple files

Hi all, I want the 2nd column of every file in the directory in a single file with the file name as column header. $cat file1.txt a b c d e f $cat file2.txt f g h g h j $cat file3.txt a b d f g h (2 Replies)
Discussion started by: newbie83
2 Replies

8. Shell Programming and Scripting

Concatenate columns from files

Hi, I have a file1.txt like this: and a file2.txt like this: I wat only append file2.txt to file1.txt and to have something like this: How could i do ? (2 Replies)
Discussion started by: AdminLew
2 Replies

9. Shell Programming and Scripting

concatenate 'n' number of columns in a file

i have a file which may have 'n' number of columns 1 222 fafda 32 afdaf 4343 4343 234 43fdaf 4343 fdd fdfd fdfd fdd fdfd fdfd fdfd fdfd fdfd fdd fdfd fdfd need to concatenate the columns with... (3 Replies)
Discussion started by: mlpathir
3 Replies

10. Shell Programming and Scripting

Find multiple patterns on multiple lines and concatenate output

I'm trying to parse COBOL code to combine variables into one string. I have two variable names that get literals moved into them and I'd like to use sed, awk, or similar to find these lines and combine the variables into the final component. These variable names are always VAR1 and VAR2. For... (8 Replies)
Discussion started by: wilg0005
8 Replies
Login or Register to Ask a Question