1. AI技术聚合首页
  2. Python

2022第五届“泰迪杯”数据分析技能赛-B题-银行客户忠诚度分析（上）

社会演员多 • • Python • 阅读 94

新手参加比赛，不足之处敬请谅解

题目链接：链接:https://pan.baidu.com/s/1x1U-kobvPjNMm8xnvS9Gdg 
提取码:7id3

目录

任务1 数据探索与清洗

任务1.1 数据探索与预处理

任务1.2 特征编码

任务2 产品营销数据可视化分析

任务2.1 

任务2.2

任务2.3

任务2.4

 任务3 客户流失因素可视化分析

任务3.1

任务3.2

任务3.3

任务3.4 

任务1 数据探索与清洗

        分别对短期客户产品购买数据“short-customer-data.csv”（简称短期数据）和长期客户资源信息数据的训练集“long-customer-train.csv”（简称长期数据）进行数据探索与清洗。

任务1.1 数据探索与预处理

(1) 探索短期数据各指标数据的缺失值和“user_id”列重复值，删除缺失值、重复值所在行数据。请在报告中给出处理过程及必要结果，完整的结果保存到文件“result1_1.xlsx”中。

1.首先通过info函数查看数据情况

import pandas as pd
data=pd.read_csv("F:\泰迪杯B题\B题：银行客户忠诚度分析赛题数据\B题：银行客户忠诚度分析赛题数据\short-customer-data.csv")
data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 41176 entries, 0 to 41175
Data columns (total 14 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   user_id      41176 non-null  object
 1   age          41176 non-null  int64 
 2   job          40846 non-null  object
 3   marital      41096 non-null  object
 4   education    39446 non-null  object
 5   default      32580 non-null  object
 6   housing      40186 non-null  object
 7   loan         40186 non-null  object
 8   contact      41176 non-null  object
 9   month        41176 non-null  object
 10  day_of_week  41176 non-null  object
 11  duration     41176 non-null  int64 
 12  poutcome     41176 non-null  object
 13  y            41176 non-null  object
dtypes: int64(2), object(12)
memory usage: 4.4+ MB

2.删除缺失值所在行数据

data.dropna(inplace=True)

3. 查看“user_id”列重复值

(data["user_id"].value_counts()).head(20)
BA2200001    12
BA2200775     7
BA2239485     7
BA2241169     6
BA2200077     2
BA2241172     2
BA2201983     2
BA2240741     2
BA2200107     2
BA2229101     1
BA2220590     1
BA2208766     1
BA2225619     1
BA2204624     1
BA2227831     1
BA2239381     1
BA2224934     1
BA2224984     1
BA2200497     1
BA2203990     1
Name: user_id, dtype: int64

4.通过drop_duplicates函数删除重复值所在行数据

data.drop_duplicates(subset=['user_id'],inplace=True)

5. 写入excel文件

df1=pd.DataFrame(data)
df1.to_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result1_1.xlsx',sheet_name='sheet1',index=None)
(2) 长期数据中的客户年龄“Age”列存在数值为-1、0 和“-”的异常值，删除存在该情况的行数据；“Age”列存在空格和“岁”等异常字符，删除这些异常字符但须保留年龄数值，将处理后的数值存于“Age”列。请在报告中给出处理过程及必要结果，完整的结果保存到文件“result1_2.xlsx”中。 1.首先取出 “Age”列不存在数值为-1、0 和“-”的数据
long_data=long_data[~long_data["Age"].isin([0,'-',-1])]

2.数据处理

long_data["Age"] = long_data["Age"].str.replace("岁","")
long_data["Age"] = long_data["Age"].str.replace("","")
long_data["Age"].unique()
array(['52', '41', '42', '61', '39', '44', '53', '48', '60', '32', '38',
       '51', '56', '28', '57', '45', '46', '40', '30', '59', '33', '35',
       '54', '62', '23', '24', '36', '47', '73', '49', '37', '58', '21',
       '55', '29', '70', '34', '43', '31', '27', '50', '66', '26', '20',
       '64', '63', '71', '22', '25', '73 ', '75', '18', '1', '65', '72',
       '69', '33 ', '25 ', '24 ', '76', '47 ', '74', '19', '77', '0',
       '68', '49 ', '67', '63 ', '34 ', '50 ', '64 ', '29 ', '81', '27 ',
       '83', '79', '78', '23 ', '30 ', '26 ', '45 ', '77 ', '84', '80',
       '92', '32 ', '28 ', '31 ', '66 ', '22 ', '62 ', '46 ', '21 ',
       '57 ', '82', '88'], dtype=object)

3.写入excel文件

df2=pd.DataFrame(long_data)
df2.to_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result1_2.xlsx',sheet_name='sheet1',index=None)

任务1.2 特征编码

对短期数据中的字符型数据进行特征编码，如将信用违约情况{‘否’,‘是’}编码为{0,1}。请在报告中给出处理思路、过程及必要结果，完整的结果保存到文件“result1_3.xlsx”中。1.观察数据类型
data.dtypes
user_id        object
age             int64
job            object
marital        object
education      object
default        object
housing        object
loan           object
contact        object
month          object
day_of_week    object
duration        int64
poutcome       object
y              object
dtype: object

2.分别求各字符型数据值分布

data["job"].value_counts()
admin.           8724
blue-collar      5670
technician       5459
services         2855
management       2310
retired          1212
self-employed    1092
entrepreneur     1088
unemployed        738
housemaid         688
student           609
Name: job, dtype: int64
data["marital"].value_counts()
married     17472
single       9426
divorced     3547
Name: marital, dtype: int64

data["education"].value_counts()
undergraduate     10397
postgraduate       8037
high school        7688
junior college     4312
illiterate           11
Name: education, dtype: int64
data["housing"].value_counts()
yes    16498
no     13947
Name: housing, dtype: int64
data["loan"].value_counts()
no     25680
yes     4765
Name: loan, dtype: int64
data["contact"].value_counts()
cellular     20420
telephone    10025
Name: contact, dtype: int64
data["month"].value_counts()
may    9718
jul    5075
aug    4667
jun    3608
nov    3492
apr    2112
oct     641
sep     493
mar     482
dec     157
Name: month, dtype: int64
data["day_of_week"].value_counts()
thu    6388
mon    6274
wed    6116
tue    5939
fri    5728
Name: day_of_week, dtype: int64
data["poutcome"].value_counts()
nonexistent    25796
failure         3458
success         1191
Name: poutcome, dtype: int64
data["y"].value_counts()
no     26589
yes     3856
Name: y, dtype: int64

2.将数据分布为{‘否’,‘是’}编码为{0,1}

data["housing"] = data["housing"].str.replace("yes","1")
data["housing"] = data["housing"].str.replace("no","0")
data["loan"] = data["loan"].str.replace("yes","1")
data["loan"] = data["loan"].str.replace("no","0")
data["default"] = data["default"].str.replace("yes","1")
data["default"] = data["default"].str.replace("no","0")
data["y"] = data["y"].str.replace("yes","1")
data["y"] = data["y"].str.replace("no","0")

3.使用sklearn中的独热编码对其它非{yes,no}的具体值特征都转换为哑变量，分别赋值“0”和“1”

ps：数据量较多，为了谨慎处理避免出错，对数据多次处理

from sklearn.preprocessing import OneHotEncoder
index=data.values[:,0:2]
index_data=index #获得ID列
job_marital_education=data.values[:,2:5]
enc=OneHotEncoder() #建立模型对象
df1_new=enc.fit_transform(job_marital_education).toarray() #标志转换
df1_index_education=pd.concat((pd.DataFrame(index_data),pd.DataFrame(df1_new)),axis=1) #组合为数据框
df1_index_education.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate']
df1_index_education.head()
contact_month_week=data.values[:,8:11]
enc=OneHotEncoder() #建立模型对象
df2_new=enc.fit_transform(contact_month_week).toarray() #标志转换
df_index_week=pd.concat((pd.DataFrame(df1_index_education),pd.DataFrame(df2_new)),axis=1) #组合为数据框
df_index_week.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate',
                      'cellular','telephone','apr','aug','dec','jul','jun','mar','may','nov','oct','sep','fri','mon','thu','tue','wed']
df_index_week
default_loan=data.values[:,5:8]
df_now=pd.concat((pd.DataFrame(df_index_week),pd.DataFrame(default_loan)),axis=1) #组合为数据框
df_now.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate',
                      'cellular','telephone','apr','aug','dec','jul','jun','mar','may','nov','oct','sep','fri','mon','thu','tue','wed','default',
       'housing','loan']
df_now
data["y"]=data["y"].astype(dtype='int')
poutcome=data.values[:,11:]
data["y"]=data["y"].astype(dtype='int')
poutcome=data.values[:,12:]
enc=OneHotEncoder() #建立模型对象
df3_new=enc.fit_transform(poutcome).toarray() #标志转换
df_all=pd.concat((pd.DataFrame(df_index_week),pd.DataFrame(df3_new)),axis=1) #组合为数据框
df_all.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate',
                      'cellular','telephone','apr','aug','dec','jul','jun','mar','may','nov','oct','sep','fri','mon','thu','tue','wed',
               'failure','nonexistent','success','1','0']
df_all.drop("0",axis=1,inplace=True)
df_all.drop("1",axis=1,inplace=True)
df_all
last=data.values[:,11:14:2]
data_last=pd.concat((pd.DataFrame(df_all),pd.DataFrame(last)),axis=1) #组合为数据框
data_last.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate',
                      'cellular','telephone','apr','aug','dec','jul','jun','mar','may','nov','oct','sep','fri','mon','thu','tue','wed','failure','nonexistent','success','duration','y']
