基于多列与子串高效合并DataFrame行的Pandas优化方案

问题背景 #

分析大量*nix系统sudo日志时，超长命令会被sudo和syslog拆分为多条记录。已将数据导入Pandas DataFrame，但原循环实现处理大量数据时速度极慢，需要更高效的Pandas风格方案。

原低效代码：

# Load some dummy data
data = [
        ["1694030392144", "server1", "bob" , "/home/bob/", "command=a bunch of commands here; "        ],
        ["1694030392145", "server1", "bob" , "/home/bob/", "(command continued) more commands here; "  ],
        ["1694030392146", "server1", "bob" , "/home/bob/", "(command continued) even commands here; "  ],
        ["1694030392147", "server1", "bob" , "/home/bob/", "(command continued) yet more commands here"],
        ["1694030392148", "server9", "bob" , "/home/bob/", "(command continued) WTF"                   ],
        ["1694030392149", "server2", "bob" , "/home/bob/", "command=a new command"                     ],
        ["1694030392150", "server3", "bob" , "/home/bob/", "command=I did something; "                 ],
        ["1694030392151", "server3", "bob" , "/home/bob/", "(command continued) I did another thing"   ],
        ["1694030392152", "server2", "fred", "/"         , "command=a new command"                     ],
        ["1694030392153", "server1", "todd", "/tmp/"     , "command=I did something; "                 ],
        ["1694030392154", "server1", "todd", "/tmp/"     , "(command continued) I did another thing"   ]
    ]
df = pd.DataFrame(data, columns=['epoch', 'server', 'account', 'pwd', 'command'])
# Data is typically in the correct order when loaded, but sorting just to be safe.
df.sort_values(['account','server','epoch'], inplace=True, ignore_index=True)

FirstLoop = True
for index, row in df.iterrows():
    # If the server or account changed, or it begins with "command =" again...
    if  curServer != row["server"] or curAccount != row["account"] or 
        row['command'].startswith("command=") == True:
        if FirstLoop == True:
            FirstLoop = False
        else:
            df.at[(curCommandIndex), 'command'] = curCommand
    
        # Index of current command grouping
        curCommandIndex = index
        # Starting building the full command and replace unecessary strings
        if row['command'].startswith("(command continued)"):
            curCommand = row['command'].replace("(command continued) ", "")
        elif row['command'].startswith("command="):
            curCommand = row['command'].replace("command=", "")
        else:
            curCommand = row['command']

    # Otherwise concat the commands together
    else: 
        if row['command'].startswith("(command continued)"):
            curCommand = curCommand + row['command'].replace("(command continued) ", "")    
        elif row['command'].startswith("command="):
            curCommand = curCommand + row['command'].replace("command=", "")
        else:
            curCommand = curCommand + row['command']
        # Drop the row after concating it onto the command
        df.drop(index, inplace=True)

    curAccount = row['account']
    curServer  = row['server']

高效解决方案 #

原方案用iterrows()逐行处理+drop()删除行，属于O(n²)操作，数据量大时性能骤降。改用Pandas向量化操作和分组聚合，时间复杂度降至O(n)，效率提升显著。

完整代码 #

import pandas as pd

# 加载示例数据
data = [
        ["1694030392144", "server1", "bob" , "/home/bob/", "command=a bunch of commands here; "        ],
        ["1694030392145", "server1", "bob" , "/home/bob/", "(command continued) more commands here; "  ],
        ["1694030392146", "server1", "bob" , "/home/bob/", "(command continued) even commands here; "  ],
        ["1694030392147", "server1", "bob" , "/home/bob/", "(command continued) yet more commands here"],
        ["1694030392148", "server9", "bob" , "/home/bob/", "(command continued) WTF"                   ],
        ["1694030392149", "server2", "bob" , "/home/bob/", "command=a new command"                     ],
        ["1694030392150", "server3", "bob" , "/home/bob/", "command=I did something; "                 ],
        ["1694030392151", "server3", "bob" , "/home/bob/", "(command continued) I did another thing"   ],
        ["1694030392152", "server2", "fred", "/"         , "command=a new command"                     ],
        ["1694030392153", "server1", "todd", "/tmp/"     , "command=I did something; "                 ],
        ["1694030392154", "server1", "todd", "/tmp/"     , "(command continued) I did another thing"   ]
    ]
df = pd.DataFrame(data, columns=['epoch', 'server', 'account', 'pwd', 'command'])

# 1. 先排序确保顺序正确
df.sort_values(['account', 'server', 'epoch'], inplace=True, ignore_index=True)

# 2. 提取纯命令文本，去除前缀
def clean_command(cmd):
    if cmd.startswith("command="):
        return cmd.replace("command=", "")
    elif cmd.startswith("(command continued)"):
        return cmd.replace("(command continued) ", "")
    return cmd

df['clean_cmd'] = df['command'].apply(clean_command)

# 3. 标记每个命令组的起始行：
# - 当command以command=开头，或者account/server与上一行不同时，视为新组起点
df['is_new_group'] = (
    df['command'].str.startswith("command=") |
    (df['account'] != df['account'].shift(1)) |
    (df['server'] != df['server'].shift(1))
)

# 4. 生成组ID：累计起始标记的数量，同组的行拥有相同ID
df['group_id'] = df['is_new_group'].cumsum()

# 5. 按组聚合：合并clean_cmd，保留组内第一条记录的其他字段
result_df = df.groupby('group_id').agg(
    epoch=('epoch', 'first'),
    server=('server', 'first'),
    account=('account', 'first'),
    pwd=('pwd', 'first'),
    full_command=('clean_cmd', ''.join)
).reset_index(drop=True)

print(result_df)

核心逻辑说明 #

1. 清洗命令文本：用apply()批量处理command列，去除冗余前缀，比循环逐行处理更高效。
2. 标记组起点：用向量化逻辑判断新命令组的起始行，避免循环中的条件判断。
3. 生成组ID：通过cumsum()累计起始标记，快速为同属一个命令的行分配统一ID。
4. 分组聚合：用groupby().agg()一次性完成命令合并和字段保留，这是Pandas最擅长的批量操作，性能远超逐行处理。

该方案在处理百万级记录时，速度会比原循环方案快几十甚至上百倍。

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