Search code examples
rdplyrreshape2

Relative Change Calculation

  Prod  T1  T2   
    A   42  48  
    A   21  18  
    B   38  37  
    B   57  68  
    B   38  30  
    C   51  54  
    C   47  49  
    C   22  15

For each row I want to add relative change corresponding to each product. e.g for Row1 Change of Product A is ((48+18)-(42+21))/(42+21) = 4.7% and change for Row 1 is (48-42)/42= 14%. So in the new column I want to calculate 14% - 4.7% = 9.3%. i.e Actual Change % - Product Change %. What is the best possible way to do this

Solution

  • Lets df be your data.frame.

    We calculate the change by row and the change by Product, creating new columns for this values. Then we do the calculations you need and only keep a new column called RelativeChange.

    df %>% 
  mutate(by_row = (T2 - T1) / T1) %>% 
  group_by(Prod) %>% 
  mutate(by_prod = ( sum(T2) - sum(T1) ) / sum(T1),
         RelativeChange = by_row - by_prod) %>%
  ungroup() %>% 
  select(-c(by_row, by_prod))