data_last

这里到后面做任务2的时候才发现漏了几列数据[o(╥﹏╥)o]，然后又回来补上

bb=data.values[:,5:8]
data_data=pd.concat((pd.DataFrame(data_last),pd.DataFrame(bb)),axis=1) #组合为数据框
data_data.columns=['user_id', 'age', 
                             'admin','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student',
                 'technician','unemployed','divorced','married','single',
                             'high school','illiterate','junior college','postgraduate','undergraduate',
                      'cellular','telephone','apr','aug','dec','jul','jun','mar','may','nov','oct','sep','fri','mon','thu','tue','wed',
               'failure','nonexistent','success','duration','y','default','housing','loan']
data_data

3.最后写入Excel

df3=pd.DataFrame(data_data)
df3.to_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result1_3.xlsx',sheet_name='sheet1',index=None)

任务1完！

任务2 产品营销数据可视化分析

基于短期数据分析不同指标客户与购买银行产品行为的关联性，挖掘短期客户对银行的忠诚度。

任务2.1 

计算短期数据所有指标之间的相关性，绘制相关系数热力图，并在报告中对结果进行必要分析。
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import missingno as ms
import plotly.express as px
import plotly.graph_objs as go
import plotly.figure_factory as ff
from plotly.subplots import make_subplots
import plotly.offline as pyo
pyo.init_notebook_mode()
sns.set_style('darkgrid')
plt.style.use('fivethirtyeight')
%matplotlib inline
import shap
plt.rc('figure',figsize=(18,9))


short_data=pd.read_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result1_3.xlsx')
corr=short_data.corr() #仅针对数值连续变量
plt.figure(figsize=(20,15))
sns.heatmap(corr
           ,annot=True
           ,linewidths=2
           ,linecolor='lightgrey')
plt.show()

任务2.2

