目录

任务1 数据探索与清洗

任务1.1

任务1.2

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

任务2.1

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

任务3.1

任务3.2

任务3.3

任务3.4

任务4 特征构建

任务1 数据探索与清洗

任务1.1

df = pd.read_csv(r'E:\编程学习\比赛用的\2022-B题\B题：银行客户忠诚度分析赛题数据\short-customer-data.csv',encoding='gbk')
dfs = pd.read_csv(r'E:\编程学习\比赛用的\2022-B题\B题：银行客户忠诚度分析赛题数据\long-customer-train.csv',encoding='utf-8')
df# 任务1_1 (1)
df = pd.read_csv(r'E:\编程学习\比赛用的\2022-B题\B题：银行客户忠诚度分析赛题数据\short-customer-data.csv',encoding='gbk')
df.dtypes
# 1. 查找所有缺失值的数量
df.isnull().sum()
# 2.数据表清洗,去除空值行
d = df.dropna()
d.isnull().sum()
# 3.去重复的值
d.drop_duplicates(["user_id"],keep='first',inplace=True)
d.to_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result1_1.xlsx', index=False, encoding='utf-8')# (2)
dfs = pd.read_csv(r'E:\编程学习\比赛用的\2022-B题\B题：银行客户忠诚度分析赛题数据\long-customer-train.csv',encoding='utf-8')
dfs.dtypes
# # 1. 查找所有缺失值的数量
df.isnull().sum()
dfs['Age'] = dfs['Age'].str.replace("岁",'')
dfs['Age'] = dfs['Age'].str.replace("-",'0')
dfs['Age'] = dfs['Age'].str.replace(" ",'')
# 转换Age（客户年龄）数据类型为int类型
dfs['Age'] = dfs['Age'].astype('int64')
dfs.dtypes
dfs.to_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result1_2.xlsx', index=False, encoding='utf-8')

任务1.2

# 特征转换
from sklearn.preprocessing import OrdinalEncoder
df_data_3 = d.copy()
# 特征转换
df_data_3[['job', 'marital', 'education', 'default', 'housing', 'loan', 'contact', 'month', 'day_of_week', 'poutcome', 'y']] = \
OrdinalEncoder()\
.fit_transform(d[['job', 'marital', 'education', 'default', 'housing', 'loan', 'contact', 'month', 'day_of_week', 'poutcome', 'y']])
df_data_3.to_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result1_3.xlsx', index=False, encoding='utf-8')
df_data = d.copy()
df_data_dummies = pd.get_dummies(df_data[['job', 'marital', 'education', 'default', 'housing', 'loan', 'contact', 'month', 'day_of_week', 'poutcome', 'y']])
df_data_dummies

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

任务2.1

import pandas as pd
df = pd.read_excel(r'E:\编程学习\备赛-数据分析比赛\Day02\练习题-任务 2\result1.xlsx')
df
df2 = df.copy()
del df2['user_id']
df2# 引入第三方库
import sklearn
from sklearn import cluster
from sklearn.preprocessing import StandardScaler
import seaborn as sns
import matplotlib.pyplot as plt# 10个聚类簇
model = cluster.KMeans(n_clusters=10)
# 标准化模型
scaler = StandardScaler()
# 标准化
data_ = scaler.fit_transform(df2.iloc[:,:])
# 模型训练
model.fit(data_)
# 对数据进行聚类得到标签
labels = model.predict(data_)
df2['标签'] = labels
df2df2['值'] = df2['job']
summarys = df2.pivot_table(index='标签',columns='marital',values='值',aggfunc='count', fill_value=0)
summarysplt.style.use('ggplot')
#处理中文乱码
plt.rcParams['font.sans-serif']=['Microsoft YaHei']
#坐标轴负号的处理
plt.rcParams['axes.unicode_minus']=False
sns.heatmap(data=summarys,cmap="YlGnBu_r",vmin=100, vmax=1300, linewidths=.2,annot=True, fmt='g')
plt.title('十类人群在不同姻状况的数量热力图')
plt.show()

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

任务3.1

