CODEWITHSUNDEEP
rdataframedplyr
user157545
user157545

Reputation: 189

How to apply a sequence of custom operations on a group by

I have a dataframe of the form:

ID Rank Var1 Var2
a 3 na 6
b 2 3 na
a 1 na 5
a 2 2 1
b 1 7 1

I am trying to apply a sequence of operations:

  1. Group by ID column
  2. Sort by Rank Column descending
  3. For each variable column, select the highest ranked non na value So the output would be
ID Var1 Var 2
a 2 6
b 3 1

So far I have

df %>% group_by(ID) %>% arange(desc(Rank))

But am unsure how to then filter each column for non NA and select the highest. The NA filter should be applied on a column basis only - if Var 1 has na, it should be excluded from the calculation of Var 1, but not for Var2.

Upvotes: 1

Views: 71

Answers (3)

PaulS
PaulS

Reputation: 25353

Another possible solution:

library(tidyverse)

map(c("Var1", "Var2"), ~ 
      select(df, ID, Rank, all_of(.x)) %>% 
      group_by(ID) %>% 
      drop_na() %>% 
      slice_max(Rank) %>% 
      ungroup %>% select(-Rank)) %>% 
  reduce(inner_join)

#> Joining, by = "ID"
#> # A tibble: 2 x 3
#>   ID     Var1  Var2
#>   <chr> <int> <int>
#> 1 a         2     6
#> 2 b         3     1

Upvotes: 0

Carl
Carl

Reputation: 7540

library(tidyverse)

tribble(
  ~ID, ~Rank, ~Var1, ~Var2,
  "a", 3, NA, 6,
  "b", 2, 3, NA,
  "a", 1, NA, 5,
  "a", 2, 2, 1,
  "b", 1, 7, 1
) |> 
  group_by(ID) |> 
  arrange(ID, desc(Rank)) |> 
  fill(everything(), .direction = "up") |> 
  summarise(across(starts_with("Var"), first))
#> # A tibble: 2 × 3
#>   ID     Var1  Var2
#>   <chr> <dbl> <dbl>
#> 1 a         2     6
#> 2 b         3     1

Created on 2022-05-11 by the reprex package (v2.0.1)

Upvotes: 1

Ma&#235;l
Ma&#235;l

Reputation: 52209

Select the first non-NA value across the selected variables after grouping:

library(dplyr)

df %>% 
  na_if("na") %>% 
  group_by(ID) %>% 
  arrange(desc(Rank), .by_group = T) %>% 
  summarise(across(Var1:Var2, ~ head(.x[!is.na(.x)], 1)))

# A tibble: 2 × 3
  ID    Var1  Var2 
  <chr> <chr> <chr>
1 a     2     6    
2 b     3     1

or with first:

df %>% 
  na_if("na") %>% 
  group_by(ID) %>% 
  summarise(across(Var1:Var2, ~ first(.x[!is.na(.x)], order_by = "Rank")))

data

df <- read.table(header = T, text = "ID Rank    Var1    Var2
a   3   na  6
b   2   3   na
a   1   na  5
a   2   2   1
b   1   7   1")

Upvotes: 4

Related Questions