Substract rows by sums of other rows based on condition

0
votes

I have following dataframe:

a = data.frame("ID"=c(1,1,1,1,1,1,5,5,5,5),
                "Year"=c(2016,2017,2016,2017,2016,2017,2017,2017,2016,2017),
                "Quarter"=c(1,1,2,2,3,3,1,2,3,3),
                "Revenue" = c(100,300,200,600,500,700,200,300,800,900))


   ID Year Quarter Revenue
1   1 2016       1     100
2   1 2017       1     300
3   1 2016       2     200
4   1 2017       2     600
5   1 2016       3     500
6   1 2017       3     700
7   5 2017       1     200
8   5 2017       2     300
9   5 2016       3     800
10  5 2017       3     900

I want to subtract the revenues of the preceding quarters for each quarter for each ID. The revenue of the 2. quarter gets substracted by the revenue of the 1. quarter, the revenue of the 3. quarter gets substracted by the sum of the 1. and 2. quarter, etc.. So this would mean for example for ID=1, year=2016:

2016 Q1 = 100, 2016 Q2 = 100 (200-100), 2016 Q3 = 300 (500-100-100).

There are also some ID's that dont have data for some quarters, like ID=5, year=2016, who only has data for the 3. quarter, so imaginary 0's could be substracted and we would get 2016 Q3 = 800 (800-0-0).

The desired output should look like this:

   ID Year Quarter Revenue
1   1 2016       1     100
2   1 2017       1     300
3   1 2016       2     100
4   1 2017       2     300
5   1 2016       3     300
6   1 2017       3     100
7   5 2017       1     200
8   5 2017       2     100
9   5 2016       3     800
10  5 2017       3     600

Is there a simple way to do this or do I need to write a function for this?

3
r

3 Answers

2
votes
library(tidyverse)

a %>%
   left_join(mutate(a, Quarter = Quarter + 1),
             by= c("ID", "Year", "Quarter"))%>%
   mutate(Revenue = Revenue.x - coalesce(Revenue.y,0)) %>%
   select(-contains("."))
   ID Year Quarter Revenue
1   1 2016       1     100
2   1 2017       1     300
3   1 2016       2     100
4   1 2017       2     300
5   1 2016       3     300
6   1 2017       3     100
7   5 2017       1     200
8   5 2017       2     100
9   5 2016       3     800
10  5 2017       3     600
0
votes

You can use purrr's accumulate function to perform such recursive calculation.

library(dplyr)
a %>%
  group_by(ID, Year) %>%
  mutate(Revenue = Revenue - cumsum(purrr::accumulate(lag(Revenue)[-1], 
                                    ~.y - .x, .init = 0)))

#      ID  Year Quarter Revenue
#   <dbl> <dbl>   <dbl>   <dbl>
# 1     1  2016       1     100
# 2     1  2017       1     300
# 3     1  2016       2     100
# 4     1  2017       2     300
# 5     1  2016       3     300
# 6     1  2017       3     100
# 7     5  2017       1     200
# 8     5  2017       2     100
# 9     5  2016       3     800
#10     5  2017       3     600
0
votes

You can do it in data.table.

library(data.table)

setDT(a)

a[,Revenue:= Revenue-shift(Revenue,type = 'lag',fill = 0), by= c('ID','Year')]