Delete duplicates with multiple grouping conditions

user_15982725 0
Viewed 71

I want to delete duplicates with multiple grouping conditions but always get way less results than expected.

The dataframe compares two companies per year. Like this:

year c1 c2
2000 a b
2000 a c
2000 a d
2001 a b
2001 b d
2001 a c

For every c1 I want to look at c2 and delete rows which are in the previous year. I found a similar problem but with just one c. Here are some of my tries so far:

df<- df%>%
  group_by(c1,c2) %>%
  mutate(dup = n() > 1) %>%
  group_split() %>%
  map_dfr(~ if(unique(.x$dup) & (.x$year[2] - .x$year[1]) == 1) {
    .x %>% slice_head(n = 1)
  } else {
    .x
  }) %>%
  select(-dup) %>%
  arrange(year)

df<- sqldf("select a.*
  from df a
  left join df b on b.c1=a.c1 and b.c2 = a.c2 and b.year = a.year - 1
  where  b.year is null")

The desired output for the example would be:

year c1 c2
2000 a b
2000 a c
2000 a d
2001 b d
r
6 Answers

Assuming you want to check duplicate in the previous year only. So showing it to you on a modified sample

library(tidyverse)

df <- read.table(header = T, text = 'year   c1  c2
2000    a   b
2000    a   c
2000    a   d
2001    a   b
2001    b   d
2001    a   c
2002  a d')

df %>%
  filter(map2_lgl(df$year, paste(df$c1, df$c2), ~ !paste(.x -1, .y) %in% paste(df$year, df$c1, df$c2)))
#>   year c1 c2
#> 1 2000  a  b
#> 2 2000  a  c
#> 3 2000  a  d
#> 4 2001  b  d
#> 5 2002  a  d

Created on 2021-07-08 by the reprex package (v2.0.0)

user_28848590

Some of the other solutions won't work because I think they ignore the fact that you will probably have many years and want to eliminate duplicates from only the prior.

Here is something fairly simple. You could do this in some map function or whatnot, but sometimes a simple loop does just fine. For each year of data, use anti_join() to return only those values from the current year which are not in the prior year. Then just restack the data.

df_split <- df %>% 
  group_split(year)

for (this_year in 2:length(df_split)) {
  
  df_split[[this_year]] <- df_split[[this_year]] %>% 
    anti_join(df_split[[this_year - 1]], by = c("c1", "c2"))
  
}

bind_rows(df_split)
# # A tibble: 4 x 3
#    year c1    c2   
#   <int> <chr> <chr>
# 1  2000 a     b    
# 2  2000 a     c    
# 3  2000 a     d    
# 4  2001 b     d

Edit

Another approach is to add a dummy column for the prior year and just use an anti_join() with that. This is probably what I would do.

df %>% 
  mutate(prior_year = year - 1) %>% 
  anti_join(df, by = c(prior_year = "year", "c1", "c2")) %>% 
  select(-prior_year)
user_109174790

You can also use the following solution.

library(dplyr)
library(purrr)

df %>%
  filter(pmap_int(list(df$c1, df$c2, df$year), ~ df %>%
                    filter(year %in% c(..3, ..3 - 1)) %>%
                    rowwise() %>%
                    mutate(output = all(c(..1, ..2) %in% c_across(c1:c2))) %>%
                    pull(output) %>% sum) < 2)

# AnilGoyal's modified data set
  year c1 c2
1 2000  a  b
2 2000  a  c
3 2000  a  d
4 2001  b  d
5 2002  a  d
user_143145200

this will only keep the data u want. The datais your data frame.

data[!duplicated(data[,2:3]),]

user_133006370

I think this is pretty simple with base duplicated using the fromLast option to get the last rather than the first entry. (It does assum the ordering by year.

dat[!duplicated(dat[2:3], fromLast=TRUE), ] # negate logical vector in i-position
  year c1 c2
3 2000  a  d
4 2001  a  b
5 2001  b  d
6 2001  a  c

I do get a different result than you said was expected so maybe I misunderstood the specifications?

user_18556770

Assuming, that you indeed wanted to keep your last year, as stated in the question, but contrary to your example table, you could simply use slice:

library(dplyr)
df = data.frame(year=c("2000","2000","2000","2001","2001","2001"),
                c1 = c("a","a","a","a","b","a"),c2=c("b","c","d","b","d","c"))
df  %>% group_by(c1,c2) %>%
  slice_tail() %>%arrange(year,c1,c2)

Use slice_head(), if you wanted the first year. Here is the documentation: slice

user_72224130
Related