如何在Pandas中基于数据字典的值筛选DataFrame

原始数据与需求 #

原始DataFrame df #

Server     Model    Slot  
  server1    Cisco     1        
  server1    Cisco     2         
  server1    Cisco     3        
  server1    Cisco     4         
  server1    Cisco     8        
  server1    Cisco   Chasis
  server1    Cisco   Chasis
  server2    IBM     Slot 5        
  server2    IBM     Slot 8         
  server2    IBM     Slot 9 
  server3    Micr    Slot 22       
  server3    Micr    Slot 18        
  server3    Micr    Slot 1
  server3    Micr    Chasis 1  

筛选规则字典 #

n_slots={'Cisco': 8, 'IBM':6, 'Micr':4} 

处理要求 #

• 仅保留Slot值在1至对应n_slots字典数值范围内的记录（兼容带"Slot"前缀的格式）
• 移除Slot单元格中的"Slot"文本
• 最终仅保留符合范围的有效记录

实现代码 #

import pandas as pd

# 构造原始DataFrame（若已有df可跳过此段）
data = {
    'Server': ['server1']*6 + ['server2']*3 + ['server3']*4,
    'Model': ['Cisco']*6 + ['IBM']*3 + ['Micr']*4,
    'Slot': ['1', '2', '3', '4', '8', 'Chasis', 'Chasis', 'Slot 5', 'Slot 8', 'Slot 9', 'Slot 22', 'Slot 18', 'Slot 1', 'Chasis 1']
}
df = pd.DataFrame(data)

# 提取Slot中的数字，转为数值类型
df['Slot_num'] = df['Slot'].str.extract(r'(\d+)').astype(float)

# 匹配每个Model对应的最大Slot值，过滤出1到最大值之间的有效记录
df['max_slot'] = df['Model'].map(n_slots)
valid_df = df[(df['Slot_num'].between(1, df['max_slot'])) & (~df['Slot_num'].isna())]

# 清理Slot列，保留纯数字
valid_df['Slot'] = valid_df['Slot_num'].astype(int).astype(str)

# 整理成最终结果
final_df = valid_df[['Server', 'Model', 'Slot']].reset_index(drop=True)

print(final_df)

最终结果 #

Server  Model Slot
0  server1  Cisco    1
1  server1  Cisco    2
2  server1  Cisco    3
3  server1  Cisco    4
4  server1  Cisco    8
5  server2    IBM    5
6  server3   Micr    1

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