供应链-物料齐套性算法模型(二)-python数据处理

一.数据处理

在上个篇幅中，介绍了这个齐套性算法模型，并用excel作演练。 现在，用python来清洗数据。

1 导入数据表，作简单处理。

#文件路径
path = 'C:/Users/Desktop/物料齐套性案例/数据源/'
path_jg = 'C:/Users/Desktop/物料齐套性案例/结果/'

da_paichan = pd.read_excel(path+"基础数据.xlsx", sheet_name="排产计划表")
da_BOM = pd.read_excel(path+"基础数据.xlsx", sheet_name="BOM拆套表")
da_kucun = pd.read_excel(path+"基础数据.xlsx", sheet_name="库存表")
da_zaitu = pd.read_excel(path+"基础数据.xlsx", sheet_name="在途库存表")

## 获取今天的日期
#today = pd.Timestamp(datetime.now().date())
today = pd.to_datetime("2024-11-27")

## 简单处理排产表
da_paichan["日期"] = da_paichan["日期"].dt.normalize()
da_paichan["工厂+产品编码+BOM版本"] = da_paichan["工厂"]+da_paichan["产品编码"]+da_paichan["最终BOM版本"]
da_paichan["工厂+产品编码"] = da_paichan["工厂"]+da_paichan["产品编码"]
da_paichan = da_paichan[da_paichan["时间标签"]!="过去"]
da_paichan["成品→待包品前置期"] = 7
da_paichan['待包品需求日期'] = (da_paichan["日期"] - pd.Timedelta(days=7)).dt.normalize()

2 从成品拆解到待包品，匹配bom表

da_daibaopin = pd.merge(da_paichan, da_BOM.loc[:,['父项SKUBOM编码','子项sku编码','子项SKUBOM编码','bom量','拆解标签']].drop_duplicates(), left_on='最终BOM版本', right_on='父项SKUBOM编码', how='left')
da_daibaopin['*bom(待包品)'] = da_daibaopin['计划量']*da_daibaopin['bom量']
da_daibaopin["工厂+产品编码+BOM版本+子项sku"] = da_daibaopin["工厂+产品编码+BOM版本"]+da_daibaopin["子项sku编码"]

# /  ****************** 按照工厂、产品编码、日期和BOM版本进行排序，加序列值 ********************/
pc_sorted = da_daibaopin.sort_values(by=['工厂', '产品编码', '日期'])
# 使用groupby和cumcount生成每个组合下的顺序值
pc_sorted['rn_a'] = pc_sorted.groupby(['工厂', '产品编码', '日期']).cumcount() + 1 #工厂+产品编码，
pc_sorted_a = pc_sorted.sort_values(by=['工厂', '产品编码'])
pc_sorted_a['rn_b'] = pc_sorted_a.groupby(['工厂', '产品编码']).cumcount() + 1
pc_sorted_b = pc_sorted_a.sort_values(by=['工厂', '产品编码','最终BOM版本','子项sku编码'])
pc_sorted_b['rn_c'] = pc_sorted_a.groupby(['工厂', '产品编码','最终BOM版本','子项sku编码']).cumcount() + 1

3 匹配最新的库存、在途库存

这个齐套性推演模型比较关键的一点是，每个工厂+物料编码+BOM版本，日期=今天才有期初库存。排产计划表未来几十天，每天的情况，需要根据第一天的库存，推演每日的"进销存"。按照工厂+物料编码+BOM版本的维度，看其在未来几十天的【理论期初库存】。

未来日期的【理论期初库存】需要一天天推演下去。 下一天的理论期初库存=上一天【理论期初库存】+【在途库存】-上一天【计划量】。

上一天【计划量】：按照【产品+物料编码】的维度，对上一天的计划量求和。

【在途库存】：按照【产品+物料编码+日期】的维度，去作匹配。

