Kevin M - 1 year ago 60
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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download