Data Wrangling With data.table

Intro

Note that the examples here use data.table version 1.9.8. Some of the methods are NOT compatible with prior versions of data.table (e.g. non-equi joins and secondary indexing).

Install data.table (pandas)

install.packages("data.table")

Load data.table (pandas)

library(data.table)

Build a data.table from scratch (pandas)

transactions <- data.table(
  TransactionID = seq(1, 10),
  TransactionDate = as.Date(c("2010-08-21", "2011-05-26", "2011-06-16", "2012-08-26", "2013-06-06", 
                              "2013-12-23", "2013-12-30", "2014-04-24", "2015-04-24", "2016-05-08")),
  UserID = c(7L, 3L, 3L, 1L, 2L, 2L, 3L, NA, 7L, 3L),
  ProductID = c(2L, 4L, 3L, 2L, 4L, 5L, 4L, 2L, 4L, 4L),
  Quantity = c(1L, 1L, 1L, 3L, 1L, 6L, 1L, 3L, 3L, 4L)
)

Read data from a CSV file (pandas)

Load transactions (pandas)

transactions <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv")

Meta info (pandas)

Full summary (pandas)

str(transactions)

How many rows? (pandas)

nrow(transactions)

How many columns? (pandas)

ncol(transactions)

Get the row names (pandas)

rownames(transactions)

Get the column names (pandas)

colnames(transactions)

Change the name of column "Quantity" to "Quant" (pandas)

setnames(transactions, "Quantity", "Quant")
setnames(transactions, "Quant", "Quantity")  # change it back

Change the name of columns ProductID and UserID to PID and UID respectively (pandas)

setnames(transactions, c("ProductID", "UserID"), c("PID", "UID"))
setnames(transactions, c("PID", "UID"), c("ProductID", "UserID"))  # change them back

Ordering the rows of a data.table (pandas)

Order the rows of transactions by TransactionID descending (pandas)

transactions[order(-TransactionID)]

Order the rows of transactions by Quantity ascending, TransactionDate descending (pandas)

setorderv(transactions, c("Quantity", "TransactionDate"), order=c(1, -1))
setorder(transactions, TransactionID)  # change it back

Ordering the columns of a data.table (pandas)

Set the column order of transactions as ProductID, Quantity, TransactionDate, TransactionID, UserID (pandas)

setcolorder(transactions, c("ProductID", "Quantity", "TransactionDate", "TransactionID", "UserID"))
setcolorder(transactions, c("TransactionID", "TransactionDate", "UserID", "ProductID", "Quantity"))  # reset the column order

Make UserID the first column of transactions (pandas)

setcolorder(transactions, unique(c("UserID", colnames(transactions))))
setcolorder(transactions, c("TransactionID", "TransactionDate", "UserID", "ProductID", "Quantity"))  # reset the column order

Extracting vectors from a data.table (pandas)

Get the 2nd column (pandas)

transactions[[2]]

Get the ProductID vector (pandas)

transactions$ProductID

Get the ProductID vector using a variable (pandas)

col <- "ProductID"
transactions[[col]]

Row subsetting (pandas)

Subset rows 1, 3, and 6 (pandas)

transactions[c(1,3,6)]

Subset rows exlcuding 1, 3, and 6 (pandas)

transactions[-c(1,3,6)]
transactions[!c(1,3,6)]

Subset the first 3 rows (pandas)

transactions[1:3]
head(transactions, 3)

Subset rows excluding the first 3 rows (pandas)

transactions[-1:-3]
tail(transactions, -3)

Subset the last 2 rows (pandas)

tail(transactions, 2)

Subset rows excluding the last 2 rows (pandas)

tail(transactions, -2)

Subset rows where Quantity > 1 (pandas)

transactions[Quantity > 1]

Subset rows where UserID = 2 (pandas)

transactions[UserID == 2]

Subset rows where Quantity > 1 and UserID = 2 (pandas)

transactions[Quantity > 1 & UserID == 2]

Subset rows where Quantity + UserID is > 3 (pandas)

transactions[Quantity + UserID > 3]

Subset rows where an external vector, foo, is TRUE (pandas)

foo <- c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE)
transactions[foo]

Subset rows where an external vector, bar, is positive (pandas)

bar <- c(1, -3, 2, 2, 0, -4, -4, 0, 0, 2)
transactions[bar > 0]

Subset rows where foo is TRUE or bar is negative (pandas)

transactions[foo | bar < 0]

Subset the rows where foo is not TRUE and bar is not negative (pandas)