# /  ****************** 匹配期初库存、在途库存 ********************/
### 过滤出日期为今天的行
today_paichan = pc_sorted_b[(pc_sorted_b["时间标签"]=="当前") & (pc_sorted_b["rn_b"]==1) ]
today_paichan = today_paichan[["工厂","子项sku编码","日期","产品编码","rn_b"]]
today_kucun = pd.merge(today_paichan, da_kucun[(da_kucun["类型"]=="待包品")].loc[:,['工厂归属','物料编码','当前库存数量']], left_on=['工厂','子项sku编码'], right_on=['工厂归属','物料编码'], how='left')
today_kucun = today_kucun[["工厂","子项sku编码","当前库存数量","产品编码","日期","rn_b"]] #工厂,物料编码匹配，日期匹配，rn_b=1

#today_kucun.to_excel(path_jg+"结果1.xlsx", index=False, sheet_name='MySheet')
##匹配最新库存
pc_sorted_b = pd.merge(pc_sorted_b, today_kucun, left_on=["工厂","子项sku编码","日期","产品编码","rn_b"], right_on=["工厂","子项sku编码","日期","产品编码","rn_b"], how='left')
pc_sorted_b = pc_sorted_b.rename(columns={'当前库存数量': '期初库存'})
pc_sorted_b = pc_sorted_b.loc[:,['日期', '周', '工厂', '产品编码', '最终BOM版本', '计划量', '时间标签', '工厂+产品编码+BOM版本',
       '工厂+产品编码', '成品→待包品前置期', '待包品需求日期',  '子项sku编码',
       '子项SKUBOM编码', 'bom量', '拆解标签', '*bom(待包品)', '工厂+产品编码+BOM版本+子项sku',
       'rn_a', 'rn_b', 'rn_c', '期初库存']]
pc_sorted_b["期初库存"] = pc_sorted_b["期初库存"].fillna(0)
pc_sorted_b.to_excel(path_jg+"结果1.xlsx", index=False, sheet_name='MySheet')

### # 匹配在途库存：工厂+物料编码匹配，日期匹配，BOM版本排序=1才有
pipei_zaitu = pc_sorted_a[pc_sorted_a["rn_a"]==1 ]
pipei_zaitu = pipei_zaitu[["工厂","子项sku编码","日期","产品编码","rn_a"]]
zaitu_kucun = pd.merge(pipei_zaitu, da_zaitu[(da_zaitu["标签"]=="待包品")], left_on=["工厂","子项sku编码","日期"], right_on=["工厂归属","物料编码","入库日期"], how='left')
zaitu_kucun = zaitu_kucun[["日期","子项sku编码","工厂","产品编码","rn_a","未入库数量"]] #工厂,物料编码匹配，日期匹配，rn_a=1
pc_sorted_b = pd.merge(pc_sorted_b, zaitu_kucun, left_on=["工厂","子项sku编码","日期","产品编码","rn_a"], right_on=["工厂","子项sku编码","日期","产品编码","rn_a"], how='left')
pc_sorted_b["未入库数量"] = pc_sorted_b["未入库数量"].fillna(0)

pc_sorted_b["期初+在途库存"] = pc_sorted_b["期初库存"] + pc_sorted_b["未入库数量"] 
pc_sorted_b["工厂+产品编码+子项sku编码"] = pc_sorted_b["工厂"] + pc_sorted_b["产品编码"]  + pc_sorted_b["子项sku编码"] 

4 从成品→待包品，看bom用量与每日需求

# （2）循环
# 遍历【工厂+产品编码+BOM版本+子项sku】，
#     {
#      对每个【工厂+产品编码+子项sku编码】
#          如果【排序rn_c】=1，【动态库存】=该行的【期初+在途库存】；
#          如果【排序rn_c】>1，【动态库存】=上一行的【期初+在途库存】-上一行的【计划量】+本行的【期初+在途库存】
#      }

