【练习笔记(第一次)】2020年数学建模国赛C题:数据处理、源代码

前言

本来因为ACM竞赛培训的原因没有参加数学建模的培训和系列赛事,开学被同学拉过来凑数,就这样阴差阳错的参加了一次数学建模国赛。由于没有系统的学习数学建模,还是第一次接触数学建模正式赛事,所以论文写的比较水。主要用到的方法都是烂大街的没有新意的方法,都是我在平时做数据练习时的一些偏机器学习的模型。前期想着队长能有什么模型想出来,结果队长啥也没想出来只好自己上,导致正式开始动笔建模时已经比较晚。其实论文还有很多可以改进的地方,比如没有查阅足够的文献资料,综合前人的结果,其实前人已经做出一些信用风险评估模型,其次集成学习比较仓促,没有分配分类器权重系数,时间够的话还应该可以做一下信用迁移矩阵,将题目中的时间用到。但最终还是写出了一个可行但并非最优解,也算完成论文。
最近好多童鞋在问我要论文,这里统一回复一下,当时做的实在太烂了,没有留下相关资源
2020年国赛明令提出不能使用topsis等算法,我使用了,其实做的很烂

题目

在这里插入图片描述

摘要

在这里插入图片描述

说明

详细论文、程序源码和数据

请见代码仓库,过一阵子放

数据预处理

据说这个部分让很多队伍头痛,其实也不是很难,不用很多时间。主要是根据发票信息对每个公司进行统计,我们算出了一些指标,以便以后使用,由于时间原因,没有处理发票中的时间信息。如果时间足够的话,其实还可以算一下信用迁移矩阵。详细程序见下。

综合评价系统

我们用的是Topsis+熵权法,其实综合评价系统有很多种,应该针对不同的数据区别使用,但笔者没系统学过建模,也是现学现用。读者可以尝试使用其他评价系统,或者尝试一些现有信用风险模型,谷歌学术搜一搜就可。

分类系统

使用kmeans分类,我也只知道这个,太菜了╯﹏╰。论文里分了九类,是根据聚类效果评价算法得出来的最佳聚类簇个数,其实3个也就够了的。

程序

1.问题1

数据读取

#导入相关库
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from scipy.stats import zscore
from sklearn.decomposition import PCA
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
#读取数据
data = pd.read_excel('a0.xlsx')
data2 =pd.read_excel('a0.xlsx',sheet_name=1)
data3 =pd.read_excel('a0.xlsx',sheet_name=2)
#将企业代号前的E去掉,使其成为数值型变量,便于排序
data['企业代号']=data['企业代号'].apply(lambda x:int(x[1:]))
data2['企业代号']=data2['企业代号'].apply(lambda x:int(x[1:]))
data3['企业代号']=data3['企业代号'].apply(lambda x:int(x[1:]))

数据概览

data
企业代号企业名称信誉评级是否违约
01***电器销售有限公司A
12***技术有限责任公司A
23***电子(中国)有限公司***分公司C
34***发展有限责任公司C
45***供应链管理有限公司B
...............
118119***药房D
119120***陈列广告有限公司D
120121***药业连锁有限公司***药店D
121122***商贸有限责任公司D
122123***创科技有限责任公司D

123 rows × 4 columns

data2
企业代号发票号码开票日期销方单位代号金额税额价税合计发票状态
0133909392017-07-18A00297-943.40-56.60-1000.00有效发票
1133909402017-07-18A00297-4780.24-286.81-5067.05有效发票
2133909412017-07-18A00297943.4056.601000.00有效发票
3133909422017-07-18A002974780.24286.815067.05有效发票
4199026692017-08-07A05061326.219.79336.00有效发票
...........................
210942122547062342019-04-17A08967223.306.70230.00有效发票
210943122557213442020-01-10A09184264.1515.85280.00有效发票
210944123384932952017-12-15A03624264.1515.85280.00有效发票
210945123954720012018-12-29A03626264.1515.85280.00有效发票
210946123544698832019-12-18A03626264.1515.85280.00有效发票

210947 rows × 8 columns

data3
企业代号发票号码开票日期购方单位代号金额税额价税合计发票状态
01114593562017-08-04B037119401.711598.2911000.0有效发票
1150762392017-08-09B008448170.941389.069560.0有效发票
2150762402017-08-09B008448170.941389.069560.0有效发票
3150762412017-08-09B008444085.47694.534780.0有效发票
4150762422017-08-09B008444085.47694.534780.0有效发票
...........................
16247912388877012019-12-17B109444827.67144.834972.5有效发票
16248012388877022019-12-17B109447412.62222.387635.0有效发票
162481123341730852019-12-17B130931917.4757.531975.0有效发票
16248212388877032019-12-25B130937252.42217.587470.0有效发票
16248312388877042019-12-25B130936660.19199.816860.0有效发票

162484 rows × 8 columns

数据预处理

#计算有效发票、负数发票和作废发票数量
def cnt(a,b,df):
    tot=0
    for i in range(a,b):
        if df.iloc[i,6]<0 and df.iloc[i,7] == '有效发票':
            tot+=1 
    if(len(df.iloc[a:b,:]['发票状态'].value_counts())==1):
        return  df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],0,tot
    return df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],df.iloc[a:b,:]['发票状态'].value_counts()['作废发票'],tot
#计算有效金额,有效税额,作废额
def cnt2(a,b,df):
    zf=0
    yxj=0
    yxs=0
    for i in range(a,b):
        if df.iloc[i,7] == '作废发票':
            zf+=df.iloc[i,6]
        else:
            yxj+=df.iloc[i,4]
            yxs+=df.iloc[i,5]
    return yxj,yxs,zf
#对进项发票进行统计
checkin=pd.DataFrame(data2['企业代号'].value_counts())
checkin.columns=['进项发票数量']
checkin.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkin)):
    a,b,c=cnt(last,last+int(checkin.iloc[i,0]),data2)
    d,e,f=cnt2(last,last+int(checkin.iloc[i,0]),data2)
    last=last+int(checkin.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)

checkin['进项有效发票']=temp1
checkin['进项作废发票']=temp2
checkin['进项负数发票']=temp3
checkin['进项有效金额']=temp4
checkin['进项有效税额']=temp5
checkin['进项无效额']=temp6
checkin['进项有效价税']=temp7
#对销项发票进行统计
checkout=pd.DataFrame(data3['企业代号'].value_counts())
checkout.columns=['销项发票数量']
checkout.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkout)):
    a,b,c=cnt(last,last+int(checkout.iloc[i,0]),data3)
    d,e,f=cnt2(last,last+int(checkout.iloc[i,0]),data3)
    last=last+int(checkout.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)
    
checkout['销项有效发票']=temp1
checkout['销项作废发票']=temp2
checkout['销项负数发票']=temp3
checkout['销项有效金额']=temp4
checkout['销项有效税额']=temp5
checkout['销项无效额']=temp6
checkout['销项有效价税']=temp7
#保存中间结果
checkin.to_excel('value_counts_in.xls')
checkout.to_excel('value_counts_out.xls')
#合并进项和销项发票收据数据,为进行下一步计算做准备
checks=pd.concat([checkin,checkout],axis=1)
#对发票进行汇总统计
temp1=data['信誉评级']
temp2=data['是否违约']

temp1.index=range(1,124)
temp2.index=range(1,124)
checks=pd.concat([temp1,temp2,checks],axis=1)
checks['收入']=checks['销项有效价税']-checks['进项有效价税']

temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
last1=last2=0
a=b=0
for i in range(len(checks)):
    next1=last1+checks.iloc[i,2]
    next2=last2+checks.iloc[i,10]
    temp3=data2.iloc[last1:next1,3].value_counts()
    temp4=data3.iloc[last2:next2,3].value_counts()
    if(len(temp3)<=3):
        temp1.append(1)
        temp2.append(1)
        temp5.append(1)
    else:
        a=sum(temp3[:3])/sum(temp3)
        b=sum(temp4[:3])/sum(temp4)
        temp1.append(a)
        temp2.append(b)
        temp5.append(np.sqrt((a*a+b*b)/2))
    last1=next1
    last2=next2
    
checks['进稳定度指标']=temp1
checks['销稳定度指标']=temp2
checks['供求稳定度指标']=temp5

temp1=[]
temp2=[]
for i in range(len(checks)):
    sum1=sum2=sum3=0
    sum1=checks.iloc[i,2]+checks.iloc[i,10]
    sum2=checks.iloc[i,4]+checks.iloc[i,12]
    sum3=checks.iloc[i,5]+checks.iloc[i,13]
    temp1.append(sum2/sum1)
    temp2.append(sum3/sum1)
    
checks['作废发票率']=temp1
checks['负数发票率']=temp2
#保存中间结果
checks.to_excel('checks_1.xls')

第一问子问题一求解

pa0=checks[checks['信誉评级']!='D']
pa0
信誉评级是否违约进项发票数量进项有效发票进项作废发票进项负数发票进项有效金额进项有效税额进项无效额进项有效价税...销项有效金额销项有效税额销项无效额销项有效价税收入进稳定度指标销稳定度指标供求稳定度指标作废发票率负数发票率
1A34413249192715.744706e+098.932358e+082.547518e+086.637942e+09...4.065843e+096.327901e+081.001785e+084.698633e+09-1.939309e+090.5158380.2362520.4011880.0360140.025885
2A32156314357211561.557623e+086.725653e+069.248509e+061.624880e+08...5.908417e+083.545392e+076.841255e+076.262956e+084.638077e+080.2685970.0845200.1991080.0392970.011457
3C45614367194265.202698e+072.152374e+063.341486e+065.417935e+07...5.701780e+089.089228e+072.166951e+076.610703e+086.068909e+080.1688230.5689360.4196360.0202210.154781
4C5585213742.198771e+083.470711e+071.258232e+082.545842e+08...1.839970e+093.065709e+081.990901e+082.146541e+091.891956e+090.2616490.7108920.5356430.0813910.004661
5B2169208485161.977850e+082.954699e+074.718235e+062.273320e+08...2.026323e+083.065841e+079.497473e+062.332907e+085.958721e+060.3932690.5915090.5022670.0433570.008052
..................................................................
98B5150111.993190e+059.963970e+032.060000e+022.092830e+05...1.403242e+064.186923e+043.697712e+051.445111e+061.235828e+060.3921570.7320260.5872180.1029410.034314
104C11002.641500e+021.585000e+010.000000e+002.800000e+02...2.626505e+057.879490e+032.000000e+042.705300e+052.702500e+051.0000001.0000001.0000000.0909090.045455
105C66007.436920e+031.043080e+030.000000e+008.480000e+03...9.248194e+052.774452e+041.039500e+049.525639e+059.440839e+050.8333330.1967210.6054520.0078120.031250
106B3635105.033527e+042.125830e+039.000000e+035.246110e+04...5.860489e+051.762900e+045.552000e+046.036779e+055.512168e+050.3611110.1568630.2783950.0846560.021164
110C33009.633900e+027.661000e+010.000000e+001.040000e+03...1.969199e+055.907650e+037.257769e+042.028275e+052.017875e+051.0000001.0000001.0000000.2441860.000000

99 rows × 24 columns

pa0.iloc[:,0].value_counts()
B    38
C    34
A    27
Name: 信誉评级, dtype: int64
#替换指标值、删除多余项
pa1=pa0
pa1=pa1.replace('是',0)
pa1=pa1.replace('否',1)
pa1=pa1.replace('A',100)
pa1=pa1.replace('B',80)
pa1=pa1.replace('C',52.45)

pa1.drop(pa1.columns[range(2,18)],axis=1,inplace=True)
pa1.drop(pa1.columns[range(3,5)],axis=1,inplace=True)
pa1
信誉评级是否违约收入供求稳定度指标作废发票率负数发票率
1100.001-1.939309e+090.4011880.0360140.025885
2100.0014.638077e+080.1991080.0392970.011457
352.4516.068909e+080.4196360.0202210.154781
452.4511.891956e+090.5356430.0813910.004661
580.0015.958721e+060.5022670.0433570.008052
.....................
9880.0011.235828e+060.5872180.1029410.034314
10452.4512.702500e+051.0000000.0909090.045455
10552.4519.440839e+050.6054520.0078120.031250
10680.0015.512168e+050.2783950.0846560.021164
11052.4512.017875e+051.0000000.2441860.000000

99 rows × 6 columns

#成本型指标转换,数据归一化
pa1['作废发票率']=(max(pa1['作废发票率'])-pa1['作废发票率'])/(max(pa1['作废发票率'])-min(pa1['作废发票率']))
pa1['负数发票率']=(max(pa1['负数发票率'])-pa1['负数发票率'])/(max(pa1['负数发票率'])-min(pa1['负数发票率']))
pa1=(pa1-pa1.min())/(pa1.max()-pa1.min())
pa2=np.asarray(pa1)
#熵权法计算权重
ta=pa1
tb=pa2
n,m=np.shape(tb)
s=np.sum(ta,axis=0)
s=np.asarray(s)
s=np.reshape(s,(1,len(s)))
s=np.repeat(s,len(tb),axis=0)
s=s.reshape((n,m))
tb=tb/s
a=tb*1.0
a[np.where(tb==0)]=0.001
e=(-1.0/np.log(n))*np.sum(tb*np.log(a),axis=0)
w=(1-e)/np.sum(1-e)
w
array([0.62060705, 0.04171411, 0.02020687, 0.25088922, 0.04182548,
       0.02475726])
#Topsis综合评价
df=pa2/np.sqrt((pa2**2).sum())
df=pd.DataFrame(df)
X=pd.DataFrame([df.min(),df.max()],index=['负理想解','正理想解'])
R=df.copy()
R.columns=['信誉评级','是否违约','收入','供求稳定度指标','作废发票率','负数发票率']
R['正理想解']=np.sqrt(((df - X.loc['正理想解']) ** 2 * w).sum(axis=1))
R['负理想解']=np.sqrt(((df - X.loc['负理想解']) ** 2 * w).sum(axis=1))
R['综合得分']=R['负理想解'] / (R['负理想解'] + R['正理想解'])
R['排序']=R.rank(ascending=False)['综合得分']
R['公司识别码']=pa1.index
R['利率']=15-(R['综合得分']-R['综合得分'].min())/(R['综合得分'].max()-R['综合得分'].min())*11
#保存第一问子问题一结果
R.to_excel('result_1.xls')

第一问子问题二求解

pb0=pa0
pb1=pb0.iloc[:,[2,6,7,10,14,15,18,19,20,21,22,23]]#删去多余的指标
pb2=np.asarray(pb1)#转化为numpy数组,便于进行下一步计算
pb2=zscore(pb2)#数据标准化
#PCA主成分分析
pca=PCA()
pca.fit(pb2)
#计算差异保留比例
pcavr=[]
for i in range(1,13):
    pcavr.append(sum(pca.explained_variance_ratio_[:i]))
pcavr
[0.35983267152542864,
 0.5976689949959799,
 0.7300543810440263,
 0.8069829584129986,
 0.8737054990987985,
 0.9362732312043334,
 0.9788073230784305,
 0.9989350168474531,
 0.9995699794090525,
 0.9999523012802018,
 0.9999999999999999,
 0.9999999999999999]
#PCA差异保留画图分析
X = range(1,13)
#plt.figure(figsize=(12,8))
plt.xlabel('n_components')
plt.title('Principal Component Analysis')
plt.ylabel('Explained Variance Ratio')
plt.plot(X,pcavr,'o-')
plt.locator_params('x',nbins=12)
plt.savefig('Principal_Component_Analysis.jpg')
plt.show()

在这里插入图片描述

#选取最佳维度6,保留93%差异性
pca_=PCA(n_components=6)
pb3=pca_.fit_transform(pb2)
#肘部法则计算最佳聚类数
SSE=[]
for k in range(2,21):
    km=KMeans(n_clusters=k,random_state=10)
    km.fit(pb3)
    SSE.append(km.inertia_)
    X = range(2,21)
    
#肘部法则画图分析
#plt.figure(figsize=(12,8))
plt.xlabel('k')
plt.title('Elbow Method')
plt.ylabel('SSE')
plt.plot(X,SSE,'o-')
plt.locator_params('x',nbins=20)
plt.savefig('Elbow_Method.jpg')
plt.show()

在这里插入图片描述

#轮廓系数计算最佳聚类数
SC=[]
for k in range(2,21):
    km=KMeans(n_clusters=k,random_state=10)
    km.fit(pb3)
    a=silhouette_score(pb3,km.labels_, metric='euclidean')
    SC.append(a)
    
#轮廓系数画图分析
X = range(2,21)
#plt.figure(figsize=(12,8))
plt.xlabel('k')
plt.title('Silhouette Coefficient')
plt.ylabel('SC')
plt.plot(X,SC,'o-')
plt.locator_params('x',nbins=20)
plt.savefig('Silhouette_Coefficient.jpg')
plt.show()

在这里插入图片描述

#选取最佳聚类数9进行聚类分析
km=KMeans(n_clusters=9,random_state=12)
km.fit(pb3)
#标签统计
pd.DataFrame(km.labels_)[0].value_counts()
7    34
6    29
2    16
5     8
8     5
4     3
3     2
1     1
0     1
Name: 0, dtype: int64
#加入标签
pb1['分类']=km.labels_
pb1
/home/yang/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

进项发票数量进项有效金额进项有效税额销项发票数量销项有效金额销项有效税额收入进稳定度指标销稳定度指标供求稳定度指标作废发票率负数发票率分类
134415.744706e+098.932358e+0881104.065843e+096.327901e+08-1.939309e+090.5158380.2362520.4011880.0360140.0258851
2321561.557623e+086.725653e+06127075.908417e+083.545392e+074.638077e+080.2685970.0845200.1991080.0392970.0114574
345615.202698e+072.152374e+06240735.701780e+089.089228e+076.068909e+080.1688230.5689360.4196360.0202210.1547813
45582.198771e+083.470711e+0722311.839970e+093.065709e+081.891956e+090.2616490.7108920.5356430.0813910.0046610
521691.977850e+082.954699e+0710602.026323e+083.065841e+075.958721e+060.3932690.5915090.5022670.0433570.0080527
..........................................
98511.993190e+059.963970e+031531.403242e+064.186923e+041.235828e+060.3921570.7320260.5872180.1029410.0343146
10412.641500e+021.585000e+01212.626505e+057.879490e+032.702500e+051.0000001.0000001.0000000.0909090.0454555
10567.436920e+031.043080e+031229.248194e+052.774452e+049.440839e+050.8333330.1967210.6054520.0078120.0312506
106365.033527e+042.125830e+031535.860489e+051.762900e+045.512168e+050.3611110.1568630.2783950.0846560.0211647
11039.633900e+027.661000e+01831.969199e+055.907650e+032.017875e+051.0000001.0000001.0000000.2441860.0000005

99 rows × 13 columns

#保存问题一子问题二结果
pb1.to_excel('result_2.xls')

2.附件一Topsis综合评价

说明
这些程序从问题一改写而来,问题要去掉信誉等级为D的再算topsis综合得分,为了神经网络的学习,这里不需要去D

数据读取

#导入相关库
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from scipy.stats import zscore
from sklearn.decomposition import PCA
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
#读取数据
data = pd.read_excel('a0.xlsx')
data2 =pd.read_excel('a0.xlsx',sheet_name=1)
data3 =pd.read_excel('a0.xlsx',sheet_name=2)
#将企业代号前的E去掉,使其成为数值型变量,便于排序
data['企业代号']=data['企业代号'].apply(lambda x:int(x[1:]))
data2['企业代号']=data2['企业代号'].apply(lambda x:int(x[1:]))
data3['企业代号']=data3['企业代号'].apply(lambda x:int(x[1:]))

数据概览

data
企业代号企业名称信誉评级是否违约
01***电器销售有限公司A
12***技术有限责任公司A
23***电子(中国)有限公司***分公司C
34***发展有限责任公司C
45***供应链管理有限公司B
...............
118119***药房D
119120***陈列广告有限公司D
120121***药业连锁有限公司***药店D
121122***商贸有限责任公司D
122123***创科技有限责任公司D

123 rows × 4 columns

data2
企业代号发票号码开票日期销方单位代号金额税额价税合计发票状态
0133909392017-07-18A00297-943.40-56.60-1000.00有效发票
1133909402017-07-18A00297-4780.24-286.81-5067.05有效发票
2133909412017-07-18A00297943.4056.601000.00有效发票
3133909422017-07-18A002974780.24286.815067.05有效发票
4199026692017-08-07A05061326.219.79336.00有效发票
...........................
210942122547062342019-04-17A08967223.306.70230.00有效发票
210943122557213442020-01-10A09184264.1515.85280.00有效发票
210944123384932952017-12-15A03624264.1515.85280.00有效发票
210945123954720012018-12-29A03626264.1515.85280.00有效发票
210946123544698832019-12-18A03626264.1515.85280.00有效发票

210947 rows × 8 columns

data3
企业代号发票号码开票日期购方单位代号金额税额价税合计发票状态
01114593562017-08-04B037119401.711598.2911000.0有效发票
1150762392017-08-09B008448170.941389.069560.0有效发票
2150762402017-08-09B008448170.941389.069560.0有效发票
3150762412017-08-09B008444085.47694.534780.0有效发票
4150762422017-08-09B008444085.47694.534780.0有效发票
...........................
16247912388877012019-12-17B109444827.67144.834972.5有效发票
16248012388877022019-12-17B109447412.62222.387635.0有效发票
162481123341730852019-12-17B130931917.4757.531975.0有效发票
16248212388877032019-12-25B130937252.42217.587470.0有效发票
16248312388877042019-12-25B130936660.19199.816860.0有效发票

162484 rows × 8 columns

数据预处理

#计算有效发票、负数发票和作废发票数量
def cnt(a,b,df):
    tot=0
    for i in range(a,b):
        if df.iloc[i,6]<0 and df.iloc[i,7] == '有效发票':
            tot+=1 
    if(len(df.iloc[a:b,:]['发票状态'].value_counts())==1):
        return  df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],0,tot
    return df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],df.iloc[a:b,:]['发票状态'].value_counts()['作废发票'],tot
#计算有效金额,有效税额,作废额
def cnt2(a,b,df):
    zf=0
    yxj=0
    yxs=0
    for i in range(a,b):
        if df.iloc[i,7] == '作废发票':
            zf+=df.iloc[i,6]
        else:
            yxj+=df.iloc[i,4]
            yxs+=df.iloc[i,5]
    return yxj,yxs,zf
#对进项发票进行统计
checkin=pd.DataFrame(data2['企业代号'].value_counts())
checkin.columns=['进项发票数量']
checkin.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkin)):
    a,b,c=cnt(last,last+int(checkin.iloc[i,0]),data2)
    d,e,f=cnt2(last,last+int(checkin.iloc[i,0]),data2)
    last=last+int(checkin.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)

checkin['进项有效发票']=temp1
checkin['进项作废发票']=temp2
checkin['进项负数发票']=temp3
checkin['进项有效金额']=temp4
checkin['进项有效税额']=temp5
checkin['进项无效额']=temp6
checkin['进项有效价税']=temp7
#对销项发票进行统计
checkout=pd.DataFrame(data3['企业代号'].value_counts())
checkout.columns=['销项发票数量']
checkout.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkout)):
    a,b,c=cnt(last,last+int(checkout.iloc[i,0]),data3)
    d,e,f=cnt2(last,last+int(checkout.iloc[i,0]),data3)
    last=last+int(checkout.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)
    
checkout['销项有效发票']=temp1
checkout['销项作废发票']=temp2
checkout['销项负数发票']=temp3
checkout['销项有效金额']=temp4
checkout['销项有效税额']=temp5
checkout['销项无效额']=temp6
checkout['销项有效价税']=temp7
#合并进项和销项发票收据数据,为进行下一步计算做准备
checks=pd.concat([checkin,checkout],axis=1)
#对发票进行汇总统计
temp1=data['信誉评级']
temp2=data['是否违约']

temp1.index=range(1,124)
temp2.index=range(1,124)
checks=pd.concat([temp1,temp2,checks],axis=1)
checks['收入']=checks['销项有效价税']-checks['进项有效价税']

temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
last1=last2=0
a=b=0
for i in range(len(checks)):
    next1=last1+checks.iloc[i,2]
    next2=last2+checks.iloc[i,10]
    temp3=data2.iloc[last1:next1,3].value_counts()
    temp4=data3.iloc[last2:next2,3].value_counts()
    if(len(temp3)<=3):
        temp1.append(1)
        temp2.append(1)
        temp5.append(1)
    else:
        a=sum(temp3[:3])/sum(temp3)
        b=sum(temp4[:3])/sum(temp4)
        temp1.append(a)
        temp2.append(b)
        temp5.append(np.sqrt((a*a+b*b)/2))
    last1=next1
    last2=next2
    
checks['进稳定度指标']=temp1
checks['销稳定度指标']=temp2
checks['供求稳定度指标']=temp5

temp1=[]
temp2=[]
for i in range(len(checks)):
    sum1=sum2=sum3=0
    sum1=checks.iloc[i,2]+checks.iloc[i,10]
    sum2=checks.iloc[i,4]+checks.iloc[i,12]
    sum3=checks.iloc[i,5]+checks.iloc[i,13]
    temp1.append(sum2/sum1)
    temp2.append(sum3/sum1)
    
checks['作废发票率']=temp1
checks['负数发票率']=temp2

对不去D的数据进行topsis综合评价

#pa0=checks[checks['信誉评级']!='D']
pa0=checks
pa0
信誉评级是否违约进项发票数量进项有效发票进项作废发票进项负数发票进项有效金额进项有效税额进项无效额进项有效价税...销项有效金额销项有效税额销项无效额销项有效价税收入进稳定度指标销稳定度指标供求稳定度指标作废发票率负数发票率
1A34413249192715.744706e+098.932358e+082.547518e+086.637942e+09...4.065843e+096.327901e+081.001785e+084.698633e+09-1.939309e+090.5158380.2362520.4011880.0360140.025885
2A32156314357211561.557623e+086.725653e+069.248509e+061.624880e+08...5.908417e+083.545392e+076.841255e+076.262956e+084.638077e+080.2685970.0845200.1991080.0392970.011457
3C45614367194265.202698e+072.152374e+063.341486e+065.417935e+07...5.701780e+089.089228e+072.166951e+076.610703e+086.068909e+080.1688230.5689360.4196360.0202210.154781
4C5585213742.198771e+083.470711e+071.258232e+082.545842e+08...1.839970e+093.065709e+081.990901e+082.146541e+091.891956e+090.2616490.7108920.5356430.0813910.004661
5B2169208485161.977850e+082.954699e+074.718235e+062.273320e+08...2.026323e+083.065841e+079.497473e+062.332907e+085.958721e+060.3932690.5915090.5022670.0433570.008052
..................................................................
119D315314112.197387e+052.999611e+046.250000e+012.497348e+05...3.567186e+041.070140e+035.000000e+023.674200e+04-2.129928e+050.7619050.3333330.5880520.0119050.002976
120D3635101.811556e+041.330780e+031.075000e+041.944634e+04...1.906732e+055.720210e+030.000000e+001.963934e+051.769471e+050.5833330.2068970.4376550.3230770.000000
121D5050011.073486e+061.776337e+050.000000e+001.251120e+06...1.014620e+051.282553e+043.505210e+031.142875e+05-1.136832e+060.7400000.2150540.5449070.0974580.004237
122D4847115.067211e+043.074150e+039.620000e+025.374626e+04...5.892182e+041.818780e+032.674846e+046.074060e+046.994340e+030.4375000.1694920.3317630.1024100.054217
123D33007.924500e+024.755000e+010.000000e+008.400000e+02...2.002358e+052.732385e+043.960850e+042.275597e+052.267197e+051.0000001.0000001.0000000.4705880.000000

123 rows × 24 columns

pa0.iloc[:,0].value_counts()
B    38
C    34
A    27
D    24
Name: 信誉评级, dtype: int64
#替换指标值、删除多余项
pa1=pa0
pa1=pa1.replace('是',0)
pa1=pa1.replace('否',1)
pa1=pa1.replace('A',100)
pa1=pa1.replace('B',80)
pa1=pa1.replace('C',52.45)
pa1=pa1.replace('D',1)

pa1.drop(pa1.columns[range(2,18)],axis=1,inplace=True)
pa1.drop(pa1.columns[range(3,5)],axis=1,inplace=True)
pa1
信誉评级是否违约收入供求稳定度指标作废发票率负数发票率
1100.001-1.939309e+090.4011880.0360140.025885
2100.0014.638077e+080.1991080.0392970.011457
352.4516.068909e+080.4196360.0202210.154781
452.4511.891956e+090.5356430.0813910.004661
580.0015.958721e+060.5022670.0433570.008052
.....................
1191.000-2.129928e+050.5880520.0119050.002976
1201.0001.769471e+050.4376550.3230770.000000
1211.000-1.136832e+060.5449070.0974580.004237
1221.0006.994340e+030.3317630.1024100.054217
1231.0002.267197e+051.0000000.4705880.000000

123 rows × 6 columns

#成本型指标转换,数据归一化
pa1['作废发票率']=(max(pa1['作废发票率'])-pa1['作废发票率'])/(max(pa1['作废发票率'])-min(pa1['作废发票率']))
pa1['负数发票率']=(max(pa1['负数发票率'])-pa1['负数发票率'])/(max(pa1['负数发票率'])-min(pa1['负数发票率']))
pa1=(pa1-pa1.min())/(pa1.max()-pa1.min())
pa2=np.asarray(pa1)
#熵权法计算权重
ta=pa1
tb=pa2
n,m=np.shape(tb)
s=np.sum(ta,axis=0)
s=np.asarray(s)
s=np.reshape(s,(1,len(s)))
s=np.repeat(s,len(tb),axis=0)
s=s.reshape((n,m))
tb=tb/s
a=tb*1.0
a[np.where(tb==0)]=0.001
e=(-1.0/np.log(n))*np.sum(tb*np.log(a),axis=0)
w=(1-e)/np.sum(1-e)
w
array([0.34111674, 0.33907811, 0.01653923, 0.24240319, 0.03652877,
       0.02433396])
#Topsis综合评价
df=pa2/np.sqrt((pa2**2).sum())
df=pd.DataFrame(df)
X=pd.DataFrame([df.min(),df.max()],index=['负理想解','正理想解'])
R=df.copy()
R.columns=['信誉评级','是否违约','收入','供求稳定度指标','作废发票率','负数发票率']
R['正理想解']=np.sqrt(((df - X.loc['正理想解']) ** 2 * w).sum(axis=1))
R['负理想解']=np.sqrt(((df - X.loc['负理想解']) ** 2 * w).sum(axis=1))
R['综合得分']=R['负理想解'] / (R['负理想解'] + R['正理想解'])
R['排序']=R.rank(ascending=False)['综合得分']
R['公司识别码']=pa1.index
R['利率']=15-(R['综合得分']-R['综合得分'].min())/(R['综合得分'].max()-R['综合得分'].min())*11
#保存topsisi分析结果
R.to_excel('result_a1.xls')

3.问题2

数据读取

#导入相关库
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from scipy.stats import zscore
from sklearn.decomposition import PCA
from sklearn.pipeline import make_pipeline
from sklearn import svm
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
from sklearn.preprocessing import StandardScaler
#读取数据
data = pd.read_excel('b0.xlsx')
data2 =pd.read_excel('b0.xlsx',sheet_name=1)
data3 =pd.read_excel('b0.xlsx',sheet_name=2)
#将企业代号前的E去掉,使其成为数值型变量,便于排序
data['企业代号']=data['企业代号'].apply(lambda x:int(x[1:]))
data2['企业代号']=data2['企业代号'].apply(lambda x:int(x[1:]))
data3['企业代号']=data3['企业代号'].apply(lambda x:int(x[1:]))
#比较坑,附件一附件二进销项位置不一样,互换位置即可
temp=data2
data2=data3
data3=temp

数据概览

data
企业代号企业名称
0124个体经营E124
1125个体经营E125
2126个体经营E126
3127个体经营E127
4128个体经营E128
.........
297421***保温材料有限公司
298422***童装店
299423***通风设备有限公司
300424***贸易有限公司
301425***商贸有限公司

302 rows × 2 columns

data2
企业代号发票号码开票日期销方单位代号金额税额价税合计发票状态
0124188916762017-09-01 00:00:00C00014338.4657.54396.0有效发票
1124186912672017-09-01 00:00:00C00480230.106.90237.0有效发票
2124129954122017-09-01 00:00:00C23675223.306.70230.0有效发票
312463781932017-09-01 11:11:03C0033390090.099909.91100000.0有效发票
412463781942017-09-01 11:12:01C0033390090.099909.91100000.0有效发票
...........................
395170425721013752019-12-30 00:00:00C23112663.1119.89683.0有效发票
395171425202532852020-01-05 00:00:00C0193745.414.0949.5有效发票
395172425202533152020-01-05 00:00:00C01937110.141.16111.3有效发票
395173425524494042020-01-06 00:00:00C1503273.226.5879.8有效发票
39517442556662992020-01-10 00:00:00C23112132.043.96136.0有效发票

395175 rows × 8 columns

data3
企业代号发票号码开票日期购方单位代号金额税额价税合计发票状态
0124152124832017-09-01 11:58:43D00585839350.5592328.56931679.11有效发票
1124152124842017-09-01 11:59:20D00585900900.9099099.101000000.00有效发票
2124152124852017-09-01 11:59:51D00585900900.9099099.101000000.00有效发票
3124152124862017-09-01 12:00:45D00585697365.7476710.23774075.97有效发票
412460896152017-09-05 10:58:38D00108726216.2279883.78806100.00有效发票
...........................
330830425218034682018-04-03 00:00:00D214469000.00270.009270.00有效发票
330831425218034692018-04-03 00:00:00D214465155.34154.665310.00有效发票
330832425218034702019-06-11 00:00:00D021264854.37145.635000.00作废发票
330833425218034712019-06-11 00:00:00D021264854.37145.635000.00作废发票
330834425218034722019-06-11 00:00:00D021264854.37145.635000.00有效发票

330835 rows × 8 columns

数据预处理

#计算有效发票、负数发票和作废发票数量
def cnt(a,b,df):
    tot=0
    for i in range(a,b):
        if df.iloc[i,6]<0 and df.iloc[i,7] == '有效发票':
            tot+=1 
    if(len(df.iloc[a:b,:]['发票状态'].value_counts())==1):
        return  df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],0,tot
    return df.iloc[a:b,:]['发票状态'].value_counts()['有效发票'],df.iloc[a:b,:]['发票状态'].value_counts()['作废发票'],tot
#计算有效金额,有效税额,作废额
def cnt2(a,b,df):
    zf=0
    yxj=0
    yxs=0
    for i in range(a,b):
        if df.iloc[i,7] == '作废发票':
            zf+=df.iloc[i,6]
        else:
            yxj+=df.iloc[i,4]
            yxs+=df.iloc[i,5]
    return yxj,yxs,zf
#对进项发票进行统计
checkin=pd.DataFrame(data2['企业代号'].value_counts())
checkin.columns=['进项发票数量']
checkin.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkin)):
    a,b,c=cnt(last,last+int(checkin.iloc[i,0]),data2)
    d,e,f=cnt2(last,last+int(checkin.iloc[i,0]),data2)
    last=last+int(checkin.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)

checkin['进项有效发票']=temp1
checkin['进项作废发票']=temp2
checkin['进项负数发票']=temp3
checkin['进项有效金额']=temp4
checkin['进项有效税额']=temp5
checkin['进项无效额']=temp6
checkin['进项有效价税']=temp7
#对销项发票进行统计
checkout=pd.DataFrame(data3['企业代号'].value_counts())
checkout.columns=['销项发票数量']
checkout.sort_index(inplace=True)

last=0
temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
temp6=[]
temp7=[]

for i in range(len(checkout)):
    a,b,c=cnt(last,last+int(checkout.iloc[i,0]),data3)
    d,e,f=cnt2(last,last+int(checkout.iloc[i,0]),data3)
    last=last+int(checkout.iloc[i,0])
    temp1.append(a)
    temp2.append(b)
    temp3.append(c)
    temp4.append(d)
    temp5.append(e)
    temp6.append(f)
    temp7.append(d+e)
    
checkout['销项有效发票']=temp1
checkout['销项作废发票']=temp2
checkout['销项负数发票']=temp3
checkout['销项有效金额']=temp4
checkout['销项有效税额']=temp5
checkout['销项无效额']=temp6
checkout['销项有效价税']=temp7
#保存中间结果
checkin.to_excel('value_counts_in.xls')
checkout.to_excel('value_counts_out.xls')
#合并进项和销项发票收据数据,为进行下一步计算做准备
checks=pd.concat([checkin,checkout],axis=1)
#对发票进行汇总统计
checks['收入']=checks['销项有效价税']-checks['进项有效价税']

temp1=[]
temp2=[]
temp3=[]
temp4=[]
temp5=[]
last1=last2=0
a=b=0
for i in range(len(checks)):
    next1=last1+checks.iloc[i,0]
    next2=last2+checks.iloc[i,8]
    temp3=data2.iloc[last1:next1,3].value_counts()
    temp4=data3.iloc[last2:next2,3].value_counts()
    if(len(temp3)<=3):
        temp1.append(1)
        temp2.append(1)
        temp5.append(1)
    else:
        if(sum(temp3)==0):
            a=0
        else:
            a=sum(temp3[:3])/sum(temp3)
        if(sum(temp4)==0):
            b=0
        else:
            b=sum(temp4[:3])/sum(temp4)
        temp1.append(a)
        temp2.append(b)
        temp5.append(np.sqrt((a*a+b*b)/2))
    last1=next1
    last2=next2
    
checks['进稳定度指标']=temp1
checks['销稳定度指标']=temp2
checks['供求稳定度指标']=temp5

temp1=[]
temp2=[]
for i in range(len(checks)):
    sum1=sum2=sum3=0
    sum1=checks.iloc[i,0]+checks.iloc[i,8]
    sum2=checks.iloc[i,2]+checks.iloc[i,10]
    sum3=checks.iloc[i,3]+checks.iloc[i,11]
    temp1.append(sum2/sum1)
    temp2.append(sum3/sum1)
    
checks['作废发票率']=temp1
checks['负数发票率']=temp2
#保存中间结果
checks.to_excel('checks_2.xls')
#读取问题一的信誉评级数据
checks_2=pd.read_excel('checks_a1.xls',index_col=0)
#做标注,为下面的支持向量机做准备
labels=checks_2['信誉评级']
labels=labels.replace('A',1)
labels=labels.replace('B',2)
labels=labels.replace('C',3)
labels=labels.replace('D',4)
labels=np.asarray(labels)
#合并附件一和附件二的企业
checks_2.drop(['信誉评级','是否违约'],axis=1,inplace=True)
checks_3=pd.concat([checks_2,checks],axis=0)
checks_3
进项发票数量进项有效发票进项作废发票进项负数发票进项有效金额进项有效税额进项无效额进项有效价税销项发票数量销项有效发票...销项有效金额销项有效税额销项无效额销项有效价税收入进稳定度指标销稳定度指标供求稳定度指标作废发票率负数发票率
134413249192715.744706e+098.932358e+082.547518e+086.637942e+0981107886...4.065843e+096.327901e+081.001785e+084.698633e+09-1.939309e+090.5158380.2362520.4011880.0360140.025885
232156314357211561.557623e+086.725653e+069.248509e+061.624880e+081270711665...5.908417e+083.545392e+076.841255e+076.262956e+084.638077e+080.2685970.0845200.1991080.0392970.011457
345614367194265.202698e+072.152374e+063.341486e+065.417935e+072407323688...5.701780e+089.089228e+072.166951e+076.610703e+086.068909e+080.1688230.5689360.4196360.0202210.154781
45585213742.198771e+083.470711e+071.258232e+082.545842e+0822312041...1.839970e+093.065709e+081.990901e+082.146541e+091.891956e+090.2616490.7108920.5356430.0813910.004661
52169208485161.977850e+082.954699e+074.718235e+062.273320e+0810601005...2.026323e+083.065841e+079.497473e+062.332907e+085.958721e+060.3932690.5915090.5022670.0433570.008052
..................................................................
4211919003.039381e+041.776190e+030.000000e+003.217000e+042928...2.457008e+057.371070e+030.000000e+002.530718e+052.209018e+050.8947370.8620690.8785550.0208330.000000
42233008.893900e+027.061000e+010.000000e+009.600000e+023027...2.701831e+048.383000e+021.351000e+032.785661e+042.689661e+041.0000001.0000001.0000000.0909090.000000
4232121016.104614e+048.005560e+030.000000e+006.905170e+0476...7.278640e+042.183600e+035.770000e+037.497000e+045.918300e+030.7142861.0000000.8689660.0357140.035714
4242525002.543299e+051.766949e+040.000000e+002.719994e+054337...7.689642e+042.306880e+031.148231e+047.920330e+04-1.927961e+050.5600000.5348840.5475860.0882350.029412
425118116235.719625e+044.885150e+032.678840e+036.208140e+041810...8.512621e+042.553790e+036.947200e+048.768000e+042.559860e+040.5762711.0000000.8161150.0735290.022059

425 rows × 22 columns

checks_3.to_excel('checks_3.xls')

问题二子问题一利率分配

