merge multiple txt files

Question

I have multiple txt files, 3 of them are like:

file1:

sample  input filtered
5809378   1      2
5811151   3      4
5811237   5      6

file2:

sample  chi tri
5809378  7   8
5811151      
5811237  9   10

file3:

sample   bra  doe
5809378  11 
5811151        12
5811237  13    14

I want to merge these 3 files into 1 based on the first column: sample ID, so the output would look like:

sample  input  filters  chi  tri   bra   doe
5809378    1     2      7     8     11     0
5811151    3     4      0     0     0     12
5811237    5     6      9     10    13    14

Notice that if there is no Corresponding data, there must be a zero, or in the worst case, an empty tab.

I have tried awk and join, and couldn't;t find a best solution. Anyone has any idea?

Answer 1

The file3 is not totally correct from my point of view, because line

5811151 12

may have number "12" in second or third column, depending on how we read the file (a columns separator is not defined and not the same everywhere).

Anyway.

a=$(cat file1|awk '{if($2==""){$2="0"};if($3==""){$3="0"}; print $1,$2,$3}'|sort);
for f in file2 file3; do
    b=$(cat $f|awk '{if($2==""){$2="0"};if($3==""){$3="0"}; print $1,$2,$3}'|sort);
    a=$(join -j 1 <(echo "${a}") <(echo "${b}"));
done;
echo "${a}"|sort -n

output is

sample input filtered chi tri bra doe
5809378 1 2 7 8 11 0
5811151 3 4 0 0 12 0
5811237 5 6 9 10 13 14

So, we

1) Each taken file transformed

cat file|awk '{if($2==""){$2="0"};if($3==""){$3="0"}; print $1,$2,$3}'|sort

to replace missing numbers with '0' and sort lines.

2) In a cycle we take a next file and merge it to previous result

join -j 1 file_current file_next

so the line "for f in file2 file3; do" may be changed to include more files, for example "for f in file2 file3 file4 file5 file6; do".

3) Print the result, sorted according to string numerical value (to order and to print column names first). Also we can format the output here if needed.

Answer 2

Assuming your files have tab-separated columns (So you can tell which columns are empty in cases like the third line of your file3), and are sorted on the first column like your samples, a bash script like so:

#!/bin/bash

function fixup() { # Add 0's to blank columns
    awk -v cols="$2" 'BEGIN { FS = OFS = "\t" }
                      { for (i = 1; i <= cols; i++)
                         if ($i == "") $i = 0
                      } 1' "$1"
}

join --header -t$'\t' -j1 \
     <(join --header -t$'\t' -j1 <(fixup "$1" 3) \
                                 <(fixup "$2" 3)) \
     <(fixup "$3" 3)

will do it:

$ ./combine file1 file2 file3
sample  input   filtered        chi     tri     bra     doe
5809378 1       2       7       8       11      0
5811151 3       4       0       0       0       12
5811237 5       6       9       10      13      14

(Does require the GNU coreutils version of join).

Answer 3

Assuming that your starting files are tab delimited, and that the empty fields are still tab separated, you could zero fill the missing columns with awk, like:

awk -F'     ' '!$2{$2=0}!$3{$3=0}{print}' file1 > file1-n
awk -F'     ' '!$2{$2=0}!$3{$3=0}{print}' file2 > file3-n
awk -F'     ' '!$2{$2=0}!$3{$3=0}{print}' file3 > file3-n

That's awk -F '<TAB>', to be clear. Then you could use paste to merge them, with another awk to filter the unneeded columns:

bash-$ paste file1-n file2-n file3-n | awk {'print $1, $2, $3, $5, $6, $8, $9'}
sample input filtered chi tri bra doe
5809378 1 2 7 8 11 0
5811151 3 4 0 0 0 12
5811237 5 6 9 10 13 14

Or space the columns if human readability is important:

bash-$ paste file1-n file2-n file3-n | awk {'printf "%-7s %-5s %-8s %-3s %-3s %-3s %-3s\n", $1, $2, $3, $5, $6, $8, $9'}
sample  input filtered chi tri bra doe
5809378 1     2        7   8   11  0
5811151 3     4        0   0   0   12
5811237 5     6        9   10  13  14

Answer 4

An alternative, assuming a tab delimiter

First fix the files by inserting a 0 between double tabs or where the line ends $ without either text (i.e. it's a title) or a number [^[:alnum:]]

TAB=$'\t'; sed -Ei "s/([^[:alnum:]]|${TAB})($|${TAB})/\10\2/g" file*

Then just join them

join --header file2 file3 | join --header file1 - | column -t

Output

sample   input  filtered  chi  tri  bra  doe
5809378  1      2         7    8    11   0
5811151  3      4         0    0    0    12
5811237  5      6         9    10   13   14