Remove duplicate elements in a row from a data frame in R

user_3751263 0
Viewed 64

Removing duplicate rows in a dataframe is relatively easy. However, removing duplicate elements of a row within a data frame is a more challenging issue.

Let's start with this df:

df <- structure(list(V1 = c("B1182", "B1182", "B1182", "B1182", "B1182", 
"B1182", "B1182", "B1182", NA, NA, "B1182", "B1182", "B1182", 
NA, NA, NA, NA, "P2000", "P2000", NA), V2 = c("B124D", "B124D", 
"B124D", "B124D", "B124D", "B124D", "B124D", "B124D", NA, NA, 
"B124D", "B124D", "B124D", NA, NA, NA, NA, "P2000", "P2000", 
NA), V3 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, "U3003", "U3003", NA), V4 = c(NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "P2000", 
"P2000", NA), V5 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), V6 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), V7 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_), 
    V8 = c(NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    )), .Names = c("V1", "V2", "V3", "V4", "V5", "V6", "V7", 
"V8"), row.names = c(NA, 20L), class = "data.frame")

This is the output of df:

      V1    V2    V3    V4   V5   V6   V7   V8
1  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
2  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
3  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
4  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
5  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
6  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
7  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
8  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
9   <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
10  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
11 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
12 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
13 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
14  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
15  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
16  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
17  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
18 P2000 P2000 U3003 P2000 <NA> <NA> <NA> <NA>
19 P2000 P2000 U3003 P2000 <NA> <NA> <NA> <NA>
20  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>

As you can see, rows 18 and 19 contain duplicate codes (P2000). I would like to remove these duplicate elements and keep only the first that appears in the row. Notice that this is an extract of my original df so that it must be applicable to all the situations.

The expected output might be like this:

      V1    V2    V3    V4   V5   V6   V7   V8
1  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
2  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
3  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
4  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
5  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
6  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
7  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
8  B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
9   <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
10  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
11 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
12 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
13 B1182 B124D  <NA>  <NA> <NA> <NA> <NA> <NA>
14  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
15  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
16  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
17  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>
18 P2000  <NA> U3003  <NA> <NA> <NA> <NA> <NA>
19 P2000  <NA> U3003  <NA> <NA> <NA> <NA> <NA>
20  <NA>  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA>

I don't care about the variables themselves, as they will be rearranged and transformed later.

So, how can I remove duplicate elements within a row in this df? Thanks in advance.

data-manipulation dataframe duplicates r
3 Answers

You can use tapply on the rows and replace duplicates with NA:

df[t(apply(df,  1, duplicated))] <- NA

> df
      V1    V2    V3   V4   V5   V6   V7   V8
1  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
2  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
3  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
4  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
5  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
6  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
7  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
8  B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
9   <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
10  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
11 B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
12 B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
13 B1182 B124D  <NA> <NA> <NA> <NA> <NA> <NA>
14  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
15  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
16  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
17  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
18 P2000  <NA> U3003 <NA> <NA> <NA> <NA> <NA>
19 P2000  <NA> U3003 <NA> <NA> <NA> <NA> <NA>
20  <NA>  <NA>  <NA> <NA> <NA> <NA> <NA> <NA>
user_75145270

It looked like your other question included tidyverse, so here is an alternative that uses both pivot_longer and pivot_wider:

library(tidyverse)

df %>%
  mutate(rn = row_number()) %>%
  pivot_longer(cols = -rn, names_to = "var", values_to = "value") %>%
  group_by(rn) %>%
  mutate(value = ifelse(duplicated(value), NA, value)) %>%
  pivot_wider(id_cols = rn, names_from = "var", values_from = "value")

Output

# A tibble: 20 x 9
# Groups:   rn [20]
      rn V1    V2    V3    V4    V5    V6    V7    V8   
   <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
 1     1 B1182 B124D NA    NA    NA    NA    NA    NA   
 2     2 B1182 B124D NA    NA    NA    NA    NA    NA   
 3     3 B1182 B124D NA    NA    NA    NA    NA    NA   
 4     4 B1182 B124D NA    NA    NA    NA    NA    NA   
 5     5 B1182 B124D NA    NA    NA    NA    NA    NA   
 6     6 B1182 B124D NA    NA    NA    NA    NA    NA   
 7     7 B1182 B124D NA    NA    NA    NA    NA    NA   
 8     8 B1182 B124D NA    NA    NA    NA    NA    NA   
 9     9 NA    NA    NA    NA    NA    NA    NA    NA   
10    10 NA    NA    NA    NA    NA    NA    NA    NA   
11    11 B1182 B124D NA    NA    NA    NA    NA    NA   
12    12 B1182 B124D NA    NA    NA    NA    NA    NA   
13    13 B1182 B124D NA    NA    NA    NA    NA    NA   
14    14 NA    NA    NA    NA    NA    NA    NA    NA   
15    15 NA    NA    NA    NA    NA    NA    NA    NA   
16    16 NA    NA    NA    NA    NA    NA    NA    NA   
17    17 NA    NA    NA    NA    NA    NA    NA    NA   
18    18 P2000 NA    U3003 NA    NA    NA    NA    NA   
19    19 P2000 NA    U3003 NA    NA    NA    NA    NA   
20    20 NA    NA    NA    NA    NA    NA    NA    NA
user_34606700

An option with tidyverse using pmap

library(purrr)
library(dplyr)
df %>% 
    pmap_dfr(., ~ {x1 <- c(...); replace(x1, duplicated(x1), NA)})
# A tibble: 20 x 8
#   V1    V2    V3    V4    V5    V6    V7    V8   
# * <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
# 1 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 2 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 3 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 4 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 5 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 6 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 7 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 8 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
# 9 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#10 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#11 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#12 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#13 B1182 B124D <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#14 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#15 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#16 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#17 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#18 P2000 <NA>  U3003 <NA>  <NA>  <NA>  <NA>  <NA> 
#19 P2000 <NA>  U3003 <NA>  <NA>  <NA>  <NA>  <NA> 
#20 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
user_37322710
Related