10 replies ›

1. The code for this, and for an additional group_summarize() verb can be found here.

   Loading...

2. It took me a while to discover what group_by with mutate would do (no collapsing) and somehow found it unintuitive in an otherwise very intuitive package. By comparison, in Stata the equivalent:
   by cyl gear: egen group_mean_mpg = mean(mpg)
   does what you’d expect without collapsing as summarize in dplyr does.

   Loading...

   1. I come from a SQL background, so I find the non-collapsing case to be the odd one. My gut always says: build a table with the right keying and join it back in.

      However, running dplyr::show_query() on the examples (this time using a PostgreSQL back end) is very illuminating. We see the summarize collapse triggered by the SQL group command, and the windowed calculation co-mingling with other results in the mutate example.

      suppressPackageStartupMessages(library("dplyr"))
      library('RPostgreSQL')
      #> Loading required package: DBI
      
      # Local Postgres.app database; no password by default
      # Of course, you fill in your own database information here.
      con = DBI::dbConnect(DBI::dbDriver("PostgreSQL"),
                           host = 'localhost',
                           port = 5432,
                           user = 'postgres',
                           password = 'pg')
      
      copy_to(con, 
              mtcars %>% mutate(id = row_number()), 
              'mtcars')
      mtcars2 <- tbl(con, "mtcars")
      
      mtcars2 %>%
        select(id, mpg, cyl) %>%
        group_by(cyl) %>%
        mutate(mpg_min = min(mpg)) %>% 
        show_query()
      #> <SQL>
      #> SELECT "id", "mpg", "cyl", min("mpg") OVER (PARTITION BY "cyl") AS "mpg_min"
      #> FROM (SELECT "id" AS "id", "mpg" AS "mpg", "cyl" AS "cyl"
      #> FROM "mtcars") "kkztfjtpuw"
      
      mtcars2 %>%
        select(id, mpg, cyl) %>%
        group_by(cyl) %>%
        summarize(mpg_min = min(mpg)) %>% 
        show_query()
      #> <SQL>
      #> SELECT "cyl", MIN("mpg") AS "mpg_min"
      #> FROM (SELECT "id" AS "id", "mpg" AS "mpg", "cyl" AS "cyl"
      #> FROM "mtcars") "pdtcvcoqju"
      #> GROUP BY "cyl"
      

      Loading...

3. # group_by() also works with mutate() eliminating the need for summarise() and join()

   mtcars %>%
   group_by(cyl, gear) %>%
   mutate(group_mean_mpg = mean(mpg),
   group_mean_disp = mean(disp)) %>%
   head()

   Loading...

   1. Yes, that is what I hoped to indicate with “The analyst doesn’t have to start thinking about joins immediately”.

      And the shorthand works, until it does not.

      suppressPackageStartupMessages(library("dplyr"))
      con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
      copy_to(con, mtcars)
      mtcars2 <- tbl(con, "mtcars")
      
      mtcars2 %>%
        group_by(cyl, gear) %>%
        mutate(group_mean_mpg = mean(mpg),
               group_mean_disp = mean(disp)) %>%
        head()
      #> Error: Window function `avg()` is not supported by this database
      

      Loading...

4. A thank-you to rlang author Lionel Henry for taking the time to point out we can use rlang::syms for the symbol creation. I have updated the code here and in GitHub.

   Loading...

5. A similar pattern that I find myself often using to avoid a join is the group_by() %>% summarize(), where the grouping is over-specified. An example of this is a time series with columns year, month, date, value, (multiple values per day) and the analyst wants to summarize by date.

   A simple group_by(date) %>% summarize(day_value = sum(value)) will drop columns that we want to retain (year, month), but can re-derive (or join). To avoid the hassle, there are multiple options, all sub-optimal:

   “`
   #Over-specify grouping
   #Over-specify grouping
   dta %>%
   group_by(year, month, date) %>%
   summarize(value = sum(value))

   #Summarize extra columns
   dta %>%
   group_by(date) %>%
   summarize(value = sum(value),
   month = first(month),
   year = first(year))

   #Explicit join
   dta %>%
   group_by(date) %>%
   summarize(value = sum(value)) %>%
   left_join(dta[c(“year”, “month”, “date”)], by = “date”)
   “`

   Loading...

   1. Olivia B-R,

      That is a great issue and a great example. Thanks for bringing it up (and your solutions).

      To state it back: suppose are grouping by US zipcode and you have also a US State column. Under typical `group_by(zip_code) %>% summarize(age = mean(age))` type work you lose the (potentially useful) state column.

      In `SQL` if you try to include such columns you usually get an error message of the nature “ERROR: column ‘d.state’ must appear in the GROUP BY clause or be used in an aggregate function.”

      The solutions, as you state are usually one of:

      Add the column to the “group by” to “overkey.”
      Add the column to the query with a useless aggregator (like ‘min()’). We teach this one and suggest adding the comment “pseudo aggregator” to the code for readability.
      Joining the columns back in from a fact table later.

      As I said our favorite solution is the pseudo-aggegator.

      library("DBI")
      library("RPostgreSQL")
      suppressPackageStartupMessages(library("dplyr"))
      
      conn <- dbConnect(dbDriver("PostgreSQL"),
                        host = 'localhost',
                        port = 5432,
                        user = 'postgres',
                        password = 'pg')
      
      dbWriteTable(conn, 'd', 
                   data.frame(state='CA', zipcode=94207, age=40,
                              stringsAsFactors = FALSE),
                   temporary=TRUE, overwite=TRUE)
      #> [1] TRUE
      DBI::dbGetQuery(conn, "select 
                                zipcode, 
                                min(state) state, -- pseudo aggregator, column is a function of keys
                                avg(age) 
                             from 
                                d 
                             group by
                                zipcode")
      #>   zipcode state avg
      #> 1   94207    CA  40
      d <- dplyr::tbl(conn, 'd')
      
      # loses state
      d %>% 
        group_by(zipcode) %>% 
        summarize(meanage = mean(age))
      #> # Source:   lazy query [?? x 2]
      #> # Database: postgres 9.6.1 [postgres@localhost:5432/postgres]
      #>   zipcode meanage
      #>     <dbl>   <dbl>
      #> 1   94207      40
      
      # over keying
      d %>% 
        group_by(zipcode, state) %>% 
        summarize(meanage = mean(age))
      #> # Source:   lazy query [?? x 3]
      #> # Database: postgres 9.6.1 [postgres@localhost:5432/postgres]
      #> # Groups:   zipcode
      #>   zipcode state meanage
      #>     <dbl> <chr>   <dbl>
      #> 1   94207    CA      40
      
      # pseudo-aggregator
      d %>% 
        group_by(zipcode) %>% 
        summarize(meanage = mean(age), 
                  state= min(state) #  pseudo aggregator, column is a function of keys
        )
      #> # Source:   lazy query [?? x 3]
      #> # Database: postgres 9.6.1 [postgres@localhost:5432/postgres]
      #>   zipcode meanage state
      #>     <dbl>   <dbl> <chr>
      #> 1   94207      40    CA
      
      dbExecute(conn, 'drop table d')
      #> [1] 0
      

      Loading...

      1. Hi John,

         Thank you for your response. The zip code example is a more natural one (well, other than for a handful of zips, see https://gis.stackexchange.com/a/167333).

         My prefered method is the over-keying one. Could you explain why you prefer, and teach, the pseudo-aggregator instead?

         I generally think of the group_by dimensions as the keys/coordinates of the data, and the summarize ones as my measurements. In that framework, I think it makes more sense to over-key, although I guess there is an argument that the first key is sufficient and that the state is a “measurement” done at the zipcode level.

         My ideal solution would be to encode that key hierarchy in the data itself. I however cannot think of a nice design to allow the programmer to easily express this notion. I have been playing with the idea of using information in the join plan, we’ll see where that goes. I am very interested in other people’s thoughts on this.

         Best,
         Olivia

         Loading...

      2. I should have figured there would be mixed-state zip codes (argh). It is just non-time series examples are simpler for those who have not worked with time series.

         Over-keying is a great solution. I tend to use pseudo-aggergation because I come from a SQL world, so I am often in the middle of the join and the aggregation notation is often a bit more convenient than naming table-qualified columns (or even worse grouping by derived expressions).

         I agree- more specification and having the code deal with it would be better. In Practical Data Science with R we didn’t talk about pseudo-aggregators, but did talk about column roles (keys, values) and had a concept of a “derived column.” A system like you are talking about could deal with work automatically if we had such annotations.

         Loading...