如何在DataFrame中为分组计算最近值并分配剩余值至另一分组

现有两个DataFrame：

df1

Store  Category  split_table    tot_tables
11      FRUIT       7.0             12
11      VEG         5.0             12  
13      FRUIT       5.0             11
13      VEG         6.0             11

df2

Site    sum_list
11      [1,2,3,4,5,6.5,8,9.5,11,12]
13      [1,2,3,4,5,6.5,7.5,9,10,11]

需要为df1生成新列df1['final_table']，规则如下：

• 对df1中每个Store的FRUIT分类，final_table取对应Store的df2['sum_list']中的最近值；
• 对df1中每个Store的VEG分类，final_table通过df1['tot_tables'] - 同Store FRUIT的final_table值计算，确保每个Store的final_table总和等于tot_tables。

预期输出：
df1

Store  Category  split_table    tot_tables      final_table
11      FRUIT       7.0             12              6.5
11      VEG         5.0             12              5.5 
13      FRUIT       5.0             11              5.0
13      VEG         6.0             11              6.0

使用Pandas可实现需求，具体步骤如下：

1. 重命名df2的列名，方便与df1按Store关联合并；
2. 合并两个DataFrame，保留所有df1的行数据；
3. 定义函数，用于从列表中找到与目标值最接近的元素；
4. 针对FRUIT分类，计算对应的final_table值；
5. 按Store分组提取FRUIT的结果，计算VEG分类的final_table值。

完整代码：

import pandas as pd
import numpy as np

# 构造示例数据
df1 = pd.DataFrame({
    'Store': [11, 11, 13, 13],
    'Category': ['FRUIT', 'VEG', 'FRUIT', 'VEG'],
    'split_table': [7.0, 5.0, 5.0, 6.0],
    'tot_tables': [12, 12, 11, 11]
})

df2 = pd.DataFrame({
    'Site': [11, 13],
    'sum_list': [[1,2,3,4,5,6.5,8,9.5,11,12], [1,2,3,4,5,6.5,7.5,9,10,11]]
})

# 重命名df2的列，匹配df1的Store字段
df2 = df2.rename(columns={'Site': 'Store'})

# 合并两个DataFrame
merged_df = df1.merge(df2, on='Store', how='left')

# 定义函数：找出列表中与目标值最接近的元素
def find_nearest(target, lst):
    lst_arr = np.array(lst)
    closest_idx = np.abs(lst_arr - target).argmin()
    return lst_arr[closest_idx]

# 计算FRUIT分类的final_table
fruit_filter = merged_df['Category'] == 'FRUIT'
merged_df.loc[fruit_filter, 'final_table'] = merged_df[fruit_filter].apply(
    lambda row: find_nearest(row['split_table'], row['sum_list']),
    axis=1
)

# 提取每个Store的FRUIT结果，计算VEG分类的final_table
fruit_final_vals = merged_df[fruit_filter].set_index('Store')['final_table']
veg_filter = merged_df['Category'] == 'VEG'
merged_df.loc[veg_filter, 'final_table'] = merged_df[veg_filter].apply(
    lambda row: row['tot_tables'] - fruit_final_vals[row['Store']],
    axis=1
)

# 整理输出结果（保留原df1的列顺序）
result_df = merged_df[['Store', 'Category', 'split_table', 'tot_tables', 'final_table']]
print(result_df)

运行代码后输出与预期一致：

Store Category  split_table  tot_tables  final_table
0     11    FRUIT          7.0          12          6.5
1     11      VEG          5.0          12          5.5
2     13    FRUIT          5.0          11          5.0
3     13      VEG          6.0          11          6.0

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