Merging two dataframes in R

Question

I have a file with cluster number of a scRNA-seq and corresponding annotated cell type like

> dput(head(anno))
structure(list(cluster = 1:6, celltype = c("T:CD4+NAIVE", "T:CD4+NAIVE", 
"T:CD8+NAIVE", "NK:CD56+16+3+NKT", "T:CD8+NAIVE", "B:")), row.names = c(NA, 
6L), class = "data.frame")


> unique(anno$cluster)
 [1]  1  2  3  4  5  6  8  9 11 12 15 16 19 20 21 22 23 24 25 27 28 29 30 31 35 36 37 38 39 40 41 42 43 44 45 46
[37] 47 48 49 50 51 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 80 81 82 83 84
[73] 85 86 87 88 89 90 91 92 93 94 95
> unique(anno$celltype)
 [1] "T:CD4+NAIVE"        "T:CD8+NAIVE"        "NK:CD56+16+3+NKT"   "B:"                 "NKT"               
 [6] "T:CD8+EM"           "T:CD4+CM"           "mDC:"               "MACROPHAGE:"        "MONOCYTE:precursor"
[11] "NEUTROPHIL:"        "NK:CD56+16+3-"      "MAST:"              "MONOCYTE:"          "pDC:"              
[16] "T:Reg"  

and another data frame with cell IDs and corresponding cluster number of each cell

> dput(head(df2))
structure(list(cluster = c(1L, 1L, 1L, 1L, 1L, 1L), cellid = c("cell17203", 
"cell17205", "cell17206", "cell17207", "cell17208", "cell17209"
)), row.names = 15498:15503, class = "data.frame")

> unique(df2$cluster)
 [1]  1  2  3  4  5  6  8 12 16 19 21 23 27 29 30 35 36 40 53 55 92 11 24 38 41 46 90  9 15 20 47 74 57 39 42 25
[37] 56 64
> 
> dim(df2)
[1] 16056     2
>   

I want to create a cell type column in the second data frame (df2) in which relate cell type from the first data frame to cluster number for instance cluster 1 is T:CD4+NAIVE

I have tried these

> df2[anno, on = "cluster"]
Error in `[.data.frame`(df2, anno, on = "cluster") : 
  unused argument (on = "cluster")
> df2[c, on = anno$cluster]
Error in `[.data.frame`(df2, anno, on = c$cluster) : 
  unused argument (on = anno$cluster)
> df2[c, on = df2$cluster]
Error in `[.data.frame`(df2, anno, on = df2$cluster) : 
  unused argument (on = df2$cluster)

Answer 1

You used data.table notation without actually having data.table:

library(data.table)
setDT(anno)
setDT(df2)
df2[anno, on = "cluster"]

    cluster    cellid         celltype
 1:       1 cell17203      T:CD4+NAIVE
 2:       1 cell17205      T:CD4+NAIVE
 3:       1 cell17206      T:CD4+NAIVE
 4:       1 cell17207      T:CD4+NAIVE
 5:       1 cell17208      T:CD4+NAIVE
 6:       1 cell17209      T:CD4+NAIVE
 7:       2      <NA>      T:CD4+NAIVE
 8:       3      <NA>      T:CD8+NAIVE
 9:       4      <NA> NK:CD56+16+3+NKT
10:       5      <NA>      T:CD8+NAIVE
11:       6      <NA>               B:

Answer 2

For merging dataframes, I find it easiest to use the tidyverse / dplyr functions inner/full/left/right_join. See the "Data Transformation Cheatsheet" on this page. For the merge that @user438383 has mentioned, this would be a left_join:

library(tidyverse)
anno <- data.frame(cluster = 1:6, celltype = c("T:CD4+NAIVE", "T:CD4+NAIVE", 
                   "T:CD8+NAIVE", "NK:CD56+16+3+NKT", "T:CD8+NAIVE", "B:"))

df2 <- data.frame(cluster = c(1L, 1L, 1L, 1L, 1L, 1L),
                  cellid = c("cell17203", "cell17205", "cell17206",
                             "cell17207", "cell17208", "cell17209"),
                  row.names = 15498:15503)

> left_join(anno, df2)
Joining, by = "cluster"
   cluster         celltype    cellid
1        1      T:CD4+NAIVE cell17203
2        1      T:CD4+NAIVE cell17205
3        1      T:CD4+NAIVE cell17206
4        1      T:CD4+NAIVE cell17207
5        1      T:CD4+NAIVE cell17208
6        1      T:CD4+NAIVE cell17209
7        2      T:CD4+NAIVE      <NA>
8        3      T:CD8+NAIVE      <NA>
9        4 NK:CD56+16+3+NKT      <NA>
10       5      T:CD8+NAIVE      <NA>
11       6               B:      <NA>