1

I was trying to use

library(dplyr)
library(tidyr)
library(stringr)

# Dataframe has "Date" column and date in the format "dd/mm/yyyy" or "dd/m/yyyy"
df <- data.frame(Date = c("10/1/2001", "15/01/2010", "15/2/2010", "20/02/2010", "25/3/2010", "31/03/2010"))

# extract into three columns
df %>% extract(Date, c("Day", "Month", "Year"), "([^/]+), ([^/]+), ([^)]+)")

But above code is returning:

   Day Month Year
1 <NA>  <NA> <NA>
2 <NA>  <NA> <NA>
3 <NA>  <NA> <NA>
4 <NA>  <NA> <NA>
5 <NA>  <NA> <NA>
6 <NA>  <NA> <NA>

How to correctly extract the dates in the result as expected:

   Day Month Year
1 10  1 2010
2 15  1 2010
3 15  2 2010
4 20  2 2010
5 25  3 2010
6 31  3 2010
LeMarque
  • 733
  • 5
  • 21

4 Answers4

4

Might be easier to use separate in this case

df %>% 
  separate("Date", into=c("Day","Month","Year"), sep="/") %>% 
  mutate(Month=str_replace(Month, "^0",""))

That will keep everything as character values. If you want the values to be numeric, use

df %>% 
  separate("Date", into=c("Day","Month","Year"), sep="/", convert=TRUE)
MrFlick
  • 195,160
  • 17
  • 277
  • 295
  • just one question, if i wanted to keep month and date in the formats such as 01, 02, 03.... 11, 12, .... 30, 31 , is it possible in here? – LeMarque Sep 24 '21 at 05:29
  • 1
    Use the first option and skip the `mutate()` step. That's just there to remove the leading zero. If you want to add missing zeros, then you can use one of the options here: https://stackoverflow.com/questions/5812493/how-to-add-leading-zeros – MrFlick Sep 24 '21 at 05:32
  • Agree. but that will keep the format for month as "1" from the date 15/1/2010, instead "01" isn't it? but actually i was thinking to have "01", instead "1". Looking for a workaround method :-) – LeMarque Sep 24 '21 at 05:36
2

Your regex pattern is off. Use this version:

df %>% extract(Date, c("Day", "Month", "Year"), "(\\d+)/(\\d+)/(\\d+)")
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

We could use lubridate:

library(lubridate)
library(dplyr)
df %>% 
    mutate(Date = dmy(Date), # if your Date column is character type
           across(Date, funs(year, month, day)))

        Date Date_year Date_month Date_day
1 2001-01-10      2001          1       10
2 2010-01-15      2010          1       15
3 2010-02-15      2010          2       15
4 2010-02-20      2010          2       20
5 2010-03-25      2010          3       25
6 2010-03-31      2010          3       31
TarJae
  • 72,363
  • 6
  • 19
  • 66
  • Thanks :) - just one question, if I wanted to keep month and date in the formats such as 01, 02, 03.... 11, 12, .... 30, 31 , is it possible in this code? or will require some other tweaking? – LeMarque Sep 24 '21 at 05:30
  • 1
    You could add `mutate(Date_month_char = sprintf("%02d", Date_month))`. This will give you a character column. Or if you want the month names abbreviated: `mutate(Date_month_abbr = month(Date_month, label = TRUE))` – TarJae Sep 24 '21 at 06:04
0

We may use read.table from base R

read.table(text = df$Date, sep="/", header = FALSE, 
     col.names = c("Day", "Month", "Year"))
  Day Month Year
1  10     1 2001
2  15     1 2010
3  15     2 2010
4  20     2 2010
5  25     3 2010
6  31     3 2010
akrun
  • 874,273
  • 37
  • 540
  • 662