如何用df2分组首行的open/high替换df1分组第二行对应值？

我有两个Pandas DataFrame：

import pandas as pd 

df1 = pd.DataFrame(
    {
        'sym': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'c'],
        'open': [99, 22, 34, 63, 75, 86, 1800, 82],
        'high': [3987, 41123, 46123, 6643, 75, 3745, 72123, 74],
        'x': ['gd', 'ed', 'we', 'vt', 'de', 'sw', 'ee', 'et'],

    }
)


df2 = pd.DataFrame(
    {
        'sym': ['a', 'a', 'b', 'b', 'c', 'c', 'c'],
        'open': [77, 232, 434, 33, 55, 66, 1000],
        'high': [177, 11123, 1123, 343, 55, 3545, 21323],
        'x': ['g', 'e', 'w', 'v', 'd', 's', 'g'],
    }
)

期望输出：

sym  open   high   x
0   a    99   3987  gd
1   a    77   177   ed
2   a    34  46123  we
3   a    63   6643  vt
4   b    75     75  de
5   b   434   1123  sw
6   b  1800  72123  ee
7   c    82     74  et

需求是按sym字段分组执行以下操作：

• 选取df2每个分组的首行；
• 仅提取该首行的open和high字段值；
• 用这些值替换df1对应分组第二行的open和high字段值。

举个例子，对于sym='a'的分组：

• 选取df2的第0行；
• 提取open=77、high=177；
• 将df1第1行的open=22、high=41123替换为77和177。

我尝试了以下代码，触发了IndexError：

def replace_second_row(df):
    selected_sym = df.sym.iloc[0]
    row = df2.loc[df2.sym == selected_sym]
    row = row[['open', 'high']].iloc[0]
    df.iloc[1, df.columns.get_loc('open'): df.columns.get_loc('open') + 2] = row
    return df


output = df1.groupby('sym').apply(replace_second_row)

报错回溯信息：

Traceback (most recent call last):
  File "D:\python\py_files\example_df.py", line 1618, in <module>
    x = df1.groupby('sym').apply(replace_second_row)
  File "C:\Users\AF\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\core\groupby\groupby.py", line 894, in apply
    result = self._python_apply_general(f, self._selected_obj)
  File "C:\Users\AF\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\core\groupby\groupby.py", line 928, in _python_apply_general
    keys, values, mutated = self.grouper.apply(f, data, self.axis)
  File "C:\Users\AF\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\core\groupby\ops.py", line 238, in apply
    res = f(group)
  File "D:\python\py_files\example_df.py", line 1614, in replace_second_row
    df.iloc[1, df.columns.get_loc('open'): df.columns.get_loc('open') + 2] = row
  File "C:\Users\AF\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\core\indexing.py", line 689, in __setitem__
    self._has_valid_setitem_indexer(key)
  File "C:\Users\AF\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\core\indexing.py", line 1401, in _has_valid_setitem_indexer
    raise IndexError("iloc cannot enlarge its target object")
IndexError: iloc cannot enlarge its target object

需要解决该报错，并实现需求的正确方法。

报错原因 #

报错iloc cannot enlarge its target object是因为当分组的行数不足2行时（比如sym='c'的分组只有1行），尝试访问df.iloc[1]会超出索引范围，导致Pandas试图扩大对象来赋值，这是不允许的。

正确实现方案 #

方案一：高效匹配替换（推荐） #

通过预提取映射数据+组内行号标记的方式，避免循环操作，效率更高：

import pandas as pd

# 提取df2每个sym分组的首行open和high值，构建映射表
df2_first = df2.groupby('sym').first()[['open', 'high']].rename(columns={'open': 'new_open', 'high': 'new_high'})

# 给df1添加组内行号，用于定位第二行
df1['group_idx'] = df1.groupby('sym').cumcount()

# 合并映射表到df1
df1_merged = df1.merge(df2_first, on='sym', how='left')

# 仅对组内行号为1的行替换open和high
mask = df1_merged['group_idx'] == 1
df1.loc[mask, ['open', 'high']] = df1_merged.loc[mask, ['new_open', 'new_high']].values

# 删除临时列
df1.drop('group_idx', axis=1, inplace=True)

print(df1)

方案二：改进groupby.apply逻辑 #

在原思路基础上加入边界判断，只处理行数≥2的分组：

def replace_second_row(df):
    # 仅当分组行数≥2时执行替换
    if len(df) >= 2:
        sym = df['sym'].iloc[0]
        # 获取df2对应分组的首行open和high
        replace_vals = df2[df2['sym'] == sym][['open', 'high']].iloc[0]
        # 替换组内第二行的目标字段
        df.iloc[1, df.columns.get_indexer(['open', 'high'])] = replace_vals
    return df

output = df1.groupby('sym', group_keys=False).apply(replace_second_row)
print(output)

两种方案都能得到期望的输出，方案一适合大数据量场景，方案二更贴近你原本的代码逻辑。

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