I am using the following libraries in R:
library(tidyverse)
library(timetk)
library(readxl)
library(writexl)
I have data like this:
structure(list(hour = c("0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300", "0000", "0100", "0200", "0300", "0400",
"0500", "0600", "0700", "0800", "0900", "1000", "1100", "1200",
"1300", "1400", "1500", "1600", "1700", "1800", "1900", "2000",
"2100", "2200", "2300"), Monday = c("6", "2", "4", "1", "3",
"1", "2", "4", "10", "10", "22", "16", "9", "10", "12", "8",
"8", "10", "14", "5", "5", "7", "12", "4", "5", "6", "3", "1",
"2", "2", "1", "5", "11", "7", "13", "9", "8", "14", "8", "13",
"11", "8", "11", "13", "7", "15", "8", "2", "2", "2", "1", "2",
"2", "1", "2", "5", "8", "19", "16", "10", "12", "9", "16", "10",
"13", "6", "10", "17", "12", "4", "6", "10", "2", "1", "4", "5",
"3", "2", "6", "5", "4", "9", "10", "12", "15", "11", "2", "14",
"15", "16", "15", "7", "19", "7", "7", "5", "1", "3", "2", "1",
"2", "4", "4", "5", "10", "11", "13", "8", "15", "19", "17",
"11", "13", "10", "6", "8", "7", "16", "8", "7", "3", "3", "3",
"3", "4", "4", "3", "6", "5", "8", "19", "13", "9", "8", "10",
"15", "10", "11", "9", "14", "12", "12", "4", "7", "6", "2",
"4", NA, "1", "2", "1", "7", "9", "7", "8", "11", "14", "13",
"16", "8", "19", "12", "10", "17", "13", "7", "3", "7"), Tuesday = c("7",
"5", "4", "1", "1", "5", "3", "3", "13", "9", "8", "11", "5",
"13", "7", "7", "6", "9", "9", "8", "9", "9", "7", "5", "1",
"2", NA, "3", "1", "2", "4", "8", "15", "7", "8", "9", "11",
"5", "6", "7", "13", "12", "12", "9", "8", "7", "13", "4", "1",
"4", "1", "3", "1", "1", "3", "4", "8", "6", "16", "9", "17",
"9", "6", "10", "13", "6", "13", "8", "7", "5", "5", "4", "4",
"2", "3", "0", "1", "2", "4", "7", "14", "13", "8", "9", "10",
"9", "14", "8", "6", "13", "17", "13", "13", "7", "6", "2", "3",
"2", "1", "1", "3", "1", "5", "9", "9", "12", "8", "14", "15",
"8", "9", "17", "6", "15", "4", "12", "17", "8", "7", "7", "4",
"1", "4", "2", "0", "4", "3", "2", "8", "12", "9", "7", "9",
"11", "9", "7", "10", "11", "10", "12", "2", "8", "8", "6", "1",
"2", "3", "5", "2", "3", "7", "8", "6", "11", "10", "12", "15",
"13", "10", "7", "12", "17", "8", "10", "9", "10", "5", "3"),
Wednesday = c("4", "5", "4", "3", "1", "4", "6", "6", "13",
"8", "13", "13", "13", "9", "5", "16", "9", "8", "18", "6",
"11", "4", "4", "10", "3", NA, "1", "2", "3", "3", "1", "2",
"4", "13", "8", "7", "12", "13", "8", "9", "10", "12", "12",
"6", "15", "11", "7", "6", "6", "2", "2", "4", "4", "2",
"4", "8", "7", "8", "14", "7", "8", "11", "10", "12", "11",
"10", "11", "11", "17", "5", "9", "4", "3", "1", "1", "3",
"2", "2", "0", "4", "12", "10", "11", "7", "11", "12", "12",
"7", "14", "12", "6", "12", "12", "8", "11", "5", "3", "2",
"0", "2", "2", "2", "4", "5", "5", "10", "9", "13", "14",
"14", "7", "9", "6", "5", "11", "13", "8", "11", "9", "8",
"8", "3", "4", "1", "2", "1", "3", "4", "4", "16", "14",
"13", "5", "9", "14", "15", "12", "9", "8", "11", "9", "6",
"9", "3", "4", "2", "3", "2", "2", "1", "3", "7", "7", "7",
"5", "16", "14", "8", "7", "9", "11", "15", "17", "16", "9",
"10", "6", "7"), Thursday = c("5", "3", "4", "1", "2", "1",
"1", "4", "5", "8", "13", "14", "10", "9", "9", "16", "10",
"6", "8", "10", "10", "11", "6", "12", "4", "3", "4", "6",
"2", "3", "2", "5", "7", "9", "13", "15", "14", "11", "9",
"6", "12", "11", "8", "10", "6", "8", "5", "3", "4", "5",
"0", "3", "3", "4", "1", "1", "7", "3", "16", "9", "11",
"16", "10", "15", "13", "11", "9", "12", "12", "7", "6",
"9", "4", "5", "2", "3", "1", NA, "2", "6", "4", "6", "9",
"7", "10", "9", "7", "7", "10", "11", "16", "7", "22", "12",
"6", "5", "3", "4", "3", "1", "1", "3", "3", "6", "7", "7",
"8", "10", "12", "9", "8", "10", "5", "12", "13", "13", "10",
"7", "5", "5", "8", "3", "2", "2", "2", "0", "6", "4", "3",
"11", "9", "11", "12", "13", "10", "9", "8", "7", "6", "9",
"6", "9", "8", "10", "3", "4", "6", "3", "3", "2", "4", "7",
"7", "7", "6", "11", "11", "11", "9", "5", "10", "7", "14",
"16", "7", "5", "6", "6"), Friday = c("6", "4", "1", "1",
"2", "0", "2", "4", "10", "4", "8", "9", "15", "9", "9",
"9", "9", "8", "8", "7", "10", "13", "5", "2", "3", "3",
"5", "3", "3", "8", "3", "4", "7", "9", "14", "9", "15",
"8", "7", "11", "14", "10", "4", "11", "8", "8", "7", "8",
"4", "2", "8", "2", "1", "1", "4", "1", "8", "7", "11", "16",
"11", "12", "9", "10", "4", "5", "4", "13", "8", "9", "5",
"7", "4", "3", "5", "1", "2", "3", "2", "6", "4", "7", "12",
"11", "7", "10", "8", "9", "12", "7", "5", "12", "7", "9",
"8", "9", "2", "2", "1", "3", "2", "2", "4", "3", "6", "7",
"14", "8", "13", "13", "10", "12", "11", "13", "8", "8",
"11", "8", "10", "6", "4", "3", "4", "3", "2", "3", "1",
"1", "3", "4", "10", "12", "9", "18", "5", "12", "9", "9",
"13", "10", "14", "4", "11", "6", "2", "5", "2", "3", "2",
"3", "4", "7", "8", "11", "7", "15", "12", "10", "8", "14",
"6", "10", "11", "9", "16", "13", "9", "6"), Saturday = c("4",
"6", "4", "4", "1", "4", "5", "4", "4", "5", "6", "7", "6",
"8", "7", "4", "11", "10", "10", "10", "9", "7", "6", "5",
"8", "5", "6", "2", "1", "1", "5", "5", "5", "10", "11",
"12", "8", "9", "8", "10", "9", "3", "9", "12", "11", "7",
"17", "5", "4", "4", "0", "4", "4", "3", "2", "4", "5", "13",
"7", "5", "9", "8", "10", "11", "9", "8", "12", "18", "8",
"11", "10", "4", "5", "8", "5", "3", "4", "5", "2", "6",
"5", "6", "9", "11", "7", "11", "8", "11", "2", "12", "9",
"10", "13", "9", "7", "8", "7", "3", "3", "2", "3", "3",
"5", "8", "5", "5", "13", "15", "8", "13", "10", "8", "10",
"6", "13", "6", "8", "9", "8", "6", "5", "6", "6", "4", "3",
"2", "4", "5", "4", "8", "10", "6", "13", "15", "11", "10",
"10", "4", "8", "7", "10", "9", "8", "8", "6", "3", "6",
"5", "3", "3", "2", "6", "9", "5", "11", "10", "8", "8",
"16", "9", "11", "6", "7", "11", "17", "10", "9", "8"), Sunday = c("6",
"6", "4", "2", "1", "1", "3", "4", "8", "7", "9", "10", "11",
"14", "7", "13", "8", "11", "9", "10", "12", "6", "8", "3",
"5", "3", "1", "2", "4", "3", "2", "8", "11", "9", "11",
"9", "7", "7", "8", "6", "5", "6", "10", "24", "15", "4",
"5", "2", "8", "2", "2", "5", "0", "2", "4", "6", "4", "8",
"6", "7", "9", "6", "8", "17", "10", "10", "8", "10", "13",
"6", "5", "4", "5", "6", "2", "4", "1", "2", "1", "3", "8",
"8", "12", "13", "10", "8", "8", "6", "7", "8", "9", "11",
"8", "8", "5", "5", "3", "4", "3", "3", "1", "1", "5", "3",
"6", "10", "3", "8", "4", "9", "7", "6", "7", "5", "10",
"8", "11", "10", "8", "13", "4", "6", "4", "2", "4", "1",
"6", "3", "5", "6", "7", "14", "8", "7", "6", "11", "9",
"8", "12", "6", "11", "10", "7", "5", "3", "4", "3", "4",
"2", "6", "7", "7", "6", "9", "15", "19", "5", "9", "10",
"9", "12", "10", "10", "12", "10", "7", "12", "7")), row.names = c(NA,
-168L), class = c("tbl_df", "tbl", "data.frame"))
I know the start date and the end date, so I have them set:
start_date <- "2018-04-09 00:00:00"
end_date <- "2021-09-26 23:00:00"
date_seq <- tk_make_timeseries(
start_date = start_date,
end_date = end_date,
by = "hour"
) %>%
as_tibble() %>%
mutate(rn = rep(1:24, nrow(date_seq)/24))
What I am trying to do is get my data out of the weird wide format and make it long. Monday 00:00:00 Value, Monday 01:00:00 Value etc.
Possible?
I don't know if I understand your intention well. However, it seems to me that you want to transform your pasted data into a long structure. So I have your data in a tibble named df.
# A tibble: 168 x 8
hour Monday Tuesday Wednesday Thursday Friday Saturday Sunday
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 0000 6 7 4 5 6 4 6
2 0100 2 5 5 3 4 6 6
3 0200 4 4 4 4 1 4 4
4 0300 1 1 3 1 1 4 2
5 0400 3 1 1 2 2 1 1
6 0500 1 5 4 1 0 4 1
7 0600 2 3 6 1 2 5 3
8 0700 4 3 6 4 4 4 4
9 0800 10 13 13 5 10 4 8
10 0900 10 9 8 8 4 5 7
# ... with 158 more rows
First, let's do the appropriate mutation. However, I recommend using the lubridate package.
library(tidyverse)
library(lubridate)
df1 = df %>% mutate(
nWeek = rep(1:(nrow(.)/24), each=24),
hour = as.numeric(hour)/100) %>%
pivot_longer(c(-hour,-nWeek), names_to = "WeekDay", values_to = "val") %>%
mutate(WeekDay = WeekDay %>% fct_inorder()) %>%
arrange(nWeek, WeekDay, hour)
output df1
# A tibble: 1,176 x 4
hour nWeek WeekDay val
<dbl> <int> <fct> <chr>
1 0 1 Monday 6
2 1 1 Monday 2
3 2 1 Monday 4
4 3 1 Monday 1
5 4 1 Monday 3
6 5 1 Monday 1
7 6 1 Monday 2
8 7 1 Monday 4
9 8 1 Monday 10
10 9 1 Monday 10
# ... with 1,166 more rows
Now we can add a start date to this.
star_date = ymd_hms("2018-04-09 00:00:00")
df1 %>% mutate(
DateTime = star_date +
ddays((nWeek-1)*7+as.numeric(WeekDay)-1) + dhours(hour)
)
output
# A tibble: 1,176 x 5
hour nWeek WeekDay val DateTime
<dbl> <int> <fct> <chr> <dttm>
1 0 1 Monday 6 2018-04-09 00:00:00
2 1 1 Monday 2 2018-04-09 01:00:00
3 2 1 Monday 4 2018-04-09 02:00:00
4 3 1 Monday 1 2018-04-09 03:00:00
5 4 1 Monday 3 2018-04-09 04:00:00
6 5 1 Monday 1 2018-04-09 05:00:00
7 6 1 Monday 2 2018-04-09 06:00:00
8 7 1 Monday 4 2018-04-09 07:00:00
9 8 1 Monday 10 2018-04-09 08:00:00
10 9 1 Monday 10 2018-04-09 09:00:00
# ... with 1,166 more rows
Hope this is the effect you have been expecting.