I want to merge two tables which contain identical row headers A, B, etc, and a number of column headers a, b, etc. that overlap only partially. I need to merge the tables so that matching columns are merged, but the rows are added.
Example:
Table 1
a b c d
A 1 NA 3 2
B NA NA 1 3
C 2 3 NA NA
D NA 5 NA 1
Table 2
a d e f
A NA 1 3 NA
B NA 1 NA 2
C 4 NA 3 NA
D 1 NA NA 2
resulting table
a b c d e f
A 1 NA 3 2 NA NA
A NA NA NA 1 3 NA
B NA NA 1 3 NA NA
B NA NA NA 1 NA 2
C 2 3 NA NA NA NA
C 4 NA NA NA 3 NA
D NA 5 NA 1 NA NA
D 1 NA NA NA NA 2
The columns and rows do not need to be sorted in any special order. I played around with the join command, but it requires sorted files, which my data is somehow not. When I try a variant of
join <(sort file1.txt) <(sort file2.txt)
I get different results where some part (or all) of the data is removed. The NA strings can be replaced by some other placeholder if that makes the task easier.