3

thanks for your help in advance. i am working with the getQuote function in the quantmod package, which returns the following data frame:

enter image description here

is there a way to modify all the dates in the first column to exclude the time stamp, while retaining the data frame structure? i just want the "YYYY-MM-DD" in the first column. i know that if it was a vector of dates, i would use substr(df[,1],1,10). i have also looked into the apply function, with: apply(df[,1],1,substr,1,10).

jonnie
  • 745
  • 1
  • 13
  • 22

5 Answers5

4

Another option not mentioned yet:

tt <- getQuote("AAPL")
trunc(tt[,1], units='days')

This returns the date in POSIXlt. You can wrap it in as.POSIXct, if you want.

Matthew Plourde
  • 43,932
  • 7
  • 96
  • 113
  • Looks nice and descriptive, but actually using it is uglier: `tt[,1]=as.POSIXct(trunc(tt[,1],units="days"))` gives me "_'origin' must be supplied_" – Darren Cook Jan 17 '13 at 09:20
  • @DarrenCook, hmm, I get no such error. I'm not very familiar with quantmod. When you do `str(tt)`, is the first column `Trade Time` in `POSIXct`? – Matthew Plourde Jan 17 '13 at 12:17
  • @MatthewPlourde It doesn't do it for me now either (a fresh R session). Sorry, it must have been noise, something leftover in my R session. – Darren Cook Jan 17 '13 at 12:56
  • `as.Date` would be even more succinct, and not give you a nasty `POSIXlt` – hadley Jan 17 '13 at 18:39
  • @hadley, sure, `trunc` is more general, though. and either way, if OP needs POSIXct, he'll have to convert. `as.Date` might be faster in this case... – Matthew Plourde Jan 17 '13 at 19:12
2

using ?strptime

tt <- getQuote("AAPL")
tt[,1]
[1] "2013-01-16 02:52:00 CET"
as.POSIXct(strptime(tt[,1],format ='%Y-%m-%d')) ## as.POSIXct because strptime returns POSIXlt
[1] "2013-01-16 CET"

EDIT

You can use the format argument of POSIXct, but you need to convert the tt[,1] to character before.

as.POSIXct(as.character(tt[,1]),format ='%Y-%m-%d')
[1] "2013-01-16 CET"
agstudy
  • 119,832
  • 17
  • 199
  • 261
  • `as.POSIXct` takes a `format` argument, you don't need `strptime` if you want a `POSIXct` object. – Matthew Plourde Jan 16 '13 at 20:18
  • this looks good, i looked into strptime, too. but, how do i apply this to the entire data frame? for multiple stocks? something like: apply(tt[,1],1,strptime,format ='%Y-%m-%d') – jonnie Jan 16 '13 at 20:27
  • @user1499626 you don't need to apply, just use it as in the example – rrs Jan 16 '13 at 20:32
  • @MatthewPlourde thanks! but I prefer strptime method in this case. You can see my update – agstudy Jan 16 '13 at 21:09
  • @agstudy, oh, what does `tt[,1]` come as? from your example it looked like it was already `character`. – Matthew Plourde Jan 16 '13 at 21:14
  • @MatthewPlourde it is a POSIXct , and it comes from `getQuote` as the OP in his question. I mentioned strptime because the help about formatting is in strptime not in POSIXct. – agstudy Jan 16 '13 at 21:18
  • +1 oh, then there are other options, `trunc` and `cut`, as well. can't check right now, but I'm curious which is fastest. – Matthew Plourde Jan 16 '13 at 21:27
2

I would do this with lubridate

library(plyr)
library(lubridate)

tickers <- c("AAPL","AAJX","ABR")
df <- ldply(tickers, getQuote)
rownames(df) <- tickers

df[,"Trade Time"] <- paste(year(df[,"Trade Time"]),month(df[,"Trade Time"]),day(df[,"Trade Time"]),sep="-")

There might be a more elegant way of printing the date, but this is what came to me first.

rrs
  • 9,615
  • 4
  • 28
  • 38
  • You shouldn't need `as.character` in `ymd_hms` - lubridate should automatically coerce, even if it's a factor. – hadley Jan 16 '13 at 20:46
  • Hi @hadley, I'm a big fan and Rice alumnus! I tried it without the as.character and it gave me an error: `Error in parse_date_time(dates, orders, tz = tz, locale = locale, quiet = quiet) : No formats could be infered from the training set.` The `getQuote` function returns `POSIXct`. – rrs Jan 16 '13 at 20:59
  • cool! if it's a `POSIXct` already then you could skip the `ymd_hms` step altogether. – hadley Jan 16 '13 at 21:16
  • Hadley, the first time I tried that it didn't work. I'll edit my answer. – rrs Jan 16 '13 at 21:18
0

You may just use gsub. No need to convert data type.

tt <- getQuote("AAPL")
tt[, 'Trade Time']<- gsub(" [0-9]{2}:[0-9]{2}:[0-9]{2}", "", tt[, 'Trade Time'])
CHP
  • 16,981
  • 4
  • 38
  • 57
0

It can be as simple as:

tt[,1]=as.Date(tt[,1])

(where tt is tt <- getQuote("AAPL"), as shown in the alternative answers)

The blank before the comma means "do all rows" and the 1 after the comma means "operate on (just) the first column".

I prefer this solution because it gives you a Date object, which must be exactly what you want if you are trying to strip off timestamps.

agstudy's answer give you a date with a timezone, and that is going to bite you the first time you run your script in a different timezone. (Aside: I got some regressions in a unit test suite when I ran them in the U.K. while there at Christmas, due to a subtle timezone assumption in my test code.)

Darren Cook
  • 27,837
  • 13
  • 117
  • 217