创建Pandas DataFrame时指定规则项未被包含问题求助

问题背景 #

现有四个列表需要转换为DataFrame并分组统计，但"More Than 3 Failed Login Attempts Within 1 Hour"规则未出现在统计结果中：

输入列表：

rule_name_list = ['More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'Member Added to Security-Enabled Global Group', 'Security-Enabled Global Group Modified']
risk_score_list = [47, 47, 47, 47, 47, 47, 73, 73]
severity_list = ['medium', 'medium', 'medium', 'medium', 'medium', 'medium', 'high', 'high']
code_list = ['N/A', '4625', '4625', '4625', '4625', '4625', '4728', '4737']

实现代码：

def dataframe():
    df=pd.DataFrame({"Code":code_list,"Event":rule_name_list,"RiskScore":risk_score_list,"Severity":severity_list})
    # index = 1 * pd.RangeIndex(start=1, stop=2) #Add auto index
    #Group Columns
    df = df[df.Code.isin(event_code_list)] \
        .groupby(["Code",'Event','RiskScore','Severity']) \
        .agg(Detections = ("Event", len)) \
        .reset_index()
    df = pd.DataFrame(df,columns=["Code",'Event','RiskScore','Severity','Detections'])
    return df
final = dataframe()

当前输出（缺失目标规则）：

Code                                          Event  RiskScore Severity  Detections
0  4728  Member Added to Security-Enabled Global Group         73     high           1
1  4737         Security-Enabled Global Group Modified         73     high           1

预期输出：

Code                                          Event  RiskScore Severity  Detections
0  4728  Member Added to Security-Enabled Global Group         73     high           1
1  4737         Security-Enabled Global Group Modified         73     high           1
2  4625  More Than 3 Failed Login Attempts Within 1 Hour       47     medium         6

问题原因 #

1. 过滤条件排除了目标数据：代码中df[df.Code.isin(event_code_list)]这一步，若event_code_list未包含'4625'，则所有Code为4625的行都会被过滤掉，导致目标规则无法出现在结果中。
2. 规则名称格式不一致：rule_name_list中前六个元素末尾带有空格，而预期输出中的规则名称无空格，若不处理会导致分组后名称不统一（虽不是当前缺失的核心原因，但属于潜在问题）。

修复方案 #

方案1：直接过滤无效数据（推荐） #

如果不需要依赖event_code_list过滤，直接排除Code为'N/A'的无效行，确保4625的有效数据被保留，同时清理规则名称的空格：

修改后的代码：

import pandas as pd

rule_name_list = ['More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'Member Added to Security-Enabled Global Group', 'Security-Enabled Global Group Modified']
risk_score_list = [47, 47, 47, 47, 47, 47, 73, 73]
severity_list = ['medium', 'medium', 'medium', 'medium', 'medium', 'medium', 'high', 'high']
code_list = ['N/A', '4625', '4625', '4625', '4625', '4625', '4728', '4737']

def dataframe():
    df=pd.DataFrame({"Code":code_list,"Event":rule_name_list,"RiskScore":risk_score_list,"Severity":severity_list})
    # 过滤无效的N/A行
    df = df[df.Code != 'N/A']
    # 清理Event列首尾空格，统一规则名称
    df['Event'] = df['Event'].str.strip()
    # 分组统计
    df = df.groupby(["Code",'Event','RiskScore','Severity']) \
           .agg(Detections = ("Event", len)) \
           .reset_index()
    return df

final = dataframe()
print(final)

方案2：更新过滤列表（保留原有逻辑） #

如果必须使用isin过滤，确保event_code_list包含'4625'，同时清理规则名称空格：

import pandas as pd

# 确保过滤列表包含目标Code
event_code_list = ['4625', '4728', '4737']

rule_name_list = ['More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'More Than 3 Failed Login Attempts Within 1 Hour ', 'Member Added to Security-Enabled Global Group', 'Security-Enabled Global Group Modified']
risk_score_list = [47, 47, 47, 47, 47, 47, 73, 73]
severity_list = ['medium', 'medium', 'medium', 'medium', 'medium', 'medium', 'high', 'high']
code_list = ['N/A', '4625', '4625', '4625', '4625', '4625', '4728', '4737']

def dataframe():
    df=pd.DataFrame({"Code":code_list,"Event":rule_name_list,"RiskScore":risk_score_list,"Severity":severity_list})
    # 使用包含目标Code的列表过滤
    df = df[df.Code.isin(event_code_list)]
    # 清理Event列空格
    df['Event'] = df['Event'].str.strip()
    # 分组统计
    df = df.groupby(["Code",'Event','RiskScore','Severity']) \
           .agg(Detections = ("Event", len)) \
           .reset_index()
    return df

final = dataframe()
print(final)

修复后输出 #

两种方案都会得到预期的统计结果：

Code                                          Event  RiskScore Severity  Detections
0  4625  More Than 3 Failed Login Attempts Within 1 Hour         47   medium           6
1  4728  Member Added to Security-Enabled Global Group         73     high           1
2  4737         Security-Enabled Global Group Modified         73     high           1

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