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.

Answer 2

Convert with as.Date function:

 mydate<-"2010-12-28 0.0071366"
 mydate<-as.Date(mydate,"%Y-%m-%d")
 format(mydate,"%Y-%U")

Answer 3

I am fairly new to R. I saved the file with the name

gdpOz <- read_excel("C:/Users/stand/Documents/1.Excel/GDP_2013-18.xlsx",sheet="Sheet1")

it gave the same error it is finicky and unpredictable with read.excel, So I tried this to convert,

Date.POSIXlt(Oz[23,],format , tryFormats = c("%Y-%m-%d", "%Y/%m/%d")

and many other combinations blindly without success, What I found was that excel file save as below saved the file as general text which proved completely unmanageable no matter what. Excel save as xlsx

However if you save as a csv file after converting to short date format as belowexcel short date csv format

Then use this command in R

Oz2 <- read.csv.zoo("C:/Users/stand/Documents/1.Excel/gdpOz.csv")

It converts without any worries, the problem lies in excels date format with xlsx files, and R much prefers csv files. For some reason everytime you save or convert any files, if you try and resave the same file,you have to change the name of the file otherwise it never works, no matter what. Most people who are not spreadsheet technicians or power users probably never encounter too many excel problems with R, or maybe they get it every time? Who knows?