r - Error in as.POSIXlt.character(x, tz, …) : character string is not in a standard unambiguous format

Question

I use a data set imported from excel:

OBX <- read_excel("C:/Users/neri_/Desktop/OBX Weekly Return.xlsx")

         Date          OBX
1   2010-12-28    0.0071366
2   2010-12-29  4.97265e-05
3   2010-12-30  -0.00452489
4   2011-01-03   0.00896603
5   2011-01-04   -0.0101488
6   2011-01-05   0.00520143
7   2011-01-06 -0.000422917
8   2011-01-07  -0.00301145
9   2011-01-10  -0.00341996
10  2011-01-11     0.013326
11  2011-01-12   0.00882484

I'm struggling with this error message,

"Error in as.POSIXlt.character(x, tz, ...) : character string is not in a standard unambiguous format", which I'm encountering when I'm trying to run the code:

weeklydata<-to.weekly(OBX).

I want to convert daily data data to weekly data, how do I modify the date format, so I can run the to.weekly command?

Appreciate your help!

Answer 1

You may use date2ISOweek() from the ISOweek package. Using stringr for pattern updating and dplyr to produce the week-results; The code would be like this, after loading the packages.

library(ISOweek)
library(stringr)
library(dplyr)

# mocking up some data 
OBX <- data.frame(
  Date = c("2010-12-28", "2010-12-29", "2010-12-30", "2010-12-31", "2011-01-03", "2011-01-04"),
  OBX = 101:106
)

# transform form month- to week-based and trim some
OBX$dateWeek <- date2ISOweek(OBX$Date)

# get rid of the preceeding "W", next drop day-of-week
OBX$dateWeek <- stringr::str_replace(OBX$dateWeek, "-W", "-")
OBX$dateWeek <- stringr::str_replace(OBX$dateWeek, "-.$", "")

# sum per week and print
OBX %>% 
  group_by(dateWeek) %>%
  summarise(weekSum = sum(OBX))

# the results of the mocked data
# A tibble: 2 × 2
  dateWeek weekSum
     <chr>   <int>
1  2010-52     410
2  2011-01     211

Please let me know whether this is an answer fitting your needs.