Autoplot doesn't recognize time(Year , months)

crepuscule · August 31, 2020, 3:53pm

Hello ,

i am new to R and R studio and i am trying to build a timeseries , but i am struggling because the function doesn't recognize time nor the spend data .

here is the code , and the how the plot looks like :

library(ggplot2)
library(ggfortify)
library(readxl)
library(zoo)
library(forecast)

TSpend <- read_excel("TSpend.xlsx")
View(TSpend)

myts <- ts(TSpend$Spend €, start = c(2016, 1), frequency = 4)

View(myts)
autoplot(myts)

Thank you!!

here is a sample of the dataset

Fiscal Year	Spend €
2018	€ 27,22
2020	€ 27,61
2018	€ 27,77
2019	€ 28,80
2016	€ 31,15
2016	€ 31,15
2016	€ 31,15
2016	€ 31,15
2017	€ 32,07
2017	€ 32,07
2020	€ 32,82
2020	€ 32,82
2019	€ 35,00
2019	€ 35,00
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2016	€ 37,37
2019	€ 38,15
2020	€ 38,65
2020	€ 38,65
2017	€ 40,41
2017	€ 41,44
2019	€ 42,80
2019	€ 42,80
2019	€ 42,80
2016	€ 43,33
2016	€ 43,33
2016	€ 43,33
2016	€ 43,33
2016	€ 43,33
2019	€ 43,91
2016	€ 45,16
2017	€ 45,54
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2016	€ 46,10
2018	€ 47,78
2018	€ 47,78
2018	€ 47,78
2018	€ 47,78
2016	€ 48,99
2016	€ 51,06
2016	€ 51,06
2016	€ 51,06
2016	€ 51,06
2016	€ 51,06
2016	€ 51,06
2016	€ 51,06
2018	€ 52,91
2016	€ 57,12
2016	€ 57,12
2017	€ 57,12
2017	€ 57,12
2016	€ 57,66
2016	€ 57,66
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2017	€ 58,00
2017	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2017	€ 58,00
2017	€ 58,00
2017	€ 58,00
2017	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2018	€ 58,00
2017	€ 58,00
2017	€ 58,00
2020	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2019	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2018	€ 59,00
2019	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2019	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2020	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00
2018	€ 59,00

andresrcs · August 31, 2020, 4:13pm

Hi!

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

crepuscule · August 31, 2020, 6:19pm

Hello ,

Thank you for answering.

I added a sample of the dataset that i am working with.

andresrcs · August 31, 2020, 6:30pm

Ok but that is still not reproducible nor copy/paste friendly, please read the guide I gave you and try to make a proper reproducible example.

crepuscule · August 31, 2020, 6:51pm

I am not quiet sure i understood , i think all the points mentioned are respected .

could you be more specific please ? what do i need to add ?

andresrcs · August 31, 2020, 8:01pm

It seems you are avoiding to read the guide, I sincerely think I can't be more specific than the instructions on the guide I linked for you, but to try to clarify, the points that are not respected are:

The sample data is not on a copy/paste friendly format.
Your example is not self-contained since I can't simply copy it and run it into my own R session.
The code is not minimal, since you are including unnecessary library calls and View() commands.

As an example, this would be more of a proper reprex, Can you explain your problem in the context of this example?

library(ggplot2)
library(ggfortify)

# Sample data on a copy/paste friendly format
TSpend <- data.frame(
    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(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)
)

myts <- ts(TSpend$Spend, start = c(2016, 1), frequency = 4)
autoplot(myts)

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

crepuscule · September 1, 2020, 1:28pm

Thank you for that , i am new to this that's why i didn't get fully your answer

should i redo the same thing and edit my post ?

andresrcs · September 1, 2020, 2:00pm

I still don't understand your issue, if you could explain it using the reprex I provided, then it would be no need to make another one, but if it doesn't show your problem then you should make a new reprex that it does.

crepuscule · September 1, 2020, 2:03pm

okay i will try to explain :
if you see in the plot above , the time axes has 2020-2030-2040 etc , and i have no idea where this comes from ,the data that i have is for the years 2016-2020
or it shows this :

but the data that i have is just the year and tha value
hope this clarifies

andresrcs · September 1, 2020, 2:11pm

That is because you are creating a time series with the ts command and since you have more values per year in your data than the frequency you have specified so you end up with more years.

It is still not clear to me what kind of plot you are trying to get

crepuscule · September 1, 2020, 2:20pm

when i put in frequency 52 instead , it shows the correct years , but i want to to have is the data by quaters , but it doesn't work

andresrcs · September 1, 2020, 2:45pm

Ok, then you have to perform some sort of time aggregation. In your sample data you are showing several values per year and they are not even in order, How do you know to which time period they belong? Is there another time related variable in your actual data set?

crepuscule · September 1, 2020, 2:58pm

yes there is another column in the intial dataset where we have months , how can i perform this time aggregation ?

andresrcs · September 1, 2020, 3:01pm

I think you can do it with the ts approach but I'm not used to work with that so I can't tell you how but tou can also use packages like tsibble or tibbletime, if you provide suitable sample data I can give you an example

crepuscule · September 1, 2020, 3:07pm

I searched but i couldn't find anything on how to do it ,

