I am trying to create a new column which uses lag when grouped by another variable (my week column).
I have tried using dplyr based on the other queries that I have read but the solutions don't appear to be working for me. I am not sure if it just my dataset or I am missing something glaringly obvious!
I have tried the following:
model <-
model %>%
group_by(week) %>%
mutate(lag_kWh_at_r1 = dplyr::lag(model$kwh_at_r1, n = 1, default = 0)
)
but this returns the below error:
Error: Column `lag_kWh_at_r1` must be length 7 (the group size) or one, not 182
I also tried adding mutate(lag_kwh_at_r1 = 0) %>%before the group_by but this did not help.
I would expect the first day of each week in the lag_kwh_at_r1 to be 0.00.
Any ideas?
My current data set is:
dput(model)
structure(list(day = 1:182, week_day = structure(c(3L, 4L, 2L,
6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L,
5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L,
3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L,
2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L,
7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L,
1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L,
4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L,
6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L,
5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L,
3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L,
2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L, 7L, 5L, 1L, 3L, 4L, 2L, 6L,
7L, 5L, 1L), .Label = c("Fri", "Mon", "Sat", "Sun", "Thurs",
"Tues", "Weds"), class = "factor"), date_day = c(1L, 2L, 3L,
4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L,
18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L,
31L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L,
14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L,
27L, 28L, 29L, 30L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L,
11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L,
24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 1L, 2L, 3L, 4L, 5L, 6L,
7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L,
20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 1L,
2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L,
16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L,
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L,
15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L,
28L, 29L, 30L, 31L), month = structure(c(6L, 6L, 6L, 6L, 6L,
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L,
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 5L, 5L, 5L, 5L, 5L, 5L,
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L,
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L), .Label = c("dec", "feb", "jan", "mar", "nov", "oct"), class = "factor"),
week = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L,
7L, 7L, 7L, 7L, 7L, 7L, 7L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 9L,
9L, 9L, 9L, 9L, 9L, 9L, 10L, 10L, 10L, 10L, 10L, 10L, 10L,
11L, 11L, 11L, 11L, 11L, 11L, 11L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 14L, 14L, 14L,
14L, 14L, 14L, 14L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 16L,
16L, 16L, 16L, 16L, 16L, 16L, 17L, 17L, 17L, 17L, 17L, 17L,
17L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 19L, 19L, 19L, 19L,
19L, 19L, 19L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 21L, 21L,
21L, 21L, 21L, 21L, 21L, 22L, 22L, 22L, 22L, 22L, 22L, 22L,
23L, 23L, 23L, 23L, 23L, 23L, 23L, 24L, 24L, 24L, 24L, 24L,
24L, 24L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 26L, 26L, 26L,
26L, 26L, 26L, 26L), top_up = c(7.5, 0, 0, 0, 0, 0, 0, 7.5,
0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0,
0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5,
0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0,
0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5,
0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0,
0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5,
0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0,
0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5,
0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0,
0, 0, 7.5, 0, 0, 0, 0, 0, 0, 7.5, 0, 0, 0, 0, 0, 0), daily_heat_use = c(26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26, 26,
26), daily_hw_use = c(15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38, 15.38,
15.38, 15.38, 15.38, 15.38, 15.38, 15.38), daily_unm_losses = c(10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3,
10.3), total_m_use = c(41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38, 41.38,
41.38, 41.38, 41.38, 41.38, 41.38, 41.38), cum_use = c(41.38,
82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76,
124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14,
165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52,
206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9,
248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28,
289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66,
41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38,
82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76,
124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14,
165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52,
206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9,
248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28,
289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66,
41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38,
82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76,
124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14,
165.52, 206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52,
206.9, 248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9,
248.28, 289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28,
289.66, 41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66,
41.38, 82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38,
82.76, 124.14, 165.52, 206.9, 248.28, 289.66, 41.38, 82.76,
124.14, 165.52, 206.9, 248.28, 289.66), kwh_at_r1 = c(41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615, 41.38,
82.76, 124.14, 163.044615384615, 163.044615384615, 163.044615384615,
163.044615384615, 41.38, 82.76, 124.14, 163.044615384615,
163.044615384615, 163.044615384615, 163.044615384615)), row.names = c(NA,
-182L), groups = structure(list(week = 1:26, .rows = list(1:7,
8:14, 15:21, 22:28, 29:35, 36:42, 43:49, 50:56, 57:63, 64:70,
71:77, 78:84, 85:91, 92:98, 99:105, 106:112, 113:119, 120:126,
127:133, 134:140, 141:147, 148:154, 155:161, 162:168, 169:175,
176:182)), row.names = c(NA, -26L), class = c("tbl_df", "tbl",
"data.frame"), .drop = TRUE), class = c("grouped_df", "tbl_df",
"tbl", "data.frame"))
model$kwh_at_r1 should be kwh_at_r1. The first refers to the ungrouped value and the second refers to the grouped value.'
One other possible problem is that model shown in the dput output is already grouped and you may not have intended that.
groups(model) # model is formed from the dput output shown in the question
## [[1]]
## week
That can be caused by not using ungroup after group_by. Furthermore, the code in the question also has no ungroup to close off the group_by.
Although you often see code doing that it can result in unexpected output later and it is better to always use ungroup to close a group_by unless you really intended to produce a grouped object.