2020数学建模C题

后来越来越觉得，当时建模做了个shi么…方法搞不清楚怎么用就套，代码，害，老师肯定觉得 熊孩子怎么回事 一个程序用这么多次不知道写个函数都…

程序一.附件一数据处理

1.	#导包  
2.	import pandas as pd    
3.	import numpy as np   
4.	  
5.	#文件  
6.	file_path='123家有信贷记录企业的相关数据及产业分类.xlsx'  
7.	#读取sheet的名字  
8.	sheetName1='企业信息'  
9.	sheetName2='进项发票信息'  
10.	sheetName3='销项发票信息'  
11.	data_1=pd.read_excel(file_path,sheet_name=sheetName1)  
12.	data_2=pd.read_excel(file_path,sheet_name=sheetName2)  
13.	data_3=pd.read_excel(file_path,sheet_name=sheetName3)  
14.	  
15.	# 拷贝一下，老写错代码，导数据真慢  
16.	data_1a=data_1.copy(deep=True)  
17.	data_2a=data_2.copy(deep=True)  
18.	data_3a=data_3.copy(deep=True)  
19.	  
20.	#时间转化为整型以方便比较，分类  
21.	def time_trans(time_ch):    
22.	    year = time_ch.split('-')[0]    
23.	    month = time_ch.split('-')[1]    
24.	    day = time_ch.split('-')[2].split(' ')[0]    
25.	    new_date = (year +  month + day )    
26.	    return int(new_date)  
27.	  
28.	a = list(data_2a['开票日期'])  
29.	for i in range(len(data_2a['开票日期'])):  
30.	    a[i] = str(a[i])  
31.	    a[i] = time_trans(a[i])  
32.	data_2a['开票日期'] = a  
33.	  
34.	# 企业代号转化为整型  
35.	def num_trans(num_ch):   
36.	    num =  num_ch.split('E')[1]  
37.	    return int(num)  
38.	a2 = list(data_2a['企业代号'])  
39.	for i in range(len(a2)):  
40.	    a2[i] = num_trans(a2[i])  
41.	data_2a['企业代号'] = a2  
42.	  
43.	data_21 = data_2a.loc[data_2a['开票日期']<20180101]    #18年之前  
44.	data_22 = data_2a.loc[(data_2a['开票日期']>=20180101) & (data_2a['开票日期']<20190101)]    #18年  
45.	data_23 = data_2a.loc[data_2a['开票日期']>=20190101]    #19年及之后  
46.	# 18年之前  
47.	data_211 = data_21.loc[(data_21['发票状态']=='有效发票') & (data_21['价税合计']>0)]  
48.	data_212 = data_21.loc[data_21['发票状态']=='作废发票']  
49.	data_213 = data_21.loc[(data_21['发票状态']=='有效发票') & (data_21['价税合计']<0)]  
50.	# 18年  
51.	data_221 = data_22.loc[(data_22['发票状态']=='有效发票') & (data_22['价税合计']>0)]  
52.	data_222 = data_22.loc[data_22['发票状态']=='作废发票']  
53.	data_223 = data_22.loc[(data_22['发票状态']=='有效发票') & (data_22['价税合计']<0)]  
54.	# 19年及之后  
55.	data_231 = data_23.loc[(data_23['发票状态']=='有效发票') & (data_23['价税合计']>0)]  
56.	data_232 = data_23.loc[data_23['发票状态']=='作废发票']  
57.	data_233 = data_23.loc[(data_23['发票状态']=='有效发票') & (data_23['价税合计']<0)]  
58.	  
59.	jin211 = [0 for index in range(123)]    #存放18年之前每家企业的进项有效发票金额  
60.	a211 = list(data_211['企业代号']);b211 = list(data_211['价税合计'])  
61.	for i in range(len(a211)):  
62.	    for j in range(123):  
63.	        if a211[i] == j+1:  
64.	            jin211[j] += b211[i]  
65.	  
66.	jin212 = [0 for index in range(123)]    #存放18年之前每家企业的进项作废发票数量  
67.	a212 = list(data_212['企业代号']);b212 = list(data_212['价税合计'])  
68.	for i in range(len(a212)):  
69.	    for j in range(123):  
70.	        if a212[i] == j+1:  
71.	            jin212[j] += 1  
72.	  
73.	jin213 = [0 for index in range(123)]    #存放18年之前每家企业的进项负数发票数量  
74.	a213 = list(data_213['企业代号']);b213 = list(data_213['价税合计'])  
75.	for i in range(len(a213)):  
76.	    for j in range(123):  
77.	        if a213[i] == j+1:  
78.	            jin213[j] += 1  
79.	  
80.	jin221 = [0 for index in range(123)]    #存放18年每家企业的进项有效发票金额  
81.	a221 = list(data_221['企业代号']);b221 = list(data_221['价税合计'])  
82.	for i in range(len(a221)):  
83.	    for j in range(123):  
84.	        if a221[i] == j+1:  
85.	            jin221[j] += b221[i]  
86.	  
87.	jin222 = [0 for index in range(123)]    #存放18年每家企业的进项作废发票数量  
88.	a222 = list(data_222['企业代号']);b222 = list(data_222['价税合计'])  
89.	for i in range(len(a222)):  
90.	    for j in range(123):  
91.	        if a222[i] == j+1:  
92.	            jin222[j] += 1  
93.	  
94.	jin223 = [0 for index in range(123)]    #存放18年每家企业的进项负数发票数量  
95.	a223 = list(data_223['企业代号']);b223 = list(data_223['价税合计'])  
96.	for i in range(len(a223)):  
97.	    for j in range(123):  
98.	        if a223[i] == j+1:  
99.	            jin223[j] += 1  
100.	  
101.	jin231 = [0 for index in range(123)]    #存放19年及之后每家企业的进项有效发票金额  
102.	a231 = list(data_231['企业代号']);b231 = list(data_231['价税合计'])  
103.	for i in range(len(a231)):  
104.	    for j in range(123):  
105.	        if a231[i] == j+1:  
106.	            jin231[j] += b231[i]  
107.	  
108.	jin232 = [0 for index in range(123)]    #存放19年及之后每家企业的进项作废发票数量  
109.	a232 = list(data_232['企业代号']);b232 = list(data_232['价税合计'])  
110.	for i in range(len(a232)):  
111.	    for j in range(123):  
112.	        if a232[i] == j+1:  
113.	            jin232[j] += 1  
114.	  
115.	jin233 = [0 for index in range(123)]    #存放19年及之后每家企业的进项负数发票数量  
116.	a233 = list(data_213['企业代号']);b233 = list(data_233['价税合计'])  
117.	for i in range(len(a233)):  
118.	    for j in range(123):  
119.	        if a233[i] == j+1:  
120.	            jin233[j] += 1  
121.	  
122.	a2 = list(data_3a['企业代号'])  
123.	for i in range(len(a2)):  
124.	    a2[i] = num_trans(a2[i])  
125.	data_3a['企业代号'] = a2  
126.	  
127.	a = list(data_3a['开票日期'])  
128.	for i in range(len(data_3a['开票日期'])):  
129.	    a[i] = str(a[i])  
130.	for i in range(len(a)):  
131.	    a[i] = time_trans(a[i])  
132.	data_3a['开票日期'] = a  
133.	  
134.	data_31 = data_3a.loc[data_3a['开票日期']<20180101]    #18年之前  
135.	data_32 = data_3a.loc[(data_3a['开票日期']>=20180101) & (data_3a['开票日期']<20190101)]    #18年  
136.	data_33 = data_3a.loc[data_3a['开票日期']>=20190101]    #19年及之后  
137.	# 18年之前  
138.	data_311 = data_31.loc[(data_31['发票状态']=='有效发票') & (data_31['价税合计']>0)]  
139.	data_312 = data_31.loc[data_31['发票状态']=='作废发票']  
140.	data_313 = data_31.loc[(data_31['发票状态']=='有效发票') & (data_31['价税合计']<0)]  
141.	# 18年  
142.	data_321 = data_32.loc[(data_32['发票状态']=='有效发票') & (data_32['价税合计']>0)]  
143.	data_322 = data_32.loc[data_32['发票状态']=='作废发票']  
144.	data_323 = data_32.loc[(data_32['发票状态']=='有效发票') & (data_32['价税合计']<0)]  
145.	# 19年及之后  
146.	data_331 = data_33.loc[(data_33['发票状态']=='有效发票') & (data_33['价税合计']>0)]  
147.	data_332 = data_33.loc[data_33['发票状态']=='作废发票']  
148.	data_333 = data_33.loc[(data_33['发票状态']=='有效发票') & (data_33['价税合计']<0)]  
149.	  
150.	jin311 = [0 for index in range(123)]    #存放18年之前每家企业的销项有效发票金额  
151.	a311 = list(data_311['企业代号']);b311 = list(data_311['金额'])  
152.	for i in range(len(a311)):  
153.	    for j in range(123):  
154.	        if a311[i] == j+1:  
155.	            jin311[j] += b311[i]  
156.	  
157.	jin312 = [0 for index in range(123)]    #存放18年之前每家企业的销项作废发票数量  
158.	a312 = list(data_312['企业代号']);b312 = list(data_312['金额'])  
159.	for i in range(len(a312)):  
160.	    for j in range(123):  
161.	        if a312[i] == j+1:  
162.	            jin312[j] += 1  
163.	  
164.	jin313 = [0 for index in range(123)]    #存放18年之前每家企业的销项负数发票数量  
165.	a313 = list(data_313['企业代号']);b313 = list(data_313['金额'])  
166.	for i in range(len(a313)):  
167.	    for j in range(123):  
168.	        if a313[i] == j+1:  
169.	            jin313[j] += 1  
170.	  
171.	jin321 = [0 for index in range(123)]    #存放18年每家企业的销项有效发票金额  
172.	a321 = list(data_321['企业代号']);b321 = list(data_321['金额'])  
173.	for i in range(len(a321)):  
174.	    for j in range(123):  
175.	        if a321[i] == j+1:  
176.	            jin321[j] += b321[i]  
177.	  
178.	jin322 = [0 for index in range(123)]    #存放18年每家企业的销项作废发票数量  
179.	a322 = list(data_322['企业代号']);b322 = list(data_322['金额'])  
180.	for i in range(len(a322)):  
181.	    for j in range(123):  
182.	        if a322[i] == j+1:  
183.	            jin322[j] += 1  
184.	  
185.	jin323 = [0 for index in range(123)]    #存放18年每家企业的销项负数发票数量  
186.	a323 = list(data_323['企业代号']);b323 = list(data_323['金额'])  
187.	for i in range(len(a323)):  
188.	    for j in range(123):  
189.	        if a323[i] == j+1:  
190.	            jin323[j] += 1  
191.	  
192.	jin331 = [0 for index in range(123)]    #存放19年及之后每家企业的销项有效发票金额  
193.	a331 = list(data_331['企业代号']);b331 = list(data_331['金额'])  
194.	for i in range(len(a331)):  
195.	    for j in range(123):  
196.	        if a331[i] == j+1:  
197.	            jin331[j] += b331[i]  
198.	  
199.	jin332 = [0 for index in range(123)]    #存放19年及之后每家企业的销项作废发票数量  
200.	a332 = list(data_332['企业代号']);b332 = list(data_332['金额'])  
201.	for i in range(len(a332)):  
202.	    for j in range(123):  
203.	        if a332[i] == j+1:  
204.	            jin332[j] += 1  
205.	  
206.	jin333 = [0 for index in range(123)]    #存放19年及之后每家企业的销项负数发票数量  
207.	a333 = list(data_333['企业代号']);b333 = list(data_333['金额'])  
208.	for i in range(len(a333)):  
209.	    for j in range(123):  
210.	        if a333[i] == j+1:  
211.	            jin333[j] += 1  
212.	  
213.	a2 = list(data_1a['企业代号'])  
214.	for i in range(len(a2)):  
215.	    a2[i] = num_trans(a2[i])  
216.	data_1a['企业代号'] = a2  
217.	  
218.	# 根据企业代号排序  
219.	data_1a = data_1a.sort_values(['企业代号'], ascending = True)  
220.	  
221.	#精度问题，四舍五入保留两位小数  
222.	from decimal import Decimal, ROUND_HALF_UP  
223.	def round_up(number, num_digits):       
224.	    """ 
225.	    按指定位数对数值进行四舍五入。 
226.	    :param number:要四舍五入的数字。 
227.	    :param num_digits:要进行四舍五入运算的位数。 
228.	    """  
229.	    if num_digits > 0:  
230.	        res = round_up(number * 10, num_digits - 1) / 10  
231.	    else:  
232.	        res = Decimal(number * 10 ** num_digits).quantize(Decimal('1'), rounding=ROUND_HALF_UP) * 10 ** -num_digits  
233.	    return res  
234.	  
235.	jin21 = [0 for index in range(123)];jin22 = [0 for index in range(123)];jin23 = [0 for index in range(123)]  
236.	jin31 = [0 for index in range(123)];jin32 = [0 for index in range(123)];jin33 = [0 for index in range(123)]  
237.	for i in range(123):  
238.	    jin21[i] = jin211[i]*0.2 +jin221[i]*0.3 + jin231[i]*0.5 #进项发票金额  
239.	    jin22[i] = jin212[i]*0.2 +jin222[i]*0.3 + jin232[i]*0.5 + jin312[i]*0.2 +jin322[i]*0.3 + jin332[i]*0.5 #作废发票数量  
240.	    jin23[i] = jin213[i]*0.2 +jin223[i]*0.3 + jin233[i]*0.5 + jin313[i]*0.2 +jin323[i]*0.3 + jin333[i]*0.5 #负数发票数量  
241.	    jin31[i] = jin311[i]*0.2 +jin321[i]*0.3 + jin331[i]*0.5 #销项发票金额  
242.	for i in range(123):  
243.	    jin21[i] = round_up(jin21[i],2)  
244.	    jin31[i] = round_up(jin31[i],2)  
245.	  
246.	data_1a['进项发票金额'] = jin21  
247.	data_1a['作废发票数量'] = jin22  
248.	data_1a['负数发票数量'] = jin23  
249.	data_1a['销项发票金额'] = jin31  
250.	  
251.	# 进销平衡  
252.	ph1 = [0 for index in range(123)];ph2 = [0 for index in range(123)];ph3 = [0 for index in range(123)];ph = [0 for index in range(123)]  
253.	for i in range(len(ph1)):  
254.	    ph1[i] = jin311[i]-jin211[i]  
255.	    ph2[i] = jin321[i]-jin221[i]  
256.	    ph3[i] = jin331[i]-jin231[i]  
257.	    ph[i] = ph1[i]*0.2+ph2[i]*0.3+ph3[i]*0.5  
258.	for i in range(123):  
259.	    ph1[i] = round_up(ph1[i],2)  
260.	    ph2[i] = round_up(ph2[i],2)  
261.	    ph3[i] = round_up(ph3[i],2)  
262.	    ph[i] = round_up(ph[i],2)  
263.	data_1a['盈余'] = ph  
264.	  
265.	# 信誉评级量化  
266.	level = list(data_1a['信誉评级'])  
267.	for i in range(len(level)):  
268.	    if level[i] == 'A':  
269.	        level[i] = 0.95  
270.	    if level[i] == 'B':  
271.	        level[i] = 0.7   
272.	    if level[i] == 'C':  
273.	        level[i] = 0.5   
274.	    if level[i] == 'D':  
275.	        level[i] = 0  
276.	data_1a['信誉评级']=level  
277.	  
278.	# 行业划分量化影响力  
279.	a = list(data_1a['企业类别'])  
280.	for i in range(len(a)):  
281.	    if a[i] == 1:   
282.	        a[i] = 0.15    
283.	    if a[i] == 2:    
284.	        a[i] = 0.3    
285.	    if a[i] == 3:  
286.	        a[i] = 0.55    
287.	data_1a['企业类别'] = a    
288.	  
289.	data_1a.rename(columns={'企业类别':'影响力'}, inplace = True)  
290.	  
291.	# 极小型指标转化为极大型指标  
292.	max(data_1a['作废发票数量'])#660.0  
293.	max(data_1a['负数发票数量'])#1450.7  
294.	data_1a['作废发票数量'] = 660.0-data_1a['作废发票数量']   
295.	data_1a['负数发票数量'] = 1450.70-data_1a['负数发票数量']   
296.	  
297.	#保存文件  
298.	data_1a.to_excel('附件一处理后的数据.xlsx')  

