Adding data to a data.frame to complete a sequence

Question

With the data included below, the first bit of which looks like

head(dat, 9)
      IndID BinID Freq
1 BHS_034_A     7   20
2 BHS_034_A     8   27
3 BHS_034_A     9   67
4 BHS_034_A    10  212
5 BHS_037_A     5    1
6 BHS_037_A     7   12
7 BHS_037_A     8   65
8 BHS_037_A     9  122
9 BHS_037_A    10  301

I want to fill in missing numbers of BinID so that all individuals (IndID) have a BinID sequence from 1 to 10. Freq values should be 0 when new values of BinID are added.

I hope to accommodate many individuals, but have only included a few here.

This question, is similar to another post but here I am trying to also add 0 to the filled in columns.

The data:

dat <- structure(list(IndID = c("BHS_034_A", "BHS_034_A", "BHS_034_A", 
"BHS_034_A", "BHS_037_A", "BHS_037_A", "BHS_037_A", "BHS_037_A", 
"BHS_037_A", "BHS_068_A", "BHS_068_A", "BHS_068_A", "BHS_068_A", 
"BHS_068_A", "BHS_068_A", "BHS_068_A", "BHS_070_A", "BHS_070_A", 
"BHS_070_A", "BHS_071_A", "BHS_071_A", "BHS_071_A", "BHS_071_A", 
"BHS_071_A", "BHS_071_A", "BHS_071_A", "BHS_071_A", "BHS_071_A"
), BinID = c(7L, 8L, 9L, 10L, 5L, 7L, 8L, 9L, 10L, 3L, 4L, 5L, 
7L, 8L, 9L, 10L, 8L, 9L, 10L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L), Freq = c(20L, 27L, 67L, 212L, 1L, 12L, 65L, 122L, 301L, 
2L, 1L, 1L, 4L, 14L, 104L, 454L, 7L, 90L, 470L, 6L, 11L, 11L, 
7L, 18L, 19L, 15L, 31L, 344L)), .Names = c("IndID", "BinID", 
"Freq"), row.names = c(NA, 28L), class = "data.frame")

score 3 · Accepted Answer · answered Jul 11 '17 at 19:09

3

tidyr provides the complete function that allows you to find missing combinations in your dataset:

tidyr::complete(dat, IndID, BinID = 1:10)

answered Jul 11 '17 at 19:09

fmic_

2,281
16
23

Great new function for me! – B. Davis Jul 11 '17 at 19:17
and with the `fill` argument, it is possible to fill in with 0 rather than NA. `fill = list(Freq = 0)). – B. Davis Jul 11 '17 at 19:29

score 1 · Answer 2 · answered Jul 11 '17 at 19:11

Using:

library(data.table)
setDT(dat)[CJ(BinID = 1:10, IndID = IndID, unique = TRUE), on = .(IndID, BinID)]

Or:

library(dplyr)
library(tidyr)
dat %>% 
  group_by(IndID) %>% 
  expand(BinID = 1:10) %>% 
  left_join(., dat)

gives:

        IndID BinID Freq
 1: BHS_034_A     1   NA
 2: BHS_037_A     1   NA
 3: BHS_068_A     1   NA
 4: BHS_070_A     1   NA
 5: BHS_071_A     1   NA
 6: BHS_034_A     2   NA
 7: BHS_037_A     2   NA
 8: BHS_068_A     2   NA
 9: BHS_070_A     2   NA
10: BHS_071_A     2    6
11: BHS_034_A     3   NA
12: BHS_037_A     3   NA
13: BHS_068_A     3    2
14: BHS_070_A     3   NA
15: BHS_071_A     3   11
16: BHS_034_A     4   NA
17: BHS_037_A     4   NA
18: BHS_068_A     4    1
19: BHS_070_A     4   NA
20: BHS_071_A     4   11
21: BHS_034_A     5   NA
22: BHS_037_A     5    1
23: BHS_068_A     5    1
24: BHS_070_A     5   NA
25: BHS_071_A     5    7
26: BHS_034_A     6   NA
27: BHS_037_A     6   NA
28: BHS_068_A     6   NA
29: BHS_070_A     6   NA
30: BHS_071_A     6   18
31: BHS_034_A     7   20
32: BHS_037_A     7   12
33: BHS_068_A     7    4
34: BHS_070_A     7   NA
35: BHS_071_A     7   19
36: BHS_034_A     8   27
37: BHS_037_A     8   65
38: BHS_068_A     8   14
39: BHS_070_A     8    7
40: BHS_071_A     8   15
41: BHS_034_A     9   67
42: BHS_037_A     9  122
43: BHS_068_A     9  104
44: BHS_070_A     9   90
45: BHS_071_A     9   31
46: BHS_034_A    10  212
47: BHS_037_A    10  301
48: BHS_068_A    10  454
49: BHS_070_A    10  470
50: BHS_071_A    10  344

Adding data to a data.frame to complete a sequence

2 Answers2