Convert character column into date column (month column)

Question

I have the following data table, where the column month is a character-column. I want to convert this into a date format.

    structure(list(delivYear= c("2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018"
    ), year = c("2014", "2014", "2014", "2014", "2014", 
    "2014", "2014", "2014", "2014", "2014", "2014", "2014", "2015", 
    "2015", "2015", "2015", "2015", "2015", "2015", "2015", "2015", 
    "2015", "2015", "2015", "2016", "2016", "2016", "2016", "2016", 
    "2016", "2016", "2016", "2016", "2016", "2016", "2016", "2017", 
    "2017", "2017", "2017", "2017", "2017", "2017", "2017", "2017", 
    "2017", "2017", "2017", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018"), q = c("Q1", 
    "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4", 
    "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", 
    "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", 
    "Q4", "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", 
    "Q4", "Q4", "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", 
    "Q3", "Q4", "Q4", "Q4"), month = c("Jan", "Feb", "Mar", "Apr", 
"May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", 
"Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", 
"Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", 
"Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", 
"May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", 
"Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", 
"Nov", "Dec"), nrOrders = c("0", "0", "0", "0", "0", 
    "0", "0", "0", "1", "1", "2", "0", "2", "4", "5", "3", "7", "3", 
    "5", "4", "3", "7", "8", "7", "2", "24", "16", "33", "9", "27", 
    "16", "10", "27", "9", "31", "35", "11", "11", "25", "15", "18", 
    "19", "19", "8", "27", "34", "43", "51", "0", "11", "2", "0", 
    "0", "0", "0", "0", "4", "5", "1", "0"), eur = c("0", "0", "0", 
    "0", "0", "0", "0", "0", "180", "8760", "17760", "0", "6730", 
    "5354", "2840", "22190", "29210", "8725", "7226", "12500", "2090", 
    "13780", "21434", "15719", "29000", "112366", "38148", "125900", 
    "10285", "54281", "23777", "111512", "76907", "18306", "124393", 
    "103220", "8890", "52082", "119095", "28482", "62841", "54356", 
    "54119", "42999", "112459", "169798", "123808", "504226", "0", 
    "55336", "3017", "0", "0", "0", "0", "0", "15880", "1844", "252", 
    "0"), yearMonth = c("2014_Jan", "2014_Feb", "2014_Mrz", 
    "2014_Apr", "2014_Mai", "2014_Jun", "2014_Jul", "2014_Aug", "2014_Sep", 
    "2014_Okt", "2014_Nov", "2014_Dez", "2015_Jan", "2015_Feb", "2015_Mrz", 
    "2015_Apr", "2015_Mai", "2015_Jun", "2015_Jul", "2015_Aug", "2015_Sep", 
    "2015_Okt", "2015_Nov", "2015_Dez", "2016_Jan", "2016_Feb", "2016_Mrz", 
    "2016_Apr", "2016_Mai", "2016_Jun", "2016_Jul", "2016_Aug", "2016_Sep", 
    "2016_Okt", "2016_Nov", "2016_Dez", "2017_Jan", "2017_Feb", "2017_Mrz", 
    "2017_Apr", "2017_Mai", "2017_Jun", "2017_Jul", "2017_Aug", "2017_Sep", 
    "2017_Okt", "2017_Nov", "2017_Dez", "2018_Jan", "2018_Feb", "2018_Mrz", 
    "2018_Apr", "2018_Mai", "2018_Jun", "2018_Jul", "2018_Aug", "2018_Sep", 
    "2018_Okt", "2018_Nov", "2018_Dez")), row.names = c(NA, -60L), class = c("data.table", 
    "data.frame")

How can I handle this to get the month-column from character to date format?

If none of these solutions works, please add the code you've tried and did not produce the desired result: https://stackoverflow.com/search?q=%5Br%5D+character+to+date — I_O, Aug 28 '23 at 09:50

score 1 · Accepted Answer · answered Aug 28 '23 at 09:51

1

For your specific case, if your data.frame is stored in variable a, you would do this:

b <- paste(a$year,a$month,"01")
b <- as.Date(b, format = "%Y %B %d")
a <- cbind(a, myDate=b)

Does this work for you?

answered Aug 28 '23 at 09:51

NicolasH2

774
5
20

The problem is: For the months march, may, october and december I get NAs (after step 2). I think here is a problem with the local settings. Furthermore I want to have the same column input as in column ``month`` but when using ``str()``, I want to get date and not character column. – MikiK Aug 28 '23 at 10:29
I would see that this is a problem if you would use the yearMonth column, because there the months are in german abbreviation (e.g. Mrz instead of Mar), but in your month column that is not the case. Never heard of regional settings effecting as.Date(), but you can of course try to replace line 2 with this: `b <- as.Date(gsub("Mar","Mrz",b), format = "%Y %B %d")` – NicolasH2 Aug 28 '23 at 11:29

score 0 · Answer 2 · answered Aug 28 '23 at 11:03

0

Suppose the data table is dt, then do this:

dt$month = factor(dt$month, levels = month.abb)

answered Aug 28 '23 at 11:03

MikiK

398
6
19

Convert character column into date column (month column)

2 Answers2