Autoplot doesn't recognize time(Year , months)

crepuscule · September 1, 2020, 3:48pm

I am fully aware of that and i appreciate and i wanna make it easier , but i am really struggling , i am following what is the instructions , but it just doesn't work , i understood that data should be in that format :
Spend = c(2722,2761,2777,2880,3115,3115,3115,
3115,3207,3207,3282,3282,3500,3500,3737,3737,3737,
3737,3737,3737,3737,3737,3737,3737,3737,3737,3737,
3737,3815,3865,3865,4041,4144,4280,4280,4280,4333,4333,
4333,4333,4333,4391,4516,4554,4610,4610,4610,4610,
4610,4610,4610,4610,4610,4610,4610,4610,4610,4610,
4610,4610,4778,4778,4778,4778,4899,5106,5106,5106,5106,
5106,5106,5106,5291,5712,5712,5712,5712,5766,5766,
5800,5800,5800,5800,5800,5800,5800,5800,5800,5800,
5800,5800,5800,5800,5800,5800,5800,5800,5800,5800,5800,
5800,5800,5800,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900,5900,5900,5900,5900,5900,5900,5900,5900,
5900,5900)
)

but i didn't understand how to do it , clicked on addings but it doesn't show the same thing , or do i need to do it manually ?

andresrcs · September 1, 2020, 4:14pm

Assuming your data frame is called TSpend you could simply run dput(TSpend) in your R console and paste the output here.

crepuscule · September 1, 2020, 7:41pm

wow , thank you!!!
structure(list(Fiscal Year = c("2018", "2020", "2018", "2019",
"2016", "2016", "2016", "2016", "2017", "2017", "2020", "2020",
"2019", "2019", "2016", "2016", "2016", "2016", "2016", "2016",
"2016", "2016", "2016", "2016", "2016", "2016", "2016", "2016",
"2019", "2020", "2020", "2017", "2017", "2019", "2019", "2019",
"2016", "2016", "2016", "2016", "2016", "2019", "2016", "2017",
"2016", "2016", "2016", "2016", "2016", "2016", "2016", "2016",
"2016", "2016", "2016", "2016", "2016", "2016", "2016", "2016",
"2018", "2018", "2018", "2018", "2016", "2016", "2016", "2016",
"2016", "2016", "2016", "2016", "2018", "2016", "2016", "2017",
"2017", "2016", "2016", "2018", "2018", "2018", "2017", "2017",
"2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018",
"2017", "2017", "2017", "2017", "2018", "2018", "2018", "2018",
"2018", "2017", "2017", "2020", "2018", "2018", "2018", "2018",
"2018", "2018", "2018", "2018", "2018", "2019", "2019", "2019",
"2019", "2019", "2019", "2019", "2019", "2019", "2019", "2019",
"2019", "2019", "2019", "2019", "2019", "2019", "2019", "2019",
"2019", "2019", "2020", "2020", "2020", "2020", "2020", "2020",
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2018",
"2019", "2020", "2020", "2020", "2020", "2020", "2020", "2020",
"2020", "2019", "2020", "2020", "2020", "2020", "2020", "2020",
"2020", "2020", "2020", "2020", "2018", "2018", "2018", "2018",
"2018", "2018"), Spend € = c(27.22, 27.61, 27.77, 28.8, 31.15,
31.15, 31.15, 31.15, 32.07, 32.07, 32.82, 32.82, 35, 35, 37.37,
37.37, 37.37, 37.37, 37.37, 37.37, 37.37, 37.37, 37.37, 37.37,
37.37, 37.37, 37.37, 37.37, 38.15, 38.65, 38.65, 40.41, 41.44,
42.8, 42.8, 42.8, 43.33, 43.33, 43.33, 43.33, 43.33, 43.91, 45.16,
45.54, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1,
46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 47.78, 47.78, 47.78,
47.78, 48.99, 51.06, 51.06, 51.06, 51.06, 51.06, 51.06, 51.06,
52.91, 57.12, 57.12, 57.12, 57.12, 57.66, 57.66, 58, 58, 58,
58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58,
58, 58, 58, 58, 58, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59)), row.names = c(NA,
-174L), class = c("tbl_df", "tbl", "data.frame"))

crepuscule · September 1, 2020, 7:48pm

here is the correct one :