def calculate_dynamic_inventory(uni_product, da_frame):
    # 创建一个空的字典来承接计算结果
    result_df_dict = {}
    
    # 遍历工厂+产品编码
    for i in range(len(uni_product)):
        # 选取其中一个工厂+产品编码
        do_df = da_frame[da_frame["工厂+产品编码+BOM版本+子项sku"] == uni_product.iloc[i]]  # 使用 .iloc[i] 访问元素
        do_df.sort_values(by=['rn_c'], inplace=True)  # 对rn_b进行排序
        do_df.reset_index(drop=True, inplace=True)  # 重置索引
        do_df["动态库存"] = np.nan  # 初始化动态库存列
        
        # 遍历行进行计算
        for j in range(len(do_df)):  # j=0,1,2,3....68
            if do_df.loc[j, "rn_c"] == 1:  # rn_b在数据表的第10列
                do_df.loc[j, "动态库存"] = do_df.loc[j, "期初+在途库存"]  # 使用 .loc 进行赋值
                
            else:
                do_df.loc[j, "动态库存"] = do_df.loc[j-1, "动态库存"] - do_df.loc[j-1, "*bom(待包品)"] + do_df.loc[j, "期初+在途库存"]
        
        # 将每个工厂+产品编码对应的DataFrame存储在字典中
        result_df_dict[f'do_df{i}'] = do_df  # 使用字典存储每个DataFrame
        
    # 返回包含所有DataFrame的字典,将字典里面的dataframe上下合并
    result_collector = pd.concat(list(result_df_dict.values()), axis=0)
    return result_collector

# 函数调用
uniq_product = pc_sorted_b["工厂+产品编码+BOM版本+子项sku"].drop_duplicates()
pc_sorted_c = calculate_dynamic_inventory(uniq_product, pc_sorted_b)
pc_sorted_c["动态库存"] = pc_sorted_c["动态库存"].map(lambda x:x if x > 0 else 0)

pc_sorted_c.to_excel(path_jg+"结果3.xlsx", index=False, sheet_name='MySheet')
#计算待包品新需求量
pc_sorted_c['每日库存可满足量'] = np.where(pc_sorted_c['动态库存'] > pc_sorted_c['*bom(待包品)'], 0, pc_sorted_c['*bom(待包品)'] - pc_sorted_c['动态库存'])
pc_sorted_c['新需求量(待包品)'] = np.where(pc_sorted_c['*bom(待包品)'] < pc_sorted_c['动态库存'],0,pc_sorted_c['*bom(待包品)'] - pc_sorted_c['动态库存'])

5 从待包品→原料拆解需求

这个流程和上个步骤类似。先匹配待包品的每日期初库存、在途库存，然后推演每日理论期初库存，及待包品新需求。

pc_sorted_c["成品→待包品前置期"] = 9
pc_sorted_c['待包品需求日期'] = (pc_sorted_c["待包品需求日期"] - pd.Timedelta(days=9)).dt.normalize()

## 匹配bom表，到原料层级

pc_sorted_d = pd.merge(pc_sorted_c, da_BOM.loc[:,["父项sku编码",'父项SKUBOM编码','子项sku编码','子项SKUBOM编码','原料sku编码','原料bom编码','原料bom量','拆解标签']].drop_duplicates(),
                       left_on=["产品编码","最终BOM版本","子项sku编码","子项SKUBOM编码"],right_on=["父项sku编码","父项SKUBOM编码","子项sku编码","子项SKUBOM编码"], how='left')

pc_sorted_d = pc_sorted_d.loc[:,['日期', '周', '工厂', '产品编码', '最终BOM版本', '计划量', '时间标签', '工厂+产品编码+BOM版本',
       '工厂+产品编码', '成品→待包品前置期', '待包品需求日期', '子项sku编码', '子项SKUBOM编码', 'bom量',
       '拆解标签_x', '*bom(待包品)', '工厂+产品编码+BOM版本+子项sku', 'rn_a', 'rn_b', 'rn_c',
       '期初库存', '未入库数量', '期初+在途库存', '工厂+产品编码+子项sku编码', '动态库存', '每日库存可满足量',
       '新需求量(待包品)','原料sku编码', '原料bom编码', '原料bom量','拆解标签_y']]
