How to mutate over every possible combination of columns

Hi there,

I have a sample data frame with 5 variables: Network, BG, RD, Year, and Rate. I want to create a column for each possible combinations of Network, BG, RD, and Year. The logic behind this is that all this four variables with different combinations determine the incremental value for the Rate.

I've been struggling to use mutate and case_when and didn't figure out how to include all the possible combinations as input variables. For example, in the case below, I have 4 elements for Network, 2 elements for BG, 2 elements for RD, and 4 elements for Year. In total, that would be 4 x 2 x 2 x 4=64 combinations of new incremental values. However, I only want the user interface contains 4 numericinputs based on the filter I selected above. The case_when in my reprex example is not perfect, it is just giving you an idea on how the incremental value is applied based on the filters.

I wonder if there is an easy way to do this without using loop. Or any suggestions would be greatly appreciated.

library(shiny)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(data.table)
#> 
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:dplyr':
#> 
#>     between, first, last
library(tidyr)

df1<-data.frame("Network"=c("50K","50K","50K","50K", "45K","45K","45K","45K", "40K","40K","40K","40K","30K","30K","30K","30K"), 
                "BG"=c("B","B", "G","G","B","B", "G","G","B","B", "G","G","B","B", "G","G"), 
                "RD"=c("R","D","R","D","R","D","R","D","R","D","R","D","R","D","R","D"))

df2<- data.frame("Year"=c(2020,2021,2022,2023),
                 "Rate"=c(0.1,0.2,0.3,0.4))

df3<-merge(df1,df2)

df4<-function(input, var1, var2, var3, var4){
  
  var1<-enexpr(var1)
  var2<-enexpr(var2)
  var3<-enexpr(var3)
  var4<-enexpr(var4)
  
   df3<-df3 %>% filter(Network %in% input$Network,BG %in% input$BG, RD %in% input$RD) 
     
    df3<-df3 %>% rowwise() %>% 
    mutate(delta= case_when(Network =="50K" & BG =="B" & RD == "R" & Year==2021 ~ as.numeric(!!var1), 
                            Network =="45K" & BG =="B" & RD == "R" & Year==2022 ~ as.numeric(!!var2), 
                            Network =="40K" & BG =="B" & RD == "R" & Year==2023 ~ as.numeric(!!var3),
                            Network =="30K" & BG =="B" & RD == "R" & Year==2024 ~ as.numeric(!!var4),
                            TRUE~0)) %>% 
     mutate(new_rate=Rate+delta) %>% 
      select(Network, BG, RD, Year, new_rate) %>% as.data.frame()
   
  df3<-df3 %>% spread(key=Year, value=new_rate, fill = FALSE) 
}

ui <- fluidPage(
  titlePanel("Input"),
  sidebarLayout(
    sidebarPanel(
      selectInput("Network","Choose a network:",
                  choices = c(unique(toupper(df3$Network))),
                  selected = '50K'),
      selectInput("BG","Choose B or G:",
                  choices = c(unique(df3$BG)),
                  selected = "B"),
      selectInput("RD","Choose R or D:",
                  choices = c(unique(df3$RD)),
                  selected = "R"),
      br(),
      numericInput("p1","Period 1:", 0, min = NA, max = NA),
      numericInput("p2","Period 2", 0, min = NA, max = NA),
      numericInput("p3","Period 3", 0, min = NA, max = NA),
      numericInput("p4","Period 4", 0, min = NA, max = NA)),
    
    mainPanel(
      DT::dataTableOutput("Rate")
    )
  )
)
  


# Define server logic for random distribution app ----
server <- function(input, output) {
  
  data<-reactive({
    df4(input, input$p1, input$p2, input$p3, input$p4)
  })
  
  output$Rate <- DT::renderDataTable({
    df3<- data() 
    
  })
}

shinyApp(ui=ui, server = server)

Shiny applications not supported in static R Markdown documents

Created on 2020-10-12 by the reprex package (v0.3.0)

f(x) = y, where x is df3 and y is

