having 0 when there is no counts (using group_by)

Question

this is my data

 Date              Injury.Cause n
1   2019-04-14              Road traffic 1
2   2019-04-15       Accidental injuries 1
3   2019-04-18              Road traffic 1
4   2019-04-23                     Burns 1
5   2019-04-26              Road traffic 1
6   2019-05-01              Road traffic 1
7   2019-05-02              Road traffic 1
8   2019-05-08                     Burns 1
9   2019-05-08              Road traffic 1
10  2019-05-09                     Falls 1
11  2019-05-09              Road traffic 1
12  2019-05-14                     Burns 1
13  2019-05-16                     Falls 1
14  2019-05-20                     Falls 1
15  2019-05-22              Road traffic 1

I want to plot them on line plot, so I did this

Pivot.start.with_25 <- keep_long_format %>%
  group_by(Date, Injury.Cause ) %>%
  summarise(Date.sum = sum(n))

now, they are ready

 Date       Injury.Cause        Date.sum
   <chr>      <chr>                  <int>
 1 2019-03-25 Accidental injuries        1
 2 2019-03-25 Burns                      5
 3 2019-03-25 Falls                      4
 4 2019-03-25 Road traffic              12
 5 2019-06-25 Burns                      5
 6 2019-06-25 Falls                      8
 7 2019-06-25 Road traffic              17
 8 2019-09-25 Accidental injuries        2
 9 2019-09-25 Burns                      8
10 2019-09-25 Falls                      6

but I want to have all causes of injury in the first date to appear on the plot eventhose with 0 cases. How can I bring them here with 0 values, so they appear as 0 in this date in the plot?

thank you in advance

score 1 · Accepted Answer · answered Sep 16 '21 at 23:26

We may use complete

library(tidyr)
library(dplyr)
Pivot.start.with_25 %>%
   ungroup %>%
   complete(Date, Injury.Cause, fill = list(Date.sum = 0))

-output

# A tibble: 12 x 3
   Date       Injury.Cause        Date.sum
   <chr>      <chr>                  <dbl>
 1 2019-03-25 Accidental injuries        1
 2 2019-03-25 Burns                      5
 3 2019-03-25 Falls                      4
 4 2019-03-25 Road traffic              12
 5 2019-06-25 Accidental injuries        0
 6 2019-06-25 Burns                      5
 7 2019-06-25 Falls                      8
 8 2019-06-25 Road traffic              17
 9 2019-09-25 Accidental injuries        2
10 2019-09-25 Burns                      8
11 2019-09-25 Falls                      6
12 2019-09-25 Road traffic               0

data

Pivot.start.with_25 <- structure(list(Date = c("2019-03-25", "2019-03-25", "2019-03-25", 
"2019-03-25", "2019-06-25", "2019-06-25", "2019-06-25", "2019-09-25", 
"2019-09-25", "2019-09-25"), Injury.Cause = c("Accidental injuries", 
"Burns", "Falls", "Road traffic", "Burns", "Falls", "Road traffic", 
"Accidental injuries", "Burns", "Falls"), Date.sum = c(1L, 5L, 
4L, 12L, 5L, 8L, 17L, 2L, 8L, 6L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"))

having 0 when there is no counts (using group_by)

1 Answers1

data