Merging 3 files based on first column

Question

[rse]

Hi,

How can i merge 3 files based on the text input in the first column.

For e.g.

File 1:

A 1
B 2
C 5

File 2:

A 3
B 3
D 4

File 3:

A 6
E 5

Output :

A 1 3 6
B 2 3 -
C 5 - -
D - 4 -
E - - 5

Thanks

[Kevin Blighe]

What is this for? This forum is for bioinformatics-related topics.

[rse]

Yes, this is for bioinformatics data analysis. I want to create matrix from 3 lists based on 1st column value. It doesn't display properly here. I use awk and join commands

[h.mon]

It doesn't display properly here.

Use the code button (the 101010 button) to format your post. I've formatted file1 to help get you started.

[rse]

Thank you for formatting :)

[Pierre Lindenbaum]

highly similar to Combining files based on chromosome and position next to each other - column vise

[rse]

Yes, it is similar. But the chromosome and position can be combined using bedtools (more easier) but the text cannot.

Answer 1

[shenwei356]

# converting to tab-delimited
$ ls *.txt | parallel 'csvtk space2tab {} > {.}.tsv'

# all possible values in 1th column
$ cut -f 1 *.tsv | sort | uniq | tee 0.tsv

# join together
$ csvtk join -H -t -k -t *.tsv --fill -
A       1       3       6
B       2       3       -
C       5       -       -
D       -       4       -
E       -       -       5

[rse]

Thank you for your answer :) Will try it