structure(list(Software - Application Licenses = c("Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses",
"Software - Application Licenses", "Software - Application Licenses"
), Jun = c("Jun", "Aug", "Sep", "Aug", "Aug", "Aug", "Aug", "Sep",
"Sep", "Oct", "Oct", "Dec", "Dec", "Dec", "Feb", "Jan", "Feb",
"Feb", "Mar", "Mar", "Apr", "Apr", "May", "May", "May", "Jun",
"May", "Jun", "Jun", "Aug", "Dec", "Feb", "Jan", "Mar", "Mar",
"Apr", "May", "May", "Jun", "Aug", "Feb", "Mar", "Apr", "Jun",
"Aug", "Feb", "Jun", "Aug", "Feb", "Aug", "Aug", "Mar", "Feb",
"Feb", "Feb", "Apr", "Jan", "Feb", "May", "Feb", "Jan", "Feb",
"May", "May", "May", "May", "Oct", "Jun", "Jun", "Apr", "Dec",
"Dec", "Feb", "Feb", "Mar", "Mar", "Mar", "Jan", "Jan", "Jan",
"Nov", "Nov", "Nov", "Nov", "Feb", "Feb", "Feb", "Feb", "Feb",
"Dec", "Dec", "Oct", "Nov", "May", "Jun", "Jun", "Jun", "Jul",
"Jan", "Jan", "Mar", "Mar", "Mar", "May", "Apr", "Aug", "Oct",
"Oct", "Oct", "Oct", "Jan", "Jan", "Feb", "Feb", "Feb", "Feb",
"Jan", "Jan", "Feb", "Feb", "Jan", "Jan", "Feb", "Feb", "Jan",
"Feb", "Jul", "Nov", "Nov", "Apr", "Apr", "Apr", "Apr", "Apr",
"Sep", "Sep", "Sep"), 2019 = c("2019", "2019", "2019", "2019",
"2019", "2019", "2019", "2019", "2019", "2019", "2019", "2019",
"2019", "2019", "2020", "2020", "2020", "2020", "2020", "2020",
"2020", "2020", "2020", "2020", "2020", "2019", "2019", "2019",
"2019", "2019", "2019", "2020", "2020", "2020", "2020", "2020",
"2020", "2019", "2019", "2019", "2020", "2020", "2020", "2019",
"2019", "2020", "2019", "2019", "2020", "2019", "2019", "2019",
"2020", "2020", "2020", "2020", "2019", "2019", "2019", "2020",
"2019", "2019", "2019", "2019", "2019", "2019", "2019", "2018",
"2018", "2018", "2017", "2017", "2018", "2018", "2018", "2018",
"2018", "2018", "2018", "2018", "2017", "2017", "2017", "2017",
"2018", "2018", "2018", "2018", "2018", "2017", "2017", "2016",
"2016", "2016", "2016", "2017", "2017", "2018", "2016", "2016",
"2016", "2016", "2016", "2016", "2016", "2016", "2018", "2018",
"2018", "2018", "2016", "2016", "2016", "2016", "2016", "2016",
"2016", "2016", "2016", "2016", "2016", "2016", "2016", "2016",
"2016", "2016", "2017", "2016", "2019", "2016", "2016", "2016",
"2016", "2016", "2019", "2019", "2019"), 59 = c(59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59, 59,
58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58, 58,
58, 58, 58, 58, 58, 58, 58, 58, 57.66, 57.66, 57.12, 57.12, 57.12,
57.12, 52.91, 51.06, 51.06, 51.06, 51.06, 51.06, 51.06, 51.06,
48.99, 47.78, 47.78, 47.78, 47.78, 46.1, 46.1, 46.1, 46.1, 46.1,
46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1, 46.1,
45.54, 45.16, 43.91, 43.33, 43.33, 43.33, 43.33, 43.33, 42.8,
42.8, 42.8)), row.names = c(NA, -137L), class = c("tbl_df", "tbl",
"data.frame"))

andresrcs · September 1, 2020, 9:09pm

Your sample data doesn't have headers, and you have too many values per month, I still don't know to which time period they belong, I assume you have weekly data but I can't be sure so I'm going to arbitrarily reduce the data points just to exemplify how to perform time aggregation.

library(tidyverse)
library(lubridate)
library(tsibble)

