Frictionless Data Science with Python¶
Catherine Devlin ([email protected], @catherinedevlin)
Postgres Open 2014
About Me¶
- Oracle DBA 1999 - 2010
- PostgreSQL DBA 2010 -
- Python 2004 -
- Founding chair of PyOhio
All About Recent Data-Handling Developments in Python
Some Recent Data-Handling Developments in Python
A Few Recent Data-Handling Developments in Python
- IPython Notebook
- Get & Manipulate
- Pandas
- ddlgenerator
- Serve
- sandman
- HTSQL
- Visualize
- matplotlib
- mpld3
- Bokeh
(aka "Project Jupyter")
Browser-based enhanced Python environment
%sql¶
pip install ipython_sql
pip install psycopg2In [1]:
%load_ext sql
%sql postgresql://:@/pgo
Out[1]:
In [2]:
%sql SELECT * FROM yob2010 LIMIT 5
Out[2]:
In [3]:
%%sql
SELECT *
FROM yob2010
LIMIT 5
Out[3]:
In [4]:
names = %sql SELECT * FROM yob2010
In [5]:
names[2]
Out[5]:
In [6]:
names[2].sex
Out[6]:
WARNINGS:
- Do not run biiiiiiiig queries with
%sql - Keeps an open session
Getting & Manipulating Data¶
Getting data¶
In [7]:
import pandas as pd
In [8]:
!head sample_data/yob2000.txt
In [9]:
yob2000 = pd.read_csv('sample_data/yob2000.txt', names=['name','sex','n_births'])
In [10]:
yob2000.head()
Out[10]:
In [11]:
pd.read_excel
Out[11]:
Manipulating data with Pandas¶
- Join
- Sort
- Filter
- Broadcast
- Python functions
- Apply functions to groups
- Moving averages
- yawn
In [12]:
yob2000.head(6).T
Out[12]:
In [13]:
yob2000.head(6).T.sum(axis=1)
Out[13]:
In [14]:
ls sample_data/yob*.txt
In [15]:
all_names = pd.DataFrame()
for yr in range(2000, 2011):
one_yr = pd.read_csv('sample_data/yob%d.txt' % yr, names=['name','sex','n_births'])
one_yr['year'] = yr
all_names = all_names.append(one_yr, ignore_index=True)
In [16]:
all_names.head()
Out[16]:
In [17]:
all_names.tail()
Out[17]:
In [18]:
lesleys = all_names[all_names.name.str.startswith('Lesl')]
lesleys
Out[18]:
In [19]:
lesleys.pivot_table('n_births', rows='year',
cols='sex', aggfunc=sum)
Out[19]:
In [20]:
gender = all_names.pivot_table('n_births', rows='name',
cols='sex', aggfunc=sum)
gender
Out[20]:
In [21]:
gender['total'] = gender.F + gender.M
gender['ratio'] = gender.F / gender.total
gender.head()
Out[21]:
In [22]:
gender['andro'] = -1 * (abs(0.5 - gender.ratio))
gender.sort('andro', ascending=False)[
gender.total > 10000].head()
Out[22]:
Dropping duplicates¶
In [23]:
states = pd.read_csv('sample_data/states.csv')
states
Out[23]:
In [24]:
states.drop_duplicates(['state','capitol'])
Out[24]:
In [25]:
states.drop_duplicates(['state','capitol'],take_last=True)
Out[25]:
PostgreSQL -> Pandas -> PostgreSQL¶
In [26]:
%sql SELECT count(*) FROM yob2010
Out[26]:
In [27]:
import sqlalchemy as sa
db_engine = sa.create_engine('postgresql://:@/pgo')
allpeople = pd.read_sql_table('yob2010',
db_engine)
xmen = allpeople[allpeople.name.str.startswith('X')]
xmen.to_sql('xmen', db_engine)
In [28]:
%sql SELECT * FROM xmen LIMIT 5
Out[28]:
%sql shortcut¶
In [29]:
chris = %sql SELECT * FROM yob2010 WHERE name LIKE 'Chris%'
chris[:5]
Out[29]:
In [30]:
chrises = chris.DataFrame()
chrises.head()
Out[30]:
In [31]:
%config SqlMagic.autopandas = True
Special bonus: autopandas makes biiiiiiiiig queries safer in %sql
In [32]:
erics = %sql SELECT * FROM yob2010 WHERE name LIKE 'Eric%'
erics.head()
Out[32]:
In [33]:
%sql SELECT count(*) FROM erics
In [34]:
%sql PERSIST erics
Out[34]:
In [35]:
%sql SELECT * FROM erics LIMIT 5
Out[35]:
A quick-and-dirty *-to-SQL converter¶
pip install ddlgenerator
pip install beautifulsoup4
In [36]:
cat sample_data/animals.csv
In [37]:
!ddlgenerator postgresql sample_data/animals.csv
In [38]:
!ddlgenerator --inserts postgresql sample_data/animals.csv | psql pgo
In [39]:
%sql SELECT * FROM animals
Out[39]:
In [40]:
!ddlgenerator postgresql --inserts http://en.wikipedia.org/wiki/List_of_cities_in_Ohio | psql pgo > /dev/null
In [41]:
%sql SELECT * FROM list_of_cities_in_ohio LIMIT 5;
Out[41]:
In [42]:
!cat sample_data/knights.yaml
In [43]:
!ddlgenerator postgresql sample_data/knights.yaml
In [44]:
!ddlgenerator --inserts postgresql sample_data/knights.yaml | psql pgo
In [45]:
%sql select * from knights
Out[45]:
In [46]:
%sql select * from followers
Out[46]:
Serving data¶
sandmanctl postgresql://username:[email protected]/dbname
- Web admin tool
REST server (HATEOAS)
- RESTful data server
Compact, rich query language
htsql-ctl serve pgsql://:@/pgo
Visualizing data¶
matplotlib in Pandas¶
In [47]:
import matplotlib
matplotlib.use('nbagg')
In [48]:
lesleys.head()
Out[48]:
In [49]:
lesley_summary = lesleys.pivot_table('n_births',
rows='year',
cols='sex',
aggfunc=sum)
lesley_summary
Out[49]:
In [50]:
lesley_summary.plot()
Out[50]:
In [51]:
lesleys.pivot_table('n_births', rows='name', cols='sex',
aggfunc='sum')
Out[51]:
In [52]:
lesleys.pivot_table('n_births', rows='name', cols='sex',
aggfunc='sum'
).plot(kind='barh', stacked=True)
Out[52]:
In [53]:
yob2000.head()
Out[53]:
In [54]:
yob2000[yob2000.n_births > 1000].hist(bins=10)
Out[54]:
In [55]:
gsum = gender[(gender.total > 10000) & (gender.andro > -0.3)] \
.sort('andro', ascending=False)
gsum.head()
Out[55]:
In [56]:
gsum['len'] = gsum.index.map(len)
gsum.plot(kind='scatter', x='len', y='ratio',
s=gsum.total/100, alpha=0.3)
Out[56]:
mpld3¶
In [57]:
import mpld3
mpld3.enable_notebook()
gsum.plot(kind='scatter', x='len', y='ratio',
s=gsum.total/100, alpha=0.3)
Out[57]:
In [58]:
import matplotlib.pyplot as plt
fig, ax = plt.subplots()
points = ax.scatter(gsum.len, gsum.ratio,
s=gsum.total/100, alpha=0.3)
tooltip = mpld3.plugins.PointLabelTooltip(points, list(gsum.index))
mpld3.plugins.connect(fig, tooltip)
In [59]:
yob2000_common = yob2000[yob2000.n_births > 5000]
hacknied_gender_color_map = {'F': 'red', 'M': 'blue'}
hgcm = hacknied_gender_color_map
In [60]:
yob2000_common.sex.map(hgcm)
Out[60]:
In [61]:
import bokeh.plotting as bp
bp.scatter(yob2000_common.name.str.len(), yob2000_common.n_births,
color=yob2000_common.sex.map(hgcm),
fill_alpha=0.2, size=10)
Out[61]:
In [62]:
bp.output_notebook()