HW5

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

"""

Created on Wed May 30 15:16:29 2018



@author: Rachel

"""



# In[1]:

import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

import seaborn as sns

import os



# In[2]:

os.chdir(r'E:\Python_learning\data_science\task_0529\HW5')

loanfile = os.listdir()

createVar = locals()

for i in loanfile:

    if i.endswith("csv"):        

        createVar[i.split('.')[0]] = pd.read_csv(i, encoding = 'gbk')

        print(i.split('.')[0])



# In[]:

data2 = pd.merge(clients, disp, on = 'client_id', how = 'left')

data2 = pd.merge(data2, card, on = 'disp_id', how = 'left')

#ssex = {"女":"femal","男":"male"}

#data2["ssex"] = data2.sex.map(ssex)



data2.head()

# In[]:

data2.columns = ['client_id','sex','birth_date','district_id','disp_id','account_id','user_type','card_id','issued','card_type']

data2.head()



# In[]:

from pylab import mpl

mpl.rcParams['font.sans-serif'] = ['SimHei'] # 指定默认字体

mpl.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题

d1 = pd.crosstab(data2.card_type,data2.sex)

d1.plot(kind = 'bar',stacked=True)



# In[]:

d1["sum"] = d1.sum(1)

d1.head()



# In[]:

d1 = d1.div(d1.sum(1),axis = 0)

d1.head()



# In[]:

#不同类型卡的持卡人的性别对比

d1[["女","男"]].plot(kind = "bar",stacked = True)



# In[]:

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

data2["birth_date"] = pd.to_datetime(data2["birth_date"])

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

data2.head()



# In[]:

import datetime

data2["age"] = data2["issued"].dt.year -data2["birth_date"].dt.year

data2 = data2[pd.notnull(data2['age'])]

data2.tail()





# In[]:

d2 = data2.groupby("card_type")["age"].mean()

d2.head()



# In[]:

sns.boxplot(x = 'card_type', y = 'age', data = data2)

# In[]:

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

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

data3= pd.merge(loans[['account_id', 'date']],

                trans[['account_id','type','amount','balance','date']],

                on = 'account_id') 

data3 = pd.merge(data2[['account_id','card_type']],

                 data3,on='account_id')

data3.head()

# In[]:

data3.columns = ['account_id', 'card_type','date', 'type', 'amount', 'balance', 't_date']

data3.head()



# In[]:

data3 = data3.sort_values(by = ['account_id','t_date'])

data3.head()



# In[]:

data3['date']=pd.to_datetime(data3['date'])

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

data3.head()

# In[]:

data3['balance2'] = data3['balance'].map(lambda x: int(''.join(x[1:].split(','))))

data3['amount2'] = data3['amount'].map(lambda x: int(''.join(x[1:].split(','))))



data3.head()

# In[]:



data4 = data3[data3.date > data3.t_date][data3.date < data3.t_date + datetime.timedelta(days=365)]

data4.head()

# In[]:

d4= data4.groupby('card_type')['balance2'].mean() 

d4.head()



# In[]:

sns.boxplot(x='card_type', y = 'balance2',data = data4)



# In[]:

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

data4['type1'] = data4.type.map(type_dict)

data4.head()



# In[]:

data4 = data4.groupby(['card_type','type1'])[['amount2']].sum()

data4.head()



# In[]:

data5 = pd.pivot_table(data4, values = 'amount2', index = 'card_type', columns = 'type1')



data5.fillna(0, inplace = True)



data5.head()



# In[]:

data5['in'] = data5['income']-data5['out']

data5

1、不同类型卡的持卡人年龄对比

2、不同持卡人在办卡时的平均年龄对比

card_type

普通卡    42.000000

金卡     42.988636

青年卡    18.110345

Name: age, dtype: float64

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

card_type

普通卡    47086.267095

金卡     52628.433873

青年卡    48431.566944

Name: balance2, dtype: float64

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

type1        income       out        in

card_type                              

普通卡        51518571  39264210  12254361

金卡          6635783   5227548   1408235

青年卡         9991911   8074408   1917503