This is a test version of Biostars. For the public version, visit https://www.biostars.org.
how to merge two files without duplicating same column

Dear all,

I am using below command to merge two files based on one of the similar column, but this command also duplicate the other common columns;

file1 <- read.table("CpG.csv", header=T, sep=",", as.is=T, na.strings="NA")
    file1[c(1:3), c(1:3)]
        Sample_ID                          PC1                         PC2
    1 NSS.1.0093                        -25382.95                     22243.17
    2 NSS.1.0095                        -29640.00                     27610.33
    3 NSS.1.0096                        -41261.36                     30188.37
     file2 <- read.table("Phe_121023.csv", header=T, sep=",", as.is=T, na.strings="NA")
    file2[c(1:3), c(1:3)]
        Sample_ID         BeacChip.ID   Sentrix_ID
    1 NSS.1.0093 200772280026_R05C01 200772280026
    2 NSS.1.0095 200772280026_R07C01 200772280026
    3 NSS.1.0096 200772280026_R08C01 200772280026
     PCs <- read.table("Control_probe_PCs_all_preprocessed.txt", header=T, sep="\t", as.is=T, na.strings="NA")
    PCs[c(1:3), c(1:3)]
        Sample_ID       PC1      PC2
    1 NSS.1.0093 -25382.95 22243.17
    2 NSS.1.0095 -29640.00 27610.33
    3 NSS.1.0096 -41261.36 30188.37

tmp=merge(file1, file2, by="Sample_ID")

write.table(tmp, file="your_merged_commonData.txt", sep="\t")
r statistics biostatistics
• 1,388 views • link
updated by DBScan • DBScan • written by Bioinfonext Bioinfonext

2 answers

Yes, indeed, when both data.frames contain the same column (or a similarly named one) then the suffix .x and .y will be added to the column name.

It is not up to R to guess which columns you would like to keep. Either you exclude them already prior to merging or you drop them later. This for example will drop the columns with .y and rename those .x:

tmp <- tmp[,!grepl("\\.y$",colnames(tmp))]
colnames(tmp) <- gsub("\\.x$","",colnames(tmp))
• 0 views • link
written by Matthias Zepper Matthias Zepper

You can use a join function from dplyr package like this:

inner_join(file1, PCs)

This would keep only sample which occur in both of your dataframes.

• 0 views • link
written by DBScan • DBScan

Log in to answer this question.

More posts like this