The dplyr package is one of the most powerful and popular package in R. This package was written by the most popular R programmer Hadley Wickham who has written many useful R packages such as ggplot2, tidyr etc. This post includes several examples and tips of how to use dplyr package for cleaning and transforming data. It's a complete tutorial on data manipulation and data wrangling with R.
The dplyr is a powerful R-package to manipulate, clean and summarize unstructured data. In short, it makes data exploration and data manipulation easy and fast in R.
The package "dplyr" comprises many functions that perform mostly used data manipulation operations such as applying filter, selecting specific columns, sorting data, adding or deleting columns and aggregating data. Another most important advantage of this package is that it's very easy to learn and use dplyr functions. Also easy to recall these functions. For example, filter() is used to filter rows.
dplyr functions process faster than base R functions. It is because dplyr functions were written in a computationally efficient manner. They are also more stable in the syntax and better supports data frames than vectors.
People have been utilizing SQL for analyzing data for decades. Every modern data analysis software such as Python, R, SAS etc supports SQL commands. But SQL was never designed to perform data analysis. It was rather designed for querying and managing data. There are many data analysis operations where SQL fails or makes simple things difficult. For example, calculating median for multiple variables, converting wide format data to long format etc. Whereas, dplyr package was designed to do data analysis.
The names of dplyr functions are similar to SQL commands such as select() for selecting variables, group_by() - group data by grouping variable, join() - joining two data sets. Also includes inner_join() and left_join(). It also supports sub queries for which SQL was popular for.
To install the dplyr package, type the following command.
In this tutorial, we are using the following data which contains income generated by states from year 2002 to 2015. Note : This data do not contain actual income figures of the states.
This dataset contains 51 observations (rows) and 16 variables (columns). The snapshot of first 6 rows of the dataset is shown below.
Submit the following code. Change the file path in the code below.
The sample_n function selects random rows from a data frame (or table). The second parameter of the function tells R the number of rows to select.
The sample_frac function returns randomly N% of rows. In the example below, it returns randomly 10% of rows.
The distinct function is used to eliminate duplicates.
The .keep_all function is used to retain all other variables in the output data frame.
In the example below, we are using two variables - Index, Y2010 to determine uniqueness.
It is used to select only desired variables.
Suppose you are asked to select only a few variables. The code below selects variables "Index", columns from "State" to "Y2008".
The minus sign before a variable tells R to drop the variable.
The starts_with() function is used to select variables starts with an alphabet.
The following functions helps you to select variables based on their names.
num_range() Numerical range like x01, x02, x03.
one_of() Variables in character vector.
The code below keeps variable 'State' in the front and the remaining variables follow that.
 "State" "Index" "Y2002" "Y2003" "Y2004" "Y2005" "Y2006" "Y2007" "Y2008" "Y2009"
 "Y2010" "Y2011" "Y2012" "Y2013" "Y2014" "Y2015"
