How to join two reactive dataframes in Shiny by input keys?

user_3403135 0
Viewed 811

I have two reactive dataframes (for explanation purposes I'm creating mock dataframes) and I need them to be joined in shiny using input fields as keys.

library(shiny)
library(tidyverse)
df <- data.frame(key1=c(1:4),key2 = c(5:8),
                 left= c("l1", "l2", "l3", "l4"),
                 right = c("r1", "r2", "r3", "r4"))
left_df <- df[,1:3]
right_df <- df[,c(1:2,3)]

ui <- fluidPage(

    # Sidebar with a slider input for number of bins 
    sidebarLayout(
        sidebarPanel(
            selectInput("key_right", "Right Key", c("key1", "key2")),
            selectInput("key_left", "Right Key", c("key1", "key2"))
        ),

        mainPanel(
           tableOutput("final_data")
        )
    )
)


server <- function(input, output) {
    
    left_df2 <- reactive({
        left_df
    })
    
    rightt_df2 <- reactive({
        right_df
    })
    
     final_df <- reactive({
         left_df2() %>%
             left_join(right_df2(), by = c(input$key_right = input$key_left))
    })
     
     output$final_data <- reactive({
         final_df()
     })

}

I get this error:

Error in parse(file, keep.source = FALSE, srcfile = src, encoding = enc) : 
  /app.R:37:60: unexpected '='
36:          left_df2() %>%
37:              left_join(right_df2(), by = c(input$key_right =
Error in sourceUTF8(fullpath, envir = new.env(parent = sharedEnv)) :

I need to perform the join by either key1 or key2 and that should be a user input as there is a chance the user uploads a file and may have one key or the other.

Thanks in advance!

shiny r
1 Answers

TL;DR

     final_df <- reactive({
         left_df2() %>%
             left_join(right_df2(), by = setNames(input$key_right, input$key_left))
     })

(I think you have the keys reversed in your code ... the first frame's keyname should be on the left of the =, not the right as you have it in your example. This is one reason why in my example below I demonstrate with different key names in the right_df.)

Explanation

input$key_right = ... is effectively a reassignment operation (over-writing the value of the key_right property of the input list), not what you intend.

For a clear demonstration, I'll (1) fix right_df so that it includes the right column, and (2) change its key names to be different.

right_df <- df[,c(1:2,4)]
names(right_df)[1:2] <- c("key3", "key4")
right_df
#   key3 key4 right
# 1    1    5    r1
# 2    2    6    r2
# 3    3    7    r3
# 4    4    8    r4

From here, a static join could be:

left_join(left_df, right_df, by = c("key1" = "key3"))
#   key1 key2 left key4 right
# 1    1    5   l1    5    r1
# 2    2    6   l2    6    r2
# 3    3    7   l3    7    r3
# 4    4    8   l4    8    r4

An equivalent way of assigning the name "key1" to a string "key3" is to use setNames:

left_join(left_df, right_df, by = setNames("key3", "key1"))

An advantage to this method is that it allows you to define the name programmatically from a variable.

user_33582720
Related