在同一画布中，绘制反映两种产品购买结果下不同年龄客户量占比的分组柱状图，x 轴为年龄，y 轴为占比数值，并在报告中对结果进行必要分析。 
num_20_1=0
num_30_1=0
num_40_1=0
num_50_1=0
num_60_1=0
num_70_1=0
num_80_1=0
num_90_1=0
num_100_1=0
for i in range(len(short_data["age"])):
    if short_data.loc[i,'age']<=20 and short_data.loc[i,'y']==1:
        num_20_1+=1
    if 21<=short_data.loc[i,'age']<=30 and short_data.loc[i,'y']==1:
        num_30_1+=1
    if 31<=short_data.loc[i,'age']<=40 and short_data.loc[i,'y']==1:
        num_40_1+=1
    if 41<=short_data.loc[i,'age']<=50 and short_data.loc[i,'y']==1:
        num_50_1+=1
    if 51<=short_data.loc[i,'age']<=60 and short_data.loc[i,'y']==1:
        num_60_1+=1
    if 61<=short_data.loc[i,'age']<=70 and short_data.loc[i,'y']==1:
        num_70_1+=1
    if 71<=short_data.loc[i,'age']<=80 and short_data.loc[i,'y']==1:
        num_80_1+=1
    if 81<=short_data.loc[i,'age']<=90 and short_data.loc[i,'y']==1:
        num_90_1+=1
    if 91<=short_data.loc[i,'age']<=100 and short_data.loc[i,'y']==1:
        num_100_1+=1
num_20_0=0
num_30_0=0
num_40_0=0
num_50_0=0
num_60_0=0
num_70_0=0
num_80_0=0
num_90_0=0
num_100_0=0
for i in range(len(short_data["age"])):
    if short_data.loc[i,'age']<=20 and short_data.loc[i,'y']==0:
        num_20_0+=1
    if 21<=short_data.loc[i,'age']<=30 and short_data.loc[i,'y']==0:
        num_30_0+=1
    if 31<=short_data.loc[i,'age']<=40 and short_data.loc[i,'y']==0:
        num_40_0+=1
    if 41<=short_data.loc[i,'age']<=50 and short_data.loc[i,'y']==0:
        num_50_0+=1
    if 51<=short_data.loc[i,'age']<=60 and short_data.loc[i,'y']==0:
        num_60_0+=1
    if 61<=short_data.loc[i,'age']<=70 and short_data.loc[i,'y']==0:
        num_70_0+=1
    if 71<=short_data.loc[i,'age']<=80 and short_data.loc[i,'y']==0:
        num_80_0+=1
    if 81<=short_data.loc[i,'age']<=90 and short_data.loc[i,'y']==0:
        num_90_0+=1
    if 91<=short_data.loc[i,'age']<=100 and short_data.loc[i,'y']==0:
        num_100_0+=1
total_1=num_20_1+num_30_1+num_40_1+num_50_1+num_60_1+num_70_1+num_80_1+num_90_1+num_100_1
total_0=num_20_0+num_30_0+num_40_0+num_50_0+num_60_0+num_70_0+num_80_0+num_90_0+num_100_0
from matplotlib import pyplot as plt
font = {'family': 'MicroSoft YaHei',
        'weight': 'bold',
        'size': '12'}
matplotlib.rc("font",**font)
x=["<20","21-30","31-40","41-50","51-60","61-70","71-80","81-90","91-100"]
y_1=[num_20_1/total_1,num_30_1/total_1,num_40_1/total_1,num_50_1/total_1,num_60_1/total_1,num_70_1/total_1,num_80_1/total_1,num_90_1/total_1,num_100_1/total_1]
y_0=[num_20_0/total_0,num_30_0/total_0,num_40_0/total_0,num_50_0/total_0,num_60_0/total_0,num_70_0/total_0,num_80_0/total_0,num_90_0/total_0,num_100_0/total_0]
ax=plt.subplot(1,2,1)

