2020国赛C中小微企业的信贷策略数据预处理代码

本题的思路做法有很多，本文不再赘述，只进行简单的数据预处理为后面几问分析做基础
本文目标是用python提取数据整理出最基础的数据

原题一共有3个附件，我们主要针对前两个附件进行处理

• 附件1 123家有信贷记录企业的相关数据
• 附件2 302家无信贷记录企业的相关数据
• 附件3 银行贷款年利率与客户流失率关系的2019年统计数据

整理目标指标体系：
进项：单位个数，月均价税合计累计，负金额个数，月均负金额累计，作废发票个数，月均作废金额累计
销项：单位个数，月均价税合计累计，负金额个数，月均负金额累计，作废发票个数，月均作废金额累计

123家进项：

import numpy as np
import pandas as pd

import os

data1=pd.read_excel('data1.xlsx','销项发票信息')

data1

	企业代号	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
0	E1	11459356	2017-08-04	B03711	9401.71	1598.29	11000.0	有效发票
1	E1	5076239	2017-08-09	B00844	8170.94	1389.06	9560.0	有效发票
2	E1	5076240	2017-08-09	B00844	8170.94	1389.06	9560.0	有效发票
3	E1	5076241	2017-08-09	B00844	4085.47	694.53	4780.0	有效发票
4	E1	5076242	2017-08-09	B00844	4085.47	694.53	4780.0	有效发票
...	...	...	...	...	...	...	...	...
162479	E123	8887701	2019-12-17	B10944	4827.67	144.83	4972.5	有效发票
162480	E123	8887702	2019-12-17	B10944	7412.62	222.38	7635.0	有效发票
162481	E123	34173085	2019-12-17	B13093	1917.47	57.53	1975.0	有效发票
162482	E123	8887703	2019-12-25	B13093	7252.42	217.58	7470.0	有效发票
162483	E123	8887704	2019-12-25	B13093	6660.19	199.81	6860.0	有效发票

162484 rows × 8 columns

data1['发票状态'] =data1['发票状态'].map({'作废发票':0,'有效发票':1})
data1['发票状态'].value_counts()

1.0    151278
0.0     11159
Name: 发票状态, dtype: int64

data1

	企业代号	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
0	E1	11459356	2017-08-04	B03711	9401.71	1598.29	11000.0	1.0
1	E1	5076239	2017-08-09	B00844	8170.94	1389.06	9560.0	1.0
2	E1	5076240	2017-08-09	B00844	8170.94	1389.06	9560.0	1.0
3	E1	5076241	2017-08-09	B00844	4085.47	694.53	4780.0	1.0
4	E1	5076242	2017-08-09	B00844	4085.47	694.53	4780.0	1.0
...	...	...	...	...	...	...	...	...
162479	E123	8887701	2019-12-17	B10944	4827.67	144.83	4972.5	1.0
162480	E123	8887702	2019-12-17	B10944	7412.62	222.38	7635.0	1.0
162481	E123	34173085	2019-12-17	B13093	1917.47	57.53	1975.0	1.0
162482	E123	8887703	2019-12-25	B13093	7252.42	217.58	7470.0	1.0
162483	E123	8887704	2019-12-25	B13093	6660.19	199.81	6860.0	1.0

162484 rows × 8 columns

data1['开票日期']=data1['开票日期'].map(lambda x: 100*x.year + x.month)
data1

	企业代号	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
0	E1	11459356	201708	B03711	9401.71	1598.29	11000.0	1.0
1	E1	5076239	201708	B00844	8170.94	1389.06	9560.0	1.0
2	E1	5076240	201708	B00844	8170.94	1389.06	9560.0	1.0
3	E1	5076241	201708	B00844	4085.47	694.53	4780.0	1.0
4	E1	5076242	201708	B00844	4085.47	694.53	4780.0	1.0
...	...	...	...	...	...	...	...	...
162479	E123	8887701	201912	B10944	4827.67	144.83	4972.5	1.0
162480	E123	8887702	201912	B10944	7412.62	222.38	7635.0	1.0
162481	E123	34173085	201912	B13093	1917.47	57.53	1975.0	1.0
162482	E123	8887703	201912	B13093	7252.42	217.58	7470.0	1.0
162483	E123	8887704	201912	B13093	6660.19	199.81	6860.0	1.0

162484 rows × 8 columns

data1.groupby(['企业代号']).agg('count')

	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
企业代号
E1	8110	8110	8110	8110	8110	8110	8110
E10	568	568	568	568	568	568	568
E100	99	99	99	99	99	99	99
E101	63	63	63	63	63	63	63
E102	177	177	177	177	177	177	177
...	...	...	...	...	...	...	...
E95	913	913	913	913	913	913	913
E96	20	20	20	20	20	20	20
E97	264	264	264	264	264	264	264
E98	153	153	153	153	153	153	153
E99	8	8	8	8	8	8	8

123 rows × 7 columns

dd=pd.pivot_table(data1, index=['企业代号','开票日期'],aggfunc=['count'])

#dd.to_excel('dd.xlsx')

data1.groupby(['企业代号'],sort=False).agg('count')

	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
企业代号
E1	8110	8110	8110	8110	8110	8110	8110
E2	12707	12707	12707	12707	12707	12707	12707
E3	24073	24073	24073	24073	24073	24073	24073
E4	2231	2231	2231	2231	2231	2231	2231
E5	1060	1060	1060	1060	1060	1060	1060
...	...	...	...	...	...	...	...
E119	21	21	21	21	21	21	21
E120	29	29	29	29	29	29	29
E121	186	186	186	186	186	186	186
E122	118	118	118	118	118	118	118
E123	65	65	65	65	65	65	65

123 rows × 7 columns

index=data1['企业代号']
index=index.drop_duplicates()
index

0           E1
8110        E2
20817       E3
44890       E4
47121       E5
          ... 
162065    E119
162086    E120
162115    E121
162301    E122
162419    E123
Name: 企业代号, Length: 123, dtype: object

index.iloc[1]

'E2'

count=[0 for i in range(len(index))]
for i in range(len(data1['企业代号'])):
    for j in range(len(index)):
        if index.iloc[j] == data1['企业代号'][i]:
            count[j]+=1
            continue
count

[8110,
 12707,
 24073,
 2231,
 1060,
 1052,
 8149,
 9425,
 5906,
 568,
 1117,
 285,
 8005,
 3336,
 2412,
 439,
 676,
 381,
 3099,
 777,
 1466,
 1562,
 3570,
 1782,
 978,
 504,
 1468,
 569,
 475,
 800,
 2608,
 552,
 1073,
 707,
 770,
 1547,
 1273,
 628,
 264,
 883,
 663,
 327,
 245,
 2435,
 325,
 974,
 3518,
 1016,
 513,
 285,
 895,
 379,
 454,
 3501,
 590,
 495,
 438,
 388,
 2149,
 513,
 1992,
 219,
 673,
 1375,
 485,
 679,
 215,
 73,
 587,
 578,
 375,
 248,
 236,
 555,
 8655,
 245,
 258,
 268,
 86,
 48,
 649,
 343,
 706,
 498,
 293,
 22,
 74,
 400,
 172,
 261,
 129,
 39,
 148,
 196,
 913,
 20,
 264,
 153,
 8,
 99,
 63,
 177,
 64,
 21,
 122,
 153,
 54,
 33,
 33,
 83,
 250,
 22,
 47,
 75,
 6,
 47,
 44,
 144,
 21,
 29,
 186,
 118,
 65]

index2=index.drop_duplicates()
index2=index2.reset_index(drop=True)

unitcount=pd.Series(count)
namecop=pd.Series(index2)
df=pd.concat([namecop,unitcount],axis=1)
df.columns=['企业代号','发票数']
df

	企业代号	发票数
0	E1	8110
1	E2	12707
2	E3	24073
3	E4	2231
4	E5	1060
...	...	...
118	E119	21
119	E120	29
120	E121	186
121	E122	118
122	E123	65

123 rows × 2 columns

newdata=data1.set_index('企业代号')#将企业代号作为列索引
newdata

	发票号码	开票日期	购方单位代号	金额	税额	价税合计	发票状态
企业代号
E1	11459356	201708	B03711	9401.71	1598.29	11000.0	1.0
E1	5076239	201708	B00844	8170.94	1389.06	9560.0	1.0
E1	5076240	201708	B00844	8170.94	1389.06	9560.0	1.0
E1	5076241	201708	B00844	4085.47	694.53	4780.0	1.0
E1	5076242	201708	B00844	4085.47	694.53	4780.0	1.0
...	...	...	...	...	...	...	...
E123	8887701	201912	B10944	4827.67	144.83	4972.5	1.0
E123	8887702	201912	B10944	7412.62	222.38	7635.0	1.0
E123	34173085	201912	B13093	1917.47	57.53	1975.0	1.0
E123	8887703	201912	B13093	7252.42	217.58	7470.0	1.0
E123	8887704	201912	B13093	6660.19	199.81	6860.0	1.0

