data.table is a highly optimizedlibrary which improves upon R’s standard data.frame via the data.table object. data.tables can be used in any scenario that accepts a data.frame, and thus can be used as a replacement for the standard data.frame is all scenarios.
Due to the highly optimized nature of data.table, aggregate operations are much faster compared with standard R builtins like aggregate. However, the syntax takes a little getting used to. In this article, we’ll demonstrate how to perform various common aggregate operations using the data.table library.
Data
Throughout this article, we’ll work with a simulated loss frequency dataset:
library("data.table")
set.seed(516)
DT0 = CJ(
district=c(1,2 ,3 ,4), group=c("A", "B", "C", "D"),
age=c("<25", "25-34", "35-49", ">50")
)
DT0[,`:=`(
claims=rpois(nrow(DT), 100),
holders=sample(1000:5000, size=nrow(DT), replace=FALSE)
)]
# Table of adjustment factors.
DT1 = data.table(
age=c("<25", "25-34", "35-49", ">50"),
adj=c(1.50, 1.075, .85, .975),
stringsAsFactors=FALSE
)
DT = DT1[DT0, on="age"]
DT[,claims:=as.integer(claims * adj)]
DT[,adj:=NULL]Taking a look at the first few records yields:
age district group claims holders
1: 25-34 1 A 95 4359
2: 35-49 1 A 92 3764
3: <25 1 A 157 4613
4: >50 1 A 111 2529
5: 25-34 1 B 90 2105
6: 35-49 1 B 97 3379Aggregate Operations
Before proceeding, we should highlight data.table’s general calling convention, that when understood, can be used to translate data.table expressions of arbitrary complexity into 3 steps:
DT[i, j, by]
Which translates to:
Take
DT, subset rows usingi, then calculatejgrouped byby.
This can be represented in a diagram as follows:
DT[ i, j, by ] # + extra arguments
| | |
| | -------> grouped by what?
| -------> what to do?
---> on which rows?This may not be immediately clear, but should start to make sense as we look at examples.
Returning a Single Value from a Single Column
Referring to DT from above, the total number of policyholders can be determined as:
> DT[,sum(holders)]
[1] 190885To compute the total number of policyholders and claims, run:
> DT[,.(holders=sum(holders), claims=sum(claims))]
holders claims
1: 190885 7018This returns a 1-row data.table with the total number of holders and claims. To change fieldnames in the resulting table, change the name on the left-hand side for the desired field within the sum expression:
> DT[,.(total_holders=sum(holders), total_claims=sum(claims))]
total_holders total_claims
1: 190885 7018 Assigning an Aggregate to Each Row of an Existing Table
It may be necessary to assign an aggregate value from a table to each record in the same table. This can be accomplished using the := operator. Note that when computing aggregate operations that utilize :=, the number of rows in the resulting table will not change. Next we assign the total number of claims to each row in DT in a column identified as total_claims:
> DT[,total_claims:=sum(claims)]
> head(DT)
age district group claims holders total_claims
1: 25-34 1 A 95 4359 7018
2: 35-49 1 A 92 3764 7018
3: <25 1 A 157 4613 7018
4: >50 1 A 111 2529 7018
5: 25-34 1 B 90 2105 7018
6: 35-49 1 B 97 3379 7018If we want to aggregate two or more columns, it is necessary to use a slight variation of the := operator. Next we include total_claims and total_holders:
> DT[, ":=" (total_claims=sum(claims), total_holders=sum(holders))]
> head(DT)
age district group claims holders total_claims total_holders
1: 25-34 1 A 95 4359 7018 190885
2: 35-49 1 A 92 3764 7018 190885
3: <25 1 A 157 4613 7018 190885
4: >50 1 A 111 2529 7018 190885
5: 25-34 1 B 90 2105 7018 190885
6: 35-49 1 B 97 3379 7018 190885
> This differs from the standard syntax in two ways: First, the := operator is specified upfront, and is surrounded by quotes (":="). Second, when specifying the columns to aggregate, we use =, not :=, since this is already specified upfront. It is also possible to use the modified syntax for adding a single column. The following expressions are identical:
DT[,total_claims:=sum(claims)]
DT[,":=" (total_claims=sum(claims))]Aggregate Operations by Group
To perform aggregate operations by group, the syntax introduced in the previous examples remains unchanged: The only difference is the addition of the by keyword.
In DT, there are four distinct levels in each of age, district and group:
> table(DT$age)
<25 >50 25-34 35-49
16 16 16 16 > table(DT$district)
1 2 3 4
16 16 16 16 > table(DT$district)
A B C D
16 16 16 16 To calculate the total number of claims by each level in age, run:
> DT[,sum(claims), by=age]
age V1
1: 25-34 1643
2: 35-49 1363
3: <25 2410
4: >50 1602This returns total number of claims in a single unnamed column (identified as V1 by default). In order to preserve column names, we use the syntax from before, updated with by. In what follows, we compute the total number of claims and policyholders by age while preserving column names:
> DT[,.(claims=sum(claims), holders=sum(holders)), by="age"]
age claims holders
1: 25-34 1643 42109
2: 35-49 1363 52062
3: <25 2410 48932
4: >50 1602 47782When it is necessary to aggregate by two or more columns, we supply a vector of column names over which to aggregate. To determine the total number of claims and policyholders by age and district, do:
> DT[,.(claims=sum(claims), holders=sum(holders)), by=c("age", "district")]
age district claims holders
1: 25-34 1 384 10080
2: 35-49 1 345 13576
3: <25 1 649 13665
4: >50 1 423 10747
5: 25-34 2 422 12495
6: 35-49 2 343 13462
7: <25 2 587 11348
8: >50 2 424 11888
9: 25-34 3 420 8101
10: 35-49 3 330 14933
11: <25 3 582 13167
12: >50 3 385 13730
13: 25-34 4 417 11433
14: 35-49 4 345 10091
15: <25 4 592 10752
16: >50 4 370 11417Assigning Aggregate Values by Group to Each Row in Existing Table
As before, aggrergate operations by group can be assigned to an existing table. Next we assign the average number of claims and policyholders by age to each record in the original dataset (note that I’ve removed total_claims and total_holders to simplify viewing the tables. It is fine to keep them in if you’re following along):
> DT[,":="(avg_nbr_claims=mean(claims), avg_nbr_holders=mean(holders)), by="age"]
> head(DT)
age district group claims holders avg_nbr_claims avg_nbr_holders
1: 25-34 1 A 95 4359 102.6875 2631.812
2: 35-49 1 A 92 3764 85.1875 3253.875
3: <25 1 A 157 4613 150.6250 3058.250
4: >50 1 A 111 2529 100.1250 2986.375
5: 25-34 1 B 90 2105 102.6875 2631.812
6: 35-49 1 B 97 3379 85.1875 3253.875The average number of claims and policyholders by district and group can be computed as:
> head(DT, 25)
age district group claims holders avg_nbr_claims avg_nbr_holders
1: 25-34 1 A 95 4359 113.75 3816.25
2: 35-49 1 A 92 3764 113.75 3816.25
3: <25 1 A 157 4613 113.75 3816.25
4: >50 1 A 111 2529 113.75 3816.25
5: 25-34 1 B 90 2105 112.75 2277.50
6: 35-49 1 B 97 3379 112.75 2277.50
7: <25 1 B 160 1359 112.75 2277.50
8: >50 1 B 104 2267 112.75 2277.50
9: 25-34 1 C 92 1942 111.75 2598.25
10: 35-49 1 C 68 2765 111.75 2598.25
11: <25 1 C 178 4626 111.75 2598.25
12: >50 1 C 109 1060 111.75 2598.25
13: 25-34 1 D 107 1674 112.00 3325.00
14: 35-49 1 D 88 3668 112.00 3325.00
15: <25 1 D 154 3067 112.00 3325.00
16: >50 1 D 99 4891 112.00 3325.00
17: 25-34 2 A 96 4765 111.75 2748.75
18: 35-49 2 A 90 1526 111.75 2748.75
19: <25 2 A 144 2877 111.75 2748.75
20: >50 2 A 117 1827 111.75 2748.75
21: 25-34 2 B 118 2059 117.75 2778.00
22: 35-49 2 B 89 3323 117.75 2778.00
23: <25 2 B 148 2687 117.75 2778.00
24: >50 2 B 116 3043 117.75 2778.00
25: 25-34 2 C 105 3863 110.50 3931.00
age district group claims holders avg_nbr_claims avg_nbr_holdersAlternative Aggregation Specification
I will briefly discuss an alternative aggregation approach that greatly simplifies performing aggregate operations on large tables.
It’s hard to visualize with our example, but imagine having a table with 10’s or 100’s of columns. Performing aggregate operations as demonstrated would quickly become untenable. To address this, we can take advantage of R’s special symbols. We use .SD as a placeholder for the columns to aggregate, then pass a vector of these column names to .SDcols. .SD by default gets assigned all columns except the columns mentioned in by=. For example, aggregating claims and holders by age and district can be accomplished as follows:
> aggColumns = c("claims", "holders")
> keyColumns = c("age", "district")
> DT[,lapply(.SD, sum, na.rm=TRUE), by=keyColumns, .SDcols=aggColumns]
age district claims holders
1: 25-34 1 384 10080
2: 35-49 1 345 13576
3: <25 1 649 13665
4: >50 1 423 10747
5: 25-34 2 422 12495
6: 35-49 2 343 13462
7: <25 2 587 11348
8: >50 2 424 11888
9: 25-34 3 420 8101
10: 35-49 3 330 14933
11: <25 3 582 13167
12: >50 3 385 13730
13: 25-34 4 417 11433
14: 35-49 4 345 10091
15: <25 4 592 10752
16: >50 4 370 11417Using this approach, we can write programs that assign which columns to aggregate dynamically at the point of execution.
Summary
A few things to keep in mind when performing aggregate operations in data.table:
.SDby default gets assigned all columns except the columns mentioned inby=..SDcolsrepresents the fields that should be aggregated (columns must be numeric).by=is similiar to SQLGROUP BY. Specifies the keys over which data should be aggregated.If you keep the
:=operator, the results will not be aggregated. Instead the aggregate amount of the target column will be appended to each record in the original data.table.