Finding missing pair combinations

Question

I have a dataframe. I'd like to find out which disease is not recorded in an area. So for example: Area A does not have Mumps

What I'd like to do is wherever an area doesn't have a disease, I'd like to record a zero in the n column.

I thought it might be something like

DATA$missing<-DATA%>% if (DISEASE %in% DISEASE){"no"}

But that doesn't work and I didn't really expect it to but did hope it would...

Here's my data, in this example I've removed Mumps from Area A by filtering the original test data frame. How can I work out that Mumps isn't in Area A in this new data set? And other combinations of DISEASE AND AREA which might be missing and then return a value of n = 0 in the count column? thanks.

library (tidyverse)
library (epitools)


# here's my made up data

DISEASE = c("Marco Polio","Marco Polio","Marco Polio","Marco Polio","Marco Polio",
            "Mumps","Mumps","Mumps","Mumps","Mumps",
            "Chicky Pox","Chicky Pox","Chicky Pox","Chicky Pox","Chicky Pox")
YEAR = c(2011, 2012, 2013, 2014, 2015,
         2011, 2012, 2013, 2014, 2015,
         2011, 2012, 2013, 2014, 2015)
VALUE = c(82,89,79,51,51,
          79,91,69,89,78,
          71,69,95,61,87)
AREA =c("A", "B","C")

DATA = data.frame(DISEASE, YEAR, VALUE,AREA)

DATA<-DATA%>%filter(DISEASE !="Mumps" | AREA !="A")

Edit: My expected outcome would be this

new_row<-c("Mumps","2015",0,"A")
DATA<-rbind(DATA,new_row)

absolutely a duplicate! Thanks for finding this. I'll edit my question. — damo, Apr 08 '19 at 14:47

iod · Accepted Answer · 2019-04-08T14:37:24.773

DATA %>% complete(AREA, DISEASE, fill=list(VALUE=0))
# A tibble: 14 x 4
# Groups:   AREA [3]
   AREA  DISEASE      YEAR VALUE
   <fct> <fct>       <dbl> <dbl>
 1 A     Chicky Pox   2013    95
 2 A     Marco Polio  2011    82
 3 A     Marco Polio  2014    51
 4 A     Mumps          NA     0
 5 B     Chicky Pox   2011    71
 6 B     Chicky Pox   2014    61
 7 B     Marco Polio  2012    89
 8 B     Marco Polio  2015    51
 9 B     Mumps        2013    69
10 C     Chicky Pox   2012    69
11 C     Chicky Pox   2015    87
12 C     Marco Polio  2013    79
13 C     Mumps        2011    79
14 C     Mumps        2014    89

If you'd like the 0 line to have a specific year, you can add that to the fill=list() argument.

Finding missing pair combinations

1 Answers1