Select matches based on the value of another column

user_12457626 0
Viewed 460

I have a dataframe with company matches and I want to extract certain rows(matches)

my input

df <- data.frame(company_us=
c("apple","google","netflix","apple","netflix","google"),
                company_eu = c("nokia","vodafone","sky","sky","nokia","vodafone"),
                difference = c(5,5,5,10,10,10)
       )

#the df

company_us company_eu difference
1      apple      nokia          5
2     google   vodafone          5
3    netflix        sky          5
4      apple        sky         10
5    netflix      nokia         10
6     google   vodafone         10

For each company_us find the pairs, here for apple it would be apple nokia and apple sky I want to select only the match with the smallest difference. In this case the row apple,nokia,5

the output I want is:

 company_us company_eu difference
1      apple      nokia          5
2     google   vodafone          5
3    netflix        sky          5

I have no Idea how to go about this problem. If anyone could recommend literature or something else on how to tackle a problem like this I would be very grateful!

dplyr dataframe r
3 Answers

With dplyr you can use the slice_min() function to retrieve the minimum value of a variable inside a group

library(dplyr)

df %>% 
  group_by(company_us) %>% 
  slice_min(difference)

# A tibble: 3 x 3
# Groups:   company_us [3]
  company_us company_eu difference
  <chr>      <chr>           <dbl>
1 apple      nokia               5
2 google     vodafone            5
3 netflix    sky                 5

Since you seem lacking some knowledge about operations within groups of data, I leave you this link with some examples of applications of the group_by() function.

user_74654620

Using ave from base R

df[with(df, difference == ave(difference, company_us, FUN = min)),]
user_37322710

A data.table option using ´which.min`

> setDT(df)[, .SD[which.min(difference)], company_us]
   company_us company_eu difference
1:      apple      nokia          5
2:     google   vodafone          5
3:    netflix        sky          5
user_121587570
Related