I need to find matches with the string in column 1 ($1) in file1.txt with the string in column 1 ($1) in file2.txt. Then I want to join the lines where there was a match in a new file.
cat file1.txt
1050008 5.156725968 8.404038296 124.9198605 3.23E-21 2.33E-17 38.57865782
3310747 5.631470026 8.581936875 124.6039122 3.34E-21 2.33E-17 38.55204806
5910451 4.900364671 8.455329195 124.5720603 3.35E-21 2.33E-17 38.54935989
730156 5.565210738 8.48792701 122.2168789 4.28E-21 2.33E-17 38.34773989
cat file2.txt
4230037 ILMN Controls ILMN_Controls ERCC-00071 ILMN_333646 ERCC-00071 ERCC-00071
1050008 ILMN Controls ILMN_Controls ERCC-00009 ILMN_333584 ERCC-00009 ERCC-00009
5260356 ILMN Controls ILMN_Controls ERCC-00053 ILMN_333628 ERCC-00053 ERCC-00053
3310747 ILMN Controls ILMN_Controls ERCC-00144 ILMN_333719 ERCC-00144 ERCC-00144
5910451 ILMN Controls ILMN_Controls ERCC-00003 ILMN_333578 ERCC-00003 ERCC-00003
1710435 ILMN Controls ILMN_Controls ERCC-00138 ILMN_333713 ERCC-00138 ERCC-00138
1400612 ILMN Controls ILMN_Controls ERCC-00084 ILMN_333659 ERCC-00084 ERCC-00084
730156 ILMN Controls ILMN_Controls ERCC-00017 ILMN_333592 ERCC-00017 ERCC-00017
I would like the output file to look like this:
out.txt
1050008 5.156725968 8.404038296 124.9198605 3.23E-21 2.33E-17 38.57865782 1050008 ILMN Controls ILMN_Controls ERCC-00009 ILMN_333584 ERCC-00009 ERCC-00009
3310747 5.631470026 8.581936875 124.6039122 3.34E-21 2.33E-17 38.55204806 3310747 ILMN Controls ILMN_Controls ERCC-00144 ILMN_333719 ERCC-00144 ERCC-00144
5910451 4.900364671 8.455329195 124.5720603 3.35E-21 2.33E-17 38.54935989 5910451 ILMN Controls ILMN_Controls ERCC-00003 ILMN_333578 ERCC-00003 ERCC-00003
730156 5.565210738 8.48792701 122.2168789 4.28E-21 2.33E-17 38.34773989 730156 ILMN Controls ILMN_Controls ERCC-00017 ILMN_333592 ERCC-00017 ERCC-00017
The files are tab delimited and have missing values in some columns.
There is 31 columns in file2.txt and >47000 lines and I'm trying to do this in bash (OSX)
If you have a solution I would greatly appreciate if you could briefly explainn the steps as I'm very new to this.
If you don't mind the output being ordered by the first column then you can use this invocation of the join command:
join <(sort file1.txt) <(sort file2.txt) >out.txt
awk 'BEGIN {
FS = OFS = "\t"
}
NR == FNR {
# while reading the 1st file
# store its records in the array f
f[$1] = $0
next
}
$1 in f {
# when match is found
# print all values
print f[$1], $0
}' file1 file2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With