Manipulating Dataframes in R

It is said that eighty percent of the time in a data science project is spent on data preparation and data cleaning. In this guide, you will learn about the tricks and techniques of manipulating dataframes in R using the popular package dplyr.

The 'dplyr' library offers several powerful functions to manipulate the dataframe, which is a two-dimensional data structure containing rows and columns.

In particular, you will learn the following data manipulation techniques:

1. Filter
2. Select
3. Mutate
4. Arrange
5. Summarize
6. Group_by
7. Count 8: Rename

Let’s begin by loading the data.

In this guide, we will be using fictitious data of loan applicants containing 600 observations and 10 variables, as described below:

1. Marital_status: Whether the applicant is married ("Yes") or not ("No")

2. Is_graduate: Whether the applicant is a graduate ("Yes") or not ("No")

3. Income: Annual Income of the applicant (in USD)

4. Loan_amount: Loan amount (in USD) for which the application was submitted

5. Credit_score: Whether the applicants credit score is good ("Good") or not ("Bad")

6. Approval_status: Whether the loan application was approved ("Yes") or not ("No")

7. Age: The applicant's age in years

8. Sex: Whether the applicant is a male ("M") or a female ("F")

9. Investment: Total investment in stocks and mutual funds (in USD) as declared by the applicant

10. Purpose: Purpose of applying for the loan

Let's load the required libraries and the data.

Output:

The output shows that the data has six categorical variables (labeled as 'chr'), and four numerical variables (labeled as 'int'). We will convert the categorical variables into the 'factor' type, using the lines of code below.

Output:

The data is now ready for carrying out the various data manipulation steps.

The filter command selects rows based on the specified condition. We will start by filtering the data for applicants whose loan is approved. The first line of code below prints the table of the variable 'approval_status'. The output shows that there are 410 applicants whose loan was approved.

The second line creates a new dataframe, 'approved_loan', while the third line prints the dimension, which is 410 rows and 10 variables.

Output:

One of the things to notice in the code above is the use of the pipe operator, written as ***%>%***. This pipe operator enables us to chain multiple 'dplyr' commands together, such that it takes output from one command and feeds it as input to the next command.

The 'filter' command can also be used to include multiple conditions. The code below filters the data using different conditions on two variables, ‘approval_status’ and ‘credit_score’.

Output:

The above output shows that the resultant data now has 372 records instead of the original 600 because of the conditions we used.

The filter command can also be used with numerical variables, as shown in the lines of code below. The output confirms that the operation is completed.

Output:

The select() command selects columns in a data. The lines of code below provides the entire data as input to the ‘select’ function using the '%>%' operator. Inside the select function, the columns to be selected are specified.

The output shows that the resultant data has 600 observations and 3 variables.

Output:

The mutate() function helps in feature engineering by allowing us to create new variables. For example, we can use two variables, 'Investment' and 'Income', to create a new variable, 'Inv_inc_ratio'. The mutate verb adds the new column to the dataframe, as shown in the syntax below.

Output:

The arrange() function arranges the rows based upon the specified condition. For example, if we want to arrange the 'Inv_inc_ratio' variable in an order, we can do that using the lines of code below.

Output:

By default, the 'arrange()' function orders the variable in ascending order. In order to sort and arrange in descending order, we add the function desc() to the code.

Output:

Multiple columns can also be included in the 'arrange()' function. In such a case, each column is used to break ties in the values of the preceding columns.

Output:

The summarize() function summarizes the variables in the dataframe. For example, if we want to get the mean value of variables like income, loan amount, or age, the line of code below will produce and display the desired output.

Output:

The group_by() function groups the data based on one or more columns and then manipulates the grouped dataframe. The 'group_by()' function is often used along with the other five 'dplyr' commands discussed in the previous sections.

We will take one case of using the 'group_by()' and 'summarize()' commands together. The line of code below groups the data by the variable 'Purpose', and then calculates the summary of average income for each 'Purpose' group.

Output:

The count() function counts the number of observations in a variable or the dataset. The first line of code below prints the count of the entire dataset, 'dat'. The second line extends this functionality further and provides the count of respective categories within the 'Purpose' variable.

Output:

We can also sort the above output in descending order, as shown in the line of code below.

Output:

The rename() function is used to rename one or more columns. If we want to change the name of the variable 'Inv_inc_ratio' and rename it 'investment_ratio', we can do that using the line of code below. The general syntax is ***rename(new name = old name)***.

Output:

In this guide, you have learned about data manipulation techniques using the popular 'dplyr' library. These techniques will help you handle complex, real-world data sets.

To learn more about data science using 'R', please refer to the following guides:

1. Interpreting Data Using Descriptive Statistics with R
2. Interpreting Data Using Statistical Models with R
3. Time Series Forecasting Using R
4. Hypothesis Testing - Interpreting Data with Statistical Models
5. Machine Learning with Text Data Using R
6. Visualization of Text Data Using Word Cloud in R