Using dplyr to summarize a variable and calculate the mean of another dependent on other variables

Question

In the data set below, I want to summary the rentals grouped_by city and date and additionally calculate the mean duration grouped_by date + city.

   date       rentals  City          duration  
   <date>       <dbl> <fct>            <drtn> 
 1 2014-01-01       1 Hamburg          15 mins
 2 2014-01-01       1 Hamburg          18 mins
 3 2014-01-01       1 Vienna          13 mins
 4 2014-01-02       1 Vienna           1 mins
 5 2014-01-02       1 Hamburg           8 mins
 6 2014-01-02       1 Berlin           4 mins
 7 2014-01-03       1 Hamburg          13 mins
 8 2014-01-03       1 Hamburg           2 mins
 9 2014-01-03       1 Berlin            4 mins
10 2014-01-04       1 Hamburg          17 mins
...

I'd like to use dplyr and tried the following:

df <- df %>%
  group_by(date, city) %>% 
  summarise((rentals=sum(rentals)), duration=mean(duration))

I end up having only one row left with the summarized rentals and the mean overall duration. It seems that it just ignored my group_by function.

Would be great to get some help :)

Answer 1

I think you're just not capitalizing City properly. This works for me:

library(dplyr)

df <- read.table(text = "date       rentals  City          duration  
                 1 2014-01-01       1 Hamburg          15 
                 2 2014-01-01       1 Hamburg          18 
                 3 2014-01-01       1 Vienna          13 
                 4 2014-01-02       1 Vienna           1 
                 5 2014-01-02       1 Hamburg           8 
                 6 2014-01-02       1 Berlin           4 
                 7 2014-01-03       1 Hamburg          13 
                 8 2014-01-03       1 Hamburg           2 
                 9 2014-01-03       1 Berlin            4 
                 10 2014-01-04       1 Hamburg          17 ")

df2 <- df %>%
  group_by(date, City) %>% 
  summarise(rentals=sum(rentals), duration=mean(duration))

df2 output:

# A tibble: 8 x 4
# Groups:   date [4]
  date       City    rentals duration
  <chr>      <chr>     <int>    <dbl>
1 2014-01-01 Hamburg       2     16.5
2 2014-01-01 Vienna        1     13  
3 2014-01-02 Berlin        1      4  
4 2014-01-02 Hamburg       1      8  
5 2014-01-02 Vienna        1      1  
6 2014-01-03 Berlin        1      4  
7 2014-01-03 Hamburg       2      7.5
8 2014-01-04 Hamburg       1     17