Home / ubuntu / Questions / 1086568
In Process
Negar
Asked: 2018-10-24 16:34:56 +0800 CST

Merge expressions with a one-to-one join

  • 772

I have two text tables as below:
file1.txt

1 A a 
2 B b 
3 C c 
4 D d

file2.txt 

A x i 
C z iii 
D w iiii 
B y ii

I want to have
final.txt 

1 A a x i 
2 B b y ii 
3 C c z iii 
4 D d w iiii
command-line text-processing

2 Answers

  1. $ join  -12 -21 -o 1.1,1.2,1.3,2.2,2.3 <(sort file1.txt) <(sort file2.txt) > final.txt
1 A a x i
2 B b y ii
3 C c z iii
4 D d w iiii

    • join -12 = file 1 column 2 (ABCD), -21 = file 2 column 1 (ABCD)

    • -o = output, 2.3 = file 2, column 3. 

    $ paste -d' ' <(sort file1.txt) <(sort file2.txt) | awk -F ' ' '{print $1, $2, $3, $5, $6}' > final.txt
1 A a x i
2 B b y ii
3 C c z iii
4 D d w iiii
    • 1

  2. This can be scripted in Python:

    #!/usr/bin/env python3
import sys

with open(sys.argv[1]) as f1,open(sys.argv[2]) as f2:
    for f1_line in f1:
        f2_line = f2.readline()
        print(" ".join([f1_line.strip(),f2_line.strip()]))

    Works as so:

    $ ./merge.py file1.txt file2.txt 
1 A a A x i
2 B b C z iii
3 C c D w iiii
4 D d B y ii

    You can create final.txt with shell redirection operator ./merge.py file1.txt file2.txt > final.txt

    If you have sqlite3 installed and the two files are the same length you can script that in SQL since table field joins are exactly what SQL is designed to do:

    sqlite3 <<EOF
.mode csv
.separator " "
CREATE TABLE tb1 (col1 INT,col2 TEXT,col3 TEXT);
CREATE TABLE tb2 (col1 TEXT,col2 TEXT,col3 TEXT);
.import file1.txt tb1
.import file2.txt tb2
SELECT * FROM tb1 JOIN tb2 on tb1.rowid=tb2.rowid;
EOF

    Which works as so:

    $ bash merge.sqlite
1 A a A x i
2 B b C z iii
3 C c D w iiii
4 D d B y ii

    Note that original files shouldn't have trailing newlines, otherwise there will be errors thrown by SQLite.

    • 0