Validate time series index

Question

I am using a dataset which is grouped by group_by function of dplyr package. Each Group has it's own time index which i.e. supposedly consist of 12 months sequences. This means that it can start from January and end up in December or in other cases it can start from June of the year before and end up in May next year.

Here is the dataset example:

     ID       DATE
      8 2017-01-31
      8 2017-02-28
      8 2017-03-31
      8 2017-04-30
      8 2017-05-31
      8 2017-06-30
      8 2017-07-31
      8 2017-08-31
      8 2017-09-30
      8 2017-10-31
      8 2017-11-30
      8 2017-12-31
     32 2017-01-31
     32 2017-02-28
     32 2017-03-31
     32 2017-04-30
     32 2017-05-31
     32 2017-06-30
     32 2017-07-31
     32 2017-08-31
     32 2017-09-30
     32 2017-10-31
     32 2017-11-30
     32 2017-12-31
     45 2016-09-30
     45 2016-10-31
     45 2016-11-30
     45 2016-12-31
     45 2017-01-31
     45 2017-02-28
     45 2017-03-31
     45 2017-04-30
     45 2017-05-31
     45 2017-06-30
     45 2017-07-31
     45 2017-08-31

The Problem is that I can't confirm or validate visualy because of dataset dimensions if there are so called "jumps", in other words if dates are consistent. Is there any simple way in r to do that, perhaps some modification/combination of functions from tibbletime package.

Any help will by appreciated.

Thank you in advance.

You need to be more specific. By "if dates are consistent", do you mean that all groups start in Jan and end in Dec? — acylam, May 11 '18 at 15:18
@useR Not necessarly, they can strat at any month but have to be a sequence of 12 months. — user3185925, May 11 '18 at 15:21
What if an `ID` has 12 months it, but skips a month? Would that count as valid? — acylam, May 11 '18 at 15:28

acylam · Answer 1 · 2018-05-11T19:00:19.560

You can use the summarise function from dplyr to return a logical value of whether there are any day differences greater than 31 within each ID. You do this by first constructing a temporary date using only the year and month and attaching "-01" as the fake day:

library(dplyr)
library(lubridate)

df %>%
  group_by(ID) %>%
  mutate(DATE2 = ymd(paste0(sub('\\-\\d+$', '', DATE),'-01')),
         DATE_diff = c(0, diff(DATE2))) %>%
  summarise(Valid = !any(DATE_diff > 31))

Result:

# A tibble: 3 x 2
     ID Valid
  <int> <lgl>
1     8  TRUE
2    32  TRUE
3    45  TRUE

You can also visually check if there are any gaps by plotting your dates for each ID:

library(ggplot2)

df %>%
  mutate(DATE = ymd(paste0(sub('\\-\\d+$', '', DATE),'-01')),
         ID = as.factor(ID)) %>%
  ggplot(aes(x = DATE, y = ID, group = ID)) + 
  geom_point(aes(color = ID)) +
  scale_x_date(date_breaks = "1 month",
               date_labels = "%b-%Y") +
  labs(title = "Time Line by ID")

Unfortunatly this is not it. I am aware of this function, but what I need is to find out if there missing months. i.e. the number of months will be 12, but they will be running from 2016-08 till 2016-12 and then from 2017-03 till 2017-08. So the Count will be 12 but there still missing months. Thank you. — user3185925, May 11 '18 at 15:31
@user3185925 Edited my answer. See if this is what you are looking for now. — acylam, May 11 '18 at 19:01

score 0 · Answer 2 · answered May 11 '18 at 16:53

Here's how I would typically approach this problem using data.table -- the cut.Date() and seq.Date() functions from base are the meat of the logic, so you use the same approach with dplyr if desired.

library(data.table)

## Convert to data.table
setDT(df)

## Convert DATE to a date in case it wasn't already
df[,DATE := as.Date(DATE)]

## Order by ID and Date
setkey(df,ID,DATE)

## Create a column with the month of each date
df[,Month := as.Date(cut.Date(DATE, breaks = "months"))]

## Generate a sequence of Dates by month for the number of observations
## in each group -- .N
df[,ExpectedMonth := seq.Date(from = min(Month),
                              by = "months",
                              length.out = .N), by = .(ID)]

## Create a summary table to test whether an ID had 12 observations where
## the actual month was equal to the expected month
Test <- df[Month == ExpectedMonth, .(Valid = ifelse(.N == 12L,TRUE,FALSE)), by = .(ID)]

print(Test)
#    ID Valid
# 1:  8  TRUE
# 2: 32  TRUE
# 3: 45  TRUE

## Do a no-copy join of Test to df based on ID
## and create a column in df based on the 'Valid' column in Test
df[Test, Valid := i.Valid, on = "ID"]

## The final output:
head(df)
#    ID       DATE      Month ExpectedMonth Valid
# 1:  8 2017-01-31 2017-01-01    2017-01-01  TRUE
# 2:  8 2017-02-28 2017-02-01    2017-02-01  TRUE
# 3:  8 2017-03-31 2017-03-01    2017-03-01  TRUE
# 4:  8 2017-04-30 2017-04-01    2017-04-01  TRUE
# 5:  8 2017-05-31 2017-05-01    2017-05-01  TRUE
# 6:  8 2017-06-30 2017-06-01    2017-06-01  TRUE

You could also do things a little more compactly if you really wanted to using a self-join and skip creating Test

setDT(df)

df[,DATE := as.Date(DATE)]
setkey(df,ID,DATE)
df[,Month := as.Date(cut.Date(DATE, breaks = "months"))]
df[,ExpectedMonth := seq.Date(from = min(Month), by = "months", length.out = .N), keyby = .(ID)]
df[df[Month == ExpectedMonth,.(Valid = ifelse(.N == 12L,TRUE,FALSE)),keyby = .(ID)], Valid := i.Valid]

@useR and Matt, I would like to thank you for neww ideas and good Implementation. — user3185925, May 14 '18 at 17:49

Validate time series index

2 Answers2