Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
Error in as.POSIXlt.character(x, tz, …) : character string is not in a standard unambiguous format

Ask 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!
                Do you want to convert the data from a month-based format like  2012-12-30 to a week-based format like 2012-52-6? By the way, be sure to test around the year ends.
– KoenV
                Apr 17, 2017 at 13:02
                Please consider taking a moment to read more about formatting your posts, as that will help everybody trying to help. For example, use code blocks (for code, obviously) and blockquotes for error messages. Read more here.
– Barthy
                Apr 17, 2017 at 13:31
                I just want to convert the daily prices to weekly prices. Normally, this isn't a issue when I download stock prices directly from Yahoo Finance through Quantmod. However, the problem arises when I import the data from an excel file.
– Neri Kim
                Apr 17, 2017 at 13:59
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.
                Thank you for your effort! I tried your code and I don't think it is the best fit for the answer  I am looking for I'm afraid. As I mentioned earlier, this example works completely fine:                                                     #download data from Yahoo Finance
– Neri Kim
                Apr 17, 2017 at 18:58
                I'm not sure if this solved my issue. I tried to use your code and then run to.weekly command. But I got the same error
– Neri Kim
                Apr 17, 2017 at 14:01
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?
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.