基于另一DataFrame为df1中8月数据设置对应体重目标的正确方法

编辑补充：添加更易复现的DataFrame

我有两个如下所示的DataFrame：

df1 #

代码定义：

import pandas as pd
import numpy as np

index = [0,1,2,3,4,5,6,7,8]
a = pd.Series(["John Smith", "John Smith", "John Smith", "Kobe Bryant", "Kobe Bryant", "Kobe Bryant", "Jeff Daniels", "Jeff Daniels", "Jeff Daniels"], index=index)
b = pd.Series(["7/29/2022", "8/7/2022", "8/29/2022", "7/9/2022", "7/29/2022", "8/9/2022", "7/28/2022", "8/8/2022", "8/28/2022"], index=index)
c = pd.Series([185, 187, 186.5, 212.5, 217.5, 220.5, 211.1, 210.5, 213], index=index)
df1 = pd.DataFrame({"Name": a, "Date": b, "Weight": c, "Goal": np.nan})
# 注：原代码字符串未加引号，此处补充修正以保证可运行

表格格式：

df2 #

代码定义：

index = [0,1,2]
a = pd.Series(["John Smith", "Kobe Bryant", "Jeff Daniels"], index=index)
b = pd.Series([195,230,220], index=index)
df2 = pd.DataFrame({"Name": a, "Weight Goal": b})
# 注：原代码多余空Series已移除，同时补充字符串引号

表格格式：

需求：为df1中每位选手设置df2对应的体重目标，但仅针对8月的数据，忽略7月日期。

我尝试用for循环实现，但返回空DataFrame且出现SettingWithCopy警告，代码如下：

for player in df1['Name']:
    df1 = df1.loc[(df1['Name'] == f'{player}') & (df1['Date'] > '8/1/2022')]
    df1.at[df2['Name'] == f'{player}',  'Goal'] = (df2.loc[df2.Name == f'{player}']['Weight Goal'])

希望得到正确的实现方法。

步骤说明 #

1. 统一日期格式：将df1的Date列转为datetime类型，确保日期筛选的准确性，避免字符串比较的潜在问题。
2. 关联目标数据：通过merge方法，基于Name列将df2的体重目标匹配到df1中。
3. 条件赋值：仅给8月的行填充目标体重，7月的行保持NaN。

实现代码 #

import pandas as pd
import numpy as np

# 创建可运行的df1和df2
index = [0,1,2,3,4,5,6,7,8]
a = pd.Series(["John Smith", "John Smith", "John Smith", "Kobe Bryant", "Kobe Bryant", "Kobe Bryant", "Jeff Daniels", "Jeff Daniels", "Jeff Daniels"], index=index)
b = pd.Series(["7/29/2022", "8/7/2022", "8/29/2022", "7/9/2022", "7/29/2022", "8/9/2022", "7/28/2022", "8/8/2022", "8/28/2022"], index=index)
c = pd.Series([185, 187, 186.5, 212.5, 217.5, 220.5, 211.1, 210.5, 213], index=index)
df1 = pd.DataFrame({"Name": a, "Date": b, "Weight": c, "Goal": np.nan})

index = [0,1,2]
a = pd.Series(["John Smith", "Kobe Bryant", "Jeff Daniels"], index=index)
b = pd.Series([195,230,220], index=index)
df2 = pd.DataFrame({"Name": a, "Weight Goal": b})

# 核心处理逻辑
# 1. 转换日期列格式
df1['Date'] = pd.to_datetime(df1['Date'], format='%m/%d/%Y')
# 2. 合并df2的目标体重数据
df1 = df1.merge(df2, on='Name', how='left')
# 3. 仅为8月的数据填充Goal列
df1['Goal'] = np.where(df1['Date'].dt.month == 8, df1['Weight Goal'], df1['Goal'])
# 4. 可选：移除临时的Weight Goal列
df1 = df1.drop('Weight Goal', axis=1)

print(df1)

运行结果 #

Name       Date  Weight   Goal
0   John Smith 2022-07-29   185.0    NaN
1   John Smith 2022-08-07   187.0  195.0
2   John Smith 2022-08-29   186.5  195.0
3  Kobe Bryant 2022-07-09   212.5    NaN
4  Kobe Bryant 2022-07-29   217.5    NaN
5  Kobe Bryant 2022-08-09   220.5  230.0
6  Jeff Daniels 2022-07-28   211.1    NaN
7  Jeff Daniels 2022-08-08   210.5  220.0
8  Jeff Daniels 2022-08-28   213.0  220.0

原代码问题分析 #

• 循环中每次重新赋值df1 = df1.loc[...]会持续缩小数据集，最终导致df1为空：第一次循环后df1只剩当前选手的8月数据，后续循环无法匹配其他选手的记录。
• df1.at[...]的索引使用错误，未匹配df1自身的索引，导致赋值失败并触发SettingWithCopy警告。

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