transactions[!foo & bar >= 0]

Column subsetting (pandas)

Subset by columns 1 and 3 (pandas)

transactions[, c(1, 3), with=FALSE]

Subset by columns TransactionID and TransactionDate (pandas)

transactions[, list(TransactionID, TransactionDate)]
transactions[, .(TransactionID, TransactionDate)]  # short-hand version of line above

Subset rows where TransactionID > 5 and subset columns by TransactionID and TransactionDate (pandas)

transactions[TransactionID > 5, list(TransactionID, TransactionDate)]

Subset columns by a variable vector of columm names (pandas)

cols <- c("TransactionID", "UserID", "Quantity")
transactions[, cols, with=FALSE]

Subset columns excluding a variable vector of column names (pandas)

cols <- c("TransactionID", "UserID", "Quantity")
transactions[, !cols, with=FALSE]

Inserting and updating values (pandas)

Convert the TransactionDate column to type Date (pandas)

transactions[, TransactionDate := as.Date(TransactionDate)]

Insert a new column, Foo = UserID + ProductID (pandas)

transactions[, Foo := UserID + ProductID]

Subset rows where TransactionID is even and set Foo = NA (pandas)

transactions[TransactionID %% 2 == 0, Foo := NA]

Add 100 to each TransactionID (pandas)

transactions[, TransactionID := TransactionID + 100L]
transactions[, TransactionID := TransactionID - 100L]  # revert to original IDs

Insert a column indicating each row number (pandas)

transactions[, RowIdx := .I]

Insert columns indicating the rank of each Quantity, minimum Quantity and maximum Quantity (pandas)

transactions[, `:=`(QuantityRk=frank(Quantity, ties.method = "average"), QuantityMin=min(Quantity), QuantityMax=max(Quantity))]

Remove column Foo (pandas)

transactions[, Foo := NULL]

Remove multiple columns RowIdx, QuantityRk, and RowIdx (pandas)

transactions[, c("RowIdx", "QuantityRk", "QuantityMin", "QuantityMax") := NULL]

Grouping the rows of a data.table (pandas)

Group By + Aggregate (pandas)

Group the transations per user, measuring the number of transactions per user (pandas)

transactions[, list(Transactions = .N), by=UserID]

Group the transactions per user, measuring the transactions and average quantity per user (pandas)

transactions[, list(Transactions = .N, QuantityAvg = mean(Quantity)), by=UserID]

Group the transactions per year of the transaction date, measuring the number of transactions per year (pandas)

transactions[, list(Transactions = .N), by=year(TransactionDate)]

Group the transactions per (user, transaction-year) pair, measuring the number of transactions per group (pandas)

transactions[, list(Transactions = .N), by=list(UserID, TransactionYear=year(TransactionDate))]

Group the transactions per user, measuring the max quantity each user made for a single transaction and the date of that transaction (pandas)

transactions[, list(MaxTransactionQuantityDate=TransactionDate[which.max(Quantity)], MaxQuantity=max(Quantity)), by=UserID]

Group the transactions per (user, transaction-year), and then group by transaction-year to get the number of users who made a transaction each year (pandas)

transactions[, list(Transactions = .N), by=list(UserID, TransactionYear=year(TransactionDate))][, list(Users=.N), by=TransactionYear]

Group By + Update (pandas)

Insert a column in transactions indicating the number of transactions per user (pandas)

transactions[, UserTransactions := .N, by=UserID]

Insert columns in transactions indicating the first transaction date and last transaction date per user (pandas)

transactions[, `:=`(FirstTransactionDate=min(TransactionDate), LastTransactionDate=max(TransactionDate)), by=UserID]

For each transaction, get the date of the previous transaction made by the same user (pandas)

setorder(transactions, "UserID", "TransactionDate")
transactions[, PrevTransactionDate := c(as.Date(NA), head(TransactionDate, -1)), by=UserID]

Joining data.tables (pandas)

Setup (pandas)

# Load datasets from CSV
users <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv")
sessions <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/sessions.csv")
products <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/products.csv")
transactions <- transactions <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv")

# Convert date columns to Date type
users[, `:=`(Registered = as.Date(Registered), Cancelled = as.Date(Cancelled))]
sessions[, SessionDate := as.Date(SessionDate)]
transactions[, TransactionDate := as.Date(TransactionDate)]

Basic Joins (pandas)

Join users to transactions, keeping all rows from transactions and only matching rows from users (left join) (pandas)

users[transactions, on="UserID"]