plt.bar(x,y_1,color='orange')
plt.xlabel("年龄分组")
plt.xticks(x)
plt.ylabel("人数占比")
plt.title('客户购买结果为1')
ax=plt.subplot(1,2,2)
plt.bar(x,y_0,color='green')
plt.grid(color='#95a5a6',linestyle='--',linewidth=1,axis='y',alpha=0.5)
plt.xlabel("年龄分组")
plt.xticks(x)
plt.ylabel("人数占比")
plt.title('客户购买结果为0')  
plt.grid(color='#95a5a6',linestyle='--',linewidth=1,axis='y',alpha=0.5)
plt.show()

任务2.3

在同一画布中，绘制蓝领（blue-collar）与学生（student）的产品购买情况饼图，并设定饼图的标签，显示产品购买情况的占比。 
blue_1=0
student_1=0
for i in range(len(short_data["age"])):
    if short_data.loc[i,"blue-collar"]==1 and short_data.loc[i,'y']==1:
        blue_1+=1
    if short_data.loc[i,"student"]==1 and short_data.loc[i,'y']==1:
        student_1+=1

blue_0=0
student_0=0
for i in range(len(short_data["age"])):
    if short_data.loc[i,"blue-collar"]==1 and short_data.loc[i,'y']==0:
        blue_0+=1
    if short_data.loc[i,"student"]==1 and short_data.loc[i,'y']==0:
        student_0+=1

num1=[blue_1,student_1]
num0=[blue_0,student_0]
colors = ['blue','yellow']
# #设置突出模块偏移值
expodes = (0,0)
#设置绘图属性并绘图
ax=plt.subplot(1,2,1)
plt.pie(num1,explode=expodes,labels=labels,shadow=True,colors=colors)
plt.title("产品购买结果为1")
plt.axis('equal')
ax=plt.subplot(1,2,2)
plt.pie(num0,explode=expodes,labels=labels,shadow=True,colors=colors)
plt.title("产品购买结果为0")
plt.axis('equal')
plt.show()

任务2.4

以产品购买结果为 x 轴、拜访客户的通话时长为 y 轴，绘制拜访客户的通话时长箱线图，并在报告中对结果进行必要分析。 
x=["产品购买结果为1","产品购买结果为0"]
y_1=[]
y_0=[]
for i in range(len(short_data["age"])):
    if short_data.loc[i,'y']==0:
        y_1.append(short_data.loc[i,'duration'])
    if short_data.loc[i,'y']==1:
        y_0.append(short_data.loc[i,'duration'])
dt = pd.DataFrame({'产品购买结果为0': y_0})
dt = pd.DataFrame({'产品购买结果为1': y_1})
dt.boxplot()  #对数据框中每列画箱线图，pandas自己有处理的过程
plt.show()

 任务3 客户流失因素可视化分析

基于长期数据分析导致银行客户流失的因素，并进行可视化呈现。

任务3.1

在同一画布中，绘制反映两种流失情况下不同年龄客户量占比的折线图，x 轴为年龄，y 轴为占比数值
import pandas as pd
long_data=pd.read_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result1_2.xlsx')
num_20_1=0
num_30_1=0
num_40_1=0
num_50_1=0
num_60_1=0
num_70_1=0
num_80_1=0
num_90_1=0
num_100_1=0
for i in range(len(short_data["Age"])):
    if short_data.loc[i,'Age']<=20 and short_data.loc[i,'Exited']==1:
        num_20_1+=1
    if 21<=short_data.loc[i,'Age']<=30 and short_data.loc[i,'Exited']==1:
        num_30_1+=1
    if 31<=short_data.loc[i,'Age']<=40 and short_data.loc[i,'Exited']==1:
        num_40_1+=1
    if 41<=short_data.loc[i,'Age']<=50 and short_data.loc[i,'Exited']==1:
        num_50_1+=1
    if 51<=short_data.loc[i,'Age']<=60 and short_data.loc[i,'Exited']==1:
        num_60_1+=1
    if 61<=short_data.loc[i,'Age']<=70 and short_data.loc[i,'Exited']==1:
        num_70_1+=1
    if 71<=short_data.loc[i,'Age']<=80 and short_data.loc[i,'Exited']==1:
        num_80_1+=1
    if 81<=short_data.loc[i,'Age']<=90 and short_data.loc[i,'Exited']==1:
        num_90_1+=1
    if 91<=short_data.loc[i,'Age']<=100 and short_data.loc[i,'Exited']==1:
        num_100_1+=1
