首先我们导出来的源表是采购订单和调价表,然后根据这两个表就可以做出周报,包含周下单张数,数量,金额,调价及调价占比,各类型的平均单价,各类型的金额,数量,张数占比等信息。
前两张图片分别是源表采购订单和调价表,后三张就是生成的周报,平均单价,各类型占比喽。
#!/usr/bin/env python
# coding: utf-8
# # 周报
# In[26]:
import pandas as pd
df=pd.read_excel(r"D:小工具专案多表拼接周报采购订单.xlsx",sheet_name="sheet1")
df.head(2)
# In[27]:
df.info()
# In[28]:
df["采购日期"]=pd.to_datetime(df["采购日期"])#转换为日期格式
df.info()
# In[29]:
df["week"]=df["采购日期"].apply(lambda x:x.week)
df.head(2)
# In[53]:
df_week=df.groupby("week").aggregate({"物料编码":"count","采购数量":"sum","金额":"sum"})
# In[54]:
df_week["平均单价"]=round(df_week["金额"]/df_week["采购数量"],2)#round(a/b,2)保留两位小数
df_week.head(2)
# In[57]:
df_week=df_week.rename(columns={"物料编码":"图纸张数"})
# In[64]:
df_weekly=df.groupby([df["week"],df["加工件类型"]]).aggregate({"物料编码":"count","采购数量":"sum","金额":"sum"})
# In[67]:
df_weekly["平均单价"]=round(df_weekly["金额"]/df_weekly["采购数量"],2)
df_weekly.head(2)
# In[76]:
#平均单价报表
df_weekly_mean=pd.pivot_table(df_weekly,values="平均单价",columns="加工件类型",index="week",aggfunc="mean",fill_value=0)
df_weekly_mean.head(2)
# In[81]:
df_weekly_totalsum=pd.pivot_table(df_weekly,values="金额",columns="加工件类型",index="week",aggfunc="sum",fill_value=0)
# In[83]:
#对行求和得总额
df_weekly_totalsum["总额"]=df_weekly_totalsum.sum(axis=1)
# In[119]:
df_weekly_totalsum.head()
# In[120]:
#取各类型占比
df_weekly_totalsum1=df_weekly_totalsum.apply(lambda x:round(x/df_weekly_totalsum["总额"],4))
df_weekly_totalsum1.head(2)
# In[121]:
#合并金额与占比
df_weekly_totalsum2=pd.merge(df_weekly_totalsum,df_weekly_totalsum1,on="week")
df_weekly_totalsum2.head(2)
# In[122]:
#图纸张数的报表
df_weekly_totalquantity=pd.pivot_table(df_weekly,values="采购数量",columns="加工件类型",index="week",aggfunc="sum",fill_value=0)
df_weekly_totalquantity.head(2)
# In[123]:
df_weekly_totalquantity["总数量"]=df_weekly_totalquantity.sum(axis=1)#每周总数量
df_weekly_totalquantity1=df_weekly_totalquantity.apply(lambda x:round(x/df_weekly_totalquantity["总数量"],4))#换算成占比
df_weekly_totalquantity1.head(2)#预览前两行
# In[125]:
df_weekly_totalquantity2=pd.merge(df_weekly_totalquantity,df_weekly_totalquantity1,on="week")#合并数量与数量占比
df_weekly_totalquantity2.head(2)
# In[134]:
#张数,张数占比
df_weekly_totalcount=pd.pivot_table(df_weekly,values="物料编码",columns="加工件类型",index="week",aggfunc="sum",fill_value=0)
df_weekly_totalcount["总张数"]=df_weekly_totalcount.sum(axis=1)
df_weekly_totalcount1=df_weekly_totalcount.apply(lambda x:round(x/df_weekly_totalcount["总张数"],4))
df_weekly_totalcount2=pd.merge(df_weekly_totalcount,df_weekly_totalcount1,on="week")
df_weekly_totalcount2.head(2)
# In[176]:
df_price=pd.read_excel(r"D:小工具专案多表拼接周报采购调价表.xlsx",sheet_name="sheet1",index_col=0)
# In[191]:
df_price1=pd.merge(df_price,df,left_on=["调后单价","物料编码"],right_on=["单价","物料编码"],how="inner")
df_price1.to_excel(excel_writer=r"D:小工具专案多表拼接周报dfpriceinner.xlsx")
# In[232]:
df_price1["调前金额"]=df_price1["调前单价"]*df_price1["采购数量"]
df_price1["调价差额"]=df_price1["金额"]-df_price1["调前金额"]
df_price1.head(5)
df_price2=df_price1.groupby("week")["调价差额"].sum()
df_price3=df_price1.groupby("week")["调价差额"].count()
df_price4=pd.merge(df_price2,df_price3,on="week",how="outer")
#df_price4.rename({调差差额_x:调价差额})
df_price4=df_price4.rename(columns={"调价差额_x":"调价差额","调价差额_y":"调价图纸张数"})
df_price5=pd.merge(df_week,df_price4,on="week",how="outer")
df_price5["调价差额占比"]=round(df_price5["调价差额"]/df_price5["金额"],4)
df_price5["调价张数占比"]=round(df_price5["调价图纸张数"]/df_price5["图纸张数"],4)
df_price5.fillna(0)
# In[235]:
writer=pd.ExcelWriter("D:/小工具专案/多表拼接/周报/2020周报.xlsx",engine="xlsxwriter")
df_price5.to_excel(writer,sheet_name="汇总")
df_weekly_mean.to_excel(writer,sheet_name="平均单价")
df_weekly_totalsum2.to_excel(writer,sheet_name="金额及金额占比")
df_weekly_totalquantity2.to_excel(writer,sheet_name="数量及数量占比")
df_weekly_totalcount2.to_excel(writer,sheet_name="张数及张数占比")
writer.save()
print("亲爱的,周报已完成")