Remove duplicates based on conditions in rows in a dataframe

user_7635619 0
Viewed 648

I have a dataframe containing many duplicate names, below is a reproducible example.
I am trying to clean the dataset by removing rows that have duplicate names AND lowest information.
I have added a column where I calculate % of NA of cells in each row, in my example I have called it %_Scoring.

Among the duplicate name rows I want to keep the row that have the lowest %_Scoring (% of NA)
N:B If %_Scoring is equal, it does not matter, one of the two rows should still be removed.

data_people <- "https://raw.githubusercontent.com/max9nc9/Temp/main/data_people.csv"
data_people <- read.csv(data_people, sep = ",")

In the above data example, I would only keep 2 rows:

  • First row is Margarita Pan
  • Second row is John Doe where %_Scoring = 0.56
data-wrangling dataframe duplicates r
3 Answers

Use slice_max after grouping by 'Name'

library(dplyr)
data_people %>% 
    group_by(Name) %>%
    slice_max(n = 1, order_by = X._Scoring) %>%
    ungroup

-output

# A tibble: 2 x 4
  Name          Information                    Height X._Scoring
  <chr>         <chr>                           <dbl>      <dbl>
1 John Doe      This is an information           1.88       0.89
2 Margarita Pan This is an information as well   1.47       0.78

Or if we want to keep the minimum value, then use slice_min

data_people %>% 
    group_by(Name) %>%
    slice_min(n = 1, order_by = X._Scoring) %>%
    ungroup
# A tibble: 2 x 4
  Name          Information                    Height X._Scoring
  <chr>         <chr>                           <dbl>      <dbl>
1 John Doe      This is an information          NA          0.56
2 Margarita Pan This is an information as well   1.47       0.78
user_37322710 
library(dplyr)
data_people %>% 
    group_by(Name) %>% 
    arrange(X._Scoring) %>% 
    filter(!duplicated(Name) & min(X._Scoring))

Output

  Name          Information                    Height X._Scoring
  <chr>         <chr>                           <dbl>      <dbl>
1 John Doe      This is an information          NA          0.56
2 Margarita Pan This is an information as well   1.47       0.78
user_133216470

A base R option with duplicated + ave

subset(
  data_people,
  !duplicated(Name) & ave(rowSums(!is.na(data_people)), Name, FUN = function(x) x == max(x))
)

gives

           Name                    Information Height X._Scoring
1      John Doe         This is an information   1.88       0.89
2 Margarita Pan This is an information as well   1.47       0.78
user_121587570
Related