Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help joining two files with a common string

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help joining two files with a common string
# 1  
Old 02-02-2011
Need help joining two files with a common string
Hi all,

I have one file that is in the form:
Code:
S0243K05_T7_S0243K05_|_BASS2243.C7_K05    groupVI.    88.76
S0137F20_SP6_S0137F20_|_BASS2137d.SPB2.2_C10    groupXXI    88.06
S0056F03_T7_S0056F03_|_BASS256c.C7_C02    groupXIX    85.99
S0056F03_T7_S0056F03_|_BASS256c.C7_C02    groupXIX    83.23

and another in the form of:
Code:
S0001A01    fps36    
S0056F03    fps427  
S0243K05    fps679
S0243L07    fps209

And what I need to do is get the 2nd column of the 2nd file added to a new column of the first file when the 1st column matches one in the first file.

So output would be like this:
Code:
S0243K05_T7_S0243K05_|_BASS2243.C7_K05    groupVI.    88.76          fps679
S0137F20_SP6_S0137F20_|_BASS2137d.SPB2.2_C10    groupXXI    88.06  fpsnnn
S0056F03_T7_S0056F03_|_BASS256c.C7_C02    groupXIX    85.99    S0056F03    fps427 
S0056F03_T7_S0056F03_|_BASS256c.C7_C02    groupXIX    83.23    S0056F03    fps427

It's a little confusing, so let me know if you need any clarification.
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 02-02-2011 at 03:16 PM.. Reason: code tags, please!
# 2  
Old 02-02-2011
Code:
nawk 'FNR==NR{f2[$1]=$2;next} $1 in f2 {print $0 OFS f2[$1]}' file2 FS='_' file1

This User Gave Thanks to vgersh99 For This Post:
repiv
# 3  
Old 02-02-2011
try:
Code:
awk -v FS="_| +" 'NR==FNR{a[$1]=$0}NR!=FNR{print $0" "(a[$1]?a[$1]:"fpsnnn")}' file2 file1

# 4  
Old 02-02-2011
Great thanks so much vgersh99! Worked perfectly, except I had to change nawk to awk, but can I get the thought process behind this script? Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Please help me in joining two files

I have two files with the below contents : sampleoutput3.txt 20150202;hostname1 20150223;hostname2 20150716;hostname3 sampleoutput1.txt hostname;packages_out_of_date;errata_out_of_date; hostname1;11;0; hostnamea;12;0; hostnameb;11;0; hostnamec;95;38; hostnamed;440;358;... (2 Replies)
Discussion started by: rahul2662
2 Replies

2. Shell Programming and Scripting

Search string within a file and list common words from the line having the search string

Hi, Need your help for this scripting issue I have. I am not really good at this, so seeking your help. I have a file looking similar to this: Hello, i am human and name=ABCD. How are you? Hello, i am human and name=PQRS. I am good. Hello, i am human and name=ABCD. Good bye. Hello, i... (12 Replies)
Discussion started by: royzlife
12 Replies

3. Shell Programming and Scripting

Help with joining files and adding headers to files

Hi, I have about 20 tab delimited text files that have non sequential numbering such as: UCD2.summary.txt UCD45.summary.txt UCD56.summery.txt The first column of each file has the same number of lines and content. The next 2 column have data points: i.e UCD2.summary.txt: a 8.9 ... (8 Replies)
Discussion started by: rrdavis
8 Replies

4. Shell Programming and Scripting

Joining Three Files

Hi guys, I have three files which needs to be joined to a single file. File 1: Col a, Col b, Col c File 2: Col 1a, Col 1b File 3: Col 2a, Col 2b Output: Col 1a, Col 2a, Col a, Col b, Col c. All the files are comma delimited. I need to join Col b with Col 1b and need to... (17 Replies)
Discussion started by: mac4rfree
17 Replies

5. UNIX for Dummies Questions & Answers

Combine multiple files with common string into one new file.

I need to compile a large amount of data with a common string from individual text files throughout many directories. An example data file is below. I want to search for the following string, "cc_sectors_1" and combine all the data from each file which contains this string, into one new... (2 Replies)
Discussion started by: GradStudent2010
2 Replies

6. Shell Programming and Scripting

joining two or more files

i have three files file a has contents 123 234 238 file b has contents 189 567 567 and file c has contents qwe ert ery (1 Reply)
Discussion started by: tomjones
1 Replies

7. UNIX for Dummies Questions & Answers

Joining string on multiple files

Hi guys, I am a forum (and a bit of a unix) newbie, and I currently have a tricky problem lying ahead of me. I have multiple files, and I am looking to join the files on the first column. Example: File 1 andy b 100 amy c 200 amy d 300 File 2 andy c 200 amy c 100 clyde o 50 ... (3 Replies)
Discussion started by: jdr0317
3 Replies

8. Shell Programming and Scripting

Joining string arguments

Hi, how can I join given arguments (not starting from the first one) to form one string, each argument separated by a space. For example, out of 5 given arguments, I'll like to start joining from the 3rd to the last. In python there exists something like ' '.join(sys.argv) and it starts joining... (5 Replies)
Discussion started by: plhelpme
5 Replies

9. Shell Programming and Scripting

How to strip out common terms in string

Hi, I have this kinda of data:- 0,0,0,0,1,2,0,4,5,6,7,foo 0,0,0,0,1,4,0,5,5,5,5,foo1 0,0,6,0,1,6,0,6,1,2,3,orange etc... I wanted to remove the 0 which occur on the same rows of foo,foo1 and orange in this case. Desired output is:- 0,1,2,4,5,6,7,foo 0,1,4,5,5,5,5,foo1... (9 Replies)
Discussion started by: ahjiefreak
9 Replies

10. UNIX for Dummies Questions & Answers

joining files

Hi, Could anyone help me ? I'm trying to join two files, but no common field are on them. So I think on generate \000\ sequence to add for each line on both files, so then will be able to join these files. Any idea? Thanks in advance, (2 Replies)
Discussion started by: Manu
2 Replies
Login or Register to Ask a Question