This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Merging same one column different files

Hello, Please I would like to merge one same column to multiple different files. Those files have the same structure but from different samples and I want to merge them with snp position column each file separately.
Is there any kind of loop (bash, R , python ...) that could do this ?

input files 

RS  1-51.Log R Ratio    1-51.B Allele Freq
A28         -0.1656                     1

column :

RS       Position
A28      5555

Output: 

RS  1-51.Log R Ratio    1-51.B Allele Freq        Position
A28         -0.1656                     1          5555

Thank you very much

merge bash
• 1,748 views • link
updated by Pierre Lindenbaum Pierre Lindenbaum • written by mel22 mel22

You should provide the first few lines in each file and an example of the desired output. It would be difficult to answer the question without this information.

• 0 views • link
written by rpolicastro rpolicastro

with tsv-utils :

input:

$ cat first_file.txt second_file.txt 

RS  Position
A28 5555
RS  1-51.Log_R_Ratio    1-51.B_Allele_Freq
A28 -0.1656 1

output:

$ tsv-join -H -f first_file.txt -k RS   --write-all  -1 -a Position second_file.txt

RS  1-51.Log_R_Ratio    1-51.B_Allele_Freq  Position
A28 -0.1656 1   5555
• 0 views • link
written by cpad0112 cpad0112

2 answers

Looks like a task for the merge function in R. See ?merge

• 0 views • link
written by Carlo Yague Carlo Yague

Adding to Carlo's point:

  1. Use list.files to get a list of file names/locations - ths will be the list of files to read.
  2. Use lapply with read.table on the above list to get a list of data.frame objects with the content of each file. You will want to uniquify column names in individual data.frames so merging would not create suffixes on similarly names columns.
  3. Use Reduce with merge to combine the list from step-2 to get a single data frame.
• 0 views • link
written by Ram Ram

Thanks for elaborating my rather blunt answer ! I just wanted to ad that by default, the merge is performed based on the the columns with identical names, so depending on the case, it might not be needed to uniquify column names.

• 0 views • link
written by Carlo Yague Carlo Yague

That is both a pro and a con. When merging multiple output files from RSEM, for example, such a merge would be faulty. IMO a merge should always be done with explicit column name specification.

• 0 views • link
written by Ram Ram 
join -t $'\t' -1 1 -2 1 <(sort -t $'\t' -k1,1 file1.tsv) <(sort -t $'\t' -k1,1 file2.tsv)
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this