Split-Apply-Combine in Python

Last time we talked about how to do data wrangling by R, and we found that the package dplyr is very functional. Then I am wondering, does python also have a similar module or package?

The answer is Yes! Pandas has the similar groupby operation. Let us then see how does python work on split-Apply-Combine problems.

First let us load some libraries and load the classic iris data as our example dataset.

# First we load the library
import numpy as np
import pandas as pd
import seaborn as sns
from sklearn import datasets
iris = datasets.load_iris()

#Transform the sklearn Dataframe into pandas Dataframe
iris = pd.DataFrame(np.hstack((iris.data, iris.target.reshape(-1,1))),
                   columns = iris.feature_names + ['target'])
iris.target = iris.target.astype('category')

iris.head()

Then let us see more specific functions comparing with dplyr in R.

1. select

There is no specific select function in pandas but we can use its normal extraction or indexing operators. Also, if we want to rename the column, we can use rename() function.
Here is an example:

# Select from column 'sepal length(cm)' to 'target'
iris.loc[:, 'sepal length (cm)':'target'].head()

# Select and at the same time rename the columns
iris.rename(columns={'sepal length (cm)':'sepal_len',
                     'sepal width (cm)':'sepal_w',
                     'petal length (cm)':'petal_len',
                     'petal width (cm)':'petal_w'}, inplace = True)
iris.head()

2. filter

This time pandas has a matching function called query. The difference is that we nned to write the expression as a string, and evaluate the string in the context of the DataFrame.

pd.DataFrame.query(expr, inplace=False, **kwargs)

Here is an example:

# Filter to only have the first species whose sepal_len longer or equal to 5 
iris.query('sepal_len >= 5 & target in [0.0, 1.0]').head()

3. arrange

pd.DataFrame.sort_values(by, axis=0, ascending=True, inplace=False, kind='quicksort', na_position='last')

where by is a string or list of string contains names which refer to the axis items; axis is the axis to sort. 0 represents index and 1 represents columns. for na_position we could choose put them at the beginning or at the end.

Here is an example:

#Descending sort by sepal length and width
iris.sort_values(['sepal_len', 'sepal_w'], ascending = False).head()

Actually, there is another sorting method called sort_index, which as its literally meaning, sorts the dataframe by index.

4. mutate

Here, the matching function is called assign. The difference between arrange and assign here is that python cannot calculate the arguments in order since here the args are considered in a dictionary which is unordered. In this case, we need to split up the assigns and pass in a callable to the second assign (We may use lambda function as our callable).

Here is an example:

# Calculate the ratio of sepal
iris.assign(sepal_ratio = iris.sepal_len/iris.sepal_w).head()

# Calculate the ratio of sepal and petal and then calculate the difference between them
iris.assign(sepal_ratio = iris.sepal_len/iris.sepal_w,
           petal_ratio = iris.petal_len/iris.petal_w).assign(diff = lambda df: df.sepal_ratio - df.petal_ratio).head()

5. summarise

DataFrame.agg(func, axis=0, *args, **kwargs) where func is the function to use for aggregating the data. Accepted Combinations are:

• string function name
• function
• list of functions
• dict of column names -> functions (or list of functions)

# Use simple mean function
iris.sepal_len.mean()

5.843333333333335

# Use agg
print(iris.agg(['mean','sum']))

# Specify which column
print(iris.agg({'sepal_len': ['count','std']}))

       sepal_len  sepal_w   petal_len     petal_w
mean    5.843333    3.054    3.758667    1.198667
sum   876.500000  458.100  563.800000  179.800000
        sepal_len
count  150.000000
std      0.828066

6. group_by

pandas has create an specific function for groupby. After this operation, the DataFrame will become to another type of object called pd.DataFrame.groupby objects. Here is the detailed information:

pd.DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)[source] Normally, we will pass a string or a list of column names to by.

Here is an example:

iris.groupby('target').agg(['mean','sum','count'])

We could see the results is a MultiIndex in the columns which can be useful when dealing with some complicated datasets. If you want to still use the normal index, remember to add as_index = False within groupby function.

iris.groupby('target').mean()

7. join

Here we use join function in pandas. Here is the detailed information:

pd.DataFrame.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False) where on is the column name of a list of column names; how can choose from {‘left’, ‘right’, ‘outer’, ‘inner’}; lsuffix and rsuffix is the left suffix and right suffix.

By the way, we can also use pd.concat. Here is an example:

# Both dfs are 3 x 4 DataFrame
df_mean = iris.groupby('target').mean()
df_count = iris.groupby('target').count()

#Make a outer join
df_count.join(df_mean, how='outer', lsuffix='_mean', rsuffix='_n')

8. Make them all together!

Now we have known all the matching operations in pandas, let us now put them all together and see how it works.

Our target is: Select only sepal information for these three species, find the samples that has sepal length longer than 4 and sepal width is 3; then generate mean and max summary information for each species

iris.head()

# Get the groupby objects
iris_group = iris.iloc[:,[0,1,4]].query('sepal_len > 4 & sepal_w == 3').groupby('target')
# use agg
iris_group.agg(['mean','sum'])

# Use join
iris_group.mean().join(iris_group.sum(), how = 'outer',
                       lsuffix='_mean', rsuffix='_sum' )

Reference:

https://pandas.pydata.org/pandas-docs/stable/groupby.html

http://nbviewer.jupyter.org/gist/TomAugspurger/6e052140eaa5fdb6e8c0