Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Merge selective columns from files based on common key
# 1  
Old 11-04-2015
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:
Code:
#file1-header1
file1-header2    
111    qwe   rtz   uio
198    asd    fgh    jkl
165    yxc
789    poi    uzt    rew
89    lkj

File2:
Code:
#file2-header2
file2-header2
165    ghz    nko2    vhu1    yse
198    mlp    nko1    vhu1    xft    sew

The output file should look like File1 but with additional printing of values from $3 and $4 from File2 if the pattern in $1 is matched:

File3:
Code:
#file1-header1
file1-header2    
111    qwe   rtz   uio
198    asd    fgh    jkl    nko1    vhu1
165    yxc    nko2    vhu1
789    poi    uzt    rew
89    lkj

I don't mind that the order of the rows is changed, so I tried
Code:
join <(sort File1) <(sort File2) > File3

but it doesn't print as I would. An awk solution might be awesome

Please help
# 2  
Old 11-04-2015
Would this come close:
Code:
 awk 'FNR==NR {T[$1]=$3 FS $4; next} {print $0, T[$1]}' file2 file1
#file1-header1 
file1-header2     
111    qwe   rtz   uio 
198    asd    fgh    jkl nko1 vhu1
165    yxc nko2 vhu1
789    poi    uzt    rew 
89    lkj

This User Gave Thanks to RudiC For This Post:
dovah
# 3  
Old 11-04-2015
Awesome, worked like a charm! Smilie
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 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. Shell Programming and Scripting

awk - Merge two files based on one key

Hi, I am struggling with the an awk command to merge two files based on a common key. I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended File1 COL1|COL2|COL3|COL4|COL5|COL6|COL7... (3 Replies)
Discussion started by: Ads89
3 Replies

4. 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

5. 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

6. 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

7. Shell Programming and Scripting

file merge based on common columns

I have two files 1.txt 34, ABC, 7, 8, 0.9 35, CDE, 6.5, -2, 0.01 2.txt 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 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... (3 Replies)
Discussion started by: manas_ranjan
3 Replies

8. Shell Programming and Scripting

Merge two files based on a 3rd key file

Hi, I want to merge the two files based on the key file's columns. The key file: DATE~DATE HOUSE~IN_HOUSE CUST~IN_CUST PRODUCT~PRODUCT ADDRESS~CUST_ADDR BASIS_POINTS~BASIS_POINTS ... The other 2 files are From_file & To_file - The From_file: DATE|date/time|29|9 ... (9 Replies)
Discussion started by: dips_ag
9 Replies

9. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

10. Shell Programming and Scripting

Merge files based on key

Hi Friends, Can any one help me with merging these file based on two columns : File1: A|123|99|SAMS B|456|95|GEORGE D|789|85|HOVARD File2: S|123|99|NANcY|6357 S|123|99|GREGRO|83748 A|456|95|HARRY|827|somers S|456|95|ANTONY|546841|RUDOLPH|7263 B|456|95|SMITH|827|BOISE STATE|834... (3 Replies)
Discussion started by: sbasetty
3 Replies
Login or Register to Ask a Question