Question: Merging 3 files based on first column
0
gravatar for rse
13 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 • 437 views
ADD COMMENTlink modified 13 months ago by shenwei3565.7k • written 13 months ago by rse90

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

ADD REPLYlink written 13 months ago by Kevin Blighe69k

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 13 months ago • written 13 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 13 months ago • written 13 months ago by h.mon32k

Thank you for formatting :)

ADD REPLYlink written 13 months ago by rse90

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

ADD REPLYlink written 13 months ago by Pierre Lindenbaum132k

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

ADD REPLYlink written 13 months ago by rse90
1
gravatar for shenwei356
13 months ago by
shenwei3565.7k
China
shenwei3565.7k 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 13 months ago • written 13 months ago by shenwei3565.7k

Thank you for your answer :) Will try it

ADD REPLYlink written 13 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: 990 users visited in the last hour