New column with week on week spending by store

Question

I have a dataset that I need to track customers spending week by week based on the store.

store <- c(1,2,3,4,5,6,1,2,3,4,5,6)
week <- c(1,1,1,1,1,1,2,2,2,2,2,2)
spending <- c(10,12,14,12,11,14,13,12,11,15,12,13)

df <- data.frame(store,week,spending)

I want to create a new column that will tell me the percentage increase / decrease in spending by store week on week.

So starting in week 2 for store 1 I need a column that is 30% i.e. (week2 spend $13 - week1 spend $10) / week1 spend $10 *100

I know how to get the total weekly increase but no idea how to get this to a store level.

Answer 1

We group by 'store', create the 'perc' by taking the difference of spending and the lag of 'spending' divided by the lag and multiplied by 100

library(dplyr)
df %>% 
  group_by(store) %>% 
  mutate(perc = (spending - lag(spending))/lag(spending) * 100) %>%
  ungroup

-output

# A tibble: 12 × 4
   store  week spending   perc
   <dbl> <dbl>    <dbl>  <dbl>
 1     1     1       10  NA   
 2     2     1       12  NA   
 3     3     1       14  NA   
 4     4     1       12  NA   
 5     5     1       11  NA   
 6     6     1       14  NA   
 7     1     2       13  30   
 8     2     2       12   0   
 9     3     2       11 -21.4 
10     4     2       15  25   
11     5     2       12   9.09
12     6     2       13  -7.14

---

Or using base R with diff

df$perc <- with(df, ave(spending, store, FUN =
   function(x) c(NA, diff(x))/c(NA, x[-length(x)]))) * 100

Answer 2

Here's an option using data.table:

library(data.table)

setDT(df)[ , diff := 100 * ((spending / shift(spending)) - 1), by = store][]

#>     store week spending       diff
#>  1:     1    1       10         NA
#>  2:     2    1       12         NA
#>  3:     3    1       14         NA
#>  4:     4    1       12         NA
#>  5:     5    1       11         NA
#>  6:     6    1       14         NA
#>  7:     1    2       13  30.000000
#>  8:     2    2       12   0.000000
#>  9:     3    2       11 -21.428571
#> 10:     4    2       15  25.000000
#> 11:     5    2       12   9.090909
#> 12:     6    2       13  -7.142857