Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Trying to combine fields with sort/join

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Trying to combine fields with sort/join
# 1  
Old 05-20-2015
Trying to combine fields with sort/join
I have a file with two fields in it delimited by a comma. Some of the first fields are duplicates. I am trying to eliminate any duplicate records in the first field, and combine the second fields in the output file.

For example, if the input is:

Code:
Jane,group=A
Bob,group=A
Bob,group=D
Joe,group=B
Bill,group=A
Cathy,group=C
David,group=A
David,group=B
Ed,group=A

I want the output to be:

Code:
Jane,group=A
Bob,group=A,group=D
Joe,group=B
Bill,group=A
Cathy,group=C
David,group=A,group=B
Ed,group=A

I've tried numerous combinations of join and sort -u with keys, etc, but can't get the desired result.

Thanks
Last edited by vgersh99; 05-20-2015 at 01:26 PM.. Reason: code tags, please
# 2  
Old 05-20-2015
Code:
awk -F, '{a[$1]=($1 in a)?a[$1] OFS $2:$2} END{ for(i in a) print i,a[i]}' OFS=, myInputFile.txt

This User Gave Thanks to vgersh99 For This Post:
DJR
# 3  
Old 05-20-2015
Thanks. Works perfectly. Took me about half an hour looking at AWK tutorials to figure out why, but I think I have it figured out now.
# 4  
Old 05-22-2015
RedHat
Input File
Code:
$ cat abc
Jane,group=A
Bob,group=A
Bob,group=D
Joe,group=B
Bill,group=A
Cathy,group=C
David,group=A
David,group=B
Ed,group=A

Code
Code:
$ perl -e '
my $hash;
while(<>){
chomp;
my @arr = split(",",$_,-1);
push @{$hash->{$arr[0]}}, $arr[1];
}
for my $key ( sort keys %{$hash} ) { print "$key,".join(",",@{$hash->{$key}})."\n"; }
' abc

Output
Code:
Bill,group=A
Bob,group=A,group=D
Cathy,group=C
David,group=A,group=B
Ed,group=A
Jane,group=A
Joe,group=B

SmilieSmilieSmilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Combine two Fields if there's a comma between

Hi All, Seeking for your assistance on how to combine two fields if there's a comma between them. What i did was, extract the records from database. file1.csv(extracted from db) 82|Supplies Station, Inc.|112012|KARISSA APPAREL, INC. - 112012|NON TRADE F/A AND... (2 Replies)
Discussion started by: znesotomayor
2 Replies

2. Shell Programming and Scripting

awk sort based on difference of fields and print all fields

Hi I have a file as below <field1> <field2> <field3> ... <field_num1> <field_num2> Trying to sort based on difference of <field_num1> and <field_num2> in desceding order and print all fields. I tried this and it doesn't sort on the difference field .. Appreciate your help. cat... (9 Replies)
Discussion started by: newstart
9 Replies

3. Shell Programming and Scripting

Join fields comparing 4 fields using awk

Hi All, I am looking for an awk script to do the following Join the fields together only if the first 4 fields are same. Can it be done with join function in awk?? a,b,c,d,8,,, a,b,c,d,,7,, a,b,c,d,,,9, a,b,p,e,8,,, a.b,p,e,,9,, a,b,p,z,,,,9 a,b,p,z,,8,, desired output: ... (1 Reply)
Discussion started by: aksijain
1 Replies

4. Shell Programming and Scripting

Search and combine fields

Hi all, 1. I have a log file 2011/11/14 00:42:50 | 38:guess pid=008499 opened Testing 0, 1, 2, 3 2011/11/14 11:43:42 | 38:guess pid=008499 closed 2011/11/14 11:47:08 | 39:guess pid=017567 opened Testing 0, 1, 2, 3 2011/11/14 11:47:08 | 40:guess pid=012780 opened Testing 0, 1,... (4 Replies)
Discussion started by: sabercats
4 Replies

5. Shell Programming and Scripting

join fields

My input is as below: 1|2|3|a02 test|303 2|2|4|1002 a05 ind|303 4|3|5|ind|30 Output 1|2|3|a02test|303 2|2|4|a05ind|303 4|3|5|ind|30 I used command: I am getting above output. Is there any simple way using awk to acheive this. Thanks. Please use code tags! (6 Replies)
Discussion started by: Jairaj
6 Replies

6. UNIX for Dummies Questions & Answers

Need help with Join on multiple fields

Hi, I need help with the join command I have 2 files that I want to join on multiple fields. I want to return all records from file 1 I also want empty fields in my joined file if there isn't a match in file 2 I have already sorted them so I know they are in the same order. file1 ... (0 Replies)
Discussion started by: shunter0810
0 Replies

7. Shell Programming and Scripting

How to combine first,second and third fields in a file

Hi Guys, I have a file as follows: 1 2 3 There are 5 spaces between 1 and 2 and one space between 2 and 3. I want: 123 How can I do this using awk? I tried using: (8 Replies)
Discussion started by: npatwardhan
8 Replies

8. Shell Programming and Scripting

how to combine fields from different files

linus>cat data1 8119463 | 15000 8136789 | 50000 8152238 | 15000 8167890 | 15000 7155789 | 15000 8123334 | 30000 7166330 | 15000 8146683 | 15000 8152238 | 15000 8167877 | 15000 7145389 | 15000 8178434 | 15000 7166222 | 50000 8167566 | 15000 8115410 | 45000... (2 Replies)
Discussion started by: littleb
2 Replies

9. Shell Programming and Scripting

how to combine 2 files by join

i wanna combine 2 files that need to use join(need to use -a1 -a2) to produce a new file and also need to sort the new file in numerical order by ID. The format of 2 files is: Filename: Prac1 # Results for Prac1. The format is: # 12345677 10 20693680 10 20179687 9 20781637 5 21907894 6 ... (1 Reply)
Discussion started by: mingming88
1 Replies

10. Shell Programming and Scripting

join on multiple fields

Is it possible to do a join on multiple fields of two files? I am trying to do something like join -t, -1 2,3 -2 2,3 -o 2.1,2.2,2.3,1.3 filea fileb I want the join to be on columns 2 and 3 of filea and columns 2 and 3 of fileb. What is hapenning is that the second file that I want to do the join... (1 Reply)
Discussion started by: reggiej
1 Replies
Login or Register to Ask a Question