pc_sorted_d['*bom(原料)'] = pc_sorted_d['新需求量(待包品)']*pc_sorted_d['原料bom量']
pc_sorted_d["工厂+产品编码+BOM版本+子项sku+原料sku编码"] = pc_sorted_d["工厂+产品编码+BOM版本+子项sku"]+pc_sorted_d["原料sku编码"]

#加排序
pc_sorted_d = pc_sorted_d.sort_values(by=['工厂', '产品编码','最终BOM版本','子项sku编码','原料sku编码'])
pc_sorted_d['rn_d'] = pc_sorted_d.groupby(['工厂', '产品编码','最终BOM版本','子项sku编码','原料sku编码']).cumcount() + 1

pc_sorted_d.to_excel(path_jg+"结果5.xlsx", index=False, sheet_name='MySheet')

# /  ****************** 匹配期初库存、在途库存 ********************/
#期初库存
today_yuanliao = pc_sorted_d[(pc_sorted_d["时间标签"]=="当前") & (pc_sorted_d["rn_b"]==1) ]
today_yuanliao = today_yuanliao.loc[:,['日期','工厂+产品编码+BOM版本+子项sku+原料sku编码','原料sku编码','工厂','rn_d']].drop_duplicates()

today_yl_kucun = pd.merge(today_yuanliao, da_kucun[(da_kucun["类型"]=="原料")].loc[:,['工厂归属','物料编码','当前库存数量']],
                          left_on=['工厂','原料sku编码'], right_on=['工厂归属','物料编码'], how='left')
today_yl_kucun = today_yl_kucun[['日期','工厂+产品编码+BOM版本+子项sku+原料sku编码','原料sku编码','工厂','rn_d','当前库存数量']]
today_yl_kucun = today_yl_kucun.rename(columns={'当前库存数量': '期初库存(原料)'})

pc_sorted_d = pd.merge(pc_sorted_d, today_yl_kucun, left_on=["工厂+产品编码+BOM版本+子项sku+原料sku编码","rn_d"],
                       right_on=["工厂+产品编码+BOM版本+子项sku+原料sku编码","rn_d"], how='left')

pc_sorted_d = pc_sorted_d.loc[:,['日期_x', '周', '工厂_x', '产品编码', '最终BOM版本', '计划量', '时间标签', '工厂+产品编码+BOM版本',
       '工厂+产品编码', '成品→待包品前置期', '待包品需求日期', '子项sku编码', '子项SKUBOM编码', 'bom量',
       '拆解标签_x', '*bom(待包品)', '工厂+产品编码+BOM版本+子项sku', 'rn_a', 'rn_b', 'rn_c',
       '期初库存', '未入库数量', '期初+在途库存', '工厂+产品编码+子项sku编码', '动态库存', '每日库存可满足量',
       '新需求量(待包品)', '原料sku编码_x', '原料bom编码', '原料bom量', '拆解标签_y', '*bom(原料)',
       '工厂+产品编码+BOM版本+子项sku+原料sku编码', 'rn_d', 
       '期初库存(原料)']]
pc_sorted_d = pc_sorted_d.rename(columns={'日期_x': '日期','工厂_x':'工厂','原料sku编码_x':'原料sku编码'})
pc_sorted_d["期初库存(原料)"] = pc_sorted_d["期初库存(原料)"].fillna(0)

pc_sorted_d.to_excel(path_jg+"结果6.xlsx", index=False, sheet_name='MySheet')

# ### # 匹配在途库存：工厂+物料编码+子项sku编码匹配，日期匹配，每个工厂+物料编码+子项sku编码+日期只有一个库存
#加排序
pc_sorted_d = pc_sorted_d.sort_values(by=['工厂+产品编码+子项sku编码'])
pc_sorted_d['rn_e'] = pc_sorted_d.groupby(['工厂+产品编码+子项sku编码']).cumcount() + 1

pc_sorted_d.to_excel(path_jg+"结果6.xlsx", index=False, sheet_name='MySheet')

