r- dynamically detect excel column names format as date (without df slicing)

user_10292638 0
Viewed 40

I am trying to detect column dates that come from an excel format:

library(openxlsx)

df <- read.xlsx('path/df.xlsx', sheet=1, detectDates = T)

Which reads the data as follows:

#  a b  c  44197 44228 d
#1 1 1 NA      1      1 1
#2 2 2 NA      2      2 2
#3 3 3 NA      3      3 3
#4 4 4 NA      4      4 4
#5 5 5 NA      5      5 5

I tried to specify a fix index slice and then transform these specific columns as follows:

names(df)[4:5] <-  format(as.Date(as.numeric(names(df)[4:5]),
                                  origin = "1899-12-30"), "%m/%d/%Y")

This works well when the df is sliced for those specific columns, unfortunately, there could be the possibility that these column index's change, say from names(df)[4:5] to names(df)[2:3] for example. Which will return coerced NA values instead of dates.

data:

  • Note: for this data the column name is read as X4488, while read.xlsx() read it as 4488
df <- data.frame(a=rep(1:5), b=rep(1:5), c=NA, "44197"=rep(1:5), '44228'=rep(1:5), d=rep(1:5))

Expected Output: Note: this is the original excel format for these above columns:

#  a b  c  01/01/2021 01/02/2021 d
#1 1 1 NA      1      1          1
#2 2 2 NA      2      2          2
#3 3 3 NA      3      3          3
#4 4 4 NA      4      4          4
#5 5 5 NA      5      5          5

How could I detect directly these excel format and change it to date without having to slice the dataframe?

dataframe r excel
1 Answers

We may need to only get those column names that are numbers

i1 <- !is.na(as.integer(names(df)))

and then use

names(df)[i1] <-  format(as.Date(as.numeric(names(df)[i1]),
                                  origin = "1899-12-30"), "%m/%d/%Y")

Or with dplyr

library(dplyr)
df %>%
    rename_with(~ format(as.Date(as.numeric(.), 
          origin = "1899-12-30"), "%m/%d/%Y"), matches('^\\d+$'))
user_37322710
Related