Question: Merging 3 files based on first column
0
gravatar for rse
8 months ago by
rse90
Singapore
rse90 wrote:

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 • 241 views
ADD COMMENTlink modified 8 months ago by shenwei3565.2k • written 8 months ago by rse90

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

ADD REPLYlink written 8 months ago by Kevin Blighe63k

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

ADD REPLYlink modified 8 months ago • written 8 months ago by rse90

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.

ADD REPLYlink modified 8 months ago • written 8 months ago by h.mon30k

Thank you for formatting :)

ADD REPLYlink written 8 months ago by rse90

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

ADD REPLYlink written 8 months ago by Pierre Lindenbaum129k

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

ADD REPLYlink written 8 months ago by rse90
1
gravatar for shenwei356
8 months ago by
shenwei3565.2k
China
shenwei3565.2k wrote:
# 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
ADD COMMENTlink modified 8 months ago • written 8 months ago by shenwei3565.2k

Thank you for your answer :) Will try it

ADD REPLYlink written 8 months ago by rse90
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1403 users visited in the last hour