Kevin M Kevin M - 2 months ago 7
R Question

Find missing month after grouping with dplyr

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.

Answer

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