num_20_0=0
num_30_0=0
num_40_0=0
num_50_0=0
num_60_0=0
num_70_0=0
num_80_0=0
num_90_0=0
num_100_0=0
for i in range(len(short_data["Age"])):
    if short_data.loc[i,'Age']<=20 and short_data.loc[i,'Exited']==0:
        num_20_0+=1
    if 21<=short_data.loc[i,'Age']<=30 and short_data.loc[i,'Exited']==0:
        num_30_0+=1
    if 31<=short_data.loc[i,'Age']<=40 and short_data.loc[i,'Exited']==0:
        num_40_0+=1
    if 41<=short_data.loc[i,'Age']<=50 and short_data.loc[i,'Exited']==0:
        num_50_0+=1
    if 51<=short_data.loc[i,'Age']<=60 and short_data.loc[i,'Exited']==0:
        num_60_0+=1
    if 61<=short_data.loc[i,'Age']<=70 and short_data.loc[i,'Exited']==0:
        num_70_0+=1
    if 71<=short_data.loc[i,'Age']<=80 and short_data.loc[i,'Exited']==0:
        num_80_0+=1
    if 81<=short_data.loc[i,'Age']<=90 and short_data.loc[i,'Exited']==0:
        num_90_0+=1
    if 91<=short_data.loc[i,'Age']<=100 and short_data.loc[i,'Exited']==0:
        num_100_0+=1
total_1=num_20_1+num_30_1+num_40_1+num_50_1+num_60_1+num_70_1+num_80_1+num_90_1+num_100_1
total_1
total_0=num_20_0+num_30_0+num_40_0+num_50_0+num_60_0+num_70_0+num_80_0+num_90_0+num_100_0
total_0
import pandas as pd
import matplotlib
from matplotlib import pyplot as plt
font = {'family': 'MicroSoft YaHei',
        'weight': 'bold',
        'size': '12'}
matplotlib.rc("font",**font)
x=["<20","21-30","31-40","41-50","51-60","61-70","71-80","81-90","91-100"]

y_1=[num_20_1/total_1,num_30_1/total_1,num_40_1/total_1,num_50_1/total_1,num_60_1/total_1,num_70_1/total_1,num_80_1/total_1,num_90_1/total_1,num_100_1/total_1]
y_0=[num_20_0/total_0,num_30_0/total_0,num_40_0/total_0,num_50_0/total_0,num_60_0/total_0,num_70_0/total_0,num_80_0/total_0,num_90_0/total_0,num_100_0/total_0]
plt.figure(figsize=(12,5))
ax=plt.subplot(1,2,1)

plt.plot(x,y_1,color='orange')
plt.xlabel("年龄分组")
plt.xticks(x)
plt.ylabel("人数占比")
plt.title('客户流失')
ax=plt.subplot(1,2,2)
plt.plot(x,y_0,color='green')
plt.grid(color='#95a5a6',linestyle='--',linewidth=1,axis='y',alpha=0.5)
plt.xlabel("年龄分组")
plt.xticks(x)
plt.ylabel("人数占比")
plt.title('客户不流失')  
plt.grid(color='#95a5a6',linestyle='--',linewidth=1,axis='y',alpha=0.5)
plt.show()

任务3.2

