Apply custom scaling model to data frame?

Question

Apologies in advance for the long post; I'm new to this part of R so I don't know exactly how to summarize the question.

I have a data frame for "baseline results" that looks like this

Circuit Voltage Power
      A       1     1
      B       1     3
      C       1     5

And a scaling model that looks like this

Voltage Scaled_Power
    1.0         1.00
    0.9         0.75
    0.8         0.50

This scaling model comes from a table, not from any function such as lm. This model essentially says: "if new Voltage == 0.9, then scale the baseline Power by 0.75". The scaling model is independent of "Circuit", but requires the baseline Power for the specific "Circuit" to calculate new power. The end result I want to achieve is this:

Circuit Voltage Power
      A     1.0  1.00
      B     1.0  3.00
      C     1.0  5.00
      A     0.9  0.75
      B     0.9  2.25
      C     0.9  3.75
      A     0.8  0.50
      B     0.8  1.50
      C     0.8  2.50

that is, a new data frame with all combinations of "Circuit" from the first data frame and "Voltage" from the second (I know I can do this with expand.grid), but with a new column "Power" which applies a scaling factor.

This sounds like pattern commonly solved, so I guess a solution must already exist?

I've tried the following so far

expand.grid(Circuit = df_base$Circuit,Voltage = scaling_model$Voltage) %>%
   left_join(df_base) %>% 
   left_join(scaling_model)

Circuit Voltage Power Scaled_Power
      A     1.0     1         1.00
      B     1.0     3         1.00
      C     1.0     5         1.00
      A     0.9    NA         0.75
      B     0.9    NA         0.75
      C     0.9    NA         0.75
      A     0.8    NA         0.50
      B     0.8    NA         0.50
      C     0.8    NA         0.50

but then I need some function for doing multiplication selecting a a specific value from a row (something like Excel's VLOOKUP), and I'm not sure this is the most R-ish way to solve the problem.

Here's the code for the MWE

library(dplyr)
scaling_model <-
    data.frame(
        Voltage      = c(1.0,  0.9, 0.8),
        Scaled_Power = c(1.0, 0.75, 0.5)) 
df_base <- data.frame(
        Circuit = factor(c("A", "B", "C")),
        Voltage = c(1.0, 1.0, 1.0),
        Power   = c(1.0, 3.0, 5.0))
df_result <-
    data.frame(
        Circuit = factor(c("A", "B", "C", "A", "B", "C", "A", "B", "C")),
        Voltage = c(1.0, 1.0, 1.0, 0.9, 0.9, 0.9, 0.8, 0.8, 0.8),
        Power   = c(1.0, 3.0, 5.0,
                    1.0 * 0.75, 3.0 * 0.75, 5.0 * 0.75,
                    1.0 * 0.5, 3.0 * 0.5, 5.0 * 0.5))

markus · Accepted Answer

With dplyr you could do

library(dplyr)
expand.grid(Circuit = df_base$Circuit,
            Voltage = scaling_model$Voltage) %>% 
  left_join(scaling_model) %>% 
  left_join(df_base[c("Circuit", "Power")]) %>% 
  mutate(Power = Scaled_Power * Power) %>%
  select(-Scaled_Power)
#  Circuit Voltage Power
#1       A     1.0  1.00
#2       B     1.0  3.00
#3       C     1.0  5.00
#4       A     0.9  0.75
#5       B     0.9  2.25
#6       C     0.9  3.75
#7       A     0.8  0.50
#8       B     0.8  1.50
#9       C     0.8  2.50

Here is an another base R option that uses expand.grid and match

df_result <- expand.grid(Circuit = df_base$Circuit,
                         Voltage = scaling_model$Voltage)

df_result$Power <- df_base$Power * scaling_model$Scaled_Power[match(df_result$Voltage, scaling_model$Voltage)]
df_result

match finds the positions of df_result$Voltage in scaling_model$Voltage and we use the result to subset (i.e. expand) scaling_model$Scaled_Power which we finally multiply by df_base$Power.

Apply custom scaling model to data frame?

Answers (2)

Related Questions