Date format and graph

Tati · July 15, 2021, 9:10am

Good morning everyone,

I want to represent a graph with dates on the x-axis and prices on the y-axis. When I import the CVS document, I have no problem. Each type of information is in its column, and the dates are in a suitable format. But when I try to graph this data. I get non-conforming lines, and the date format is not the same (now: 0002, 0004, 0006, 0008, 0010, etc...). What can I do?
I thank you in advance.

Here is my code and the pictures:
#Put the data in a matrix p
p <-read.csv("JSE_Price_index_and_Total_Returns1.csv", header = TRUE , sep = ",")

names(p)
names(p)<-c("Date","Price Index","Total Return Index")

#Adjust maybe date format - i don't knowv

????

#Representer Price Index - plot:
plot(as.Date(p$Date),p$Price Index, type = "l", col="black", lwd = 1)

HanOostdijk · July 15, 2021, 9:53am

Your 'dates' are obviously not the type and format that R recognizes as dates.
Show us a little part of your data. For example with head(p) that shows the first six rows.

Tati · July 15, 2021, 11:34am

Hello,

Thanks for your answer: Sure I can show you a little part of my data.

As you can see, there is no problem with the format. So I don't understand why I get a graph like that .

Tati · July 15, 2021, 11:36am

HanOostdijk · July 15, 2021, 1:01pm

I think that there is a problem with your format.
Probably your Date is not numerical but character and the as.Date comversion guesses the wrong order of days, months and years.
The lubridate package helps with handling all kind of date conversion.
See the following example where I think you will want to do the second conversion:

p <- data.frame(
  stringsAsFactors = F,
  Date = c("06/05/2006","06/06/2006","06/07/2006"),
  Price_Index = c(100,90.7,86.2),
  Tot_Ret_Index = c(100,90.68,86.19)
)

p$Date1 = lubridate::dmy(p$Date)
p$Date2 = lubridate::parse_date_time(p$Date,"%m/%d/%Y")

head(p)
#>         Date Price_Index Tot_Ret_Index      Date1      Date2
#> 1 06/05/2006       100.0        100.00 2006-05-06 2006-06-05
#> 2 06/06/2006        90.7         90.68 2006-06-06 2006-06-06
#> 3 06/07/2006        86.2         86.19 2006-07-06 2006-06-07

plot(p$Date1,p$Price_Index, type = "p", col="black", lwd = 1)


plot(p$Date2,p$Price_Index, type = "p", col="black", lwd = 1)


Created on 2021-07-15 by the reprex package (v2.0.0)

Tati · July 15, 2021, 1:26pm

Thank you very much! I will try this right away.

However, my data table contains almost 1585 observations.
So how can I use the lubridate package without putting one by one the data in the "c" vectors?
I want to be able to convert all the elements in my data frame to numeric.

HanOostdijk · July 15, 2021, 1:29pm

Hello @Tati ,

because I do not have your data file, I had to prepare an example data.frame myself.
In your case you can use the data.frame that you have read from the csv-file,
so no need to type in any data.

Tati · July 15, 2021, 1:58pm

Okay thanks HanOostdijk ,

I just don't know how to code this including all my data frame. I'm new in R.

Here is a copy in pdf format of my data frame.

Maybe I replace Date = c("06/05/2006","06/06/2006","06/07/2006") by :

Date = p$Date
I replace these ones Price_Index = c(100,90.7,86.2), Tot_Ret_Index = c(100,90.68,86.19)
by : Price_Index = p$Price_Index, Tot_Ret_Index = p$Tot_Ret_Index

Thanks in advance

Tati · July 15, 2021, 2:00pm

I am a new user so I cannot upload the pdf file

but Imagine the first 6 data are the elements of my data frame. How can I just put all the date without doing it, one by one?

HanOostdijk · July 15, 2021, 2:47pm

I understood that you already could read the data.
In that case slightly adjust your code to


p <-read.csv("JSE_Price_index_and_Total_Returns1.csv", header = TRUE , sep = ",")

