Look up IDs in data frame and plot values from another data frame

Question

For each pair in df1, I would like to get out the matching rows in df2 and df3 and make a scatterplot (facet_grid based in the pairs). In my example, it would plot hsa-let-7a-3p against 8813 and hsa-let-7a-5p against 2519. I though of using the which function to get the correct rows, but did not work.

df1 <- read.table(text = "
miRNA            Gene
hsa-let-7a-3p     8813
hsa-let-7a-5p     2729
hsa-miR-199b-3p  7114", header = TRUE)

df2

structure(c(5.27881924454587, 5.42100992826421, 5.03661514415768, 
4.61991946787712, 4.77653649009038, 4.20681831482766, 4.72326708238235, 
5.71639332668001, 4.64150222478444, 5.30683756666667, 3.12549237216981, 
3.90209754560968, 5.30553723124249, 3.84903066479419, 4.48855173958744, 
3.43691433497801), .Dim = c(4L, 4L), .Dimnames = list(c("8813", 
"2519", "2729", "4800"), c("0086", "0175", "0217", "0394")))

df3

structure(c(5.56747199573984, 17.0087987317192, 4.25668419353693, 
13.0353755634515, 12.5433058620952, 12.0651538467382, 5.12412779449881, 
15.056925223215, 2.32974750359771, 10.7172886032171, 9.51977921513182, 
8.81158700492176, 5.09668325530934, 16.1403627714742, 4.83409156331527, 
11.5486987433545, 11.3966153591115, 9.53901970845365, -4.25127156533006, 
16.6238786279913, 5.08580281916836, 12.2523973319305, 10.5197937363935, 
10.675242984158), .Dim = c(6L, 4L), .Dimnames = list(c("hsa-let-7a-3p", 
"hsa-let-7a-5p", "hsa-let-7b-3p", "hsa-let-7b-5p", "hsa-let-7c-5p", 
"hsa-let-7d-3p"), c("0086", "0175", "0217", "0394")))

zx8754 zx8754 · Accepted Answer · 2018-10-31T08:44:41

After merging all three dataframes, we need to convert wide data to long:

library(tidyverse)

# add rownames as column
df2 <- rownames_to_column(as.data.frame(df2), "Gene")
df3 <- rownames_to_column(as.data.frame(df3), "miRNA")

# then merge and gather
plotDat <- merge(
  merge(df1, df2, by = "Gene") %>%
    gather(key = "myID", value = "x", -c(Gene, miRNA)),
  merge(df1, df3, by = "miRNA") %>% 
    gather(key = "myID", value = "y", -c(Gene, miRNA))
  )

# plot with facets
ggplot(plotDat, aes(x, y)) +
  geom_point() +
  facet_grid(~ miRNA + Gene)

Look up IDs in data frame and plot values from another data frame

1 Answers