Subsetting a dataframe in R by retaining first occurrences of unique elements from all columns

user_13439978 0
Viewed 85

df is a test dataframe which has 5 rows and 6 columns and it is a subset of a much larger dataframe (dimensions: 1000000 X 30).

df <- data.frame(
 Hits = c("Hit1", "Hit2", "Hit3", "Hit4", "Hit5"),
 category1 = c("a", "", "b", "a", ""),
 category2 = c("c", "", "", "d", "c"),
 category3 = c("", "", "e", "f", "f"),
 category4 = c("", "", "", "", ""),
 category5 = c("i", "", "i", "j", ""))

df looks like this

enter image description here

For each column from category1 to category5 I need to retain only the first occurrences of all the unique elements. e.g. For category1, the unique elements are a and b and their first occurrences are in rows 1 and 3 respectively. So rows 1 and 3 should be retained, and so on. The output should look something like this

enter image description here

subset dataframe r
4 Answers

Using lapply and duplicated you could first replace duplicates per column by "", then filter for rows containing at least one non "" string:

df[-1] <- lapply(df[-1], function(x) {
    x[duplicated(x)] <- ""
    x
})
df <- df[rowSums(!df[-1] == "") > 0, ]
df
#>   Hits category1 category2 category3 category4 category5
#> 1 Hit1         a         c                             i
#> 3 Hit3         b                   e                    
#> 4 Hit4                   d         f                   j
user_129938610

I was challenged to find a tidyverse solution and ended up with this, not to recommend as solution but as a learning effect:

The basic idea was to bring the data in long format remove the duplicates and bring back to wide format, but this "simple" idea turned out to be quite complicated as you can see here:

library(tidyverse)
df %>%
  pivot_longer(
    everything()
  ) %>% 
  mutate(value = na_if(value, "")) %>% 
  unique() %>%
  group_by(id = cumsum(name=="Hits")) %>% 
  mutate(row = row_number()) %>% 
  pivot_wider() %>% 
  fill(everything(), .direction = "updown") %>% 
  filter(if_any(category1:category5, ~ !is.na(.))) %>% 
  slice(1) %>% 
  ungroup() %>% 
  select(-c(id, row)) %>% 
  mutate(across(everything(), ~replace_na(.,"")))

  Hits  category1 category2 category3 category4 category5
  <chr> <chr>     <chr>     <chr>     <chr>     <chr>    
1 Hit1  "a"       "c"       ""        ""        "i"      
2 Hit3  "b"       ""        "e"       ""        ""       
3 Hit4  ""        "d"       "f"       ""        "j"
user_133216470

Another possible solution, based on dplyr and purrr::map_dfc:

library(tidyverse)

df <- data.frame(
  Hits = c("Hit1", "Hit2", "Hit3", "Hit4", "Hit5"),
  category1 = c("a", "", "b", "a", ""),
  category2 = c("c", "", "", "d", "c"),
  category3 = c("", "", "e", "f", "f"),
  category4 = c("", "", "", "", ""),
  category5 = c("i", "", "i", "j", ""))

df %>% 
  map_dfc(~ if_else(duplicated(.x), "", .x)) %>% 
  filter(rowSums(. == "") != 5)

#> # A tibble: 3 × 6
#>   Hits  category1 category2 category3 category4 category5
#>   <chr> <chr>     <chr>     <chr>     <chr>     <chr>    
#> 1 Hit1  "a"       "c"       ""        ""        "i"      
#> 2 Hit3  "b"       ""        "e"       ""        ""       
#> 3 Hit4  ""        "d"       "f"       ""        "j"
user_115644870

A data.table version, following @PaulS's tidyverse approach; Might help speed wise with 1,000,000 rows, etc.

library(data.table)
setDT(df)

df = cbind(df[,1], df[,-1][, lapply(.SD, \(x) fifelse(duplicated(x),"",x))])
df[rowSums(df[,-1]=="")<5]

Output

   Hits category1 category2 category3 category4 category5
1: Hit1         a         c                             i
2: Hit3         b                   e                    
3: Hit4                   d         f                   j
user_44475400
Related