Replicating .SD in Python Datatable

.SD - Subset of Data

I will be using Jose Morales excellent post to show how .SD's functionality can be replicated in python's datatable. Not all functions can be replicated; R data.table has a whole lot more functions and features that are not yet implemented in datatable.

Link to Notebook

from datatable import dt, by, sort, mean, count, update, max, f, fread

df = fread('Data_files/iris.csv')
df.head()

Number of unique observations per column

# DT[, lapply(.SD, uniqueN)] --> Rdatatable

df.nunique()

Mean of all columns by species

# DT[, lapply(.SD, mean), by = species] --> Rdatatable

df[:, mean(f[:]), by('species')]

Filtering

First two observations by species

# DT[, .SD[1:2], by = species]

df[:2, :, by('species')]

In datatable, rows are selected in the i section after the grouping, unlike in R's data.table, where rows are selected in i before grouping, and rows selected in the .SD after grouping.

Last two observations by species

# DT[, tail(.SD, 2), by = species] 

df[-2:, :, by('species')]

Again, the rows are selected after grouping by using Python's negative index slicing.

Select the top two sorted by sepal length in descending order

# DT[order(-sepal_length), head(.SD, 2), by = species] 

df[:2, :, by('species'), sort(-f.sepal_length)]

In datatable, the sort function replicates the order function in R's data.table. Note the - symbol before the sepal_length f-expression; this instructs the dataframe to sort in descending order.

Select the top two sorted by the difference between the sepal length and sepal width

# DT[order(sepal_length - sepal_width), head(.SD, 2), by = species] 

df[:2, :, by('species'), sort(f.sepal_length - f.sepal_width)]

Just like in R's data.table, boolean expressions can be passed to the sort function.

Filter observations above the mean of sepal_length by species

# DT[, .SD[sepal_length > mean(sepal_length)], by = species] 

df[:, update(temp = f.sepal_length > mean(f.sepal_length)), by('species')]
df[f.temp == 1, f[:-1]]

Unlike in R's data.table, boolean expressions can not be applied within the i section, in the presence of by. The next best thing is to break it down into two steps - create a temporary column to hold the boolean value, and then filter on that column.

Filter rows with group size greater than 10

# DT[, .SD[.N > 10], keyby = .(species, petal_width)] 

df[:, update(temp = count() > 10), by('species', 'petal_width')]
df[f.temp == 1, f[:-1]]

Get the row with the max petal_length by species.

# DT[, .SD[which.max(petal_length)], by = species] OR 
# DT[, .SD[petal_length == max(petal_length)], by = species]  

# get rid of temp column
del df['temp']

df[0, :, by('species'), sort(-f.petal_length)]

In the above code, we take advantage of the fact that sorting is done within each group; this allows us to pick the first row per group when petal_length is sorted in descending order.

.SDCols

Including columns in .SD

# col_idx <- grep("^sepal", names(DT)) --> filter for the specicfic columns
# DT[, lapply(.SD, mean), .SDcols = col_idx]

# filter for the specific columns with a list comprehension
names = [f[name] for name in df.names
         if name.startswith('sepal')]

df[:, mean(names)]

Removing columns from .SD

# col_idx <- grep("^(petal|species)", names(DT))
# DT[, lapply(.SD, mean), .SDcols = -col_idx] --> exclusion occurs within .SDcols

# here, exclusion occurs within the list comprehension
names = [f[name] for name in df.names 
         if not name.startswith(('petal','species'))] 

df[:, mean(names)]

Column ranges

# DT[, lapply(.SD, mean), .SDcols = sepal_length:sepal_width]

df[:, mean(f['sepal_length':'sepal_width'])]

Summary

We've seen how to replicate .SD in datatable. There are other functionalities in .SD that are not presently possible in Python's datatable. It is possible that in the future, .SD will be implemented to allow for custom aggregation functions. That would be truly awesome, as it would allow numpy functions and functions from other Python libraries into datatable.