Creating a data.table with R

The data.table package is an enhanced version of the data.frame, which is the defacto structure for working with R. Dataframes are extremely useful, providing the user an intuitive way to organize, view, and access data. Their limitation is that it becomes trickier to perform fast data manipulation for large datasets. Also, codes can become complex and inconsistent with dataframes. This is where data.table is a better alternative because it has consistent syntax, efficient memory, and parallelization.

In this guide, you will learn about the basics of data.table and how to apply it for data manipulation and aggregation tasks. Let’s start by understanding the data.

In this guide, we will use a fictitious dataset of loan applications containing 600 observations and 10 variables:

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

2. Dependents: Number of dependents of the applicant

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

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

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

6. Credit_score: Whether the applicant’s credit score is good ("Satisfactory") or not ("Not Satisfactory")

7. Approval_status: Whether the loan application was approved ("1") or not ("0")

8. Age: The applicant's age in years

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

10. Purpose: Purpose of applying for the loan

Let's start by loading the required libraries and the data.

The common way of loading data in R is through the read_csv() function of the readr library. This is effective for smaller sized datasets, but not efficient when dealing with big data. The data.table package provides a faster alternative for reading data with the fread() function, which is a fast and parallel file reader that can read local files, files from the web, and even string files.

The lines of code below load the required libraries, read the data using the fread function, and print the view of the data.

Output:

The output shows that the dataset has five numerical (labeled as 'int) and five qualitative (labeled as chr) variables. To work with the data.table library, it's necessary to convert the data.frame into a data.table, using the line of code below. The structure of the resulting data shows that it is both a data.table and a data.frame.

Output:

We are now ready to carry out the data processing and aggregation tasks common in data science.

A common data manipulation task is data slicing based on specific rows and columns.

To extract a single row of the data, we can use the syntax dataset[rownumber, ]. For example, the line of code below prints the third row.

Output:

The output shows the resulting data has one row and ten observations. It is also possible to extract a range of rows. The code below extracts the third through fifth rows and all the columns, then stores them in the new object d'. The second line prints the structure of the new data: 3 observations of 10 variables.

Output:

It is also possible to perform advanced filtering of rows. If we want to examine records where the applicant's credit record was not satisfactory but the loan was still approved, we can do that using the first line of code below.

The second line prints the dimension of the resulting data: 38 rows and 10 variables. The third line prints the table of the two variables. The output shows that there were 38 applicants whose credit score was not satisfactory, but their loan application was approved.

Output:

The advantage of using data.table is that it provides a lot of helper functions for efficient data manipulation.

1. %like%: This function allows us to search for a pattern in a character or a factor variable. This is illustrated in the first to third lines of code below, which subset all the rows where the variable Purpose starts with the string Personal.

2. %between%: This function allows us to search for values within the closed interval. This is illustrated in the fourth to sixth lines of code below, which subset all the rows where the applicant's income is between $384,975 (first quartile) and $766,100 (third quartile).

3. %chin%: This function is only for character vectors. This is illustrated in the seventh to tenth lines of code below, which subset all the rows where the purpose for loan application is "Furniture," "Business," or "Wedding."

Output:

It’s easy to select columns in data.table using the respective names. For example, the line of code below will extract the entire vector of values for the Purpose variable. The summary function confirms that the resulting vector, d1, has a length of 600 values.

Output:

It is also possible to select multiple columns using the list() function. The first line of code below selects all the rows and two specified columns, while the second line prints the structure of the resulting data.

Output:

We have learned how to select columns, but sometimes we might want to deselect certain columns. This can be done using the convenience function that takes the form -c("col1", "col2", ...). As an example, the lines of code below create a subset of data that excludes the variables Sex and Dependents. The output of the code confirms this exclusion.

Output:

It’s easy to filter the data based on row and column conditions. The line of code below creates a data table where age is more than 60 years.

Output:

We can also subset data on various combinations of conditions. The first subset, s1, contains data for the maximum age of 76 years. The output shows there are only two such records.

The second subset, s2, contains data with records where the applicant is greater than fifty years and the purpose for loan application is “Education”. The resulting data has 97 observations of 10 variables.

The third subset, s3, contains data with records where the purpose of the loan application was either “Education” or "Personal." The resulting data has 357 observations of 10 variables.

Output:

We have so far learned how to perform data processing tasks such as filtering and subsetting. This section covers more sophisticated data aggregation techniques, such as performing summary operations within groups. This is a crucial task in descriptive and diagnostic analytics.

We start with the simple task of finding the mean of a single column, Age. The output confirms that the average age of the applicants in the data is 49 years.

Output:

The above operation on a single column can be extended to combine rows and columns. Using the data.table syntax format of [i, j, by], we can calculate the average income by approval status of the application. In this case, i represents all rows, j represents the computation on the column (mean of income), and by represents the grouping operation (approval status in this case).

Output:

The output above created a variable V1 that contains the average income value for approved and rejected loan applications. It is possible to give this variable a unique name by using the list function, as in the line of code below.

Output:

The output confirms that the column name has been changed to Avg_income, which is more intuitive. The list() function can be used to create and name multiple variables as shown in the code below.

Output:

Finally, we can perform the aggregation on more than one column. In the example below, we want to compute the average income and loan amount grouped by two columns, Purpose and approval_status. The grouping at the column level is done by changing the by argument, of the [i,j,by] syntax, as is done in the code below.

Output:

In this guide, you have learned how to use the powerful data.table package for data manipulation and aggregation. Understanding of these techniques will enable you to perform faster descriptive and diagnostic analytics on the data. 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

7. Exploring Data Visually with R

8. Coping with Missing, Invalid and Duplicate Data in R