Collecting columns from multiple files into one file

Question

[S.O.T.AL-HASHIMI2]

Dear all,

I hope you are all doing well. I'm new to bioinformatics and would be grateful if you could help me with the below issue.

I have 156 files named with sample_1_TEcounts.tsv, sample_2_TEcounts.tsv, ... and contain information as in the photo below (I showed it as Excel to make it clear but it is saved as tab-delimited files)

I'm only interested in the fifth column (the fpkm column). Is it possible to extract that column from every single file and collect them in one .tsv text file? I have tried to copy-paste it but with 156 files, it is a huge waste of time.

Many thanks for your help
Surar

[GenoMax]

For inspiration:

https://stackoverflow.com/questions/26081970/cut-column-from-multiple-text-files-into-new-file
https://stackoverflow.com/questions/59112724/extract-specific-column-from-files-of-a-directory-into-a-new-file
https://unix.stackexchange.com/questions/89999/how-to-select-copy-and-paste-some-columns-from-different-files-and-create-one-f

Following will lose track of where the data came from so use one of the more elegant solutions in links above but if you are ok with it use:

cut -f5 file1 file2 .. file156 > final.txt

[S.O.T.AL-HASHIMI2]

Thank you very much for sharing these links. I just want to combine the columns with no need to track the file so I have tried the suggestion by Andrés Ribone and it worked fine.

Answer 1

[Andrés Ribone]

Hi,

Do you want every original column as a separate column forming a matrix, correct?

If so, ¿Do all files have the same amount of lines, and in the same order?

For example, ¿do all the third lines correspond to the position chr1:632555-632703?

If so, you can do it in bash like this:

for i in sample_*_TEcounts.tsv; do
cut -f 5 $i > delete.$i
done
paste delete.test* > result.tsv
rm delete.*

If the number or order of lines differ, you will probably need to do it in R or python, let me know and I'll help you with that.

[S.O.T.AL-HASHIMI2]

Thanks dear Andrés, the code worked fine and I got the file now. many thanks for your help.

[GenoMax]

You can accept this answer to provide closure to this thread (green checkmark).