How to use R and dplyr to paste column name as value for lookup

user_5403155 0
Viewed 704

I am using the output coefficients from a glm regression model and I need to create a lookup value, using key paste ([column name].[Factor Level], and then return the corresponding value from another data table. The column names must be dynamic so that I don't have to explicitly name each column one by one. The returned values from the lookup are then multiplied by 1 (for factors) or by the actual numeric values and all coef_colnames summed into column Total.

I've done some example in excel but cannot replicate it in R. var_Factor1 combines the column name and the factor level from each row (using paste) to build a key for the next step lookup

var_Number1 is just the column name as it is numeric and has no factor levels

library(dplyr)

# original data
dt = data.table(
  Factor1  = c("A","B","C"),
  Number1 = c(10, 20,40),
  Factor2 = c("D","H","N"),
  Number2 = c(2, 5,3)
)

# Lookup table
model_coef = data.table(
    Factor1.A   = 10,
    Factor1.B   = 20,
    Factor1.C   = 30,
    Factor2.D   = 40,
    Factor2.H   = 50,
    Factor2.N   = 60,
    Number1 = 200,
    Number2 = 500
)

#initial steps
dt <- dt %>% mutate (
  var_Factor1 = paste("Factor1", Factor1, sep =".")
, var_Number1 = "Number1"
, var_Factor2 = paste("Factor2", Factor2, sep =".")
, var_Number2 = "Number2"
) %>% mutate (
    coef_Factor1 = model_coef[,var_Factor1]
)

#The final output should produce (as replicated from Excel)


final_output = data.table (
  Factor1= c("A", "B", "C"),
  Number1= c(10, 20, 40),
  Factor2= c("D", "H", "N"),
  Number2= c(2, 5, 3),
  var_Factor1= c("Factor1.A", "Factor1.B", "Factor1.C"),
  var_Number1= c("Number1", "Number1", "Number1"),
  var_Factor2= c("Factor2.D", "Factor2.H", "Factor2.N"),
  var_Number2= c("Number2", "Number2", "Number2"),
  coef_Factor1= c(10, 20, 30),
  coef_Number1= c(200, 200, 200),
  coef_Factor2= c(40, 50, 60),
  coef_Number2= c(500, 500, 500),
  calc_Factor1= c(10, 20, 30),
  calc_Number1= c(2000, 4000, 8000),
  calc_Factor2= c(40, 50, 60),
  calc_Number2= c(1000, 2500, 1500),
  Total= c(3050, 6570, 9590)
)
tidyr dplyr r lookup
1 Answers

It's generally a bad idea to try to generate and manipulate dynamic columns. It will probably be better to use tidy data conventions and make the data "long". Also, it looks like you're trying to mix data.table and dplyr/tidyverse. In particular, this doesn't work: mutate (coef_Factor1 = model_coef[,var_Factor1]

I've tidied your data and modified your code to use dplyr/tidyverse below:

  • using tibble instead of data.table
  • re-built lookup table to tidy-long format so it can be left_joined properly to your table
  • used mutate to do the calculations that you describe

Beyond your example, if you have more than 2 "Numbers"/"Factors" (your naming/labeling/numbering is confusing btw), there are ways to generalize further so that the code multiplies coef * number generically, for each "number"/combination. Also, your data implies but it isn't clear that A is related to D, B is related to H, etc.

library(tidyverse)

data <- tibble(Factor1  = c("A","B","C"),Number1 = c(10, 20,40),Factor2 = c("D","H","N"),Number2 = c(2, 5,3))
model_coef <- tibble(Factor1.A   = 10,Factor1.B   = 20,Factor1.C   = 30,Factor2.D   = 40,Factor2.H   = 50,Factor2.N   = 60,Number1 = 200,Number2 = 500)

(model_coef_factor1 <- model_coef %>%
    select(Factor1.A:Factor1.C) %>%
    pivot_longer(cols = everything(), names_to = c("number", "factor"), names_sep = "[.]", values_to = "coef_factor1") %>%
    select(-number))
#> # A tibble: 3 x 2
#>   factor coef_factor1
#>   <chr>         <dbl>
#> 1 A                10
#> 2 B                20
#> 3 C                30

(model_coef_factor2 <- model_coef %>%
    select(Factor2.D:Factor2.N) %>%
    pivot_longer(cols = everything(), names_to = c("number", "factor"), names_sep = "[.]", values_to = "coef_factor2") %>%
    select(-number))
#> # A tibble: 3 x 2
#>   factor coef_factor2
#>   <chr>         <dbl>
#> 1 D                40
#> 2 H                50
#> 3 N                60

(final_output <- data %>%
    left_join(model_coef_factor1, by = c("Factor1"="factor")) %>%
    left_join(model_coef_factor2, by = c("Factor2"="factor")) %>%
    mutate(coef_number1 = model_coef$Number1,
           coef_number2 = model_coef$Number2,
           calc_factor1 = coef_factor1,
           calc_number1 = Number1 * coef_number1,
           calc_factor2 = coef_factor2,
           calc_number2 = Number2 * coef_number2,
           total = calc_factor1 + calc_number1 + calc_factor2 + calc_number2) %>%
    select(total, everything()))
#> # A tibble: 3 x 13
#>   total Factor1 Number1 Factor2 Number2 coef_factor1 coef_factor2
#>   <dbl> <chr>     <dbl> <chr>     <dbl>        <dbl>        <dbl>
#> 1  3050 A            10 D             2           10           40
#> 2  6570 B            20 H             5           20           50
#> 3  9590 C            40 N             3           30           60
#> # ... with 6 more variables: coef_number1 <dbl>, coef_number2 <dbl>,
#> #   calc_factor1 <dbl>, calc_number1 <dbl>, calc_factor2 <dbl>,
#> #   calc_number2 <dbl>

Created on 2019-10-23 by the reprex package (v0.3.0)

user_46630080
Related