Proper idiom for adding zero count rows in tidyr/dplyr

Question

Suppose I have some count data that looks like this:

library(tidyr)
library(dplyr)

X.raw <- data.frame(
  x = as.factor(c("A", "A", "A", "B", "B", "B")),
  y = as.factor(c("i", "ii", "ii", "i", "i", "i")),
  z = 1:6
)
X.raw
#   x  y z
# 1 A  i 1
# 2 A ii 2
# 3 A ii 3
# 4 B  i 4
# 5 B  i 5
# 6 B  i 6

I'd like to tidy and summarise like this:

X.tidy <- X.raw %>% group_by(x, y) %>% summarise(count = sum(z))
X.tidy
# Source: local data frame [3 x 3]
# Groups: x
#
#   x  y count
# 1 A  i     1
# 2 A ii     5
# 3 B  i    15

I know that for x=="B" and y=="ii" we have observed count of zero, rather than a missing value. i.e. the field worker was actually there, but because there wasn't a positive count no row was entered into the raw data. I can add the zero count explicitly by doing this:

X.fill <- X.tidy %>% spread(y, count, fill = 0) %>% gather(y, count, -x)
X.fill
# Source: local data frame [4 x 3]
# 
#   x  y count
# 1 A  i     1
# 2 B  i    15
# 3 A ii     5
# 4 B ii     0

But that seems a little bit of a roundabout way of doing things. Is there a cleaner idiom for this?

Just to clarify: My code already does what I need it to do, using spread then gather, so what I'm interested in is finding a more direct route within tidyr and dplyr.

If you know that for "B"/"ii" there is an observed count of zero, why isn't that in your source dataset? Your source dataset presently indicates that combination as missing. — A5C1D2H2I1M1N2O1R2T1, Sep 21 '14 at 05:45
The raw dataset only includes positive counts. But we know all combinations were surveyed. — pete, Sep 21 '14 at 05:52
Do you have another table that shows what combinations of "x" and "y" are all valid? Otherwise, how do you plan to distinguish between `0` and `NA`? — A5C1D2H2I1M1N2O1R2T1, Sep 21 '14 at 06:09

moodymudskipper · Accepted Answer · 2022-11-02T09:10:59.157

42

Since dplyr 0.8 you can do it by setting the parameter .drop = FALSE in group_by:

X.tidy <- X.raw %>% group_by(x, y, .drop = FALSE) %>% summarise(count=sum(z))
X.tidy
# # A tibble: 4 x 3
# # Groups:   x [2]
#   x     y     count
#   <fct> <fct> <int>
# 1 A     i         1
# 2 A     ii        5
# 3 B     i        15
# 4 B     ii        0

This will keep groups made of all the levels of factor columns so if you have character columns you might want to convert them (thanks to Pake for the note).

edited Nov 02 '22 at 09:10

answered Feb 20 '19 at 20:41

moodymudskipper

46,417
11
121
167

7

just a hint if you made it here and this isn't working for you: be sure you're using factors and not character columns in your group_by() – Pake Mar 23 '22 at 15:16
1

@Pake big thanks for this comment! I weas going mad trying to work out why this didn't work. – Taren Sanders May 22 '22 at 04:00

aosmith · Answer 2 · 2016-07-20T17:12:05.660

The complete function from tidyr is made for just this situation.

From the docs:

This is a wrapper around expand(), left_join() and replace_na that's useful for completing missing combinations of data.

You could use it in two ways. First, you could use it on the original dataset before summarizing, "completing" the dataset with all combinations of x and y, and filling z with 0 (you could use the default NA fill and use na.rm = TRUE in sum).

X.raw %>% 
    complete(x, y, fill = list(z = 0)) %>% 
    group_by(x,y) %>% 
    summarise(count = sum(z))

Source: local data frame [4 x 3]
Groups: x [?]

       x      y count
  <fctr> <fctr> <dbl>
1      A      i     1
2      A     ii     5
3      B      i    15
4      B     ii     0

You can also use complete on your pre-summarized dataset. Note that complete respects grouping. X.tidy is grouped, so you can either ungroup and complete the dataset by x and y or just list the variable you want completed within each group - in this case, y.

# Complete after ungrouping
X.tidy %>% 
    ungroup %>%
    complete(x, y, fill = list(count = 0))

# Complete within grouping
X.tidy %>% 
    complete(y, fill = list(count = 0))

The result is the same for each option:

Source: local data frame [4 x 3]

       x      y count
  <fctr> <fctr> <dbl>
1      A      i     1
2      A     ii     5
3      B      i    15
4      B     ii     0

score 4 · Answer 3 · answered Jul 01 '15 at 13:17

4

You can use tidyr's expand to make all combinations of levels of factors, and then left_join:

X.tidy %>% expand(x, y) %>% left_join(X.tidy)

# Joining by: c("x", "y")
# Source: local data frame [4 x 3]
# 
#   x  y count
# 1 A  i     1
# 2 A ii     5
# 3 B  i    15
# 4 B ii    NA

Then you may keep values as NAs or replace them with 0 or any other value. That way isn't a complete solution of the problem too, but it's faster and more RAM-friendly than spread & gather.

answered Jul 01 '15 at 13:17

inscaven

2,514
19
29

2

Also see `complete` in the development version of tidyr (0.2.0.9000), which is a handy wrapper for `expand`, `left_join`, and `replace_na`. – aosmith Jul 01 '15 at 15:02
Thanks @aosmith that's exactly what I needed. If you write it up as an answer I'll accept it. – pete Jun 09 '16 at 00:28

score 3 · Answer 4 · edited May 23 '17 at 12:09

3

plyr has the functionality you're looking for, but dplyr doesn't (yet), so you need some extra code to include the zero-count groups, as shown by @momeara. Also see this question. In plyr::ddply you just add .drop=FALSE to keep zero-count groups in the final result. For example:

library(plyr)

X.tidy = ddply(X.raw, .(x,y), summarise, count=sum(z), .drop=FALSE)

X.tidy
  x  y count
1 A  i     1
2 A ii     5
3 B  i    15
4 B ii     0

edited May 23 '17 at 12:09

Community

1
1

answered Sep 21 '14 at 06:18

eipi10

91,525
24
209
285

do you mean ddply instead of dplyr in the second line? – momeara Sep 21 '14 at 06:25

momeara · Answer 5 · 2015-10-23T02:24:50.000

2

You could explicitly make all possible combinations and then joining it with the tidy summary:

x.fill <- expand.grid(x=unique(x.tidy$x), x=unique(x.tidy$y)) %>%
    left_join(x.tidy, by=("x", "y")) %>%
    mutate(count = ifelse(is.na(count), 0, count)) # replace null values with 0's

edited Oct 23 '15 at 02:24

answered Sep 21 '14 at 06:16

momeara

1,341
2
17
29

score 0 · Answer 6 · answered Jul 13 '22 at 13:51

You can also use the data.table package and its Cross Join CJ() function for that.

require(data.table)

X = data.table(X.raw)[
  CJ(y = y,
     x = x,
     unique = TRUE), 
  on = .(x, y)
  ][ , .(z = sum(z)), .(x, y) ][ order(x, y) ]
X

# filling the NAs with 0s
setnafill(X, fill = 0, cols = 'z')
X
#    x  y  z
# 1: A  i  1
# 2: A ii  5
# 3: B  i 15
# 4: B ii  0

Though it's not initially asked for, I'm adding a data.table solution here for the sake of completeness and to also link to the related data.table question.

Proper idiom for adding zero count rows in tidyr/dplyr

6 Answers6

Linked

Related