solution-code-17.ipynb

Kernel: Python 2

import pandas as pd
from pandas.io import sql

Pandas can be used to connect to most relational databases. In this demonstration, we will create and connect to a SQLite database. SQLite creates portable SQL databases saved in a single file. These databases are stored in a very efficient manner and allow fast querying, making them ideal for small databases or databases that need to be moved across machines.

If you are looking to start using a database without the setup of mysql or postgres, SQLite is a good start.

import sqlite3

conn = sqlite3.connect('dat-test.db')

Let's return to the Rossmann sales data and load that into the database.

Data is moved to the database through the to_sql command, similar to the to_csv command.

to_sql takes as arugments: - name, the table name to create - con, a connection to a database - index, whether to input the index column - schema, if we want to write a custom schema for the new table - if_exists, what to do if the table already exists. We can overwrite it, add to it, or fail

data = pd.read_csv('../../../lesson-15/assets/dataset/rossmann.csv', low_memory=False)
data.head()

data.to_sql('rossmann_sales',
            con=conn,
            if_exists='replace',
            index=False)

sql.read_sql('select * from rossmann_sales limit 10', con=conn)

CHECK: Load the Rossmann Store metadata in rossmann-stores.csv and create a table into the database from it

rossmann_stores = pd.read_csv('../../assets/dataset/rossmann-stores.csv')
rossmann_stores.to_sql('rossmann_stores', if_exists='replace', index=False, con=conn)

CHECK: (SELECT) Have the students write a query that returns the Store, Date and Customers

sql.read_sql(
"""
SELECT 
Store, Date, Customers 
FROM rossmann_sales;
""", con=conn).head()

CHECK: (WHERE) Have the students write a query that returns the Store, Date and Customers for when the stores were open and running a promotion

sql.read_sql(
"""
SELECT 
Store, Date, Customers 
FROM rossmann_sales
WHERE Open = 1 and Promo = 1;
""", con=conn).head()

CHECK: (GROUP BY) Have the students write a query that returns the total sales on promotion days.

sql.read_sql(
"""
SELECT
Promo, Store, SUM(Sales)
FROM rossmann_sales
GROUP BY Promo
""", con=conn).head()

Exercises

1. Load the Walmart sales and store features data

2. Create a table for each of those datasets

3. Select the store, date and fuel price on days it was over 90 degrees

4. Select the store, date and weekly sales and temperature

5. What were average sales on holiday vs. non-holiday sales

6. What were average sales on holiday vs. non-holiday sales when the temperature was below 32 degrees

# Load the Walmart sales and store features data
walmart_sales = pd.read_csv('../../assets/dataset/walmart-sales.csv')
walmart_sales.head()

walmart_features = pd.read_csv('../../assets/dataset/features.csv')
walmart_features.head()

# Create a table for each of those datasets
walmart_sales.to_sql('walmart_sales', index=False, if_exists='replace', con = conn)
walmart_features.to_sql('walmart_features', if_exists='replace', index=False, con=conn)

# Select the store, date and fuel price on days it was over 90 degrees
query = """
SELECT 
Date, Store, Fuel_Price, Temperature
FROM walmart_features
WHERE Temperature > 90
"""
sql.read_sql(query, con = conn).head()

# Select the store, date and weekly sales and temperature
query = """
SELECT 
s.Store, s.Date, s.Weekly_Sales, f.Temperature
FROM walmart_sales as s
JOIN walmart_features as f
ON s.Store = f.Store and s.Date = f.Date
"""
sql.read_sql(query, con = conn).head()

# What were average sales on holiday vs. non-holiday sales
query = """
SELECT 
IsHoliday, AVG(Weekly_Sales)
FROM walmart_sales as s
GROUP BY IsHoliday
"""
sql.read_sql(query, con = conn)

# What were average sales on holiday vs. non-holiday sales when the temperature was below 32 degrees
query = """
SELECT 
s.IsHoliday, AVG(s.Weekly_Sales)
FROM walmart_sales as s
JOIN walmart_features as f
ON s.Store = f.Store and s.Date = f.Date
WHERE f.Temperature < 32
GROUP BY s.IsHoliday
"""
sql.read_sql(query, con = conn)