转载ben老师第三讲答案，膜拜的同时分享给大家

import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

import matplotlib

import seaborn as sns

import os

os.chdir(r'C:\Users\知行合一\Documents\ben\HW5')

In [2]:

import sqlite3

In [3]:

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

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

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

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

card.to_sql('card',con)

disp.to_sql('disp',con)

clients.to_sql('clients',con)



car_sql='''

select a.*,c.sex,c.birth_date,c.district_id

from card as a

left join disp as b on a.disp_id=b.disp_id

left join clients as c on b.client_id=c.client_id

where b.type='所有者'

'''

card_t=pd.read_sql(car_sql,con)

In [4]:

from pylab import mpl

mpl.rcParams['font.sans-serif']=['SimHei']

mpl.rcParams['axes.unicode_minus']=False

from datetime import *

card_t['issued_date']=pd.to_datetime(card_t['issued'])

card_t['issued_year']=card_t['issued_date'].map(lambda x:x.year)

card_t.card_id.groupby(card_t['issued_year']).count().plot(kind='bar')

Out[4]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017b731240>

In [5]:

card_t.type.value_counts().plot(kind='pie',autopct='%.1f%%')

Out[5]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017b7587f0>

In [6]:

bar_year= card_t.type.value_counts().plot(kind='bar')

In [7]:

pd.crosstab(card_t.issued_year,card_t.type).plot(kind='bar')

Out[7]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017b8e9668>

In [8]:

#del t1

t1=pd.crosstab(card_t.issued_year,card_t.type)

t1

Out[8]:

type普通卡金卡青年卡
issued_year
1993100
19941704
199542417
199683528
19971902626
19983265370

In [9]:

#t1.drop('sum1',axis=0)

t1['sum1']=t1.sum(1)

t1

Out[9]:

type普通卡金卡青年卡sum1
issued_year
19931001
1994170421
19954241763
199683528116
19971902626242
19983265370449

In [10]:

t2=t1.div(t1.sum1,axis=0)

In [11]:

t2

Out[11]:

type普通卡金卡青年卡sum1
issued_year
19931.0000000.0000000.0000001.0
19940.8095240.0000000.1904761.0
19950.6666670.0634920.2698411.0
19960.7155170.0431030.2413791.0
19970.7851240.1074380.1074381.0
19980.7260580.1180400.1559021.0

In [12]:

t2.drop('sum1',1).plot(kind='bar',stacked=True)

Out[12]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017b71e908>

In [13]:

import matplotlib.pyplot as plt 

labels=['青年卡','普通卡','金卡']

y1=t1.loc[:,'青年卡'].astype('int')

y2=t1.loc[:,'普通卡'].astype('int')

y3=t1.loc[:,'金卡'].astype('int')

x=t1.index.astype('int')

plt.stackplot(x,y1,y2,y3,labels=labels)

plt.title('发卡趋势')

plt.ylabel('发卡量')

plt.legend(loc='upper left')

plt.show()

In [14]:

##3、不同持卡人的性别比对

sub_sch=pd.crosstab(card_t.type,card_t.sex)

sub_sch.div(sub_sch.sum(1),axis=0).plot(kind='bar',stacked=True)

Out[14]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017ba02dd8>

In [15]:

from stack2dim import *

In [16]:

stack2dim(card_t,'type','sex')

In [17]:

##不同类型卡的持卡人在办卡时的平均年龄对比

import seaborn as sns

import time

card_t['age']=(pd.to_datetime(card_t['issued'])-pd.to_datetime(card_t['birth_date']))

card_t['age1'] =card_t['age'].map(lambda x:x.days/365)  

sns.boxplot(x='type',y='age1',data=card_t)

Out[17]:

<matplotlib.axes._subplots.AxesSubplot at 0x2017be5dbe0>

In [18]:

#不同类型卡的持卡人在办卡前一年内的平均账户余额对比

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

trans.to_sql('trans',con)

card_t.to_sql('card_t',con)

E:\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py:2728: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.

  interactivity=interactivity, compiler=compiler, result=result)

E:\Anaconda3\lib\site-packages\pandas\io\sql.py:450: UserWarning: the 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.

  chunksize=chunksize, dtype=dtype)

In [20]:

car_sql='''

select a.card_id,a.issued,a.type,c.type as t_type,c.amount,c.balance,c.date as t_date

from card as a

left join disp as b on a.disp_id=b.disp_id

left join trans as c on b.account_id=c.account_id

where b.type='所有者'

order by a.card_id,c.date

'''

card_t2=pd.read_sql(car_sql,con)

card_t2['issued']=pd.to_datetime(card_t2['issued'])

card_t2['t_date']=pd.to_datetime(card_t2['t_date'])

In [21]:

card_t2.head()

Out[21]:

card_idissuedtypet_typeamountbalancet_date
011998-10-16金卡贷$900$9001996-11-24
111998-10-16金卡贷$33975$348751996-12-07
211998-10-16金卡借$6,600$282751996-12-24
311998-10-16金卡贷$30$283051996-12-31
411998-10-16金卡贷$22650$509551997-01-07

将对账户余额进行清洗

贷：收入 借：支出

In [30]:

import datetime

card_t2['balance2']=card_t2['balance'].map(

    lambda x:int(''.join(x[1:].split(','))))

card_t2['amount2']=card_t2['amount'].map(

    lambda x:int(''.join(x[1:].split(','))))

In [31]:

card_t2.head()

Out[31]:

card_idissuedtypet_typeamountbalancet_datebalance2amount2
011998-10-16金卡贷$900$9001996-11-24900900
111998-10-16金卡贷$33975$348751996-12-073487533975
211998-10-16金卡借$6,600$282751996-12-24282756600
311998-10-16金卡贷$30$283051996-12-312830530
411998-10-16金卡贷$22650$509551997-01-075095522650

In [27]:

int(''.join('$6,600	'.split(','))[1:])

Out[27]:

6600

In [25]:

''.join('$22650'.split(','))[1:]

Out[25]:

'22650'

In [32]:

card_t3=card_t2[card_t2.issued>card_t2.t_date][card_t2.issued<card_t2.t_date+datetime.timedelta(days=365)]

E:\Anaconda3\lib\site-packages\ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.

  """Entry point for launching an IPython kernel.

In [34]:

card_t3.head()

Out[34]:

card_idissuedtypet_typeamountbalancet_datebalance2amount2
4711998-10-16金卡借$13600$579501997-10-205795013600
4811998-10-16金卡借$15$581971997-10-315819715
4911998-10-16金卡贷$262$582121997-10-3158212262
5011998-10-16金卡借$4,880$533171997-11-06533174880
5111998-10-16金卡贷$22650$759671997-11-077596722650

In [35]:

card_t3['avg_balance']=card_t3.groupby('card_id')['balance2'].mean()

In [36]:

card_t3.head()

Out[36]:

card_idissuedtypet_typeamountbalancet_datebalance2amount2avg_balance
4711998-10-16金卡借$13600$579501997-10-20579501360046305.628571
4811998-10-16金卡借$15$581971997-10-31581971551642.731959
4911998-10-16金卡贷$262$582121997-10-315821226256695.918033
5011998-10-16金卡借$4,880$533171997-11-0653317488056870.450000
5111998-10-16金卡贷$22650$759671997-11-07759672265055621.573333

In [37]:

card_t4=card_t3.groupby(['type','card_id'])['balance2'].agg([('avg_balance','mean')])

card_t4.to_sql('card_t4',con)

In [39]:

card_t4.head()

Out[39]:

avg_balance
typecard_id
普通卡238898.610169
454222.060976
746888.702381
955854.223881
1051387.500000

In [40]:

card_t5=card_t4.reset_index()

In [41]:

card_t5.head()

Out[41]:

typecard_idavg_balance
0普通卡238898.610169
1普通卡454222.060976
2普通卡746888.702381
3普通卡955854.223881
4普通卡1051387.500000

In [42]:

sns.boxplot(x='type',y='avg_balance',data=card_t5)

Out[42]:

<matplotlib.axes._subplots.AxesSubplot at 0x201072302e8>

In [43]:

#不同类型卡的持卡人在办卡前一年的平均收入对比

In [44]:

type_dict={'借':'out','贷':'income'}

card_t3['type1']=card_t3.t_type.map(type_dict)

card_t6=card_t3.groupby(['type','card_id','type1'])[['amount2']].sum()

card_t6.to_sql('card_t6',con)

In [45]:

card_t6.head()

Out[45]:

amount2
typecard_idtype1
普通卡2income193911
out196384
4income474142
out357224
7income299797

In [46]:

card_t7=card_t6.reset_index()

In [47]:

card_t7

Out[47]:

typecard_idtype1amount2
0普通卡2income193911
1普通卡2out196384
2普通卡4income474142
3普通卡4out357224
4普通卡7income299797
5普通卡7out307044
6普通卡9income322548
7普通卡9out288021
8普通卡10income644560
9普通卡10out521400
10普通卡13income174452
11普通卡13out119009
12普通卡14income289236
13普通卡14out230090
14普通卡15income610564
15普通卡15out506536
16普通卡16income235204
17普通卡16out225563
18普通卡17income188542
19普通卡17out144615
20普通卡18income179291
21普通卡18out143235
22普通卡19income209243
23普通卡19out208616
24普通卡20income72554
25普通卡20out64080
26普通卡21income307196
27普通卡21out303872
28普通卡22income109753
29普通卡22out93090
...............
1754青年卡945income183966
1755青年卡945out112415
1756青年卡946income385755
1757青年卡946out263605
1758青年卡957income678272
1759青年卡957out552070
1760青年卡991income328652
1761青年卡991out315048
1762青年卡1038income659194
1763青年卡1038out454030
1764青年卡1040income610964
1765青年卡1040out374431
1766青年卡1043income588993
1767青年卡1043out522650
1768青年卡1070income303774
1769青年卡1070out148680
1770青年卡1100income624295
1771青年卡1100out541092
1772青年卡1115income618386
1773青年卡1115out573734
1774青年卡1139income296619
1775青年卡1139out274992
1776青年卡1194income509514
1777青年卡1194out366093
1778青年卡1217income574306
1779青年卡1217out588180
1780青年卡1223income658180
1781青年卡1223out423068
1782青年卡1239income517440
1783青年卡1239out516292

1784 rows × 4 columns

In [48]:

card_t7.to_sql('card_t7',con)

In [49]:

card_t8=pd.read_sql('select * from card_t7 where type1="income"',con)

In [50]:

card_t8.head()

Out[50]:

indextypecard_idtype1amount2
00普通卡2income193911
12普通卡4income474142
24普通卡7income299797
36普通卡9income322548
48普通卡10income644560

In [51]:

sns.boxplot(x='type',y='amount2',data=card_t8)

Out[51]:

<matplotlib.axes._subplots.AxesSubplot at 0x20114ecca58>

In [52]:

card_t9=pd.read_sql('select * from card_t7 where type1="out"',con)

In [53]:

sns.boxplot(x='type',y='amount2',data=card_t9)

Out[53]:

<matplotlib.axes._subplots.AxesSubplot at 0x20117f083c8>