在5月一号晚上，我参加了华中杯数学建模 。对于数据处理，我用到了下面代码，希望能帮助到大家 。
.py文件，用于对附件一的数据进行划分：
import pandas as pd #分析excel表格import matplotlib.pyplot as plt # matplotlib中有很多可用的模块，我们使用pyplot模块from numpy import *#读取excel表格并返回日期def readExcel():df = pd.read_excel('附件1.xlsx',sheet_name='销量数据')return set(df['货号'])#按货号分#return set(df['日期'])# 按日期分#按货号进行分开def changeGoods(x):df2 = pd.read_excel('附件1.xlsx',sheet_name='销量数据')#按货号分for i in range(len(x)):k = df2[df2['货号'] == x[i]]k.to_csv('huowu//{name}.csv'.format(name = x[i]), sep=',', header=True, index=True)#按日期进行分开def changeDay(x):df2 = pd.read_excel('附件1.xlsx',sheet_name='销量数据')#按日期分for i in range(len(x)):k = df2[df2['日期'] == x[i]]# my_data = http://www.kingceram.com/post/x[i][0:4] + x[i][5:7] + x[i][8:10]k.to_csv('data//{name}.csv'.format(name = x[i]), sep=',', header=True, index=True)#把每个日期和货物总量算出来def summaryAll(x):sum = 0df = pd.read_csv(x)for m in range(len(df['销售件数'])):sum += df['销售件数'][m]return sum#创建表格def createExcel(day,data):my_day = []for i in range(len(day)):# my_day.append(day[i][0:4] + '/' + day[i][4:6] + '/' +day[i][6:8])my_day.append(day[i][0:4] + day[i][4:6] + day[i][6:8])df1 = pd.DataFrame({'日期': my_day ,'总销量': data})df1.to_excel('每个日期的总销量二.xlsx', sheet_name='每天的总销量', startcol=0, index=False)def main():this = readExcel()#读取excel表格并返回日期my_list = list(this)#changeDay(my_list) #按日期进行分开changeGoods(my_list)# 按货号进行分开my_data = []for i in range(len(my_list)) :my_data.append(str(my_list[i][0:4]+my_list[i][5:7]+my_list[i][8:10]))my_data.sort()my_data_sum = []for j in range(len(my_data)):a = summaryAll('day/{my_day}.csv'.format(my_day = my_data[j]))my_data_sum.append(a)print(my_data)print(my_data_sum)data = []for k in range(len(my_data)):data.append(k)my_data_sum2 = []for n in range(len(my_data_sum)):my_data_sum2.append(math.log(my_data_sum[n]))print(my_data_sum2)createExcel(my_data, my_data_sum)if __name__ == '__main__':main()
.py用于对每月的货号总销量进行汇总：
import pandas as pd #分析excel表格#读取excel表格并返回产品def readExcelProdect():# df = pd.read_excel('附件一.xlsx',sheet_name='销量数据')df = pd.read_excel('附件1.xlsx', sheet_name='销量数据')return set(df['货号'])#按货物分#读取excel表格并返回日期def readExcelDay():# df = pd.read_excel('附件一.xlsx',sheet_name='销量数据')df = pd.read_excel('附件1.xlsx', sheet_name='销量数据')return set(df['日期'])# 按日期分#生成汇总表def createExcel(x,y):dict = {}dict[x[0]] = yfor i in range(1,len(x)):w = []for j in range(0,len(y)):w.append('')dict[x[i]] = wdf = pd.DataFrame(dict)df.to_excel('汇总.xlsx')#处理每一种货物的表格def createData(x,day,goods):every_month = []for i in range(0,len(x['月份'])):every_month.append(str(x['月份'][i]))month = list(set(every_month))month.sort()all_num = []for i in range(len(month)):sum = 0my = x[x['月份'] == int(month[i])]n = list(my['销售件数'])for j in range(len(n)):sum += n[j]all_num.append(sum)# print(month)# print(all_num)#进行补零all_month = dayall_xiao_shou = []#print(day)n1 = 0for m in range(len(day)):if day[m] == month[0]:n1 = m#每个货物的月份开始日期n2 = n1 + len(month) - 1#每个货物的二月份结束日期for n in range(len(day)):if n < n1or n > n2 :all_xiao_shou.append(0)else:all_xiao_shou.append(all_num[n-n1])# print(day)print(all_xiao_shou)def main():this1 = readExcelProdect()# 读取excel表格并返回货号my_goods = list(this1)my_goods_new = []for i in range(len(my_goods)):my_goods_new.append(my_goods[i][2:7])my_goods_new.sort()this_goods = []for j in range(len(my_goods_new)):this_goods.append('SS'+my_goods_new[j])#print(this_goods)#print(len(this_goods))this2 = readExcelDay()# 读取excel表格并返回日期my_day = list(this2)my_day_new = []for i in range(len(my_day)):this_day = str(my_day[i])my_day_new.append(this_day[0:6])my_day_new = list(set(my_day_new))my_day_new.sort()# print(my_day_new)# createExcel(my_day_new, this_goods)df = pd.read_csv('huowu/SS61146.csv')createData(df, my_day_new, this_goods)for j in range(len(this_goods)):df = pd.read_csv('huowu/{n}.csv'.format(n = this_goods[j]))# print(this_goods[j])createData(df,my_day_new,this_goods)if __name__ == '__main__':main()