One of the first post in my blog was about Pivot tables. I’d created a library to pivot tables in my PHP scripts. The library is not very beautiful (it throws a lot of warnings), but it works. These days I’m playing with Python Data Analysis and I’m using Pandas. The purpose of this post is something that I like a lot: Learn by doing. So I want to do the same operations that I did eight years ago in the post but now with Pandas. Let’s start.
I’ll start with the same datasource that I used almost ten years ago. One simple recordset with cliks and number of users
I create a dataframe with this data
import numpy as np import pandas as pd data = pd.DataFrame([ {'host': 1, 'country': 'fr', 'year': 2010, 'month': 1, 'clicks': 123, 'users': 4}, {'host': 1, 'country': 'fr', 'year': 2010, 'month': 2, 'clicks': 134, 'users': 5}, {'host': 1, 'country': 'fr', 'year': 2010, 'month': 3, 'clicks': 341, 'users': 2}, {'host': 1, 'country': 'es', 'year': 2010, 'month': 1, 'clicks': 113, 'users': 4}, {'host': 1, 'country': 'es', 'year': 2010, 'month': 2, 'clicks': 234, 'users': 5}, {'host': 1, 'country': 'es', 'year': 2010, 'month': 3, 'clicks': 421, 'users': 2}, {'host': 1, 'country': 'es', 'year': 2010, 'month': 4, 'clicks': 22, 'users': 3}, {'host': 2, 'country': 'es', 'year': 2010, 'month': 1, 'clicks': 111, 'users': 2}, {'host': 2, 'country': 'es', 'year': 2010, 'month': 2, 'clicks': 2, 'users': 4}, {'host': 3, 'country': 'es', 'year': 2010, 'month': 3, 'clicks': 34, 'users': 2}, {'host': 3, 'country': 'es', 'year': 2010, 'month': 4, 'clicks': 1, 'users': 1} ])
Now we want to do a simple pivot operation. We want to pivot on host
pd.pivot_table(data, index=['host'], values=['users', 'clicks'], columns=['year', 'month'], fill_value='' )
We can add totals
pd.pivot_table(data, index=['host'], values=['users', 'clicks'], columns=['year', 'month'], fill_value='', aggfunc=np.sum, margins=True, margins_name='Total' )
We can also pivot on more than one column. For example host and country
pd.pivot_table(data, index=['host', 'country'], values=['users', 'clicks'], columns=['year', 'month'], fill_value='' )
and also with totals
pd.pivot_table(data, index=['host', 'country'], values=['users', 'clicks'], columns=['year', 'month'], aggfunc=np.sum, fill_value='', margins=True, margins_name='Total' )
We can group by dataframe and calculate subtotals
data.groupby(['host', 'country'])[('clicks', 'users')].sum()
data.groupby(['host', 'country'])[('clicks', 'users')].mean()
And finally we can mix totals and subtotals.
out = data.groupby('host').apply(lambda sub: sub.pivot_table( index=['host', 'country'], values=['users', 'clicks'], columns=['year', 'month'], aggfunc=np.sum, margins=True, margins_name='SubTotal', )) out.loc[('', 'Max', '')] = out.max() out.loc[('', 'Min', '')] = out.min() out.loc[('', 'Total', '')] = out.sum() out.index = out.index.droplevel(0) out.fillna('', inplace=True)
And that’s all. A lot of to learn yet about data analysis, but Pandas will be definitely a good friend of mine.
You can see the Jupiter notebook in my github account