CODEWITHSUNDEEP
rtime-series
Björn Butter
Björn Butter

Reputation: 169

Maintain order of time series with group_by

Suppose my time series consists only of two columns: signal and day

The signal variable is supposed to repeat itself in a cycle of 1 to 6. So I need to insert empty rows for each implicit missing Signal but with signal counting from 1 to 6. (Suppose I have more columns that should also be empty (NA)). In other words, for each unique day, there should be 6 rows with signal counting from 1 to 6.

My dataframe:

df = structure(list(data.Signal = c(2, 3, 4, 5, 6, 1, 2, 3, 4, 6, 
1, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 1, 2, 3, 4, 5, 6, 2, 3, 4, 
5, 6, 1, 3, 4, 5, 6, 2, 3, 4, 5, 6, 3, 4, 6, 1, 3, 4, 5, 6, 1, 
2, 3, 4, 5, 6, 1, 2, 3, 4, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 
6, 1, 2, 3, 4, 5, 1, 2, 3, 4, 6, 2, 3, 6, 3, 4, 5, 6, 1, 3, 4, 
5, 6, 1, 1, 2, 3, 4, 5, 3, 4, 1, 2, 3, 4, 5, 5, 1, 2, 3, 4), 
    data.day = c(18, 18, 18, 18, 18, 19, 19, 19, 19, 19, 20, 
    20, 20, 20, 20, 21, 21, 21, 21, 21, 21, 22, 23, 23, 23, 23, 
    23, 23, 24, 24, 24, 24, 24, 25, 25, 25, 25, 25, 26, 26, 26, 
    26, 26, 27, 27, 27, 28, 28, 28, 28, 28, 29, 29, 29, 29, 29, 
    29, 30, 30, 30, 30, 30, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 
    2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 6, 6, 6, 6, 7, 
    7, 7, 7, 7, 8, 9, 9, 9, 9, 9, 10, 10, 11, 11, 11, 11, 11, 
    11, 12, 12, 12, 12)), class = "data.frame", row.names = c(NA, 
-114L))

My approach:

data <- df %>%
  group_by(gr=data.day) %>%
  complete(data.Signal = 1:6) %>%
  ungroup() %>% 
  select(-gr)

This however, sorts the days ascending. The order of the days is obviously meaningful in the time series data. How can I "re-sort" to the original order or is there another way of solving my problem? Thank you!

Upvotes: 0

Views: 306

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

Convert data.day to factor before using complete

library(dplyr)

df %>%
  group_by(gr = factor(data.day, levels = unique(data.day))) %>%
  tidyr::complete(data.Signal = 1:6) %>%
  ungroup() %>% 
  select(-gr)


#  data.Signal data.day
#         <dbl>    <dbl>
# 1           1       NA
# 2           2       18
# 3           3       18
# 4           4       18
# 5           5       18
# 6           6       18
# 7           1       19
# 8           2       19
# 9           3       19
#10           4       19
# … with 141 more rows

If you want those NA's filled you can use this version. 

df %>%
 mutate(grp = factor(data.day, levels = unique(data.day))) %>%
 complete(grp, data.Signal = 1:6) %>%
 ungroup() %>% 
 select(-data.day)

Upvotes: 2

Related Questions