Mixing
Recent value by weekday as new column
Recent value by weekday as new column
Is there a simple way to do this. I have a dataframe with values and dates and want to add a column that contains the value from the most recent weekday - which I've manually added below as val_recent.
val_recent
df <- data.frame(
date = seq(as.Date("2018/01/01"), by = "day", length.out = 20),
val = sample(1:10, 20, replace=TRUE)) %>%
mutate(weekday = strftime(date,'%A'))
date val weekday val_recent
1 20/01/2018 4 Saturday 4
2 19/01/2018 3 Friday 3
3 18/01/2018 4 Thursday 4
4 17/01/2018 10 Wednesday 10
5 16/01/2018 2 Tuesday 2
6 15/01/2018 6 Monday 6
7 14/01/2018 1 Sunday 1
8 13/01/2018 9 Saturday 4
9 12/01/2018 9 Friday 3
10 11/01/2018 7 Thursday 4
11 10/01/2018 8 Wednesday 10
12 9/01/2018 6 Tuesday 2
13 8/01/2018 10 Monday 6
14 7/01/2018 6 Sunday 1
15 6/01/2018 3 Saturday 4
16 5/01/2018 3 Friday 3
17 4/01/2018 10 Thursday 4
18 3/01/2018 4 Wednesday 10
19 2/01/2018 2 Tuesday 2
20 1/01/2018 8 Monday 6
20/01/2018 is the most recent Saturday and the value 4 is copied to all other Saturdays. Same for the other weekdays.
– Vlad
23 mins ago
2 Answers
2
Just group by weekend and take the first value
df%>%
group_by(weekday)%>%
mutate(val2=val[1])
# A tibble: 20 x 5
# Groups: weekday [7]
date val weekday val_recent val2
<chr> <int> <chr> <int> <int>
1 20/01/2018 4 Saturday 4 4
2 19/01/2018 3 Friday 3 3
3 18/01/2018 4 Thursday 4 4
4 17/01/2018 10 Wednesday 10 10
5 16/01/2018 2 Tuesday 2 2
6 15/01/2018 6 Monday 6 6
7 14/01/2018 1 Sunday 1 1
8 13/01/2018 9 Saturday 4 4
9 12/01/2018 9 Friday 3 3
10 11/01/2018 7 Thursday 4 4
11 10/01/2018 8 Wednesday 10 10
12 9/01/2018 6 Tuesday 2 2
13 8/01/2018 10 Monday 6 6
14 7/01/2018 6 Sunday 1 1
15 6/01/2018 3 Saturday 4 4
16 5/01/2018 3 Friday 3 3
17 4/01/2018 10 Thursday 4 4
18 3/01/2018 4 Wednesday 10 10
19 2/01/2018 2 Tuesday 2 2
20 1/01/2018 8 Monday 6 6
We can use the first from dplyr
first
dplyr
library(dplyr)
df %>%
group_by(weekday) %>%
mutate(val2 = first(val))
Assuming that the 'date' is not ordered, then use the which.min
which.min
df %>%
group_by(weekday) %>%
mutate(val2 = val[which.min(date)])
Or arrange by 'date' and 'weekday' (which would be more expensive)
arrange
df %>%
arrange(weekday, date) %>%
group_by(weekday) %>%
mutate(val2 = first(val))
should be
which.max for most recent date– Ryan
5 mins ago
which.max
@Ryan that is giving me different date than the
val_recent– akrun
3 mins ago
val_recent
There's no
set.seed here, so you can't check against the results unless you're using read.table or something. Anyway, which.max gives you most recent date, see which.max(as.Date(c("2018-01-01", "2018-01-02")))– Ryan
49 secs ago
set.seed
read.table
which.max
which.max(as.Date(c("2018-01-01", "2018-01-02")))
@Ryan You are right regarding that. But, I am not able to match with the 'val_recent'. May be the OP meant something else
– akrun
4 secs ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
what do you mean by the most recent weekday?
– Onyambu
25 mins ago