CODEWITHSUNDEEP
rdplyr
Yogesh Kumar
Yogesh Kumar

Reputation: 659

How do I insert dates based on conditions in R (example given)?

df is my current dataset and I want to insert dates from 1st Jan'2020 to 4th Jan'2020 for all possible locations . 

df<-data.frame(location=c("x","x","y"),date=c("2020-01-01","2020-01-04","2020-01-03"))

This is what my expected dataset look like .

expected_df<-data.frame(location=c("x","x","x","x","y","y","y","y"),date=c("2020-01-01","2020-01-02","2020-01-03","2020-01-04","2020-01-01","2020-01-02","2020-01-03","2020-01-04"))

  location       date
1        x 2020-01-01
2        x 2020-01-02
3        x 2020-01-03
4        x 2020-01-04
5        y 2020-01-01
6        y 2020-01-02
7        y 2020-01-03
8        y 2020-01-04

Upvotes: 1

Views: 52

Answers (2)

user2332849
user2332849

Reputation: 1450

It is essential that you place "stringsAsFactor = FALSE" in your data frame so those values do not get transformed into factors.

df <- data.frame(location=c("x","x","y"), date=c("2020-01-01","2020-01-04","2020-01-03"), stringsAsFactors = F)


'['(
  expand.grid(
  date = seq.Date(from=min(as.Date(df$date)), to=max(as.Date(df$date)), by = "day"),
  location = unique(df$location)
  ),
  c(2,1)
)

Output

  location       date
1        x 2020-01-01
2        x 2020-01-02
3        x 2020-01-03
4        x 2020-01-04
5        y 2020-01-01
6        y 2020-01-02
7        y 2020-01-03
8        y 2020-01-04

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

We can use complete from tidyr

library(dplyr)
library(tidyr)

start <- as.Date('2020-01-01')
end <- as.Date('2020-01-04')

df %>%  
  mutate(date = as.Date(date)) %>%  
  complete(location, date = seq(start, end, by = "1 day"))


# location     date      
# <fct>       <date>    
#1 x        2020-01-01
#2 x        2020-01-02
#3 x        2020-01-03
#4 x        2020-01-04
#5 y        2020-01-01
#6 y        2020-01-02
#7 y        2020-01-03
#8 y        2020-01-04

Upvotes: 1

Related Questions