Convert Multiple Columns of Numeric Data to Dates in R

Convert multiple columns of numeric data to dates in R

You may need a function that tells R that these integers represent dates, and then you need to apply that function to each column of your dataframe:

myfun <- function(x) as.Date(x, format="%Y-%m-%d", origin="1899-12-30")
xlnew <- data.frame(lapply(xl, myfun))

You can avoid all this by passing function anonymously as well or like one of the answers. Also, options(stringsAsFactors = FALSE) is also set in my environment for unwanted changes to factors.

Logic:

Excel date starts from 1900-01-01 with the index of 1, However R date usually we consider origin at 1970-01-01. There is a difference of 70 years, plus 1 day difference of indexing as R first date starts with the index of 0 not 1.Also, there is a bug in excel due to its historical reason where Excel considers 29-Feb-1900 a valid date, which is not true. Hence we should subtract 2 days ( 1 day difference due to indexing other 1 day due to the bug in excel) from the actual origin (which is 1900-01-01 of excel) to get the correct date.

Output of first 5 rows:

> xlnew
             D         D1         D2         D3
1   1991-09-02 2011-12-09 2011-06-01 2011-10-01
2   1952-08-07 2011-12-13 1985-06-07 2011-10-01
3   1951-11-15 2012-02-05 1993-06-15 2012-01-05
4   1969-03-04 2012-02-08 2010-08-01 2012-02-01
5   1939-12-02 2012-02-02 2009-07-01 2012-02-01

Convert multiple character columns to as.Date and time in R

You just need to tweak the arguments of mutate_at. Any additional arguments to as.Date are specified as arguments to mutate_at.

df2 <- df %>% mutate_at(vars(enter,disposal,date), as.Date, format="%Y-%m-%d")

The second part of your question has a similar solution.

df2 <- df2 %>% mutate_at(vars(clock, rolex), function(x) chron(times. = x))

Convert factor to date class for multiple columns

Given the format of your dates, you may try this:

# sample data
df <- data.frame(a = 1:2,
                 d1 = factor(c("2013/01/01", "2014/01/01")),
                 d2 = factor(c("2013/01/01", "2014/01/01")),
                 b = 3:4)

df[ , 2:3] <- lapply(df[ , 2:3], as.Date)

str(df)
# 'data.frame':  2 obs. of  4 variables:
# $ a : int  1 2
# $ d1: Date, format: "2013-01-01" "2014-01-01"
# $ d2: Date, format: "2013-01-01" "2014-01-01"
# $ b : int  3 4

Select multiple date columns and convert factor variables to date

Updated answer based on new data provided.

Try the following. There's no need to strip out the time component of the date-time string. You can parse it using the lubridate function which matches the data (in this case, dmy_hm()) then disregard it.

dfq_parsed <- dfq %>%
  mutate(across(contains("date", ignore.case = TRUE), dmy_hm))

This yields:

         ID Date_Construct   Date_use  Comp
1      0001     2018-03-10 2007-08-02 Revis
2     34560     2015-03-21 2007-10-31  Succ
3 100041531     2012-02-20 2008-08-13 Revis

Where the dates are as POSIXct, but that's easy enough to work with:

'data.frame':   3 obs. of  4 variables:
 $ ID            : chr  "0001" "34560" "100041531"
 $ Date_Construct: POSIXct, format: "2018-03-10" "2015-03-21" "2012-02-20"
 $ Date_use      : POSIXct, format: "2007-08-02" "2007-10-31" "2008-08-13"
 $ Comp          : chr  "Revis" "Succ" "Revis"

Loop to convert to date for multiple columns in R

We can use a function from lubridate, along with across within mutate:

library(tidyverse)

df %>%
    mutate(across(starts_with("date"), 
                  ~lubridate::parse_date_time(.,orders = c("mdy", "ymd"))))

#   id      date1      date2
# 1  a 2003-06-10 2003-07-15
# 2  b 2006-05-12 2010-10-01

converting multiple columns from character to numeric format in r

You could try

DF <- data.frame("a" = as.character(0:5),
                 "b" = paste(0:5, ".1", sep = ""),
                 "c" = letters[1:6],
                 stringsAsFactors = FALSE)

# Check columns classes
sapply(DF, class)

#           a           b           c 
# "character" "character" "character" 

cols.num <- c("a","b")
DF[cols.num] <- sapply(DF[cols.num],as.numeric)
sapply(DF, class)

#          a           b           c 
#  "numeric"   "numeric" "character"

convert numeric column to dates recognized by R

We can use dmy from lubridate

library(lubridate)
newdate <- dmy(date)

newdate
#[1] "1958-10-29" "1957-12-10" "1953-09-27" "1960-02-23" "1967-03-06" "1968-01-10" "1958-10-10" "1992-10-09"

and get the difference between the new date in years

as.integer(difftime(as.Date('2016-12-31'), newdate, units = 'days')/365)
#[1] 58 59 63 56 49 49 58 24

Converting numbers to dates in R

Here is a way to convert multiple values in one statement into Dates
(assuming yyyy mm dd). Here we target all columns that end with "date" in their name.

library(dplyr)


df <- data.frame(update_date = c(20190101, 20190102, 20190103),
       end_date = c(20200101, 20200102, 20200103))

df %>% mutate_at(vars(ends_with("date")), ~as.Date(as.character(.x),format="%Y%m%d"))

You might similarly use

mutate_at(vars(starts_with("date")) 

or

mutate_at(vars(c(update_date, end_date) 

---

Related Topics