程序二.问题一模型建立

1.	import numpy as np    
2.	import pandas as pd  
3.	df_=pd.read_excel('附件一标准化后的数据.xlsx')  
4.	  
5.	df1 = df_.loc[:,['Z作废发票数量','Z负数发票数量','Z影响力','Z盈余','Z信誉评级']]  
6.	df1.columns = [0,1,2,3,4]  
7.	  
8.	# 信誉得分权重  
9.	from scipy.sparse.linalg import eigs  
10.	from numpy import array, hstack  
11.	a=array([[1,1/2,1/4,1/7,1/6],[2,1,1/3,1/6,1/5],[4,3,1,1/5,1/4],  
12.	        [7,6,5,1,3],[6,5,4,1/3,1]])  
13.	L,V=eigs(a,1);  
14.	CR=(L-5)/4/1.12  #计算矩阵A的一致性比率  
15.	W=V/sum(V); print("最大特征值为：",L)  
16.	print("最大特征值对应的特征向量W=\n",W)  
17.	print("CR=",CR)  
18.	  
19.	# 信誉得分权重  
20.	a = np.array([[1,1/2,1/4,1/7,1/6],[2,1,1/3,1/6,1/5],[4,3,1,1/5,1/4],  
21.	        [7,6,5,1,3],[6,5,4,1/3,1]])  
22.	a1 = np.zeros((5,5))  
23.	for i in range(len(a)):  
24.	    for j in range(len(a[i])):  
25.	        a1[i][j] = a[i][j]/(a[0][j]+a[1][j]+a[2][j]+a[3][j]+a[4][j])  
26.	print('a1:{}'.format(a1))  
27.	  
28.	v = np.zeros(5)  
29.	for i in range(len(a1)):  
30.	    v[i] = sum(a1[i])  
31.	print('v:{}'.format(v))  
32.	  
33.	w = np.zeros(5)  
34.	for i in range(len(a1)):  
35.	    w[i] = v[i]/sum(v)  
36.	w = w.reshape(5,1)  
37.	print('w:{}'.format(w))  
38.	  
39.	aw = np.dot(a,w)  
40.	print('aw:{}'.format(aw))  
41.	  
42.	l = 0  
43.	for i in range(len(aw)):  
44.	    l+=aw[i]/(len(aw)*w[i])  
45.	print('l:{}'.format(l))  
46.	  
47.	ci = (l-len(aw))/(len(aw)-1)  
48.	print('ci:{}'.format(ci))  
49.	  
50.	ri = 1.12  
51.	cr = ci/ri  
52.	print('cr:{}'.format(cr))  
53.	  
54.	w = [0.0439,0.0660,0.1301,0.4804,0.2796]  
55.	score = [0  for index in range(123)]  
56.	for j in range(len(w)):  
57.	    for i in range(123):  
58.	        score[i] += df1[j][i]*w[j]  
59.	  
60.	sum(w)   #检验权重之和为1  
61.	  
62.	df_['信贷安全得分'] = score  
63.	  
64.	# 删除信誉评级为D的客户并查看还有多少客户量  
65.	df = df_.drop(df_[(df_['信誉评级']==0)].index)  
66.	df.shape    #99  
67.	  
68.	# 信誉得分  
69.	score1 = [0 for i in range(99)]  
70.	score2 = list(df['信誉评级'])  
71.	for i in range(len(score1)):  
72.	    if score2[i] == 0.95:  
73.	        score1[i] = 0.0465   
74.	    if score2[i] == 0.7 or score2[i] == 0.5:  
75.	        score1[i] = 0.0585   
76.	#     if score2[i] == 0:   
77.	#         score1[i] = nan  
78.	df['信誉年利率'] = score1  
79.	  
80.	# 信贷安全年利率  
81.	df2 = df.sort_values(by='信贷安全得分',ascending=False)  
82.	  
83.	# 利率函数计算  
84.	from sympy import *    
85.	x = symbols('x')  
86.	y = (x+0.304746)*((0.04-0.15)/(0.909247+0.304746))+0.15  
87.	  
88.	# 根据利率函数插值  
89.	x1 = list(df2['信贷安全得分'])  
90.	y1 = [0 for index in range(len(x1))]  
91.	for i in range(len(y1)):  
92.	    y1[i] = y.subs(x,x1[i])  
93.	df2['信贷安全年利率'] = y1  
94.	  
95.	# 利率   保留4位小数（看附件三是四位小数）  
96.	rate = [0 for index in range(99)]  
97.	rate1 = list(df2['信誉年利率']);rate2 = list(df2['信贷安全年利率'])  
98.	for i in range(99):  
99.	    rate[i] = rate1[i]*0.25+rate2[i]*0.75   
100.	    rate[i] = round(rate[i],4)  
101.	  
102.	#E1，E4与实际差距较大，故而单独分析  
103.	rate[0] = 0.09   
104.	df2['年利率'] = rate   
105.	df2['年利率'] = df2['年利率']*100  
106.	df2.rename(columns={'年利率':'年利率（%）'}, inplace = True)  
107.	  
108.	df3 = df.loc[:,['Z信誉评级','Z盈余','Z进项发票金额']]  
109.	df3.columns = [0,1,2]  
110.	  
111.	# 信誉得分权重  
112.	a = np.array([[1,3,1/2],[1/3,1,1/5],[2,5,1]])  
113.	a1 = np.zeros((3,3))  
114.	for i in range(len(a)):  
115.	    for j in range(len(a[i])):  
116.	        a1[i][j] = a[i][j]/(a[0][j]+a[1][j]+a[2][j])  
117.	print('a1:{}'.format(a1))  
118.	  
119.	v = np.zeros(3)  
120.	for i in range(len(a1)):  
121.	    v[i] = sum(a1[i])  
122.	print('v:{}'.format(v))  
123.	  
124.	w = np.zeros(3)  
125.	for i in range(len(a1)):  
126.	    w[i] = v[i]/sum(v)  
127.	w = w.reshape(3,1)  
128.	print('w:{}'.format(w))  
129.	  
130.	aw = np.dot(a,w)  
131.	print('aw:{}'.format(aw))  
132.	  
133.	l = 0  
134.	for i in range(len(aw)):  
135.	    l+=aw[i]/(len(aw)*w[i])  
136.	print('l:{}'.format(l))  
137.	  
138.	ci = (l-len(aw))/(len(aw)-1)  
139.	print('ci:{}'.format(ci))  
140.	  
141.	ri = 1.12  
142.	cr = ci/ri  
143.	print('cr:{}'.format(cr))  
144.	  
145.	# 额度得分  
146.	w = [0.3092,0.1096,0.5812]  
147.	score = [0  for index in range(99)]  
148.	for j in range(len(w)):  
149.	    for i in range(99):  
150.	        score[i] += df1[j][i]*w[j]  
151.	for i in range(len(score)):  
152.	    score[i]+=1.4981055297610921  
153.	  
154.	df2['额度得分'] = score   
155.	  
156.	sum(w) #检验权重相加=1  
157.	  
158.	# 按额度得分排序  
159.	df3 = df2.sort_values(by='额度得分',ascending=False)  
160.	  
161.	# 企业  
162.	x = []  
163.	for i in range(1,100):  
164.	    x.append(i)  
165.	# 额度得分  
166.	y = list(df3['额度得分'])  
167.	  
168.	#绘图   #我的编译器这块代码需要运行两次才能出图  
169.	import matplotlib.pyplot as plt  
170.	import matplotlib  
171.	from matplotlib import font_manager  
172.	my_font=font_manager.FontProperties(fname='C:\Windows\Fonts\msyh.ttc',size=10)   #fname字体路径，本电脑  
173.	plt.plot(x,y)  
174.	#绘制网格  
175.	plt.grid(alpha=0.4)  
176.	#标题     
177.	plt.xlabel('企业代号',fontproperties=my_font)  
178.	plt.ylabel('额度得分',fontproperties=my_font)  
179.	plt.title('各企业额度得分',fontproperties=my_font,color='black',size=15)  
180.	#显示  
181.	plt.show()  
182.	  
183.	# 额度  
184.	M = symbols('M')  
185.	y_sum = sum(y)  
186.	y1 = [0 for index in range(99)]  
187.	for i in range(len(y)):  
188.	    y1[i] = round(y[i]/y_sum,4)*M  
189.	  
190.	df3['额度（万）'] = y1  
191.	  
192.	df4 = df3.loc[:,['企业代号','企业名称','年利率（%）','额度（万）']]  
193.	df4 = df3.loc[:,['企业代号','信誉年利率','信贷安全年利率','额度得分','年利率（%）','额度（万）']]  
194.	df4 = pd.merge(df_,df4,how = 'left',on='企业代号')    
195.	df4.fillna(0)  
196.	  
197.	df4.to_excel('123家企业各项指标及得分.xlsx')  
198.	  
199.	# 结果  
200.	df5 = df4.loc[:,['企业代号','企业名称','年利率（%）','额度（万）']]  
201.	df5.to_excel('针对123家企业的贷款策略.xlsx')  

