%matplotlib inline

import pandas as pd
import sqlite3
import numpy as np 
import matplotlib.pyplot as plt

c = sqlite3.connect("foodmart.db")

with c:
    query = "SELECT * FROM product"
    product_frame = pd.io.sql.read_sql(query, c)
    print product_frame.shape

product_frame[:3]

with c:
    query = "SELECT * FROM product_class"
    product_class_frame = pd.io.sql.read_sql(query, c)
    print product_class_frame.shape

product_class_frame[:30]

with c:
    query = "SELECT * FROM sales_fact_1998"
    sales_frame = pd.io.sql.read_sql(query, c)
    print sales_frame.shape

sales_frame[:3]

with c:
    query = "SELECT * FROM time_by_day"
    time_frame = pd.io.sql.read_sql(query, c)
    print time_frame.shape

time_frame[:3]

with c:
    query = "SELECT * FROM customer"
    customer_frame = pd.io.sql.read_sql(query, c)
    print customer_frame.shape

customer_frame[:3]

with c:
    query = "SELECT * FROM employee"
    employee_frame = pd.io.sql.read_sql(query, c)
    print employee_frame.shape

employee_frame[:3]

employee_frame.loc[:3,['employee_id','full_name']]

employee_frame.iloc[:3,[0,1]]

with c:
    query = "SELECT * FROM salary"
    salary_frame = pd.io.sql.read_sql(query, c)
    print salary_frame.shape

salary_frame[1301:1304]

with c:
    query = "SELECT * FROM region"
    region_frame = pd.io.sql.read_sql(query, c)
    print region_frame.shape

region_frame[:3]

with c:
    query = "SELECT * FROM store"
    store_frame = pd.io.sql.read_sql(query, c)
    print store_frame.shape

store_frame[:3]

employee_frame[employee_frame.salary > 3000].loc[:,['employee_id','full_name','salary']]

jointure_employee_store = store_frame.merge(employee_frame, on='store_id')
jointure_employee_store[ jointure_employee_store.store_state=='OR'].loc[:,['employee_id','full_name','salary']]

grouped = store_frame.groupby(['store_country','store_state'])
grouped.store_id.count()













jointure_sales_time_place = sales_frame.merge(time_frame, on='time_id').merge(store_frame, on='store_id')

T = pd.pivot_table(jointure_sales_time_place, values = 'store_sales', index = ['store_country','store_state','store_city'], columns = ['quarter','the_month'], aggfunc=np.sum)

T

T.iloc[1,:].plot(kind='bar')