In [72]:
import numpy as np
import pandas as pd
from datetime import time
pd.set_option('html', False)
In [2]:
from IPython.core.display import Image
Image('http://akamaicovers.oreilly.com/images/0636920023784/lrg.jpg')
Out[2]:
Important Features in pandas¶
In [3]:
import pandas as pd
import numpy as np
Fast tabular data IO¶
In [62]:
temp = '/Users/wesm/Downloads/minutebars/%s.csv'
path = temp % 'AAPL'
!wc -l $path
In [63]:
aapl_bars = pd.read_csv(temp % 'AAPL')
aapl_bars
Out[63]:
In [64]:
%time _ = pd.read_csv(path)
Time series operations¶
In [65]:
aapl_bars.dt
Out[65]:
In [66]:
aapl_bars.index = pd.to_datetime(aapl_bars.pop('dt'))
In [69]:
aapl_bars.head()
Out[69]:
In [70]:
def load_bars(ticker):
bars = pd.read_csv(temp % ticker)
bars.index = pd.to_datetime(bars.pop('dt'))
return bars
In [79]:
aapl_bars.at_time(time(15, 0)).head(10)
Out[79]:
In [80]:
aapl_bars.close_price['2009-10-15']
Out[80]:
In [81]:
aapl_bars.close_price
Out[81]:
In [82]:
mth_mean = aapl_bars.close_price.resample('M', how=['mean', 'median', 'std'])
mth_mean
Out[82]:
In [83]:
mth_mean.plot()
Out[83]:
In [84]:
close = aapl_bars.close_price
close / close.shift(1) - 1
Out[84]:
In [85]:
minute_returns = aapl_bars.close_price.pct_change()
std_10day = pd.rolling_std(minute_returns, 390 * 10)
std_10day.resample('B').plot()
Out[85]:
Data alignment¶
In [86]:
ts1 = pd.Series(np.random.randn(10),
index=pd.date_range('1/1/2000', periods=10))
ts1
Out[86]:
In [87]:
ts2 = ts1[[0, 2, 4, 5, 6, 7, 8]]
ts2
Out[87]:
In [88]:
ts1 + ts2
Out[88]:
In [89]:
df = pd.DataFrame({'A': ts1, 'B': ts2})
df
Out[89]:
In [90]:
ibm_bars = load_bars('IBM')
In [91]:
def subsample(frame, pct=0.9):
N = len(frame)
indexer = np.sort(np.random.permutation(N)[:pct*N])
return frame.take(indexer)
f1 = subsample(ibm_bars)
f2 = subsample(aapl_bars)
f1
Out[91]:
In [92]:
both = pd.concat([f1, f2], axis=1, keys=['IBM', 'AAPL'])
both.head(20)
Out[92]:
Missing data handling¶
In [93]:
df
Out[93]:
In [94]:
df.count()
Out[94]:
In [95]:
both.count()
Out[95]:
In [96]:
df.sum()
Out[96]:
In [97]:
df.mean(1)
Out[97]:
In [98]:
df.dropna()
Out[98]:
In [99]:
df.fillna(0)
Out[99]:
In [100]:
df.fillna(method='ffill')
Out[100]:
In [101]:
df.asfreq('4h')
Out[101]:
In [102]:
df.asfreq('4h').ffill(limit=3)
Out[102]:
Groupby operations¶
In [103]:
import random, string
import matplotlib as mpl
def rands(n):
choices = string.ascii_letters
return ''.join([random.choice(choices) for _ in xrange(n)])
mpl.rc('figure', figsize=(12, 8))
ind_names = np.array(['ENERGY', 'FINANCIAL', 'TECH',
'CONSDUR', 'SERVICES', 'UTILITIES'], dtype='O')
ccys = np.array(['USD', 'EUR'], dtype='O')
Nfull = 2000
tickers = np.array(sorted(rands(5).upper() for _ in xrange(Nfull)), dtype='O')
tickers = np.unique(tickers)
industries = pd.Series(ind_names.take(np.random.randint(0, 6, Nfull)),
index=tickers, name='industry')
ccy = pd.Series(ccys.take(np.random.randint(0, len(ccys), Nfull)),
index=tickers, name='ccy')
In [104]:
ccy
Out[104]:
In [105]:
df = pd.DataFrame({'Momentum' : np.random.randn(1000) / 200 + 0.03,
'Value' : np.random.randn(1000) / 200 + 0.08,
'ShortInterest' : np.random.randn(1000) / 200 - 0.02},
index=tickers.take(np.random.permutation(Nfull)[:1000]))
df.head()
Out[105]:
In [106]:
means = df.groupby(industries).mean()
means
Out[106]:
In [107]:
means.plot(kind='barh')
Out[107]:
In [108]:
means = df.groupby([industries, ccy]).mean()
means
Out[108]:
In [109]:
keys = [industries, ccy]
zscore = lambda x: (x - x.mean()) / x.std()
normed = df.groupby(keys).apply(zscore)
In [110]:
normed.groupby(keys).agg(['mean', 'std'])
Out[110]:
Hierarchical indexing¶
In [111]:
means
Out[111]:
In [112]:
means['Momentum']
Out[112]:
In [113]:
means.ix['TECH']
Out[113]:
In [114]:
means.stack()
Out[114]:
In [115]:
means.stack().unstack('industry')
Out[115]:
Merging and joining¶
In [118]:
base = '/Users/wesm/Dropbox/book/svn/book_scripts/movielens/ml-1m'
get_path = lambda x: '%s/%s.dat' % (base, x)
unames = ['user_id', 'gender', 'age', 'occupation', 'zip']
users = pd.read_table(get_path('users'), sep='::', header=None, names=unames)
rnames = ['user_id', 'movie_id', 'rating', 'timestamp']
ratings = pd.read_table(get_path('ratings'), sep='::', header=None, names=rnames)
mnames = ['movie_id', 'title', 'genres']
movies = pd.read_table(get_path('movies'), sep='::', header=None, names=mnames)
In [119]:
movies.head()
Out[119]:
In [120]:
ratings.head()
Out[120]:
In [121]:
users.head()
Out[121]:
In [122]:
data = pd.merge(pd.merge(ratings, users), movies)
data
Out[122]:
In [123]:
rating_counts = data.groupby('title').size()
freq_titles = rating_counts.index[rating_counts > 1000]
freq_titles
Out[123]:
In [124]:
highest_rated = data.groupby('title').rating.mean()[freq_titles].order()[-20:]
highest_rated
Out[124]:
In [125]:
filtered = data[data.title.isin(highest_rated.index)]
filtered.title = filtered.title.str[:25]
filtered.groupby(['title', 'gender']).rating.count().unstack()
Out[125]:
Pivot tables¶
In [126]:
mean_ratings = data.pivot_table('rating', rows='title',
cols='gender', aggfunc='mean')
mean_ratings.tail(20)
Out[126]:
Data summary, statistics¶
summary, value_counts, etc.
In [127]:
data.title.value_counts()
Out[127]:
In [128]:
data.rating.describe()
Out[128]:
In [129]:
by_gender = data.groupby('gender').rating.describe()
by_gender
Out[129]:
In [130]:
by_gender.unstack(0)
Out[130]:
In [ ]: