3

I'd like to join two files in bash using a common column. I want to retain both all pairable and unpairable lines from both files. Unfortunately using join I could save unpairable fields from only one file, eg. join -1 1 -2 2 -a1 -t" ".
I'd also want to retain all pairings for repeated entries (in join column) from both files. I.e. If file1 is
x id1 a b
x id1 c d
x id1 d f
x id2 c x
x id3 f v

and second file is

id1 df cf
id1 ds dg
id2 cv df
id2 as ds
id3 cf cg

the resulting file should be:

x id1 a b df cf
x id1 a b ds dg
x id1 c d df cf
x id1 c d ds dg
x id1 d f df cf
x id1 d f ds dg
x id2 c x cv df
x id2 c x as ds
x id3 f v cf cg

That's why I've always using SAS to make such join, after sorting appropriate columns.

data x;
merge file1 file2;
by common_column;
run;

It works fine but
1. as I use Ubuntu for most time I have to switch to Windows to merge data in SAS.
2. most importantly, SAS can truncate too long data entries.

That's why I'd prefer to join my files in bash, but I don't know appropriate command.
Could someone help me, or direct me to appropriate resource?

boczniak767
  • 315
  • 3
  • 10
  • 2
    SAS has a maximum variable length of 32767 chars. So if you are experiencing truncation issues and your files are not wider than that then something else is going wrong. You may possibly need to add the `lrecl=32767` statement to your filename or infile statement. – Robert Penridge Jan 12 '12 at 17:27
  • I've used import wizard, there are field in which I can specify number of "guessing rows", SAS during import scans this rows and assign variables' length according to the longest record found. I'll try import using code and your suggestion. – boczniak767 Jan 12 '12 at 21:30

2 Answers2

4

According to join's man page, -a <filenum> retains all unpairable lines from file <filenum> (1 or 2). So, just add -a1 -a2 to your command line and you should be done. For example:

# cat a
1 blah
2 foo

# cat b
2 bar
3 baz

# join -1 1 -2 1 -t" " a b
2 foo bar

# join -1 1 -2 1 -t" " -a1 a b
1 blah
2 foo bar

# join -1 1 -2 1 -t" " -a2 a b
2 foo bar
3 baz

# join -1 1 -2 1 -t" " -a1 -a2 a b
1 blah
2 foo bar
3 baz

Is this what you were looking for?

Edit:

Since you provided more detail, here is how to produce your desired output (note that my file a is your first file and my file b your second file. I had to reverse -1 1 -2 2 to -1 2 -2 1 to join on the id). I added a field list to format the output as well - note that '0' is the join field in it:

# join -1 2 -2 1 -o 1.1,0,1.3,1.4,2.2,2.3 a b

produces what you've given. Add -a1 -a2 to retain unpairable lines from both files you then get two more lines (you can guess my test data from them):

x id4 u t
 id5   ui oi

Which is rather unreadable since any left out field is just a space. So let's replace them with a '-', leading to:

# join -1 2 -2 1 -a1 -a2 -e- -o 1.1,0,1.3,1.4,2.2,2.3 a b
x id1 a b df cf
x id1 a b ds dg
x id1 c d df cf
x id1 c d ds dg
x id1 d f df cf
x id1 d f ds dg
x id2 c x cv df
x id2 c x as ds
x id3 f v cf cg
x id4 u t - -
- id5 - - ui oi
firefrorefiddle
  • 3,795
  • 20
  • 31
  • It's almost perfect solution. Almost - because requires specification of columns. My real files have 15 and 4 columns, respectively. So specification of that is not very convenient. But works, so I accept your response - Thanks. – boczniak767 Jan 12 '12 at 23:27
1

If join command is not powerful enough I usually use sqlite if I need to perform such operations in shell.

You can easily import flat files to tables, then do SQL SELECT with proper JOIN.

Note, that with sqlite, you can utilize index to make the join even faster.

sqlite3 << EOF!
CREATE TABLE my table1 (.... -- define your table here
CREATE TABLE my table2 (.... -- define your table here
.separator "," -- define input field separator here if needed
.import input_file.txt mytable1
.import input_file.txt mytable2
SELECT ... JOIN ...
EOF!

sqlite is free and mutiplatform. Very handy.

Michał Šrajer
  • 30,364
  • 7
  • 62
  • 85