2020-10-03

python数学建模2019第一题代码

# 导入模块
import pandas as pd
import numpy as np
import openpyxl

# 读取数据
df1 = pd.read_csv(r'附件2.csv', encoding='gb18030')
df2 = pd.read_csv(r'附件2.csv', encoding='gb18030')
# 合并列表
frames = [df1, df2]
result = pd.concat(frames, axis=0, join='outer')
# 删除订单为0
result = result.drop(result[result['Order'] == 0].index)
# 将成本价为0的设置为空值
result[result == 0] = np.nan
# 计算成本价
result['Cost price'] = result['Cost price'].fillna(result['Price'] * 0.7)
# 将Date设置为关键字
result['Date'] = pd.to_datetime(result['Date'])
result = result.set_index('Date')
# 生成2016-11-30到2019-1-2的每一天
index = [x.strftime('%Y-%m-%d') for x in list(pd.date_range(start='2016-11-30', end='2019-1-2'))]
# 创建列表
T = []
for i in index:
    data = {}
    a = np.nansum(result[i]['Number'] * result[i]['Price'])
    b = np.nansum(result[i]['Number'] * result[i]['Cost price'])
    data['date'] = i
    data['Turnover'] = "{:.2f}".format(a)
    data['Total_cost'] = "{:.2f}".format(b)
    T.append(data)
wb = openpyxl.Workbook()
ws = wb.active
head_row = ['日期',
            '营业额',
            '总成本']
ws.append(head_row)
for data in T:
    data_row = [data['date'],
                data['Turnover'],
                data['Total_cost']]

    ws.append(data_row)
wb.save('第一题数据.xlsx')