as_tbl_time Error: Specified `index` is not time based

SimoneA · April 16, 2020, 8:53pm

Dear all,

This is my very first post and I am completely new to R (RGui), I deeply apologise if I am writing in the wrong place, section or even on the wrong website. I hope it's not!

I spend a few days trying to work out a way to solve the issue I've got, but I can't find a solution.

I am trying to use the anomalize package.
This requires to transform the data in tibble time.

After I've imported my data (.CSV), I have been able to transform them into tibble (tbl_df) but, if i try to convert them into tbl_time I get this message:

Error: Specified index is not time based

I have tried to adjust the .CSV format (not sure if that is needed) changing the date format and the labels, but still... doesn't work!

I will paste what I've got, I am sure some of you will be able to help me. Thanks a lot!

This is part of the original dataset, as simple as monthly acquisition datapoints (gwt) and the acquisition date next to it (date)

              date gwt

1 01/02/2003 01:21:18 0.41
2 01/03/2003 01:21:18 0.35
3 01/04/2003 01:21:18 0.22
4 01/05/2003 01:21:18 0.03
5 01/06/2003 01:21:18 -0.05
6 01/07/2003 01:21:18 -0.09
7 01/08/2003 01:21:18 -0.10
8 01/09/2003 01:21:18 -0.02
9 01/10/2003 01:21:18 0.04
10 01/11/2003 01:21:18 0.11
11 01/12/2003 01:21:18 0.08
12 01/01/2004 01:21:18 0.00
13 01/02/2004 01:21:18 -0.19
14 01/03/2004 01:21:18 -0.34
15 01/04/2004 01:21:18 -0.46
16 01/05/2004 01:21:18 -0.59
17 01/06/2004 01:21:18 -0.63

This is the tbl_df I've been able to obtain:

A tibble: 46 x 2

date gwt

1 01/02/2003 01:21:18 0.41
2 01/03/2003 01:21:18 0.35
3 01/04/2003 01:21:18 0.22
4 01/05/2003 01:21:18 0.03
5 01/06/2003 01:21:18 -0.05
6 01/07/2003 01:21:18 -0.09
7 01/08/2003 01:21:18 -0.1
8 01/09/2003 01:21:18 -0.02
9 01/10/2003 01:21:18 0.04
10 01/11/2003 01:21:18 0.11

... with 36 more rows

and this is the message i get when i try to get the tbl_time (as mentioned above):

t2 <- as_tbl_time(t1, date)
Error: Specified index is not time based

P.S. I've tried to change the format, removing the time and leaving the date only, but it doesn't make any difference.

Is anyone able to help? I'd much appreciate it

Sorry again for the long post apologise in advance for any mistake.

Kind regards,

Simone

vikjam · April 16, 2020, 10:30pm

It seems like you haven't converted the date column into a valid date variable. I've included an example below that might be helpful.

Packages

It seems like you’re using tibbletime, but tibbletime has been
replaced with tsibble. See the README on the GitHub repository of
tibbletime for the annoucement.
lubridate is a helpful package for handling dates.

library(tsibble) 
library(lubridate)

## 
## Attaching package: 'lubridate'

## The following objects are masked from 'package:tsibble':
## 
##     interval, new_interval

## The following object is masked from 'package:base':
## 
##     date

Loading data as an example

When posting a question, it can be helpful to post the data in a form
that others can replicate. You can get output like I have below, by
using the command dput.

ex_data <- structure(list(date = c(
  "01/02/2003 01:21:18", "01/03/2003 01:21:18",
  "01/04/2003 01:21:18", "01/05/2003 01:21:18", "01/06/2003 01:21:18",
  "01/07/2003 01:21:18", "01/08/2003 01:21:18", "01/09/2003 01:21:18",
  "01/10/2003 01:21:18", "01/11/2003 01:21:18", "01/12/2003 01:21:18",
  "01/01/2004 01:21:18", "01/02/2004 01:21:18", "01/03/2004 01:21:18",
  "01/04/2004 01:21:18", "01/05/2004 01:21:18", "01/06/2004 01:21:18"
), gwt = c(
  0.41, 0.35, 0.22, 0.03, -0.05, -0.09, -0.1, -0.02,
  0.04, 0.11, 0.08, 0, -0.19, -0.34, -0.46, -0.59, -0.63
)), row.names = c(
  NA,
  -17L
), class = "data.frame")
print(head(ex_data))

##                  date   gwt
## 1 01/02/2003 01:21:18  0.41
## 2 01/03/2003 01:21:18  0.35
## 3 01/04/2003 01:21:18  0.22
## 4 01/05/2003 01:21:18  0.03
## 5 01/06/2003 01:21:18 -0.05
## 6 01/07/2003 01:21:18 -0.09

Handling dates

We need to convert the date column into a valid date object. We can
use mdy_hms from the lubridate package to accomplish this. However,
in the example data you posted, it seems like the hour, minute, seconds
portion of the date was the same for each row. In this case,
as_tsibble will throw an error. See the FAQ on error “Can’t obtain
the interval due to the mismatched index
class.”.
So, we can truncate the date-time object into just a date object (i.e.,
we ignore the hours, minutes and seconds) using as_date from the
lubridate package.

ex_data$date_num <- as_date(mdy_hms(ex_data$date))
print(head(ex_data))

##                  date   gwt   date_num
## 1 01/02/2003 01:21:18  0.41 2003-01-02
## 2 01/03/2003 01:21:18  0.35 2003-01-03
## 3 01/04/2003 01:21:18  0.22 2003-01-04
## 4 01/05/2003 01:21:18  0.03 2003-01-05
## 5 01/06/2003 01:21:18 -0.05 2003-01-06
## 6 01/07/2003 01:21:18 -0.09 2003-01-07

