I don't have much experience in awk, so I struggle to overcome one problem with it. I have two files named file1.txt and file2.txt. File1.txt:

20 101 1 2 3 4
20 102 5 6 7 8
20 108 3 3 3 3


20 100 99 99 99 99
20 101 11 22 33 44
20 103 55 66 77 88

There are always 4 values after the first two columns in each of the files.

What I'm trying to do is to merge these files into one. I'm joining them by the first and second columns.

In the resulting file there should be 10 columns. First two are the key one, the next 4 columns are values from the first file, and the last 4 are from the second one.

In the resulting file every record from the first file that doesn't have a match with the second file (and vice versa) will have additional zeros that represent the missing values.

Everything is separated by whitespace character.

The result should look like this:

20 100 0 0 0 0 99 99 99 99
20 101 1 2 3 4 11 22 33 44
20 102 5 6 7 8 0 0 0 0
20 103 0 0 0 0 55 66 77 88
20 108 3 3 3 3 0 0 0 0

This is my awk script that I modified from one that I found while searching through the web:

BEGIN {                                                                                                                
   OFS=" "
   i=$1 OFS $2 #Making key out of first and second field of the first file                    
   NR==FNR {                             
   A[i]=$0  #Saving records from first file to an array using first two columns as index                           
#Next part assumes that I'm reading file2.txt                                         
i in A {                                
   printf "%s",A[i]  #Here, I have a match with first file, and I want to print the joined record I saved from file1.txt                                                                                               
   print $3,$4,$5,$6 #In order to print a joined record, after printing record from first file, I'm printing columns from the second file                                                                                           
   delete A[i]                           

{ #Here I print records from file2.txt that don't have a match with file1.txt, and put zeroes to fill missing values
   print 0,0,0,0,$3,$4,$5,$6
END { #In the END block I'm printing everything from file1.txt that doesn't have a match and print zeroes aftewards to fill missing values
   for (i in A) {  printf "%s",A[i]; print 0,0,0,0  }                           

The result is sorted by the second column and all missing values are filled with zeroes. However, the result I'm currently getting looks like this:

20 100 0 0 0 0 99 99 99 99
11 22 33 443 4
20 103 0 0 0 0 55 66 77 88
20 108 3 3 3 3 0 0 0 0
0 0 0 0 6 7 8

Despite the fact that the file isn't sorted (I can always use sort -k 2), some of the lines are not printed the way I intended, and I can't explain why it can't normally print element of an A array. I've tried various things like temporarily changing ORS (no output at all) or using print instead of printf (the result looks strange too).

Due to lack of experience this raises some additional questions:

Is it reasonable to use awk to complete this task? I've tried using join, but eventually got stuck, because it couldn't print column with a newline character at the end. Maybe a Python script will be more useful?

Considering that I will use very large files for merging, is it reasonable to use arrays memory-wise?

Thanks in advance!

  • Please edit your question and explain how the files should be joined. I think you want to use the first and second fields as the key to join on, but it isn't very clear.
    – terdon
    Oct 29, 2021 at 17:14
  • Thanks, I've edited the question. But yes, you're completely right, the first and second fields would be the key.
    – dave10808
    Oct 29, 2021 at 17:17
  • Please explain in words the entire process. Why are there 0s being added? how do we know how many to add? Since you tell us that your script doesn't do what you need, we can't use the script to figure out your requirements, so you need to explain them. Note that you are using I in some places but i in others. That is likely the main issue.
    – terdon
    Oct 29, 2021 at 17:26
  • I've added more explanation to this question. Basically, zeroes are meant to fill missing values (e.g. file2.txt doesn't have a row with 20 102 in the first two fields, so in the result file row with those fields will look like 20 102 5 6 7 8 0 0 0 0). So, we will always add 4 zeroes, it's just a matter of whether it will be fields [3-6] or [7-10] that are filled with them. I've edited upper characters, I guess they popped up while copy-pasting, no such things in the code itself.
    – dave10808
    Oct 29, 2021 at 17:51
  • 1
    Regarding Considering that I will use very large files for merging, is it reasonable to use arrays memory-wise? - what do you mean by "very large" - 1000 lines, 1 million lines, 1MB, 1GB, 100GB, or something else? Are your files always sorted on the first 2 fields as in your example or not? Do you care about the output order and, if so, what should that be - input order or numerical ascending or something else? Please edit your question to provide all answers.
    – Ed Morton
    Oct 29, 2021 at 19:14

1 Answer 1

awk '!second { file1vals[$1 FS $2]=$0 }
      second { print (($1 FS $2 in file1vals)?file1vals[$1 FS $2]: $1 FS $2 FS "0 0 0 0") FS $3, $4, $5, $6;
               delete file1vals[$1 FS $2]
END{ for(x in file1vals) print file1vals[x], "0 0 0 0" }' file1 second=1 file2

this will work as long as there is enough memory in order to loading the first file1 into the memory.

In the the first block !second {...}, which it only runs when it's the first file and we load the file1 into an associated array on the pair of 1st &2nd columns as the keys for the array.

In the second block second {...}, which it only runs when it's the second file we print the joined lines those that have matched keys in both files, otherwise we print the keys and 0s followed by rest of the columns from file2; then we also delete the keys from the array with delete file1vals[$1 FS $2] which their keys was exist in both files.

In the last block at the END, we print the remained non-matching keys related to file1.


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.