如何用循环计算Excel中2018与2021年值的差值并返回唯一区域数据

需要从Excel文件中提取唯一的Country和REGION_2D数据，计算2018年与2021年对应value的差值并展示。尝试嵌套循环后出现重复值、错误值及计算失效的问题。

示例数据 #

import pandas as pd
df = pd.DataFrame({'Country': ['Austria', 'Austria', 'Ireland', 'Ireland'],
                   'REGION_2D': ['AT10', 'AT10', '4', '4'],
                   'value': [850.01874, 869.550869, 545.4036925, 560.695257],
                   'YEAR': [2018, 2021, 2018, 2021]})

现有代码 #

import pandas as pd

df = pd.read_excel(r"C:\Users\blakecar\PycharmProjects\LFS Data\Mock File Population.xlsx", sheet_name='Sheet1')

df["COUNTRY"] = df["COUNTRY"].astype(pd.StringDtype())
df["REGION_2D"] = df["REGION_2D"].astype(pd.StringDtype())
df["Key"] = df["Key"].astype(pd.StringDtype())
df["Variable"] = df["Variable"].astype(pd.StringDtype())
df["variable_category"] = df["variable_category"].astype(pd.StringDtype())
df["YEAR"] = df["YEAR"].astype(pd.StringDtype())
print(df.dtypes)

#output dataframe to print the excel file to
output = pd.DataFrame()

output["COUNTRY"] = ""
output['REGION_2D'] = " "
output["Variable"] = ''
output['Key'] = ''
output["AC_Change"] = ''

def func1(geog):

    print("------------")
    print(geog)
    print("------------")
    filtered_df = df[df["COUNTRY"].isin([geog])]
    filtered_df = filtered_df[filtered_df["Variable"].isin(["people_15_64_W"])]
    #filtered_df = filtered_df[filtered_df["variable_category"].isin(['None'])]

    #filter through 2018 and summed it up
    filtered_2018 = filtered_df[filtered_df["YEAR"].isin(["2018"])]
    total_2018 = filtered_2018['value'].sum()
    #print(total_2018)
    #filter through 2021 and summed it up
    filtered_2021 = filtered_df[filtered_df["YEAR"].isin(["2021"])]
    total_2021 = filtered_2021['value'].sum()
    #print(total_2021)

    #AC change calculation
    total = total_2018 - total_2021
    #print(total)
    return total

#iterates through unique values- calls function should print to excel
geo = df['REGION_2D'].unique()
for geog in geo:
    funcValue1 = func1(geog)
    output.loc[len(output)] = {'REGION_2D': geog,'AC_Change': funcValue1}
#print(output)
output.to_excel('Mock File2.xlsx', index=False)

期望输出 #

问题分析与解决方案 #

原有代码的问题 #

1. 逻辑不匹配：func1函数用COUNTRY字段筛选传入的geog参数，但循环遍历的是REGION_2D的唯一值，导致筛选逻辑完全错误。
2. 类型转换冗余：将YEAR转为字符串类型，无必要且易引发类型相关问题。
3. 手动循环低效易出错：手动构建outputDataFrame，容易出现索引混乱、数据遗漏等问题，不符合pandas的向量化操作思路。

优化后的代码 #

直接利用pandas的分组与透视功能，无需手动循环，高效且避免错误：

import pandas as pd

# 读取数据，保留YEAR为数值类型
df = pd.read_excel(r"C:\Users\blakecar\PycharmProjects\LFS Data\Mock File Population.xlsx", sheet_name='Sheet1')

# 仅转换需要的字符串类型字段
str_cols = ["COUNTRY", "REGION_2D", "Key", "Variable", "variable_category"]
df[str_cols] = df[str_cols].astype(pd.StringDtype())

# 筛选目标变量
filtered_df = df[df["Variable"] == "people_15_64_W"]

# 按Country和REGION_2D分组，计算2018与2021的value差值
result = filtered_df.pivot_table(
    index=["COUNTRY", "REGION_2D"],
    columns="YEAR",
    values="value",
    aggfunc="sum"
).assign(
    Value=lambda x: x[2018] - x[2021]
).round(4)  # 保留4位小数

# 重置索引并整理列顺序
result = result.reset_index()[["COUNTRY", "REGION_2D", "Value"]]

# 输出到Excel
result.to_excel('Mock File2.xlsx', index=False)

代码说明 #

1. 类型处理：仅转换必要的字符串字段，YEAR保留数值类型，避免筛选时的类型冲突。
2. 数据筛选：先筛选出目标变量people_15_64_W，减少后续计算量。
3. 透视表计算：用pivot_table按COUNTRY和REGION_2D分组，聚合两年的value总和，直接计算差值。
4. 结果整理：重置索引并调整列顺序，输出完全符合期望格式的结果。

内容的提问来源于stack exchange，提问作者rogue1