<jupyter_start><jupyter_text>Pandas, SQL, and the Grammar of Data--- Table of Contents* [Pandas, SQL, and the Grammar of Data](Pandas,-SQL,-and-the-Grammar-of-Data)	* [Populating the Database](Populating-the-Database)		* [SQLITE](SQLITE)		* [Init](Init)			* [Populating with Pandas!!](Populating-with-Pandas!!)			* [Or populate with SQL INSERT](Or-populate-with-SQL-INSERT)			* [Bulk insert](Bulk-insert)	* [Single Table Verbs](Single-Table-Verbs)		* [QUERY](QUERY)		* [SORT](SORT)		* [SELECT-COLUMNS](SELECT-COLUMNS)		* [SELECT-DISTINCT](SELECT-DISTINCT)		* [ASSIGN](ASSIGN)		* [AGGREGATE](AGGREGATE)		* [GROUP-AGG](GROUP-AGG)		* [DELETE](DELETE)		* [LIMIT](LIMIT)	* [Indexes](Indexes)	* [Relationships: JOINs are Cartesian Products.](Relationships:-JOINs-are-Cartesian-Products.)		* [Simple subselect](Simple-subselect)		* [implicit join](implicit-join)		* [Explicit INNER JOIN](Explicit-INNER-JOIN)		* [Outer JOIN](Outer-JOIN)			* [left outer (contributors on candidates)](left-outer-%28contributors-on-candidates%29)			* [right outer (contributors on candidates) = left outer (candidates on contributors)](right-outer-%28contributors-on-candidates%29-=-left-outer-%28candidates-on-contributors%29)			* [full outer](full-outer)	* [Pandas /SQL](Pandas-/SQL)	* [Useful Links](Useful-Links)<jupyter_code># The %... is an iPython thing, and is not part of the Python language.
# In this case we're just telling the plotting library to draw things on
# the notebook, instead of on a separate window.
%matplotlib inline
# See all the "as ..." contructs? They're just aliasing the package names.
# That way we can call methods like plt.plot() instead of matplotlib.pyplot.plot().
import numpy as np
import scipy as sp
import matplotlib as mpl
import matplotlib.cm as cm
import matplotlib.pyplot as plt
import pandas as pd
import time
pd.set_option('display.width', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.notebook_repr_html', True)
import seaborn as sns
sns.set_style("whitegrid")
sns.set_context("poster")<jupyter_output><empty_output><jupyter_text>Populating the Database Lets start with Relational Databases, so called because they contain "relations" (tables), which are SETS of "tuples" (rows) which map "attributes" to atomic values.The available attributes are constrained by a "header" tuple of attributes which set the type. We do this below here.<jupyter_code>ourschema="""
DROP TABLE IF EXISTS "candidates";
DROP TABLE IF EXISTS "contributors";
CREATE TABLE "candidates" (
    "id" INTEGER PRIMARY KEY  NOT NULL ,
    "first_name" VARCHAR,
    "last_name" VARCHAR,
    "middle_name" VARCHAR,
    "party" VARCHAR NOT NULL
);
CREATE TABLE "contributors" (
    "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,
    "last_name" VARCHAR,
    "first_name" VARCHAR,
    "middle_name" VARCHAR,
    "street_1" VARCHAR,
    "street_2" VARCHAR,
    "city" VARCHAR,
    "state" VARCHAR,
    "zip" VARCHAR,
    "amount" INTEGER,
    "date" DATETIME,
    "candidate_id" INTEGER NOT NULL,
    FOREIGN KEY(candidate_id) REFERENCES candidates(id)
);
"""<jupyter_output><empty_output><jupyter_text>SQLITE We use sqlite here (and recommend Postgres for production purposes). Still sqlite is great for on-disk large databases which wont fit into memory. Its also built into Python, but to use the [command line tool](https://www.sqlite.org/cli.html), I recommend you install it: https://www.sqlite.org/download.html. I also recommend you download and install the sqlite browser: http://sqlitebrowser.org .Python implements a standard database API over all databases. Its called [DBAPI2](http://cewing.github.io/training.codefellows/lectures/day21/intro_to_dbapi2.html). It works across many SQL databases.There is an even higher level API available, called [SQLAlchemy](http://www.sqlalchemy.org). While we wont use it here, I thoroughly recommend it, either in its direct relational form, or ORM form. Many things in Pandas use it to interface with databases. Here we'll get away with things by using SQLITE. ---Sqlite is a text or memory based database. Connect and get a DBAPI2 connection.<jupyter_code>from sqlite3 import dbapi2 as sq3
import os
PATHSTART="."
def get_db(dbfile):
    sqlite_db = sq3.connect(os.path.join(PATHSTART, dbfile))
    return sqlite_db<jupyter_output><empty_output><jupyter_text>Drop tables if they exist and create them.<jupyter_code>def init_db(dbfile, schema):
    """Creates the database tables."""
    db = get_db(dbfile)
    db.cursor().executescript(schema)
    db.commit()
    return db<jupyter_output><empty_output><jupyter_text>Use Pandas to read in the data<jupyter_code>dfcand=pd.read_csv("./candidates.txt", sep='|')
dfcand
dfcwci=pd.read_csv("./contributors_with_candidate_id.txt", sep="|")
dfcwci.head()
del dfcwci['id']
dfcwci.head()<jupyter_output><empty_output><jupyter_text>Init Initializing the database<jupyter_code>db=init_db("cancont.db", ourschema)<jupyter_output><empty_output><jupyter_text>Populating with Pandas!!<jupyter_code>dfcand.to_sql("candidates", db, if_exists="append", index=False)
dfcwci.to_sql("contributors", db, if_exists="append", index=False)
dfcwci.shape
sel="""
SELECT * FROM candidates;
"""
c=db.cursor().execute(sel)
c.fetchall()
rem="""
DELETE FROM candidates;
"""
c=db.cursor().execute(rem)
db.commit()
c.fetchall()<jupyter_output><empty_output><jupyter_text>Or populate with SQL INSERT ![xkcd-sqlinj](http://imgs.xkcd.com/comics/exploits_of_a_mom.png)<jupyter_code>ins="""
INSERT INTO candidates (id, first_name, last_name, middle_name, party) \
    VALUES (?,?,?,?,?);
"""
with open("candidates.txt") as fd:
    slines =[l.strip().split('|') for l in fd.readlines()]
    for line in slines[1:]:
        theid, first_name, last_name, middle_name, party = line
        print theid, first_name, last_name, middle_name, party
        valstoinsert = (int(theid), first_name, last_name, middle_name, party)
        print ins, valstoinsert
        db.cursor().execute(ins, valstoinsert)
        
    
def make_query(sel):
    c=db.cursor().execute(sel)
    return c.fetchall()
make_query("SELECT * FROM candidates;")
rem="""
DELETE FROM candidates;
"""
c=db.cursor().execute(rem)
db.commit()<jupyter_output><empty_output><jupyter_text>Bulk insert You almost always have to do this from the command line. Its typically faster, but also different foe every database<jupyter_code>%%bash
tail -n +2 candidates.txt > candidates_nohead.txt
echo ".import candidates_nohead.txt candidates" | sqlite3 cancont.db
make_query("SELECT * FROM candidates;")<jupyter_output><empty_output><jupyter_text>Single Table Verbs Let us now focus on core data manipulation commands. The reason to do this is that they are *universal across systems, and by identifying them, we can quickly ask how to do these* when we encounter a new system. See https://gist.github.com/TomAugspurger/6e052140eaa5fdb6e8c0/ which has a comparison of r/dplyr and pandas. I stole and modified this table from there:``dplyr`` has a small set of nicely defined verbs. I've listed their closest pandas verbs.      VERB    dplyr    pandas    SQL        QUERY/SELECTION    filter() (and slice())    query() (and loc[], iloc[])    SELECT WHERE        SORT    arrange()    sort()    ORDER BY        SELECT-COLUMNS/PROJECTION    select() (and rename())    [](__getitem__) (and rename())    SELECT COLUMN        SELECT-DISTINCT    distinct()    unique(),drop_duplicates()    SELECT DISTINCT COLUMN        ASSIGN    mutate() (and transmute())    assign    ALTER/UPDATE        AGGREGATE    summarise()    describe(), mean(), max()    None, AVG(),MAX()        SAMPLE    sample_n() and sample_frac()    sample()    implementation dep, use RAND()        GROUP-AGG    group_by/summarize    groupby/agg, count, mean    GROUP BY        DELETE    ?    drop/masking    DELETE/WHERE<jupyter_code>dfcwci.head()<jupyter_output><empty_output><jupyter_text>QUERY<jupyter_code>dfcwci.query("state=='VA' & amount < 400")
dfcwci[(dfcwci.state=='VA') & (dfcwci.amount < 400)]
cont_cols = [e[1] for e in make_query("PRAGMA table_info(contributors);")]
cont_cols
def make_frame(list_of_tuples, legend=cont_cols):
    framelist=[]
    for i, cname in enumerate(legend):
        framelist.append((cname,[e[i] for e in list_of_tuples]))
    return pd.DataFrame.from_items(framelist)
out=make_query("SELECT * FROM contributors WHERE state='VA' AND amount < 400;")
print out
make_frame(out)
out=make_query("SELECT * FROM contributors WHERE state IS NULL;")
make_frame(out)
dfcwci[dfcwci.state.isnull()]
out=make_query("SELECT * FROM contributors WHERE state IS NOT NULL;")
make_frame(out).shape
dfcwci[dfcwci.state.notnull()].shape
out=make_query("SELECT * FROM contributors WHERE state IN ('VA','WA');")
make_frame(out).head(10)
dfcwci[dfcwci.state.isin(['VA','WA'])].head(10)
out=make_query("SELECT * FROM contributors WHERE amount BETWEEN 10 AND 50;")
make_frame(out).head(10)
dfcwci.query("10 <= amount <= 50").head(10)<jupyter_output><empty_output><jupyter_text>SORT<jupyter_code>dfcwci.sort("amount").head(10)
dfcwci.sort("amount", ascending=False).head(10)
out=make_query("SELECT * FROM contributors ORDER BY amount;")
make_frame(out).head(10)
out=make_query("SELECT * FROM contributors ORDER BY amount DESC;")
make_frame(out).head(10)<jupyter_output><empty_output><jupyter_text>SELECT-COLUMNS<jupyter_code>dfcwci[['first_name', 'amount']].head(10)
out=make_query("SELECT first_name, amount FROM contributors;")
make_frame(out,['first_name', 'amount']).head(10)<jupyter_output><empty_output><jupyter_text>SELECT-DISTINCT<jupyter_code>dfcwci[['last_name','first_name']].count()
dfcwci[['last_name','first_name']].drop_duplicates().count()
dfcwci[['last_name','first_name']].drop_duplicates().head(10)
out=make_query("SELECT DISTINCT last_name, first_name FROM contributors;")
make_frame(out,['last_name', 'first_name']).head(10)<jupyter_output><empty_output><jupyter_text>ASSIGN<jupyter_code>dfcwci['name']=dfcwci['last_name']+", "+dfcwci['first_name']
dfcwci.head(10)
dfcwci.assign(ucname=dfcwci.last_name+":"+dfcwci.first_name).head(10)<jupyter_output><empty_output><jupyter_text>Will the above command actually change `dfcwci`?What if we wanted to change an existing assignment?<jupyter_code>dfcwci[dfcwci.state=='VA']
dfcwci.loc[dfcwci.state=='VA', 'name']
dfcwci.loc[dfcwci.state=='VA', 'name']="junk"
dfcwci.query("state=='VA'")<jupyter_output><empty_output><jupyter_text>---Let us see the entire process in SQL<jupyter_code>alt="ALTER TABLE contributors ADD COLUMN name;"
db.cursor().execute(alt)
make_query("PRAGMA table_info(contributors);")
out = make_query("SELECT id, last_name,first_name from contributors;")
out2 = [(e[1]+", "+e[2],e[0]) for e in out]
out2
alt2="UPDATE contributors SET name = ? WHERE id = ?;"
for ele in out2:
    db.cursor().execute(alt2, ele)
db.commit()
out=make_query("SELECT * from contributors;")
make_frame(out,cont_cols+["name"]).head(10)<jupyter_output><empty_output><jupyter_text>And lets now do an assignment to an existing column<jupyter_code>upd="UPDATE contributors SET name = 'junk' WHERE state = 'VA';"
db.cursor().execute(upd)
db.commit()
out=make_query("SELECT * from contributors where state='VA';")
make_frame(out,cont_cols+["name"]).head(10)<jupyter_output><empty_output><jupyter_text>No DROP COLUMN in SQLITEIts available in other databases. Here you must just re-create your database, or no about this gotcha from the start.<jupyter_code>alt="ALTER TABLE contributors DROP COLUMN name;"
db.cursor().execute(alt)
db.commit()<jupyter_output><empty_output><jupyter_text>Its much simpler in Pandas, of-course<jupyter_code>del dfcwci['name']<jupyter_output><empty_output><jupyter_text>AGGREGATE<jupyter_code>dfcwci.describe()
dfcwci.amount.max()
dfcwci[dfcwci.amount==dfcwci.amount.max()]
out=make_query("SELECT *, MAX(amount) AS maxamt FROM contributors;")
print out
make_frame(out, cont_cols+['maxamt'])
out=make_query("SELECT COUNT(amount) AS AMOUNTCOUNT FROM contributors;")
print out
out=make_query("SELECT AVG(amount) FROM contributors;")
print out
dfcwci[dfcwci.amount > dfcwci.amount.max() - 2300]
out=make_query("SELECT * FROM contributors WHERE amount > (select (MAX(amount) - 2300) FROM contributors);")
make_frame(out)<jupyter_output><empty_output><jupyter_text>Aso `MIN`, `SUM`, `AVG`. GROUP-AGG<jupyter_code>dfcwci.groupby("state").sum()
dfcwci.groupby("state")['amount'].mean()
dfcwci.state.unique()
out=make_query("SELECT state,SUM(amount) FROM contributors GROUP BY state;")
make_frame(out, legend=['state','sum'])<jupyter_output><empty_output><jupyter_text>DELETE<jupyter_code>dfcwci.head()<jupyter_output><empty_output><jupyter_text>In-place drops<jupyter_code>df2=dfcwci.copy()
df2.set_index('last_name', inplace=True)
df2.head()
df2.drop(['Ahrens'], inplace=True)
df2.head()
df2.reset_index(inplace=True)
df2.head()<jupyter_output><empty_output><jupyter_text>The recommended way to do it is to create a new dataframe. This might be impractical is things are very large.<jupyter_code>dfcwci=dfcwci[dfcwci.last_name!='Ahrens']
dfcwci.head(10)
drow="DELETE FROM contributors WHERE last_name=\"Ahrens\";"
db.cursor().execute(drow)
db.commit()
out=make_query("SELECT * FROM contributors;")
make_frame(out).head(10)<jupyter_output><empty_output><jupyter_text>LIMIT<jupyter_code>out=make_query("SELECT * FROM contributors LIMIT 3;")
make_frame(out).head(10)
dfcwci[0:3]<jupyter_output><empty_output><jupyter_text>Indexes<jupyter_code>crind="CREATE INDEX amount_ix ON contributors(amount);"
db.cursor().execute(crind)
db.commit()
%%bash
echo ".schema" | sqlite3 cancont.db
crind="DROP INDEX amount_ix;"
db.cursor().execute(crind)
db.commit()
%%bash
echo ".schema" | sqlite3 cancont.db<jupyter_output>CREATE TABLE "candidates" (
    "id" INTEGER PRIMARY KEY  NOT NULL ,
    "first_name" VARCHAR,
    "last_name" VARCHAR,
    "middle_name" VARCHAR,
    "party" VARCHAR NOT NULL
);
CREATE TABLE "contributors" (
    "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,
    "last_name" VARCHAR,
    "first_name" VARCHAR,
    "middle_name" VARCHAR,
    "street_1" VARCHAR,
    "street_2" VARCHAR,
    "city" VARCHAR,
    "state" VARCHAR,
    "zip" VARCHAR,
    "amount" INTEGER,
    "date" DATETIME,
    "candidate_id" INTEGER NOT NULL, name,
    FOREIGN KEY(candidate_id) REFERENCES candidates(id)
);<jupyter_text>Relationships: JOINs are Cartesian Products. Simple subselect<jupyter_code>dfcand.head()
obamaid=dfcand.query("last_name=='Obama'")['id'].values[0]
obamacontrib=dfcwci.query("candidate_id==%i" % obamaid)
obamacontrib.head()
russiandollsel="""
SELECT * FROM contributors WHERE 
    candidate_id = (SELECT id from candidates WHERE last_name = 'Obama');
"""
out=make_query(russiandollsel)
make_frame(out).head()<jupyter_output><empty_output><jupyter_text>implicit join<jupyter_code>implicitjoinsel="""
SELECT 
    contributors.last_name, contributors.first_name, contributors.amount, candidates.last_name 
FROM 
    contributors, candidates 
WHERE contributors.candidate_id = candidates.id
AND candidates.last_name = 'Obama';
"""
out=make_query(implicitjoinsel)
make_frame(out, legend=["contributors.last_name", 
            "contributors.first_name", "contributors.amount", "candidates.last_name"]).head()<jupyter_output><empty_output><jupyter_text>Let's expand to not just include Obama<jupyter_code>implicitjoinsel="""
SELECT 
    contributors.last_name, contributors.first_name, contributors.amount, candidates.last_name 
FROM 
    contributors, candidates 
WHERE contributors.candidate_id = candidates.id;
"""
out=make_query(implicitjoinsel)
make_frame(out, legend=["contributors.last_name", 
            "contributors.first_name", "contributors.amount", "candidates.last_name"]).head()<jupyter_output><empty_output><jupyter_text>Explicit INNER JOIN ![inner join](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key_inner.png)(from http://pandas.pydata.org/pandas-docs/stable/merging.html)<jupyter_code>cols_wanted=['last_name_x', 'first_name_x', 'candidate_id', 'id', 'last_name_y']
dfcwci.merge(dfcand, left_on="candidate_id", right_on="id")[cols_wanted]
explicitjoinsel="""
SELECT 
    contributors.last_name, contributors.first_name, candidates.last_name 
FROM 
    contributors JOIN candidates 
ON contributors.candidate_id = candidates.id;
"""
out=make_query(explicitjoinsel)
make_frame(out, legend=["contributors.last_name", 
            "contributors.first_name",  "candidates.last_name"]).head()
explicitjoinsel="""
SELECT 
    COUNT(contributors.id), contributors.first_name, candidates.last_name 
FROM 
    contributors JOIN candidates 
ON contributors.candidate_id = candidates.id

GROUP BY candidates.last_name;
"""
out=make_query(explicitjoinsel)
make_frame(out, legend=["count(contributors.id)", 
            "contributors.first_name",  "candidates.last_name"])
len(make_query("SELECT DISTINCT id, last_name FROM candidates;"))<jupyter_output><empty_output><jupyter_text>Outer JOIN left outer (contributors on candidates) ![left outer](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key_left.png)<jupyter_code>dfcwci.merge(dfcand, left_on="candidate_id", right_on="id", how="left")[cols_wanted]
explicitjoinsel="""
SELECT 
    COUNT(contributors.id), contributors.first_name, candidates.last_name,
        contributors.candidate_id, candidates.id
FROM 
    contributors LEFT OUTER JOIN candidates 
ON contributors.candidate_id = candidates.id

GROUP BY candidates.last_name;
"""
out=make_query(explicitjoinsel)
make_frame(out, legend=["count(contributors.id)", "contributors.first_name",  
            "contributors.candidate_id", "candidates.id", "candidates.last_name"])<jupyter_output><empty_output><jupyter_text>right outer (contributors on candidates) = left outer (candidates on contributors) We do it thus as sqlite has no support for right outer or plain outer. If it did we could write:```sqlSELECT     COUNT(contributors.id), contributors.first_name, candidates.last_name FROM     contributors RIGHT OUTER JOIN candidates ON contributors.candidate_id = candidates.idGROUP BY candidates.last_name;``` ![right outer](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key_right.png)<jupyter_code>dfcwci.merge(dfcand, left_on="candidate_id", right_on="id", how="right")[cols_wanted]
explicitjoinsel="""
SELECT 
    COUNT(contributors.id), contributors.first_name, candidates.last_name, 
        contributors.candidate_id, candidates.id
FROM 
    candidates LEFT OUTER JOIN contributors 
ON contributors.candidate_id = candidates.id

GROUP BY candidates.last_name;
"""
out=make_query(explicitjoinsel)
make_frame(out, legend=["count(contributors.id)", "contributors.first_name",  
                    "contributors.candidate_id", "candidates.id", "candidates.last_name"])<jupyter_output><empty_output><jupyter_text>full outer also not supported by sqlite```sqlSELECT     COUNT(contributors.id), contributors.first_name, candidates.last_name FROM     contributors FULL OUTER JOIN candidates ON contributors.candidate_id = candidates.idGROUP BY candidates.last_name;``` ![outer](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key_outer.png)<jupyter_code>dfcwci.merge(dfcand, left_on="candidate_id", right_on="id", how="outer")[cols_wanted]<jupyter_output><empty_output><jupyter_text>When to use which?See this:http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ Pandas /SQL<jupyter_code>pd.read_sql("SELECT * FROM candidates WHERE party= 'D';", db)
pd.read_sql(implicitjoinsel, db)<jupyter_output><empty_output><jupyter_text>This is very useful if the database is big and out of memory. Sqlite3 is the only db2api database supported. For any other database you should use `SQLAlchemy`. See, for eg: https://plot.ly/ipython-notebooks/big-data-analytics-with-pandas-and-sqlite/<jupyter_code>db.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13215348157111884, 'fraction_numerical': 0.0066555740432612314, 'mean_word_length': 6.243973703433163, 'pattern_counts': {'":': 1, '<': 149, '<?xml version=': 0, '>': 147, 'https://': 4, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '547012', 'license': 'MIT', 'n_tokens_mistral': 7103, 'n_tokens_neox': 6762, 'n_words': 1464, 'path': 'Lectures/Lecture4/PandasAndSQL.ipynb', 'repo_name': 'eltziaf/Testing', 'size': '19833'}