1

I am new in awk and shell, wondering how can I merge 2 files with the line that have the same record using shell/awk? file1 and file2 may have different order for the Name. I only want to merge the lines that has the same record. please help.

file1.txt
Mary 68 
Tom 50 
Jason 45
Lu 66

file2.txt
Jason 37
Tom 26
Mary 74
Tina 80

mergefile.txt
Marry 68 74
Tom 50 26
Jason 45 37 

I have a try of awk, but it takes some times to running the script. Wondering if there could be a more faster and simple implement.

cat file1.txt | while read line
do
    score1=$( echo $line | awk '{print $2}');
    name1=$( echo $line | awk '{print $1}');

    cat file2.txt | while read l
    do
        score2=$( echo $l | awk '{print $2}');
        name2=$( echo $l | awk '{print $1}');
        if [[ $name1 == $name2 ]]
        then
            echo "$name1 $score1 $score2" >> mergefile
            break
        fi
    done
done
1
6

If you want to use awk:

$ awk 'NR==FNR {a[$1] = $2; next} $1 in a {print $1, $2, a[$1]}' file2.txt file1.txt 
Mary 68 74
Tom 50 26
Jason 45 37

No sorting is required and the output will be in the order of the second file given.

Explanation:

  • NR==FNR is the canonical way to select records from the first named file
  • {a[$1] = $2; next} populate an array with keys from the first field and values from the second
  • $1 in a if the first field was already seen in the first file; then
  • {print $1, $2, a[$1]} print the key and value from the second file and the value from the first
2
  • can you explain the awk command, thanks – Tiger Mar 6 '20 at 20:14
  • 1
    @Tiger please see edit – steeldriver Mar 6 '20 at 23:18
5

This sound like a job for join, the relational database operator

join <(sort file1.txt) <(sort file2.txt)

Tests

$ cat file1.txt
Mary 68
Tom 50
Jason 45
Lu 66

$ cat file2.txt
Jason 37
Tom 26
Mary 74
Tina 80

$ join <(sort file1.txt) <(sort file2.txt)
Jason 45 37
Mary 68 74
Tom 50 26

join is standard tool specified in POSIX.

The join man page states:

The files file1 and file2 shall be ordered in the collating sequence of sort -b on the 
fields on which they shall be joined, by default the first in each line. All selected 
output shall be written in the same collating sequence.
2
  • if I want to combine 2 files together, can "join" do the work? also combine the name and scores that only appears in one file? – Tiger Mar 9 '20 at 19:06
  • @Tiger What do you mean by "combine 2 files together". See the join link in my answer to understand what its purpose. – Paulo Tomé Mar 10 '20 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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