do not count zeros but keep group number

user_583464 0
Viewed 67

I am grouping data based on mdo value and counting the instances of these values. I then use the previous count to do some computations.

library(tidyverse)
library(dplyr)

df <- tibble(mydate = as.Date(c("2019-05-11 23:01:00", "2019-05-11 23:02:00", "2019-05-11 23:03:00", "2019-05-11 23:04:00",
                                "2019-05-12 23:05:00", "2019-05-12 23:06:00", "2019-05-12 23:07:00", "2019-05-12 23:08:00",
                                "2019-05-13 23:09:00", "2019-05-13 23:10:00", "2019-05-13 23:11:00", "2019-05-13 23:12:00",
                                "2019-05-14 23:13:00", "2019-05-14 23:14:00", "2019-05-14 23:15:00", "2019-05-14 23:16:00",
                                "2019-05-15 23:17:00", "2019-05-15 23:18:00", "2019-05-15 23:19:00", "2019-05-15 23:20:00",
                                "2019-05-15 23:21:00", "2019-05-15 23:22:00", "2019-05-15 23:23:00", "2019-05-15 23:24:00",
                                "2019-05-15 23:25:00")),
             mdo = c(1500, 1500, 1500, 1500,
                     1500, 1500, NA, 0,
                     0, 0, NA, NA, NA, NA, 1100, 1100,
                     1100, 200, 200, 200,200,
                     1100, 1100, 1100, 0
             ))


#Create a group number
df1 <- df %>% mutate(grp = data.table::rleid(mdo))

df1 <- df1 %>%
    #Keep only non-NA value
    filter(!is.na(mdo)) %>%
    #count occurence of each grp
    count(grp, name = 'count') %>%
    #Shift the count to the previous group
    mutate(count = lag(count)) %>%
    #Join with the original data
    right_join(df1, by = 'grp')

Right now , it counts zeros. I don't want to count zeros but I want to keep them in a different group.

I want something like:

count(ifelse(mdo != 0, grp, NA), name='count') %>%

but this gives:

Error: by can't contain join column grp which is missing from LHS

The result right now is:

 grp count mydate       mdo
   <int> <int> <date>     <dbl>
 1     1    NA 2019-05-11  1500
 2     1    NA 2019-05-11  1500
 3     1    NA 2019-05-11  1500
 4     1    NA 2019-05-11  1500
 5     1    NA 2019-05-12  1500
 6     1    NA 2019-05-12  1500
 7     2    NA 2019-05-12    NA
 8     3     6 2019-05-12     0
 9     3     6 2019-05-13     0
10     3     6 2019-05-13     0
11     4    NA 2019-05-13    NA
12     4    NA 2019-05-13    NA
13     4    NA 2019-05-14    NA
14     4    NA 2019-05-14    NA
15     5     3 2019-05-14  1100
16     5     3 2019-05-14  1100
17     5     3 2019-05-15  1100
18     6     3 2019-05-15   200
19     6     3 2019-05-15   200
20     6     3 2019-05-15   200
21     6     3 2019-05-15   200
22     7     4 2019-05-15  1100
23     7     4 2019-05-15  1100
24     7     4 2019-05-15  1100
25     8     3 2019-05-15     0

the result I want:

 grp count mydate       mdo
   <int> <int> <date>     <dbl>
 1     1    NA 2019-05-11  1500
 2     1    NA 2019-05-11  1500
 3     1    NA 2019-05-11  1500
 4     1    NA 2019-05-11  1500
 5     1    NA 2019-05-12  1500
 6     1    NA 2019-05-12  1500
 7     2    NA 2019-05-12    NA
 8     3     6 2019-05-12     0
 9     3     6 2019-05-13     0
10     3     6 2019-05-13     0
11     4    NA 2019-05-13    NA
12     4    NA 2019-05-13    NA
13     4    NA 2019-05-14    NA
14     4    NA 2019-05-14    NA
15     5    NA 2019-05-14  1100
16     5    NA 2019-05-14  1100
17     5    NA 2019-05-15  1100
18     6     3 2019-05-15   200
19     6     3 2019-05-15   200
20     6     3 2019-05-15   200
21     6     3 2019-05-15   200
22     7     4 2019-05-15  1100
23     7     4 2019-05-15  1100
24     7     4 2019-05-15  1100
25     8     3 2019-05-15     0
dplyr r
2 Answers

