This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Merging 3 files based on first column

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

next-gen
• 3,771 views • link
updated by shenwei356 shenwei356 • written by rse rse

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

• 0 views • link
written by Kevin Blighe Kevin Blighe

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

• 0 views • link
written by rse rse

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.

• 0 views • link
written by h.mon h.mon

Thank you for formatting :)

• 0 views • link
written by rse rse

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

• 0 views • link
written by rse rse

1 answer 

# 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
• 0 views • link
written by shenwei356 shenwei356

Thank you for your answer :) Will try it

• 0 views • link
written by rse rse

Log in to answer this question.

More posts like this