R: How to parse a string of dates in dataframe, split it, and return the date closest to today?

user_15508528 0
Viewed 68

I really apologize for the question but I am new to R. I have a dataframe with 2 columns. The first column contains names and the second one a string of dates in text format ("YYYYMMDD"). I am trying to split the string in the second column and get the closest day to today for every row in the dataframe. Below is a sample of the dataframe:

Column 1  Column2 
Name 1    20190607,20200901,20211203           
Name 2  20190607,20200901,20211203

The end result should look like the sample dataframe but with a column to the right showing the dates closest to today. In this example it would be 20211203. Thank you in advance for any help

r
3 Answers

We may use separate_rows to split the 'Column2' at the ,, into new rows, then convert the 'Column2' to Date class (ymd from lubridate) and slice the rows where the difference from today is the minimum (after grouping by 'Column1')

library(dplyr)
library(tidyr)
library(lubridate)
df2 <- df1 %>%
    separate_rows(Column2, sep=",\\s*") %>%
    mutate(Column2 = ymd(Column2)) %>%
    group_by(Column1) %>%
    slice(which.min(abs(Column2 - today()))) %>%
    ungroup

-output

df2
# A tibble: 2 x 2
  Column1 Column2   
  <chr>   <date>    
1 Name 1  2021-12-03
2 Name 2  2021-12-03

data

df1 <- structure(list(Column1 = c("Name 1", "Name 2"), Column2 = c("20190607,20200901,20211203", 
"20190607,20200901,20211203")), 
class = "data.frame", row.names = c(NA, 
-2L))
user_37322710 
DF = read.csv(text = "'Name 1';'20190607,20200901,20211203'
'Name 2';'20190607,20200901,20211203'", sep=";", header=F, quote="'")

DF
     V1                          V2
1 Name 1 20190607,20200901,20211203 
2 Name 2  20190607,20200901,20211203

strsplit(DF[[2]], ',') |>
lapply(\(x) as.Date(x,format="%Y%m%d")) |> 
lapply(max) # `max` gets you the closest date to today since a date class is internally represented in integer using origin 1970 (i.e. 0).
[[1]]
[1] "2021-12-03"

[[2]]
[1] "2021-12-03"

You can assign the result to the data.frame as a new column.

user_97822170

Split the string on comma, take difference of current date with all other dates, return the date with minimum difference for each row.

Here is a base R option -

df$Column3 <- as.Date(sapply(strsplit(df$Column2, ','), function(x) {
  x <- as.Date(x, '%Y%m%d')
  x[which.min(abs(x - Sys.Date()))]
}), origin = '1970-01-01')
df

#  Column1                    Column2    Column3
#1  Name 1 20190607,20200901,20211203 2021-12-03
#2  Name 2 20190607,20200901,20211203 2021-12-03
user_39629140
Related