In [1]:

import datetime as dt

import numpy as np
import pandas as pd

pd.set_option('precision', 2)

In [2]:

# Sample dataset w/ 5 days of data

def make_dataset(n_days=4):
    if n_days not in (4, 5):
        raise ValueError(f"n_days must be 4 or 5, got {n_days:d}.")
    
    data = {
        'date': ['2015-12-28', '2015-12-29', '2015-12-30', 
                 '2015-12-31', '2016-01-04'],
        'goog': [762.51, 776.60, 771.00, 758.88, 741.84],
        'aapl': [106.82, 108.74, 107.32, 105.26, 105.35]
    }
    
    n_max = len(data['date'])
    slice_ = slice(n_max - n_days, n_max)
    
    sub = {k: v[slice_] for k,v in data.items()}
    dates = sub['date']
    n = len(dates)
    
    # breakpoint()
    rv = pd.DataFrame({
        'ticker': ['GOOG'] * n + ['AAPL'] * n,
        'date': [pd.to_datetime(x) for x in dates] * 2,
        'close': sub['goog'] + sub['aapl']
    })
    
    return rv

In [3]:

def make_long():
    day_1 = dt.date(2015, 12, 29)
    day_2 = dt.date(2015, 12, 30)
    
    col_close = 'close'
    col_open = 'open'
    
    cols = ['date', 'ticker', 'variable', 'value']
    
    rv = pd.DataFrame([
      {'ticker': 'GOOG', 'date': day_1, 'variable': col_close, 'value': 776.60},
      {'ticker': 'GOOG', 'date': day_2, 'variable': col_close, 'value': 771.00},
      {'ticker': 'AAPL', 'date': day_1, 'variable': col_open,  'value': 107.01},
      {'ticker': 'AAPL', 'date': day_1, 'variable': col_close, 'value': 105.26}
    ], columns=cols)
    
    return rv

Programming with Data:
Advanced Python and Pandas

Advanced Merging & Reshaping¶

Grouped and Ordered Data¶

Working again with securities market data. In quant finance, this is a common data type, daily stock prices.

Display the data¶

In [4]:

_dts = ['2015-12-29', '2015-12-30', '2015-12-31', '2016-01-04']
_goog = [776.60, 771.00, 758.88, 741.84]
_aapl = [108.74, 107.32, 105.26, 105.35]

df = pd.DataFrame({
    'ticker': ['GOOG'] * 4 + ['AAPL'] * 4,
    'date': [pd.to_datetime(x) for x in _dts] * 2,
    'close': _goog + _aapl
})
df

Out[4]:

	ticker	date	close
0	GOOG	2015-12-29	776.60
1	GOOG	2015-12-30	771.00
2	GOOG	2015-12-31	758.88
3	GOOG	2016-01-04	741.84
4	AAPL	2015-12-29	108.74
5	AAPL	2015-12-30	107.32
6	AAPL	2015-12-31	105.26
7	AAPL	2016-01-04	105.35

A single, ordered series¶

In [5]:

tbill = pd.DataFrame({
    'date': [pd.to_datetime(x) for x in ['2015-12-30', '2016-01-04']],
    'rate': [2.40, 2.56]
})
tbill

Out[5]:

	date	rate
0	2015-12-30	2.40
1	2016-01-04	2.56

Merge data that is grouped and ordered¶

Left panel is irregularly spaced, e.g. business days
Right time series also irregularly spaced, e.g. a sparse subset of the first series

How not to do the merge¶

Don't use plain pd.merge and fill forward across groups.

In [6]:

pd.merge(df, tbill, on='date', how='left').ffill()

Out[6]:

	ticker	date	close	rate
0	GOOG	2015-12-29	776.60	NaN
1	GOOG	2015-12-30	771.00	2.40
2	GOOG	2015-12-31	758.88	2.40
3	GOOG	2016-01-04	741.84	2.56
4	AAPL	2015-12-29	108.74	2.56
5	AAPL	2015-12-30	107.32	2.40
6	AAPL	2015-12-31	105.26	2.40
7	AAPL	2016-01-04	105.35	2.56

Merge Ordered V2¶

In [7]:

mkt = pd.merge_ordered(df, tbill, on='date', left_by='ticker', fill_method='ffill')
mkt