程序三.附件二数据处理

1.	#导包  
2.	import pandas as pd    
3.	import numpy as np    
4.	  
5.	#文件  
6.	file_path='302家无信贷记录企业的相关数据及产业分类.xlsx'  
7.	#读取sheet的名字  
8.	sheetName1='企业信息'  
9.	sheetName2='进项发票信息'  
10.	sheetName3='销项发票信息'  
11.	data_1=pd.read_excel(file_path,sheet_name=sheetName1)  
12.	data_2=pd.read_excel(file_path,sheet_name=sheetName2)  
13.	data_3=pd.read_excel(file_path,sheet_name=sheetName3)  
14.	  
15.	# 拷贝一下，老写错代码，导数据真慢  
16.	data_1a=data_1.copy(deep=True)  
17.	data_2a=data_2.copy(deep=True)  
18.	data_3a=data_3.copy(deep=True)  
19.	  
20.	#时间转化为整型以方便比较，分类  
21.	def time_trans(time_ch):    
22.	    year = time_ch.split('-')[0]    
23.	    month = time_ch.split('-')[1]    
24.	    day = time_ch.split('-')[2].split(' ')[0]    
25.	    new_date = (year +  month + day )    
26.	    return int(new_date)  
27.	  
28.	a = list(data_2a['开票日期'])  
29.	for i in range(len(data_2a['开票日期'])):  
30.	    a[i] = str(a[i])  
31.	    a[i] = time_trans(a[i])  
32.	data_2a['开票日期'] = a  
33.	  
34.	# 企业代号转化为整型  
35.	def num_trans(num_ch):   
36.	    num =  num_ch.split('E')[1]  
37.	    return int(num)  
38.	a2 = list(data_2a['企业代号'])  
39.	for i in range(len(a2)):  
40.	    a2[i] = num_trans(a2[i])  
41.	  
42.	data_2a['企业代号'] = a2  
43.	  
44.	data_21 = data_2a.loc[data_2a['开票日期']<20180101]    #18年之前  
45.	data_22 = data_2a.loc[(data_2a['开票日期']>=20180101) & (data_2a['开票日期']<20190101)]    #18年  
46.	data_23 = data_2a.loc[data_2a['开票日期']>=20190101]    #19年及之后  
47.	# 18年之前  
48.	data_211 = data_21.loc[(data_21['发票状态']=='有效发票') & (data_21['价税合计']>0)]  
49.	data_212 = data_21.loc[data_21['发票状态']=='作废发票']  
50.	data_213 = data_21.loc[(data_21['发票状态']=='有效发票') & (data_21['价税合计']<0)]  
51.	# 18年  
52.	data_221 = data_22.loc[(data_22['发票状态']=='有效发票') & (data_22['价税合计']>0)]  
53.	data_222 = data_22.loc[data_22['发票状态']=='作废发票']  
54.	data_223 = data_22.loc[(data_22['发票状态']=='有效发票') & (data_22['价税合计']<0)]  
55.	# 19年及之后  
56.	data_231 = data_23.loc[(data_23['发票状态']=='有效发票') & (data_23['价税合计']>0)]  
57.	data_232 = data_23.loc[data_23['发票状态']=='作废发票']  
58.	data_233 = data_23.loc[(data_23['发票状态']=='有效发票') & (data_23['价税合计']<0)]  
59.	  
60.	jin211 = [0 for index in range(302)]    #存放18年之前每家企业的进项有效发票金额  
61.	a211 = list(data_211['企业代号']);b211 = list(data_211['价税合计'])  
62.	for i in range(len(a211)):  
63.	    for j in range(302):  
64.	        if a211[i] == j+124:  
65.	            jin211[j] += b211[i]  
66.	  
67.	jin212 = [0 for index in range(302)]    #存放18年之前每家企业的进项作废发票数量  
68.	a212 = list(data_212['企业代号']);b212 = list(data_212['价税合计'])  
69.	for i in range(len(a212)):  
70.	    for j in range(302):  
71.	        if a212[i] == j+124:  
72.	            jin212[j] += 1  
73.	  
74.	jin213 = [0 for index in range(302)]    #存放18年之前每家企业的进项负数发票数量  
75.	a213 = list(data_213['企业代号']);b213 = list(data_213['价税合计'])  
76.	for i in range(len(a213)):  
77.	    for j in range(302):  
78.	        if a213[i] == j+124:  
79.	            jin213[j] += 1  
80.	  
81.	jin221 = [0 for index in range(302)]    #存放18年每家企业的进项有效发票金额  
82.	a221 = list(data_221['企业代号']);b221 = list(data_221['价税合计'])  
83.	for i in range(len(a221)):  
84.	    for j in range(302):  
85.	        if a221[i] == j+124:  
86.	            jin221[j] += b221[i]  
87.	  
88.	jin222 = [0 for index in range(302)]    #存放18年每家企业的进项作废发票数量  
89.	a222 = list(data_222['企业代号']);b222 = list(data_222['价税合计'])  
90.	for i in range(len(a222)):  
91.	    for j in range(302):  
92.	        if a222[i] == j+124:  
93.	            jin222[j] += 1  
94.	  
95.	jin223 = [0 for index in range(302)]    #存放18年每家企业的进项负数发票数量  
96.	a223 = list(data_223['企业代号']);b223 = list(data_223['价税合计'])  
97.	for i in range(len(a223)):  
98.	    for j in range(302):  
99.	        if a223[i] == j+124:  
100.	            jin223[j] += 1  
101.	  
102.	jin231 = [0 for index in range(302)]    #存放19年及之后每家企业的进项有效发票金额  
103.	a231 = list(data_231['企业代号']);b231 = list(data_231['价税合计'])  
104.	for i in range(len(a231)):  
105.	    for j in range(302):  
106.	        if a231[i] == j+124:  
107.	            jin231[j] += b231[i]  
108.	  
109.	jin232 = [0 for index in range(302)]    #存放19年及之后每家企业的进项作废发票数量  
110.	a232 = list(data_232['企业代号']);b232 = list(data_232['价税合计'])  
111.	for i in range(len(a232)):  
112.	    for j in range(302):  
113.	        if a232[i] == j+124:  
114.	            jin232[j] += 1  
115.	  
116.	jin233 = [0 for index in range(302)]    #存放19年及之后每家企业的进项负数发票数量  
117.	a233 = list(data_213['企业代号']);b233 = list(data_233['价税合计'])  
118.	for i in range(len(a233)):  
119.	    for j in range(302):  
120.	        if a233[i] == j+124:  
121.	            jin233[j] += 1  
122.	  
123.	a2 = list(data_3a['企业代号'])  
124.	for i in range(len(a2)):  
125.	    a2[i] = num_trans(a2[i])  
126.	  
127.	data_3a['企业代号'] = a2  
128.	  
129.	a = list(data_3a['开票日期'])  
130.	for i in range(len(data_3a['开票日期'])):  
131.	    a[i] = str(a[i])  
132.	  
133.	for i in range(len(a)):  
134.	    a[i] = time_trans(a[i])  
135.	data_3a['开票日期'] = a  
136.	  
137.	data_31 = data_3a.loc[data_3a['开票日期']<20180101]    #18年之前  
138.	data_32 = data_3a.loc[(data_3a['开票日期']>=20180101) & (data_3a['开票日期']<20190101)]    #18年  
139.	data_33 = data_3a.loc[data_3a['开票日期']>=20190101]    #19年及之后  
140.	# 18年之前  
141.	data_311 = data_31.loc[(data_31['发票状态']=='有效发票') & (data_31['价税合计']>0)]  
142.	data_312 = data_31.loc[data_31['发票状态']=='作废发票']  
143.	data_313 = data_31.loc[(data_31['发票状态']=='有效发票') & (data_31['价税合计']<0)]  
144.	# 18年  
145.	data_321 = data_32.loc[(data_32['发票状态']=='有效发票') & (data_32['价税合计']>0)]  
146.	data_322 = data_32.loc[data_32['发票状态']=='作废发票']  
147.	data_323 = data_32.loc[(data_32['发票状态']=='有效发票') & (data_32['价税合计']<0)]  
148.	# 19年及之后  
149.	data_331 = data_33.loc[(data_33['发票状态']=='有效发票') & (data_33['价税合计']>0)]  
150.	data_332 = data_33.loc[data_33['发票状态']=='作废发票']  
151.	data_333 = data_33.loc[(data_33['发票状态']=='有效发票') & (data_33['价税合计']<0)]  
152.	  
153.	jin311 = [0 for index in range(302)]    #存放18年之前每家企业的销项有效发票金额  
154.	a311 = list(data_311['企业代号']);b311 = list(data_311['金额'])  
155.	for i in range(len(a311)):  
156.	    for j in range(302):  
157.	        if a311[i] == j+124:  
158.	            jin311[j] += b311[i]  
159.	  
160.	jin312 = [0 for index in range(302)]    #存放18年之前每家企业的销项作废发票数量  
161.	a312 = list(data_312['企业代号']);b312 = list(data_312['金额'])  
162.	for i in range(len(a312)):  
163.	    for j in range(302):  
164.	        if a312[i] == j+124:  
165.	            jin312[j] += 1  
166.	  
167.	jin313 = [0 for index in range(302)]    #存放18年之前每家企业的销项负数发票数量  
168.	a313 = list(data_313['企业代号']);b313 = list(data_313['金额'])  
169.	for i in range(len(a313)):  
170.	    for j in range(302):  
171.	        if a313[i] == j+124:  
172.	            jin313[j] += 1  
173.	  
174.	jin321 = [0 for index in range(302)]    #存放18年每家企业的销项有效发票金额  
175.	a321 = list(data_321['企业代号']);b321 = list(data_321['金额'])  
176.	for i in range(len(a321)):  
177.	    for j in range(302):  
178.	        if a321[i] == j+124:  
179.	            jin321[j] += b321[i]  
180.	  
181.	jin322 = [0 for index in range(302)]    #存放18年每家企业的销项作废发票数量  
182.	a322 = list(data_322['企业代号']);b322 = list(data_322['金额'])  
183.	for i in range(len(a322)):  
184.	    for j in range(302):  
185.	        if a322[i] == j+124:  
186.	            jin322[j] += 1  
187.	  
188.	jin323 = [0 for index in range(302)]    #存放18年每家企业的销项负数发票数量  
189.	a323 = list(data_323['企业代号']);b323 = list(data_323['金额'])  
190.	for i in range(len(a323)):  
191.	    for j in range(302):  
192.	        if a323[i] == j+124:  
193.	            jin323[j] += 1  
194.	  
195.	jin331 = [0 for index in range(302)]    #存放19年及之后每家企业的销项有效发票金额  
196.	a331 = list(data_331['企业代号']);b331 = list(data_331['金额'])  
197.	for i in range(len(a331)):  
198.	    for j in range(302):  
199.	        if a331[i] == j+124:  
200.	            jin331[j] += b331[i]  
201.	  
202.	jin332 = [0 for index in range(302)]    #存放19年及之后每家企业的销项作废发票数量  
203.	a332 = list(data_332['企业代号']);b332 = list(data_332['金额'])  
204.	for i in range(len(a332)):  
205.	    for j in range(302):  
206.	        if a332[i] == j+124:  
207.	            jin332[j] += 1  
208.	  
209.	jin333 = [0 for index in range(302)]    #存放19年及之后每家企业的销项负数发票数量  
210.	a333 = list(data_333['企业代号']);b333 = list(data_333['金额'])  
211.	for i in range(len(a333)):  
212.	    for j in range(302):  
213.	        if a333[i] == j+124:  
214.	            jin333[j] += 1  
215.	  
216.	a2 = list(data_1a['企业代号'])  
217.	for i in range(len(a2)):  
218.	    a2[i] = num_trans(a2[i])  
219.	  
220.	data_1a['企业代号'] = a2  
221.	  
222.	# 根据企业代号排序  
223.	data_1a = data_1a.sort_values(['企业代号'], ascending = True)  
224.	  
225.	#精度问题，四舍五入保留两位小数  
226.	from decimal import Decimal, ROUND_HALF_UP  
227.	def round_up(number, num_digits):       
228.	    """ 
229.	    按指定位数对数值进行四舍五入。 
230.	    :param number:要四舍五入的数字。 
231.	    :param num_digits:要进行四舍五入运算的位数。 
232.	    """  
233.	    if num_digits > 0:  
234.	        res = round_up(number * 10, num_digits - 1) / 10  
235.	    else:  
236.	        res = Decimal(number * 10 ** num_digits).quantize(Decimal('1'), rounding=ROUND_HALF_UP) * 10 ** -num_digits  
237.	    return res  
238.	  
239.	jin21 = [0 for index in range(302)];jin22 = [0 for index in range(302)];jin23 = [0 for index in range(302)]  
240.	jin31 = [0 for index in range(302)];jin32 = [0 for index in range(302)];jin33 = [0 for index in range(302)]  
241.	for i in range(302):  
242.	    jin21[i] = jin211[i]*0.2 +jin221[i]*0.3 + jin231[i]*0.5 #进项发票金额  
243.	    jin22[i] = jin212[i]*0.2 +jin222[i]*0.3 + jin232[i]*0.5 + jin312[i]*0.2 +jin322[i]*0.3 + jin332[i]*0.5 #作废发票数量  
244.	    jin23[i] = jin213[i]*0.2 +jin223[i]*0.3 + jin233[i]*0.5 + jin313[i]*0.2 +jin323[i]*0.3 + jin333[i]*0.5 #负数发票数量  
245.	    jin31[i] = jin311[i]*0.2 +jin321[i]*0.3 + jin331[i]*0.5 #销项发票金额  
246.	for i in range(302):  
247.	    jin21[i] = round_up(jin21[i],2)  
248.	    jin31[i] = round_up(jin31[i],2)  
249.	  
250.	data_1a['进项发票金额'] = jin21  
251.	data_1a['作废发票数量'] = jin22  
252.	data_1a['负数发票数量'] = jin23  
253.	data_1a['销项发票金额'] = jin31  
254.	  
255.	# 进销平衡  
256.	ph1 = [0 for index in range(302)];ph2 = [0 for index in range(302)];ph3 = [0 for index in range(302)];ph = [0 for index in range(302)]  
257.	for i in range(len(ph1)):  
258.	    ph1[i] = jin311[i]-jin211[i]  
259.	    ph2[i] = jin321[i]-jin221[i]  
260.	    ph3[i] = jin331[i]-jin231[i]  
261.	    ph[i] = ph1[i]*0.2+ph2[i]*0.3+ph3[i]*0.5  
262.	for i in range(302):  
263.	    ph1[i] = round_up(ph1[i],2)  
264.	    ph2[i] = round_up(ph2[i],2)  
265.	    ph3[i] = round_up(ph3[i],2)  
266.	    ph[i] = round_up(ph[i],2)  
267.	data_1a['盈余'] = ph  
268.	  
269.	# 行业划分量化影响力  
270.	a = list(data_1a['企业类别'])  
271.	for i in range(len(a)):  
272.	    if a[i] == 1:   
273.	        a[i] = 0.2    
274.	    if a[i] == 2:    
275.	        a[i] = 0.8    
276.	    if a[i] == 3:  
277.	        a[i] = 0.2   
278.	    if a[i] == 4:   
279.	        a[i] = 0.45    
280.	    if a[i] == 5:    
281.	        a[i] = 0.65    
282.	    if a[i] == 6:  
283.	        a[i] = 0.55   
284.	    if a[i] == 7:   
285.	        a[i] = 0.4    
286.	data_1a['企业类别'] = a    
287.	data_1a.head()  
288.	  
289.	data_1a.rename(columns={'企业类别':'影响力'}, inplace = True)  
290.	  
291.	# 极小型指标转化为极大型指标  
292.	max(data_1a['作废发票数量'])#1077.9  
293.	max(data_1a['负数发票数量'])#179.1  
294.	data_1a['作废发票数量'] = 1077.9-data_1a['作废发票数量']   
295.	data_1a['负数发票数量'] = 179.1-data_1a['负数发票数量']   
296.	  
297.	data_1a.to_excel('附件二处理后的数据.xlsx')  

程序四.问题二模型建立

1.	#导包，文件  
2.	import numpy as np    
3.	import pandas as pd  
4.	df=pd.read_excel('附件二标准化后的数据.xlsx')  
5.	  
6.	df1 = df.loc[:,['Z作废发票数量','Z负数发票数量','Z盈余','Z影响力']]  
7.	df1.columns = [0,1,2,3]  
8.	  
9.	# 信誉得分权重  
10.	a = np.array([[1,1/2,1/7,1/4],[2,1,1/6,1/3],[7,6,1,4],[4,3,1/4,1]])  
11.	a1 = np.zeros((4,4))  
12.	for i in range(len(a)):  
13.	    for j in range(len(a[i])):  
14.	        a1[i][j] = a[i][j]/(a[0][j]+a[1][j]+a[2][j]+a[3][j])  
15.	print('a1:{}'.format(a1))  
16.	  
17.	v = np.zeros(4)  
18.	for i in range(len(a1)):  
19.	    v[i] = sum(a1[i])  
20.	print('v:{}'.format(v))  
21.	  
22.	w = np.zeros(4)  
23.	for i in range(len(a1)):  
24.	    w[i] = v[i]/sum(v)  
25.	w = w.reshape(4,1)  
26.	print('w:{}'.format(w))  
27.	  
28.	aw = np.dot(a,w)  
29.	print('aw:{}'.format(aw))  
30.	  
31.	l = 0  
32.	for i in range(len(aw)):  
33.	    l+=aw[i]/(len(aw)*w[i])  
34.	print('l:{}'.format(l))  
35.	  
36.	ci = (l-len(aw))/(len(aw)-1)  
37.	print('ci:{}'.format(ci))  
38.	  
39.	ri = 1.12  
40.	cr = ci/ri  
41.	print('cr:{}'.format(cr))  
42.	  
43.	w = [0.0639,0.1012,0.6072,0.2277]  
44.	score = [0  for index in range(len(df1[1]))]  
45.	for j in range(len(w)):  
46.	    for i in range(len(df1[1])):  
47.	        score[i] += df1[j][i]*w[j]  
48.	  
49.	sum(w)   #检验权重之和为1  
50.	  
51.	df['信贷安全得分'] = score  
52.	  
53.	# 信誉得分排序  
54.	df2 = df.sort_values(by='信贷安全得分',ascending=False)  
55.	  
56.	# 利率函数计算  
57.	from sympy import *    
58.	x = symbols('x')  
59.	y = (x+1.331047)*((0.04-0.15)/(1.504037+1.331047))+0.15  
60.	  
61.	# 根据利率函数插值  
62.	x1 = list(df2['信贷安全得分'])  
63.	y1 = [0 for index in range(len(x1))]  
64.	for i in range(len(y1)):  
65.	    y1[i] = y.subs(x,x1[i])  
66.	    y1[i] = round(y1[i],4)  
67.	for i in range(len(y1)):  
68.	    if y1[i]<0.04:  
69.	        y1[i] = 0.04   
70.	    if y1[i]>0.15:   
71.	        y1[i] = 0.15  
72.	  
73.	df2['年利率（%）'] = y1  
74.	df2['年利率（%）'] = df2['年利率（%）']*100  
75.	  
76.	df3 = df2.loc[:,['Z盈余','Z进项发票金额']]  
77.	df3.columns = [0,1]  
78.	  
79.	# 额度得分  
80.	w = [0.65,0.35]  
81.	score = [0 for index in range(len(df3))]  
82.	for j in range(len(w)):  
83.	    for i in range(len(df3)):  
84.	        score[i] += df3.iloc[i,j]*w[j]  
85.	  
86.	df2['额度得分'] = score   
87.	  
88.	sum(w) #检验权重相加=1  
89.	  
90.	# 按额度得分排序  
91.	df3 = df2.sort_values(by='额度得分',ascending=False)  
92.	  
93.	score = list(df3['额度得分'])  
94.	# print(score)  
95.	min(score)   #-0.9139382826107219  
96.	for i in range(len(score)):  
97.	    score[i] += 0.9139382826107219   
98.	# score  
99.	limit = [ 0 for i in range(len(score))]  
100.	for i in range(len(score)):  
101.	    if score[i]>3:  
102.	        limit[i] = 100   
103.	# print(score)  
104.	s = sum(score[5:])  
105.	for i in range(5,len(score)):  
106.	    limit[i] = (score[i]/s)*9500  
107.	  
108.	for i in range(len(limit)):  
109.	    if limit[i]>100:  
110.	        limit[i] = 100    
111.	    if limit[i]<10:  
112.	        limit[i] = 0   
113.	    limit[-5] = 10  
114.	  
115.	sum(limit)  #检验总额度  
116.	  
117.	# 企业  
118.	x = []  
119.	for i in range(124,426):  
120.	    x.append(i)  
121.	# 额度得分  
122.	y = list(df3['额度得分'])  
123.	#绘图    
124.	import matplotlib.pyplot as plt  
125.	import matplotlib  
126.	from matplotlib import font_manager  
127.	my_font=font_manager.FontProperties(fname='C:\Windows\Fonts\msyh.ttc',size=10)   #fname字体路径，本电脑  
128.	plt.plot(x,y)  
129.	#绘制网格  
130.	plt.grid(alpha=0.4)  
131.	#标题     
132.	plt.xlabel('企业代号',fontproperties=my_font)  
133.	plt.ylabel('额度得分',fontproperties=my_font)  
134.	plt.title('各企业额度得分',fontproperties=my_font,color='black',size=15)  
135.	#显示  
136.	plt.show()  
137.	  
138.	df3['额度（万）'] = limit  
139.	  
140.	df3.to_excel('302家企业各项指标及得分.xlsx')  
141.	  
142.	# 结果  
143.	df4 = df3.loc[:,['企业代号','企业名称','年利率（%）','额度（万）']]  
144.	df4.to_excel('针对302家企业的贷款策略.xlsx')  

