3

Objective: Merge the contents of two files using common key present in the files

 file1.txt
 =========
 key1   11
 key2   12
 key3   13


 file2.txt
 =========
 key2   22
 key3   23
 key4   24
 key5   25


 Expected Output :
 ==================
 key1   11
 key2   12    22
 key3   13    23 
 key4   24
 key5   25

Approaches tried:

  1. join command:

    join -a 1 -a 2 file1.txt file2.txt ## full outer join
    
  2. awk:

    awk 'FNR==NR{a[$1]=$2;next;}{ print $0, a[$1]}' 2.txt 1.txt
    

Approach 2 is resulting in a right outer join and NOT a full outer join:

   key1  11
   key2  12    22
   key3  13    23 

What needs to be modified in approach 2 to result in a full outer join?

  • i need to do the full outer join on multiple csvs. My keys are sorted but values are not. I tried Approach 1 and it complained about sorting. What to do? – Abu Shoeb Mar 15 '18 at 7:05
0

With awk, try:

awk '{a[$1]=($1 in a)?a[$1]" "$2:$2};END{for(i in a)print i,a[i]}' file1 file2

For huge files, you should use join instead of awk approach, since when awk approach will store all files content in memory before printing out.

4

My solution using join:

join -a1 -a2  -1 1 -2 1 -o 0,1.2,2.2 -e "NULL" file1 file2 

I don't know much about awk for joining large files and always use join.

key1 11 NULL
key2 12 22
key3 13 23
key4 NULL 24
key5 NULL 25
  • 1
    -1 1 -2 1 is equal -j 1 – Costas Apr 8 '15 at 13:41
  • @Costas fwiw, POSIX.1-2017 states: Earlier versions of this standard allowed -j, -j1, -j2 options […]. These forms are no longer specified by POSIX.1-2017 but may be present in some implementations. – myrdd May 24 '18 at 13:00
1

My solution with awk:

awk '{a[$1]=a[$1]" "$2} END{for(i in a)print i, a[i]}' file1.txt file2.txt

With keyn as index, append the second fields from each line to corresponding a[keyn](with space). At the end, print all the indices and array element.

Output:

AMD$ awk '{a[$1]=a[$1]" "$2} END{for(i in a)print i, a[i]}' file1.txt file2.txt
key1  11
key2  12 22
key3  13 23
key4  24
key5  25
0

Your first join seems to be ok here, although it is misspelled in caps letters:

$>join -a 1 -a 2 file1.txt file2.txt 
key1 11
key2 12 22
key3 13 23
key4 24
key5 25

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.