Sponsored Content
Top Forums UNIX for Dummies Questions & Answers How to merge two tables based on a matched column? Post 302917536 by karthikram on Wednesday 17th of September 2014 10:41:21 AM
Old 09-17-2014
How to merge two tables based on a matched column?

Hi,

Please excuse me , i have searched unix forum, i am unable to find what i expect ,
my query is , i have 2 files of same structure and having 1 similar field/column , i need to merge 2 tables/files based on the one matched field/column (that is field 1),

file 1:
Code:
TABLE_NAME|PROD_COUNT|UAT_COUNT|DIFFERENCE
IBOXX_INDEX_WEIGHTS|21018|21018|0
EQUITY_INDEX_WEIGHTS|211909|211909|0
IGMR_FEED_NODE_MAP|38873|38873|0
BUS_HIERARCHY|105249|105249|0
SLICE_HIERARCHY|5942|5942|0
EQUITY_INDEX_WEIGHTS_ROLLUP|4246|4844|598
IBOXX_INDEX_WEIGHTS_ROLLUP|943|943|0
MRAD_CACHE|90571|90571|0
SPOT_RATES|218|218|0
POSITION_RATING|0|0|0
CDS_INDEX_JTD_WEIGHTS|0|841320|841320
CURVE_RECOVERY_RATE|1983|1983|0
FS_ENRICH|7634190|7634190|0
IBOXX_ENRICH|47957|47957|0

file 2:
Code:
TABLE_NAME|PROD_COUNT|UAT_COUNT|DIFFERENCE
IBOXX_INDEX_WEIGHTS|21018|21018|0
EQUITY_INDEX_WEIGHTS|213936|213936|0
IGMR_FEED_NODE_MAP|38166|38166|0
BUS_HIERARCHY|105230|105230|0
SLICE_HIERARCHY|5940|5940|0
EQUITY_INDEX_WEIGHTS_ROLLUP|4220|4629|409
IBOXX_INDEX_WEIGHTS_ROLLUP|943|943|0
MRAD_CACHE|0|90569|90569
SPOT_RATES|0|218|218
POSITION_RATING|0|0|0
CDS_INDEX_JTD_WEIGHTS|0|841320|841320
CURVE_RECOVERY_RATE|0|1983|1983
FS_ENRICH|7274101|7274101|0
IBOXX_ENRICH|0|47957|47957

expected output (in file2 , excluding field1, need to merge rest of field with file1):
Code:
TABLE_NAME|PROD_COUNT|UAT_COUNT|DIFFERENCE|PROD_COUNT|UAT_COUNT|DIFFERENCE
IBOXX_INDEX_WEIGHTS|21018|21018|0|21018|21018|0
EQUITY_INDEX_WEIGHTS|211909|211909|0|213936|213936|0
IGMR_FEED_NODE_MAP|38873|38873|0|38166|38166|0
BUS_HIERARCHY|105249|105249|0|105230|105230|0
SLICE_HIERARCHY|5942|5942|0|5940|5940|0
EQUITY_INDEX_WEIGHTS_ROLLUP|4246|4844|598|4220|4629|409
IBOXX_INDEX_WEIGHTS_ROLLUP|943|943|0|943|943|0
MRAD_CACHE|90571|90571|0|0|90569|90569
SPOT_RATES|218|218|0|0|218|218
POSITION_RATING|0|0|0|0|0|0
CDS_INDEX_JTD_WEIGHTS|0|841320|841320|0|841320|841320
CURVE_RECOVERY_RATE|1983|1983|0|0|1983|1983
FS_ENRICH|7634190|7634190|0|7274101|7274101|0
IBOXX_ENRICH|47957|47957|0|0|47957|47957


tried like this
Code:
awk 'FNR==NR{A[NR]=$1;next}{print A[FNR]"$1"$1}' file2 file1

but first column is also appearing.

Please advise and give a gist of awk command.

Thanks,
Regards,
karthikram
 

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ... (10 Replies)
Discussion started by: apjneeraj
10 Replies

3. Shell Programming and Scripting

merge two two txt files into one file based on one column

Hi, I have file1.txt and file2.txt and would like to create file3.txt based on one column in UNIX Eg: file1.txt 17328756,0000786623.pdf,0000786623 20115537,0000793892.pdf,0000793892 file2.txt 12521_74_4.zip,0000786623.pdf 12521_15_5.zip,0000793892.pdf Desired Output ... (5 Replies)
Discussion started by: techmoris
5 Replies

4. Shell Programming and Scripting

Merge Two Tables with duplicates in first table

Hi.. File 1: 1 aa rep 1 dd rep 1 kk rep 2 bb sad 2 ss sad 3 ee dam File 2 1 apple fruit 2 mango tree 3 lilly flower output: 1 aaple fruit aa,dd,kk rep (7 Replies)
Discussion started by: empyrean
7 Replies

