1

I have a dataframe containing a DATE column which I reference as NM_DATA$DATE. It is a numeric in the form of yyyymmdd, I know, a hundred threads on this...

I tried every solution I could find and they won't work for me. About to reload RStudio.

NM_DATA$DATES <- as.Date(NM_DATA$DATES, "%Y%m%d")

returns

Error in as.Date.default(NM_DATA$DATES, "%Y%m%d") : do not know how to convert 'NM_DATA$DATES' to class "Date"

Can someone please explain how to get DATE column:

DATE
19870401
19870501
etc

into:

DATE
1987-04
1987-05

Any help is greatly appreciated!

zx8754
  • 52,746
  • 12
  • 114
  • 209
user1680636
  • 51
  • 1
  • 6
  • I should clarify that I will be averaging yearly data but would like to be able to reference monthly patterns in the future.... that is why I would like to split for now but keep the month in this data set – user1680636 Apr 01 '14 at 03:51
  • `library(lubridqte); year(ymd(NM_DATA$DATE))`? Can you be more specific about the error? – David LeBauer Apr 01 '14 at 03:53
  • NM_DATA$DATE <- library(lubridate); year(ymd(NM_DATA$DATE)) all formats failed to parse, no formats found... library(lubridate); year(ymd(NM_DATA$DATE)) returned years in the console that looked correct.... – user1680636 Apr 01 '14 at 04:03
  • 2
    In your hours of search, did you come across [this](http://stackoverflow.com/questions/17518564/format-date-in-r-yyyymmdd) or [this](http://stackoverflow.com/questions/18116388/r-read-dates-in-format-yyyymmdd)? :) – jbaums Apr 01 '14 at 04:06
  • yes and yes.... cant get either to work... – user1680636 Apr 01 '14 at 04:15
  • Error in as.Date.default(NM_DATA$DATES, "%Y%M%D") : do not know how to convert 'NM_DATA$DATES' to class "Date" when i use NM_DATA$DATES <- as.Date(NM_DATA$DATES, "%Y%M%D") oops... fixed %M%D to %m%d... still same error – user1680636 Apr 01 '14 at 04:16
  • @jbaums your second example shows the same solution as here, except the question itself is not worded very well. The example provided here presents the question in a more understandable way, imo. – Alex Apr 01 '14 at 04:19

3 Answers3

6

In the interest of having a complete answer here, I'm fleshing out @Alex's answer somewhat.

The as.Date function can be used to convert a character string (or vector thereof) to Date format. The help page for strptime (?strptime) gives some valuable information about possible formats that Date objects can use.

In your case, you want to convert the NM_DATA$DATE vector to dates. The format yyyymmdd is represented by %Y%m%d, thus if your vector is character, we can convert it like so:

NM_DATA$DATE <- as.Date(NM_DATA$DATE, format='%Y%m%d')

However, if it is numeric (instead of character), we first need to coerce to character to avoid an 'origin' must be supplied error. (You could check the mode of the vector with mode(NM_DATA$DATE).)

NM_DATA$DATE <- as.Date(as.character(NM_DATA$DATE), format='%Y%m%d')

Now that the vector is a Date object, we can format it in various ways (outlined at ?strptime). To extract year, month and day numbers:

NM_DATA$YEAR <- format(NM_DATA$DATE, '%Y')
NM_DATA$MONTH <- format(NM_DATA$DATE, '%m')
NM_DATA$DAY <- format(NM_DATA$DATE, '%d')

If you want month name, instead, you can use %B (or %b, for abbreviated month names), e.g.:

NM_DATA$MONTHNAME <- format(NM_DATA$DATE, '%B')
jbaums
  • 27,115
  • 5
  • 79
  • 119
  • I tried NM_DATA$DATE <- as.Date(as.character(NM_DATA$DATE), format='%Y%m%d') however I only get a 4 digit character in the DATE column... – user1680636 Apr 01 '14 at 04:27
  • 1
    Then there must be some peculiarities about your `DATE` column... not all elements are `yyyymmdd`. Do some elements only have a year, rather than the full date? This would also explain your error `do not know how to convert 'NM_DATA$DATES' to class "Date"`, and changes the question completely, from one of "how do I convert yyyymmdd to a Date object", to "how do I convert a vector of mixed date formats to a Date object". The answer to the latter is that you need to make them consistent first, by converting to character and performing string manipulations. – jbaums Apr 01 '14 at 04:30
  • it is 145.208 rows long.... I am 90% sure they are all formatted yyyymmdd... I am going to bed. Will try again tomorrow. thanks again for all your help – user1680636 Apr 01 '14 at 04:32
  • 2
    Try `unique(nchar(NM_DATA$DATES))` or `NM_DATA$DATES[which(nchar(NM_DATA$DATES) != 8)]` – jbaums Apr 01 '14 at 04:34
3

Use the format option with the right syntax to process the 8 digits:

as.Date("20130408",format="%Y%m%d")

Then, use the the format() command to extract what you want, see Extract month and year from a zoo::yearmon object

Community
  • 1
  • 1
Alex
  • 15,186
  • 15
  • 73
  • 127
  • unexpected string constant in NM_DATA$DATE <- as.Date(NM_DATA$DATE, "20130408", format "%Y%m%d") also tried as.Date( "20130408", format "%Y%m%d") to print to console and no luck... same error – user1680636 Apr 01 '14 at 04:08
  • 1
    @user1680636: More specifically, try `NM_DATA$DATE <- as.Date(NM_DATA$DATE, format='%Y%m%d')`. Then, to split into components: `NM_DATA$MONTH <- format(NM_DATA$DATE, '%m')` and `NM_DATA$DAY <- format(NM_DATA$DATE, '%d')`. Take a look at `?strptime` for other possible formats, e.g. `%b` for abbreviated month name. – jbaums Apr 01 '14 at 04:09
  • origin must be supplied... tried origin = "1970-01-01" returns error unexpected symbol – user1680636 Apr 01 '14 at 04:09
  • 1
    You are missing equals signs in your code posted here as comments... `format='%Y%m%d'`. – jbaums Apr 01 '14 at 04:12
  • 1
    This method is the way to go and works as described if the data is a character vector, e.g.: `test <- "19870401"; format(as.Date(test,"%Y%m%d"),"%Y-%m")` Try breaking your issue down to a simple example that works and then find where it fails. – thelatemail Apr 01 '14 at 04:32
2

Maybe you can try substr according to your request.

YEAR<-as.numeric(substr(as.numeric(NM_DATA$DATE),1,4))
MONTH<-as.numeric(substr(as.numeric(NM_DATA$DATE),5,6))

I agree @Alex answer is more elegant.

YIn Li
  • 21
  • 1