Filter row based on max & unique value in one column

user_12070334 0
Viewed 706

Expected outputIts a bit tricky to explain, Ill try my best, query below. I have a df as below. I need to filter rows by group based on maximum pop in country column but which has not already occurred in the above groups. (As per output (image), the reason why A didnt feature in group2 because it had already occured in Group 1)

In short, I need to get unique values in country column at the same time get maximum value in pop (on a group level). I hope picture can convey what I could not. (Tidyverse solution preferred)

[![Expected output][2]][2]

df<- structure(list(Group = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), country = c("A", "B", "C", "A", "E", "F", "A", "E", "G"), pop = c(200L, 100L, 50L, 200L, 150L, 120L, 200L, 150L, 
100L)), class = "data.frame", row.names = c(NA, -9L))
dplyr tidyverse r
4 Answers

I think this will do. Explanation of syntax

  • split the data into list for each group
  • leave first group (as it will be used as .init in next step but after filtering for the max of pop value.
  • use purrr::reduce here which will reduce the list of tibbles to a single tibble
  • iterations used in reduce
    • .init used as filtered first group
    • thereafter countries in previous groups removed through anti_join
    • this data filtered for max pop again
    • added the previously filtered countries by bind_rows()
  • Thus, in the end we will have desired tibble.
df %>% group_split(Group) %>% .[-1] %>%
  reduce(.init =df %>% group_split(Group) %>% .[[1]] %>% 
               filter(pop == max(pop)), 
             ~ .y  %>%
               anti_join(.x, by = c("country" = "country")) %>% 
               filter(pop == max(pop)) %>%
               bind_rows(.x) %>% arrange(Group)) 

# A tibble: 3 x 3
  Group country   pop
  <int> <chr>   <int>
1     1 A         200
2     2 E         150
3     3 G         100
user_28848590

You can create a helper function that writes the maximum pop from each group in a vector and use it to filter the dataframe.

library(tidyverse)
max_values <- c()

helper <- function(dat, ...){
  dat <- dat[!(dat %in% max_values)] # exclude maximum values from previous groups
  max_value <- max(dat) # get current max. value
  max_values <<- c(max_values, max_value) # append 
  return(max_value)
}

df %>% 
  group_by(Group) %>% 
  filter(pop == helper(pop))

which gives you:

# A tibble: 3 x 3
# Groups:   Group [3]
  Group country   pop
  <int> <chr>   <int>
1     1 A         200
2     2 E         150
3     3 H         120

Data used:

> df
   Group country pop
1      1       A 200
2      1       B 100
3      1       C  50
4      2       A 200
5      2       E 150
6      2       F 120
7      3       A 200
8      3       E 150
9      3       G 100
10     3       H 120
user_154926420

Here is another possibility, but Overly Simplified in that it does not take into account the possibility of a group having a higher population in a Group where it does not win.

library(dplyr)
df<- structure(list(Group = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), country = c("A", "B", "C", "A", "E", "F", "A", "E", "G"), pop = c(200L, 100L, 50L, 200L, 150L, 120L, 200L, 150L, 
100L)), class = "data.frame", row.names = c(NA, -9L))

df %>% 
  group_by(country) %>% 
  summarize(popmax = max(pop))  %>% 
  inner_join(df, by = c("popmax" = 'pop')) %>% 
  rename(country = country.y) %>% 
  select(-country.x) %>% 
  group_by(country) %>% 
  arrange(Group) %>% 
  slice(1) %>% 
  ungroup() %>% 
  group_by(Group) %>% 
  arrange(country) %>% 
  slice(1) %>%  
  select(Group, country, popmax) %>% 
  rename(pop = popmax)

My answer fails (while other answers don't) with this data set:

df <- tribble(
  ~Group, ~ country, ~pop,
  1     ,         'A',    200,
  1     ,         'B',    100,
  1     ,         'C',     50,
  1     ,         'G',    150,
  2     ,         'A',    200,
  2     ,         'E',    150,
  2     ,         'F',    120,
  3     ,         'A',    200,
  3     ,         'E',    150,
  3     ,         'G',    100
)
user_46299160

Update @Crestor who is claiming that my answer is not correct.

  • My answer is correct because my code gives the desired output as requested by OP.

  • Your objection that my code does not work on another scenario may be correct, but in this setting it is irrelevant, as my answer was only intended to solve the task at hand.

  • Here is the answer to your raised scenario with this dataset:

df1 <- structure(list(Group = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), 
    country = c("A", "B", "C", "A", "E", "F", "A", "E", "G"), 
    pop = c(200L, 100L, 250L, 220L, 150L, 120L, 200L, 150L, 100L
    )), row.names = c(NA, -9L), class = c("tbl_df", "tbl", "data.frame"
))

expected output by Crestor:

# A tibble: 3 x 3
  Group country   pop
  <int> <chr>   <int>
1     1 C         250
2     2 A         220
3     3 E         150

My code for your scenario @crestor

library(dplyr)

df1 %>% 
group_by(country) %>% 
arrange(Group) %>% 
filter(pop == max(pop)) %>% 
group_by(Group) %>% 
filter(pop == max(pop))

Output:

  Group country   pop
  <int> <chr>   <int>
1     1 C         250
2     2 A         220
3     3 E         150

Original answer to the question by OP

To keep it simple: First arrange to bring your dataset in position. Then group_by and keep first row in each group with slice. Then group_by Group and filter the max pop

library(dplyr)
df %>% 
  arrange(country, pop) %>% 
  group_by(country) %>% 
  slice(1) %>% 
  group_by(Group) %>% 
  filter(pop==max(pop))

Output:

  Group country   pop
  <int> <chr>   <int>
1     1 A         200
2     2 E         150
3     3 G         100
user_133216470
Related