Very often in your datasets you have a situation where
you have duplicated values of data, when one row has same values as some other
row. That doesn’t have to be wrong or bad, but sometimes you get into
situations where you only want to consider specific values and exclude values
that you already have taken into account.
Now, if you have small datasets that you can quickly analyze by naked eye, you can simply remove the rows you want:
planets <-c(“Mercury”,”Venus”,”Mars”,”Earth”,”Jupyter”,
“Saturn”,”Uranus”,”Neptune”,”Pluto”)
position <- c(“First”,”Second”,”Third”,”Fourth”,”Fifth”,
“Seventh”,”Eighth”,”Nineth”)
df <- data.frame(planets, position)
df
planets position
1 Mercury First
2 Venus Second
3 Mars Third
4 Earth Fourth
5 Jupyter Fifth
6 Saturn Sixth
7 Uranus Seventh
8 Neptune Eighth
9 Pluto Nineth
Now I can remove the 9th row by:
df <- df[-c(9), ]
I specified row (9) to remove and left all the columns as they are.
Now, what happens when you have thousands of rows? Here is where dplyr comes in help. Dplyr is a package which provides a set of tools for efficiently manipulating datasets in R.
In the context of removing duplicate rows, there are three functions from this package that are of our interest: duplicated(), unique() and distinct()
duplicated() identifies rows which values appear more than once.
unique() identifies rows which are original (don’t appear more than once).
distinct() is a function which removes duplicate data from a dataset
Let’s see on example how to use these methods.
Dplyr comes from tidyverse library, so we first must include it by executing:
library(tidyverse)
We’ll use the R built-in iris data set, which we start by converting into a tibble data frame for easier data analysis:
my_data <- as_tibble(iris)
my_data
## # A tibble: 150 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 144 more rows
The R function duplicated() returns a logical vector where TRUE specifies which elements of a vector or data frame are duplicates:
x <- c(1, 1, 4, 5, 4, 6)
duplicated(x)
[1] FALSE TRUE FALSE FALSE TRUE FALSE
To extract duplicate elements:
x[duplicated(x)]
## [1] 1 4
If you want to remove duplicated elements, use !duplicated(), where ! is a logical negation:
x[!duplicated(x)]
## [1] 1 4 5 6
You will rarely get identical rows, but very often you will get identical values in specific columns. For example, in our iris data (my_data), Sepal.width is very often repeated by values. Let’s see how to remove rows based on duplicate values from that column:
my_data[!duplicated(my_data$Sepal.Width), ]
## # A tibble: 23 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 17 more rows
Another way of removing duplicates is by using unique() function. It works in opposite way of duplicated() function. For example:
unique(c(1,1,4,5,4,6))
## [1] 1 4 5 6
It’s also possible to apply unique() on a data frame, for removing duplicated rows as follow:
unique(my_data)
## # A tibble: 149 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 143 more rows
You notice that in those filtering methods where we don’t specify the column, we get rid of only one row because there is not many rows that are identical by all columns. But when we specify the column, we often get rid of quite a lot of rows.
The function distinct() [dplyr package] can be used to keep only unique/distinct rows from a data frame. If there are duplicate rows, only the first row is preserved. It’s an efficient version of the R base function unique().
Remove duplicate rows based on all columns:
my_data %>% distinct()
## # A tibble: 149 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 143 more rows
Remove duplicate rows based on certain columns (Sepal.Length):
my_data %>% distinct(Sepal.Length, .keep_all = TRUE)
## # A tibble: 149 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 29 more rows
The .keep_all attribute is used to retain all other variables in the output data frame.
Remove duplicated rows based on Sepal.Length and Petal.Width:
my_data %>% distinct(Sepal.Length, Petal.Width, .keep_all = TRUE)
## # A tibble: 149 x 5
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <fct>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## # … with 104 more rows
Here we use pipe operator %>%
The point of pipe is to help you write a code that is easier to read and understand.
When we say
my_data %>% distinct()
It is equivalent to
my_data <- distinct(my_data,…)
To learn more about pipe operator and its benefits, check out free e book ‘R for data science’ by Garett Grolemund