在同一画布中，绘制反映两种流失情况下客户信用资格与年龄分布的散点图，x 轴为年龄，y 轴为信用资格。
x_1=[]
y_1=[]
x_0=[]
y_0=[]
for i in range(len(short_data["Age"])):
    if short_data.loc[i,'Exited']==1:
        x1=short_data.loc[i,'Age']
        y1=short_data.loc[i,'CreditScore']
        x_1.append(x1)
        y_1.append(y1)
    if short_data.loc[i,'Exited']==0:
        x0=short_data.loc[i,'Age']
        y0=short_data.loc[i,'CreditScore']
        x_0.append(x0)
        y_0.append(y0)
fig=plt.figure(figsize=(12,5),dpi=100)
ax=plt.subplot(1,2,1)
plt.scatter(x_1,y_1,s=1,)
plt.xlabel("年龄")
plt.ylabel("信用资格")
plt.title('客户流失')
ax=plt.subplot(1,2,2)
plt.scatter(x_0,y_0,s=1,)
plt.xlabel("年龄")
# plt.ylabel("信用资格")
plt.yticks([])
plt.title('客户不流失')
plt.show()

任务3.3

构造包含各账号户龄在不同流失情况下的客户量占比透视表（详见表 4），并在同一画布中绘制反映两种流失情况的客户各账号户龄占比量的堆叠柱状图，x 轴为客户的户龄，y 轴为占比量。

 

count_0_0=0
count_1_0=0
count_2_0=0
count_3_0=0
count_4_0=0
count_5_0=0
count_6_0=0
count_7_0=0
count_8_0=0
count_9_0=0
count_10_0=0
count_0=[count_0_0,count_1_0,count_2_0,count_3_0,count_4_0,count_5_0,count_6_0,count_7_0,count_8_0,count_9_0,count_10_0]
count_0_1=0
count_1_1=0
count_2_1=0
count_3_1=0
count_4_1=0
count_5_1=0
count_6_1=0
count_7_1=0
count_8_1=0
count_9_1=0
count_10_1=0
count_1=[count_0_1,count_1_1,count_2_1,count_3_1,count_4_1,count_5_1,count_6_1,count_7_1,count_8_1,count_9_1,count_10_1]
for i in range(len(short_data["Age"])):
    if short_data.loc[i,'Exited']==1:
        a1=short_data.loc[i,'Tenure']
        if a1==0:
            count_0_1+=1
        if a1==1:
            count_1_1+=1
        if a1==2:
            count_2_1+=1
        if a1==3:
            count_3_1+=1
        if a1==4:
            count_4_1+=1
        if a1==5:
            count_5_1+=1
        if a1==6:
            count_6_1+=1
        if a1==7:
            count_7_1+=1
        if a1==8:
            count_8_1+=1
        if a1==9:
            count_9_1+=1
        if a1==10:
            count_10_1+=1
    if short_data.loc[i,'Exited']==0:
        a2=short_data.loc[i,'Tenure']
        if a2==0:
            count_0_0+=1
        if a2==1:
            count_1_0+=1
        if a2==2:
            count_2_0+=1
        if a2==3:
            count_3_0+=1
        if a2==4:
            count_4_0+=1
        if a2==5:
            count_5_0+=1
        if a2==6:
            count_6_0+=1
        if a2==7:
            count_7_0+=1
        if a2==8:
            count_8_0+=1
        if a2==9:
            count_9_0+=1
        if a2==10:
            count_10_0+=1

得到透视表：

total_0=0
total_1=0
for i in range(0,11):
    total_0+=count_0[i]
    total_1+=count_1[i]
total_0
total_1
for i in range(0,11):
    count_0[i]=count_0[i]/total_0
    count_1[i]=count_1[i]/total_1
data_1={'count_0':count_0,'count_1':count_1}
df_1=pd.DataFrame(data_1)
df_1.plot(kind='bar',stacked=True,alpha=0.5)

 

任务3.4

按照表 5 和表 6 对账号户龄和客户金融资产进行划分，并分别进行特征编码作为新的客户特征，其中客户状态存于“Status”列，资产阶段存于“AssetStage”列，编码结果保存到文件“result3.xlsx”中。 
Status=[]
AssetStage=[]