Which transactions have a UserID not in users? (anti join) (pandas)

transactions[!users, on="UserID"]

Join users to transactions, keeping only rows from transactions and users that match via UserID (inner join) (pandas)

users[transactions, on="UserID", nomatch=0]

Join users to transactions, displaying all matching rows AND all non-matching rows (full outer join) (pandas)

merge(users, transactions, by="UserID", all=TRUE)

Determine which sessions occured on the same day each user registered (pandas)

users[sessions, on=c("UserID", "Registered" = "SessionDate"), nomatch=0]

Build a dataset with every possible (UserID, ProductID) pair (cross join) (pandas)

CJ(UserID=users$UserID, ProductID=products$ProductID)

Determine how much quantity of each product was purchased by each user (pandas)

transactions[, list(Quantity=sum(Quantity)), by=list(UserID, ProductID)][CJ(UserID=users$UserID, ProductID=products$ProductID), on=c("UserID", "ProductID")]

For each user, get each possible pair of pair transactions (TransactionID1, TransactionID2) (pandas)

t1 <- transactions[, list(UserID, TransactionID1=TransactionID)]
t2 <- transactions[, list(UserID, TransactionID2=TransactionID)]
t1[t2, on="UserID", allow.cartesian=TRUE]

Join each user to his/her first occuring transaction in the transactions table (pandas)

transactions[users, on="UserID", mult="first"]

Rolling Joins (pandas)

Determine the ID of the last session which occured prior to (and including) the date of each transaction per user (pandas)

sessions[, RollDate := SessionDate]
transactions[, RollDate := TransactionDate]
setkey(sessions, "UserID", "RollDate")
setkey(transactions, "UserID", "RollDate")
sessions[transactions, roll=TRUE]

Determine the ID of the first session which occured after (and including) the date of each transaction per user (pandas)

sessions[, RollDate := SessionDate]
transactions[, RollDate := TransactionDate]
setkey(sessions, "UserID", "RollDate")
setkey(transactions, "UserID", "RollDate")
sessions[transactions, roll=-Inf]

Non-equi joins (pandas)

Determine the first transaction that occured for each user prior to (and including) his/her Cancelled date (pandas)

setorder(transactions, "TransactionDate")
transactions[users, on=list(UserID, TransactionDate <= Cancelled), mult="first"]

Get all transactions where TransactionDate is after the user's Cancellation Date (pandas)

users[transactions, on=list(UserID, Cancelled < TransactionDate), nomatch=0]

Join + Update (pandas)

Insert the price of each product in the transactions dataset (join + update) (pandas)

transactions[products, ProductPrice := Price, on="ProductID"]

Insert the number of transactions each user made into the users dataset (pandas)

users[transactions, on="UserID", Transactions := .N, by=UserID]

Setting a key and secondary indexing (pandas)

Set the key of Transactions as UserID (pandas)

setkey(transactions, "UserID")  # notice rows are now sorted by UserID

View the key of transactions (pandas)

key(transactions)

Set the key of users as UserID and join to transactions, matching rows only (inner join) (pandas)

setkey(users, "UserID")
transactions[users, nomatch=0]

Set ProductID as the key of transactions and products without re-ordering the rows, then join matching rows only (pandas)

setkey(transactions, "ProductID", physical=FALSE)
setkey(products, "ProductID", physical=FALSE)
transactions[products, nomatch=0]

Set each ID column as a secondary join index (pandas)

setindex(transactions, "TransactionID")
setindex(transactions, "ProductID")
setindex(transactions, "UserID")
setindex(products, "ProductID")
setindex(users, "UserID")

View indices (pandas)

indices(transactions)
indices(products)
indices(users)

Inner join between users, transactions, and products (pandas)

users[transactions, on="UserID"][products, on="ProductID"]  # Note that having the pre-computed secondary indices makes this faster

Reshaping a data.table (pandas)

Setup (pandas)

# Read datasets from CSV
users <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv")
transactions <- transactions <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv")

# Convert date columns to Date type
users[, `:=`(Registered = as.Date(Registered), Cancelled = as.Date(Cancelled))]
transactions[, TransactionDate := as.Date(TransactionDate)]

# Add column TransactionWeekday as a factor with levels Sunday through Saturday
transactions[, TransactionWeekday := factor(weekdays(TransactionDate), levels=c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))]

Convert data from tall format to wide format (pandas)

One-hot encode Weekday (i.e. convert data from tall to wide, where each possible weekday is a column) (pandas)