程序五.疫情影响下的信贷策略

1.	import pandas as pd    
2.	import numpy as np   
3.	data = pd.read_excel('302家企业各项指标及得分及细分产业.xlsx')  
4.	  
5.	classify = list(data['产业细分'])  
6.	influence = [0 for index in range(len(classify))]  
7.	add = [0 for index in range(len(classify))]  
8.	for i in range(len(classify)):  
9.	    if classify[i] == 1:    
10.	        influence[i] = -0.028 ; add[i] = 0.034  
11.	    if classify[i] == 2:  
12.	        influence[i] = -0.085 ; add[i] = 0.041  
13.	    if classify[i] == 3:  
14.	        influence[i] = -0.102 ; add[i] = 0.044  
15.	    if classify[i] == 4:  
16.	        influence[i] = -0.175 ; add[i] = 0.078  
17.	    if classify[i] == 5:  
18.	        influence[i] = -0.178  ; add[i] = 0.012  
19.	    if classify[i] == 6:  
20.	        influence[i] = -0.140   ; add[i] = 0.017  
21.	    if classify[i] == 7:  
22.	        influence[i] = -0.353   ; add[i] = -0.18  
23.	    if classify[i] == 8:  
24.	        influence[i] = -0.243  ; add[i] = 0.048  
25.	    if classify[i] == 9:  
26.	        influence[i] = -0.061  ; add[i] = 0.041  
27.	    if classify[i] == 10:  
28.	        influence[i] = 0.132  ; add[i] = 0.057  
29.	    if classify[i] == 11:  
30.	        influence[i] = -0.018  ; add[i] = 0.090  
31.	    if classify[i] == 12:  
32.	        influence[i] = -0.114    ; add[i] = -0.053  
33.	    if classify[i] == 13:  
34.	        influence[i] = -0.094    ; add[i] = -0.080  
35.	    if classify[i] == 14:  
36.	        influence[i] = -0.316   ; add[i] = -0.102  
37.	    if classify[i] == 15:  
38.	        influence[i] = -0.785    ; add[i] = -0.693  
39.	    if classify[i] == 16:  
40.	        influence[i] = -0.028    ; add[i] = 0.034  
41.	    if classify[i] == 17:  
42.	        influence[i] = -2.131   ; add[i] = -1.685  
43.	  
44.	data['波动'] = influence  
45.	data['同期增长'] = add   
46.	  
47.	data['信贷安全得分'] = data['信贷安全得分']*((data['波动']+1)*0.6+(data['同期增长']+1)*0.4)  
48.	data1 = data.sort_values(by='信贷安全得分',ascending=False)  
49.	  
50.	# 利率函数计算  
51.	from sympy import *    
52.	x = symbols('x')  
53.	y = (x+1.211785)*((0.04-0.15)/(1.393039+1.211785))+0.15  
54.	  
55.	# 根据利率函数插值  
56.	x1 = list(data1['信贷安全得分'])  
57.	y1 = [0 for index in range(len(x1))]  
58.	for i in range(len(y1)):  
59.	    y1[i] = y.subs(x,x1[i])  
60.	    y1[i] = round(y1[i],4)  
61.	  
62.	for i in range(len(y1)):  
63.	    if y1[i]<0.0400: y1[i]=0.0400  
64.	    if y1[i]>0.1500: y1[i]=0.1500  
65.	  
66.	data1['年利率'] = y1   
67.	data1['年利率'] = data1['年利率']*100  
68.	data1.rename(columns={'年利率':'年利率（%）'}, inplace = True)  
69.	  
70.	data1['额度得分'] = data1['额度得分']*((data1['波动']+1)*0.6+(data['同期增长']+1)*0.4)  
71.	data2 = data1.sort_values(by='额度得分',ascending=False)  
72.	  
73.	# 企业  
74.	x = []  
75.	for i in range(1,len(y1)+1): x.append(i)  
76.	# 额度得分  
77.	y = list(data2['额度得分'])  
78.	#绘图   #我的编译器这块代码需要运行两次才能出图  
79.	import matplotlib.pyplot as plt  
80.	import matplotlib  
81.	from matplotlib import font_manager  
82.	my_font=font_manager.FontProperties(fname='C:\Windows\Fonts\msyh.ttc',size=10)   #fname字体路径，本电脑  
83.	plt.plot(x,y)  
84.	#绘制网格  
85.	plt.grid(alpha=0.4)  
86.	#标题     
87.	plt.xlabel('企业代号',fontproperties=my_font)  
88.	plt.ylabel('额度得分',fontproperties=my_font)  
89.	plt.title('各企业额度得分',fontproperties=my_font,color='black',size=15)  
90.	#显示  
91.	plt.show()  
92.	  
93.	score = list(data2['额度得分'])  
94.	# print(score)  
95.	# min(score)   #-0.9685167812983371  
96.	for i in range(len(score)):  
97.	    score[i] += 0.9685167812983371  
98.	# score  
99.	limit = [ 0 for i in range(len(score))]  
100.	for i in range(len(score)):  
101.	    if score[i]>4:  
102.	        limit[i] = 100   
103.	s = sum(score[2:])  
104.	for i in range(2,len(score)):  
105.	    limit[i] = (score[i]/s)*9800  
106.	  
107.	for i in range(len(limit)):  
108.	    if limit[i]>100:  limit[i]=100   
109.	    if limit[i]<10:   limit[i]=10  
110.	  
111.	sum(limit)   #检验总额度是否超过100万  
112.	  
113.	data2['额度']=limit   
114.	data2.rename(columns={'额度':'额度（万）'}, inplace = True)  
115.	  
116.	data3 = data2.loc[:,['企业代号','企业名称','产业细分','波动','同期增长','年利率（%）','额度（万）']]  
117.	data3['波动'] = data3['波动']*100   
118.	data3.rename(columns={'波动':'波动（%）'}, inplace = True)   
119.	data3['同期增长'] = data3['同期增长']*100   
120.	data3.rename(columns={'同期增长':'同期增长（%）'}, inplace = True)   
121.	  
122.	data3.to_excel('疫情影响下针对302家企业的贷款策略.xlsx')  

思路有时间再写