Merging two file based on comparison of first columns Post: 302921265

Sponsored Content

Top Forums Shell Programming and Scripting Merging two file based on comparison of first columns Post 302921265 by RavinderSingh13 on Thursday 16th of October 2014 05:58:57 AM

10-16-2014

Moderator

Hello Manoj,

Following may also help on same.

Code:

awk -vVAR="Does not exist in FILE1" 'FNR==NR{X[$1]=$2;next} ($1 in X){print $0 OFS X[$1];delete X[$1];next} !($1 in X) {print $0 OFS "00000000"} END{for(j in X){print j OFS X[j] OFS VAR}}' file2 file1

Output wille bas follows.

Code:

1123730002 00033284 00000000
1123730009 00040274 00040890
1123730012 00226766 00226766
1123730013 00210287 00210737
1123730014 00010837 00000000
1123730019 00052739 00052739
1123730022 00065186 00065253
1123730023 00043871 00044045
1123730024 00109525 00110138
1123730025 00521107 00521520
1123730026 00286315 00286315
1123730027 00080430 00080430
1123730028 00023592 00024264
1123730030 00118469 00118469
1123730034 00207165 00207312
1123730035 00121622 00121845
1123730036 00109599 Does not exist in FILE1
1123730037 00238295 Does not exist in FILE1
1123730038 00446582 Does not exist in FILE1
1123730000 00517526 Does not exist in FILE1
1123730041 00182018 Does not exist in FILE1

EDIT: Just adding a non single liner form of solution as follows.

Code:

awk -vVAR="DOES NOT EXIST IN FILE1" '
FNR==NR{
X[$1]=$2;next
}
($1 in X) {print $0 OFS X[$1];delete X[$1];next}
!($1 in X) {print $0 OFS "00000000"}
END{
for(j in X)
{print j OFS X[j] OFS VAR}
}' file2 file1

Thanks,
R. Singh

Last edited by RavinderSingh13; 10-16-2014 at 07:03 AM.. Reason: Added a non single liner form of soluiton too

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

merging few columns of two text files to a new file

hi i need to select a few columns of two txt files and write it to a new file. there is one common field for both of these files. plz help me in this thanks in advance

2. Shell Programming and Scripting

Merging columns from multiple files in one file

Hi, I want to select columns from multiple files and combine them in one file. The files are simulation-data-files with 23 columns each and about 50 rows. I now use: cut -f 11 Sweep?wing-30?scale=0.?0?fan2?.txt | pr -3 | awk '{printf("\n%s\t%s\t%s",$1,$2,$3)}' > ../Data_Processed/output.txtI...

3. Shell Programming and Scripting

merging similar columns in a single line file

Hi Guys. I have tried the commands sort and join. But I couldn't able to to find the command for joining in a single line based on keys.My example inputs and outputs are like the following. Help would be appreciated.:D Input file a1tabXXXXXXX a2tabXXXXXXX a6tabYYYYYYYYY a71tabXXXXXXX...

4. Shell Programming and Scripting

Simple XML file comparison and merging

Okay, first of all, thanks to everyone who's helped me out before... I appreciate the opportunity to learn. I have two iTunes XML files, and I simply want to compare the contents, then merge. Theoretically, this will allow me to merge two libraries, keeping playlists intact (depending on iTunes'...

5. UNIX for Dummies Questions & Answers

Merging two files based on two columns to make a third file

Hi there, I'm trying to merge two files and make a third file. However, two of the columns need to match exactly in both files AND I want everything from both files in the output if the two columns match in that row. First file looks like this: chr1 10001980 T A Second...

6. Shell Programming and Scripting

Merging columns based on one or more column in two files

I have two files. FileA.txt 30910 rs7468327 36587 rs10814410 91857 rs9408752 105797 rs1133715 146659 rs2262038 152695 rs2810979 181843 rs3008128 182129 rs3008131 192118 rs3008170 FileB.txt 30910 1.9415219673 0 36431 1.3351312477 0.0107191428 36587 1.3169171182...

7. Shell Programming and Scripting

File comparison based on contents

Hi I have 2 files 1.del ---- 1,2,3,4,5 1,2,3,4,4 1,1,1,1,2 2.del ---- 1,2,3,4,5 1, 1,2,3,4,4 1,1,1,1,2 I need to compare the above two files in unix, as in the output should only tell the difference in contents as I should get only the line 1 ( from 2.del) , rest all lines are...

8. Shell Programming and Scripting

Awk: Modifying columns based on comparison

Hi, I have following input in the file in which i want to club the entries based on $1. Also $11 is equal to $13 of other record(where $13 must be on higher side for any $1) then sum all other fields except $11 & $13. Final output required is as follows: INPUTFILE: ...

9. Shell Programming and Scripting