Using row value in dataframe to choose column in second dataframe

Question

So I have two dataframes... one is a wide format where the column names are dates, and listed under the dates are all the issues that happened on those days. The second dataframe is one that has a variety of columns that depicts other information of things that've happened on that day.. like this:

df_1 <- 2017-07-15       2017-08-15      2017-09-15      2017-10-15
         'crashed'         'crashed'       'reset'         'crashed'
         'damaged'         'reset'         'reset'          'reset'
       'no problems'       'crashed'      'crashed'         'reset'

df_2 <-    Date             Make           Model            Color
        2017-07-15          iPhone           7               black
        2017-08-15          Android        Galaxy            silver
        2017-09-15          iPhone           6               white
        2017-10-15        Blackberry       Curve             black

I want to access all of the data in the column of df_1 who's name matches the Date of df_2... Reason being because I'm trying to use the information from df_2 for a ggplot geom_point() and display the information from df_1 in a tooltip when the cursor hovers over a point on the same date

(The following code was taken from here, simply inputting my dataframe): https://gitlab.com/snippets/16220

library("shiny")
library("ggplot2")

ui <- pageWithSidebar(
    headerPanel("Tooltips in ggplot2 + shiny"),

  sidebarPanel(
HTML("Tooltips are managed by combination of shiny+ggplot hover functionality",
     "and css styles. By setting hover argument of 'plotOutput' we could access",
     "hover data from the server side, as an ordinary input. Hover input is",
     "a list with: position of cursor ON the image; domain - that is",
     "values of variables at the plotting area edges; range - that is position",
     "of plotting area edges in pixels relative to whole image element.",
     "Additionally for ggplot used mappings are returned. </br>",
     "To create tooltip first we need to identify position of the cursor",
     "inside the image element. We do it by calculating distances from left and",
     "top edge of image element from hover data. Then we create tooltip, in this",
     "app it is 'wellPanel' with some info inside, and set 'position' property",
     "to 'absolute' and set 'left' and 'top' properties to calculated values.",
     "However, 'absolute' position is defined as relative to the nearest positioned",
     "ancestor. Because we want to position tooltip inside the image, we need",
     "to put both 'plotOutput' with image and 'uiOutput' with tooltip content",
     "inside additional 'div' element with 'position' property set to 'relative'.",
     "We don't set top, left etc. for this element, so the actual position of",
     "the image doesn't change - it's edges are identical as previously, so",
     "we can use 'div' (for positioning tooltip) as substitute for image. </br>"),
width = 3
),

 mainPanel(

# this is an extra div used ONLY to create positioned ancestor for tooltip
# we don't change its position
div(
  style = "position:relative",
  plotOutput("scatterplot", 
             hover = hoverOpts("plot_hover", delay = 100, delayType = "debounce")),
  uiOutput("hover_info")
),
width = 7
  )
)

server <- function(input, output) {

  output$scatterplot <- renderPlot({
    ggplot(df_2, aes(x = Date, y = make)) +
      geom_point()
  })


  output$hover_info <- renderUI({
hover <- input$plot_hover
point <- nearPoints(df_2, hover, threshold = 5, maxpoints = 1, addDist = TRUE)
if (nrow(point) == 0) return(NULL)

# calculate point position INSIDE the image as percent of total dimensions
# from left (horizontal) and from top (vertical)
left_pct <- (hover$x - hover$domain$left) / (hover$domain$right - hover$domain$left)
top_pct <- (hover$domain$top - hover$y) / (hover$domain$top - hover$domain$bottom)

# calculate distance from left and bottom side of the picture in pixels
left_px <- hover$range$left + left_pct * (hover$range$right - hover$range$left)
top_px <- hover$range$top + top_pct * (hover$range$bottom - hover$range$top)

# create style property fot tooltip
# background color is set so tooltip is a bit transparent
# z-index is set so we are sure are tooltip will be on top
style <- paste0("position:absolute; z-index:100; background-color: rgba(245, 245, 245, 0.85); ",
                "left:", left_px + 2, "px; top:", top_px + 2, "px;")

# actual tooltip created as wellPanel
wellPanel(
  style = style,
  p(HTML(paste0(DF_1 DATE THAT MATCHES DF_2 DATE)))
   )
  })
}

