Question: combine columns from multiple files
0
gravatar for bk11
2.2 years ago by
bk1140
bk1140 wrote:

I have more than 100 tab delimited files with seven different columns in each files. I was to get a single file with first and 7th column of first file and seventh columns of every other files. Any help will be appreciated.

file1:

  A     B   C   D   E   F   G
gene1   6   12  3   4   0   1
gene2   8   0   2   5   10  3
gene3   9   1   1   6   11  5
gene4   10  3   0   7   2   7

file 2:

  A     H   I   J   K   K   M
gene1   0   2   1   2   4   2
gene2   10  3   5   5   7   4
gene3   20  40  7   0   9   6
gene4   1   25  9   2   10  7

file3:

file4: so on

I want to get output like this:

  A     G   M
gene1   1   2
gene2   3   4
gene3   5   6
gene4   7   7
bash • 708 views
ADD COMMENTlink modified 13 months ago by rse90 • written 2.2 years ago by bk1140

Please edit your question and explain how this is related to bioinformatics. If not, the post will be closed as off-topic.

ADD REPLYlink written 2.2 years ago by _r_am32k

If the first column of the input files are not same, paste command will give wrong results

ADD REPLYlink written 13 months ago by rse90

This comment is not an answer by itself, but a comment on an existing answer. I've moved it to a comment on the top level post.

The question clearly shows that all files share the same first column. If that were not to be the case, OP would state that.

ADD REPLYlink written 13 months ago by _r_am32k
3
gravatar for ATpoint
2.2 years ago by
ATpoint44k
ATpoint44k wrote:

paste <(cut -f1,7 file1) <(cut -f7 file2)

Assuming all files end on .txt using GNU parallel:

paste <(cut -f1,7 file1.txt) <(tr "\n" "\t" < <(ls *.txt | grep -v 'file1.txt' | sort -V | parallel -k "cut -f7 {}"))

or with a for loop:

paste <(cut -f1,7 file1.txt) <(tr "\n" "\t" < <(for i in `ls *.txt | sort -V`; do if [[ $i == "file1.txt" ]]; then continue; fi; cut -f7 $i; done))
ADD COMMENTlink modified 2.2 years ago • written 2.2 years ago by ATpoint44k

I not only have two files. I have 100 such files.

ADD REPLYlink modified 2.2 years ago • written 2.2 years ago by bk1140

I edited my post. See if it works for you.

ADD REPLYlink modified 2.2 years ago • written 2.2 years ago by ATpoint44k

Perhaps worth noting that you can paste as many columns as you want (up to the limit on command line length), e.g.:

$ paste <(cut -f1,7 file1) <(cut -f7 file2) <(...) <(cut -f7 fileN) > answer.txt

To do this programmatically, you might use a script to generate the <(cut -f7 fileX) statements for files 2 through N.

ADD REPLYlink modified 2.2 years ago • written 2.2 years ago by Alex Reynolds31k
0
gravatar for cpad0112
2.2 years ago by
cpad011214k
Hyderabad India
cpad011214k wrote:

with datamash (present in most of the distro repos): (note: Do not output to a .txt file)

output;

$ paste *.txt | datamash transpose | awk 'NR==1{print};NR%7==0{print}' | datamash transpose
A   G   M
gene1   1   2
gene2   3   4
gene3   5   6
gene4   7   7

or

$ cut -f1,7 *.txt | datamash  -sg 1 collapse 2  | tr -s "," "\t"

A   G   M
gene1   1   2
gene2   3   4
gene3   5   6
gene4   7   7

input:

$ tail -n+2 *.txt
==> file1.txt <==
gene1   6   12  3   4   0   1
gene2   8   0   2   5   10  3
gene3   9   1   1   6   11  5
gene4   10  3   0   7   2   7

==> file2.txt <==
gene1   0   2   1   2   4   2
gene2   10  3   5   5   7   4
gene3   20  40  7   0   9   6
gene4   1   25  9   2   10  7
ADD COMMENTlink modified 2.2 years ago • written 2.2 years ago by cpad011214k
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: 1772 users visited in the last hour