2022年泰迪杯数据分析_B题：银行客户忠诚度分析赛题数据_任务四

import pandas as pd

long_data21=pd.read_excel('result1_2.xlsx')
long_data25=pd.read_excel('result3.xlsx')

long_data21['Status']=long_data25['Status']
long_data21['AssetStage']=long_data25['AssetStage']
long_data21

	CustomerId	CreditScore	Gender	Age	Tenure	Balance	NumOfProducts	HasCrCard	IsActiveMember	EstimatedSalary	Exited	Status	AssetStage
0	15553251	713	1	52	0	185891.54	1	1	1	46369.57	1	1	3
1	15553256	619	1	41	8	0.00	3	1	1	79866.73	1	2	2
2	15553283	603	1	42	8	91611.12	1	0	0	144675.30	1	2	0
3	15553308	589	1	61	1	0.00	1	1	0	61108.56	1	1	2
4	15553387	687	1	39	2	0.00	3	0	0	188150.60	1	1	2
...	...	...	...	...	...	...	...	...	...	...	...	...	...
9175	15815628	711	1	37	8	113899.92	1	0	0	80215.20	0	2	0
9176	15815645	481	0	37	8	152303.66	2	1	1	175082.20	0	2	3
9177	15815656	541	1	39	9	100116.67	1	1	1	199808.10	1	2	0
9178	15815660	758	1	34	1	154139.45	1	1	1	60728.89	0	1	3
9179	15815690	614	1	40	3	113348.50	1	1	1	77789.01	0	1	0

9180 rows × 13 columns

long_data21['Tenure'].unique()

array([ 0,  8,  1,  2, 10,  5,  9,  7,  4,  3,  6], dtype=int64)

构建新老客户活跃程度的特征，并将结果存于“IsActiveStatus”
列：根据题目pdf中表5的账户户龄划分区间，再结合用户的活跃状态，构建新老客户活跃程度的特征，存于IsActiveStatus列。

long_data22=long_data21.copy()
#long_data22['账号户龄']=0
long_data22.loc[(long_data22.Tenure>6) & (long_data22.IsActiveMember==1),'IsActiveStatus']=5
long_data22.loc[(long_data22.Tenure>6) & (long_data22.IsActiveMember==0),'IsActiveStatus']=2
long_data22.loc[(long_data22.Tenure<=3) & (long_data22.IsActiveMember==1),'IsActiveStatus']=3
long_data22.loc[(long_data22.Tenure<=3) & (long_data22.IsActiveMember==0),'IsActiveStatus']=0
long_data22.loc[(long_data22.IsActiveStatus.isna()) & (long_data22.IsActiveMember==1),'IsActiveStatus']=4
long_data22.loc[(long_data22.IsActiveStatus.isna()) & (long_data22.IsActiveMember==0),'IsActiveStatus']=1

long_data22.head(3)

	CustomerId	CreditScore	Gender	Age	Tenure	Balance	NumOfProducts	HasCrCard	IsActiveMember	EstimatedSalary	Exited	Status	AssetStage	IsActiveStatus
0	15553251	713	1	52	0	185891.54	1	1	1	46369.57	1	1	3	3.0
1	15553256	619	1	41	8	0.00	3	1	1	79866.73	1	2	2	5.0
2	15553283	603	1	42	8	91611.12	1	0	0	144675.30	1	2	0	2.0

构建不同金融资产客户活跃程度的特征，并将结果存于
“IsActiveAssetStage”列:根据题目pdf中表6的客户金融资产划分区间，再结合用户的活跃状态，构建出不同金融资产客户活跃程度的特征，存于IsActiveAssetStage列。

long_data22.loc[(long_data22.Balance>120000) & (long_data22.IsActiveMember==1),'IsActiveAssetStage']=9
long_data22.loc[(long_data22.Balance>120000) & (long_data22.IsActiveMember==0),'IsActiveAssetStage']=3

long_data22.loc[(long_data22.Balance>90000) & (long_data22.Balance<=120000) & (long_data22.IsActiveMember==1),'IsActiveAssetStage']=8
long_data22.loc[(long_data22.Balance>90000) & (long_data22.Balance<=120000) & (long_data22.IsActiveMember==0),'IsActiveAssetStage']=2

long_data22.loc[(long_data22.Balance>50000) & (long_data22.Balance<=90000) & (long_data22.IsActiveMember==1),'IsActiveAssetStage']=7
long_data22.loc[(long_data22.Balance>50000) & (long_data22.Balance<=90000) & (long_data22.IsActiveMember==0),'IsActiveAssetStage']=1

long_data22.loc[(long_data22.Balance<=50000) & (long_data22.IsActiveMember==1),'IsActiveAssetStage']=6
long_data22.loc[(long_data22.Balance<=50000) & (long_data22.IsActiveMember==0),'IsActiveAssetStage']=0

long_data22.head(3)

	CustomerId	CreditScore	Gender	Age	Tenure	Balance	NumOfProducts	HasCrCard	IsActiveMember	EstimatedSalary	Exited	Status	AssetStage	IsActiveStatus	IsActiveAssetStage
0	15553251	713	1	52	0	185891.54	1	1	1	46369.57	1	1	3	3.0	9.0
1	15553256	619	1	41	8	0.00	3	1	1	79866.73	1	2	2	5.0	6.0
2	15553283	603	1	42	8	91611.12	1	0	0	144675.30	1	2	0	2.0	2.0

构建不同金融资产信用卡持有状态的特征，并将结果存于
“CrCardAssetStage”列：

long_data22.loc[(long_data22.Balance>120000) & (long_data22.HasCrCard==1),'CrCardAssetStage']=9
long_data22.loc[(long_data22.Balance>120000) & (long_data22.HasCrCard==0),'CrCardAssetStage']=5

long_data22.loc[(long_data22.Balance>90000) & (long_data22.Balance<=120000) & (long_data22.HasCrCard==1),'CrCardAssetStage']=9
long_data22.loc[(long_data22.Balance>90000) & (long_data22.Balance<=120000) & (long_data22.HasCrCard==0),'CrCardAssetStage']=5

long_data22.loc[(long_data22.Balance>50000) & (long_data22.Balance<=90000) & (long_data22.HasCrCard==1),'CrCardAssetStage']=7
long_data22.loc[(long_data22.Balance>50000) & (long_data22.Balance<=90000) & (long_data22.HasCrCard==0),'CrCardAssetStage']=2

long_data22.loc[(long_data22.Balance<=50000) & (long_data22.HasCrCard==1),'CrCardAssetStage']=6
long_data22.loc[(long_data22.Balance<=50000) & (long_data22.HasCrCard==0),'CrCardAssetStage']=0

long_data22.head(3)

	CustomerId	CreditScore	Gender	Age	Tenure	Balance	NumOfProducts	HasCrCard	IsActiveMember	EstimatedSalary	Exited	Status	AssetStage	IsActiveStatus	IsActiveAssetStage	CrCardAssetStage
0	15553251	713	1	52	0	185891.54	1	1	1	46369.57	1	1	3	3.0	9.0	9.0
1	15553256	619	1	41	8	0.00	3	1	1	79866.73	1	2	2	5.0	6.0	6.0
2	15553283	603	1	42	8	91611.12	1	0	0	144675.30	1	2	0	2.0	2.0	5.0

long_data22.to_excel("result4.xlsx",encoding = 'openpyxl',index = False)