Group by multiple variables and summarise dplyr

Question

I'm trying to average CO2 concentration data every 30 seconds, for each of my sensors:

    head(df)
# A tibble: 6 x 7
# Groups: BinnedTime [1]

  Sensor Date       Time   calCO2 DeviceTime          cuts   BinnedTime         
  <fctr> <date>     <time>  <dbl> <dttm>              <fctr> <chr>              
1 N1     2019-02-12 13:24     400 2019-02-12 13:24:02 (0,10] 2019-02-12 13:24:02
2 N1     2019-02-12 13:24     400 2019-02-12 13:24:02 (0,10] 2019-02-12 13:24:02
3 N1     2019-02-12 13:24     400 2019-02-12 13:24:03 (0,10] 2019-02-12 13:24:03
4 N2     2019-02-12 13:24     400 2019-02-12 13:24:03 (0,10] 2019-02-12 13:24:02
5 N3     2019-02-12 13:24     400 2019-02-12 13:24:03 (0,10] 2019-02-12 13:24:02
6 N3     2019-02-12 13:24     400 2019-02-12 13:24:05 (0,10] 2019-02-12 13:24:04

I use:

df %>%
  group_by(Sensor)%>%
  group_by(BinnedTime = cut(DeviceTime, breaks="30 sec")) %>%
  summarize(Concentration = mean(calCO2))

But it doesn't group by Sensor first, it ignores them and calculates the average over the BinnedTime instead. Any thoughts would be welcomed.

I've read about .dots=c("Sensor","BinnedTime") but this doesn't work.

Note, I haven't created dummy data so you can see exactly what mine looks like, as there seem to be some subtleties with time and date that I can't quite get my head around.

You need to set `add = TRUE` in the second `group_by`, otherwise the grouping by BinnedTime overwrites the grouping by Sensor. Alternatively you could also do: `df %>% group_by(Sensor,BinnedTime = cut(DeviceTime, breaks="30 sec")) %>% summarize(Concentration = mean(calCO2))` — kath, Feb 17 '19 at 16:55
Works perfectly, thank you! Small add add-on but related question- How do I get dplyr to just add Concentration as an additional column of df instead of dropping all the ones I haven't used in the above function? — HCAI, Feb 17 '19 at 17:01
Use `mutate` instead of `summarize`, but this will duplicate the value of Concentration for each Sensor and BinnedTime — kath, Feb 17 '19 at 17:03

score 1 · Accepted Answer · answered Feb 18 '19 at 00:34

So to summarize the comments by @kath with some improvements to address your follow-on question:

df %>%
    group_by(Sensor, BinnedTime = cut(DeviceTime, breaks="30 sec")) %>%
        mutate(Concentration = mean(calCO2)) %>%
    ungroup()

The above will maintain all columns, but duplicate the Concentration calculation for each row of the df. An alternative that would allow you to both roll up and retain more columns of interest is to simply add them to the summarize operation, as illustrated below.

    df %>%
    group_by(Sensor, BinnedTime = cut(DeviceTime, breaks="30 sec")) %>%
        summarize(Concentration = mean(calCO2),
                   Date = min(Date),
                   Time = min(Time),
                   StartDeviceTime = min(DeviceTime),
                   EndDeviceTime = max(DeviceTime))

Group by multiple variables and summarise dplyr

1 Answers1

Linked