runApp(list(ui = ui, server = server))

updated. I tagged ggplot because it is what I am using this for — Naji, Aug 08 '17 at 06:19
Could you use tidyr's gather on df_1 and then use dplyr's left_join it to joing it to df_2: df_1_long <- df_1 %>% gather(Date, Performance, 1:4), combined <- left_join(df_2, df_1_long). Not 100% how you want this formatted for plotting but it might be a start? — Sarah, Aug 08 '17 at 06:39
it is a good start. Per the answer below it seems like thats exactly what Moody is trying to do. However, gather is creating two columns with dates repeated as there are multiple entries for each date (ie. there will be 4 rows, each with the first date, and each with one of the values listed from the original dataframe). I cant join this to my df_2 because those columns have 1 row per date, so the data doesnt match up properly... — Naji, Aug 08 '17 at 18:13

score 1 · Answer 1 · answered Aug 08 '17 at 08:00

Would this suit you ?

data

df_1 <- read.table(text="2017-07-15       2017-08-15      2017-09-15      2017-10-15
'crashed'         'crashed'       'reset'         'crashed'
'damaged'         'reset'         'reset'          'reset'
'no problems'       'crashed'      'crashed'         'reset'",header=TRUE,stringsAsFactors = FALSE)

df_2 <- read.table(text="Date             Make           Model            Color
2017-07-15          iPhone           7               black
2017-08-15          Android        Galaxy            silver
2017-09-15          iPhone           6               white
2017-10-15        Blackberry       Curve             black",header=TRUE,stringsAsFactors = FALSE)

code

library(dplyr)
library(tidyr)
df_1 %>%
  mutate(variable = letters[1:n()]) %>%
  gather(Date, val, 1:4) %>%
  mutate(Date = gsub("\\.","-",substr(Date,2,nchar(Date)))) %>% # you may not have to do this with your actual data
  rbind(df_2 %>% gather(variable,val,-1)) %>%
  spread(Date,val)

result

#   variable  2017-07-15 2017-08-15 2017-09-15 2017-10-15
# 1        a     crashed    crashed      reset    crashed
# 2        b     damaged      reset      reset      reset
# 3        c no problems    crashed    crashed      reset
# 4    Color       black     silver      white      black
# 5     Make      iPhone    Android     iPhone Blackberry
# 6    Model           7     Galaxy          6      Curve

this is almost working.. except for some reason on the second line `gather(Date, val, 1:4)` it isn't gathering my data properly. Rather it is giving me two columns: one of all of the Dates (as expected) and another of purely NA values for each date. Also, I want to select all of the columns, not just 1:4... — Naji, Aug 08 '17 at 17:54

score 0 · Accepted Answer · answered Aug 09 '17 at 00:51

OK so here's my workaround, which although might not be the best? seemed to be the only thing I could do to get exactly what i wanted. THANK YOU TO MOODY_MUDSKIPPER FOR THE STARTING POINT!!

       df_1 %>%
          mutate(variable = letters[1:n()])
       df_1 <- melt(df_1, id.vars='variable')
       colnames(df_1)[2] <- "Date" #because the newly created variable column and the column with the dates were both named 'variable'...
       df_1 <- dcast(df_1, Date ~ variable)

       df_2 <- cbind(df_2,df_1,[,-1]) #subsetting to remove the first column from df_1 so that there arent two columns with Dates in them

now df_2 will have all of the information from df_1 organized with it

great, if you have the same dates on both sides in the same order it will work, just be aware that it's not a general solution. Could you add `library(reshape2)` at the start of your code ? In melt you have the option `variable.name` set to `"variable"` by default, you may want to set it to "Date" there to gain a line and some elegance. And finally, if you want to go the dirty route, you could also use `t` , cbind both tables and drop the row name: `'rownames<-'((cbind(df_2,t(df_1))),NULL)` — moodymudskipper, Aug 09 '17 at 09:40

Using row value in dataframe to choose column in second dataframe

2 Answers2