IFRA数据处理

编程开发, IFRA数据处理

今天可以说是来公司后最有成就感的一天, 将IFRA这个曾以为无法自动化处理的工作靠自己基本实现, 也是我从去年开始编程提高效率后的一个集大成之作和阶段性里程碑, 先写一下今天的工作内容:

VBA的失败

昨天用VBA完成了筛选计算配方中83种过敏源的宏, 今天还想沿用这个思路尝试处理IFRA数据计算问题, 我的大致思路为:

用之前类似的代码把配方中原料编号, 原料名称和含量三列数据复制到新建的工作表 IFRA 中;
把之前整理好的IFRA数据也复制到刚刚建立的工作表 IFRA 中;
用2024-01-24的思路对配方中原料进行筛选, 考虑稀释料以及同一个IFRA项目对应多种原料编号的情况, 并把含量与对应的IFRA数据写在同一行;
在IFRA数据下方进行除法计算以及大小比较, 得出最后的一行数据后再用以前的IFRA处理宏进行转置, 编程可以直接往报告里粘贴的形式.

但没想到却夭折在一个十分简单的步骤上: IFRA的260+行数据在复制粘贴时始终提示 下标越界, 在ChatGPT提示下反复修改还是无法解决; 另外即使直接引用数据进行计算还是会提示除数为零等问题. 在此过程中产生了一种对VBA代码的厌恶: 冗长, 对象管理混乱, 数据类型模糊. 上午就这样一事无成的过去, 中午吃饭时突然意识到VBA的精髓是宏录制, 给了他最后一次机会, 结果仍是失败.

中午因为大脑兴奋没有睡着, 眯着眼的时候突然想到能不能用Python尝试一下呢? 因为IFRA数据处理涉及多个表格, 我问了ChatGPT: Python可以用pandas建立多个表格对象进行计算吗, 得到肯定的回答后, 我感觉有戏.

转战Python

用Python之后开发过程非常流畅, 问了几个基础问题确认代码后, 从下午四点到晚上七点半, 三个半小时终于把问题解决, 用Python文件可以直接拿到我想要的最后一行数据, 我再复制粘贴一下用之前的IFRA处理宏即可拿到可以直接粘贴的形式.

开发过程中学到或者值得留意的一些点:

使用 pandas 的 iloc 或 loc 方法来选择特定的列和行, 把Excel文件中的某些部分复制出来创建 DataFrame:

import pandas as pd

# 读取 Excel 文件
excel_file_path = '路径/到/你的/Excel文件.xlsx'
df = pd.read_excel(excel_file_path)

# 选择第三列和第五列，从第三行开始到没有数据截止
selected_data = df.iloc[2:, [2, 4]]

根据特定的原料编号来筛选配方中的原料, 如果该物质存在, 把含量填写到用来存放IFRA数据的表格对象 df2 中的Conc 列对应位置上:

import pandas as pd

# 读取 'FA16202D-1 姜面包.xlsx'
excel_file_path1 = 'FA16202D-1 姜面包.xlsx'
df1 = pd.read_excel(excel_file_path1)

# 选择第2列，第3列，第6列，从第3行开始
formula = df1.iloc[2:, [1, 2, 5]]

# 读取 'IFRA_DB.xlsx'
excel_file_path2 = 'IFRA_DB.xlsx'
df2 = pd.read_excel(excel_file_path2)

# 目标值
target_value = 99038

# 在 'formula' 表中查找目标值对应的行
target_row = formula[formula.iloc[:, 0] == target_value]

# 如果找到了匹配的行
if not target_row.empty:
    # 获取目标值对应的 'formula' 表中的第三列的值
    conc_value = target_row.iloc[0, 2]

    # 将 'conc' 列的第一行更新为 'formula' 表中的值
    df2.loc[0, 'Conc'] = conc_value

如果同一个IFRA条目对应多种原料, 需要将对应的含量相加后填入到 Conc 列相应位置, 这个时候会定义和的初始值为0, 最后做一个判断如果为0就不填写:

import pandas as pd

# 读取 'FA16202D-1 姜面包.xlsx'
excel_file_path1 = 'FA16202D-1 姜面包.xlsx'
df1 = pd.read_excel(excel_file_path1)

# 选择第2列，第3列，第6列，从第3行开始
formula = df1.iloc[2:, [1, 2, 5]]

# 读取 'IFRA_DB.xlsx'
excel_file_path2 = 'IFRA_DB.xlsx'
df2 = pd.read_excel(excel_file_path2)

# 目标值
target_values = [97007, 97056]

# 初始化总和
total_sum = 0

# 遍历目标值列表
for target_value in target_values:
    # 在 'formula' 表中查找目标值对应的行
    target_row = formula[formula.iloc[:, 0] == target_value]

    # 如果找到了匹配的行
    if not target_row.empty:
        # 获取目标值对应的 'formula' 表中的第三列的值
        conc_value = target_row.iloc[0, 2]

        # 累加到总和
        total_sum += conc_value

# 如果有匹配的值，填充到 'df2' 的 'conc' 列的第30行
if total_sum != 0:
    df2.loc[29, 'conc'] = total_sum