5. Shell Programming and Scripting

Help with merge two file based on similar column content

Input file 1: A1BG A1BG A1BG A1CF A1CF BCAS BCAS A2LD1 A2M A2M HAT . . Input file 2: A1BG All A1CF TEMP (5 Replies)
Discussion started by: perl_beginner
5 Replies

6. Shell Programming and Scripting

merge multiple tables with perl

Hi everyone, I once again got stuck with merging tables and was wondering if someone could help me out on that problem. I have a number of tab delimited tables which I need to merge into one big one. All tables have the same header but a different number of rows (this could be changed if... (6 Replies)
Discussion started by: TuAd
6 Replies

7. UNIX for Dummies Questions & Answers

Cut from tables based on column values

Hello, I have a tab-delimited table that may contain 11,12 or 13 columns. Depending on the number of columns, I want to cut and get a sub table as shown below. However, the awk commands in the code seem to be an issue. What should I be doing differently? #cut columns 1-2,4-5,11 when 12 &... (3 Replies)
Discussion started by: Gussifinknottle
3 Replies

8. Shell Programming and Scripting

Merge multiple tables into big matrix

Hi all, I have a complex (beyond my biological expertise) problem at hand. I need to merge multiple files into 1 big matrix. Please help me with some code. Inp1 Ang_0 chr1 98 T A Ang_0 chr1 352 G A Ang_0 chr1 425 C T Ang_0 chr2 ... (1 Reply)
Discussion started by: newbie83
1 Replies

9. Shell Programming and Scripting

Merge files based on the column value

Hi Friends, I have a file file1.txt 1|ABC|3|jul|dhj 2|NHU|4|kil|eu 3|hjd|34|hfd|43 file2.txt 1||3|KING|dhj 2|NHU||k| 3|hjd|34|hd|43 i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values , (5 Replies)
Discussion started by: i150371485
5 Replies

10. Shell Programming and Scripting

Find matched patterns in a column of 2 files with different size and merge them

Hi, i have input files like below:- input1 Name Seq_ID NewID Scores MT1 A0QZX3 1.65 277.4 IVO A0QZX3 1.65 244.5 HPO A0QZX3 1.65 240.5 RgP A0Q3PP 5.32 241.0 GX1 LPSZ3S 96.1 216.9 MEL LPSS3X 4.23 204.1 LDD LPSS3X 4.23 100.2 input2 Fac AddName NewID ... (9 Replies)
Discussion started by: redse171
9 Replies
GIT-MERGE-INDEX(1)						    Git Manual							GIT-MERGE-INDEX(1)

NAME
git-merge-index - Run a merge for files needing merging SYNOPSIS
git merge-index [-o] [-q] <merge-program> (-a | [--] <file>*) DESCRIPTION
This looks up the <file>(s) in the index and, if there are any merge entries, passes the SHA-1 hash for those files as arguments 1, 2, 3 (empty argument if no file), and <file> as argument 4. File modes for the three files are passed as arguments 5, 6 and 7. OPTIONS
-- Do not interpret any more arguments as options. -a Run merge against all files in the index that need merging. -o Instead of stopping at the first failed merge, do all of them in one shot - continue with merging even when previous merges returned errors, and only return the error code after all the merges. -q Do not complain about a failed merge program (a merge program failure usually indicates conflicts during the merge). This is for porcelains which might want to emit custom messages. If git merge-index is called with multiple <file>s (or -a) then it processes them in turn only stopping if merge returns a non-zero exit code. Typically this is run with a script calling Git's imitation of the merge command from the RCS package. A sample script called git merge-one-file is included in the distribution. ALERT ALERT ALERT! The Git "merge object order" is different from the RCS merge program merge object order. In the above ordering, the original is first. But the argument order to the 3-way merge program merge is to have the original in the middle. Don't ask me why. Examples: torvalds@ppc970:~/merge-test> git merge-index cat MM This is MM from the original tree. # original This is modified MM in the branch A. # merge1 This is modified MM in the branch B. # merge2 This is modified MM in the branch B. # current contents or torvalds@ppc970:~/merge-test> git merge-index cat AA MM cat: : No such file or directory This is added AA in the branch A. This is added AA in the branch B. This is added AA in the branch B. fatal: merge program failed where the latter example shows how git merge-index will stop trying to merge once anything has returned an error (i.e., cat returned an error for the AA file, because it didn't exist in the original, and thus git merge-index didn't even try to merge the MM thing). GIT
Part of the git(1) suite Git 1.8.5.3 01/14/2014 GIT-MERGE-INDEX(1)
All times are GMT -4. The time now is 03:29 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy