create a data frame by selecting the max from subset of a data set

Question

I wish to create a data frame from another data frame by selecting the max of subsets from the 1st data frame. Say I have the 1st data frame

df:

name     date      weekday    util
----     ----      -------    ---- 
alpha    201801          1    10.1
alpha    201801          2    7.1
alpha    201801          3    1.1 
alpha    201801          4    4.1  
bravo    201801          1    9.9
bravo    201801          2    8.9
bravo    201801          3    1.9
bravo    201801          4    11.9
charlie  201801          1    0.1
charlie  201801          2    2.0
charlie  201801          3    7.3
charlie  201801          4    5.0

I wish to create another data frame using the max util for every unique date, name tuple

df_unique:

name       date       util
-----      ----       ----
alpha      201801     10.1
bravo      201801     11.9
charlie    201801     7.3

I know we can choose a max by using

>max(df[df$name == 'alpha' && df$date == 201801, df$util])
10.1

However how would we do this for every unique name, date tuple. Is this even possible or do I need to create a function to do this?

`aggregate(df1["util"], df1[c("name", "date")], max)` – d.b Aug 12 '18 at 02:56 — d.b, Aug 12 '18 at 02:56

score 0 · Answer 1 · answered Aug 12 '18 at 03:04

Here is a solution using the data.table package.

library(data.table)

setDT(dat)

dat2 <- dat[, .(util = max(util)), by = .(name, date)]
dat2
#       name   date util
# 1:   alpha 201801 10.1
# 2:   bravo 201801 11.9
# 3: charlie 201801  7.3

Or we can use the dplyr package.

library(dplyr)

dat2 <- dat %>%
  group_by(name, date) %>%
  summarise(util = max(util)) %>%
  ungroup()
dat2
# # A tibble: 3 x 3
#   name      date  util
#   <chr>    <int> <dbl>
# 1 alpha   201801  10.1
# 2 bravo   201801  11.9
# 3 charlie 201801   7.3

DATA

dat <- read.table(text = "name     date      weekday    util
                  alpha    201801          1    10.1
                  alpha    201801          2    7.1
                  alpha    201801          3    1.1 
                  alpha    201801          4    4.1  
                  bravo    201801          1    9.9
                  bravo    201801          2    8.9
                  bravo    201801          3    1.9
                  bravo    201801          4    11.9
                  charlie  201801          1    0.1
                  charlie  201801          2    2.0
                  charlie  201801          3    7.3
                  charlie  201801          4    5.0",
                  header = TRUE, stringsAsFactors = FALSE)

create a data frame by selecting the max from subset of a data set

1 Answers1