58014

Converting integer format date to double format of date

Question:

I have date format in following format in a data frame:

Jan-85 Apr-99 1-Nov Feb-96

When I see the typeof(df$col) I get the answer as "integer".

Actually when I see the format in excel it is in m/d/yyyy format. I was trying to convert this to date format in R. All my efforts yielded NA.

I tried parse_date_time function. I tried as.date along with as.character. I tried as.POSIXct but everything is giving me NA.

My trials were as follows and everything was a failure:

as.Date.numeric(df$col,"m%d%Y") transform(df$col, as.Date(as.character(df$col), "%m%d%Y")) as.Date(df$col,"m%d%Y") as.POSIXct.numeric(as.character(loan_new$issue_d), format="%Y%m%d") as.POSIXct.date(as.character(df$col), format="%Y%m%d") mdy(df$col) parse_date_time(df$col,c("mdy"))

How can I convert this to date format? I have used lubridate package for parse_date_time and mdy package.

dput output is below

Label <- factor(c("Apr-08", "Apr-09", "Apr-10", "Apr-11", "Aug-07", "Aug-08", "Aug-09", "Aug-10", "Aug-11", "Dec-07", "Dec-08", "Dec-09", "Dec-10", "Dec-11", "Feb-08", "Feb-09", "Feb-10", "Feb-11", "Jan-08", "Jan-09", "Jan-10", "Jan-11", "Jul-07", "Jul-08", "Jul-09", "Jul-10", "Jul-11", "Jun-07", "Jun-08", "Jun-09", "Jun-10", "Jun-11", "Mar-08", "Mar-09", "Mar-10", "Mar-11", "May-08", "May-09", "May-10", "May-11", "Nov-07", "Nov-08", "Nov-09", "Nov-10", "Nov-11", "Oct-07", "Oct-08", "Oct-09", "Oct-10", "Oct-11", "Sep-07", "Sep-08", "Sep-09", "Sep-10", "Sep-11"))

Answer1:

NA is typically what you get when you misspecify the format. Which is what you do. That said, if your data is really looking like the first example you gave, it's impossible to simply convert this to a date. You have two different formats, one being month-year and the other day-month.

If your updated date (i.e. Dec-11) is the correct format, then you use the format argument of as.Date like this:

date <- "Dec-11" as.Date(date, format = "%b-%d") # [1] "2017-12-11"

Or on your example data:

as.Date(Label, format = "%b-%d") # [1] "2017-04-08" "2017-04-09" "2017-04-10" "2017-04-11" "2017-08-07" "2017-08-08" # [7] "2017-08-09" "2017-08-10" "2017-08-11" "2017-12-07" "2017-12-08" "2017-12-09"

If you want to convert something like Jan-85, you have to decide which day of the month that date should have. Say we just take the first of each month, then you can do:

x <- "Jan-85" xd <- paste0("1-",x) as.Date(xd, "%d-%b-%y") # [1] "1985-01-01"

More information on the format codes can be found on ?strptime

Note that R will automatically add this year as the year. It has to, otherwise it can't specify the date. In case you do not have a day of the month (eg like Jan-85), conversion to a date is impossible because the underlying POSIX algorithms don't have all necessary information.

Also keep in mind that this only works when your locale is set to english. Otherwise you have a big chance your OS won't recognize the month abbreviations correctly. To do so, do eg:

Sys.setlocale(category = "LC_TIME", locale = "English_United Kingdom")

You can later set it back to the original one if you must, or restart your R session to reset the locale settings.

<strong>note</strong>: Please check carefully which locale notations are valid for your OS. The above example works on Windows, but is not guaranteed on either Linux or Mac.

<strong>Why you see integer</strong>

The fact that these string values are of integer type, is due to the fact that R automatically convert character vectors to factors when reading in a data frame. So typeof() returns integer because that's the internal representation of a factor.

Recommend

  • How to convert varchar to date only when it contains a valid date?
  • Looking up data within a file versus merging
  • jquery datepicker remove .ui-state-highlight class onSelect event
  • Parse datetime with lubridate
  • Converting integer format date to double format of date
  • `gather` can't handle rownames
  • How to separate date and time
  • Change multiple character columns to date
  • Implementing span to click on plot and move in R shiny
  • Force lowercase .htaccess
  • Cumulative sum of values by month, filling in for missing months
  • Is it possible to plot two variables using a third one as filter in R?
  • datetime strftime does not output correct timestamp
  • How to configure setenv.sh for tomcat8?
  • Airflow basic auth - cannot create user
  • Catching UnknownHostException of RetrievalUrl in WorldWindJava 2.1 in Netbeans
  • UNIX:Two directories with same name on the same path? [closed]
  • Firefox version for selenium-firefox-driver 2.41.0
  • How to have a difference in week units between two days (even if they're close but belong to di
  • multiple colors on beanplot in R
  • Trying to find the last non-empty cell in a specific row/range over multiple sheets
  • R Error - cannot change value of locked binding for 'df'
  • AWS Elastic Beanstalk - changing owner of webapp folder
  • x86: Count transitions from 1 to 0 in 32 bit number
  • How to generate Date Series in HIVE? (Creating table)
  • How to Cache Images Dynamically-Generated with PHP?
  • How to parse utc date
  • How can you tell if a Python program has anything to read from stdin?
  • rapply over a nested list in R
  • php is_file always return false
  • Geom_jitter colour based on values
  • rewrite uppercase url to lowercase url htaccess
  • Shouldn't else be indented in the below code
  • Replace value with Factor in r data.table
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal