Find missing month after grouping with dplyr

3
votes

I have a data frame with two columns that I am grouping by with dplyr, a column of months (as numerics, e.g. 1 through 12), and several columns with statistical data following that (values unimportant). An example:

ID_1   ID_2   month  st1    st2
1      1      1      0.5    0.2
1      1      2      0.7    0.9
1      1      3      1.1    1.7
1      1      4      2.6    0.8
1      1      5      1.8    1.3
1      1      6      2.1    2.2
1      1      7      0.5    0.2
1      1      8      0.7    0.9
1      1      9      1.1    1.7
1      1      10     2.6    0.8
1      1      11     1.8    1.3
1      1      12     2.1    2.2
1      2      1      0.5    0.2
1      2      2      0.7    0.9
1      2      3      1.1    1.7
1      2      4      2.6    0.8
1      2      5      1.8    1.3
1      2      6      2.1    2.2
1      2      7      0.5    0.2
1      2      9      1.1    1.7
1      2      10     2.6    0.8
1      2      11     1.8    1.3
1      2      12     2.1    2.2

For the second grouping (ID_1 = 1 and ID_2 = 2), there is a month missing from the data (month = 8). Is there a way I can find this month and insert a row with the correct ID_1 and ID_2 values, the missing month value, and NA values for the rest of the columns? I've been playing around with this using dplyr functions and can't seem to figure it out, perhaps there is even a non-dplyr solution out there as well.

PS: If it helps, each unique grouping of ID_1 and ID_2 will have no more than 1 month missing.

3
rdplyrmissing-data
its not clear to me what you're looking for. You really want a whole new column just to show the value of the missing month? What would be the other values of the column for the other months that werent missing -would they be NA? – Cyrus Mohammadian
My wording is off in my post, I've edited it. I want to insert a new row where the missing month should be, with the columns of that new row populated with NA (aside from the ID columns). – Kevin M

3 Answers

7
votes

Expand grid to make all combos of groups, then merge:

# make reference with all needed rows
ref <- data.frame(expand.grid(unique(df1$ID_1),
                              unique(df1$ID_2),
                              1:12))
colnames(ref) <- colnames(df1)[1:3]

# them merge with all TRUE
res <- merge(df1, ref, all = TRUE)

# to check output, show only month = 8
res[ res$month == 8, ]
#    ID_1 ID_2 month st1 st2
# 8     1    1     8 0.7 0.9
# 20    1    2     8  NA  NA
6
votes

This can be done via tidyr::complete:

library(dplyr)
library(tidyr)

dat %>% 
    group_by(ID_1, ID_2) %>%
    complete(month = 1:12)

Tail of dataset:

Source: local data frame [6 x 5]
Groups: ID_1, ID_2 [1]

   ID_1  ID_2 month   st1   st2
  <int> <int> <int> <dbl> <dbl>
1     1     2     7   0.5   0.2
2     1     2     8    NA    NA
3     1     2     9   1.1   1.7
4     1     2    10   2.6   0.8
5     1     2    11   1.8   1.3
6     1     2    12   2.1   2.2
3
votes

If you go with tidyr, there is the complete function for this, you can nest ID_1 and ID_2 if you want both of the two variables as your grouping variable:

library(tidyr)
df1 = df %>% complete(nesting(ID_1, ID_2), month)

tail(df1)    
# Source: local data frame [6 x 5]

#    ID_1  ID_2 month   st1   st2
#   <int> <int> <int> <dbl> <dbl>
# 1     1     2     7   0.5   0.2
# 2     1     2     8    NA    NA
# 3     1     2     9   1.1   1.7
# 4     1     2    10   2.6   0.8
# 5     1     2    11   1.8   1.3
# 6     1     2    12   2.1   2.2