I have an XTS timeseries in R of the following format and am trying to do some processing, subsetting and re-arranging before exporting as a CSV for work in another program.
head(master_1)
S_1
2010-03-03 00:00:00 2.8520
2010-03-03 00:30:00 2.6945
2010-03-03 01:00:00 2.5685
2010-03-03 01:30:00 2.3800
2010-03-03 02:00:00 2.2225
2010-03-03 02:30:00 2.0650
and
str(master_1)
An ‘xts’ object from 2010-03-03 to 2010-05-25 08:30:00 containing:
Data: num [1:4000, 1] 2.85 2.69 2.57 2.38 2.22 ...
- attr(*, "dimnames")=List of 2
..$ : NULL
..$ : chr "S_1"
Indexed by objects of class: [POSIXt,POSIXct] TZ:
Original class: 'zoo'
xts Attributes:
List of 1
$ dateFormat: chr "Date"
And I would like to convert this to a data.frame so I can manipulate it more easily and then export to another program. However, when I use test1 <- as.data.frame(master_1)
the test1 does have the Index (i.e. the dates and times) visible,
head(test1)
S_1
2010-03-03 00:00:00 2.8520
2010-03-03 00:30:00 2.6945
2010-03-03 01:00:00 2.5685
2010-03-03 01:30:00 2.3800
2010-03-03 02:00:00 2.2225
2010-03-03 02:30:00 2.0650
But the Index is not shown,
str(test1)
'data.frame': 4000 obs. of 1 variable:
$ S_1: num 2.85 2.69 2.57 2.38 2.22 ...
And writing a csv write.csv(master_1, file="master_1.csv")
does not include the time or date. Why is this, and how can I include the data/time data as a column, so it is used in other R commands and exported properly?
Thanks for any help.
That's because the dates are rownames in your data.frame. You need to make them a separate column.
Try this:
data.frame(date=index(master_1), coredata(master_1))
This is a bit of a sidebar, but the fortify(...)
function in package ggplot2
will convert a variety of objects to data frames suitable for use in ggplot(...)
, including xts
objects.
library(xts)
set.seed(1) # for reproducible example
master_1 <- xts(rnorm(10,mean=2,sd=0.1),as.POSIXct("2010-03-03")+30*(0:9))
library(ggplot2)
df <- fortify(master_1)
head(df)
# Index master_1
# 1 2010-03-03 00:00:00 1.937355
# 2 2010-03-03 00:00:30 2.018364
# 3 2010-03-03 00:01:00 1.916437
# 4 2010-03-03 00:01:30 2.159528
# 5 2010-03-03 00:02:00 2.032951
# 6 2010-03-03 00:02:30 1.917953
So if you're already using gggplot
this is an easy way to do it. Note that the index goes into a column named Index
(capital "I").
Since 1.9.6
You can convert directly from/to xts
without losing index class. As simple as:
as.data.table(master_1)
The index is added as the first column in the result data.table
, it retains index Date
or POSIXct
classes.
You can convert an xts object to a data.frame that includes the index as a column named "Index" with zoo::fortify.zoo()
.
You don't need ggplot2, but this will still work if you have xts (or zoo) and ggplot2 loaded.
For example:
library(xts)
data(sample_matrix)
x <- as.xts(sample_matrix, dateFormat = "Date")
my_df <- fortify.zoo(x)
head(my_df)
# Index Open High Low Close
# 1 2007-01-02 50.03978 50.11778 49.95041 50.11778
# 2 2007-01-03 50.23050 50.42188 50.23050 50.39767
# 3 2007-01-04 50.42096 50.42096 50.26414 50.33236
# 4 2007-01-05 50.37347 50.37347 50.22103 50.33459
# 5 2007-01-06 50.24433 50.24433 50.11121 50.18112
# 6 2007-01-07 50.13211 50.21561 49.99185 49.99185
str(my_df)
# 'data.frame': 180 obs. of 5 variables:
# $ Index: Date, format: "2007-01-02" "2007-01-03" ...
# $ Open : num 50 50.2 50.4 50.4 50.2 ...
# $ High : num 50.1 50.4 50.4 50.4 50.2 ...
# $ Low : num 50 50.2 50.3 50.2 50.1 ...
# $ Close: num 50.1 50.4 50.3 50.3 50.2 ...
Shane is right. you might be looking for index(your xts). Here's a reproducible example.
library(xts)
example(xts)
x = head(sample.xts)
datefield = index(x)
newdf = data.frame(x,datefield)
Then you should be able to simply export it to a csv. Of course you can rename the rows, too.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With