How to Input Date as Headers into R without it getting Converted into Number

Question

I am working with a data frame which has date in the first row as given below:

However, when I input them these dates get converted to numbers as shown below:

I would like this to be dates in the same format as in the image above.

> library(readxl)
> Book1 <- read_excel("C:/X/X/X/Book1.xlsx",skip=1)
> View(Book1)

The data frame is given below:

structure(list(ds = c("ABC", "ABX", "ABZ"), `44866` = c(0, 0, 
0), `44896` = c(0, 0, 0), `44927` = c(0, 0, 0), `44958` = c(0, 
0, 0), `44986` = c(0, 0, 0), `45017` = c(0, 0, 0)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -3L))

Can someone share work around on this?

akrun · Accepted Answer · 2022-11-08 17:56:07Z

2

An option is to use rename_with on the numeric columns and convert to DateTime with openxlsx::convertToDateTime

library(dplyr)
df1 %>% 
  rename_with(~ as.character(openxlsx::convertToDateTime(as.numeric(.x))), 
   -ds)

-output

# A tibble: 3 × 7
  ds    `2022-11-01` `2022-12-01` `2023-01-01` `2023-02-01` `2023-03-01` `2023-04-01`
  <chr>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
1 ABC              0            0            0            0            0            0
2 ABX              0            0            0            0            0            0
3 ABZ              0            0            0            0            0            0

edited Nov 8, 2022 at 17:56

answered Nov 8, 2022 at 16:25

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quinten · Accepted Answer · 2022-11-08 16:20:32Z

First, convert the column names to numeric and then to a date and finally define the format you want like this:

df <- structure(list(ds = c("ABC", "ABX", "ABZ"), `44866` = c(0, 0, 
                                                              0), `44896` = c(0, 0, 0), `44927` = c(0, 0, 0), `44958` = c(0, 
                                                                                                                          0, 0), `44986` = c(0, 0, 0), `45017` = c(0, 0, 0)), class = c("tbl_df", 
                                                                                                                                                                                        "tbl", "data.frame"), row.names = c(NA, -3L))
names(df)[-1] <- format(as.Date(as.numeric(names(df)[-1]), origin = "1899-12-30"), "%Y-%m-%d")
df
#>    ds 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01
#> 1 ABC          0          0          0          0          0          0
#> 2 ABX          0          0          0          0          0          0
#> 3 ABZ          0          0          0          0          0          0

^{Created on 2022-11-08 with reprex v2.0.2}

Collectives™ on Stack Overflow

How to Input Date as Headers into R without it getting Converted into Number

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related