Parsing Multiple Conditions through grouped variables with dplyr

Question

I'm trying to create multiple conditions through grouped variables in R. What I am trying to do is get the min and max of Index within the grouped variables and extract out the respective price. The desired output is shown below

df <- data.frame(ID = c("ABC", "ABC", "BCD", "BCD", "BCD", "DEF", "DEF"), 
                 Price = c(31, 81, 100, 84, 15, 31, 42),
                 Index = c(3,6,2,9,5,12,18))

df
   ID Price Index
1 ABC    31     3
2 ABC    81     6
3 BCD   100     2
4 BCD    84     9
5 BCD    15     5
6 DEF    31    12
7 DEF    42    18

For instance if we look at ID = "BCD", there are 3 entries. Based on the index, at min(index) = 2, the price = 100 and at max(index) = 9, the price is 84

This does not perform the grouping though

df %>% group_by(ID) %>% mutate(firstPrice = min(df$Order), lastPrice = max(df$Order)) 

  ID    Price Order firstPrice lastPrice
  <fct> <dbl> <dbl>      <dbl>     <dbl>
1 ABC      31     3          2        18
2 ABC      81     6          2        18
3 BCD     100     2          2        18
4 BCD      84     9          2        18
5 BCD      15     5          2        18
6 DEF      31    12          2        18
7 DEF      42    18          2        18

Intended Output

ID    Price     Order firstPrice lastPrice
1 ABC      31     3        31       81
2 ABC      81     6        31       81
3 BCD     100     2        100      84
4 BCD      84     9        100      84
5 BCD      15     5        100      84
6 DEF      31    12        31       42
7 DEF      42    18        31       42

score 1 · Accepted Answer · answered Jan 18 '19 at 09:38

We can group_by ID and use which.min and which.max to get the index and then get the corresponding Price from it

library(dplyr)

df %>%
  group_by(ID) %>%
  mutate(firstPrice = Price[which.min(Index)], 
         lastPrice = Price[which.max(Index)])



#   ID    Price Index firstPrice lastPrice
#  <fct> <dbl> <dbl>      <dbl>     <dbl>
#1 ABC      31     3         31        81
#2 ABC      81     6         31        81
#3 BCD     100     2        100        84
#4 BCD      84     9        100        84
#5 BCD      15     5        100        84
#6 DEF      31    12         31        42
#7 DEF      42    18         31        42

@Javier if useful, please consider accepting as answer. – zx8754 Jan 18 '19 at 10:55 — zx8754, Jan 18 '19 at 10:55

score 1 · Answer 2 · answered Jan 18 '19 at 09:44

You can also do:

df %>%
 group_by(ID) %>%
 mutate(firstprice = Price[Index == min(Index)],
        lastprice = Price[Index == max(Index)])

  ID    Price Index firstprice lastprice
  <fct> <dbl> <dbl>      <dbl>     <dbl>
1 ABC     31.    3.        31.       81.
2 ABC     81.    6.        31.       81.
3 BCD    100.    2.       100.       84.
4 BCD     84.    9.       100.       84.
5 BCD     15.    5.       100.       84.
6 DEF     31.   12.        31.       42.
7 DEF     42.   18.        31.       42.

Or:

df %>%
 group_by(ID) %>%
 arrange(Index, .by_group = TRUE) %>%
 mutate(firstprice = Price[Index == first(Index)],
        lastprice = Price[Index == last(Index)])

  ID    Price Index firstprice lastprice
  <fct> <dbl> <dbl>      <dbl>     <dbl>
1 ABC     31.    3.        31.       81.
2 ABC     81.    6.        31.       81.
3 BCD    100.    2.       100.       84.
4 BCD     15.    5.       100.       84.
5 BCD     84.    9.       100.       84.
6 DEF     31.   12.        31.       42.
7 DEF     42.   18.        31.       42.

akrun · Answer 3 · 2019-01-18T10:08:59.550

An option using data.table

library(data.table)
setDT(df)[, .(firstprice = Price[which.min(Index)], 
      lastprice = Price[which.max(Index)]), ID]
#     ID firstprice lastprice
#1: ABC         31        81
#2: BCD        100        84
#3: DEF         31        42

Or another option with tidyverse

library(tidyverse)
df %>% 
  group_by(ID) %>%
  filter(Index %in% range(Index)) %>% 
  mutate(newCol = c("firstprice", "lastprice")) %>% 
  select(-Index) %>% 
  spread(newCol, Price)
# A tibble: 3 x 3
# Groups:   ID [3]
#  ID    firstprice lastprice
#  <fct>      <dbl>     <dbl>
#1 ABC           31        81
#2 BCD          100        84
#3 DEF           31        42

Parsing Multiple Conditions through grouped variables with dplyr

3 Answers3