This is a test version of Biostars. For the public version, visit https://www.biostars.org.
matching and merging data frames of different lengths

Hi,

I have 2 files and I want to match the first column in first file with the first column in second file then if they match, I want to pick the corresponding value in 2nd column of 2nd file and add it in a new column in the first file. The problem is, when I match my 2 files I end up with data frames of different lengths because my first file contains gene Ids multiple times but in the second file, I have the corresponding gene name which would of course just occur once. As an example,

File1:

Gene Id     value_sample1     value_sample2
x            0.0001            0.00000034

File2:

Gene Id     Gene Name
x           y

Thanks a lot!!

r
• 52,226 views • link
updated by Ram Ram • written by Diana Diana

have a look into the merge function

• 0 views • link
updated by Ram Ram • written by russhh russhh

and what do you want to have in the end, if a gene is present more than once in your first file? do you still want to get all its rows or for example only the first one?

• 0 views • link
updated by Ram Ram • written by Martombo Martombo

Yes I want to get all the rows of the gene in the first file. But add the gene name in a new column. When I match the first file with second I end up with data frames of different lengths and hence I can't cbind them. Any advice?

• 0 views • link
updated by Ram Ram • written by Diana Diana

are you using R I guess? In R you can use the command merge as suggested by @russ_hyde, as long as your data is stored in two data.frames. you can use it like: merge(data.frame1,data.frame2) if your data.frames have the gene id in the first vector (as in your example)

A very quick command with bash would be: join <(sort file1) <(sort file2) >fileOut

• 0 views • link
updated by Ram Ram • written by Martombo Martombo

2 answers

People have suggested using the merge function, and I agree. I created an example for you. You mentioned something about multiple gene IDs in the first file that were duplicates so I tried to include that in the example. If you want a different result related to which data is returned I'd check out the merge() function.

> # These commands were written in R
> #create file 1
> dat1 <- matrix(data = rnorm(12), nrow = 4, ncol = 3)
> 
> colnames(dat1) <- c("Gene_Id",     "value_sample1",     "value_sample2")
> dat1[,1] <- c("x", "y", "z", "x")
> 
> dat1
     Gene_Id value_sample1        value_sample2      
[1,] "x"     "-0.475801113106774" "0.373054164662158"
[2,] "y"     "1.28872139254973"   "0.965427944478498"
[3,] "z"     "-0.226327653738543" "0.375294465742173"
[4,] "x"     "-1.2569558077676"   "-1.58830615905416"
> 
> #create file 2
> dat2 <- matrix(data = NA, nrow = 2, ncol = 2)
> colnames(dat2) <- c("Gene_Id",     "gene_name")
> dat2[,1] <- c("x", "y")
> dat2[,2] <- c("A", "B")
> 
> dat2
     Gene_Id gene_name
[1,] "x"     "A"      
[2,] "y"     "B"      
> 
> #merge file 1 and file 2 using the "Gene_Id" column
> merge(dat1, dat2, by.x = "Gene_Id", by.y="Gene_Id")
  Gene_Id      value_sample1     value_sample2 gene_name
1       x -0.475801113106774 0.373054164662158         A
2       x   -1.2569558077676 -1.58830615905416         A
3       y   1.28872139254973 0.965427944478498         B
>
• 0 views • link
updated by Ram Ram • written by Jason Jason

Thanks a alot!!

• 0 views • link
written by Diana Diana

If your tables are pretty big, consider using the merge function in the data.table package. It's very fast even with tables of millions of records. See quick comparison with base merge:

install.packages('data.table') ## Install package
library(data.table)

## Dummy data:
dat1<- data.table(Gene_Id= 1:10000000, value.x= rnorm(n= 10000000))
dat2<- data.table(Gene_Id= 1:10000000, value.y= rnorm(n= 10000000))

setkeyv(dat1, c('Gene_Id'))
setkeyv(dat2, c('Gene_Id'))

system.time(datm<- merge(dat1, dat2))


## Use merge in base pckg
datA<- as.data.frame(dat1)
datB<- as.data.frame(dat2)

system.time(datM<- base:::merge(datA, datB))

#   user  system elapsed
#  0.750   0.004   0.755   <<- data.table

# 73.689   1.203  74.901  <<- base

Set the option all=TRUE to include all rows from both tables.

data.table has also the fread() function which is much faster than `read.table()

• 0 views • link
updated by Ram Ram • written by dariober dariober

Log in to answer this question.

More posts like this