"""成本预测"""
import pandas as pd
import os
from functools import reduce
pd.options.mode.chained_assignment = None # default='warn'
from 成本周报.WEEKFUN import merge_data_fun, month_fun, worktime_change_fun, zxfee_fun, date_kuzhan_fun
from 成本周报.week_fee_fun.week_fun import ftcz_fun, gdcz_fun, tchcfee_fun, city_shaixuan_fun, area_fun
import calendar
import datetime
now = datetime.datetime.now()
"""参数修改：以周度进行刷新：07、14、21、"""
count_date = int(str(datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]))[0:7].replace('-','')+'14')
航头分摊比 = 0.58
os.chdir(r'D:\工作\经营分析\成本周报\成本周报自动化')
jk_city = pd.DataFrame(['上海','北京','深圳','广州','成都','西安','南京','杭州','武汉','重庆','青岛','长沙','昆明','郑州','济南','南昌','贵阳','合肥'])
jk_city.columns = ['区域']
"""输入数据源"""
#操作费：大仓费用-外包操作费：线上计费-变动数据计算-B2B代运营费按天汇总-外包操作费
b2b_wbczf_data = pd.read_excel(r'202303\大仓fdc报表_大仓fdc按天汇总报表_大仓fdc按天汇总报表.xlsx',sheet_name='大仓fdc报表_大仓fdc按天汇总报表_大仓fdc按天汇总报表')
b2b_wbczf_data = b2b_wbczf_data[b2b_wbczf_data ['仓名称'].str.contains('FDC')]
b2b_wbczf_data['计费日期'] = b2b_wbczf_data['计费日期'].apply(lambda x : x.replace('-',''))
wbczf_key = pd.read_excel(r'键值对匹配.xlsx',sheet_name='B2B代运营费按天汇总-外包操作费')
set1 = [b2b_wbczf_data,wbczf_key]
b2b_wbczf_data_handle = merge_data_fun(set1,['仓名称'])
b2b_wbczf_data_handle['计费费用'] = b2b_wbczf_data_handle['计费费用'] * 0.94
b2b_wbczf_data1 = b2b_wbczf_data_handle[b2b_wbczf_data_handle['费用项名称'] != '仓储费-库存托盘数'].groupby([ '业态', '分类', '区域','计费日期'])['计费费用'].sum().reset_index()
b2b_wbczf_data1.columns = [ '业态', '分类', '区域','计费日期','外包操作线上计费']
b2b_wbczf_data1['辅助日期'] = b2b_wbczf_data1['计费日期'].apply(lambda x : x[0:6])
#操作费：大仓费用-外包操作费：手工单
sgd_data = pd.read_excel(r'202303\大仓手工单管理_20230316.xlsx' , sheet_name = 'data')
sgd_data = sgd_data[sgd_data ['应用大仓名称'].str.contains('FDC')]
sgd_data = sgd_data[(sgd_data['状态']=='审批通过') | (sgd_data['状态']=='已提交')]
sgd_data['仓名称'] = sgd_data['应用大仓名称']
sgd_data['发生日期'] = sgd_data['发生日期'] .apply(lambda x : x[0:10].replace('-','') )
sgd_data['计费日期'] = sgd_data['发生日期']
sgd_data = sgd_data.drop(sgd_data[sgd_data['费用类型名称'].str.contains('装卸货费',na=False)].index, inplace = False)
set2 = [sgd_data,wbczf_key]
sgd_data_handle = merge_data_fun(set2,['仓名称']).groupby([ '业态', '分类', '区域','计费日期'])['未税金额(元)'].sum().reset_index()
sgd_data_handle['费用类型'] = '手工单'
sgd_data_handle.columns = [ '业态', '分类', '区域','计费日期','手工单费用','费用类型']
sgd_data_handle['辅助日期'] = sgd_data_handle['计费日期'].apply(lambda x : x[0:6])
#操作费：大仓费用-外包操作费：调拨装卸费
dbzx_data = pd.read_excel('202303/全国大仓出入库监控_20230316.xlsx',sheet_name = '全国大仓出入库监控')
dbzx_data = dbzx_data[dbzx_data['仓库名称'].str.contains('FDC')]
dbzx_data['区域'] = dbzx_data['子公司名称'].apply(lambda x : x[0:2])
dbzx_data['区域'] = dbzx_data['区域'].apply(lambda x : x.replace('贵州','贵阳'))
dbzx_data['区域'] = dbzx_data['区域'].apply(lambda x : x.replace('浙江','杭州'))
set3 = [jk_city,dbzx_data]
dbzx_data_handle = merge_data_fun(set3,['区域'])
dbzx_data_handle = dbzx_data_handle.replace('\n','', regex=True)
dbzx_data_handle = dbzx_data_handle.dropna(subset=['计划到店日期'])
dbzx_data_handle['作业日期'] =dbzx_data_handle.apply( lambda row : worktime_change_fun(row['计划到店日期']),axis = 1)
dbzx_data_handle['仓名称'] = dbzx_data_handle['仓库名称']
#装卸单价：CDC、RDC只有装货费，没有卸货费，线上计费的只有调拨出的配货额对应的费用
#自营仓外包装卸：包括装货费和卸货费；FDC有卸货费。
zxdj_data = pd.read_excel(r'键值对匹配.xlsx',sheet_name= '装卸单价')
set4 = [dbzx_data_handle,zxdj_data]
zxdj_data_handle = merge_data_fun(set4,['仓名称'])
# #装卸单价：CDC、RDC只有装货费，没有卸货费，线上计费的只有调拨出的配货额对应的费用
# #自营仓外包装卸：包括装货费和卸货费；FDC有卸货费。
dbzx_data_handle11= zxdj_data_handle[zxdj_data_handle['订单类型']=='调拨']
dbzx_data_handle1 = pd.DataFrame(dbzx_data_handle11,columns = ['分类', '业态','区域_x','计划到店日期','子公司名称','仓库名称','订单类型','实际入库箱数_采购规格','实际出库箱数_采购规格','单价','操作方式','作业日期'])
dbzx_data_handle1['装货费'] = dbzx_data_handle1['实际出库箱数_采购规格'] * dbzx_data_handle1['单价']*0.94
dbzx_data_handle1['卸货费']= dbzx_data_handle1['实际入库箱数_采购规格'] * dbzx_data_handle1['单价']*0.94
dbzx_data_handle1['装卸费'] = dbzx_data_handle1.apply(lambda row : zxfee_fun(row['操作方式'],row['装货费'],row['卸货费'],row['业态']),axis = 1)
dbzx_data_handle2 = dbzx_data_handle1.copy()
dbzx_data_handle1 = dbzx_data_handle1.groupby([ '业态', '分类', '区域_x','作业日期'])['装卸费'].sum().reset_index()
dbzx_data_handle1.columns = ['业态', '分类', '区域','计费日期','装卸费']
dbzx_data_handle1['辅助日期'] = dbzx_data_handle1['计费日期'].apply(lambda x : x[0:6])
##固定费用转天计算
#操作费：大仓费用-非生产人员成本：
from dateutil.relativedelta import relativedelta
ygxz_data = pd.read_excel(r'D:\工作\经营分析\成本周报\成本周报自动化\202303\人力成本模型_202303.xlsx',sheet_name = '底表')
ygxz_data = ygxz_data[(ygxz_data['仓类型'] == 'FDC') | (ygxz_data['仓类型'] == '园区中台') ]
from datetime import timedelta
ygxz_data['月份'] = ygxz_data['月份'].apply(lambda x : (datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))+datetime.timedelta(days=+1))
ygxz_data['月天数'] = ygxz_data['月份'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
#员工成本:未包含分摊人:需要把活鲜改为暂养中心
ygcost = ygxz_data[ygxz_data['计薪方式']!='时薪'].groupby(['区域名称','仓类型','仓性质','计薪方式','月份','月天数'])['人效成本_财务','FTE人数'].sum().reset_index()
def hxxg_fun(仓性质):
name = ''
if 仓性质=='活鲜仓':
name = '暂养中心'
else :
name =仓性质
return name
ygcost['仓性质'] = ygcost.apply(lambda row :hxxg_fun(row['仓性质']),axis = 1)
ygcost.columns = ['区域','业态','分类','计薪方式','计费时间','月天数','员工薪资','FTE人数']#仓内人数，不满勤
#分摊人员费用，需要手工计算。
yqft_data_tep = pd.read_excel(r'D:\工作\经营分析\成本周报\成本周报自动化\202303\人力成本模型_202303.xlsx',sheet_name = '分摊')
yqft_data_tep = yqft_data_tep[yqft_data_tep['仓类型'].str.contains('FDC')]
yqft_data_tep['月天数'] = yqft_data_tep['月份'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
yqft_data_tep1= pd.DataFrame(yqft_data_tep,columns = ['区域名称', '仓类型', '仓性质', '计薪方式', '月份', '月天数', '人效成本_财务', 'FTE人数'])
yqft_data_tep1.columns = ['区域', '业态', '分类', '计薪方式', '计费时间', '月天数', '员工薪资', 'FTE人数']
set8 = [ygcost,yqft_data_tep1]
ygcost2 = pd.concat(set8 ,axis = 0)
ygcost2['分类'] = ygcost2['分类'].apply(lambda x : x.replace('仓',''))
ygcost1 = ygcost2.groupby(['区域','业态','分类','计费时间','月天数'])['员工薪资','FTE人数'].sum().reset_index()
#航头分摊：6月：54%
def htft_fun(区域 , 业态 , 分类 , 员工薪资):
员工薪资1 = 0
if 区域 == '上海' and 业态 == 'FDC' and 分类=='冷链' :
print('hahahh')
员工薪资1 = 员工薪资 *航头分摊比
else :
员工薪资1 = 员工薪资
return 员工薪资1
ygcost1['员工薪资'] = ygcost1.apply(lambda row : htft_fun(row['区域'] , row['业态'] , row['分类'] , row['员工薪资']) , axis = 1)
ygcost1 = ygcost1.groupby(['区域','业态','分类','计费时间','月天数'])['员工薪资','FTE人数'].sum().reset_index()
# 除了自有员工的费用，还会涉及到园区共有人员(4个质控，46858*15%）和区域HR成本(3个，27550*24%）的分摊
#操作费：大仓费用-小时工成本：需要看员工出勤率
xsg_price_data = pd.read_excel(r'键值对匹配.xlsx',sheet_name = '小时工单价')
xsg_gs_data = pd.read_excel(r'202303/自营仓人效监控&日维度-202303.xlsx',sheet_name = 'Sheet1')
xsg_gs_data = xsg_gs_data[xsg_gs_data['业态'].str.contains('FDC')]
xsg_gs_data['计费时间'] = xsg_gs_data['业务'].apply(lambda x : str(x).replace('-','')[0:8])
set5 = [xsg_gs_data,xsg_price_data]
xsg_gs_data_handle = merge_data_fun(set5,['业态','分类','区域'])
xsg_gs_data_handle['小时工成本'] =xsg_gs_data_handle['小时工时'] * xsg_gs_data_handle['小时工单价']
xsg_gs_data_handle['辅助日期'] = xsg_gs_data['业务'].apply(lambda x : str(x).replace('-','')[0:6])
gdfee_data = pd.DataFrame(pd.read_excel(r'202303\物流管报_管理报表_业态明细_202303.xlsx',sheet_name='物流管报_管理报表_业态明细'),
columns=['业态仓类型性质', '表项名称', '区域名称', '期间', '当期数'])
gdfee_data = gdfee_data[gdfee_data['业态仓类型性质'].str.contains('FDC')]
gdfee_data['期间'] = gdfee_data['期间'].apply(lambda x : datetime.datetime(datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').year,
datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').month,
calendar.monthrange(datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').year, datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').month)[1])+datetime.timedelta(days=+1))
gdfee_data['月天数'] = gdfee_data['期间'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
gdfee_data['费用预测时间'] = gdfee_data['期间']
gdfee_data['当期数'] = -gdfee_data['当期数']
"""仓租分摊模式变更，被分摊区域的仓租设置为0，操作费需要清零，
分摊比例：
1、湾区常温FDC：深圳:广州=6:4，仅分摊操作费、仓租、配货箱数；
2、南京常温FDC：南京:合肥，仅分摊操作费、仓租、配货箱数；
3、南京常温FDC：南京:合肥，仅分摊操作费、仓租、配货箱数；
3、上海FDC：苏州的销售额+上海销售额"""
gdfee_data['当期数'] = gdfee_data.apply(lambda row : ftcz_fun(row['期间'],row['业态仓类型性质'],row['表项名称'],row['区域名称'],row['当期数']) , axis = 1)
gbkey_data = pd.read_excel(r'键值对匹配.xlsx',sheet_name = '固定费用-管报')
set6 = [gdfee_data,gbkey_data]
gdfee_data_handle = merge_data_fun(set6,['业态仓类型性质'])
gdfee_data_handle['区域名称'] = gdfee_data_handle['区域名称'].apply(lambda x : x.replace('-','').replace('区域',''))
"""大仓费用-仓租物业"""
#流量库位仓租
wbllcz_data = b2b_wbczf_data_handle[b2b_wbczf_data_handle['费用项名称'] == '仓储费-库存托盘数'].groupby([ '业态', '分类', '区域','计费日期'])['计费费用'].sum().reset_index()
wbllcz_data.columns = [ '业态', '分类', '区域名称','计费日期','仓租费用']
#固定仓租:园区分摊月度会有天数的变动,非园区的不会.需要更新逻辑
gdczfee_data = pd.read_excel(r'D:\工作\经营分析\成本周报\成本周报自动化\键值对匹配.xlsx',sheet_name='仓租').drop(columns = ['使用方' , '园区'])
gdczfee_data = gdczfee_data[gdczfee_data['业态'].str.contains('FDC')]
gdczfee_data['费用预测时间'] = gdczfee_data['费用预测时间'].apply(lambda x : datetime.datetime(datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').year,
datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').month,
calendar.monthrange(datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').year, datetime.datetime.strptime(str(x)[0:7].replace('-',''), '%Y%m').month)[1])+datetime.timedelta(days=+0))
gdczfee_data['月天数'] = gdczfee_data['费用预测时间'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
gdczfee_data1 = pd.DataFrame(gdczfee_data , columns = [ '业态', '分类', '区域名称','费用预测时间', '月天数', '仓租费用'] ).groupby(['业态', '分类', '区域名称','费用预测时间', '月天数'])['仓租费用'].sum().reset_index()
gdczfee_data1.columns = ['业态', '分类', '区域名称', '计费日期', '月天数','仓租费用']
# gdczfee_data1['操作类型'] = '固定仓租'
# wbllcz_data['操作类型'] = '流量仓租'
gdczfee_data1['辅助日期'] = gdczfee_data1['计费日期'].apply(lambda x : str(x)[0:7].replace('-',''))
wbllcz_data['辅助日期'] = wbllcz_data['计费日期'].apply(lambda x : str(x)[0:6])
"""大仓费用-折旧:5月份折旧异动,预测成本会偏低"""
zhejiu_data = pd.DataFrame(gdfee_data_handle[gdfee_data_handle['表项名称']=='大仓费用-折旧'],columns = ['业态', '分类', '区域名称','费用预测时间','当期数'])
zhejiu_data1 = zhejiu_data.groupby(['业态', '分类', '区域名称','费用预测时间'])['当期数'].sum().reset_index()
zhejiu_data1['月天数'] = zhejiu_data1['费用预测时间'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
zhejiu_data1['辅助日期'] = zhejiu_data1['费用预测时间'].astype(str).apply(lambda x : int(str(x)[0:7].replace('-','')))
"""大仓费用-水电费"""
shuidian_data = pd.DataFrame(gdfee_data_handle[gdfee_data_handle['表项名称']=='大仓费用-水电费'],columns = ['业态', '分类', '区域名称','费用预测时间','当期数'])
shuidian_data['月天数'] = shuidian_data['费用预测时间'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
shuidian_data['辅助日期'] = shuidian_data['费用预测时间'].astype(str).apply(lambda x : int(str(x)[0:7].replace('-','')))
"""大仓费用-其他运营费"""
yunying_fee_data1 = pd.DataFrame(gdfee_data_handle[(gdfee_data_handle['表项名称']=='大仓费用-其他运营费') |(gdfee_data_handle['表项名称']=='大仓费用-营业外收支') |(gdfee_data_handle['表项名称']=='大仓费用-门店端费用')],columns = ['业态', '分类', '区域名称','费用预测时间','当期数'])
yunying_fee_data = yunying_fee_data1.groupby(['业态', '分类', '区域名称', '费用预测时间'])['当期数'].sum().reset_index()
yunying_fee_data['月天数'] = yunying_fee_data['费用预测时间'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
yunying_fee_data['辅助日期'] = yunying_fee_data['费用预测时间'].astype(str).apply(lambda x : int(str(x)[0:7].replace('-','')))
"""大仓费用-耗材"""
haocai_data = pd.read_excel(r'202303/财务进销存日报（新）_TAB标签组_日明细数据（新）_202303.xlsx')
haocai_data = haocai_data[haocai_data['门店名称'].str.contains('FDC')]
haocai_data = haocai_data[(haocai_data['库存业务类型']== '采购在途出库') | (haocai_data['库存业务类型']== '采购在途入库')| (haocai_data['库存业务类型']== '采购入库')
| (haocai_data['库存业务类型']== '采购退货')| (haocai_data['库存业务类型']== '调拨出库')| (haocai_data['库存业务类型']== '调拨入库')
| (haocai_data['库存业务类型']== '配货出库')| (haocai_data['库存业务类型']== '生产消耗')]
haocai_data['仓名称'] = haocai_data['门店名称']
set8 = [haocai_data,wbczf_key]
haocai_data_handle = merge_data_fun(set8,['仓名称'])
haocai_data_count = haocai_data_handle.groupby(['业态', '分类', '区域','统计日期'])['发生未税成本金额汇总'].sum().reset_index()
haocai_data_count.columns = ['业态', '分类', '区域','计费日期','耗材费用']
#剔除费自营常温仓耗材
haocai_data_count['耗材费用_管报'] = haocai_data_count.apply(lambda row : tchcfee_fun(row['业态'],row['分类'],row['区域'],row['耗材费用']) , axis = 1)
haocai_data_count['辅助日期'] = haocai_data_count['计费日期'].astype(str).apply(lambda x : x[0:6])
"""大仓费用-宿舍成本"""
shushe_fee_data = pd.read_excel(r'202303\宿舍成本.xlsx').drop(columns = '仓名')
shushe_fee_data = shushe_fee_data[shushe_fee_data['业态'].str.contains('FDC')]
shushe_fee_data['月天数'] = shushe_fee_data['日期'].apply(lambda x : str(datetime.datetime(x.year, x.month, calendar.monthrange(x.year, x.month)[1]))[8:10])
shushe_fee_data=shushe_fee_data.groupby(['业态', '分类', '区域','日期','月天数'])['住宿费用'].sum().reset_index()
# shushe_fee_data.columns = ['业态', '分类', '区域','计费日期','住宿费用']
shushe_fee_data['辅助日期'] = shushe_fee_data['日期'].apply(lambda x : str(x)[0:7].replace('-',''))
shushe_fee_data.columns = ['业态', '分类', '区域','日期','月天数' ,'住宿费用','辅助日期']
shushe_fee_data['分类'] = shushe_fee_data['分类'].apply(lambda x : x.replace('仓',''))
"""结果合并"""
yunying_fee_data['分类'] = yunying_fee_data['分类'].apply(lambda x : x.replace('仓',''))
shuidian_data['分类'] = shuidian_data['分类'].apply(lambda x : str(x).replace('仓',''))
zhejiu_data1['分类'] = zhejiu_data1['分类'].apply(lambda x : x.replace('仓',''))
b2b_wbczf_data1['辅助日期'] = b2b_wbczf_data1['辅助日期'].astype(int); sgd_data_handle['辅助日期'] = sgd_data_handle['辅助日期'].astype(int)
dbzx_data_handle1['辅助日期'] = dbzx_data_handle1['辅助日期'].astype(int); xsg_gs_data_handle['辅助日期'] = xsg_gs_data_handle['辅助日期'].astype(int)
ygcost1['辅助日期'] = ygcost1['计费时间'].apply(lambda x : str(x)[0:7].replace('-',''))
ygcost1['辅助日期'] = ygcost1['辅助日期'].astype(int); gdczfee_data1['辅助日期'] = gdczfee_data1['辅助日期'].astype(int)
wbllcz_data['辅助日期'] = wbllcz_data['辅助日期'].astype(int); yunying_fee_data['辅助日期'] = yunying_fee_data['辅助日期'].astype(int)
shuidian_data['辅助日期'] = shuidian_data['辅助日期'].astype(int);
haocai_data_count['辅助日期'] = haocai_data_count['辅助日期'].astype(int); zhejiu_data1['辅助日期'] = zhejiu_data1['辅助日期'].astype(int); shushe_fee_data['辅助日期'] = shushe_fee_data['辅助日期'].astype(int)
xsg_gs_data_handle1 = pd.DataFrame(xsg_gs_data_handle , columns = ['小时工时', '区域', '业态', '分类', '计费日期', '小时工单价', '小时工成本', '辅助日期'])
wbllcz_data.columns = ['业态', '分类', '区域', '计费日期', '仓租费用', '辅助日期']
xsg_gs_data_handle.columns = ['业务', '小时工时', '区域', '业态', '分类', '计费日期', '小时工单价', '小时工成本', '辅助日期']
#by天的结果合并
set_merge1 =[wbllcz_data,b2b_wbczf_data1,sgd_data_handle,dbzx_data_handle1,xsg_gs_data_handle,haocai_data_count]
wbllcz_data['计费日期'] = wbllcz_data['计费日期'].astype(int); b2b_wbczf_data1['计费日期'] = b2b_wbczf_data1['计费日期'].astype(int)
sgd_data_handle['计费日期'] = sgd_data_handle['计费日期'].astype(int); dbzx_data_handle1['计费日期'] = dbzx_data_handle1['计费日期'].astype(int)
xsg_gs_data_handle['计费日期'] = xsg_gs_data_handle['计费日期'].astype(int); haocai_data_count['计费日期'] = haocai_data_count['计费日期'].astype(int)
data1 = reduce(lambda left , right : pd.merge(left , right ,on = ['业态', '分类', '区域', '计费日期','辅助日期'],how = 'outer'),set_merge1)
data1.columns = ['业态', '分类', '区域', '计费日期', '仓租费用_流量', '辅助日期', '外包操作线上计费', '手工单费用', '费用类型',
'装卸费', '业务', '小时工时', '小时工单价', '小时工成本', '耗材费用', '耗材费用_管报']
#by月的结果合并
shushe_fee_data = shushe_fee_data.drop(columns = ['日期']);yunying_fee_data = yunying_fee_data.drop(columns = ['费用预测时间']);shuidian_data = shuidian_data.drop(columns = ['费用预测时间']);
zhejiu_data1= zhejiu_data1.drop(columns = ['费用预测时间']);
ygcost1 =ygcost1.drop(columns = ['计费时间'])
ygcost1.columns = ['区域名称', '业态', '分类', '月天数', '员工薪资', 'FTE人数', '辅助日期']
yunying_fee_data.columns = ['业态', '分类', '区域名称', '运营费用', '月天数', '辅助日期']
shuidian_data.columns = ['业态', '分类', '区域名称', '水电费用', '月天数', '辅助日期']
zhejiu_data1.columns = ['业态', '分类', '区域名称', '折旧费用', '月天数', '辅助日期']
shushe_fee_data.columns = ['业态', '分类', '区域名称', '月天数', '住宿费用', '辅助日期']
# 字段统一:
zhejiu_data1['业态'] = zhejiu_data1['业态'] .apply(lambda x : x.replace('仓','')) ;
zhejiu_data1['分类'] = zhejiu_data1['分类'] .apply(lambda x : x.replace('仓',''))
shushe_fee_data['业态'] = shushe_fee_data['业态'] .apply(lambda x : x.replace('仓','')) ;
shushe_fee_data['分类'] = shushe_fee_data['分类'] .apply(lambda x : x.replace('仓',''))
ygcost1['分类'] = ygcost1['分类'] .apply(lambda x : x.replace('仓',''))
set_merge2 = [yunying_fee_data ,shuidian_data,zhejiu_data1, ygcost1,gdczfee_data1, shushe_fee_data]
data2 = reduce(lambda left , right : pd.merge(left , right ,on = ['业态', '分类', '区域名称','辅助日期','月天数'],how = 'outer'),set_merge2)
data2['月天数'] = data2['月天数'].astype(int)
#结果合并
data2.columns =['业态', '分类', '区域', '运营费用', '月天数', '辅助日期', '水电费用', '折旧费用', '员工薪资',
'FTE人数', '计费日期', '仓租费用-固定', '住宿费用']
set_merge3 = [data1, data2]
data1['辅助日期'] = data1['辅助日期'].astype(int)
data2['辅助日期'] = data2['辅助日期'].astype(int)
data = reduce(lambda left , right : pd.merge(left , right ,on = ['业态', '分类', '区域','辅助日期'],how = 'outer'),set_merge3)
#跨城配货成本划分
# 青岛分摊比例:外包操作费
data['计费日期'] = data['计费日期_x']; data = data.drop(columns = ['计费日期_x','计费日期_y']);
data = data[data['计费日期']!=0]
#采购出库箱数
# zxdj_data_handle['计划到店日期'] = zxdj_data_handle['计划到店日期'].astype(int)
out_num_data = dbzx_data_handle
out_num_data = out_num_data[out_num_data['订单类型'] == '整体']
out_key = pd.read_excel(r'键值对匹配.xlsx',sheet_name='配货箱数')
out_num_data['仓名称'] = out_num_data['仓库名称']
set10 = [out_num_data , out_key]
out_num_data1 = merge_data_fun(set10 , ['子公司名称','区域','仓名称'])
out_num_data2 = out_num_data1.groupby( ['子公司名称','区域','业态','分类','计划到店日期'])['实际入库箱数_采购规格','实际出库箱数_采购规格'].sum().reset_index()
out_num_data2.columns = ['子公司名称','区域','业态','分类','计费日期','实际入库箱数','最终配货出库箱数']
out_num_data2['计费日期'] = out_num_data2['计费日期'].astype(int)
#区域还原
data['区域_辅助'] = data.apply(lambda row : area_fun(row['业态'],row['分类'],row['区域']),axis = 1)
data3 = data[(data['业态'] == 'FDC' )& (((data['分类'] == '常温') & ((data['区域'] == '合肥') | (data['区域'] == '苏州') | (data['区域'] == '深圳')))|((data['分类'] == '冷链') & ((data['区域'] == '合肥') | (data['区域'] == '苏州'))))]
data3_temp = data3.groupby(['业态', '分类', '区域_辅助','辅助日期','月天数'])['仓租费用-固定','运营费用','水电费用','折旧费用'].mean().reset_index()
data3_temp.columns = ['业态', '分类', '区域_辅助', '辅助日期','月天数','仓租费用_分摊','运营费用_分摊','水电费用_分摊','折旧费用_分摊']
data3_temp['仓租费用_分摊'] = data3_temp['仓租费用_分摊']/data3_temp['月天数']
data3_temp['运营费用_分摊'] = data3_temp['运营费用_分摊']/data3_temp['月天数']
data3_temp['水电费用_分摊'] = data3_temp['水电费用_分摊']/data3_temp['月天数']
data3_temp['折旧费用_分摊'] = data3_temp['折旧费用_分摊']/data3_temp['月天数']
out_num_data2['区域_辅助'] = out_num_data2.apply(lambda row : area_fun(row['业态'],row['分类'],row['区域']),axis = 1)
# data_tep['单箱成本'] = data_tep['成本总计_合并'] / data_tep['配货箱数_合并']
set9 = [data,data3_temp]
data_totall = merge_data_fun(set9 , ['业态', '分类', '区域_辅助', '辅助日期','月天数'])
"""计费空值填充"""
data_totall['运营费用'] = data_totall['运营费用'].fillna(0)
data_totall['运营费用_分摊'] = data_totall['运营费用_分摊'].fillna(0)
data_totall['水电费用'] = data_totall['水电费用'].fillna(0)
data_totall['水电费用_分摊'] = data_totall['水电费用_分摊'].fillna(0)
data_totall['折旧费用'] = data_totall['折旧费用'].fillna(0)
data_totall['折旧费用_分摊'] = data_totall['折旧费用_分摊'].fillna(0)
data_totall['员工薪资'] = data_totall['员工薪资'].fillna(0)
data_totall['FTE人数'] = data_totall['FTE人数'].fillna(0)
data_totall['仓租费用-固定'] = data_totall['仓租费用-固定'].fillna(0)
data_totall['仓租费用_流量'] = data_totall['仓租费用_流量'].fillna(0)
data_totall['仓租费用_分摊'] = data_totall['仓租费用_分摊'].fillna(0)
data_totall['住宿费用'] = data_totall['住宿费用'].fillna(0)
data_totall['外包操作线上计费'] = data_totall['外包操作线上计费'].fillna(0)
data_totall['手工单费用'] = data_totall['手工单费用'].fillna(0)
data_totall['装卸费'] = data_totall['装卸费'].fillna(0)
data_totall['小时工成本'] = data_totall['小时工成本'].fillna(0)
#by天计算
data_totall['运营费用_天'] = data_totall['运营费用']/data_totall['月天数']+data_totall['运营费用_分摊']
data_totall['水电费用_天']= data_totall['水电费用']/data_totall['月天数']+data_totall['水电费用_分摊']
data_totall['折旧费用_天'] = data_totall['折旧费用']/data_totall['月天数']+data_totall['折旧费用_分摊']
data_totall['员工薪资_天'] = data_totall['员工薪资']/data_totall['月天数']
data_totall['FTE人数_天'] = data_totall['FTE人数']/data_totall['月天数']
data_totall['仓租费用_天'] = data_totall['仓租费用-固定']/data_totall['月天数']+data_totall['仓租费用_流量']+data_totall['仓租费用_分摊']
data_totall['住宿费用_天'] = data_totall['住宿费用']/data_totall['月天数']
"""限制计费区域为管报区域，保证全国费用加总无异常"""
data_totall['城市'] = data_totall.apply(lambda row : city_shaixuan_fun(row['区域']),axis = 1)
set11 = [data_totall,out_num_data2]
data_totall= data_totall.fillna(0)
data_total = merge_data_fun(set11 , ['区域', '业态', '分类', '计费日期', '区域_辅助'])
data_total = data_total[~((data_total['业态'] == 'FDC' )& (((data_total['分类'] == '常温') & ((data_total['区域'] == '合肥') | (data_total['区域'] == '苏州') | (data_total['区域'] == '深圳')))|((data_total['区域'] == '冷链')
& ((data_total['区域'] == '合肥') | (data_total['区域'] == '苏州')))))]
data_total = data_total.fillna(0)
data_total['成本总计'] = data_total['外包操作线上计费']+data_total['手工单费用']+data_total['装卸费']+data_total['小时工成本']+data_total['水电费用_天']+\
data_total['员工薪资_天']+data_total['仓租费用_天']+data_total['运营费用_天']+data_total['耗材费用_管报']+data_total['折旧费用_天']+data_total['住宿费用_天']
data_total['单箱成本'] = data_total['成本总计'] / data_total['最终配货出库箱数']
data_total = data_total[(data_total['计费日期'] != 0)]
#增加全国的指标
qg_data_tep1 = data_total.groupby(['业态','分类','月天数','计费日期','辅助日期'])['仓租费用-固定','折旧费用','员工薪资','FTE人数','住宿费用','运营费用','水电费用','小时工单价'].mean().reset_index()
qg_data_tep2 = data_total.groupby(['业态','分类','月天数','计费日期','辅助日期'])['仓租费用_流量','外包操作线上计费','手工单费用','装卸费','小时工时','小时工成本','耗材费用','耗材费用_管报','运营费用_天','水电费用_天','折旧费用_天',
'员工薪资_天', 'FTE人数_天','仓租费用_天','住宿费用_天','成本总计','实际入库箱数','最终配货出库箱数','仓租费用_分摊','运营费用_分摊','水电费用_分摊',
'折旧费用_分摊'].sum().reset_index()
set11 = [qg_data_tep1,qg_data_tep2]
qg_total_merge = reduce(lambda left , right : pd.merge(left , right ,on = ['业态','分类','月天数','计费日期','辅助日期'],how = 'outer'),set11)
qg_total_merge=qg_total_merge.fillna(0)
qg_total_merge['单箱成本'] = qg_total_merge['成本总计'] / qg_total_merge['最终配货出库箱数']
qg_total_merge['区域_辅助'] = '全国'
qg_total_merge1 = pd.DataFrame(qg_total_merge,columns = ['业态','分类','区域_辅助','计费日期','辅助日期','月天数','仓租费用-固定','仓租费用_流量','折旧费用','水电费用','员工薪资','FTE人数','住宿费用','运营费用',
'小时工单价','外包操作线上计费','手工单费用','装卸费','小时工时','小时工成本','耗材费用','耗材费用_管报','运营费用_天','水电费用_天','折旧费用_天','员工薪资_天',
'FTE人数_天','仓租费用_天','住宿费用_天','仓租费用_分摊','运营费用_分摊','水电费用_分摊','折旧费用_分摊','成本总计','实际入库箱数','最终配货出库箱数','单箱成本'
])
data_tota2 = pd.DataFrame(data_total,columns = ['业态','分类','区域_辅助','计费日期','辅助日期','月天数','仓租费用-固定','仓租费用_流量','折旧费用','水电费用','员工薪资','FTE人数','住宿费用','运营费用',
'小时工单价','外包操作线上计费','手工单费用','装卸费','小时工时','小时工成本','耗材费用','耗材费用_管报','运营费用_天','水电费用_天','折旧费用_天','员工薪资_天',
'FTE人数_天','仓租费用_天','住宿费用_天','仓租费用_分摊','运营费用_分摊','水电费用_分摊','折旧费用_分摊','成本总计','实际入库箱数','最终配货出库箱数','单箱成本'
])
data_total1 =pd.concat([ data_tota2,qg_total_merge1 ] ,axis = 0)
data_total1 = data_total1[data_total1['计费日期']!=0]
"""预算目标:目标合并"""
ystarget_data = pd.read_excel(r'键值对匹配.xlsx' , sheet_name='预算值')
ystarget_data = ystarget_data.replace('\n','', regex=True)
# # now_month_ys = ystarget_data[ystarget_data['日期'] == 202303]
ystarget_data.columns = ['区域','月目标销售费率','日期','分类', '业态', '月目标单箱成本','月目标配货箱数','月目标配货额','月目标销售额']
ystarget_data['区域_辅助'] = ystarget_data.apply(lambda row : area_fun(row['业态'],row['分类'],row['区域']),axis = 1).astype(str)
ystarget_data['辅助日期'] = ystarget_data['日期']
ystarget_data['月目标成本'] = ystarget_data['月目标销售费率'] * ystarget_data['月目标销售额']
# 单天成本
ystarget_data_tep = pd.DataFrame(ystarget_data,columns=['辅助日期','分类', '区域_辅助','业态', '月目标成本','月目标配货箱数','月目标配货额','月目标销售额']).groupby(['辅助日期','分类', '区域_辅助','业态'])[ '月目标成本','月目标配货箱数','月目标配货额','月目标销售额'].sum().reset_index()
ystarget_data_tep['月目标单箱成本'] = ystarget_data_tep['月目标成本']/ystarget_data_tep['月目标配货箱数']
ystarget_data_tep['月目标销售费率'] = ystarget_data_tep['月目标成本']/ystarget_data_tep['月目标销售额']
ystarget_data_tep=ystarget_data_tep.fillna(0)
ystarget_data1 = pd.DataFrame(ystarget_data_tep,columns = ['区域_辅助','月目标销售费率','辅助日期','分类', '业态', '月目标单箱成本','月目标成本','月目标配货箱数','月目标配货额','月目标销售额'])
# data_tota2 = data_total1[(data_total1['业态']=='FDC') & (data_total1['分类'] != '暂养中心')]
data_tota2 = data_total1[(data_total1['业态']=='FDC')]
data_tota2['区域_辅助'] = data_tota2['区域_辅助'].astype(str)
set12 = [data_tota2,ystarget_data1]
data_tota3 = reduce(lambda left , right : pd.merge(left , right ,on = ['业态', '分类', '区域_辅助','辅助日期'],how = 'left'),set12)
data_tota3=data_tota3.fillna(0)
# data_cost =
data_tota3['大仓费用-操作费'] = data_tota3['外包操作线上计费']+data_tota3['手工单费用']+data_tota3['装卸费']+data_tota3['小时工成本']+data_tota3['员工薪资_天']
data_tota3['大仓费用-仓租'] = data_tota3['仓租费用_天']+data_tota3['住宿费用_天']
data_tota3['大仓费用-运营费'] = data_tota3['运营费用_天']
data_tota3['大仓费用-折旧费'] = data_tota3['折旧费用_天']
data_tota3['大仓费用-水电费'] = data_tota3['水电费用_天']
"""大盘：
一、不同业态分摊
RDC：分摊不存在，有单独仓code；
奥莱：仓租固定划分，不分摊，除仓租外的所有配货箱数、成本项按照奥莱配货箱数/FDC配货箱数的占比进行成本划分。
二、同FDC分摊比例：
1、湾区常温FDC：深圳:广州=6:4，仅分摊操作费、仓租、配货箱数；
2、南京常温FDC：南京:合肥，仅分摊操作费、仓租、配货箱数；
3、南京常温FDC：南京:合肥，仅分摊操作费、仓租、配货箱数；
3、上海FDC：苏州的销售额+上海销售额
4、青岛配济南门店商品箱数：涉及仓租+操作费的分摊（暂未解决）
"""
#实际销售额:需要进行半月度滚动，所有月份只滚动到14日
fdc_sale_data = pd.read_excel(r'D:\工作\经营分析\成本周报\成本周报自动化\202303\销售额.xlsx' , sheet_name = 'Sheet1')
fdc_sale_data['区域_辅助'] = fdc_sale_data.apply(lambda row : area_fun(row['业态'],row['分类'],row['区域']),axis = 1)
fdc_sale_data1 =pd.DataFrame(fdc_sale_data,columns = ['实际收入', '辅助日期', '业态', '分类', '区域_辅助']).groupby([ '业态', '分类', '区域_辅助','辅助日期'])['实际收入'].sum().reset_index()
qg_sale_tep = fdc_sale_data1.groupby([ '业态', '分类','辅助日期'])['实际收入'].sum().reset_index()
qg_sale_tep['区域_辅助'] = '全国'
qg_sale = pd.DataFrame(qg_sale_tep , columns = ['业态', '分类', '区域_辅助', '辅助日期', '实际收入'])
fdc_sale_data2 = pd.concat([fdc_sale_data1,qg_sale],axis = 0)
#确保数据小于等于当月14日，根据实际情况修改
"""根据需求进行变更"""
data_tota3 = data_tota3[data_tota3['计费日期']<=count_date]
data_tota3_merge1 = pd.DataFrame(data_tota3 , columns = ['业态', '分类', '区域_辅助','辅助日期', '月天数', '外包操作线上计费','手工单费用','装卸费','住宿费用_天','小时工成本',
'员工薪资_天','仓租费用_天','运营费用_天','耗材费用_管报', '折旧费用_天', '水电费用_天','成本总计', '大仓费用-操作费', '大仓费用-仓租',
'大仓费用-运营费', '大仓费用-折旧费', '大仓费用-水电费', '实际收入_天','实际入库箱数','最终配货出库箱数']).groupby(['业态', '分类', '区域_辅助','辅助日期', '月天数'])\
['外包操作线上计费','手工单费用','装卸费','住宿费用_天','小时工成本',
'员工薪资_天','仓租费用_天','运营费用_天','耗材费用_管报', '折旧费用_天', '水电费用_天', '成本总计','大仓费用-操作费', '大仓费用-仓租',
'大仓费用-运营费', '大仓费用-折旧费', '大仓费用-水电费','实际入库箱数','最终配货出库箱数'].sum().reset_index()
set13 = [data_tota3_merge1,fdc_sale_data2]
data_tota3_merge = reduce( lambda left , right :pd.merge(left , right, on = [ '业态', '分类', '区域_辅助','辅助日期'] , how = 'left'),set13)
#单箱情况
data_tota3_merge['单箱-成本总计'] = data_tota3_merge['成本总计'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-操作费'] = data_tota3_merge['大仓费用-操作费'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-仓租'] = data_tota3_merge['大仓费用-仓租'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-运营费'] = data_tota3_merge['大仓费用-运营费'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-折旧费'] = data_tota3_merge['大仓费用-折旧费'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-水电费'] = data_tota3_merge['大仓费用-水电费'] /data_tota3_merge['最终配货出库箱数']
data_tota3_merge['单箱-包耗材'] = data_tota3_merge['耗材费用_管报'] /data_tota3_merge['最终配货出库箱数']
#销售费率情况
data_tota3_merge['销售费率-成本总计'] = data_tota3_merge['成本总计'] /data_tota3_merge['实际收入']
data_tota3_merge['销售费率-操作费'] = data_tota3_merge['大仓费用-操作费'] /data_tota3_merge['实际收入']
data_tota3_merge['销售费率-仓租'] = data_tota3_merge['大仓费用-仓租'] /data_tota3_merge['实际收入']
data_tota3_merge['销售费率-运营费'] = data_tota3_merge['大仓费用-运营费'] /data_tota3_merge['实际收入']
data_tota3_merge['销售费率-折旧费'] = data_tota3_merge['大仓费用-折旧费'] /data_tota3_merge['实际收入']
data_tota3_merge['销售费率-水电费'] = data_tota3_merge['大仓费用-水电费'] /data_tota3_merge['实际收入']
#仓端目标
target_cost = pd.DataFrame(data_tota3[data_tota3['辅助日期'] >= 202304],columns = ['业态', '分类', '区域_辅助', '辅助日期', '月天数','月目标销售费率',
'月目标单箱成本', '月目标成本', '月目标配货箱数', '月目标配货额', '月目标销售额']).drop_duplicates()
#目标与实际大仓合并
set14 = [target_cost , data_tota3_merge]
data_total4 = reduce(lambda left , right : pd.merge(left , right , on = ['业态', '分类', '区域_辅助', '辅助日期', '月天数'],how = 'left'),set14)
#环比校验
#历史月份管报数据处理
gdfee_data_handle['当期数'] = gdfee_data_handle['当期数']/-1
gdfee_data_handle1 =gdfee_data_handle[(gdfee_data_handle['表项名称'] == '大仓费用-仓租物业')|(gdfee_data_handle['表项名称'] == '大仓费用-自营操作费')
|(gdfee_data_handle['表项名称'] == '大仓费用-非生产人员成本')|(gdfee_data_handle['表项名称'] == '大仓费用-外包操作费')
|(gdfee_data_handle['表项名称'] == '大仓费用-其他运营费')|(gdfee_data_handle['表项名称'] == '大仓费用-营业外收支')
|(gdfee_data_handle['表项名称'] == '大仓费用-门店端费用')|(gdfee_data_handle['表项名称'] == '大仓费用-水电费')
|(gdfee_data_handle['表项名称'] == '大仓费用-包耗材')|(gdfee_data_handle['表项名称'] == '大仓费用-折旧及摊销')|(gdfee_data_handle['表项名称'] == '配货箱数')]
gdfee_data_handle2 = pd.DataFrame(gdfee_data_handle1,columns =['当期数', '表项名称', '区域名称','月天数', '费用预测时间', '业态', '分类'] ).reset_index().drop_duplicates()
#转秩
gdfee_data_pivot = gdfee_data_handle2.pivot_table(index = ['业态','分类','区域名称','费用预测时间','月天数'],columns = '表项名称' , values = '当期数').reset_index()
gdfee_data_pivot['配货箱数'] = gdfee_data_pivot['配货箱数']/-1
gdfee_data_pivot = gdfee_data_pivot.fillna(0)
"""增加湾区、南京-合肥、上海-苏州"""
#南京-合肥常温
njhf_tep = pd.DataFrame(gdfee_data_pivot[(gdfee_data_pivot['业态'] == 'FDC')&(gdfee_data_pivot['分类'] == '常温')&((gdfee_data_pivot['区域名称'] == '南京') | (gdfee_data_pivot['区域名称'] == '合肥'))]).\
groupby(['业态','分类','费用预测时间','月天数'])['大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'].sum().reset_index()
njhf_tep['区域名称'] = '南京-合肥'
njhf_cw = pd.DataFrame(njhf_tep , columns = ['业态', '分类', '区域名称', '费用预测时间', '月天数', '大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'])
#南京-合肥冷链
njhf_tep = pd.DataFrame(gdfee_data_pivot[(gdfee_data_pivot['业态'] == 'FDC')&(gdfee_data_pivot['分类'] == '冷链')&((gdfee_data_pivot['区域名称'] == '南京') | (gdfee_data_pivot['区域名称'] == '合肥'))]).\
groupby(['业态','分类','费用预测时间','月天数'])['大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'].sum().reset_index()
njhf_tep['区域名称'] = '南京-合肥'
njhf_ll = pd.DataFrame(njhf_tep , columns = ['业态', '分类', '区域名称', '费用预测时间', '月天数', '大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'])
#湾区常温
gzsz_tep = pd.DataFrame(gdfee_data_pivot[(gdfee_data_pivot['业态'] == 'FDC')&(gdfee_data_pivot['分类'] == '常温')&((gdfee_data_pivot['区域名称'] == '广州') | (gdfee_data_pivot['区域名称'] == '深圳'))]).\
groupby(['业态','分类','费用预测时间','月天数'])['大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'].sum().reset_index()
gzsz_tep['区域名称'] = '湾区'
gzsz_cw = pd.DataFrame(gzsz_tep , columns = ['业态', '分类', '区域名称', '费用预测时间', '月天数', '大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'])
#上海-苏州常温
shsz_tep = pd.DataFrame(gdfee_data_pivot[(gdfee_data_pivot['业态'] == 'FDC')&(gdfee_data_pivot['分类'] == '常温')&((gdfee_data_pivot['区域名称'] == '苏州') | (gdfee_data_pivot['区域名称'] == '上海'))]).\
groupby(['业态','分类','费用预测时间','月天数'])['大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'].sum().reset_index()
shsz_tep['区域名称'] = '上海-苏州'
shsz_cw = pd.DataFrame(shsz_tep , columns = ['业态', '分类', '区域名称', '费用预测时间', '月天数', '大仓费用-仓租物业', '大仓费用-其他运营费',
'大仓费用-包耗材', '大仓费用-外包操作费', '大仓费用-折旧及摊销', '大仓费用-水电费', '大仓费用-自营操作费',
'大仓费用-营业外收支', '大仓费用-门店端费用', '大仓费用-非生产人员成本', '配货箱数'])
#合并
set15 = [gdfee_data_pivot,njhf_cw,njhf_ll,gzsz_cw,shsz_cw]
gdfee_data_pivot1 = pd.concat(set15 , axis = 0).reset_index().fillna(0)
#单项成本加总
gdfee_data_pivot1['大仓费用-操作-生产'] = gdfee_data_pivot1['大仓费用-自营操作费'] +gdfee_data_pivot1['大仓费用-非生产人员成本'] +gdfee_data_pivot1['大仓费用-外包操作费']
gdfee_data_pivot1['大仓费用-运营'] = gdfee_data_pivot1['大仓费用-营业外收支'] +gdfee_data_pivot1['大仓费用-门店端费用'] +gdfee_data_pivot1['大仓费用-其他运营费']
gdfee_data_pivot1['成本合计'] =gdfee_data_pivot1['大仓费用-仓租物业']+gdfee_data_pivot1['大仓费用-自营操作费']+gdfee_data_pivot1['大仓费用-非生产人员成本']+\
gdfee_data_pivot1['大仓费用-外包操作费']+gdfee_data_pivot1['大仓费用-其他运营费']+gdfee_data_pivot1['大仓费用-营业外收支']+gdfee_data_pivot1['大仓费用-门店端费用']+\
gdfee_data_pivot1['大仓费用-水电费']+gdfee_data_pivot1['大仓费用-包耗材']+gdfee_data_pivot1['大仓费用-折旧及摊销']
gdfee_data_pivot1['单箱-成本总计'] = gdfee_data_pivot1['成本合计']/gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-操作费'] = gdfee_data_pivot1['大仓费用-操作-生产'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-仓租'] = gdfee_data_pivot1['大仓费用-仓租物业'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-运营费'] = gdfee_data_pivot1['大仓费用-运营'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-折旧费'] = gdfee_data_pivot1['大仓费用-折旧及摊销'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-水电费'] = gdfee_data_pivot1['大仓费用-水电费'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['单箱-包耗材'] = gdfee_data_pivot1['大仓费用-包耗材'] /gdfee_data_pivot1['配货箱数']
gdfee_data_pivot1['辅助日期'] = gdfee_data_pivot1['费用预测时间'].apply(lambda x : str(x)[0:7].replace('-','')).astype(int)
gdfee_data_pivot1['管报天数'] = gdfee_data_pivot1['费用预测时间'].apply(lambda x : str(x+datetime.timedelta(days=-1))[8:10]).astype(int)
gdfee_data_pivot1['区域_辅助'] = gdfee_data_pivot1['区域名称']
gdfee_data_pivot1 = gdfee_data_pivot1.fillna(0)
gdfee_data_pivot1 = gdfee_data_pivot1.drop(columns = ['费用预测时间','区域名称','index'])
gdfee_data_pivot1.columns =]
#合并
gdfee_data_pivot1['月天数'] = gdfee_data_pivot1['月天数'].astype(int)
set16 = [data_total4,gdfee_data_pivot1]
data_total5 = reduce(lambda left, right : pd.merge(left , right, on =['业态', '分类', '月天数','辅助日期', '区域_辅助' ],how='left'),set16)
#达成情况比较
data_total5['销售费率-达成'] = data_total5['销售费率-成本总计']/data_total5['月目标销售费率']
data_total5['单箱-成本总计-达成'] = data_total5['单箱-成本总计']/data_total5[ '月目标单箱成本']
#成本预算达成进度
data_total5['销售额-预算进度'] = data_total5['实际收入']/data_total5['月目标销售额']
data_total5['配货箱数-预算进度'] = data_total5['最终配货出库箱数']/data_total5['月目标配货箱数']
data_total5['成本-预算进度'] = data_total5['成本总计']/data_total5['月目标成本']
#单箱环比差情况
data_total5['单箱-成本总计-环比差'] = data_total5['单箱-成本总计']-data_total5[ '单箱-成本总计_上期']
data_total5['单箱-操作费-环比差'] = data_total5['单箱-操作费']-data_total5[ '单箱-操作费_上期']
data_total5['单箱-仓租-环比差'] = data_total5['单箱-仓租']-data_total5[ '单箱-仓租_上期']
data_total5['单箱-运营费-环比差'] = data_total5['单箱-运营费']-data_total5[ '单箱-运营费_上期']
data_total5['单箱-折旧费-环比差'] = data_total5['单箱-折旧费']-data_total5[ '单箱-折旧费_上期']
data_total5['单箱-水电费-环比差'] = data_total5['单箱-水电费']-data_total5[ '单箱-水电费_上期']
data_total5['单箱-包耗材-环比差'] = data_total5['单箱-包耗材']-data_total5[ '单箱-包耗材_上期']
data_total5['配货箱数-环比均差'] = data_total5['最终配货出库箱数']-abs((data_total5[ '配货箱数_上期']/data_total5['管报天数'])*int(str(count_date)[6:8]))
data_total5[ '配货箱数_上期'] = data_total5[ '配货箱数_上期']*(-1)
fdc_rank_data = pd.read_excel(r'键值对匹配.xlsx',sheet_name='FDC结果排序')
set_rank = [fdc_rank_data ,data_total5 ]
ran_data = reduce(lambda x,y : pd.merge(x,y , on = ['业态','分类','区域_辅助'],how ='left') , set_rank)
data_total6 = pd.DataFrame(ran_data , columns = ['业态'
,'分类'
,'区域_辅助'
,'辅助日期'
,'月天数'
,'销售费率-仓运'
,'销售费率-成本总计'
,'销售费率-仓租'
,'销售费率-操作费'
,'销售费率-折旧费'
,'销售费率-水电费'
,'销售费率-运营费'
,'销售费率-运输费'
,'单箱-仓运'
,'单箱-成本总计'
,'单箱-仓租'
,'单箱-操作费'
,'单箱-折旧费'
,'单箱-水电费'
,'单箱-运营费'
,'单箱-包耗材'
,'单箱-运输费'
,'实际收入'
,'仓运成本'
,'成本总计'
,'运输费用'
,'最终配货出库箱数'
,'实际入库箱数'
,'销售费率-达成'
,'单箱-仓运成本-达成'
,'单箱-成本总计-达成'
,'销售额-预算进度'
,'配货箱数-预算进度'
,'仓内成本-预算进度'
,'配货箱数_上期'
,'单箱-仓运成本-环比差'
,'单箱-仓内成本-环比差'
,'单箱-仓租-环比差'
,'单箱-操作费-环比差'
,'单箱-折旧费-环比差'
,'单箱-水电费-环比差'
,'单箱-运营费-环比差'
,'单箱-包耗材-环比差'
,'单箱-运输成本-环比差'
,'配货箱数-环比均差'
,'成本总计'
,'大仓费用-仓租'
,'仓租费用_天'
,'住宿费用_天'
,'大仓费用-操作费'
,'外包操作线上计费'
,'手工单费用'
,'装卸费'
,'小时工成本'
,'员工薪资_天'
,'大仓费用-运营费'
,'运营费用_天'
,'大仓费用-折旧费'
,'折旧费用_天'
,'大仓费用-水电费'
,'水电费用_天'
,'耗材费用_管报'
,'仓运成本合计-运营_上期'
,'大仓费用-仓租物业_上期'
,'大仓费用-其他运营费_上期'
,'大仓费用-包耗材_上期'
,'大仓费用-外包操作费_上期'
,'大仓费用-折旧及摊销_上期'
,'大仓费用-水电费_上期'
,'大仓费用-自营操作费_上期'
,'大仓费用-营业外收支_上期'
,'大仓费用-门店端费用_上期'
,'大仓费用-非生产人员成本_上期'
,'大仓费用-操作-生产_上期'
,'大仓费用-运营_上期'
,'成本合计_上期'
,'月目标销售费率'
,'月目标单份成本'
,'月目标成本'
,'月目标配货箱数'
,'月目标配货额'
,'月目标销售额'])
writer = pd.ExcelWriter('11月FDC成本预测分析.xlsx')
data_total7 = data_total6[data_total6['辅助日期'].astype(str) == str(count_date)[0:6]].T
data_total7.to_excel(writer,sheet_name = '体检表')
ran_data.to_excel(writer,sheet_name = '费用合计明细')
b2b_wbczf_data1.to_excel(writer,sheet_name = '外包线上计费', index = False)
sgd_data_handle.to_excel(writer,sheet_name = '手工单', index = False)
pd.DataFrame(dbzx_data_handle2,columns = ['区域','分类', '业态', '单价', '单位', '操作方式','作业日期','卸货费','仓库名称', '订单类型','实际出库箱数_配货规格','实际入库箱数_采购规格']).to_excel(writer,sheet_name = '装卸费', index = False)
xsg_gs_data_handle.to_excel(writer,sheet_name = '小时工', index = False)
ygcost1.to_excel(writer,sheet_name = '人员成本', index = False)
gdczfee_data1.to_excel(writer,sheet_name = '固定仓租', index = False)
wbllcz_data.to_excel(writer,sheet_name = '流量仓租', index = False)
yunying_fee_data.to_excel(writer,sheet_name = '运营杂费', index = False)
shuidian_data.to_excel(writer,sheet_name = '水电', index = False)
haocai_data_count.to_excel(writer,sheet_name = '运营耗材', index = False)
zhejiu_data1.to_excel(writer,sheet_name = '折旧费用', index = False)
shushe_fee_data.to_excel(writer,sheet_name = '宿舍费用', index = False)
writer.save()
writer.close()
print("end......")
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
