ship合并

最新推荐文章于 2024-11-18 12:05:16 发布

小小权

最新推荐文章于 2024-11-18 12:05:16 发布

阅读量261

点赞数

文章标签： python excel

本文链接：https://blog.csdn.net/u012366798/article/details/122226850

版权

本文档展示了如何从Excel数据中提取产品需求、产出计划，并通过时间序列分析计算月度需求、计划与实际发货的差距。数据整理后生成了按产品ID的汇总报告，包括累计需求、计划和发货量，适用于监控项目进度和资源分配。

摘要由CSDN通过智能技术生成

import pandas as pd
from datetime import datetime


def part_no(product):
    return product.replace('需求', '').replace('产出计划', '')


def product(product):
    if '需求' in product:
        return '需求'
    elif '产出' in product:
        return '产出'
    else:
        return ''


read_file1 = '原始1.xlsx'
read_file2 = '原始2.xlsx'
# 输出的文件名
out_file = '输出结果.csv'

# excel中第一列
file_name = ''

df1 = pd.read_excel(read_file1)
df2 = pd.read_excel(read_file2)

df1.columns = df1.columns.map(lambda x: str(x))
df2.columns = df2.columns.map(lambda x: str(x))

df1['Product'] = '实际'
df2['PartNo'] = df2['Product'].apply(part_no)
df2['Product'] = df2['Product'].apply(product)
# 获取当前时间
now = int(datetime.now().strftime('%Y%m'))

df = df1.append(df2)
# print(df.columns)
df['PartNo'] = df['PartNo'].map(lambda x: str(x).upper())
df = df.fillna(0)
cols = df.columns
cols_sel = []
for col in cols:
    try:
        int(col)
        cols_sel.append(col)
    except Exception:
        pass
# print(cols_sel)

mon_dict = {}
for mon in cols_sel:
    left = str(mon)[:4]
    if left not in mon_dict:
        mon_dict[left] = [mon]
    else:
        mon_dict[left].append(mon)

df_group = df.groupby('PratNo')
out_data = []
# print(mon_dict)
for key in df_group.groups:
    data = df_group.get_group(key)
    # print(key)
    # print(data)
    for mon_left in mon_dict:
        print(mon_left)
        # 统计结果
        cum_req = 0
        cum_pro = 0
        cum_ship = 0
        month = mon_dict[mon_left]
        # 按时间分别取三行对应的数据
        for mon in month:
            # print(data)
            month_val = data[mon].values
            # print(mon, month_val)
            try:
                int(mon)
            except Exception as e:
                print(e)
                continue
            try:
                actual_shipping = int(month_val[0])
            except Exception:
                actual_shipping = 0
            try:
                requirement = int(month_val[1])
            except Exception:
                requirement = 0
            try:
                shipping_plan = int(month_val[2])
            except Exception:
                shipping_plan = 0
            line = [file_name, '', mon, key, requirement, shipping_plan, actual_shipping,
                    actual_shipping - shipping_plan]

            cum_req += requirement
            cum_pro += shipping_plan
            cum_ship += actual_shipping
            line.extend([cum_req, cum_pro, cum_ship, cum_ship - cum_pro])
            print(line)
            out_data.append(line)

head = ['file_name', 'creat_time', 'month', 'product_id', 'requirement', 'shipping_plan',
        'actual_shipping', 'gap', 'cumulative_requirement', 'cumulative_shipping_plan', 'cumulative_shipping',
        'cumulative_gap']
out_df = pd.DataFrame(out_data, columns=head)
out_df.to_csv(out_file, index=False)
print('over')