Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Join two files combining multiple columns and produce mix and match output

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join two files combining multiple columns and produce mix and match output
# 1  
Old 11-28-2014
Join two files combining multiple columns and produce mix and match output
I would like to join two files when two columns in each file matches with each other and then produce an output when taking multiple columns.

Like I have file A
Code:
1234,ABCD,23,JOHN,NJ,USA
2345,ABCD,24,SAM,NY,USA
5678,GHIJ,24,TOM,NY,USA
5678,WXYZ,27,MAT,NJ,USA

and file B
Code:
ABCD,IND,GH,23,PIR,KUM
WXYZ,KKK,YU,27,OPI,UUU
TTTY,TTT,OP,31,IOP,RRR

and I want the output when column 2 and 3 of file A matches with Column 1 and 4 of file B; then produce output like below
Code:
fileA.column1,fileA.column3,fileB.column6,fileB.column1,fileA.column2,fileB.column1

# 2  
Old 11-29-2014
Hello Mady135,

Following may help you in same.

Code:
awk -F, 'FNR==NR{A[$2 OFS $3]=FILENAME"."$1 OFS FILENAME"."$3;B[$2 OFS $3]=FILENAME"."$2;next} ($1 OFS $4 in A){print A[$1 OFS $4] OFS FILENAME"." $6 OFS FILENAME"." $1 OFS B[$1 OFS $4] OFS FILENAME"." $1}' OFS=, fileA fileB

Output willl be as follows.
Code:
fileA.1234,fileA.23,fileB.KUM,fileB.ABCD,fileA.ABCD,fileB.ABCD
fileA.5678,fileA.27,fileB.UUU,fileB.WXYZ,fileA.WXYZ,fileB.WXYZ


Thanks,
R. Singh
# 3  
Old 11-29-2014
Try:
Code:
awk 'NR==FNR{B[$1,$4]=$6; next} ($2,$3) in B{print $1, $3, B[$2,$3], $2, $2, $2}'  FS=, OFS=, file2 file1

Are you sure that is what you want? Note that the last 3 fields are always going to be the same since fileA.column2 is always equal to fileB.column1
Last edited by Scrutinizer; 11-29-2014 at 05:40 AM..
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

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

3. Shell Programming and Scripting

Combining certain columns of multiple files into one file

Hello Unix gurus, I have a large number of files (say X) each containing two columns of data and the same number of rows. I would like to combine these files to create a unique merged file containing X columns corresponding to the second column of each file (with a bonus of having the first... (3 Replies)
Discussion started by: ksennin
3 Replies

4. UNIX for Dummies Questions & Answers

Match the columns between two files and output

Hi Help, I have two files namely a.txt and b.txt a.txt looks like a.txt 1 2 2 1 3 3 2 4 4 4 5 6 6 7 7 b.txt looks like, b.txt 1 2 1 1 3 2 2 4 3 3 4 4 4 5 5 (2 Replies)
Discussion started by: Indra2011
2 Replies

5. Shell Programming and Scripting

Match first two columns and average third from multiple files

I have the following format of input from multiple files File 1 24.01 -81.01 1.0 24.02 -81.02 5.0 24.03 -81.03 0.0 File 2 24.01 -81.01 2.0 24.02 -81.02 -5.0 24.03 -81.03 10.0 I need to scan through the files and when the first 2 columns match I... (18 Replies)
Discussion started by: ncwxpanther
18 Replies

6. Shell Programming and Scripting

Match two columns from two files and print output

Hello, I have two files which are of the following format File 1 which has two columns Protein_ID Substitution NP_997239 T53R NP_060668 V267M NP_058515 P856A NP_001206 T55M NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies

7. Shell Programming and Scripting

Combining columns from multiple files into one single output file

Hi, I have 3 files with one column value as shown File: a.txt ------------ Data_a1 Data_a2 File2: b.txt ------------ Data_b1 Data_b2 Data_b3 Data_b4 File3: c.txt ------------ Data_c1 Data_c2 Data_c3 Data_c4 Data_c5 (6 Replies)
Discussion started by: vfrg
6 Replies

8. Shell Programming and Scripting

Combining columns from multiple files to one file

I'm trying to combine colums from multiple file to a single file but having some issues, appreciate your help. The filenames are the same except for the extension, path1.m0 --------- a b c d e f g h i path1.m1 --------- m n o p q r s t u File names are path1.m The... (3 Replies)
Discussion started by: rkmca
3 Replies

9. Shell Programming and Scripting

simple join for multiple files and produce 3 outputs

sh script file1 filea fileb filec ................filez. >>output1 & output2 &output3 file1 z10 1873 1920 z_number1_E59 z10 2042 2090 z_number2_E59 Z22 2476 2560 z_number3_E59 Z22 2838 2915 z_number4_E59 z1 1873 1920 z_number1_E60 z1 ... (9 Replies)
Discussion started by: stateperl
9 Replies

10. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies
Login or Register to Ask a Question