for i in range(len(short_data["Age"])):
    if 0<=short_data.loc[i,'Tenure']<=3:
        Status[i]="新客户"
    if 3<short_data.loc[i,'Tenure']<=6:
        Status[i]="稳定客户"
    if short_data.loc[i,'Tenure']>6:
        Status[i]="老客户"
for i in range(len(short_data["Age"])):
    if 0<=short_data.loc[i,'Balance']<=50000:
        AssetStage[i]="低资产"
    if 50000<short_data.loc[i,'Balance']<=90000:
        AssetStage[i]="中下资产"
    if 90000<short_data.loc[i,'Balance']<=120000:
        AssetStage[i]="中上资产"
    if short_data.loc[i,'Balance']>120000:
        AssetStage[i]="高资产"
data_1={'Status':Status,'AssetStage':AssetStage}
df_1=pd.DataFrame(data_1)
df_1.to_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\result3.xlsx',sheet_name='sheet1',index=None)
(2) 统计新、老客户在各资产阶段中流失的客户量，在同一画布中绘制热力图，热力图颜色的最大和最小取值设为 1300 和 100，并在报告中对结果进行必要分析。
test=pd.read_excel('F:\\泰迪杯B题\\B题：银行客户忠诚度分析赛题数据\\任务3.4.2.xlsx')
new_lower=0
new_low=0
new_high=0
new_higher=0
old_lower=0
old_low=0
old_high=0
old_higher=0
for i in range(len(test['Exited'])):
    if test.loc[i,'Exited']==1 and test.loc[i,'Status']=='新客户'and test.loc[i,'AssetStage']=="低资产":
        new_lower+=1
    if test.loc[i,'Exited']==1 and test.loc[i,'Status']=='新客户'and test.loc[i,'AssetStage']=="中下资产":
        new_low+=1 
    if test.loc[i,'Exited']==1 and test.loc[i,'Status']=='新客户'and test.loc[i,'AssetStage']=="中上资产":
        new_high+=1 
    if test.loc[i,'Exited']==1 and test.loc[i,'Status']=='新客户'and test.loc[i,'AssetStage']=="高资产":
        new_higher+=1 
    if test.loc[i,'Exited']==0 and test.loc[i,'Status']=='老客户'and test.loc[i,'AssetStage']=="低资产":
        old_lower+=1 
    if test.loc[i,'Exited']==0 and test.loc[i,'Status']=='老客户'and test.loc[i,'AssetStage']=="中下资产":
        old_low+=1 
    if test.loc[i,'Exited']==0 and test.loc[i,'Status']=='老客户'and test.loc[i,'AssetStage']=="中上资产":
        old_high+=1 
    if test.loc[i,'Exited']==0 and test.loc[i,'Status']=='老客户'and test.loc[i,'AssetStage']=="高资产":
        old_higher+=1 
new=[new_lower,new_low,new_high,new_higher]
old=[old_lower,old_low,old_high,old_higher]
data_2={'new_lower':[180],'new_low':[57],'new_high':[181],'new_higher':[246],
'old_lower':[1066],'old_low':[234],'old_high':[536],'old_higher':[800]}
df_2=pd.DataFrame(data_2)
plt.figure(figsize=(10,5))
sns.heatmap(df_2,vmax=1300,vmin=100)

ps:由于篇幅太长，暂时先写到这吧。剩下的会写在另一篇，再做个比赛总结

2022第五届“泰迪杯”数据分析技能赛-B题-银行客户忠诚度分析(下)链接：https://blog.csdn.net/weixin_60200880/article/details/127939604?spm=1001.2014.3001.5502

文章出处登录后可见！

已经登录？立即刷新

共计人评分，平均分

到目前为止还没有投票！成为第一位评论此文章。

赞 (0)
社会演员多的头像社会演员多普通用户
0
上一篇 2023年11月2日
下一篇 2023年11月2日

相关推荐