yuanliao_zaitu = pc_sorted_d[pc_sorted_d["rn_e"]==1 ]
yuanliao_zaitu = yuanliao_zaitu[["工厂","子项sku编码","日期","产品编码",'原料sku编码',"rn_e"]].drop_duplicates()
yuanliao_zt_kucun = pd.merge(yuanliao_zaitu, da_zaitu[(da_zaitu["标签"]=="原料")], left_on=["工厂","原料sku编码","日期"],
                             right_on=["工厂归属","物料编码","入库日期"], how='left')
yuanliao_zt_kucun = yuanliao_zt_kucun.loc[:,["工厂","子项sku编码","日期","产品编码",'原料sku编码',"rn_e",'未入库数量']]
yuanliao_zt_kucun = yuanliao_zt_kucun.rename(columns={'未入库数量': '原料未入库'})
yuanliao_zt_kucun["原料未入库"] = yuanliao_zt_kucun["原料未入库"].fillna(0)


pc_sorted_d = pd.merge(pc_sorted_d, yuanliao_zt_kucun, left_on=["工厂","子项sku编码","日期","产品编码",'原料sku编码',"rn_e"], 
                       right_on=["工厂","子项sku编码","日期","产品编码",'原料sku编码',"rn_e"], how='left')
pc_sorted_d["原料未入库"] = pc_sorted_d["原料未入库"].fillna(0)
pc_sorted_d["期初+在途库存2"] = pc_sorted_d["期初库存(原料)"] + pc_sorted_d["原料未入库"] 

pc_sorted_d.to_excel(path_jg+"结果7.xlsx", index=False, sheet_name='MySheet')

# （2）循环

def calculate_dynamic_inventory(uni_product, da_frame):
    # 创建一个空的字典来承接计算结果
    result_df_dict = {}
    
    # 遍历工厂+产品编码
    for i in range(len(uni_product)):
        # 选取其中一个工厂+产品编码
        do_df = da_frame[da_frame["工厂+产品编码+BOM版本+子项sku+原料sku编码"] == uni_product.iloc[i]]  # 使用 .iloc[i] 访问元素
        do_df.sort_values(by=['rn_c'], inplace=True)  # 对rn_b进行排序
        do_df.reset_index(drop=True, inplace=True)  # 重置索引
        do_df["动态库存2"] = np.nan  # 初始化动态库存列
        
        # 遍历行进行计算
        for j in range(len(do_df)):  # j=0,1,2,3....68
            if do_df.loc[j, "rn_c"] == 1:  # rn_b在数据表的第10列
                do_df.loc[j, "动态库存2"] = do_df.loc[j, "期初+在途库存2"]  # 使用 .loc 进行赋值
                
            else:
                do_df.loc[j, "动态库存2"] = do_df.loc[j-1, "动态库存2"] - do_df.loc[j-1, "*bom(原料)"] + do_df.loc[j, "期初+在途库存2"]
            
        # 将每个工厂+产品编码对应的DataFrame存储在字典中
        result_df_dict[f'do_df{i}'] = do_df  # 使用字典存储每个DataFrame
        
    # 返回包含所有DataFrame的字典,将字典里面的dataframe上下合并
    result_collector = pd.concat(list(result_df_dict.values()), axis=0)
    return result_collector

# 函数调用
uniq_product = pc_sorted_d["工厂+产品编码+BOM版本+子项sku+原料sku编码"].drop_duplicates()
pc_sorted_e = calculate_dynamic_inventory(uniq_product, pc_sorted_d)
pc_sorted_e["动态库存2"] = pc_sorted_e["动态库存2"].map(lambda x:x if x > 0 else 0)
pc_sorted_e['新需求量(原料)'] = np.where(pc_sorted_e['*bom(原料)'] < pc_sorted_e['动态库存2'],0,pc_sorted_e['*bom(原料)'] - pc_sorted_e['动态库存2'])

二.可视化

最后生成一个大宽表，反向推演过程可以在BI里面实现直观点。