-1

I want to join these 2 files : File 1 (1 million lines) and File 2 (10,000 lines) in new File 3 (should be 1 million lines) using an awk command

File 1 :

471808241 29164840 1 10001 156197396 
471722917 21067410 1 31001 135961856 
471941441 20774160 1 7001  180995072 
471568655 29042630 1 15001 157502996 
471524711 20716360 1 4001  180226817 
471873918 29583520 1 2001  128567298 
471568650 29042631 1 15002 157502910 

File 2

610146 156197396 
531101 135961856 
704011 180226817 
502216 128567298 
707012 180995072 
615246 157502996 
685221 157502910 

Desired output :

471808241 29164840 1 10001 156197396 610146 
471722917 21067410 1 31001 135961856 531101 
471941441 20774160 1 7001  180995072 707012 
471568655 29042630 1 15001 157502996 615246 
471524711 20716360 1 4001  180226817 704011 
471873918 29583520 1 2001  128567298 502216 
471568650 29042631 1 15002 157502910 685221
2
3

I want to join these 2 files

So use the join command after sorting the files into key order:

sort -b -k 5 file1 > sorted-file1
sort -b -k 2 file2 > sorted-file2
join -1 5 -2 2 -o 1.1,1.2,1.3,1.4,2.2,2.1 sorted-file1 sorted-file2

Further reading

  • "Utilities: join". Shell Command Language. Single UNIX Specification. Issue 7. IEEE 1003.1. 2016. The Open Group.
3
$ awk 'NR==FNR{a[$2]=$1; next} {sub(" ?$"," "a[$5]); print}' file2 file1
471808241 29164840 1 10001 156197396 610146
471722917 21067410 1 31001 135961856 531101
471941441 20774160 1 7001  180995072 707012
471568655 29042630 1 15001 157502996 615246
471524711 20716360 1 4001  180226817 704011
471873918 29583520 1 2001  128567298 502216
471568650 29042631 1 15002 157502910 685221
  • NR==FNR if current line number is same as file wise line number, this will be true only for first file argument file2
  • a[$2]=$1; next save key-value pair from file2 lines, next is used to skip rest of code
  • sub(" ?$"," "a[$5]) use substitution to replace an optional space at end of line with space and value from array a based on 5th column as key
  • print then print the modified input lines from file1
3
  • Thank you for your answer please don't consider the "=" separator , i don't have it in both two files . – Nydenn Feb 26 '17 at 12:57
  • Please how can i use this command in expect i got an error when using " character, i tried \" but it's not working ... – Nydenn Feb 27 '17 at 13:47
  • I am not able to follow, can you add sample line and command you used, or please ask a new question with all the details – Sundeep Feb 27 '17 at 15:27
0

One Million lookups out of 10,000 keys, if performance needed, use perl:

#!/usr/bin/perl

# create hash from file2
($f2) = pop @ARGV;
%hash = map { reverse split } do { local @ARGV=($f2); <>};

# loop over file1 
while ( <> ) {
   chomp;
   @F = split;
   print "$_ $hash{$F[4]}\n";
}
0
0

This was a nice little exercise!

In Python, I came up with this:

#!/usr/bin/env python3

myDict = {}

with open("file1") as f1, open("file2") as f2: 
    for line in f2: 
        (val, key) = line.split()
        myDict[key] = val 
    for line1 in f1: 
        myKey = line1.split()[4] 
        if myKey in myDict:
            print(line1.rstrip() + " " + myDict[myKey])

Result:

./comFiles.py 
471808241 29164840 1 10001 156197396 610146
471722917 21067410 1 31001 135961856 531101
471941441 20774160 1 7001  180995072 707012
471568655 29042630 1 15001 157502996 615246
471524711 20716360 1 4001  180226817 704011
471873918 29583520 1 2001  128567298 502216
471568650 29042631 1 15002 157502910 685221

Commented version of script:

#!/usr/bin/env python3

myDict = {}

# Open both files for reading
with open("file1") as f1, open("file2") as f2: 
    for line in f2: 
        # Read file2 into dictionary
        (val, key) = line.split()
        myDict[key] = val 
    for line1 in f1: 
        # Use the 5th field of file1...
        myKey = line1.split()[4] 
        if myKey in myDict: # to search for our value in our dict (file2)
            print(line1.rstrip() + " " + myDict[myKey])
0

Perl

perl -lpe '
   BEGIN{ %h = do{local(@ARGV,$/)=pop;<>} =~ s/^(\s*\d+)(\s\d+)$/$2 $1/mgr =~ //mg;
   $_ .= $h{(/(\s\d+)$/)[0]};
' File1 File2

Bash

while read -r value key; do eval "_h_$key='$value';"; done < File2
while read -r _1 _2 _3 _4 _5; do
   eval "x=\$_h_$_5"
   printf '%s ' "$_1" "$_2" "$_3" "$_4" "$_5"
   echo "$x"
done < File1

Sed

echo '.' >> File2
sed -e '
   1{
      :file2
         N
      /\n\.$/!bfile2
      H;d
   }
   G
   s/\([ ][0-9][0-9]*\)\n.*\n\([0-9][0-9]*\)\1\n.*/\1 \2/
' File2 File1

Not the answer you're looking for? Browse other questions tagged or ask your own question.