It is used to change variable name.
The rename function can be used to rename variables.
In the following code, we are renaming 'Index' variable to 'Index1'.
It is used to subset data with matching logical conditions.
Suppose you need to subset data. You want to filter rows and retain only those values in which Index is equal to A.
The %in% operator can be used to select multiple items. In the following program, we are telling R to select rows against 'A' and 'C' in column 'Index'.
Suppose you need to apply 'AND' condition. In this case, we are picking data for 'A' and 'C' in the column 'Index' and income greater than 1.3 million in Year 2002.
The 'I' denotes OR in the logical condition. It means any of the two conditions.
The "!" sign is used to reverse the logical condition.
The grepl function is used to search for pattern matching. In the following code, we are looking for records wherein column state contains 'Ar' in their name.
It is used to summarize data.
In the example below, we are calculating mean and median for the variable Y2015.
In the following example, we are calculating number of records, mean and median for variables Y2005 and Y2006. The summarise_at function allows us to select multiple variables by their names.
We can also use custom functions in the summarise function. In this case, we are computing the number of records, number of missing values, mean and median for variables Y2011 and Y2012. The dot (.) denotes each variables specified in the second argument of the function.
Suppose you want to subtract mean from its original value and then calculate variance of it.
We are checking the number of levels/categories and count of missing observations in a categorical (factor) variable.
To sort a variable in descending order, use desc(x).
The default sorting order of arrange() function is ascending. In this example, we are sorting data by multiple variables.
Suppose you need to sort one variable by descending order and other variable by ascending oder.
It is important to understand the pipe (%>%) operator before knowing the other functions of dplyr package. dplyr utilizes pipe operator from another package (magrittr).
It allows you to write sub-queries like we do it in sql.
Note : All the functions in dplyr package can be used without the pipe operator. The question arises "Why to use pipe operator %>%". The answer is it lets to wrap multiple functions together with the use of %>%.
The %>% is NOT restricted to filter function. It can be used with any function.
The code below demonstrates the usage of pipe %>% operator. In this example, we are selecting 10 random observations of two variables "Index" "State" from the data frame "mydata".
We are calculating count and mean of variables Y2011 and Y2012 by variable Index.
Note : The dot (.) is required to refer to a data frame.
Suppose you need to pull top 2 rows from 'A', 'C' and 'I' categories of variable Index.
We are calculating third maximum value of variable Y2015 by variable Index. The following code first selects only two variables Index and Y2015. Then it filters the variable Index with 'A', 'C' and 'I' and then it groups the same variable and sorts the variable Y2015 in descending order. At last, it selects the third row.
The slice() function is used to select rows by position.
In this case, we are computing mean of variables Y2014 and Y2015 by variable Index. Then sort the result by calculated mean variable Y2015.
The following code calculates division of Y2015 by Y2014 and name it "change".
It creates new variables and name them with suffix "_new".
The output shown in the image above is truncated due to high number of variables.
It implies you are multiplying 1000 to string(character) values which are stored as factor variables. These variables are 'Index', 'State'. It does not make sense to apply multiplication operation on character variables. For these two variables, it creates newly created variables which contain only NA.
Suppose you need to calculate rank for variables Y2008 to Y2010.
The cumsum function calculates cumulative sum of a variable. With mutate function, we insert a new variable called 'Total' which contains values of cumulative income of variable Index.
by - common variable (primary key) to join by.
INNER JOIN returns rows when there is a match in both tables. In this example, we are merging df1 and df2 with ID as common variable (primary key).
LEFT JOIN : It returns all rows from the left table, even if there are no matches in the right table.
Rows that appear in both x and y.
Rows that appear in either or both x and y.
Rows that appear in x but not y.
INTERSECT selects unique rows that are common to both the data frames.
UNION displays all rows from both the tables and removes duplicate records from the combined dataset. By using union_all function, it allows duplicate rows in the combined dataset.
If a value is less than 5, add it to 1 and if it is greater than or equal to 5, add it to 2. Otherwise 0.
We can use case_when() function to write nested if-else queries. In case_when(), you can use variables directly within case_when() wrapper. TRUE refers to ELSE statement.
Make sure you set is.na() condition at the beginning in nested ifelse. Otherwise, it would not be executed.
Suppose you want to find maximum value in each row of variables 2012, 2013, 2014, 2015. The rowwise() function allows you to apply functions to rows.
Suppose you are asked to combine two data frames. Let's first create two sample datasets.
The bind_rows() function combine two datasets with rows. So combined dataset would contain 12 rows (6+6) and 2 columns.
It is equivalent to base R function rbind.
The bind_cols() function combine two datasets with columns. So combined dataset would contain 4 columns and 6 rows.
The quantile() function is used to determine Nth percentile value. In this example, we are computing percentile values by variable Index.
The ntile() function is used to divide the data into N bins.
This example explains the advanced usage of do() function. In this example, we are building linear regression model for each level of a categorical variable. There are 3 levels in variable cyl of dataset mtcars.
It includes functions like select_if, mutate_if, summarise_if. They come into action only when logical condition meets. See examples below.
The select_if() function returns only those columns where logical condition is TRUE. The is.numeric refers to retain only numeric variables.
Like select_if() function, summarise_if() function lets you to summarise only for variables where logical condition holds.
It returns 19 levels for variable Index and 51 levels for variable State.
In this example, we are converting "" to NA using na_if() function.
Result : "a" "b" NA "d"
There are hundreds of packages that are dependent on this package. The main benefit it offers is to take off fear of R programming and make coding effortless and lower processing time. However, some R programmers prefer data.table package for its speed. I would recommend learn both the packages. The data.table package wins over dplyr in terms of speed if data size greater than 1 GB.
40 Responses to "Data Manipulation with dplyr (With 50 Examples)"
Thanks for share, great stuff and examples.
Having searched many sites and lectures I am bookmarking your site after looking at this page. Its the simplicity of your presentation. Thanks.
Thank you for stopping by my blog. Glad you found it useful. Cheers!
Thank you, this indeed very helpful and precise. Great Job!
What did it mean? Could you please give me some explanation. Thanks.
First two variables in the dataframe mydata are strings that are stored as factor variables.
I got it. Thanks. I think I'm ready to go next to your another tutorial - data.table. It's quite interesting to learn R from your blog posts.
Thank you for posting alternative method. I have added it to the tutorial. Cheers!
what if I want to mutate to add only a column for Y2015?
Great tutorials. Took too much time to found this tutorial.
Wonderfull for a newcomer to R !
Thank you for this great presentation.
Try to use below code instead.
Updated. Thanks for pointing it out!
Thanks for highlighting. It's fixed now!
Every example is precise, simple and very well explained. Many thanks and congrats!
love the detailed yet simple explanations.
é de longe um dos melhores e mais completos tutorias sobre Dplyr. Obrigado!
I am Very thankful to you bro. Bec of u, i have learned dplyr and am using regularly.