dcast(transactions, TransactionID ~ TransactionWeekday, value.var="TransactionWeekday", fun.aggregate=function(x) length(x))

Convert data from wide format to tall format (pandas)

Build a data.table with columns {UserID, ActionType, Date} where ActionType is either "Registered" or "Cancelled" and Date is the corresponding date value (pandas)

melt(users, id.vars="UserID", measure.vars=c("Registered", "Cancelled"), variable.name="ActionType", value.name="Date")

Files

README.md

Latest commit

History

README.md

File metadata and controls

Data Wrangling With data.table

Intro

Install data.table (pandas)

Load data.table (pandas)

Build a data.table from scratch (pandas)

Read data from a CSV file (pandas)

Load transactions (pandas)

Meta info (pandas)

Full summary (pandas)

How many rows? (pandas)

How many columns? (pandas)

Get the row names (pandas)

Get the column names (pandas)

Change the name of column "Quantity" to "Quant" (pandas)

Change the name of columns ProductID and UserID to PID and UID respectively (pandas)

Ordering the rows of a data.table (pandas)

Order the rows of transactions by TransactionID descending (pandas)

Order the rows of transactions by Quantity ascending, TransactionDate descending (pandas)

Ordering the columns of a data.table (pandas)

Set the column order of transactions as ProductID, Quantity, TransactionDate, TransactionID, UserID (pandas)

Make UserID the first column of transactions (pandas)

Extracting vectors from a data.table (pandas)

Get the 2nd column (pandas)

Get the ProductID vector (pandas)

Get the ProductID vector using a variable (pandas)

Row subsetting (pandas)

Subset rows 1, 3, and 6 (pandas)

Subset rows exlcuding 1, 3, and 6 (pandas)

Subset the first 3 rows (pandas)

Subset rows excluding the first 3 rows (pandas)

Subset the last 2 rows (pandas)

Subset rows excluding the last 2 rows (pandas)

Subset rows where Quantity > 1 (pandas)

Subset rows where UserID = 2 (pandas)

Subset rows where Quantity > 1 and UserID = 2 (pandas)

Subset rows where Quantity + UserID is > 3 (pandas)

Subset rows where an external vector, foo, is TRUE (pandas)

Subset rows where an external vector, bar, is positive (pandas)

Subset rows where foo is TRUE or bar is negative (pandas)

Subset the rows where foo is not TRUE and bar is not negative (pandas)

Column subsetting (pandas)

Subset by columns 1 and 3 (pandas)

Subset by columns TransactionID and TransactionDate (pandas)

Subset rows where TransactionID > 5 and subset columns by TransactionID and TransactionDate (pandas)

Subset columns by a variable vector of columm names (pandas)

Subset columns excluding a variable vector of column names (pandas)

Inserting and updating values (pandas)

Convert the TransactionDate column to type Date (pandas)

Insert a new column, Foo = UserID + ProductID (pandas)

Subset rows where TransactionID is even and set Foo = NA (pandas)

Add 100 to each TransactionID (pandas)

Insert a column indicating each row number (pandas)

Insert columns indicating the rank of each Quantity, minimum Quantity and maximum Quantity (pandas)

Remove column Foo (pandas)

Remove multiple columns RowIdx, QuantityRk, and RowIdx (pandas)

Grouping the rows of a data.table (pandas)

Group By + Aggregate (pandas)

Group the transations per user, measuring the number of transactions per user (pandas)

Group the transactions per user, measuring the transactions and average quantity per user (pandas)

Group the transactions per year of the transaction date, measuring the number of transactions per year (pandas)

Group the transactions per (user, transaction-year) pair, measuring the number of transactions per group (pandas)

Group the transactions per user, measuring the max quantity each user made for a single transaction and the date of that transaction (pandas)

Group the transactions per (user, transaction-year), and then group by transaction-year to get the number of users who made a transaction each year (pandas)

Group By + Update (pandas)

Insert a column in transactions indicating the number of transactions per user (pandas)

Insert columns in transactions indicating the first transaction date and last transaction date per user (pandas)

For each transaction, get the date of the previous transaction made by the same user (pandas)

Joining data.tables (pandas)

Setup (pandas)

Basic Joins (pandas)

Join users to transactions, keeping all rows from transactions and only matching rows from users (left join) (pandas)

Which transactions have a UserID not in users? (anti join) (pandas)

Join users to transactions, keeping only rows from transactions and users that match via UserID (inner join) (pandas)

Join users to transactions, displaying all matching rows AND all non-matching rows (full outer join) (pandas)