Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

rollup and concat fields in a group

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting rollup and concat fields in a group
# 1  
Old 01-28-2011
rollup and concat fields in a group
Hello,
I have a file with two fields in the following format..

Code:
NewYork|Rob
Boston|Mellisa
NewYork|Kevin
Boston|John
Chicago|Mike
Boston|Tom


My output should be:
Code:
NewYork|Rob,Kevin
Boston|Mellisa,John,Tom
Chicago|Mike

Basically I need to rollup on column A and stringconcat column B.

Thanks!
Last edited by radoulov; 01-28-2011 at 07:06 AM.. Reason: Code tags, please!
# 2  
Old 01-28-2011
Code:
awk -F\| 'END {
  for (__ in _) print __ FS _[__]
  }
{ 
  _[$1] = _[$1] ? _[$1] OFS $2 : $2 
  }' OFS=, infile

# 3  
Old 01-28-2011
Try this,
Code:
 awk -F"|" '{if(a[$1]){a[$1]=a[$1]","$2} else { a[$1]=$2}} END {for (i in a) {print i"|"a[i]}}' inputfile

This User Gave Thanks to pravin27 For This Post:
bperl
# 4  
Old 01-28-2011
Quote:
Originally Posted by radoulov
Code:
awk -F\| 'END {
  for (__ in _) print __ FS _[__]
  }
{ 
  _[$1] = _[$1] ? _[$1] OFS $2 : $2 
  }' OFS=, infile

little updates for more understandable:

Code:
awk -F \| '
{a[$1]=a[$1]?a[$1] OFS $2:$2}
END{for (i in a) print i FS a[i]} 
' OFS=, infile

# 5  
Old 01-28-2011
Or even less readable:

Code:
awk -F\| '
  END{for(x in O)print x FS O[x]}{O[$1]=O[$1]?O[$1]OFS$2:$2}
  ' OFS=, infile

# 6  
Old 02-02-2011
Java
Quote:
Originally Posted by pravin27
Try this,
Code:
 awk -F"|" '{if(a[$1]){a[$1]=a[$1]","$2} else { a[$1]=$2}} END {for (i in a) {print i"|"a[i]}}' inputfile

Thank you. Your solution seems to work without throwing any errors.
Login or Register to Ask a Question

Previous Thread | Next Thread

6 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk: group multiple fields from different records

Hi, My input looks like that: A|123|qwer A|456|tyui A|456|wsxe B|789|dfgh Using awk, I am trying to get: A|123;456|qwer;tyui;wsxe B|789|dfgh For records with same $1, group all the $2 in a field (without replicates), and all the $3 in a field (without replicates). What I have tried:... (6 Replies)
Discussion started by: beca123456
6 Replies

2. Shell Programming and Scripting

Shell Script to Group by Based on Multiple Fields in a file

Hi, I want to know if there is any simple approach to SUM a field based on group by of different fields for e.g. file1.txt contains below data 20160622|XXX1||50.00||50.00|MONEY|Plan1| 20160622|XXX1||100.00||100.00|MONEY|Plan1| 20160623|XXX1||25.00||25.00|MONEY|Plan1|... (3 Replies)
Discussion started by: cnu_theprince
3 Replies

3. UNIX for Dummies Questions & Answers

keeping last record among group of records with common fields (awk)

input: ref.1;rack.1;1 #group1 ref.1;rack.1;2 #group1 ref.1;rack.2;1 #group2 ref.2;rack.3;1 #group3 ref.2;rack.3;2 #group3 ref.2;rack.3;3 #group3 Among records from same group (i.e. with same 1st and 2nd field - separated by ";"), I would need to keep the last record... (5 Replies)
Discussion started by: beca123456
5 Replies

4. Programming

Doing a SQL rollup in unix

If anyone is familiar with Oracle, there is a way to trace a file. Then there is is a script called tkprof that will generate a report that includes a rollup of some values. The problem with this script is that it only does the rollup properly if the query is finished. So I need something quick and... (2 Replies)
Discussion started by: guessingo
2 Replies

5. Shell Programming and Scripting

how to parse with awk (using different fields), then group by a field?

When parsing multiple fields in a file using AWK, how do you group by one of the fields and parse by delimiters? to clarify If a file had tom | 223-2222-4444 , randofield ivan | 123-2422-4444 , random filed ... | and , are the delimiters ... How would you group by the social security... (4 Replies)
Discussion started by: Josef_Stalin
4 Replies

6. Shell Programming and Scripting

concat fields

hi I have a file, I need to concatenate depening on the no of columns i need to concatenate. for example i need to concatenate field1,filed34,field2( no of columns is not always 3, it can be any number of fields) concat.ksh field1 field34 field2 how to achieve this, is there any argv ,argc... (10 Replies)
Discussion started by: markjason
10 Replies
Login or Register to Ask a Question