Search code examples
rdataframedplyrmutate

Mutating a dataframe in a vectorised fashion in R programming language

Lets say I have a dataframe, called df, in R with columns like so:

id, start_period, end_period, store_value, shop_items, in_bank, owed_money, prefix_store_value, prefix_shop_items, prefix_in_bank, prefix_owed_money, val_BP_to_x, val_SR_to_x.

I also have a vector called "columns_for_change" which consists of a subset of the columns of the dataframe (the numeric columns of df, except id), df, i.e.,

columns_for_change <- c(store_value, shop_items, in_bank, owed_money).

The R code to create that dataframe and a screenshot of the dataframe is given below:

df <- data.frame(id=c(1,2,3), 
                   start_period=c("03/04/2020","23/04/2020","12/04/2020"), 
                   end_period=c("03/04/2021","23/04/2021","12/04/2021"), 
                   store_value=c(100,390,470), 
                   shop_items=c(208,807,550), 
                   in_bank=c(450,5000,8000), 
                   owed_money=c(759,979,605), 
                   prefix_store_value=c("GD","GD","SR"), 
                   prefix_shop_items=c("GD","GD","GD"), 
                   prefix_in_bank=c("BP","GD","GD"), 
                   prefix_owed_money=c("GD","GD","GD"), 
                   val_BP_to_x=c(0.86,0.97,0.83), 
                   val_SR_to_x=c(0.99,0.89,0.87))

enter image description here

Now what I would like to do is for all column names given in the vector "columns_for_change", convert the value given under each of those columns by multiplying it by it's corresponding entry given in the last 2 columns depending on its prefix value. To be more clear, what I want is if the prefix column entry corresponding to each of the items in the vector is either "BP" or "SR", then multiply the column value by either the multipliers given in val_BP_to_x or val_SR_to_x, respectively. Otherwise, if the prefix entry is "GD", then I would like to leave the corresponding entry as it is without multiplying. For example, because the prefix_in_bank column entry in the first row for id 1 is "BP", I would like the corresponding entry given in the in_bank column, which originally was 450 to be changed to 450 * 0.86 = 387, which is df[1,c("in_bank")] = df[1,c("in_bank")] * df[1,c("val_BP_to_x")]. How do I achieve that? Any help is much appreciated, and thanks in advance.

Solution

  • We could loop across the columns to modify, get the corresponding values of the 'prefix' columns and use case_when or case_match to do the multiplication

    library(dplyr)# version >= 1.1.0
library(stringr)
columns_for_change <- c("store_value", "shop_items", "in_bank", "owed_money")

df %>% 
  mutate(across(all_of(columns_for_change), ~ {
   pfx <- cur_data()[[str_c("prefix_", cur_column())]]
  case_match(pfx, "GD"~ .x, "BP" ~ .x * val_BP_to_x,
   "SR" ~ .x * val_SR_to_x)
}))

    -output

     id start_period end_period store_value shop_items in_bank owed_money prefix_store_value prefix_shop_items prefix_in_bank
1  1   03/04/2020 03/04/2021       100.0        208     387        759                 GD                GD             BP
2  2   23/04/2020 23/04/2021       390.0        807    5000        979                 GD                GD             GD
3  3   12/04/2020 12/04/2021       408.9        550    8000        605                 SR                GD             GD
  prefix_owed_money val_BP_to_x val_SR_to_x
1                GD        0.86        0.99
2                GD        0.97        0.89
3                GD        0.83        0.87