# 任务三
data3 = pd.read_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result1_2.xlsx')
data3# 任务3-1
import pyecharts.options as opts
from pyecharts.charts import Line,Page
data3 = pd.read_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result1_2.xlsx')
data_age1 = data3.query('Exited == 0')
data_age2 = data3.query('Exited == 1')data3_count = data_age1['Age'].value_counts()
le = len(data_age1['Age'])
age = [a for a in data3_count.index]
b = [round(b/le, 2) for b in data3_count.values]data3_count2 = data_age2['Age'].value_counts()
ages = [a for a in data3_count2.index]
e = [round(b/le, 2) for b in data3_count2.values]import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif']=['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False    # 用来正常显示负号
fig = plt.figure()
ax1 = fig.add_subplot(1,2,1)
plt.title('不同年龄客户量占比')
ax1.plot(age, b, 'o:r', label='Line A')ax2 = fig.add_subplot(1,2,2)
plt.title('不同年龄客户量占比-已流失')
ax2.plot(ages,e, color='b', marker='o', label='Line B')
plt.savefig(r'E:\编程学习\比赛用的\2022-B题\作品\image\任务 3.1.png')
plt.show()

任务3.2

# 任务3-2
import matplotlib.pyplot as plt
# 无流失
CreditScore = [c for c in data_age1['CreditScore']]
CreditScore_age = [r for r in data_age1['Age']]# 已流失
CreditScore2 = [c for c in data_age2['CreditScore']]
CreditScore_ages = [r for r in data_age2['Age']]fig = plt.figure()
ax1 = fig.add_subplot(1,2,1)
plt.title('客户信用资格与年龄分布')
ax1.scatter(age, b)ax2 = fig.add_subplot(1,2,2)
plt.title('客户信用资格与年龄分布-已流失')
ax2.scatter(ages,e)
plt.savefig(r'E:\编程学习\比赛用的\2022-B题\作品\image\任务 3.2.png')
plt.show()

任务3.3

# 任务3-3
data3['值'] =data3['Tenure']
summary=data3.pivot_table(index='Exited',columns='Tenure',values='值',aggfunc='count', fill_value=0)
print(summary)

任务3.4

# 任务3-4
def Year(t):if t <= 3:return '新客户'elif t <= 6:return '稳定客户'elif t > 6:return '老客户'
def asset(z):if z <= 50000:return '低资产'elif z <= 90000:return '中下资产'elif z <= 120000:return '中上资产'elif z > 120000:return '高资产'
df_Text['Status'] = df_Text['Tenure'].apply(Year)
df_Text['AssetStage'] = df_Text['Balance'].apply(asset)
df_Text.to_excel(r'E:\编程学习\备赛-数据分析比赛\Day02\练习题--任务 4 特征构建\result3.xlsx',index=False)
data3_4 = pd.read_excel(r'E:\编程学习\比赛用的\2022-B题\作品\result\result3.xlsx')
data3_4plt.style.use('ggplot')
#处理中文乱码
plt.rcParams['font.sans-serif']=['Microsoft YaHei']
#坐标轴负号的处理
plt.rcParams['axes.unicode_minus']=False
sns.heatmap(data=summarys,cmap="YlGnBu_r",vmin=100, vmax=1300, linewidths=.2,annot=True, fmt='g')
plt.title('计新、老客户在各资产阶段中流失的客户量热力图')
plt.savefig(r'E:\编程学习\比赛用的\2022-B题\作品\image\任务 3.4.png')
plt.show()

任务4 特征构建

df = pd.read_excel(r'E:\编程学习\备赛-数据分析比赛\Day02\练习题--任务 4 特征构建\result1_2.xlsx')
dfdef Active_year(a, b):if b == 0:if a == '新客户':return 0elif a == '稳定客户':return 1elif a == '老客户':return 2else:if a == '新客户':return 3elif a == '稳定客户':return 4elif a == '老客户':return 5
def Active_AssetStage(a, b):if b == 0:if a == '低资产':return 0elif a == '中下资产':return 1elif a == '中上资产':return 2elif a == '高资产':return 3else:if a == '低资产':return 6elif a == '中下资产':return 7elif a == '中上资产':return 8elif a == '高资产':return 9def Active_HasCrCard(a, b):if b == 0:if a == '低资产':return 0elif a == '中下资产':return 2elif a == '中上资产':return 5elif a == '高资产':return 5else:if a == '低资产':return 6elif a == '中下资产':return 7elif a == '中上资产':return 9elif a == '高资产':return 9df2 = df.copy()
df2['IsActiveStatus'] = df2.apply(lambda x: Active_year(x['Status'], x['IsActiveMember']), axis=1)
df2['IsActiveAssetStage'] = df2.apply(lambda x: Active_AssetStage(x['AssetStage'], x['IsActiveMember']), axis=1)
df2['CrCardAssetStage'] = df2.apply(lambda x: Active_HasCrCard(x['AssetStage'], x['HasCrCard']), axis=1)
df2.to_excel(r'E:\编程学习\备赛-数据分析比赛\Day02\练习题--任务 4 特征构建\result1_3.xlsx', index=False)