each possible combinations of Network, BG, RD, and Year

f is tidyr::expand

suppressPackageStartupMessages({library(tidyr)})
df1 <- data.frame(
  "Network" = c("50K", "50K", "50K", "50K", "45K", "45K", "45K", "45K", "40K", "40K", "40K", "40K", "30K", "30K", "30K", "30K"),
  "BG" = c("B", "B", "G", "G", "B", "B", "G", "G", "B", "B", "G", "G", "B", "B", "G", "G"),
  "RD" = c("R", "D", "R", "D", "R", "D", "R", "D", "R", "D", "R", "D", "R", "D", "R", "D")
)

df2 <- data.frame(
  "Year" = c(2020, 2021, 2022, 2023),
  "Rate" = c(0.1, 0.2, 0.3, 0.4)
)

df3 <- merge(df1, df2)

expand(df3,Network,BG,RD,Year) # all possible
#> # A tibble: 64 x 4
#>    Network BG    RD     Year
#>    <chr>   <chr> <chr> <dbl>
#>  1 30K     B     D      2020
#>  2 30K     B     D      2021
#>  3 30K     B     D      2022
#>  4 30K     B     D      2023
#>  5 30K     B     R      2020
#>  6 30K     B     R      2021
#>  7 30K     B     R      2022
#>  8 30K     B     R      2023
#>  9 30K     G     D      2020
#> 10 30K     G     D      2021
#> # … with 54 more rows
expand(df3,nesting(Network,BG,RD,Year)) # all present
#> # A tibble: 64 x 4
#>    Network BG    RD     Year
#>    <chr>   <chr> <chr> <dbl>
#>  1 30K     B     D      2020
#>  2 30K     B     D      2021
#>  3 30K     B     D      2022
#>  4 30K     B     D      2023
#>  5 30K     B     R      2020
#>  6 30K     B     R      2021
#>  7 30K     B     R      2022
#>  8 30K     B     R      2023
#>  9 30K     G     D      2020
#> 10 30K     G     D      2021
#> # … with 54 more rows

Created on 2020-10-12 by the reprex package (v0.3.0.9001)

a column for each possible combinations

is not clear: into df3?

Yes. The new column is into df3. The new column is "delta" in the code, the new column created depends on the combinations of Network, BG, RD and Year. There should be 64 added values stored in the new column corresponding to each combinations of 64. Hope that makes sense.

Still having trouble. Shoulddf$delta represent one of the unique 64 combinations or all of them?

the delta column should contain all 64 unique variables corresponding to the 64 combinations. I need to make the case_when condition include all these 64 combinations with corresponding input variables. Each input variable represents one unique combination. But I don't know how to make it dynamically based on user input.

Help me again with y, the delta column. For illustration, assuming that it were represented as an integer in the closed range [1:64] corresponding to one of the possible combinations, is the goal to assign delta based on the combination present of the four variables other than rate? Alternatively, assuming that each entry in delta is a list object of the possible combinations, wouldn't every row have an identical delta?

sorry for the confusion. I can give you a simple example:

for example, in the UI the user select 50K, B, R, and want to add 0.05 to the rate of 2020. Then the delta here would be 0.05, and the new rate would be the old rate plus 0.05.
Or the user select 50K, G, R and want to add 0.01 to the rate of 2020, then the delta would be 0.01 and the new rate would be the old rate plus 0.01.

The purpose of this is to assign different incremental value(delta) based on different scenario. The total scenarios should include all 64 combinations. Hope it makes sense.

Then, in f(x) = y, x is a data frame with columns year, rate and a column with some object, y, that can be used in an UI operation that permits selection of oneof the 16 possible combinations of Network, BG and RD. (Without year, the snippet above can be rerun to produce the smaller number of combinations).

It is only necessary to create the list or other object that contains the 16 combinations once, and it can be duplicated the required number of times to fit the data frame with year and rate with

rep(the_object,N)

where N is some number of rows; then just cbind.

With that in hand, I'd suggest reposting with a title along the lines of: UI to display and choose one of 16 options