基于字符串匹配重命名Python列表中含多关键词的DataFrame表头

问题背景 #

从爬取的表格中得到的原始DataFrame表头示例如下：

headers = ['0 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Name  and Principal Position|', '1 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan',  '2 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Year|', '3 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Year|', '4 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '5 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Salary| ($)|', '6 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Salary| ($)|', '7 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '8 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Option  Awards| ($)|', '9 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Option  Awards| ($)|', '10 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '11 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Non-Equity  Incentive Plan Compensation| ($)|', '12 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Non-Equity  Incentive Plan Compensation| ($)|', '13 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '14 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Change  in Pension Value and Nonqualified Deferred Compensation  Earnings| ($)|', '15 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Change  in Pension Value and Nonqualified Deferred Compensation  Earnings| ($)|', '16 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '17 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan All  Other Compensation| ($)|', '18 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan All  Other Compensation| ($)|', '19 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan nan', '20 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Total| ($)|', '21 Summary  Compensation Table| for  Fiscal Year End December 31, 2006| nan Total| ($)|']

现有处理逻辑：

1. 第一步筛选包含指定关键词的列：

df= df.filter(regex='Name|Year|Salary|Bonus|Period') 

2. 第二步按关键词重命名表头：

headers = df.columns.values.tolist()
headers = ["Name" if "Name" in ele else ele for ele in headers]
headers = ["Year" if "Year" in ele else ele for ele in headers]
headers = ["Period" if "Period" in ele else ele for ele in headers]
headers = ["Salary" if "Salary" in ele else ele for ele in headers]
headers = ["Bonus" if "Bonus" in ele else ele for ele in headers]
df.columns = headers

现有问题：

• 所有表头都包含公共关键词Year，会导致所有表头最终都被重命名为Year
• 单个表头可能同时包含多个目标关键词，无法自动匹配未被使用的其他关键词

实现代码 #

通过关键词优先级匹配+已使用关键词标记的逻辑即可实现需求：

# 按优先级设置目标关键词，越靠前的优先级越高，会优先分配
target_keywords = ["Name", "Year", "Period", "Salary", "Bonus"]
used_kw = set()
new_headers = []

for old_head in df.columns:
    # 按优先级遍历关键词，找到第一个表头包含、且未被使用的关键词
    for kw in target_keywords:
        if kw in old_head and kw not in used_kw:
            new_headers.append(kw)
            used_kw.add(kw)
            break
    else:
        # 无匹配的未使用关键词时，可保留原表头或自定义其他处理规则
        new_headers.append(old_head)

df.columns = new_headers

逻辑说明 #

• 你可以根据自己的需求调整target_keywords的顺序，优先级高的关键词会被优先分配给最先匹配到的表头
• 用集合used_kw记录已经分配过的关键词，避免重复使用
• 针对你给出的示例，运行代码后会依次分配Name、Year、Salary三个唯一的表头，不会出现全部重命名为Year的问题

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