CODEWITHSUNDEEP
rdataframedplyr
user15032839
user15032839

Reputation: 93

Compare the entire data frame to the same month in the reference year

I have a dataframe below, I want to set the compare year to i.e. 2018. and calculate the value difference in i.e. 2023-06-01 versus 2018-06-01

Need to grab the month and day in the Date column, then calculate the BenchDiff by subtracting to the value at the same month, day but year is changed to 2018?

my dataframe looks like this

Date GEO Variable Value
2017-06-01 USA GDP 100
2018-06-01 USA GDP 400
2019-06-01 USA GDP 600
2017-07-01 USA GDP 5
2018-07-01 USA GDP 7
2019-07-01 USA GDP 10

the end result should be

Date GEO Variable Value
2017-06-01 USA GDP 100
2018-06-01 USA GDP 400
2019-06-01 USA GDP 600
2017-07-01 USA GDP 5
2018-07-01 USA GDP 7
2019-07-01 USA GDP 10
2019-06-01 USA BenchDiff -300
2017-06-01 USA BenchDiff 0
2018-06-01 USA BenchDiff -00
2019-06-01 USA BenchDiff -300
2017-07-01 USA BenchDiff -2
2018-07-01 USA BenchDiff 0
2019-07-01 USA BenchDiff 3

I can do it if it's set a specific date, but I can't get it roll it

df %>%
  group_by(GEO, Variable) %>%
  mutate(BenchDiff = Value - Value[match(compare_date, Date)])

Upvotes: 1

Views: 42

Answers (1)

Darren Tsai
Darren Tsai

Reputation: 35584

You need tidyr::pivot_longer():

library(dplyr)
library(tidyr)

df %>%
  group_by(month = substr(Date, 6, 7)) %>%
  mutate(BenchDiff = Value - Value[match("2018", substr(Date, 1, 4))]) %>%
  ungroup() %>% select(-month) %>%
  pivot_longer(c(Value, BenchDiff), cols_vary = "slowest")

# # A tibble: 12 × 5
#    Date       GEO   Variable name      value
#    <chr>      <chr> <chr>    <chr>     <int>
#  1 2017-06-01 USA   GDP      Value       100
#  2 2018-06-01 USA   GDP      Value       400
#  3 2019-06-01 USA   GDP      Value       600
#  4 2017-07-01 USA   GDP      Value         5
#  5 2018-07-01 USA   GDP      Value         7
#  6 2019-07-01 USA   GDP      Value        10
#  7 2017-06-01 USA   GDP      BenchDiff  -300
#  8 2018-06-01 USA   GDP      BenchDiff     0
#  9 2019-06-01 USA   GDP      BenchDiff   200
# 10 2017-07-01 USA   GDP      BenchDiff    -2
# 11 2018-07-01 USA   GDP      BenchDiff     0
# 12 2019-07-01 USA   GDP      BenchDiff     3

Upvotes: 2

Related Questions