#一些数据的初始化
pa0=checks_3
#pa1=pa0.iloc[:,[0,8,16,19,20,21]]
#pa1=pa0.drop(pa0.columns[[0,7,8,15,16,19,20,21]],axis=1)
pa1=pa0
pa1=np.asarray(pa1)
pa1=zscore(pa1)
ck=np.asarray(checks)
ck2=np.asarray(checks_2)
#主成分分析
pca=PCA()
pca.fit(pa1)
pcavr=[]
for i in range(1,23):
    pcavr.append(sum(pca.explained_variance_ratio_[:i]))
pcavr
[0.3667363880450508,
 0.5647177553788358,
 0.6694778602067868,
 0.7551550627474768,
 0.8131132267472697,
 0.865619957576671,
 0.9107898364534954,
 0.9387893796264828,
 0.9599599037916896,
 0.9788060451769551,
 0.988728920202568,
 0.9942716313124501,
 0.9978255167866447,
 0.999138389215358,
 0.9995923091351523,
 0.999967761573444,
 0.9999999766710131,
 1.0,
 1.0,
 1.0,
 1.0,
 1.0]
#PCA差异保留画图分析
X = range(1,23)
#plt.figure(figsize=(12,8))
plt.xlabel('n_components')
plt.title('Principal Component Analysis')
plt.ylabel('Explained Variance Ratio')
plt.plot(X,pcavr,'o-')
plt.locator_params('x',nbins=12)
plt.savefig('Principal_Component_Analysis.jpg')
plt.show()

在这里插入图片描述

#得到降维后的向量
pca_=PCA(n_components=8)
pa2=pca_.fit_transform(pa1)
#用于打乱训练集和测试集用
import random
rd=np.arange(123)
random.shuffle(rd)
#用支持向量机对前100个训练,后23个测试验证所得到的精度
clf = make_pipeline(StandardScaler(), svm.SVC(gamma='auto'))
clf.fit(pa2[rd[:100]],labels[rd[:100]])
clf.score(pa2[rd[100:123]],labels[rd[100:]])
0.2608695652173913
#对附件一所有企业进行训练,得到的训练精度
clf2 = make_pipeline(StandardScaler(), svm.SVC(gamma='auto'))
clf2.fit(pa2[:123],labels)
clf2.score(pa2[:123],labels)
0.5365853658536586
#得到信誉预测A指标
m1p=clf2.predict(pa2[123:])
labels=pd.DataFrame(labels)
m1p=pd.DataFrame(m1p)
c1=pd.concat([labels,m1p],axis=0)
c1.index=np.arange(1,426)
pa0['信誉预测A']=c1
#改写问题一topsis程序,不去除D,计算综合得分并归一化
ra1=pd.read_excel('result_a1.xls',index_col=0)
lb=(ra1['综合得分']-ra1['综合得分'].min())/(ra1['综合得分'].max()-ra1['综合得分'].min())
lb=np.asarray(lb)
#建立DNN密集链接神经网络模型
from keras import models
from keras import layers
def rmodel():
    model=models.Sequential()
    model.add(layers.Dense(64, activation='relu',
                       input_shape=(pa2.shape[1],)))
    model.add(layers.Dense(64, activation='relu'))
    model.add(layers.Dense(1))
    model.compile(optimizer='rmsprop', loss='mse', metrics=['mae'])
    return model

model1=rmodel()
model1.fit(pa2[rd[:100]], lb[rd[:100]],epochs=100, batch_size=1,verbose=0)
val_mse, val_mae = model1.evaluate(pa2[rd[100:123]], lb[rd[100:123]], verbose=0)
Using TensorFlow backend.
#用DNN对前100个训练,后23个测试验证所得到的误差
val_mse,val_mae
(0.19183750450611115, 0.3098321855068207)
#训练模型
model2=rmodel()
model2.fit(pa2[:123], lb,epochs=100, batch_size=1,verbose=0)
val2_mse, val2_mae = model2.evaluate(pa2[:123], lb, verbose=0)
val2_mse, val2_mae
(0.055356581490941165, 0.11002491414546967)
#得到信誉预测B指标
m2p=model2.predict(pa2[123:])
m2p=pd.DataFrame(m2p)
lb=pd.DataFrame(lb)
c2=pd.concat([lb,m2p])
c2.index=range(1,426)
pa0['信誉预测B']=c2
#统计计算
pa3=pa0.iloc[123:,:]
pa3['信誉预测A'].replace(1,100,inplace=True)
pa3['信誉预测A'].replace(2,80,inplace=True)
pa3['信誉预测A'].replace(3,52.45,inplace=True)
pa3['信誉预测A'].replace(4,1,inplace=True)
pa3['作废发票率']=(max(pa3['作废发票率'])-pa3['作废发票率'])/(max(pa3['作废发票率'])-min(pa3['作废发票率']))
pa3['负数发票率']=(max(pa3['负数发票率'])-pa3['负数发票率'])/(max(pa3['负数发票率'])-min(pa3['负数发票率']))
pa3=(pa3-pa3.min())/(pa3.max()-pa3.min())
pa3=pa3[['信誉预测A','信誉预测B','收入','供求稳定度指标','作废发票率','负数发票率']]
pa4=np.asarray(pa3)
#熵权法计算权重
ta=pa3
tb=pa4
n,m=np.shape(tb)
s=np.sum(ta,axis=0)
s=np.asarray(s)
s=np.reshape(s,(1,len(s)))
s=np.repeat(s,len(tb),axis=0)
s=s.reshape((n,m))
tb=tb/s
a=tb*1.0
a[np.where(tb==0)]=0.001
e=(-1.0/np.log(n))*np.sum(tb*np.log(a),axis=0)
w=(1-e)/np.sum(1-e)
w
array([0.11221771, 0.11019597, 0.25695496, 0.47673639, 0.01765203,
       0.02624293])
#Topsis综合评价
df=pa4/np.sqrt((pa4**2).sum())
df=pd.DataFrame(df)
X=pd.DataFrame([df.min(),df.max()],index=['负理想解','正理想解'])
R=df.copy()
R.columns=['信誉评级','是否违约','收入','供求稳定度指标','作废发票率','负数发票率']
R['正理想解']=np.sqrt(((df - X.loc['正理想解']) ** 2 * w).sum(axis=1))
R['负理想解']=np.sqrt(((df - X.loc['负理想解']) ** 2 * w).sum(axis=1))
R['综合得分']=R['负理想解'] / (R['负理想解'] + R['正理想解'])
R['排序']=R.rank(ascending=False)['综合得分']
R['利率']=15-(R['综合得分']-R['综合得分'].min())/(R['综合得分'].max()-R['综合得分'].min())*11
#保存利率计算结果
R.to_excel('result_3.xls')

问题二子问题二额度分析

pb0=pa0
pb1=pb0.iloc[123:,[0,4,5,8,12,13,16,17,18,19,20,21]]#删去多余的指标
pb2=np.asarray(pb1)#转化为numpy数组,便于进行下一步计算
pb2=zscore(pb2)#数据标准化
#PCA主成分分析
pca=PCA()
pca.fit(pb2)
#计算差异保留比例
pcavr=[]
for i in range(1,13):
    pcavr.append(sum(pca.explained_variance_ratio_[:i]))
