CODEWITHSUNDEEP
rdatetimedataframemerge
victor8910
victor8910

Reputation: 203

How to create full date column using "Month_Year" character string and join three different data frames ordered by the date column in R

I have three data frames - Winter, Spring, and Summer for years from 2014-2018. Winter months are January, February, November, December. Spring Months from March-June. Summer months from July - October. I have daily data for all the months for all seasons but have the Date column in 'Month_Year' character string. My question is how do I convert 'Month_Year' character string to a full date format '%Y-%m-%d'?

I am able to convert 'Month_Year' to yearmon using the as.yearmon function and then later convert it to a date using as.Date function. But it returns the first day of the month for every day of the month.

Following is the miminum reproducible example:

df1 <- data.frame(rep("July_2014",31))
names(df1) <- 'date'
df1$fulldate <-  as.yearmon(df1$date, "%b_%Y") 
df1$fulldate_Date <- as.Date(as.yearmon(df1$fulldate, "%m-%Y"))

Similarly, I will have three different data frames for three different seasons for years 2014-2018. Finally, I will need to merge all three data frames and create a single continuous time series from 2014-01-01 to 2018-10-31

Upvotes: 0

Views: 591

Answers (2)

Dave2e
Dave2e

Reputation: 24139

Here is a solution using the dplyr package. It paste a sequence number onto the month_year column and then converts this into a Date object. This assumes the data frame is in chronological order by day of month.

#test data
df1 <- data.frame(month=c(rep("June_2014",30), rep("July_2014",31)))

library(dplyr)
#Paste day onto the month year
answer<- df1 %>% group_by(month) %>% mutate(date = paste(month, 1:n())) 
#convert to date
answer$date<-as.Date(answer$date, "%b_%Y %d")

# month     date      
# <fct>     <date>    
# 1 June_2014 2014-06-01
# 2 June_2014 2014-06-02
# 3 June_2014 2014-06-03
# 4 June_2014 2014-06-04
# 5 June_2014 2014-06-05

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 270195

make_seq takes a date which is the first of the month and outputs a sequence of dates to the end of the month. Apply that using ave and get rid of the junk columns. rbind can be used to create a single data frame out of several having the same column names. The columns should not be factors unless they have the same levels.

make_seq <- function(x) seq(x[1], as.Date(as.yearmon(x[1]), frac = 1), by = "day")
transform(df1, Date = ave(fulldate_Date, date, FUN = make_seq))[c("fulldate", "Date")]

giving:

   fulldate       Date
1  Jul 2014 2014-07-01
2  Jul 2014 2014-07-02
3  Jul 2014 2014-07-03
4  Jul 2014 2014-07-04
5  Jul 2014 2014-07-05
6  Jul 2014 2014-07-06    
... etc ...

Upvotes: 2

Related Questions