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

0
votes

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

2
rdatetimedataframemerge
Rather than do this, why not just rbind your three date columns from your three data frames, and then run unique to remove duplicates? – Mako212
I need to rbind three data frames and also order it according to the date, since the winter dataframe will have data for only January, February, November, and December for all years 2014-2018. Same applies for Summer and Spring months.We need to find a way to rbind all three seasons' data frame in an ordered manner from 2014-01-01 to 2018-10-31 – victor8910

2 Answers

2
votes

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 ...
1
votes

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