pcavr
[0.40237293073369423,
 0.5723379535072953,
 0.6867370550736526,
 0.7714374725780188,
 0.8524884204316454,
 0.9169752220290351,
 0.9662287460931756,
 0.9882960568245,
 0.9959509809020667,
 0.9993026536897576,
 0.9999999999999999,
 0.9999999999999999]
#PCA差异保留画图分析
X = range(1,13)
#plt.figure(figsize=(12,8))
plt.xlabel('n_components')
plt.title('Principal Component Analysis')
plt.ylabel('Explained Variance Ratio')
plt.plot(X,pcavr,'o-')
plt.locator_params('x',nbins=12)
plt.savefig('Principal_Component_Analysis_B.jpg')
plt.show()

在这里插入图片描述

#选取最佳维度6,保留91%差异性
pca_=PCA(n_components=6)
pb3=pca_.fit_transform(pb2)
#肘部法则计算最佳聚类数
SSE=[]
for k in range(2,21):
    km=KMeans(n_clusters=k,random_state=10)
    km.fit(pb3)
    SSE.append(km.inertia_)
    X = range(2,21)
    
#肘部法则画图分析
#plt.figure(figsize=(12,8))
plt.xlabel('k')
plt.title('Elbow Method')
plt.ylabel('SSE')
plt.plot(X,SSE,'o-')
plt.locator_params('x',nbins=20)
plt.savefig('Elbow_Method.jpg')
plt.show()

在这里插入图片描述

#轮廓系数计算最佳聚类数
SC=[]
for k in range(2,21):
    km=KMeans(n_clusters=k,random_state=10)
    km.fit(pb3)
    a=silhouette_score(pb3,km.labels_, metric='euclidean')
    SC.append(a)
    
#轮廓系数画图分析
X = range(2,21)
#plt.figure(figsize=(12,8))
plt.xlabel('k')
plt.title('Silhouette Coefficient')
plt.ylabel('SC')
plt.plot(X,SC,'o-')
plt.locator_params('x',nbins=20)
plt.savefig('Silhouette_Coefficient.jpg')
plt.show()

在这里插入图片描述

#选取最佳聚类数10进行聚类分析
km=KMeans(n_clusters=10,random_state=20)
km.fit(pb3)
#标签统计
pd.DataFrame(km.labels_)[0].value_counts()
6    87
1    87
2    36
0    35
7    31
9     9
5     8
8     4
4     3
3     2
Name: 0, dtype: int64
#加入标签
pb1['分类']=km.labels_
pb1
进项发票数量进项有效金额进项有效税额销项发票数量销项有效金额销项有效税额收入进稳定度指标销稳定度指标供求稳定度指标作废发票率负数发票率分类
124174117.855255e+085.867726e+0712937.417804e+086.665738e+07-3.576505e+070.1277930.3232790.2458050.1251600.0204773
125202889.323832e+086.881856e+0715959.412114e+088.362975e+072.363930e+070.1210570.3034480.2310150.1246170.0200163
1265331.127735e+081.604526e+0715165.206911e+081.731628e+074.091887e+080.4652910.2500000.3734940.1317720.0170824
12714501.646702e+061.188565e+0540266.519380e+081.957022e+076.697426e+080.3889660.4679580.4302780.0270270.0010964
12832228.928033e+064.324670e+0512552.423902e+088.299067e+062.413287e+080.3075730.9808760.7268840.0406520.0033507
..........................................
421193.039381e+041.776190e+03292.457008e+057.371070e+032.209018e+050.8947370.8620690.8785550.0208330.0000000
42238.893900e+027.061000e+01302.701831e+048.383000e+022.689661e+041.0000001.0000001.0000000.0909090.0000000
423216.104614e+048.005560e+0377.278640e+042.183600e+035.918300e+030.7142861.0000000.8689660.0357140.0357140
424252.543299e+051.766949e+04437.689642e+042.306880e+03-1.927961e+050.5600000.5348840.5475860.0882350.0294126
4251185.719625e+044.885150e+03188.512621e+042.553790e+032.559860e+040.5762711.0000000.8161150.0735290.0220590

302 rows × 13 columns

#保存问题二子问题二额度分析结果
pb1.to_excel('result_4.xls')

4.问题3

根据调查结果修正利率

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
gsb=pd.read_excel('gsb.xlsx')
gsb['企业代号']=gsb['企业代号'].apply(lambda x:int(x[1:]))
gsb.columns=['企业代号','企业名称','企业评分']
gsb['企业评分'].replace([9,19,18,1,2,8,15,12,5,6,3,14,11,16,4,10,13,17,7],[int(i) for i in np.linspace(5,95,19)],inplace=True)
r3=pd.read_excel('result_3.xls',index_col=0)
r3['评分']=gsb['企业评分']
r3['修正利率']=(r3['评分']-50)*0.06
r3.to_excel('result_6.xls')

5.其他程序

主要是画图

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

问题一-隶属函数

x=np.linspace(1,4,300)
y=(0.6952*np.log(x)+0.0362)*(x<3)+((1+1.1086*(x-0.8942)**(-2))**(-1))*(x>=3)
#plt.figure(figsize=(12,8))
plt.plot(x,y)
plt.title('Membership function')
plt.xlabel('x')
plt.ylabel('y')
plt.savefig('Membership_function.jpg')
plt.show()

在这里插入图片描述

问题一-不同公司的利率散点图和风险系数散点图

result1=pd.read_excel('result_1.xls',index_col=0)
x=result1['公司识别码']
y1=result1['利率']
y2=result1['综合得分']
y2=1-y2
plt.figure(figsize=(12,8))
plt.title('The interest rate of different companys')
plt.xlabel('Company ID')
plt.ylabel('The interest rate')
plt.plot(x,y1,'ro-')
plt.savefig('The_interest_rate_of_different_companys.jpg')

在这里插入图片描述

plt.figure(figsize=(12,8))
plt.title('The overall ratings of different companys')
plt.xlabel('Company ID')
plt.ylabel('The interest rate')
plt.plot(x,y2,'ro-')
plt.savefig('The_overall_ratings_of_different_companys.jpg')

在这里插入图片描述

问题二-不同公司的利率散点图和风险系数散点图

result3=pd.read_excel('result_3.xls',index_col=0)
x=range(1,303)
y1=result3['利率']
y2=result3['综合得分']
y2=1-y2
#plt.figure(figsize=(12,8))
plt.title('The interest rate of different companys')
plt.xlabel('Company ID')
plt.ylabel('The interest rate')
plt.plot(x,y1,'go-')
plt.savefig('The_interest_rate_of_different_companys.jpg')

在这里插入图片描述

#plt.figure(figsize=(12,8))
plt.title('The overall ratings of different companys')
plt.xlabel('Company ID')
plt.ylabel('The interest rate')
plt.plot(x,y2,'go-')
plt.savefig('The_overall_ratings_of_different_companys.jpg')

在这里插入图片描述

评论 27
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值