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
Improve this question

4 Answers 4

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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?
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: stackoverflow.com/questions/5812493/how-to-add-leading-zeros
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 :-)
2

Your regex pattern is off. Use this version:

df %>% extract(Date, c("Day", "Month", "Year"), "(\\d+)/(\\d+)/(\\d+)")
Improve this answer

Comments

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
Improve this answer

2 Comments

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?
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))
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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.