162484 rows × 7 columns

index=index2
index

0        E1
1        E2
2        E3
3        E4
4        E5
       ... 
118    E119
119    E120
120    E121
121    E122
122    E123
Name: 企业代号, Length: 123, dtype: object

df

	企业代号	发票数
0	E1	8110
1	E2	12707
2	E3	24073
3	E4	2231
4	E5	1060
...	...	...
118	E119	21
119	E120	29
120	E121	186
121	E122	118
122	E123	65

123 rows × 2 columns

aa=[]
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['开票日期']
    daytemp=pd.Series(daytemp)
    day_index=daytemp.drop_duplicates()#去重
    a=day_index.count()#计算总共有多少个月份
    aa.append(a)
len(aa)

day=pd.Series(aa)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数']
df

	企业代号	发票数	月数
0	E1	8110	30
1	E2	12707	37
2	E3	24073	38
3	E4	2231	35
4	E5	1060	33
...	...	...	...
118	E119	21	10
119	E120	29	6
120	E121	186	24
121	E122	118	26
122	E123	65	15

123 rows × 3 columns

d#f.drop(df.columns[0], axis=1, inplace=True)    # 删除第1列
#df

0
1
2
3
4
...
162479
162480
162481
162482
162483

162484 rows × 0 columns

#销售单位代号个数
ab=[]
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['购方单位代号']
    daytemp=pd.Series(daytemp)
    day_index=daytemp.drop_duplicates()#去重
    a=day_index.count()#计算总共有多少个月份
    ab.append(a)
len(ab)

day=pd.Series(ab)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数']
df

	企业代号	发票数	月数	购方单位代号个数
0	E1	8110	30	360
1	E2	12707	37	1611
2	E3	24073	38	136
3	E4	2231	35	26
4	E5	1060	33	33
...	...	...	...	...
118	E119	21	10	17
119	E120	29	6	26
120	E121	186	24	123
121	E122	118	26	70
122	E123	65	15	7

123 rows × 4 columns

#价税合计累计
ac=[]
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['价税合计']
    daytemp=pd.Series(daytemp)
    c=daytemp.sum()
    ac.append(c)
len(ac)
day=pd.Series(ac)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数','价税合计累计']
df

	企业代号	发票数	月数	购方单位代号个数	价税合计累计
0	E1	8110	30	360	4.798812e+09
1	E2	12707	37	1611	6.947082e+08
2	E3	24073	38	136	6.827398e+08
3	E4	2231	35	26	2.345631e+09
4	E5	1060	33	33	2.427882e+08
...	...	...	...	...	...
118	E119	21	10	17	3.724200e+04
119	E120	29	6	26	1.963934e+05
120	E121	186	24	123	1.177928e+05
121	E122	118	26	70	8.748906e+04
122	E123	65	15	7	2.671682e+05

123 rows × 5 columns

#负金额个数
ad=[]
d=0
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['金额']
    daytemp=pd.Series(daytemp)
    for j in range(len(daytemp)):
        if daytemp[j]<0:
            d+=1
    ad.append(d)
    d=0
len(ad)

day=pd.Series(ad)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数','价税合计累计','负金额个数']
df

	企业代号	发票数	月数	购方单位代号个数	价税合计累计	负金额个数
0	E1	8110	30	360	4.798812e+09	229
1	E2	12707	37	1611	6.947082e+08	360
2	E3	24073	38	136	6.827398e+08	4501
3	E4	2231	35	26	2.345631e+09	9
4	E5	1060	33	33	2.427882e+08	10
...	...	...	...	...	...	...
118	E119	21	10	17	3.724200e+04	0
119	E120	29	6	26	1.963934e+05	0
120	E121	186	24	123	1.177928e+05	0
121	E122	118	26	70	8.748906e+04	8
122	E123	65	15	7	2.671682e+05	0

123 rows × 6 columns

# 负金额总额
ae=[]
sum=0
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['金额']
    daytemp=pd.Series(daytemp)
    for j in range(len(daytemp)):
        if daytemp[j]<0:
            sum+=daytemp[j]
    ae.append(round(sum,2))
    sum=0
