How to combine multiple dataframe by MonthYear in R

Question

I have below mentioned different dataframe:

DF1:

Origination_Date        Count1        Count2
2018-07-01              147           205
2018-07-05              180           345
2018-07-08              195           247
2018-08-04              205           788

DF2:

Date              ID
2018-07-01        I-1
2018-07-02        I-2
2018-07-02        I-3
2018-07-03        I-4
2018-07-03        I-5
2018-08-04        I-6
2018-08-04        I-7

DF3

Create_Date           ID
2018-07-01            I-1
2018-07-02            I-2
2018-07-03            I-3
2018-08-04            I-4
2018-08-04            I-5

By utilizing the above multiple dataframe, I want to create a new dataframe group by MonthYear and represent consolidated count on month wise and date wise as shown in the below sample dataframe.

Required Output:

Month   Count1   Count2   DF2_Count(ID)    DF3_Count(ID)
Aug-18  205      788      2                2
Jul-18  522      797      5                3
Jun-18  0        0        0                0

The above mentioned same data structure want to create on date basis as well, I have tried using group_by function and could create the required dataframe for each separate datafreme but not by consolidated all the dataframe.

Note: - Though I don't have Jun-18 month in my datframe, I want to create a row for the same month (want to create atleast three month in the required output dataframe considering the latest month (i.e if its Sep-18 than Aug-18 and Jul-18). - If any dataframe has 0 row than show count 0 is Required Output.

First you have to create your `$YearMonth` in each dataframe, e.g. by `substr(..., 1, 7)` — jogo, Sep 17 '18 at 09:09
@jogo already did that, using `DF1%>% mutate(Date= lubridate::ymd_hms(Origination_Date)` — Jupiter, Sep 17 '18 at 09:36
`lubridate::floor_date(Origination_Date, 'month')` may be of help here — konvas, Sep 17 '18 at 10:12

score 1 · Accepted Answer · answered Sep 17 '18 at 09:54

What about something like this:

# your data
df1 <- data.frame (Origination_Date = c('2018-07-01','2018-07-05','2018-07-08','2018-08-04'),
                   Count1 = c(147,180,195,205), Count2 = c(205,345,247,788))
df2 <- data.frame (Date = c('2018-07-01','2018-07-02','2018-07-02','2018-07-03','2018-07-03','2018-08-04','2018-08-04'),
                   ID = c('I-1','I-2','I-3','I-4','I-5','I-6','I-7'))
df3 <- data.frame (Create_Date = c('2018-07-01','2018-07-02','2018-07-03','2018-08-04','2018-08-04'), ID = c('I-1','I-2','I-3','I-4','I-5'))

# package to manage date
library(lubridate)

# first we create the yyyy-mm data.frame grouped
df1_1 <- df1 %>% 
       mutate(ym = format(ymd(Origination_Date),'%Y-%b')) %>%
       group_by(ym) %>%
       summarise(Count1 = sum(Count1) ,Count2 = sum(Count2))

df2_1 <- df2 %>%
      mutate(ym = format(ymd(Date),'%Y-%b')) %>%
      group_by(ym) %>%
      summarise(DF2_Count = n())

df3_1 <- df3 %>%
        mutate(ym = format(ymd(Create_Date),'%Y-%b')) %>%
        group_by(ym) %>%
       summarise(DF3_Count = n())


# join them together
df_1 <- df1_1 %>% full_join(df2_1, by = 'ym') %>% full_join(df3_1, by = 'ym')

    > df_1
# A tibble: 2 x 5
  ym       Count1 Count2 DF2_Count DF3_Count
  <chr>     <dbl>  <dbl>     <int>     <int>
1 2018-Aug    205    788         2         2
2 2018-Jul    522    797         5         3

Now the tricky part, add the missing month, I've created a pair of if whom checks if there is not the max month-year - 2 (the second), it add a fake row, and the first for the last but one.

if(
  format(floor_date(as.Date(max(union(union(df1[,1], df2[,1]),df3[,1]))), "month") - months(1),'%Y-%b') %in% df_1$ym == F){
  df_2 <- data.frame(ym =format(floor_date(as.Date(max(union(union(df1[,1], df2[,1]),df3[,1]))), "month") - months(1),'%Y-%b'),
                     Count1 = 0,
                     Count2 = 0,
                     DF2_Count= 0,
                     DF3_Count= 0)
  rbind(df_1,df_2)} else {'it already exists'}
[1] "it already exists"


if(
format(floor_date(as.Date(max(union(union(df1[,1], df2[,1]),df3[,1]))), "month") - months(2),'%Y-%b') %in% df_1$ym == F){
df_2 <- data.frame(ym =format(floor_date(as.Date(max(union(union(df1[,1], df2[,1]),df3[,1]))), "month") - months(2),'%Y-%b'),
                   Count1 = 0,
                   Count2 = 0,
                   DF2_Count= 0,
                   DF3_Count= 0)
rbind(df_1,df_2)
} else {'it already exists'}

    # A tibble: 3 x 5
      ym       Count1 Count2 DF2_Count DF3_Count
      <chr>     <dbl>  <dbl>     <dbl>     <dbl>
    1 2018-Aug    205    788         2         2
    2 2018-Jul    522    797         5         3
    3 2018-Jun      0      0         0         0

@Jupiter where did you get the error? To me it works fine, I'll gladly help you. — s__, Sep 17 '18 at 10:25
@Jupiter Sorry, I did not understand if you have solved your question, or you have solved the issue with my answer. — s__, Sep 17 '18 at 12:24

jogo · Answer 2 · 2018-09-17T13:46:09.930

Here is a solution with data.table:

library(data.table)

DF1 <- fread(
"Origination_Date        Count1        Count2
2018-07-01              147           205
2018-07-05              180           345
2018-07-08              195           247
2018-08-04              205           788")

DF2 <- fread(
"Date              ID
2018-07-01        I-1
2018-07-02        I-2
2018-07-02        I-3
2018-07-03        I-4
2018-07-03        I-5
2018-08-04        I-6
2018-08-04        I-7")

DF3 <- fread(
"Create_Date           ID
2018-07-01            I-1
2018-07-02            I-2
2018-07-03            I-3
2018-08-04            I-4
2018-08-04            I-5")

S1 <- DF1[, Ymon:=substr(Origination_Date, 1, 7)][, .(sum(Count1), sum(Count2)), Ymon]
S2 <- DF2[, Ymon:=substr(Date, 1, 7)][, .(DF2count=.N), Ymon]
S3 <- DF3[, Ymon:=substr(Create_Date, 1, 7)][, .(DF3count=.N), Ymon]

S <- merge(data.table(Ymon=paste0("2018-0", 6:8)), S1, all.x=TRUE)
S <- merge(S, S2, all.x=TRUE)
S <- merge(S, S3, all.x=TRUE)
S
# > S
#       Ymon  V1  V2 DF2count DF3count
# 1: 2018-06  NA  NA       NA       NA
# 2: 2018-07 522 797        5        3
# 3: 2018-08 205 788        2        2

If you want 0 instead of NA you can do:

S[is.na(S)] <- 0
S
#       Ymon  V1  V2 DF2count DF3count
# 1: 2018-06   0   0        0        0
# 2: 2018-07 522 797        5        3
# 3: 2018-08 205 788        2        2

How to combine multiple dataframe by MonthYear in R

2 Answers2