基于字典规则填充Pandas DataFrame单元格的技术实现

问题说明 #

现有如下Pandas DataFrame，需要根据指定的字典规则填充Name和Filter列：

初始DataFrame #

import pandas as pd
import numpy as np

data = {'dates': ['2/16/2023', '2/17/2023', '2/18/2023', '2/19/2023', '2/20/2023', '2/21/2023', '2/22/2023', '2/23/2023', '2/24/2023', '2/25/2023', '2/26/2023', '2/27/2023', '2/28/2023', '3/1/2023', '3/2/2023', '3/3/2023', '3/4/2023', '3/5/2023', '3/6/2023', '3/7/2023', '3/8/2023', '3/9/2023', '3/10/2023'],
        'Name': ['', '', '', '', 'A', '', '', '', '', 'B', '', '', '', '', '', 'A', '', '', '', 'D', '', '', ''],
        'Filter': [np.nan, np.nan, np.nan, np.nan, 0.0, np.nan, np.nan, np.nan, np.nan, 1.0, np.nan, np.nan, np.nan, np.nan, np.nan, 2.0, np.nan, np.nan, np.nan, 3.0, np.nan, np.nan, np.nan]}

df = pd.DataFrame(data)

填充规则字典 #

mapper = {0: {'begin': -3, 'length': 3},
          1: {'begin': -2, 'length': 2},
          2: {'begin': -3, 'length': 2},
          3: {'begin': -1, 'length': 2}}

规则示例 #

以Filter值为0的行（对应Name为A）为例：从该行位置向前偏移3位开始，连续填充3次A（到Name列）和0.0（到Filter列）。

预期输出 #

import pandas as pd
import numpy as np

data = {'dates': ['2/16/2023', '2/17/2023', '2/18/2023', '2/19/2023', '2/20/2023', '2/21/2023', '2/22/2023', '2/23/2023', '2/24/2023', '2/25/2023', '2/26/2023', '2/27/2023', '2/28/2023', '3/1/2023', '3/2/2023', '3/3/2023', '3/4/2023', '3/5/2023', '3/6/2023', '3/7/2023', '3/8/2023', '3/9/2023', '3/10/2023'],
        'Name': ['', 'A', 'A', 'A', '', '', '', 'B', 'B', '', '', '', 'A', 'A', '', '', '', '', 'D', 'D', '', '', ''],
        'Filter': [np.nan, 0.0, 0.0, 0.0, np.nan, np.nan, np.nan, 1.0, 1.0, np.nan, np.nan, np.nan, 2.0, 2.0, np.nan, np.nan, np.nan, np.nan, 3.0, 3.0, np.nan, np.nan, np.nan]}

df = pd.DataFrame(data)

解决方案 #

直接通过遍历锚点行并定位区间填充的方式实现，代码如下：

import pandas as pd
import numpy as np

# 初始化DataFrame
data = {'dates': ['2/16/2023', '2/17/2023', '2/18/2023', '2/19/2023', '2/20/2023', '2/21/2023', '2/22/2023', '2/23/2023', '2/24/2023', '2/25/2023', '2/26/2023', '2/27/2023', '2/28/2023', '3/1/2023', '3/2/2023', '3/3/2023', '3/4/2023', '3/5/2023', '3/6/2023', '3/7/2023', '3/8/2023', '3/9/2023', '3/10/2023'],
        'Name': ['', '', '', '', 'A', '', '', '', '', 'B', '', '', '', '', '', 'A', '', '', '', 'D', '', '', ''],
        'Filter': [np.nan, np.nan, np.nan, np.nan, 0.0, np.nan, np.nan, np.nan, np.nan, 1.0, np.nan, np.nan, np.nan, np.nan, np.nan, 2.0, np.nan, np.nan, np.nan, 3.0, np.nan, np.nan, np.nan]}

df = pd.DataFrame(data)

# 填充规则
mapper = {0: {'begin': -3, 'length': 3},
          1: {'begin': -2, 'length': 2},
          2: {'begin': -3, 'length': 2},
          3: {'begin': -1, 'length': 2}}

# 获取所有Filter非空的锚点行
anchor_rows = df[df['Filter'].notna()]

# 遍历每个锚点，执行填充
for idx, row in anchor_rows.iterrows():
    filter_key = int(row['Filter'])
    name_val = row['Name']
    rule = mapper[filter_key]
    
    # 计算填充区间的起始和结束索引
    start_pos = idx + rule['begin']
    end_pos = start_pos + rule['length']
    
    # 确保索引在DataFrame范围内，避免越界
    if 0 <= start_pos < end_pos <= len(df):
        df.loc[start_pos:end_pos-1, 'Name'] = name_val
        df.loc[start_pos:end_pos-1, 'Filter'] = filter_key

# 查看结果
print(df)

代码说明 #

• 先筛选出Filter列非空的行作为填充锚点，这些行记录了需要填充的目标值和规则key
• 遍历每个锚点，根据mapper中的规则计算填充的起始和结束位置
• 使用df.loc定位到目标区间，批量填充Name和Filter列的值
• 加入索引范围判断，防止出现越界报错

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