Here dataset, where is the store opening hours by day of the week
store=structure(list(weekday_code = c("fri", "mon", "sat", "sun", "thu",
"tue", "wen"), workday_sign = c(1L, 1L, 1L, 1L, 1L, 1L, 1L),
opening_time = c("9:00:00", "9:00:00", "9:00:00", "10:00:00",
"9:00:00", "9:00:00", "9:00:00"), closing_time = c("18:00:00",
"18:00:00", "17:00:00", "16:00:00", "18:00:00", "18:00:00",
"18:00:00")), class = "data.frame", row.names = c(NA, -7L
))
the second dataset also has opening_time and closing_time columns (dt_start and dt_end).
wrong=structure(list(kod_dolg = c(50000690L, 801L, 50000690L, 801L,
50000690L, 801L, 801L, 50000690L, 801L, 50000690L, 50000690L,
801L, 50000690L, 50000690L, 50000690L, 801L, 50000690L, 801L,
50000690L, 801L, 50000690L, 801L, 50000690L, 50000690L, 801L,
801L, 50000690L, 50000690L, 50000690L, 801L, 50000690L, 50000690L,
50000690L, 801L, 50000690L, 801L, 50000690L), dt_start = c("14.02.2022 12:00",
"14.02.2022 11:00", "14.02.2022 10:00", "15.02.2022 8:00", "15.02.2022 8:00",
"15.02.2022 10:00", "15.02.2022 12:00", "15.02.2022 12:00", "16.02.2022 11:00",
"16.02.2022 12:00", "16.02.2022 13:00", "17.02.2022 10:00", "17.02.2022 10:00",
"17.02.2022 12:00", "17.02.2022 13:00", "18.02.2022 8:00", "18.02.2022 8:00",
"18.02.2022 10:00", "18.02.2022 10:00", "18.02.2022 12:00", "18.02.2022 12:00",
"12.02.2022 10:00", "12.02.2022 13:00", "12.02.2022 12:00", "12.02.2022 12:00",
"19.02.2022 10:00", "19.02.2022 13:00", "19.02.2022 12:00", "19.02.2022 12:00",
"20.02.2022 10:00", "20.02.2022 10:00", "20.02.2022 12:00", "20.02.2022 13:00",
"13.02.2022 10:00", "13.02.2022 10:00", "13.02.2022 12:00", "13.02.2022 12:00"
), dt_end = c("14.02.2022 22:00", "14.02.2022 21:00", "14.02.2022 20:00",
"15.02.2022 10:00", "15.02.2022 10:00", "15.02.2022 20:00", "15.02.2022 22:00",
"15.02.2022 22:00", "16.02.2022 21:00", "16.02.2022 22:00", "16.02.2022 20:00",
"17.02.2022 20:00", "17.02.2022 20:00", "17.02.2022 22:00", "17.02.2022 22:00",
"18.02.2022 10:00", "18.02.2022 10:00", "18.02.2022 20:00", "18.02.2022 20:00",
"18.02.2022 22:00", "18.02.2022 22:00", "12.02.2022 20:00", "12.02.2022 20:00",
"12.02.2022 22:00", "12.02.2022 22:00", "19.02.2022 20:00", "19.02.2022 20:00",
"19.02.2022 22:00", "19.02.2022 22:00", "20.02.2022 20:00", "20.02.2022 20:00",
"20.02.2022 22:00", "20.02.2022 22:00", "13.02.2022 20:00", "13.02.2022 20:00",
"13.02.2022 22:00", "13.02.2022 22:00"), person_number = c(976291L,
754105L, 867801L, NA, 867801L, NA, NA, 867801L, NA, 976291L,
938541L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA,
-37L))
In first dataset store there is column weekday_code
fri Friday
mon Monday
sat saturday
sun Sunday
thu Thursday
tue Tuesday
wen wednesday
but in second dataset wrong there is not column weekday_code.
So the question how from dates from dt_start and dt_end get weekday? For example 14.02.2022 10:00 is monday.
Then the second queustion is how to insert work hours on specific days of the week into wrong dataset from store dataset.
Example.In wrong dataset monday dt_start=14.02.2022 12:00 and dt_end=14.02.2022 22:00:00
but in store dataset in monday this shop must work from 9-18.
So for each monday date in wrong dataset i must change work hours like that
dt_start=14.02.2022 9:00:00 and dt_end=14.02.2022 18:00:00.
Similarly for other days of the week. In other words, i find the date in the wrong, determine its day of the week, look at the open and closing hours for this day of the week in store and insert these hours into the dt_start and dt_end that corresponds to this day of the week.
How can i make such permutations of working hours?
CodePudding user response：
First change the type of dt_start and dt_end to date time, then use lubridate::wday() to find out the weekday. Remember to change all characters to lower case, and change wed to wen to match your store style.
Then left_join it with store and rename it back to your style.
library(lubridate)
library(tidyverse)
wrong_with_weekday <- wrong %>% mutate(dt_start = as_datetime(dt_start, format = "%y.%m.%d"),
dt_end = as_datetime(dt_end, format = "%y.%m.%d"),
weekday_code = tolower(wday(dt_start, label = T)),
weekday_code = gsub("wed", "wen", weekday_code)) %>%
select(-dt_start, - dt_end)
left_join(wrong_with_weekday, store, by = "weekday_code") %>%
dplyr::rename("dt_start" = "opening_time",
"dt_end" = "closing_time")
Output
kod_dolg person_number weekday_code workday_sign dt_start dt_end
1 50000690 976291 thu 1 9:00:00 18:00:00
2 801 754105 thu 1 9:00:00 18:00:00
3 50000690 867801 thu 1 9:00:00 18:00:00
4 801 NA fri 1 9:00:00 18:00:00
5 50000690 867801 fri 1 9:00:00 18:00:00
6 801 NA fri 1 9:00:00 18:00:00
7 801 NA fri 1 9:00:00 18:00:00
8 50000690 867801 fri 1 9:00:00 18:00:00
9 801 NA sat 1 9:00:00 17:00:00
10 50000690 976291 sat 1 9:00:00 17:00:00
11 50000690 938541 sat 1 9:00:00 17:00:00
12 801 NA mon 1 9:00:00 18:00:00
13 50000690 NA mon 1 9:00:00 18:00:00
14 50000690 NA mon 1 9:00:00 18:00:00
15 50000690 NA mon 1 9:00:00 18:00:00
16 801 NA tue 1 9:00:00 18:00:00
17 50000690 NA tue 1 9:00:00 18:00:00
18 801 NA tue 1 9:00:00 18:00:00
19 50000690 NA tue 1 9:00:00 18:00:00
20 801 NA tue 1 9:00:00 18:00:00
21 50000690 NA tue 1 9:00:00 18:00:00
22 801 NA mon 1 9:00:00 18:00:00
23 50000690 NA mon 1 9:00:00 18:00:00
24 50000690 NA mon 1 9:00:00 18:00:00
25 801 NA mon 1 9:00:00 18:00:00
26 801 NA wen 1 9:00:00 18:00:00
27 50000690 NA wen 1 9:00:00 18:00:00
28 50000690 NA wen 1 9:00:00 18:00:00
29 50000690 NA wen 1 9:00:00 18:00:00
30 801 NA thu 1 9:00:00 18:00:00
31 50000690 NA thu 1 9:00:00 18:00:00
32 50000690 NA thu 1 9:00:00 18:00:00
33 50000690 NA thu 1 9:00:00 18:00:00
34 801 NA wen 1 9:00:00 18:00:00
35 50000690 NA wen 1 9:00:00 18:00:00
36 801 NA wen 1 9:00:00 18:00:00
37 50000690 NA wen 1 9:00:00 18:00:00
CodePudding user response：
The very first thing you'll have to do is, change the data type of dt_start and dt_end to POXIXct.
Then you need to create different columns for storing date and time.
Here's how you'll do it:
wrong$dt_start <- as.POSIXct(wrong$dt_start, format = "%d.%m.%Y %H:%M", tz = Sys.timezone())
wrong$opening_time <- format(as.POSIXct(wrong$dt_start), format = "%H:%M")
wrong$dt_start <- as.Date(wrong$dt_start)
wrong <- wrong[, c(1, 2, 5, 3, 4)]
wrong$dt_end <- as.POSIXct(wrong$dt_end, format = "%d.%m.%Y %H:%M", tz = Sys.timezone())
wrong$closing_time <- format(as.POSIXct(wrong$dt_end), format = "%H:%M")
wrong$dt_end <- as.Date(wrong$dt_end)
wrong <- wrong[, c(1, 2, 3, 4, 6, 5)]
wrong$weekday <- weekdays(wrong$dt_start)
I've changed the weekday_code with weekday in order to join the dfs.
new <- c("Friday", "Monday", "Saturday", "Sunday", "Thursday", "Tuesday", "Wednesday")
store$weekday <- new[match(store$weekday, store$weekday_code, nomatch = 0)]
store$weekday_code <- NULL
store <- store[, c(4, 1, 2, 3)]
Then you can left_join the data frames to get the desired result.
library(dplyr)
wrong2 <- wrong %>%
left_join(store, by = c("weekday")) %>%
mutate(opening_time = coalesce(opening_time.y, opening_time.x)) %>%
mutate(closing_time = coalesce(closing_time.y, closing_time.x)) %>%
select(-opening_time.x, -closing_time.x, -closing_time.y, -opening_time.y)
The output will look like this:
kod_dolg dt_start dt_end person_number weekday workday_sign
1 50000690 2022-02-14 2022-02-14 976291 Monday 1
2 801 2022-02-14 2022-02-14 754105 Monday 1
3 50000690 2022-02-14 2022-02-14 867801 Monday 1
4 801 2022-02-15 2022-02-15 NA Tuesday 1
5 50000690 2022-02-15 2022-02-15 867801 Tuesday 1
6 801 2022-02-15 2022-02-15 NA Tuesday 1
7 801 2022-02-15 2022-02-15 NA Tuesday 1
8 50000690 2022-02-15 2022-02-15 867801 Tuesday 1
9 801 2022-02-16 2022-02-16 NA Wednesday 1
10 50000690 2022-02-16 2022-02-16 976291 Wednesday 1
11 50000690 2022-02-16 2022-02-16 938541 Wednesday 1
12 801 2022-02-17 2022-02-17 NA Thursday 1
13 50000690 2022-02-17 2022-02-17 NA Thursday 1
14 50000690 2022-02-17 2022-02-17 NA Thursday 1
15 50000690 2022-02-17 2022-02-17 NA Thursday 1
16 801 2022-02-18 2022-02-18 NA Friday 1
17 50000690 2022-02-18 2022-02-18 NA Friday 1
18 801 2022-02-18 2022-02-18 NA Friday 1
19 50000690 2022-02-18 2022-02-18 NA Friday 1
20 801 2022-02-18 2022-02-18 NA Friday 1
21 50000690 2022-02-18 2022-02-18 NA Friday 1
22 801 2022-02-12 2022-02-12 NA Saturday 1
23 50000690 2022-02-12 2022-02-12 NA Saturday 1
24 50000690 2022-02-12 2022-02-12 NA Saturday 1
25 801 2022-02-12 2022-02-12 NA Saturday 1
26 801 2022-02-19 2022-02-19 NA Saturday 1
27 50000690 2022-02-19 2022-02-19 NA Saturday 1
28 50000690 2022-02-19 2022-02-19 NA Saturday 1
29 50000690 2022-02-19 2022-02-19 NA Saturday 1
30 801 2022-02-20 2022-02-20 NA Sunday 1
31 50000690 2022-02-20 2022-02-20 NA Sunday 1
32 50000690 2022-02-20 2022-02-20 NA Sunday 1
33 50000690 2022-02-20 2022-02-20 NA Sunday 1
34 801 2022-02-13 2022-02-13 NA Sunday 1
35 50000690 2022-02-13 2022-02-13 NA Sunday 1
36 801 2022-02-13 2022-02-13 NA Sunday 1
37 50000690 2022-02-13 2022-02-13 NA Sunday 1
opening_time closing_time
1 9:00:00 18:00:00
2 9:00:00 18:00:00
3 9:00:00 18:00:00
4 9:00:00 18:00:00
5 9:00:00 18:00:00
6 9:00:00 18:00:00
7 9:00:00 18:00:00
8 9:00:00 18:00:00
9 9:00:00 18:00:00
10 9:00:00 18:00:00
11 9:00:00 18:00:00
12 9:00:00 18:00:00
13 9:00:00 18:00:00
14 9:00:00 18:00:00
15 9:00:00 18:00:00
16 9:00:00 18:00:00
17 9:00:00 18:00:00
18 9:00:00 18:00:00
19 9:00:00 18:00:00
20 9:00:00 18:00:00
21 9:00:00 18:00:00
22 9:00:00 17:00:00
23 9:00:00 17:00:00
24 9:00:00 17:00:00
25 9:00:00 17:00:00
26 9:00:00 17:00:00
27 9:00:00 17:00:00
28 9:00:00 17:00:00
29 9:00:00 17:00:00
30 10:00:00 16:00:00
31 10:00:00 16:00:00
32 10:00:00 16:00:00
33 10:00:00 16:00:00
34 10:00:00 16:00:00
35 10:00:00 16:00:00
36 10:00:00 16:00:00
37 10:00:00 16:00:00