Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

file merge based on common columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting file merge based on common columns
# 1  
Old 07-27-2012
file merge based on common columns
I have two files
1.txt

Code:
34, ABC, 7, 8, 0.9
35, CDE, 6.5, -2, 0.01

2.txt

Code:
34, ABC, 9, 6, -1.9
35, CDE, 8.5, -2.3, 5.01

So in both files common columns are 1 and 2

so final o/p should look like

Code:
34, ABC, 7, 8, 0.9, 9, 6, -1.9
35, CDE, 6.5, -2, 0.01, 8.5, -2.3, 5.01

I tried using joins , but it's omehow repeating itself.
Can anyone suggets better way to do this.
# 2  
Old 07-27-2012
Try this:
Code:
sed 's/,/ /' 1.txt>tmp1; sed 's/,/ /' 2.txt|join -t, tmp1 -|sed 's/ /,/'; rm tmp1

Last edited by RudiC; 07-27-2012 at 03:39 PM.. Reason: needed to adapt input filenames
# 3  
Old 07-27-2012
Awk
Hi,

Try this one,
Code:
awk 'BEGIN{FS=",";OFS="";}FNR==NR{a[$1","$2]$0;next;}{t=$1","$2;if(a[t]){gsub(t,"");print a[t],$0;}}' 1.txt  2.txt

Cheers,
Ranga:-)
# 4  
Old 07-30-2012
hi Ranga,

All i did ,

Code:
awk 'BEGIN{FS=",";OFS="";}FNR==NR{a[$1","$2","$3","$4","$5","$6]$0;next;}{t=$1","$2","$3","$4","$5","$6;if(a[t]){gsub(t,"");print a[t],$0;}}'

as in my actual file there are 6 columns, which are matching and based on that I need to join the files. but somehow it's not liking the way it's done.

but which is exactly same as my initial question.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies

2. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

3. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

4. Shell Programming and Scripting

Seperated by columns, merge in a file, sort them on common column

Hi All, I have 4 files in below format. I took them as an example. File 1: Cut from position 1-4 then 6-7 then 8-14 then rest left and make them as columns in one new file. Inserting character H to the initial of all line like HCTOT. CTOT 456787897 Low fever CTOR 556712345 High fever... (2 Replies)
Discussion started by: Mannu2525
2 Replies

5. Shell Programming and Scripting

Merge files based on columns

011111123444 1234 1 20000 011111123444 1235 1 30000 011111123446 1234 3 40000 011111123447 1234 4 50000 011111123448 1234 3 50000 File2: 011111123444,Rsttponrfgtrgtrkrfrgtrgrer 011111123446,Rsttponrfgtrgtr 011111123447,Rsttponrfgtrguii 011111123448,Rsttponrfgtrgtjiiu I have 2 files... (4 Replies)
Discussion started by: vinus
4 Replies

6. Shell Programming and Scripting

Merge records based on multiple columns

Hi, I have a file with 16 columns and out of these 16 columns 14 are key columns, 15 th is order column and 16th column is having information. I need to concate the 16th column based on value of 1-14th column as key in order of 15th column. Here are the example file Input File (multiple... (3 Replies)
Discussion started by: Ravi Agrawal
3 Replies

7. Shell Programming and Scripting

Merge files based on both common and uncommon rows

Hi, I have two files A (2190 rows) and file B (1100 rows). I want to merge the contents of two files based on common field, also I need the unmatched rows from file A file A: ABC XYZ PQR file B: >LMN|chr1:11000-12456: >ABC|chr15:176578-187678: >PQR|chr3:14567-15866: output... (3 Replies)
Discussion started by: Diya123
3 Replies

8. Shell Programming and Scripting

Merge multiple lines in same file with common key using awk

I've been a Unix admin for nearly 30 years and never learned AWK. I've seen several similar posts here, but haven't been able to adapt the answers to my situation. AWK is so damn cryptic! ;) I have a single file with ~900 lines (CSV list). Each line starts with an ID, but with different stuff... (6 Replies)
Discussion started by: protosd
6 Replies

9. Shell Programming and Scripting

merge rows based on a common column

Hi guys, Please guide me if you have a solution to this problem. I have tried paste -s but it's not giving the desired output. I have a file with the following content- A123 box1 B345 bat2 C431 my_id A123 service C431 box1 A123 my_id I need two different outputs- OUTPUT1 A123... (6 Replies)
Discussion started by: smriti_shridhar
6 Replies

10. Shell Programming and Scripting

merge based on common, awk help

All, $ cat x.txt z 11 az x 12 ax y 13 ay $ cat y.txt ay TT ax NN Output required: y 13 ay TT x 12 ax NN (3 Replies)
Discussion started by: jkl_jkl
3 Replies
Login or Register to Ask a Question