利用Pandas高效处理Excel数据：动态列赋值与向量化技巧

import pandas as pd
import random
import string

def random_alphanumeric(length, hyphen_interval=4):
    characters = string.ascii_letters + string.digits
    random_value = ''.join(random.choice(characters) for _ in range(length))
    return '-'.join(random_value[i:i + hyphen_interval] for i in range(0, len(random_value), hyphen_interval))

def process_excel(xl_input_file, xl_output_file):
    df = pd.read_excel(xl_input_file)
    df['Value'] = pd.to_numeric(df['Value'], errors='coerce')
    updated_rows = []
    for index, row in df.iterrows():
        updated_rows.append(pd.DataFrame([row], columns=df.columns))
        if row['Value'] != 0:
            new_row = row.copy()
            new_row['Value'] = -row['Value']
            updated_rows.append(pd.DataFrame([new_row], columns=df.columns))
            # 这里的修改未能生效
            new_row['ID'] = random_alphanumeric(16, hyphen_interval=4)
            new_row['gla'] = '2100-abc'
    updated_df = pd.concat(updated_rows, ignore_index=True)
    updated_df.to_excel(xl_output_file, index=False)

import pandas as pd
import random
import string

# 随机字母数字串生成函数
def random_alphanumeric(length, hyphen_interval=4):
    characters = string.ascii_letters + string.digits
    random_value = "".join(random.choice(characters) for _ in range(length))
    return "-".join(
        random_value[i : i + hyphen_interval]
        for i in range(0, len(random_value), hyphen_interval)
    )

def process_excel_optimized(xl_input_file, xl_output_file):
    df = pd.read_excel(xl_input_file)

    # 1. 值反转：直接对整列进行操作
    # pd.to_numeric用于确保Value列为数值类型，errors='coerce'会将非数值转换为NaN
    df['Value'] = -pd.to_numeric(df['Value'], errors='coerce')

    # 2. 动态生成随机ID：使用apply方法，axis=1表示按行应用函数
    # 对于每一行，调用random_alphanumeric函数生成一个ID
    df['ID'] = df.apply(lambda x: random_alphanumeric(16, hyphen_interval=4), axis=1)

    # 3. 固定值赋值：直接对整列赋值
    df['gla'] = "2100-abc"

    # 将结果保存到新的Excel文件
    df.to_excel(xl_output_file, index=False)

# 示例用法
xl_input_file = 'input.xlsx' # 确保存在input.xlsx文件
xl_output_file = 'updated_file.xlsx'
process_excel_optimized(xl_input_file, xl_output_file)

# 假设input.xlsx内容如下：
# Value
# 10
# 0
# 22
# -5
#
# 优化后代码的输出（updated_file.xlsx）示例：
# Value ID                   gla
# -10   Q1T2-0CxA-er3b-Zi5U  2100-abc
# 0     I7LL-bj56-uInj-aRpl  2100-abc
# -22   3g6V-GYPM-VwAG-qKwd  2100-abc
# 5     Vbn1-EINt-A8c8-k89w  2100-abc