2021 年高教社杯全国大学生数学建模竞赛题目 C 题 生产企业原材料的订购与运输 第一题代码_2021年高教社杯全国大学生数学建模竞赛题目c题原题附件

pip install pandas
pip install openpyxl
 
import pandas as pd
df = pd.read_excel('C:\\Users\SuYi\Desktop\python\题目\C\附件1 近5年402家供应商的相关数据.xlsx', sheet_name = 1 )  #打开excel
df['材料数总和'] = df.iloc[:,2:242].sum(axis=1)    #对指定行求和
 
df1=df.loc[df['材料分类']=='A']
df1['采购总价']=df1['材料数总和']*1.2
df2=df.loc[df['材料分类']=='B']
df2['采购总价']=df2['材料数总和']*1.1
df3=df.loc[df['材料分类']=='C']
df3['采购总价']=df3['材料数总和']          #分别对ABC对应Row_sum做乘法
 
df4 = pd.concat([df1,df2,df3],axis=0)  #行标签合并表格
 
df4 = df4.sort_index(axis=0)           #按行名排列
 
df4['方差'] = df.iloc[:,2:242].var(axis=1)   #求方差
 
sheet0 = pd.read_excel('C:\\Users\SuYi\Desktop\python\题目\C\附件1 近5年402家供应商的相关数据.xlsx', sheet_name = 0 )  #导入sheet_name = 0
 
i = 2
n = 0
while n < 10:
 df['q'] = df.iloc[:,i:i+24].sum(axis=1)
 sheet0['q'] = sheet0.iloc[:,i:i+24].sum(axis=1)
 df4['供货满足率',n] = df['q']/sheet0['q']       #每二十四周求和求满足率并输出 共十次
 i += 24
 n += 1
 
df4 = df4.fillna(0)           #将nan替换为0
 
df4['供货满足率总和'] = df4.iloc[:,245:255].sum(axis=1)   #求和
 
df4['供货满足率'] = df4['供货满足率总和']/10          #求满足率均值
 
import copy
import pandas as pd
import numpy as np
 
df5 = pd.read_excel('C:\\Users\SuYi\Desktop\python\题目\C\附件1 近5年402家供应商的相关数据.xlsx', sheet_name = 1 )
df5 = df5.drop( df5.iloc[:,0:242], axis=1 )          #删除指定列
df5['方差'] = df4['方差']
df5['供货满足率'] = df4['供货满足率']
df5['采购总价'] = df4['采购总价']
 
label_need=df5.keys()[0:]
 
data1=df5[label_need].values
 
data2=data1     #指标正向化处理后数据为data2
 
 
#越小越优指标位置
index=[0] 
for i in range(0,len(index)):
    data2[:,index[i]]=max(data1[:,index[i]])-data1[:,index[i]]
 
 
index1=[2] 
for i in range(0,len(index)):
    data2[:,index[i]]=max(data1[:,index[i]])-data1[:,index[i]]
 
 
 
#0.002~1区间归一化
[m,n]=data2.shape
data3=copy.deepcopy(data2)
ymin=0.002
ymax=1
for j in range(0,n):
    d_max=max(data2[:,j])
    d_min=min(data2[:,j])
    data3[:,j]=(ymax-ymin)*(data2[:,j]-d_min)/(d_max-d_min)+ymin
 
    
#计算信息熵
p=copy.deepcopy(data3)
for j in range(0,n):
    p[:,j]=data3[:,j]/sum(data3[:,j])
 
E=copy.deepcopy(data3[0,:])
for j in range(0,n):
    E[j]=-1/np.log(m)*sum(p[:,j]*np.log(p[:,j]))
 
 
# 计算权重
w=(1-E)/sum(1-E)
 
#计算得分
df5['得分'] = 0
s=np.dot(data3,w)
Score=100*s/max(s)
for i in range(0,len(Score)):
    df5['得分'][i] = Score[i]
 
df = pd.read_excel('C:\\Users\SuYi\Desktop\python\题目\C\附件1 近5年402家供应商的相关数据.xlsx', sheet_name = 1 )
df['方差'] = df5['方差']
df['供货满足率'] = df5['供货满足率']
df['采购总价'] = df5['采购总价']
df['得分'] = df5['得分']
 
df = df.sort_values(by='得分', ascending=False)
df
 
cf = df.iloc[0:50]
cf