Another in a series of notebooks that describe Pandas' powerful data management tools. In this one we summarize our data in a variety of ways. Which is more interesting than it sounds.
Outline:
value_counts method. Note: requires internet access to run.
This Jupyter notebook was created by Dave Backus, Chase Coleman, and Spencer Lyon for the NYU Stern course Data Bootcamp.
In [1]:
import sys # system module
import pandas as pd # data package
import matplotlib.pyplot as plt # graphics module
import datetime as dt # date and time module
import numpy as np # foundation for Pandas
%matplotlib inline
# check versions (overkill, but why not?)
print('Python version:', sys.version)
print('Pandas version: ', pd.__version__)
print('Today: ', dt.date.today())
We use the IMF's data on government debt again, specifically its World Economic Outlook database, commonly referred to as the WEO. We focus on government debt expressed as a percentage of GDP, variable code GGXWDG_NGDP.
The central question here is how the debt of Argentina, which defaulted in 2001, compared to other countries. Was it a matter of too much debt or something else?
In [2]:
url = 'http://www.imf.org/external/pubs/ft/weo/2016/02/weodata/WEOOct2016all.xls'
weo = pd.read_csv(url, sep='\t',
usecols=[1, 2] + list(range(19,46)),
thousands=',',
na_values=['n/a', '--'])
print('Variable dtypes:\n', weo.dtypes.head(6), sep='')
In [3]:
weo.head()
Out[3]:
In [4]:
# select debt variable
variables = ['GGXWDG_NGDP']
db = weo[weo['WEO Subject Code'].isin(variables)]
# drop variable code column (they're all the same)
db = db.drop('WEO Subject Code', axis=1)
# set index to country code
db = db.set_index('ISO')
# name columns
db.columns.name = 'Year'
# transpose
dbt = db.T
# see what we have
dbt.head()
Out[4]:
Example. Let's try a simple graph of the dataframe dbt. The goal is to put Argentina in perspective by plotting it along with many other countries.
In [5]:
fig, ax = plt.subplots()
dbt.plot(ax=ax,
legend=False, color='blue', alpha=0.3,
ylim=(0, 150))
ax.set_ylabel('Percent of GDP')
ax.set_xlabel('')
ax.set_title('Government debt', fontsize=14, loc='left')
dbt['ARG'].plot(ax=ax, color='black', linewidth=2)
Out[5]:
Exercise.
South America ISO codes?
Google search gives:
http://www.countrycallingcodes.com/iso-country-codes/south-america-codes.php
In [6]:
sa_iso = pd.read_html('http://www.countrycallingcodes.com/iso-country-codes/south-america-codes.php', header = 0,
attrs={"border": 1, "cellpadding": 3, "cellspacing": 0})[0]
In [7]:
sa_iso.columns = ['Country', 'iso2', 'iso3', 'domain']
In [8]:
countries = list(sa_iso['iso3'])
In [9]:
dbt_sa = db[db.index.isin(countries)].T
In [10]:
dbt_sa.head()
Out[10]:
In [11]:
fig, ax = plt.subplots(figsize = (10, 4.5))
# skip ARG
dbt_sa.iloc[:, 1:].plot(ax=ax, alpha=0.5, ylim=(0, 150))
ax.set_ylabel('Percent of GDP')
ax.set_xlabel('')
ax.set_title('Government debt', fontsize=14, loc='left')
dbt['ARG'].plot(ax=ax, color='black', linewidth=2)
dbt['USA'].plot(ax=ax, color='red', linewidth=2)
# Put the legend to the right of the current axis
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5))
Out[11]:
In [ ]:
Let's step back a minute. What we're trying to do is compare Argentina to other countries. What's the best way to do that? This isn't a question with an obvious best answer, but we can try some things, see how they look. One thing we could do is compare Argentina to the mean or median country. Or to some other feature of the distribution.
We work up to this by looking first at some features of the distribution of government debt numbers across countries. Some of this we've seen, some is new.
In [12]:
dbt.shape
Out[12]:
In [13]:
# count non-missing values
dbt.count(axis='columns').plot()
Out[13]:
Let's take the data for 2001 -- the year of Argentina's default -- and see what how Argentina compares. Was its debt high compare to other countries?
which leads to more questions. How would we compare? Compare Argentina to the mean or median? Something else?
Let's see how that works.
In [14]:
# 2001 data
db01 = dbt.loc['2001']
In [15]:
# distribution of countries? histogram
#hist takes only non missing values
db01_nonan = db01[~db01.isnull()]
In [16]:
plt.hist(db01_nonan, bins=40)
Out[16]:
In [17]:
db01['ARG']
Out[17]:
In [18]:
plt.hist(db01_nonan, bins=40)
plt.axvline(db01['ARG'], color = 'red', lw =2)
Out[18]:
In [19]:
db01.mean()
Out[19]:
In [20]:
db01.median()
Out[20]:
In [21]:
db01.quantile(q=[0.25, 0.5, 0.75])
Out[21]:
In [22]:
plt.hist(db01_nonan, bins=40, alpha = .5)
plt.axvline(db01['ARG'], color = 'red', lw =3)
plt.axvline(db01.mean(), color = 'green', alpha=.9, lw =3)
plt.axvline(db01.median(), color = 'yellow', alpha=.9, linestyle='--',lw =3)
plt.xlim([0, 250])
Out[22]:
In [23]:
plt.hist(db01_nonan, bins=40, alpha = .5)
plt.axvline(db01.quantile(q=0.25), color = 'y', lw = 2)
plt.axvline(db01.quantile(q=0.5), color = 'y', lw = 2)
plt.axvline(db01.quantile(q=0.75), color = 'y', lw = 2)
Out[23]:
In [24]:
db01.describe()
Out[24]:
This looks a bit ugly. Let's make the figrue somewhat prettier.
In [25]:
fig, ax = plt.subplots()
db01.hist(bins=35, ax=ax, alpha=0.35)
ax.set_xlabel('Government Debt (Percent of GDP)')
ax.set_ylabel('Number of Countries')
ymin, ymax = ax.get_ylim()
ax.vlines(db01['ARG'], ymin, ymax, color='red', lw=2)
Out[25]:
Comment Compared to the whole sample of countries in 2001, it doesn't seem that Argentina had particularly high debt. How about 2002?
In [26]:
db02 = dbt.loc['2002']
fig, ax = plt.subplots()
db02.hist(bins=35, ax=ax, alpha=0.35)
ax.set_xlabel('Government Debt (Percent of GDP)')
ax.set_ylabel('Number of Countries')
ymin, ymax = ax.get_ylim()
ax.vlines(db02['ARG'], ymin, ymax, color='red', lw=2)
Out[26]:
In [ ]:
In [27]:
# here we compute the mean across countries at every date
dbt.mean(axis='columns')
Out[27]:
In [28]:
dbt.mean(axis='columns').plot()
Out[28]:
In [29]:
# or we could do the median
dbt.median(axis='columns')
Out[29]:
In [30]:
# changing distribution?
fig, ax = plt.subplots()
dbt.mean(axis='columns').plot(ax=ax, label='mean')
dbt.quantile(q=.25, axis='columns').plot(ax=ax, label='25%', color='k', linestyle='--')
dbt.median(axis='columns').plot(ax=ax, label='median', color='gray', linestyle='--')
dbt.quantile(q=.75, axis='columns').plot(ax=ax, label='75%', color='k', linestyle='--')
ax.legend(loc='best')
Out[30]:
In [31]:
# changing distribution/ranking of ARG?
fig, ax = plt.subplots()
dbt.mean(axis='columns').plot(ax=ax, label='mean')
dbt.median(axis='columns').plot(ax=ax, label='median', color='gray', linestyle='--')
dbt.quantile(q=.25, axis='columns').plot(ax=ax, label='25%', color='k', linestyle='--')
dbt.quantile(q=.75, axis='columns').plot(ax=ax, label='75%', color='k', linestyle='--')
dbt['ARG'].plot(ax=ax, color='red')
ax.legend(loc='best')
Out[31]:
Descriptive statistics in the country dimension
In [32]:
# or a bunch of stats at once
# NB: db not dbt (there's no axis argument here)
db.describe()
Out[32]:
Descriptive statistics in the time dimension
In [33]:
# the other way
dbt.describe()
Out[33]:
In [ ]:
In [ ]:
Example. Let's add the mean to our graph. We make it a dashed line with linestyle='dashed'.
In [34]:
fig, ax = plt.subplots()
dbt.plot(ax=ax,
legend=False, color='blue', alpha=0.2,
ylim=(0,200)
)
dbt['ARG'].plot(ax=ax, color='black', linewidth=1.5)
ax.set_ylabel('Percent of GDP')
ax.set_xlabel('')
ax.set_title('Government debt', fontsize=14, loc='left')
dbt.mean(axis=1).plot(ax=ax, color='black', linewidth=2, linestyle='dashed')
Out[34]:
Question. Do you think this looks better when the mean varies with time, or when we use a constant mean? Let's try it and see.
In [35]:
dbar = dbt.mean().mean()
dbar
Out[35]:
In [36]:
fig, ax = plt.subplots()
dbt.plot(ax=ax,
legend=False, color='blue', alpha=0.3,
ylim=(0,150)
)
dbt['ARG'].plot(ax=ax, color='black', linewidth=1.5)
ax.set_ylabel('Percent of GDP')
ax.set_xlabel('')
ax.set_title('Government debt', fontsize=14, loc='left')
xmin, xmax = ax.get_xlim()
ax.hlines(dbar, xmin, xmax, linewidth=2, linestyle='dashed')
Out[36]:
Exercise. Which do we like better?
Exercise. Replace the (constant) mean with the (constant) median? Which do you prefer?
In [ ]:
A categorical variable is one that takes on a small number of values. States take on one of fifty values. University students are either grad or undergrad. Students select majors and concentrations.
We're going to do two things with categorical data:
value_counts method. This is a series method, we apply it to one series/variable at a time. We start with the combined MovieLens data we constructed in the previous notebook.
In [37]:
url = 'http://pages.stern.nyu.edu/~dbackus/Data/mlcombined.csv'
ml = pd.read_csv(url, index_col=0)
print('Dimensions:', ml.shape)
In [38]:
ml.head()
Out[38]:
In [39]:
# fix up the dates
ml["timestamp"] = pd.to_datetime(ml["timestamp"], unit="s")
ml.head(10)
Out[39]:
In [40]:
# which movies have the most ratings?
ml['title'].value_counts().head(10)
Out[40]:
In [42]:
ml['title'].value_counts().head(50).plot(kind='bar', alpha=0.5, figsize=(16, 4))
Out[42]:
In [43]:
# which people have rated the most movies?
ml['userId'].value_counts().head(10)
Out[43]:
In [44]:
ml['rating'][ml['title']== 'Pulp Fiction (1994)'].value_counts().plot(kind='bar')
ml['rating'][ml['title']== 'Forrest Gump (1994)'].value_counts().plot(kind='bar', color='red')
Out[44]:
Next up: group data by some variable. As an example, how would we compute the average rating of each movie? If you think for a minute, you might think of these steps:
groupby method. Pandas has tools that make that relatively easy.
In [45]:
# group
g = ml.groupby('title')
type(g)
Out[45]:
Now that we have a groupby object, what can we do with it?
In [46]:
# the number in each category
g.count()
Out[46]:
In [47]:
# what type of object have we created?
type(g.count())
Out[47]:
Comment. Note that the combination of groupby and count created a dataframe with
In [48]:
gm = g['rating'].mean()
gm.head()
Out[48]:
In [49]:
# how many ratings?
grouped = g.count()
grouped = grouped.rename(columns={'rating': 'Number'})
# average rating?
grouped['Mean'] = g['rating'].mean()
grouped.head(10)
Out[49]:
In [50]:
grouped.plot.scatter(x='Number', y='Mean')
Out[50]:
Exercise. Compute the median and add it to the dataframe.
In [51]:
grouped['Median'] = g['rating'].median()
grouped.plot.scatter(x='Number', y='Median')
Out[51]:
In [52]:
ml.head()
Out[52]:
In [53]:
mean_rating = ml.groupby(['userId', 'title']).mean()
mean_rating['rating'].head()
Out[53]:
In [57]:
mean_rating.head()
Out[57]:
In [58]:
mean_rating.index.get_level_values('userId')==100
Out[58]:
In [59]:
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==100]
Out[59]:
In [60]:
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==10].hist()
Out[60]:
In [61]:
fig, ax = plt.subplots(2, 2, figsize=(14, 8), sharex=True)
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==1].hist(ax=ax[0,0])
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==97].hist(ax=ax[0,1])
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==100].hist(ax=ax[1,0])
mean_rating['rating'].iloc[mean_rating.index.get_level_values('userId')==210].hist(ax=ax[1,1])
Out[61]:
In [ ]:
In [ ]:
In [ ]:
The Brandon Rhodes video covers most of this, too.
In [ ]: