Match and insert columns


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Match and insert columns
# 1  
Old 02-08-2014
Match and insert columns

Hi,

I've got two files to match. File one:

HTML Code:
a1 b c d e
a2 b c d e
a3 b c d e
a4 b c d e
and file 2

HTML Code:
a1 1 2 3 4
a2 5 6 7 8
a3 9 10 11 12
a4 1 2 3 4
I need to match them by the first column and add the four columns of file 2 to the file 1 so that those added columns go as columns 4-7

the merged file should look like this:

HTML Code:
a1 b c 1 2 3 4 d e
a2 b c 4 5 6 7 d e
a3 b c 9 10 11 12 d e
a4 b c 1 2 3 4 d e
I've tried matching them with awk, but the merged columns always end up being in the end of the file, not sure how to insert them to the right place.

Many thanks for your time and help in advance!
# 2  
Old 02-08-2014
Try:
Code:
join -j1 -o1.1,1.2,1.3,2.2,2.3,2.4,2.5,1.4,1.5 file1 file2

# 3  
Old 02-08-2014
Hi,

Thank you for your reply!

I am sorry, the example files are small, but in reality file one has 45700 columns :-(
# 4  
Old 02-08-2014
Try:
Code:
awk 'NR==FNR{x=$1;$1="";a[x]=$0;next}{$3=$3""a[$1]}1' file2 file1

This User Gave Thanks to bartus11 For This Post:
# 5  
Old 02-08-2014
yap! that works!
Thank you so very much!!!!
# 6  
Old 04-08-2014
Hello,

Following may help too.

Code:
awk 'NR==FNR{a[$1]=$2 OFS $3 OFS $4 OFS $5;next} ($1 in a){print $1 OFS $2 OFS $3 OFS a[$1] OFS $4 OFS $5}' check_file_order_check12114 check_file_order_check12113

Output will be as follows.

Code:
a1 b c 1 2 3 4 d e
a2 b c 5 6 7 8 d e
a3 b c 9 10 11 12 d e
a4 b c 1 2 3 4 d e

Where check_file_order_check12114 is file2 and check_file_order_check12113 is file1.


Thanks,
R. Singh
 
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

Insert text after match in XML file

Having a little trouble getting this to work just right. I have xml files that i want to split some data. I have 2 <name> tags within the file I would like to take only the first tag and split the data. tag example. From this. TAB<Name>smith, john</Name> to TAB<Name>smith,... (8 Replies)
Discussion started by: whegra
8 Replies

3. Shell Programming and Scripting

Insert file after only first match

i'm using the following code to add the entire content of a file (/tmp/resources.txt) to the line directly below the line containing a pattern (wonderful) in the file mainfile.txt: sed '/^wonderful/ r /tmp/resources.txt' mainfile.txt the problem is, it adds the entire content of... (1 Reply)
Discussion started by: SkySmart
1 Replies

4. Shell Programming and Scripting

Insert Word After Match

Dear ALL, I have sample file : host.txt fullname: FreeBSD Host: server1 ip: 1.1.1.1 mask: 255.255.255.0 fullname: CentOS Host: server2 mask: 255.255.255.0 fullname: Fedora Host: server3 ip: 1.3.1.2 mask: 255.255.255.0 (2 Replies)
Discussion started by: gnulyn
2 Replies

5. Shell Programming and Scripting

Sed; insert text two lines above match

Hi! Considering below text, how would I use sed to insert text right below the v0005-line, using the SEPARATOR-line as a pattern to search for, so two lines above the separator? I can do it right above the separator, but not 2 lines... # v0004 - Some text # v0005 - More text #... (5 Replies)
Discussion started by: indo1144
5 Replies

6. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

7. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

8. Shell Programming and Scripting

Match on columns and replace other columns

Hi Friends, I have the following input file cat input chr1 100 200 0.1 0.2 na 1 na nd chr1 105 200 0.1 0.2 1 1 na 98 chr1 110 290 nf 1 na nd na 1 chr2 130 150 12 3 na 1 na 1 chr3 450 600 nf nf na 10 na nd chr4 300 330 1 1 10 11 23 34 My requirement is 1. If $6 is na make $7 nd and... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

9. Shell Programming and Scripting

Match and insert in a sorted list

I have a sorted list (python) and I want to insert a string if it matches the pattern in list. Example : Sorted List Above list is in sorted order. I need to insert a name in sorted order and also if the name already exist then it should be inserted before the existing... (1 Reply)
Discussion started by: pratapsingh
1 Replies

10. Shell Programming and Scripting

Insert text file only after the first match with SED

Hello, I'm new in Shell scripting but i should write a script, which inserts the license header out of a txt-File into the files in our Projekt. For the Java classes it runs without Problems but for XML files not. At xml-files i have to put the license Header after the xml-Header (?xml... (1 Reply)
Discussion started by: PhoenixONE
1 Replies
Login or Register to Ask a Question