CODEWITHSUNDEEP
r
user1606540
user1606540

Reputation: 79

using melt() on Date column headers

This is how my excel looks like below. I want to use these Dates ( column name "cols" in x4" ) to plot a graph.

enter image description here

structure(list(Date = c("DBS SP Equity", "ST SP Equity", "OCBC SP Equity", 
"UOB SP Equity", "WIL SP Equity"), Industry = c("Banks", "Telecommunications", 
"Banks", "Banks", "Food"), `43402` = c(-0.00476190476190486, 
0.00958466453674123, 0.000961538461538458, 0.00332363938512659, 
0), `43399` = c(-0.0257275411218895, -0.00634920634920633, -0.0188679245283018, 
-0.0352705410821643, 0.00643086816720251), `43398` = c(-0.0161825726141079, 
0.00318471337579607, -0.00375939849624074, -0.00279776179056757, 
0.0130293159609121), `43397` = c(0.00542344597413447, 0.00319488817891389, 
0.00757575757575757, 0.00481927710843388, 0.00655737704918025
), `43396` = c(-0.0156057494866531, -0.0126182965299685, -0.0231267345050878, 
-0.015810276679842, 0)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

data4 <- read_excel("data.xlsx", sheet = 4, col_names = TRUE)
    x4 <- melt(data4, id = c("Industry"), measure.vars = 3:997,  variable = "cols")
    print(x4$cols)

The value is a integer No when I print it.
I have tried but its shows NA for Cols

x4$cols <- as.Date(x4$cols, format = "%yyyy-%mm-%dd")

I have also tried the following code . It displays int no again.

x4 <- x4 %>% mutate(Date = dmy(cols))

How do I get the actual Date?

Upvotes: 0

Views: 230

Answers (1)

russellpierce
russellpierce

Reputation: 4711

The actual date is the integer offset (origin) from 1970-01-01. You'll probably convert back with something like as.Date(x4$cols, origin="1970-01-01"). See also: https://stats.idre.ucla.edu/r/faq/how-does-r-handle-date-values/

Upvotes: 1

Related Questions