4

My question is similar to this one: Merge multiple columns based on the first column values

I have multiple files (10+) that I want to merge/join into one output file, for example:

file 1

2000 0.0202094
2001 0.0225532
2002 0.02553
2003 0.0261099
2006 0.028843

file 2

2000 0.0343179
2003 0.039579
2004 0.0412106
2006 0.041264

file 3

2001 0.03
2004 0.068689
2006 0.0645474

All files have the same two columns and are of unequal length. Where there is no entry for the columns (missing in one or several files), I would want a 1. If there is no entry in any file (like 2005) I don't want any output.

The desired output would be:

        file1       file2       file3
2000    0.0202094   0.0343179   1
2001    0.0225532   1           0.03
2002    0.02553     1           1
2003    0.0261099   0.0395799   1
2004    1           0.0412106   0.0686893
2006    0.028843    0.041264    0.0645474

I have tried to modify the awk code provided by the answer of this other question, but I feel like it could not be possible with that solution.

Improve this question

2 Answers 2

Reset to default
5

Use join:

join -a1 -a2 -e 1 -o auto <(join -a1 -a2 -e 1 -o auto file1 file2) file3

see in the man join

-a FILENUM
also print unpairable lines from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2

-e EMPTY
replace missing input fields with EMPTY

-o FORMAT
obey FORMAT while constructing output line

If FORMAT is the keyword 'auto', then the first line of each file determines the number of fields output for each line.

Note: join require sorted input, so if those are not sorted (which they are in given samples), sort them first, like:

join -a1 -a2 -e 1 -o auto \
    <(join -a1 -a2 -e 1 -o auto <(sort file1) <(sort file2)) \
    <(sort file3)

To apply this on multiple files:

  1. join first two files and save output to third file say join.tmp:

    join -a1 -a2 -e 1 -o auto file1 file2 >join.tmp

  2. next loop over the rest of the files and update join.tmp file for every run:

    for file in rest_files*; do
    join -a1 -a2 -e 1 -o auto join.tmp "$file" >join.tmp.1
    mv join.tmp.1 join.tmp
done

  3. at the end your join.tmp would be your final joined result.

print with header:

$ hdr() { awk 'FNR==1{ print "\0", FILENAME }1' "$1"; }
$ join -a1 -a2 -e 1 -o auto \
      <(join -a1 -a2 -e 1 -o auto <( hdr file1) <(hdr file2)) \
      <(hdr file3) |tr -d '\0'

for the multiple files version:

$ hdr() { awk 'FNR==1{ print "\0", FILENAME }1' "$1"; }
$ join -a1 -a2 -e 1 -o auto <(hdr file1) <(hdr file2) >join.tmp
$ for file in rest_files*; do
     join -a1 -a2 -e 1 -o auto join.tmp <(hdr "$file") >join.tmp.1
     mv join.tmp.1 join.tmp
  done
$ tr -d '\0' <join.tmp >final.file
Improve this answer
4
  • Hi, although it may be late, I'd be happy to hear from you. I use this code along with filed information as join -1 3 -2 1 -a1 -a2 <(join -a1 -a2 <(hdr file1) <(hdr file2)) > merged_file. But it says that : join: missing operand after '/dev/fd/63/dev/fd/62. Could you please help me out to solve the issue? Commented Apr 13, 2022 at 11:51
  • yes, I defined hdr before running the join code, but the error still remains Commented Apr 13, 2022 at 12:20
  • Thanks, I try it. So, in the code, join -1 3 -2 1 -a1 -a2 <(join -a1 -a2 <(hdr file1) <(hdr file2)) > merged_file, ">" for output is wrong? Commented Apr 14, 2022 at 15:03
  • @Mary, that is wrong in your code, not the code in the answer. Commented Apr 14, 2022 at 15:16
2

A bit clumsy, but this awk-code works. It uses the option of pseudo-multidimensional arrays with the SUBSEP concatenation of array indices. Saves all data in RAM, so limited in that case.

{x[$1]=$1 ; file[FILENAME]=FILENAME ; y[$1,FILENAME]=$2}

END { for (i in file) { printf "\t%s",file[i] } ; printf "\n",""
      for (i in x) { printf "%s",x[i]
        for (j in file) { if (y[x[i],file[j]] != "")
                             { printf"\t%s",y[x[i],file[j]] }
                          else { printf"\t%s","1"}
        }
        printf "\n",""
      }
    }

Output is tab-delimited only, for fixed formats, the printf-commands would need to be adjusted accordingly:

    file1   file2   file3
2000    0.0202094   0.0343179   1
2001    0.0225532   1   0.03
2002    0.02553 1   1
2003    0.0261099   0.039579    1
2004    1   0.0412106   0.068689
2006    0.028843    0.041264    0.0645474
Improve this answer
3
  • This looks fine to me, I leave this open to maybe get a less RAM heavy solution (number of rows is in the milions and up to 300 files). Thank you! Commented Mar 10, 2021 at 10:03
  • Can we force this order? sometimes e.g. file 3 would be first Commented Mar 17, 2021 at 10:29
  • @pascal Well with GNU's gawk, you could set how arrays are traversed. E.g. in dictionary order of indices by adding PROCINFO["sorted_in"]="@ind_str_asc" as first command in the END block - this would make the filenames sorted alphabetically. See: Predefined array scanning orders Commented Mar 17, 2021 at 14:21

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.