I'm not 100 % sure, how your underlying logic works, but you could try the following steps using dplyr:

df1 %>% 
  filter(!is.na(mdo)) %>%
  group_by(grp) %>%
  summarise(count=ifelse(mdo==0, NA_integer_, n()), .groups="drop") %>%
  distinct() %>%
  mutate(count=lag(count)) %>%
  right_join(df1, by="grp") %>%
  arrange(grp, mydate)

returns

# A tibble: 25 x 4
     grp count mydate       mdo
   <int> <int> <date>     <dbl>
 1     1    NA 2019-05-11  1500
 2     1    NA 2019-05-11  1500
 3     1    NA 2019-05-11  1500
 4     1    NA 2019-05-11  1500
 5     1    NA 2019-05-12  1500
 6     1    NA 2019-05-12  1500
 7     2    NA 2019-05-12    NA
 8     3     6 2019-05-12     0
 9     3     6 2019-05-13     0
10     3     6 2019-05-13     0
11     4    NA 2019-05-13    NA
12     4    NA 2019-05-13    NA
13     4    NA 2019-05-14    NA
14     4    NA 2019-05-14    NA
15     5    NA 2019-05-14  1100
16     5    NA 2019-05-14  1100
17     5    NA 2019-05-15  1100
18     6     3 2019-05-15   200
19     6     3 2019-05-15   200
20     6     3 2019-05-15   200
21     6     3 2019-05-15   200
22     7     4 2019-05-15  1100
23     7     4 2019-05-15  1100
24     7     4 2019-05-15  1100
25     8     3 2019-05-15     0

Data

structure(list(mydate = structure(c(18027, 18027, 18027, 18027, 
18028, 18028, 18028, 18028, 18029, 18029, 18029, 18029, 18030, 
18030, 18030, 18030, 18031, 18031, 18031, 18031, 18031, 18031, 
18031, 18031, 18031), class = "Date"), mdo = c(1500, 1500, 1500, 
1500, 1500, 1500, NA, 0, 0, 0, NA, NA, NA, NA, 1100, 1100, 1100, 
200, 200, 200, 200, 1100, 1100, 1100, 0), grp = c(1L, 1L, 1L, 
1L, 1L, 1L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 
6L, 6L, 7L, 7L, 7L, 8L)), row.names = c(NA, -25L), class = c("tbl_df", 
"tbl", "data.frame"))
user_125052510

Before joining you can keep the value of mdo in summarise and if the previous value in mdo is 0, turn the count value to NA.

library(dplyr)
df1 %>%
  filter(!is.na(mdo)) %>%
  group_by(grp) %>%
  summarise(mdo = first(mdo), 
            count = n()) %>%
  mutate(count = ifelse(lag(mdo) == 0, NA, lag(count))) %>%
  right_join(df1, by = c('grp', 'mdo'))  %>%
  arrange(grp)

#   grp  mdo count     mydate
#1    1 1500    NA 2019-05-11
#2    1 1500    NA 2019-05-11
#3    1 1500    NA 2019-05-11
#4    1 1500    NA 2019-05-11
#5    1 1500    NA 2019-05-12
#6    1 1500    NA 2019-05-12
#7    2   NA    NA 2019-05-12
#8    3    0     6 2019-05-12
#9    3    0     6 2019-05-13
#10   3    0     6 2019-05-13
#11   4   NA    NA 2019-05-13
#12   4   NA    NA 2019-05-13
#13   4   NA    NA 2019-05-14
#14   4   NA    NA 2019-05-14
#15   5 1100    NA 2019-05-14
#16   5 1100    NA 2019-05-14
#17   5 1100    NA 2019-05-15
#18   6  200     3 2019-05-15
#19   6  200     3 2019-05-15
#20   6  200     3 2019-05-15
#21   6  200     3 2019-05-15
#22   7 1100     4 2019-05-15
#23   7 1100     4 2019-05-15
#24   7 1100     4 2019-05-15
#25   8    0     3 2019-05-15
user_39629140
Related