len(ae)
day=pd.Series(ae)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数','价税合计累计','负金额个数','负金额累计']
df

	企业代号	发票数	月数	购方单位代号个数	价税合计累计	负金额个数	负金额累计
0	E1	8110	30	360	4.798812e+09	229	-1.300769e+08
1	E2	12707	37	1611	6.947082e+08	360	-1.909563e+07
2	E3	24073	38	136	6.827398e+08	4501	-2.047618e+08
3	E4	2231	35	26	2.345631e+09	9	-7.221601e+06
4	E5	1060	33	33	2.427882e+08	10	-9.652789e+05
...	...	...	...	...	...	...	...
118	E119	21	10	17	3.724200e+04	0	0.000000e+00
119	E120	29	6	26	1.963934e+05	0	0.000000e+00
120	E121	186	24	123	1.177928e+05	0	0.000000e+00
121	E122	118	26	70	8.748906e+04	8	-1.952390e+03
122	E123	65	15	7	2.671682e+05	0	0.000000e+00

123 rows × 7 columns

#作废发票个数
af=[]
f=0
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['发票状态']
    daytemp=pd.Series(daytemp)
    for j in range(len(daytemp)):
        if daytemp[j]==0:
            f+=1
    af.append(f)
    f=0
len(af)
day=pd.Series(af)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数','价税合计累计','负金额个数','负金额累计','作废发票个数']
df

	企业代号	发票数	月数	购方单位代号个数	价税合计累计	负金额个数	负金额累计	作废发票个数
0	E1	8110	30	360	4.798812e+09	229	-1.300769e+08	224
1	E2	12707	37	1611	6.947082e+08	360	-1.909563e+07	1042
2	E3	24073	38	136	6.827398e+08	4501	-2.047618e+08	385
3	E4	2231	35	26	2.345631e+09	9	-7.221601e+06	190
4	E5	1060	33	33	2.427882e+08	10	-9.652789e+05	55
...	...	...	...	...	...	...	...	...
118	E119	21	10	17	3.724200e+04	0	0.000000e+00	3
119	E120	29	6	26	1.963934e+05	0	0.000000e+00	20
120	E121	186	24	123	1.177928e+05	0	0.000000e+00	23
121	E122	118	26	70	8.748906e+04	8	-1.952390e+03	16
122	E123	65	15	7	2.671682e+05	0	0.000000e+00	32

123 rows × 8 columns

#作废发票金额
ag=[]
gsum=0
for i in index:
    pp=newdata.loc[i]#按照企业代号进行处理
    daytemp=pp['发票状态']
    money=pp['金额']
    daytemp=pd.Series(daytemp)
    for j in range(len(daytemp)):
        if daytemp[j]==0:
            gsum+=money[j]
    ag.append(round(gsum,2))
    gsum=0
len(ag)    
day=pd.Series(ag)
df=pd.concat([df,day],axis=1)
df.columns=['企业代号','发票数','月数','购方单位代号个数','价税合计累计','负金额个数','负金额累计','作废发票个数','作废发票金额累计']
df

	企业代号	发票数	月数	购方单位代号个数	价税合计累计	负金额个数	负金额累计	作废发票个数	作废发票金额累计
0	E1	8110	30	360	4.798812e+09	229	-1.300769e+08	224	8.715034e+07
1	E2	12707	37	1611	6.947082e+08	360	-1.909563e+07	1042	6.454014e+07
2	E3	24073	38	136	6.827398e+08	4501	-2.047618e+08	385	1.860897e+07
3	E4	2231	35	26	2.345631e+09	9	-7.221601e+06	190	1.712102e+08
4	E5	1060	33	33	2.427882e+08	10	-9.652789e+05	55	8.168982e+06
...	...	...	...	...	...	...	...	...	...
118	E119	21	10	17	3.724200e+04	0	0.000000e+00	3	4.854400e+02
119	E120	29	6	26	1.963934e+05	0	0.000000e+00	20	0.000000e+00
120	E121	186	24	123	1.177928e+05	0	0.000000e+00	23	3.184300e+03
121	E122	118	26	70	8.748906e+04	8	-1.952390e+03	16	2.565260e+04
122	E123	65	15	7	2.671682e+05	0	0.000000e+00	32	3.647341e+04

123 rows × 9 columns

df.to_excel('销项数据整理.xlsx')