I have a data frame that contains the columns "hour", "day","month" and "count".
library(tidyverse)
set.seed(0)
df <- expand_grid(expand_grid(
hour = seq(0:23),
day = c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")),
month = c("Jan", "Feb", "Mar", "Apr", "May", "Jun")) %>%
mutate(count = sample(0:100, n(), replace = TRUE))
head(df)
# A tibble: 6 × 4 hour day month count <int> <chr> <chr> <int> 1 1 Mon Jan 13 2 1 Mon Feb 67 3 1 Mon Mar 38 4 1 Mon Apr 0 5 1 Mon May 33 6 1 Mon Jun 86
I would like to add a new column named "id" that contains an increasing index which can be used to sort the data in chronological order. The solution I found is not particularly concise and requires me to set factor levels before calling arrange(). Is there another way to solve this issue that capitalises on the fact that I am working with (unformatted) dates?
This is my solution with arrange():
df2 <- df %>%
mutate(day = factor(day, levels = c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")),
month = factor(month, levels = c("Jan", "Feb", "Mar", "Apr", "May", "Jun"))) %>%
arrange(month, day, hour) %>%
mutate(id = row_number())
head(df2)
# A tibble: 6 × 5 hour day month count id <int> <fct> <fct> <int> <int> 1 1 Mon Jan 13 1 2 2 Mon Jan 43 2 3 3 Mon Jan 82 3 4 4 Mon Jan 66 4 5 5 Mon Jan 49 5 6 6 Mon Jan 79 6
Any suggestions are much appreciated. Thank you!