Getting multiple values per group based on two criteria

user_443425 0
Viewed 64

I'm trying to group a dataset and get the first and highest values based on two separate measures of time and speed. So I need the time and speed for the earliest record in each group and then the time and speed for the fastest record in each group. I've got this far but need some help...

library(tidyverse)
group <- c(1,1,1,1,1,2,2,3,3,4,4,4,4,4,4)
time <- c(1,6,4,5,7,12,10,2,3,8,9,11,13,14,15)
speed <- c(17,6, 99, 34, 12, 5, 67, 43, 23, 12, 15, 78, 61, 78, 20)
data = data.frame(group, time, speed)
summary = data %>% 
  group_by(group) %>%
  summarise(
    firstTime =  # lowest time
    HighestSpeedTime = , # time for highest speed
    firstSpeed = , #speed for lowest time
    highestSpeed = max(speed), # highest speed
  )
dplyr r
4 Answers

Update: This should work: In group 4 we have ties therefore 2 rows:(we have at two time points the highest speed)!

library(dplyr)
data %>% 
  group_by(group) %>%
  summarise(
    firstTime = min(time), # lowest time
    HighestSpeedTime = time[which(speed==max(speed))], # time for highest speed
    firstSpeed = speed[which(time==min(time))],#speed for lowest time
    highestSpeed = max(speed) # highest speed
  )

output:

  group firstTime HighestSpeedTime firstSpeed highestSpeed
  <dbl>     <dbl>            <dbl>      <dbl>        <dbl>
1     1         1                4         17           99
2     2        10               10         67           67
3     3         2                2         43           43
4     4         8               11         12           78
5     4         8               14         12           78
user_133216470

Does this work?

library(tidyverse)
group <- c(1,1,1,1,1,2,2,3,3,4,4,4,4,4,4)
time <- c(1,6,4,5,7,12,10,2,3,8,9,11,13,14,15)
speed <- c(17,6, 99, 34, 12, 5, 67, 43, 23, 12, 15, 78, 61, 78, 20)
data = data.frame(group, time, speed)

summary <-  data |> 
  arrange(group, time) |> 
  group_by(group) |> 
  summarise(
    firsttime = min(time),
    highest_speed = max(speed)
  ) |> 
  left_join(data, by = c("group", "highest_speed" = "speed")) |> 
  group_by(group) |> 
  slice(1) |> 
  rename(highest_speed_time = time) |> 
  left_join(data, by = c("group", "firsttime" = "time")) |> 
  rename(first_speed = speed)

summary

  # group firsttime highest_speed highest_speed_time first_speed
  # <dbl>     <dbl>         <dbl>              <dbl>       <dbl>
  #     1         1            99                  4          17
  #     2        10            67                 10          67
  #     3         2            43                  2          43
  #     4         8            78                 11          12
user_86994630

Another solution, with a chained inner_join:

library(tidyverse)

data %>% 
  group_by(group) %>% 
  summarise(firstTime = min(time)) %>% 
  inner_join(data,by=c("group", "firstTime"="time")) %>% 
  rename(firstSpeed=speed) %>% 
  inner_join(
    data %>% 
      group_by(group) %>% 
      summarise(highestSpeed = max(speed)) %>% 
      inner_join(data,by=c("group", "highestSpeed"="speed")) 
  ) %>% 
  relocate(highestTime=time, .before="highestSpeed")

#> Joining, by = "group"
#> # A tibble: 5 × 5
#>   group firstTime firstSpeed highestTime highestSpeed
#>   <dbl>     <dbl>      <dbl>       <dbl>        <dbl>
#> 1     1         1         17           4           99
#> 2     2        10         67          10           67
#> 3     3         2         43           2           43
#> 4     4         8         12          11           78
#> 5     4         8         12          14           78

An alternative solution, based on purrr::map_dfr:

library(tidyverse)

data %>% 
  group_split(group) %>% 
  map_dfr(
    ~ data.frame(
      group = .x$group[1],
      firstTime = .x$time[min(.x$time) == .x$time],
      firstSpeed = .x$speed[min(.x$time) == .x$time],
      highestTime = .x$time[max(.x$speed) == .x$speed],
      highestSpeed = .x$speed[max(.x$speed) == .x$speed]))

#>   group firstTime firstSpeed highestTime highestSpeed
#> 1     1         1         17           4           99
#> 2     2        10         67          10           67
#> 3     3         2         43           2           43
#> 4     4         8         12          11           78
#> 5     4         8         12          14           78

And more succinctly:

library(tidyverse)

data %>% 
  group_split(group) %>% 
  map_dfr(~ data.frame(
    group = integer(), firstTime = integer(), firstSpeed = integer(),
    highestTime = integer(), highestSpeed = integer()) %>% 
      add_row(!!!setNames(c(.x$group[1],.x[min(.x$time) == .x$time, -1],
            .x[max(.x$speed) == .x$speed, -1]), names(.))))

#>   group firstTime firstSpeed highestTime highestSpeed
#> 1     1         1         17           4           99
#> 2     2        10         67          10           67
#> 3     3         2         43           2           43
#> 4     4         8         12          11           78
#> 5     4         8         12          14           78
user_115644870

here is a data.table approach

library(data.table)
setDT(data)
temp <- data[data[, .I[speed == max(speed)], by = .(group)]$V1]
setnames(temp, new = c("group", "maxSpeedTime", "maxSpeed"))
# join together
data[, .(firstTime  = time[1],
         firstSpeed = speed[1]), 
     by = .(group)][temp, on = .(group)]
#    group firstTime firstSpeed maxSpeedTime maxSpeed
# 1:     1         1         17            4       99
# 2:     2        12          5           10       67
# 3:     3         2         43            2       43
# 4:     4         8         12           11       78
# 5:     4         8         12           14       78
user_63562780
Related