pip install ipython-sql
for sql magic
%load_ext sql
%%bash
sqlite3 enron.db .tables
We only care about the *Base tables:
- EmployeeBase
- MessageBase
- RecipientBase
(The other tables are derived from those. We don't need them for this exercise.)
%%sql sqlite:///enron.db
SELECT * FROM EmployeeBase LIMIT 5
%%sql sqlite:///enron.db
SELECT * FROM MessageBase LIMIT 5
from_eid looks like a foreign key
%%sql sqlite:///enron.db
SELECT unix_time, name, department, title, gender, seniority, subject, filename
FROM EmployeeBase JOIN MessageBase ON eid = from_eid LIMIT 5
Finally, RecipientBase:
%%sql sqlite:///enron.db
SELECT * FROM RecipientBase LIMIT 9
It looks like we can use this to determine how many recipients each message received
i.e. MAX(rno)
Who sent the email to the most recipients?
%%sql sqlite:///enron.db
SELECT name, title, MAX(rno)
FROM EmployeeBase
JOIN MessageBase ON eid = from_eid
JOIN RecipientBase USING(mid)
Who are the top five senders of mass emails?
%%sql sqlite:///enron.db
SELECT name, title, MAX(rno) AS recipient_count
FROM EmployeeBase
JOIN MessageBase ON eid = from_eid
JOIN RecipientBase USING(mid)
GROUP BY name, title ORDER BY recipient_count DESC LIMIT 5
Pandas Data Frames¶
import pandas as pd
import sqlite3
from pandas.io import sql
conn = sqlite3.connect('enron.db')
sql.frame_query("""SELECT mid, unix_time, subject, from_eid
FROM MessageBase LIMIT 5""", conn, "mid")
MessageBase = sql.frame_query("""SELECT mid, unix_time, subject, from_eid
FROM MessageBase""", conn, "mid")
MessageBase.head()
SQL JOIN (with USING):
sql.frame_query("""SELECT mid, unix_time, subject, from_eid, rno, to_eid
FROM MessageBase JOIN RecipientBase USING (mid) LIMIT 5""", conn, "mid")
pandas.DataFrame.join
RecipientBase = sql.frame_query("SELECT * FROM RecipientBase", conn, "mid")
MessageBase.join(RecipientBase).head()
SQL JOIN (with ON):
sql.frame_query("""SELECT mid, name, title, seniority,
unix_time, subject, from_eid
FROM EmployeeBase
JOIN MessageBase ON eid = from_eid
LIMIT 5""", conn, "mid")
pandas.DataFrame.merge
EmployeeBase = sql.frame_query("""SELECT eid, name, title, seniority
FROM EmployeeBase""", conn, "eid")
EmployeeBase.merge(MessageBase, right_on = 'from_eid', left_index = True).head()
Histograms are a good way to get a feel for the data. For example:
MessageBase.unix_time.hist()
What does this tell us?
Convert unix_time to human readable form (i.e. datetime)
from datetime import datetime
dt = MessageBase.unix_time.map(datetime.fromtimestamp)
MessageBase.groupby(dt).unix_time.count().resample('1W', how='sum').plot()
What does this tell us?
Consider recipient counts (i.e. MAX(rno))
recipient_counts = sql.frame_query("""SELECT mid, datetime(unix_time, 'unixepoch') AS time_sent,
MAX(rno) AS recipient_count
FROM MessageBase JOIN RecipientBase USING (mid)
GROUP BY mid, time_sent""",
conn, "mid")
recipient_counts.head()
grouping objects
w = recipient_counts.time_sent.map(lambda dt: pd.Period(dt, freq='W'))
recipient_counts.groupby(w).recipient_count.mean().plot(ylim=(0,10))
Lab
1-2 Pairs
Use matplotlib.pyplot.scatter (or plt.scatter for short) to plot the length of the subjects against the recipient_count.
Individually:
Plot the median subject length over time (using the same weekly grouping object above)