Home / ubuntu / Questions / 511785
Accepted
Roboman1723
Asked: 2014-08-16 07:00:28 +0800 CST

Can't figure out JOIN command

  • 772

I have two files and I'm trying to join them at a certain spot. I'd like to join them at the fourth column of the second file using the first column of the first. It's driving me nuts!

Here is what I'm trying:

join -j4 <(sort -k1 FirstFile.txt) <(sort -k4 SecondFile.txt)

FirstFile.txt:

24.136.152.171 US
24.136.152.171 US
24.136.152.171 US

SecondFile.txt

2014-08-03 00:00:00 User 24.136.152.171
2014-08-03 00:00:00 User 24.136.152.171
2014-08-03 00:00:00 User 24.136.152.171

Desired Output:

2014-08-03 00:00:00 User 24.136.152.171 US
2014-08-03 00:00:00 User 24.136.152.171 US
2014-08-03 00:00:00 User 24.136.152.171 US
12.04

2 Answers

  1. Best Answer

    The default output format of join is to print the join field, then remaining fields from FILE1 and then the remaining fields from FILE2, unless the format is specified with -o. Further, the option -j4 means that join field is the 4th field in both FILE1 and FILE2. So you need to split -j4 to -1 1 -2 4 .

    Try this:

    join -o '2.1 2.2 2.3 2.4 1.2' -2 4 -1 1 <(sort -k1 FirstFile.txt) <(sort -k4 SecondFile.txt)
    • 2

  2. You could us python.

    Save the following in a file called join.py in your home area:

    ffile=open('FirstFile.txt','r').read().split('\n')       # Open the first file, read it and split it into a list at the newline character
sfile=open('SecondFile.txt','r').read().split('\n')      # Open the second file, read it and split it into a list at the newline character
minlen=min(len(ffile),len(sfile))                        # Get the lengths of both, and return the minimum so it doesn't break if they are different lengths.
ofile = [] # Create an empty list.    

for i in range (minlen):                                 # Loop for the length of the shortest list.
    ofile = ofile + [ffile[i]+sfile[i]]                  # Add the first item of the first list (the first line of the first file) to the first item of the second list (the first line of the second file).

outfile=open('outputfile','w')                           # Create an output file, called outputfile.txt in your home directory

outfile.write('\n'.join(ofile))                          # Write to the output file.

    then run it with 

    python join.py
    • 0