DataTables, Indexes, Pandas, and Seaborn¶
In [11]:
import matplotlib.pyplot as plt
%matplotlib inline
plt.style.use('fivethirtyeight')
import seaborn as sns
import numpy as np
import pandas as pd
from glob import glob
sns.set_context("notebook")
Getting the Data¶
https://www.ssa.gov/OACT/babynames/index.html
We can run terminal/shell commands directly in a notebook! Here's the code to download the dataset (not running since it takes a while):
!wget https://www.ssa.gov/oact/babynames/state/namesbystate.zipIn [ ]:
# !wget https://www.ssa.gov/oact/babynames/state/namesbystate.zip
!unzip namesbystate.zip
In [15]:
!ls
In [16]:
!head CA.TXT
In [18]:
!wc -l CA.TXT
In [ ]:
# !cat CA.TXT
Question 1: What was the most popular name in CA last year?¶
In [23]:
ca = pd.read_csv('CA.TXT', header=None, names=['State', 'Sex', 'Year', 'Name', 'Count'])
ca.head()
Out[23]:
Slicing¶
In [25]:
ca['Count'].head()
Out[25]:
In [26]:
ca[0:3]
Out[26]:
In [ ]:
# ca[0]
In [28]:
ca.iloc[0:3, 0:2]
Out[28]:
In [29]:
ca.loc[0:3, 'State']
Out[29]:
In [30]:
ca.loc[0:5, 'Sex':'Name']
Out[30]:
What is the leftmost column?
In [37]:
emails = ca.head()
emails.index = ['a@gmail.com', 'b@gmail.com', 'c@gmail.com', 'd@gmail.com', 'e@gmail.com']
emails
Out[37]:
In [38]:
emails.loc['b@gmail.com':'d@gmail.com', 'Year':'Name']
Out[38]:
In [40]:
ca.head()
Out[40]:
In [44]:
(ca['Year'] == 2016).head()
Out[44]:
In [45]:
ca[ca['Year'] == 2016].head()
Out[45]:
Sorting¶
In [49]:
ca_sorted = ca[ca['Year'] == 2016]
ca_sorted.sort_values('Count', ascending=False).head()
Out[49]:
Question 2: Most popular names in all states for each year?¶
Put all DFs together¶
In [52]:
# Make sure that filesizes are managable
!ls -alh *.TXT | head
In [ ]:
glob('*.TXT')
In [55]:
file_names = glob('*.TXT')
baby_names = pd.concat(
(pd.read_csv(f, names=['State', 'Sex', 'Year', 'Name', 'Count']) for f in file_names)
).reset_index(drop=True)
baby_names.head()
Out[55]:
In [56]:
len(baby_names)
Out[56]:
Group by state and year¶
In [57]:
baby_names[
(baby_names['State'] == 'CA')
& (baby_names['Year'] == 1995)
& (baby_names['Sex'] == 'M')
].head()
# Now I could write 3 nested for loops...
Out[57]:
In [61]:
baby_names.groupby('State').size().head()
Out[61]:
In [64]:
state_counts = baby_names.loc[:, ('State', 'Count')]
state_counts.head()
Out[64]:
In [73]:
state_counts.groupby('State').sum().head()
Out[73]:
state_counts.group('State', np.sum)In [69]:
state_counts.groupby('State').agg(np.sum).head()
Out[69]:
Using a custom function to aggregate.
Equivalent to this code from Data 8:
state_and_groups.group('State', np.sum)Grouping by multiple columns¶
In [75]:
baby_names.groupby(['State', 'Year']).size().head()
Out[75]:
In [76]:
baby_names.groupby(['State', 'Year']).sum().head()
Out[76]:
In [80]:
baby_names.groupby(['State', 'Year', 'Sex']).sum().head()
Out[80]:
In [90]:
def first(series):
'''Returns the first value in the series.'''
return series.iloc[0]
most_popular_names = (
baby_names
.groupby(['State', 'Year', 'Sex'])
.agg(first)
)
most_popular_names
Out[90]:
This creates a multilevel index. It is quite complex, but just know that you can still slice:
In [91]:
most_popular_names[most_popular_names['Name'] == 'Samuel']
Out[91]:
And you can use .loc as so:
In [94]:
most_popular_names.loc['CA', 1997, 'M']
Out[94]:
In [95]:
most_popular_names.loc['CA', 1995:2000, 'M']
Out[95]:
Question 3: Can I deduce gender from the last letter of a person’s name?¶
Survey question time!
Compute last letter of each name¶
In [97]:
baby_names.head()
Out[97]:
In [101]:
baby_names['Name'].apply(len).head()
Out[101]:
In [102]:
baby_names['Name'].str.len().head()
Out[102]:
In [106]:
baby_names['Name'].str[-1].head()
Out[106]:
To add column to dataframe:
In [107]:
baby_names['Last letter'] = baby_names['Name'].str[-1]
baby_names.head()
Out[107]:
Group by last letter and sex¶
In [108]:
letter_counts = (baby_names
.loc[:, ('Sex', 'Count', 'Last letter')]
.groupby(['Last letter', 'Sex'])
.sum())
letter_counts.head()
Out[108]:
Visualize our result¶
Use .plot to get some basic plotting functionality:
In [110]:
# Why is this not good?
letter_counts.plot.barh(figsize=(15, 15))
Out[110]:
Reading the docs shows me that pandas will make one set of bars for each column in my table. How do I move each sex into its own column? I have to use pivot:
In [111]:
# For comparison, the group above:
# letter_counts = (baby_names
# .loc[:, ('Sex', 'Count', 'Last letter')]
# .groupby(['Last letter', 'Sex'])
# .sum())
last_letter_pivot = baby_names.pivot_table(
index='Last letter', # the rows (turned into index)
columns='Sex', # the column values
values='Count', # the field(s) to processed in each group
aggfunc=sum, # group operation
)
last_letter_pivot.head()
Out[111]:
In [112]:
last_letter_pivot.plot.barh(figsize=(10, 10))
Out[112]:
Why is this still not ideal?
- Plotting raw counts
- Not sorted by any order
In [113]:
totals = last_letter_pivot['F'] + last_letter_pivot['M']
last_letter_props = pd.DataFrame({
'F': last_letter_pivot['F'] / totals,
'M': last_letter_pivot['M'] / totals,
}).sort_values('M')
last_letter_props.head()
Out[113]:
In [114]:
last_letter_props.plot.barh(figsize=(10, 10))
Out[114]:
What do you notice?
In [ ]:
Seaborn¶
Let's use a subset of our dataset for now:
In [ ]:
ca_and_ny = baby_names[
(baby_names['Year'] == 2016)
& (baby_names['State'].isin(['CA', 'NY']))
]
ca_and_ny.head()
We actually don't need to do any pivoting / grouping for seaborn!
In [ ]:
sns.barplot(x=..., y=..., data=...)
Note the automatic confidence interval generation. Many seaborn functions have these nifty statistical features.
(It actually isn't useful for our case since we have a census. It also makes seaborn functions run slower since they use bootstrap to generate the CI, so sometimes you want to turn it off.)
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
Going to work with tips data just to demonstrate:
In [8]:
tips = sns.load_dataset("tips")
tips.head()
Out[8]:
In [ ]:
sns.distplot(...)
In [ ]:
sns.lmplot(x=..., y=..., data=...)
In [ ]:
In [ ]:
In [ ]:
In [ ]: