R: Within a group, retain entries based on multiple criteria

I have a table such as outlined below:

The table should end up looking as follows:

Thereby, I dont care if blabla1 is associated with dRAQx or Yprst (same for blabla2). What is crucial is that one instance of the title is maintained and one instance of the id.

sampledata <- data.frame(
  title = c("blublu", rep("blabla", 4), "pips"),
  full_name = c("some title", rep("title blabla1", 2), rep("title blabla2", 2), "some other title"),
  id = c("dk29D", rep(c("dRAQx","Yprst"), 2), "qW45m")
)

I've tried many things but the best result was:

sampledata %>% 
      group_by(title) %>% 
      mutate(id = sample(id, length(id), replace = FALSE)) %>%
      distinct(full_name, .keep_all = TRUE) %>%
      mutate(same_id = any(duplicated(id)))

The issue here is that it's random and sometimes the IDs that are retained are the same (as kept track of by the same_id variable).

I hope my issue is understandable.

I'm looking forward to your responses. Thank you in advance.

We could do it this way:

The trick is to use the number in full_name as the row_number that should be filtered:

library(dplyr)
library(parse_number)

sampledata %>%  
  group_by(title, full_name) %>% 
  mutate(x = coalesce(parse_number(full_name), 1)) %>% 
  filter(x == row_number()) %>% 
  ungroup() %>% 
  select(-x)

  title  full_name        id   
  <chr>  <chr>            <chr>
1 blublu some title       dk29D
2 blabla title blabla1    dRAQx
3 blabla title blabla2    Yprst
4 pips   some other title qW45m
Warning message:
There were 2 warnings in `mutate()`.
The first warning was:
ℹ In argument: `x = coalesce(parse_number(full_name), 1)`.
ℹ In group 3: `title = "blublu"`, `full_name = "some title"`.
Caused by warning:
! 1 parsing failure.
row col expected     actual
  1  -- a number some title
ℹ Run dplyr::last_dplyr_warnings() to see the 1 remaining warning.

Since I posted my question I thought of a possible solution that is not elegant but does its job. Maybe it'll be helpful for others in future:

sampledata <- sampledata %>% 
  group_by(title) %>% 
  mutate(id_count = n_distinct(id)) %>%
  ungroup() %>%
  arrange(title, id) %>%
  group_by(title) %>%
  group_modify(~ {
    x <- .$id_count[1]
    if (x != 1) {
      x <- x+1
    }
    slice(., seq(1, nrow(.), x[1]))
  }) %>%
  ungroup() %>%
  select(-id_count)

This code first counts, how many IDs are found within each title-group. Then it orders the table based on title and id so that in the text step, the correct rows will be chosen.

group_modify keeps every row if there is only 1 ID associated with a title. If there are two IDs associated with the title, it keeps only rows 1 and 4 (=1+2+1) per group. If there are three IDs per group, it retains rows 1, 5 (1+3+1) and 9 (5+3+1) etc. This way, all titles are retained and all IDs, the rows per group are maximally different (due to ordering the table first).

Feel free to test with 2 and 3 repetitions:

sampledata <- data.frame(
  title = c("blublu", rep("blabla", 4), "pips"),
  full_name = c("some title", rep("title blabla1", 2), rep("title blabla2", 2), "some other title"),
  id = c("dk29D", rep(c("dRAQx","Yprst"), 2), "qW45m")
)

sampledata <- data.frame(
  title = c("blublu", rep("blabla", 9), "pips"),
  full_name = c("some title", rep("title blabla1", 3), rep("title blabla2", 3), rep("title blabla3", 3), "some other title"),
  id = c("dk29D", rep(c("dRAQx","Yprst","Aw3r8"), 3), "qW45m")
)