TSpend <- data.frame(
    stringsAsFactors = FALSE,
    month = c("Jun","Aug","Sep","Aug",
              "Aug","Aug","Aug","Sep","Sep","Oct","Oct","Dec",
              "Dec","Dec","Feb","Jan","Feb","Feb","Mar","Mar","Apr",
              "Apr","May","May","May","Jun","May","Jun","Jun",
              "Aug","Dec","Feb","Jan","Mar","Mar","Apr","May",
              "May","Jun","Aug","Feb","Mar","Apr","Jun","Aug",
              "Feb","Jun","Aug","Feb","Aug","Aug","Mar","Feb",
              "Feb","Feb","Apr","Jan","Feb","May","Feb","Jan",
              "Feb","May","May","May","May","Oct","Jun","Jun",
              "Apr","Dec","Dec","Feb","Feb","Mar","Mar","Mar","Jan",
              "Jan","Jan","Nov","Nov","Nov","Nov","Feb","Feb",
              "Feb","Feb","Feb","Dec","Dec","Oct","Nov","May",
              "Jun","Jun","Jun","Jul","Jan","Jan","Mar","Mar",
              "Mar","May","Apr","Aug","Oct","Oct","Oct","Oct",
              "Jan","Jan","Feb","Feb","Feb","Feb","Jan","Jan",
              "Feb","Feb","Jan","Jan","Feb","Feb","Jan","Feb",
              "Jul","Nov","Nov","Apr","Apr","Apr","Apr","Apr","Sep",
              "Sep","Sep"),
    year = c("2019","2019","2019","2019",
             "2019","2019","2019","2019","2019","2019","2019",
             "2019","2019","2019","2020","2020","2020","2020",
             "2020","2020","2020","2020","2020","2020","2020",
             "2019","2019","2019","2019","2019","2019","2020",
             "2020","2020","2020","2020","2020","2019","2019",
             "2019","2020","2020","2020","2019","2019","2020",
             "2019","2019","2020","2019","2019","2019","2020","2020",
             "2020","2020","2019","2019","2019","2020","2019",
             "2019","2019","2019","2019","2019","2019","2018",
             "2018","2018","2017","2017","2018","2018","2018",
             "2018","2018","2018","2018","2018","2017","2017",
             "2017","2017","2018","2018","2018","2018","2018",
             "2017","2017","2016","2016","2016","2016","2017",
             "2017","2018","2016","2016","2016","2016","2016",
             "2016","2016","2016","2018","2018","2018","2018","2016",
             "2016","2016","2016","2016","2016","2016","2016",
             "2016","2016","2016","2016","2016","2016","2016",
             "2016","2017","2016","2019","2016","2016","2016",
             "2016","2016","2019","2019","2019"),
    value = c(59,59,59,59,59,59,59,59,
              59,59,59,59,59,59,59,59,59,59,59,59,59,59,
              59,59,59,59,59,59,59,59,59,59,59,59,59,59,
              59,59,59,59,59,59,59,59,59,59,59,59,59,59,59,
              59,59,59,59,59,59,59,59,59,59,59,59,59,59,
              59,59,58,58,58,58,58,58,58,58,58,58,58,58,
              58,58,58,58,58,58,58,58,58,58,58,58,57.66,
              57.66,57.12,57.12,57.12,57.12,52.91,51.06,51.06,
              51.06,51.06,51.06,51.06,51.06,48.99,47.78,47.78,
              47.78,47.78,46.1,46.1,46.1,46.1,46.1,46.1,46.1,46.1,
              46.1,46.1,46.1,46.1,46.1,46.1,46.1,46.1,45.54,
              45.16,43.91,43.33,43.33,43.33,43.33,43.33,42.8,
              42.8,42.8)
)

TSpend %>%
    group_by(year, month) %>% 
    summarise(value = mean(value)) %>% # You have too many values per month I have to reduce them to exemplify
    mutate(date = dmy(paste0(01, month, year))) %>% 
    as_tsibble(index = date) %>% 
    index_by(quarter = ~ yearquarter(.)) %>%
    summarise(
        value = sum(value, na.rm = TRUE)
    ) %>% 
    ggplot(aes(quarter, value)) +
    geom_line()

^{Created on 2020-09-01 by the reprex package (v0.3.0)}

Hermes · September 2, 2020, 5:13am

It's possible to improve the result, bring them closer to the results shown in link [link]., Using the arguments [ end, delta, frequency]?

ts(TSpend$Spend, start = c(2016, 1),end=c(2020, 1), frequency = 4)
     Qtr1 Qtr2 Qtr3 Qtr4
2016 2722 2761 2777 2880
2017 3115 3115 3115 3115
2018 3207 3207 3282 3282
2019 3500 3500 3737 3737
2020 3737               
myts <- ts(TSpend$Spend, start = c(2016, 1),end=c(2020, 1), frequency = 4)
 autoplot(myts)

ts_obj_ggplot

crepuscule · September 3, 2020, 7:52am

@andresrcs Thank you so much ! I tried it but it shows me an error regarding the function as_tsibble , and when i try to install the package tsibble , it doesn't work
I have a question the technique that you use to import the data , does it work

andresrcs · September 3, 2020, 12:09pm

You have to be more specific, What error message do you get?

I don't understand what you mean, Can you clarify?

system · September 24, 2020, 12:10pm

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.