👤
Home Man
Search
Today's Posts
Register

BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Merge input from two files into one based on conditions

Tags
awk, bash, join, linux, merging files

👤 Login to reply

 
Thread Tools Search this Thread
# 1  
Old 05-20-2015
Merge input from two files into one based on conditions

Using Linux (bash), I have two files which contain information about berries. Example:

file1.txt:
Code:
Blueberry blue 14
Raspberry red 12
Blackberry dark 4

file2.txt
Code:
Blackberry sour 4 3
Blueberry tasty 12 78
Strawberry yummy 33 88

I want to merge these two files into one. The desired result from the above would be:
Code:
Blueberry blue 14 tasty 12 78
Raspberry red 12 - - -
Blackberry dark 4 sour 4 3
Strawberry - - yummy 33 88

So the three columns of file1.txt and the four columns of file2.txt should result in a new file with six columns where all berries present in these two files only are represented with one row each. If a berry is not present in one of the two files, the corresponding entries from that file are replaced with a dash (like for Strawberry and Raspberry above).

This command is getting me somewhere close:
Code:
awk 'FNR==NR{a[$1]=$2 FS $3 FS $4;next}{ print $0, a[$1]}' file2.txt file1.txt

Result:
Code:
Blueberry blue 14 tasty 12 78
Raspberry red 12
Blackberry dark 4 sour 4 3

Only prints the berries that are present in file1.txt though and it doesn't add the dashes. Can someone help?

Thanks!
/Z
# 2  
Old 05-20-2015
You're not too far off:
Code:
awk 'FNR==NR{a[$1]=$2 FS $3 FS $4;next}{ print $0, a[$1]?a[$1]:"- -"; delete a[$1]} END {for (i in a) print i, "- -", a[i]}' file2 file1
Blueberry blue 14 tasty 12 78
Raspberry red 12 - -
Blackberry dark 4 sour 4 3
Strawberry - - yummy 33 88

The Following User Says Thank You to RudiC For This Useful Post:
Zooma (05-20-2015)
# 3  
Old 05-20-2015
Thanks RudiC! Just get a minor error (also added one missing dash in your command):
Code:
$ awk 'FNR==NR{a[$1]=$2 FS $3 FS $4;next}{ print $0, a[$1]?a[$1]:"- - -"; delete a[$1]} END {for (i in a) print i, "- -", a[i]}' file2.txt file1.txt
Blueberry blue 14 tasty 12 78
Raspberry red 12 - - -
Blackberry dark 4 sour 4 3
 - - -
Strawberry - - yummy 33 88

So there's an extra "- - -" there that I can't explain. Bonus question: if I don't know how many columns file1 and file2 have, is there some way of making this command more dynamic?

Thanks!
/Z
# 4  
Old 05-20-2015
If you sort your files you can then use join:

Code:
$ join -j 1 -e "-" -a 1 -a 2 -o 0,1.2,1.3,2.2,2.3,2.4 file1.sor file2.sor
Blackberry dark 4 sour 4 3
Blueberry blue 14 tasty 12 78
Raspberry red 12 - - -
Strawberry - - yummy 33 88

The Following User Says Thank You to Chubler_XL For This Useful Post:
Zooma (05-21-2015)
# 5  
Old 05-21-2015
Dynamic case:
Code:
awk '
FNR==NR         {a[$1]=$2 
                 d="-"
                 for (i=3; i<=NF; i++) {a[$1]=a[$1] FS $i; d=d FS "-"}
                 next
                }
                {print $0, a[$1]?a[$1]:d
                 D="-"
                 for (i=3; i<=NF; i++) D=D FS "-"
                 delete a[$1]
                }
END             {for (i in a) print i, D, a[i]
                }
        ' file2 file1

That extra line doesn't appear when I try it...
The Following User Says Thank You to RudiC For This Useful Post:
Zooma (05-21-2015)
# 6  
Old 05-21-2015
Thanks a lot RudiC. #5 was just what I was looking for. :-)
👤 Login to reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
awk - Merge two files based on one key Ads89 Shell Programming and Scripting 3 08-10-2016 09:20 AM
Need script for making files based on some conditions. ROCK_PLSQL Shell Programming and Scripting 26 05-19-2015 10:10 AM
Merge two input files and concatenate the rest jabriel Shell Programming and Scripting 3 11-01-2014 11:57 AM
Merge files based on columns vinus Shell Programming and Scripting 4 07-11-2014 04:55 PM
Read input files and merge them in given order and write them to input one param or one file hyd1234 Shell Programming and Scripting 4 02-11-2014 10:59 AM
Merge files based on the column value i150371485 Shell Programming and Scripting 5 01-09-2013 04:26 AM
awk merging files based on 2 complex conditions ruby_sgp Shell Programming and Scripting 4 04-06-2010 04:15 AM
any script for joining files based on simple conditions stateperl UNIX for Dummies Questions & Answers 0 03-26-2010 07:12 PM
Merge Two Files based on First column apjneeraj Shell Programming and Scripting 10 04-13-2009 12:44 AM
Merge files based on key sbasetty Shell Programming and Scripting 3 02-02-2007 05:05 PM


All times are GMT -4. The time now is 11:04 PM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password





Not a Forum Member?
Forgot Password?