here is another sample of data :

Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Sep	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Sep	2019	€ 59,00
Software - Application Licenses	Sep	2019	€ 59,00
Software - Application Licenses	Oct	2019	€ 59,00
Software - Application Licenses	Oct	2019	€ 59,00
Software - Application Licenses	Dec	2019	€ 59,00
Software - Application Licenses	Dec	2019	€ 59,00
Software - Application Licenses	Dec	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Jan	2020	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Mar	2020	€ 59,00
Software - Application Licenses	Mar	2020	€ 59,00
Software - Application Licenses	Apr	2020	€ 59,00
Software - Application Licenses	Apr	2020	€ 59,00
Software - Application Licenses	May	2020	€ 59,00
Software - Application Licenses	May	2020	€ 59,00
Software - Application Licenses	May	2020	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Dec	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Jan	2020	€ 59,00
Software - Application Licenses	Mar	2020	€ 59,00
Software - Application Licenses	Mar	2020	€ 59,00
Software - Application Licenses	Apr	2020	€ 59,00
Software - Application Licenses	May	2020	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Mar	2020	€ 59,00
Software - Application Licenses	Apr	2020	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Jun	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Aug	2019	€ 59,00
Software - Application Licenses	Mar	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Apr	2020	€ 59,00
Software - Application Licenses	Jan	2019	€ 59,00
Software - Application Licenses	Feb	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	Feb	2020	€ 59,00
Software - Application Licenses	Jan	2019	€ 59,00
Software - Application Licenses	Feb	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	May	2019	€ 59,00
Software - Application Licenses	Oct	2019	€ 59,00
Software - Application Licenses	Jun	2018	€ 58,00
Software - Application Licenses	Jun	2018	€ 58,00
Software - Application Licenses	Apr	2018	€ 58,00
Software - Application Licenses	Dec	2017	€ 58,00
Software - Application Licenses	Dec	2017	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Mar	2018	€ 58,00
Software - Application Licenses	Mar	2018	€ 58,00
Software - Application Licenses	Mar	2018	€ 58,00
Software - Application Licenses	Jan	2018	€ 58,00
Software - Application Licenses	Jan	2018	€ 58,00
Software - Application Licenses	Jan	2018	€ 58,00
Software - Application Licenses	Nov	2017	€ 58,00
Software - Application Licenses	Nov	2017	€ 58,00
Software - Application Licenses	Nov	2017	€ 58,00
Software - Application Licenses	Nov	2017	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Feb	2018	€ 58,00
Software - Application Licenses	Dec	2017	€ 58,00
Software - Application Licenses	Dec	2017	€ 58,00
Software - Application Licenses	Oct	2016	€ 57,66
Software - Application Licenses	Nov	2016	€ 57,66
Software - Application Licenses	May	2016	€ 57,12
Software - Application Licenses	Jun	2016	€ 57,12
Software - Application Licenses	Jun	2017	€ 57,12
Software - Application Licenses	Jun	2017	€ 57,12
Software - Application Licenses	Jul	2018	€ 52,91
Software - Application Licenses	Jan	2016	€ 51,06
Software - Application Licenses	Jan	2016	€ 51,06
Software - Application Licenses	Mar	2016	€ 51,06
Software - Application Licenses	Mar	2016	€ 51,06
Software - Application Licenses	Mar	2016	€ 51,06
Software - Application Licenses	May	2016	€ 51,06
Software - Application Licenses	Apr	2016	€ 51,06
Software - Application Licenses	Aug	2016	€ 48,99
Software - Application Licenses	Oct	2018	€ 47,78
Software - Application Licenses	Oct	2018	€ 47,78
Software - Application Licenses	Oct	2018	€ 47,78
Software - Application Licenses	Oct	2018	€ 47,78
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Jan	2016	€ 46,10
Software - Application Licenses	Feb	2016	€ 46,10
Software - Application Licenses	Jul	2017	€ 45,54
Software - Application Licenses	Nov	2016	€ 45,16
Software - Application Licenses	Nov	2019	€ 43,91
Software - Application Licenses	Apr	2016	€ 43,33
Software - Application Licenses	Apr	2016	€ 43,33
Software - Application Licenses	Apr	2016	€ 43,33
Software - Application Licenses	Apr	2016	€ 43,33
Software - Application Licenses	Apr	2016	€ 43,33
Software - Application Licenses	Sep	2019	€ 42,80
Software - Application Licenses	Sep	2019	€ 42,80
Software - Application Licenses	Sep	2019	€ 42,80

andresrcs · September 1, 2020, 3:08pm

Please post it on a copy paste friendly format

crepuscule · September 1, 2020, 3:10pm

i just copied the data and pasted it , what do you mean ?

andresrcs · September 1, 2020, 3:11pm

Again, read the guide on the link I gave you. I can't easily copy the data on that format into my own R session. If you don't believe me, give it a try. Try to copy what you have posted into R and use it as a dataframe.

crepuscule · September 1, 2020, 3:32pm

because i copied the data from excel , and what i normally do is i import it in R , but if this doesn't work for you , how can i do it differently?

andresrcs · September 1, 2020, 3:33pm

Read the guide, it explains how to provide sample data on proper format.

Have in mind that people here volunteers their time to help others with their problems, the least you can do in return (and a polite thing to do) is to make it as easy as possible by following good practices.