如果同一个IFRA条目对应多种原料, 但其中含有稀释料或者其他情况, 需要某种原料含量乘以一定系数, 例如0.01, 然后再相加后填入到 Conc 列相应位置, 这里用到了 lambda, 之前没有用过, 但这样写代码感觉非常易于后期维护修改:

# 31. 王朝酮
target_values = [(98015, lambda x: x),
                (98041, lambda x: x),
                (98070, lambda x: x * 0.1),  # 需要乘以系数的原料
                (98079, lambda x: x)]
total_sum = 0
for target_value in target_values:
    target_row = formula[formula.iloc[:, 0] == target_value[0]]  # 使用 target_value[0]

    if not target_row.empty:
        conc_value = target_row.iloc[0, 2]
        total_sum += target_value[1](conc_value)  # 使用 target_value[1] 执行操作

if total_sum != 0:
    df2.loc[30, 'Conc'] = total_sum

筛选完之后我想把df2中, Conc 列有值的行再筛选出来建立一张新的表 df_filtered:

# 假设你的DataFrame为df2，修改列名和数据类型以适应你的实际情况
df2['Conc'] = pd.to_numeric(df2['Conc'], errors='coerce')  # 将 'Conc' 列转为数字类型
df_filtered = df2[df2['Conc'].notnull()]  # 筛选出 'Conc' 列有结果的行

筛选后开始计算, Conc 列之前的每一列都要去除以对应的值, 这里学到利用了pandas的 广播机制, 然后求最小值放在最下面一行, 并把最终结果放到了一张新表 df_result:

import pandas as pd

# 选择需要除以 'Conc' 值的列
columns_to_divide = ['Column1', 'Column2', 'Column3', 'Column4', 'Column5', 'Column6', 'Column7', 'Column8', 'Column9', 'Column10']

# 使用广播机制进行除法操作
df_result = pd.DataFrame()  # 创建新的 DataFrame 存放结果
df_result[columns_to_divide] = df_filtered[columns_to_divide].div(df_filtered['Conc'] * 0.01, axis=0)

# 求每一列的最小值
min_values = df_result.min()

# 将最小值添加到新的行
df_result = df_result._append(min_values, ignore_index=True)

计算结果我想导出一些文件, 导出文件命名我想直接利用处理的配方名字再加其他, 可以先把配方名字拿出来作为字段储存备用:

import pandas as pd
import os

# 读取 'FA23656 FA22512-10 草莓.xlsx'
excel_file_path1 = 'FA23656 FA22512-10 草莓.xlsx'
df1 = pd.read_excel(excel_file_path1)

# 提取文件名的一部分作为字符串
file_name_part = os.path.basename(excel_file_path1).split('.')[0]

# 导出文件，使用提取的文件名的一部分
df1.to_excel(f'{file_name_part}_df_result.xlsx', index=False)

感想

IFRA数据处理自动化在心中已经萦绕许久, 21年刚接手工作时还是用随机数应付客户, 22年上半年开始试着做出一些计算, 但因为涉及原料太多, 只能挑铃兰醛, 龙涎酮等代表原料进行计算, 并且始终认为Excel自动化计算难于登天, 直到今天完成任务, 发现好像也没那么难, 当然成功离不开之前的铺垫:

基础数据的准备: 做IFRA报告的经验让自己将原始数据类型统一, 数据结构整齐, 易用. 禁用为0, 不限制为100, 个别原料的特殊情况也进行了甄别确认.筛选依据也可靠, 数据库的学习以及使用使得用CAS号确认原料属于哪个IFRA条目非常清晰
编程水平提高: Python已经学过很多次了, 但没有独立实践经验, 也没做成过像样的项目, 可凭借去年11月pandas库的学习留下的一点印象才能让我在今天及时调转方向, 虽然pandas当时入门的内容大部分都忘记, 但无疑这个过程非常有意义;
从去年12月开始我在Java的学习上投入大量时间精力, 这次没有使用Java来编写, 可是学习Java的过程让我对于各个语言擅长的领域有了大致认识, 当时用Java处理Excel的失败也让我今天没有选择Java作为开发语言, 并且学习Java让我的代码阅读能力也有了提高, 这种能力是可以部分迁移应用到其他语言上的.
至于VBA, Excel中数据的一些简单筛选计算还是非常给力, 特别是结合宏的使用, 可是处理这种多个来源的表格数据以及稍微复杂一点的计算凭借自己这点知识很难去解决开发过程中的报错, 并且代码不易读, 时间一长重新打开文件都会怀疑自己当初是怎么写出这些代码的.
ChatGPT: 怎么赞美也不为过, 完全就是编程时的贴心小顾问, 中间有些问题和他仿佛真的在讨论, 我指出他的错误, 他回应我的问题, 硬是让我一个pandas处理经验为零的人完成了今天的任务, 这就是天时吧, 我22年就学习Python, 可是没有他的帮助, 只能是又一次的半途而废

编程开发, IFRA数据处理

VBA的失败

转战Python

感想

添加新评论

最新文章

最近回复

分类

其它