Search code examples
rdateextractdate-format

Convert date column to date format and extract month and year

I have the data table dt.data, where the column begTime is a character-column with a special date format. The date format is the following: 01-MON-YY. This is how my data table looks like:

dt.data <- structure(list(begTime = c("01-JAN-23", "01-JAN-23", "01-JAN-23", 
"01-FEB-23", "01-FEB-23", "01-FEB-23", "01-MAR-23", "01-MAR-23", 
"01-MAR-23", "01-APR-23", "01-APR-23", "01-APR-23", "01-MAY-23", 
"01-MAY-23", "01-MAY-23", "01-JUN-23", "01-JUN-23", "01-JUN-23", 
"01-JUL-23", "01-JUL-23", "01-JUL-23", "01-AUG-23", "01-AUG-23", 
"01-AUG-23", "01-SEP-23", "01-SEP-23", "01-SEP-23", "01-OCT-23", 
"01-OCT-23", "01-OCT-23", "01-NOV-23", "01-NOV-23", "01-NOV-23", 
"01-DEC-23", "01-DEC-23", "01-DEC-23"), toBook = c("KG21", "KG64", 
NA, "KG21", "KG64", NA, "KG21", "KG64", NA, "KG21", "KG64", NA, 
"KG21", "KG64", NA, "KG21", "KG64", NA, "KG21", "KG64", NA, "KG21", 
"KG64", NA, "KG21", "KG64", NA, "KG21", "KG64", NA, "KG21", "KG64", 
NA, "KG21", "KG64", NA), sumQ = c(1440, 200.947775009365, 
-3978.571120032, 0, -17812.0922910065, 19996.296169952, 0, 12995.1445322506, 
-15165.299710032, 2160, -53923.0554299322, 54824.803160056, 504, 
-7045.79687321736, 8256.75532999996, 0, -14919.1153921838, 13458.1132399999, 
0, -15966.4071659115, 15083.67141, 720, -18714.0358020241, 18129.3816599999, 
240, 2905.88494461573, -6727.47018, 0, 39413.3865474728, -36206.19812, 
0, -9407.5891668633, 9800.53, 0, -17781.1490225234, 17946.678
), unit = c("MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", 
"MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", 
"MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", 
"MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", "MWh", 
"MWh", "MWh"), sumValue = c(108891.36, 128053.530976637, 
-273632.88711977, 0, -1165755.80930466, 1274897.88251747, 0, 
512406.636632069, -617755.657019838, 102372.48, -2495006.82184836, 
2522765.0072208, 15463.728, -305363.977539244, 336017.268406819, 
0, -487578.86709908, 445164.689526293, 0, -517033.916204391, 
475791.067501664, 26482.32, -640698.403786333, 612188.309980192, 
8319.6, 96201.4521993924, -243811.777077578, 0, 1582012.15513231, 
-1467874.96096514, 0, -429850.119024673, 434161.39860219, 0, 
-719195.470550453, 724300.2)), row.names = c(NA, -36L), class = c("data.table", 
"data.frame"))

Now I need help with the following two questions:

  1. How can I convert the character-column to a standard R date-column with the date format 01-01-2023?
  2. How can I extract the month and the year of this column to create a new column, which looks like this JAN2023, ... DEC2023?
Solution

  • seems you are using data.table:

    library(data.table)
dt.data[, new_date := as.Date(begTime, "%d-%b-%y")
       ][, md := format(new_date, "%b%Y")]

      begTime toBook        sumQ unit   sumValue   new_date      md
 1: 01-JAN-23   KG21   1440.0000  MWh   108891.4 2023-01-01 Jan2023
 2: 01-JAN-23   KG64    200.9478  MWh   128053.5 2023-01-01 Jan2023
 3: 01-JAN-23   <NA>  -3978.5711  MWh  -273632.9 2023-01-01 Jan2023
 4: 01-FEB-23   KG21      0.0000  MWh        0.0 2023-02-01 Feb2023
 5: 01-FEB-23   KG64 -17812.0923  MWh -1165755.8 2023-02-01 Feb2023
 6: 01-FEB-23   <NA>  19996.2962  MWh  1274897.9 2023-02-01 Feb2023
 7: 01-MAR-23   KG21      0.0000  MWh        0.0 2023-03-01 Mar2023
 8: 01-MAR-23   KG64  12995.1445  MWh   512406.6 2023-03-01 Mar2023
 9: 01-MAR-23   <NA> -15165.2997  MWh  -617755.7 2023-03-01 Mar2023
10: 01-APR-23   KG21   2160.0000  MWh   102372.5 2023-04-01 Apr2023

    If using tidyverse, you could do:

    library(tidyverse)

dt.data %>% 
  mutate(new_date = dmy(begTime), md = format(new_date, "%b%Y"))

# A tibble: 6 × 7
  begTime   toBook    sumQ unit   sumValue new_date   md     
  <chr>     <chr>    <dbl> <chr>     <dbl> <date>     <chr>  
1 01-JAN-23 KG21     1440  MWh     108891. 2023-01-01 Jan2023
2 01-JAN-23 KG64      201. MWh     128054. 2023-01-01 Jan2023
3 01-JAN-23 NA      -3979. MWh    -273633. 2023-01-01 Jan2023
4 01-FEB-23 KG21        0  MWh          0  2023-02-01 Feb2023
5 01-FEB-23 KG64   -17812. MWh   -1165756. 2023-02-01 Feb2023
6 01-FEB-23 NA      19996. MWh    1274898. 2023-02-01 Feb2023