第三章-2

# -*- coding: utf-8 -*-

"""

Created on Mon Jul  9 09:53:03 2018

@author: shichen

"""

# In[]:

import pandas as pd

import numpy as np

import matplotlib

import matplotlib.pyplot as plt

import seaborn as sns

import os

os.chdir(r"E:\280_Ben_八大直播八大案例配套课件\第三章\HW5")

import sqlite3

con = sqlite3.connect(':memory:') 

from stack2dim import *

# In[]:

card=pd.read_csv("card.csv",encoding='gbk')

clients=pd.read_csv("clients.csv",encoding='gbk')

disp=pd.read_csv("disp.csv",encoding='gbk')

card.to_sql('card',con)

clients.to_sql('clients',con)

disp.to_sql('disp',con)

# In[]:

f_table=pd.read_sql_query(r"select c.type,issued,d.disp_id,d.account_id,cl.sex,cl.birth_date,cl.district_id from (card c left join disp d on c.disp_id=d.disp_id) left join clients cl on d.client_id=cl.client_id",con)

t=pd.read_sql_query("select * from disp",con)

# In[]:

stack2dim(f_table,"type","sex")

f_table["apply_age"]=(pd.to_datetime(f_table['issued'])-pd.to_datetime(f_table['birth_date']))

f_table["apply_age2"]=f_table["apply_age"].map(lambda x:x.days/365)

sns.boxplot(x="type",y="apply_age2",data=f_table)

# In[]:

trans=pd.read_csv("trans.csv",encoding='gbk')

f_table.to_sql("f_table",con)

trans.to_sql("trans",con)

f_trans_sql="select a.*,b.* from f_table a left join trans b on a.account_id=b.account_id"

f_table_trans=pd.read_sql(f_trans_sql,con)

# In[]:

# In[]:

# In[]: