Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Merge rows into one if first 2 columns match

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Merge rows into one if first 2 columns match
# 1  
Old 04-15-2015
Merge rows into one if first 2 columns match
Hi,

I wanted to merge the content and below is input and required output info.

Input:
Code:
/hello,a,r
/hello,a,L
/hello,a,X
/hi,b,v
/hi,b,c

O/p:
Code:
/hello,a,r:L:X
/hi,v,:v:c


Moderator's Comments:
Mod Comment Use code tags, thanks.
Last edited by zaxxon; 04-15-2015 at 09:35 AM..
# 2  
Old 04-15-2015
Now that we know what you "wanted", where exactly are you stuck accomplishing your "wants/needs"?
# 3  
Old 04-15-2015
And, where's the b in your second output line? And, where does the leading : come from?
# 4  
Old 04-15-2015
I was able to megre first column but not sure on second column.I tried but didnt get the output in required manner.

Output should be like below.

Code:
/hello,a,r:L:X
/hi,b,:v:c

Thanks for your reply
Last edited by Scrutinizer; 04-16-2015 at 12:04 AM.. Reason: CODE tags
# 5  
Old 04-15-2015
try

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

This User Gave Thanks to senhia83 For This Post:
ankitas
# 6  
Old 04-16-2015
Quote:
Originally Posted by ankitas
I was able to megre first column but not sure on second column.I tried but didnt get the output in required manner.

Output should be like below.

Code:
/hello,a,r:L:X
/hi,b,:v:c

Thanks for your reply
Why do you insist on the leading colon in line 2's field 3?
# 7  
Old 04-18-2015
Quote:
Originally Posted by senhia83
try

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


It worked.Thank you very much for your reply
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

3. Shell Programming and Scripting

Rows to Columns with match criteria

Hello Friends, I have a input file having hundreds of rows. I want them to translate in to columns if column 1 is same. Input data: zp06 xxx zp06 rrr zp06 hhh zp06 aaa zp06 ggg zp06 qwer zp06 ser zl11 old3 zl11 old4 zl11 old5 zl11 old6 zl11 old7 zm14 luri zm14 body zm14 ucp (9 Replies)
Discussion started by: suresh3566
9 Replies

4. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

5. Shell Programming and Scripting

Merge matching rows

Hello, I need this output. thank you very much. input: Code: ***table***wood ***snack***top ***table***garfield ***big***zen ***table***cars output: Code: ***table***wood2345garfield2345cars ***snack***top ***big***zen (7 Replies)
Discussion started by: tara123
7 Replies

6. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

7. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

8. Shell Programming and Scripting

Match on first and last columns and merge

Hi Friends, I have an input file of this kind input.txt output.txt The logic is as follows, read a row, compare column1 to the next, if there is a match, compare column4. If these two match, grab the minimum of column2 and maximum of column3 and print the output with column1,... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

9. Shell Programming and Scripting

merge similar rows

I have a large file (10M lines) that contains two columns: a frequency and a string, ex: 3 aaaaa 4 bbbbb 2 ccccc 5 aaaaa 1 ddddd 4 ccccc I need to merge the lines whose string part is the same, while updating the frequency. The output should look like this: 8 aaaaa 4 bbbbb 5 ccccc... (2 Replies)
Discussion started by: tootles564
2 Replies

10. Shell Programming and Scripting

How to merge rows into columns ????

Hi guz I want to merge multiple rows into a multiple columns based on the first column. The file has symbol // I want to break the symbool // and I nedd exactlynew column at that point the output will be like this please guyz help in this isssue!!!!! merging rows into columns ... (4 Replies)
Discussion started by: bogu0001
4 Replies
Login or Register to Ask a Question