Now we have a valid date object to use as an index.

ex_tsibble <- as_tsibble(ex_data, index = date_num)
print(ex_tsibble)

## # A tsibble: 17 x 3 [1D]
##    date                  gwt date_num  
##    <chr>               <dbl> <date>    
##  1 01/02/2003 01:21:18  0.41 2003-01-02
##  2 01/03/2003 01:21:18  0.35 2003-01-03
##  3 01/04/2003 01:21:18  0.22 2003-01-04
##  4 01/05/2003 01:21:18  0.03 2003-01-05
##  5 01/06/2003 01:21:18 -0.05 2003-01-06
##  6 01/07/2003 01:21:18 -0.09 2003-01-07
##  7 01/08/2003 01:21:18 -0.1  2003-01-08
##  8 01/09/2003 01:21:18 -0.02 2003-01-09
##  9 01/10/2003 01:21:18  0.04 2003-01-10
## 10 01/11/2003 01:21:18  0.11 2003-01-11
## 11 01/12/2003 01:21:18  0.08 2003-01-12
## 12 01/01/2004 01:21:18  0    2004-01-01
## 13 01/02/2004 01:21:18 -0.19 2004-01-02
## 14 01/03/2004 01:21:18 -0.34 2004-01-03
## 15 01/04/2004 01:21:18 -0.46 2004-01-04
## 16 01/05/2004 01:21:18 -0.59 2004-01-05
## 17 01/06/2004 01:21:18 -0.63 2004-01-06

andresrcs · April 16, 2020, 11:15pm

Transform your date variable into a date_time object, see this example

library(dplyr)
library(lubridate)
library(tibbletime)

t1 <- data.frame(
  stringsAsFactors = FALSE,
              date = c("01/02/2003 01:21:18",
                       "01/03/2003 01:21:18","01/04/2003 01:21:18",
                       "01/05/2003 01:21:18","01/06/2003 01:21:18","01/07/2003 01:21:18",
                       "01/08/2003 01:21:18","01/09/2003 01:21:18",
                       "01/10/2003 01:21:18","01/11/2003 01:21:18","01/12/2003 01:21:18",
                       "01/01/2004 01:21:18","01/02/2004 01:21:18",
                       "01/03/2004 01:21:18","01/04/2004 01:21:18","01/05/2004 01:21:18",
                       "01/06/2004 01:21:18"),
               gwt = c(0.41,0.35,0.22,0.03,-0.05,
                       -0.09,-0.1,-0.02,0.04,0.11,0.08,0,-0.19,-0.34,
                       -0.46,-0.59,-0.63)
)

t1 %>% 
    mutate(date = dmy_hms(date)) %>% 
    arrange(date) %>% 
    as_tbl_time(index = date)
#> # A time tibble: 17 x 2
#> # Index: date
#>    date                  gwt
#>    <dttm>              <dbl>
#>  1 2003-02-01 01:21:18  0.41
#>  2 2003-03-01 01:21:18  0.35
#>  3 2003-04-01 01:21:18  0.22
#>  4 2003-05-01 01:21:18  0.03
#>  5 2003-06-01 01:21:18 -0.05
#>  6 2003-07-01 01:21:18 -0.09
#>  7 2003-08-01 01:21:18 -0.1 
#>  8 2003-09-01 01:21:18 -0.02
#>  9 2003-10-01 01:21:18  0.04
#> 10 2003-11-01 01:21:18  0.11
#> 11 2003-12-01 01:21:18  0.08
#> 12 2004-01-01 01:21:18  0   
#> 13 2004-02-01 01:21:18 -0.19
#> 14 2004-03-01 01:21:18 -0.34
#> 15 2004-04-01 01:21:18 -0.46
#> 16 2004-05-01 01:21:18 -0.59
#> 17 2004-06-01 01:21:18 -0.63

^{Created on 2020-04-16 by the reprex package (v0.3.0.9001)}

SimoneA · April 16, 2020, 11:44pm

Dear andresrcs,

I have pasted it and copied it and it worked perfectly! Unfortunately, I do have an issue that I'm sure you can help me to solve. As I said I'm completely new to R.

t1 <- data.frame(
stringsAsFactors = FALSE,
date = c("01/02/2003 01:21:18",
"01/03/2003 01:21:18","01/04/2003 01:21:18",
"01/05/2003 01:21:18","01/06/2003 01:21:18","01/07/2003 01:21:18",
"01/08/2003 01:21:18","01/09/2003 01:21:18",
"01/10/2003 01:21:18","01/11/2003 01:21:18","01/12/2003 01:21:18",
"01/01/2004 01:21:18","01/02/2004 01:21:18",
"01/03/2004 01:21:18","01/04/2004 01:21:18","01/05/2004 01:21:18",
"01/06/2004 01:21:18"),
gwt = c(0.41,0.35,0.22,0.03,-0.05,
-0.09,-0.1,-0.02,0.04,0.11,0.08,0,-0.19,-0.34,
-0.46,-0.59,-0.63)
)

How did you manage to copy the data in that format (i.e. date = c("01/02/2003 01:21:18",
"01/03/2003 01:21:18", ..........)?

If I understand how to transform the full dataset in this way I will be finally done!

Thanks immensely.

Regards

andresrcs · April 16, 2020, 11:48pm

No need for you to do that, that is just sample data on a copy/paste friendly format for reproducibility purposes, you can just use your actual dataframe directly.

SimoneA · April 17, 2020, 1:58am

Thanks a lot for your timely help!

SimoneA · April 17, 2020, 1:58am

Thank you very much! It worked and it did it perfectly!

system · April 24, 2020, 1:58am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.