Out[7]:

	ticker	date	close	rate
0	GOOG	2015-12-29	776.60	NaN
1	GOOG	2015-12-30	771.00	2.40
2	GOOG	2015-12-31	758.88	2.40
3	GOOG	2016-01-04	741.84	2.56
4	AAPL	2015-12-29	108.74	NaN
5	AAPL	2015-12-30	107.32	2.40
6	AAPL	2015-12-31	105.26	2.40
7	AAPL	2016-01-04	105.35	2.56

Reshaping & Pivoting¶

Wide and Long Formats¶

Depending on the operation or the data storage location, data stored in a "wide" or "long" format

Long Format¶

Common format for data in relational databases because allows new attributes without a schema change
"Long" format is also called "stacked" or "record" format in the pandas documentation. Also called Entity-Attribute-Value (EAV)
"Sparse" by design

TODO: Include diagram of Long Format

In [8]:

def make_long_aapl():
    day_1 = dt.date(2015, 12, 29)
    day_2 = dt.date(2015, 12, 30)
    
    col_close = 'close'
    col_open = 'open'
    
    cols = ['date', 'ticker', 'variable', 'value']
    
    rv = pd.DataFrame([
      {'ticker': 'AAPL', 'date': day_1, 'variable': col_open,  'value': 106.96},
      {'ticker': 'AAPL', 'date': day_1, 'variable': col_close, 'value': 108.74},
      {'ticker': 'AAPL', 'date': day_2, 'variable': col_open,  'value': 108.58},
      {'ticker': 'AAPL', 'date': day_2, 'variable': col_close, 'value': 107.32}
    ], columns=cols)
    
    return rv

Simplest Long Format¶

Multiple attributes for a single entity (AAPL)
Row for every period (12/29 & 12/30) x (number of attributes)

In [9]:

aapl_long = make_long_aapl()
aapl_long

Out[9]:

	date	ticker	variable	value
0	2015-12-29	AAPL	open	106.96
1	2015-12-29	AAPL	close	108.74
2	2015-12-30	AAPL	open	108.58
3	2015-12-30	AAPL	close	107.32

Wide Format¶

Identifiers stored in the index
Each attribute has its own column
Common format for use by machine learning algorithms

TODO: Include diagram of wide format

Long-to-Wide¶

In [10]:

aapl_long

Out[10]:

	date	ticker	variable	value
0	2015-12-29	AAPL	open	106.96
1	2015-12-29	AAPL	close	108.74
2	2015-12-30	AAPL	open	108.58
3	2015-12-30	AAPL	close	107.32

In [11]:

aapl_long.pivot(index='date', columns='variable', values='value')

Out[11]:

variable	close	open
date
2015-12-29	108.74	106.96
2015-12-30	107.32	108.58

Long-to-Wide with multiple ID columns¶

In [12]:

aapl_wide = aapl_long.set_index(['date', 'ticker', 'variable']).unstack()
aapl_wide

Out[12]:

		value
	variable	close	open
date	ticker
2015-12-29	AAPL	108.74	106.96
2015-12-30	AAPL	107.32	108.58

Wide-to-Long¶

In [13]:

aapl_wide.stack().reset_index()

Out[13]:

	date	ticker	variable	value
0	2015-12-29	AAPL	close	108.74
1	2015-12-29	AAPL	open	106.96
2	2015-12-30	AAPL	close	107.32
3	2015-12-30	AAPL	open	108.58

Pivot Tables¶

In [14]:

mkt

Out[14]:

	ticker	date	close	rate
0	GOOG	2015-12-29	776.60	NaN
1	GOOG	2015-12-30	771.00	2.40
2	GOOG	2015-12-31	758.88	2.40
3	GOOG	2016-01-04	741.84	2.56
4	AAPL	2015-12-29	108.74	NaN
5	AAPL	2015-12-30	107.32	2.40
6	AAPL	2015-12-31	105.26	2.40
7	AAPL	2016-01-04	105.35	2.56

Simple Pivot Table¶

In [15]:

pd.pivot_table(mkt, index='ticker', aggfunc='mean')

Out[15]:

	close	rate
ticker
AAPL	106.67	2.45
GOOG	762.08	2.45

Programming with Data:Advanced Python and Pandas