Pandas Apply Some Function¶
Below is a basic tutorial on applying some function to a Pandas DataFrame in various ways. For more advanced stuff, you should really educate yourself on the Pandas concept of "split-apply-combine"
We consider two scenarios, using two different data formats. Using the Pandas definitions, we consider these as "stacked" and "unstacked". For more on this, see Pandas docs on reshaping data
Enter some function: Compound Annual Growth Rate (CAGR)
$$ {CAGR={Last/First}^{1/n}-1} $$
In [1]:
import pandas as pd
import numpy as np
Scenario 1: Unstacked¶
In [2]:
df = pd.DataFrame(data=np.random.rand(36,4),
index=pd.date_range(end=pd.datetime.now(), periods=36, freq='MS'),
columns=['A', 'B', 'C', 'D'])
In [3]:
df.shape
Out[3]:
In [4]:
df.index.names = ['year_month_start']
df.columns.names = ['category']
In [5]:
df.head()
Out[5]:
In [6]:
df.tail()
Out[6]:
Unstacked: Compute per Category¶
Compute over total history of our data.
In this case, make sure your data is sorted!
In [16]:
df = df.sort_index()
In [17]:
cagr = (df.iloc[-1] / df.iloc[0]) ** (1/len(df)) - 1
In [18]:
cagr = cagr.to_frame()
cagr.columns = ['cagr']
In [19]:
cagr
Out[19]:
Unstacked: Compute per Category and Time Period¶
Group by year and compute growth over months.
In [10]:
gb = df.groupby(pd.TimeGrouper(freq='A'))
In [24]:
def_cagr = lambda d: (d.iloc[-1] / d.iloc[0]) ** (1/len(d)) - 1
In [25]:
cagr = gb.apply(def_cagr)
In [26]:
cagr.index.names = ['year_end']
In [27]:
cagr
Out[27]:
Scenario 2: Stacked¶
What if our data looked different? Below is the same data in a different format, where the category and date make up a multi-index.
In [28]:
df = df.stack().swaplevel().sort_index().to_frame()
In [29]:
df.index = df.index.set_names('category', level=0)
In [30]:
df.columns = ['data']
In [31]:
df.head()
Out[31]:
In [32]:
df.tail()
Out[32]:
Stacked: Compute per Category¶
In [33]:
gb = df.reset_index().groupby('category')
In [34]:
cagr = (gb.nth(-1).data / gb.nth(0).data) ** (1/gb.size()) - 1
In [35]:
cagr = cagr.to_frame()
cagr.columns = ['cagr']
In [36]:
cagr
Out[36]:
Stacked: Compute per Category and Time Period¶
In [37]:
gb = df.reset_index(level=0).groupby(['category', pd.TimeGrouper(freq='A')])
In [38]:
cagr = (gb.nth(-1).data / gb.nth(0).data) ** (1/gb.size()) - 1
In [39]:
cagr = cagr.to_frame()
cagr.columns = ['cagr']
cagr.index.names = ['category', 'year_end']
In [40]:
cagr
Out[40]: