基于Pandas实现df1映射df2并计算生成plan_score等新列

我来帮你解决这个DataFrame的更新问题，下面是具体的实现步骤和可直接运行的测试代码：

首先，先把示例中的df1和df2用代码构造出来，方便后续验证：

import pandas as pd

# 构造df1
df1_data = [
    ["2020-02-01", 5, None, 0],
    ["2020-02-02", 23, None, 0],
    ["2020-02-03", 14, "start", 0],
    ["2020-02-04", 23, "start", 0],
    ["2020-02-05", 23, "start", 0],
    ["2020-02-06", 23, None, 0],
    ["2020-02-07", 30, "foundation", 0],
    ["2020-02-08", 29, "foundation", 0],
    ["2020-02-09", 100, None, 0],
    ["2020-02-10", 38, "learn", 0],
    ["2020-02-11", 38, "learn", 0],
    ["2020-02-12", 38, "learn", 0],
    ["2020-02-13", 70, None, 0],
    ["2020-02-14", 70, "practice", 0],
    ["2020-02-15", 38, None, 0],
    ["2020-02-16", 38, None, 0],
    ["2020-02-17", 70, "exam", 0],
    ["2020-02-18", 70, "exam", 0],
    ["2020-02-19", 38, "exam", 0],
    ["2020-02-20", 38, None, 0],
    ["2020-02-21", 70, None, 0],
    ["2020-02-22", 70, "test", 0],
    ["2020-02-23", 38, "test", 0],
    ["2020-02-24", 38, None, 0],
    ["2020-02-25", 70, None, 0],
    ["2020-02-26", 70, None, 0],
    ["2020-02-27", 70, None, 0],
]
df1 = pd.DataFrame(df1_data, columns=["Date", "t_factor", "plan", "plan_score"])

# 构造df2
df2_data = [
    ["2020-02-03", "2020-02-05", "start", 20],
    ["2020-02-07", "2020-02-08", "foundation", 25],
    ["2020-02-10", "2020-02-12", "learn", 10],
    ["2020-02-14", "2020-02-16", "practice", 20],
    ["2020-02-15", "2020-02-21", "exam", 30],
    ["2020-02-20", "2020-02-23", "test", 10],
]
df2 = pd.DataFrame(df2_data, columns=["From", "to", "plan", "score"])

一、处理单个score字段的情况 #

按照你给出的规则，我们可以按以下步骤实现：

1. 计算每个plan在df1中的出现次数：需要知道每个plan有多少行，才能把df2中的score平均分配到每一行。
2. 关联df2和计数结果，计算每行的增量值：用df2的score除以对应plan的行数，得到每行需要累加的数值。
3. 将增量映射到df1并计算累计和：把增量值对应到df1的每一行，NaN的plan行增量设为0，然后计算累计和——这样NaN行的plan_score就会自动沿用前一行的值。

具体代码如下：

# 步骤1：计算每个plan在df1中的行数
plan_counts = df1['plan'].value_counts().reset_index()
plan_counts.columns = ['plan', 'count']

# 步骤2：合并df2和计数结果，计算每行增量
df2_with_increment = df2.merge(plan_counts, on='plan', how='left')
df2_with_increment['increment'] = df2_with_increment['score'] / df2_with_increment['count']

# 步骤3：将增量映射到df1，NaN的plan对应增量为0
df1['increment'] = df1['plan'].map(df2_with_increment.set_index('plan')['increment']).fillna(0)

# 步骤4：计算累计和得到最终的plan_score
df1['plan_score'] = df1['increment'].cumsum()

# 可选：删除临时的increment列
df1.drop('increment', axis=1, inplace=True)

运行后，df1的plan_score列会完全符合规则：比如start有3行，每行累加20/3≈6.666，三行后累计到20；foundation有2行，每行累加25/2=12.5，两行后累计到25，NaN行的plan_score会保持前一行的数值。

二、处理多个score字段的情况（如score1、score2、score3） #

如果df2包含多个score字段，我们只需要循环处理每个score列，生成对应的plan_score列，最后删除初始的全0plan_score列即可：

假设df2的结构是这样的（包含score1、score2、score3）：

# 构造含多个score字段的df2示例
df2_multi_data = [
    ["2020-02-03", "2020-02-05", "start", 20, 15, 10],
    ["2020-02-07", "2020-02-08", "foundation", 25, 20, 15],
    ["2020-02-10", "2020-02-12", "learn", 10, 8, 5],
    ["2020-02-14", "2020-02-16", "practice", 20, 18, 12],
    ["2020-02-15", "2020-02-21", "exam", 30, 25, 20],
    ["2020-02-20", "2020-02-23", "test", 10, 8, 6],
]
df2_multi = pd.DataFrame(df2_multi_data, columns=["From", "to", "plan", "score1", "score2", "score3"])

处理代码如下：

# 先计算每个plan的行数（和之前一样）
plan_counts = df1['plan'].value_counts().reset_index()
plan_counts.columns = ['plan', 'count']

# 定义需要处理的score列
score_columns = ['score1', 'score2', 'score3']

# 循环处理每个score列
for score_col in score_columns:
    # 计算对应score的增量
    df2_increment = df2_multi.merge(plan_counts, on='plan', how='left')
    df2_increment['increment'] = df2_increment[score_col] / df2_increment['count']
    # 映射到df1并计算累计和
    df1[f'plan_score_{score_col}'] = df1['plan'].map(df2_increment.set_index('plan')['increment']).fillna(0).cumsum()

# 删除初始的全0 plan_score列
df1.drop('plan_score', axis=1, inplace=True)

# 可选：如果需要将某一列重命名为plan_score（比如只保留score1对应的列）
# df1.rename(columns={'plan_score_score1': 'plan_score'}, inplace=True)

这样就会生成plan_score_score1、plan_score_score2、plan_score_score3三个列，每个列都按照规则计算得到。

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