Try to tidy an excel worksheet using R

Question

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'm not entirely sure what you want so I didn't make an answer, but my go to for changing a dataframe from wide to long is tidyr::gather: https://tidyr.tidyverse.org/reference/gather.html — Silentdevildoll, Sep 28 '21 at 16:56
You should not refer to deprecated functions! I.e. `gather` has been superseded by `pivot_longer`. https://tidyr.tidyverse.org/reference/pivot_longer.html — deschen, Sep 28 '21 at 17:17

score 2 · Accepted Answer · answered Sep 28 '21 at 18:34

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.

Do you mean that I have read your intentions correctly? Was it telepathy or what? :-) — Marek Fiołka, Sep 28 '21 at 20:35

Try to tidy an excel worksheet using R

1 Answers1