names(p)<-c("Date","Price_Index","Tot_Ret_Index")

# install.packages("lubridate") # when not yet installed: uncomment this line

p$Date2 = lubridate::parse_date_time(p$Date,"%m/%d/%Y")

plot(p$Date2,p$Price_Index, type = "l", col="black", lwd = 1)

Tati · July 15, 2021, 3:27pm

Hello HanOostdijk,

I tried it but it doesn't work. I get a linear line and the date are no more in the x abscisse(because I want the prices in the x abscisse):
I did the following code:

#Put the data in a matrix p
p <-read.csv("JSE_Price_index_and_Total_Returns1.csv", header = TRUE , sep = ",")

names(p)
names(p)<-c("Date","Price Index","Total Return Index")

#Adjust date format

install.packages("lubridate") # when not yet installed: uncomment this line

library (lubridate)
p$Date2 = lubridate::parse_date_time(p$Date,"%m/%d/%Y")

#Representer Price Index - plot:
plot(p$Date2, p$Price_Index, type = "l", col="black", lwd = 1)

#Another way to plot:

plot(as.Date(p$Date2),p$Price_Index, type = "l", col="black", lwd = 1)

Here are the class of Date and of Date2:

class(p$Date2)
[1] "POSIXct" "POSIXt"
class(p$Date)
[1] "character"

Here is the output in the table :

head(p)
Date Price Index Total Return Index Date2
1 06/05/2006 100.0 100.00 2006-06-05
2 06/06/2006 90.7 90.68 2006-06-06
3 06/07/2006 86.2 86.19 2006-06-07
4 06/08/2006 82.5 82.47 2006-06-08
5 06/09/2006 94.8 94.85 2006-06-09
6 06/12/2006 88.2 88.25 2006-06-12

And here are the linear graph : I get the same with other way of plotting the data:

Linear line like that (Google Search)
NB: I can't post picture of the graph

Please, I don't know how to solve it.

Thanks in advance.

Tati

Tati · July 15, 2021, 3:28pm

nirgrahamuk · July 15, 2021, 3:36pm

As a beginner to R you may benefit from studying this useful book.
https://r4ds.had.co.nz/
In particular
3 Data visualisation | R for Data Science (had.co.nz)

HanOostdijk · July 15, 2021, 3:55pm

Hello @Tati ,

you are confusing me.
You show me some code and therefore I suppose that you have executed it.
For instance you did rename the names of the columns and after that you show the first lines with head.
The result shows the names as Date Price Index Total Return Index Date2
When I do the same I get

 head(p)
        Date Price_Index Tot_Ret_Index      Date2
1 06/05/2006       100.0        100.00 2006-06-05
2 06/06/2006        90.7         90.68 2006-06-06
3 06/07/2006        86.2         86.19 2006-06-07

and I would except to see the same names in your output. So something is wrong.

I notice that you are editing the code and its output in this conversation and its easy (and therefore confusing) to make a mistake in that. The best way to avoid that is just copying the contents of the R-console into the window where you pose or answer questions . When you use three backticks (`) before and after your copied text it will be correctly readable.

Also read about the possibilities of the reprex package. See e.g. here

I second the suggestion of @nirgrahamuk for a good introductory text.

Tati · July 15, 2021, 5:58pm

Hello @HanOostdijk,

Thanks for your message.
I'm sorry to disturb you with my code. Thanks for the advice. I will read the document presented by
nirgrahamuk. Yes, before I tell you if the code works or not, I execute it.

But @HanOostdijk, I don't understand very well. By renaming the lines. I get the same result as you.

I think that the error is rather in the code to represent the graph.
I get this : (same as you)

Do you have a correct graphi with this code?

When I use this code:

there is no transformation of dates in numeric. But the class becomes this one:

class(p$Date2)
[1] "POSIXct" "POSIXt"
class(p$Date)
[1] "character"

Is this normal?

Thanks in advance.