如何根据公共字符串合并列表中同一网络接口的多行CSV数据？

问题场景 #

办公网络的CSV样本中，每个网络接口的信息被拆分到三行（分别对应PE_INT_DESC、PE_IPV4_ADDRESS、PE_ROUTING_VRRP类型），需将同一接口的三行数据合并为单行，同时保留0-15共16列的结构。

原始数据 #

[Oct_04_09:19:08, PE_INT_DESC, pe_node1_ge-1/2/2.20, TO_PE3, up_up, , , , , , , , , , , WA_WE_PI_960_B_Pier
Oct_04_09:19:09, PE_IPV4_ADDRESS, pe_node1_ge-1/2/2.20, , , , 192.168.20.2/24, 192.168.20.1, vrrp-group_1, , , , , , , WA_WE_PI_960_B_Pier
Oct_04_09:19:18, PE_ROUTING_VRRP, pe_node1_ge-1/2/2.20, , , , , , , , master, 192.168.20.2, vr-uptime:14w2d11:30_mr-uptime:14w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier
Oct_04_09:19:19, PE_INT_DESC, pe_node2_ge-1/1/1.10, TO_PE2, up_up, , , , , , , , , , , WA_WE_PI_960_B_Pier
Oct_04_09:19:20, PE_IPV4_ADDRESS, pe_node2_ge-1/1/1.10, , , , 192.168.10.2/24, 192.168.10.1, vrrp-group_1, , , , , , , WA_WE_PI_960_B_Pier
Oct_04_09:19:28, PE_ROUTING_VRRP, pe_node2_ge-1/1/1.10, , , , , , , , master, 192.168.10.2, vr-uptime:13w1d11:30_mr-uptime:15w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier]

预期结果 #

[Oct_04_09:19:08, PE_INT, pe_node1_ge-1/2/2.20, TO_PE3, up_up, , 192.168.20.2/24, 192.168.20.1, vrrp-group_1, , master, 192.168.20.2, vr-uptime:14w2d11:30_mr-uptime:14w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier
Oct_04_09:19:19, PE_INT, pe_node2_ge-1/1/1.10, TO_PE2, up_up, , 192.168.10.2/24, 192.168.10.1, vrrp-group_1, , master, 192.168.10.2, vr-uptime:13w1d11:30_mr-uptime:15w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier]

解决方案（Python脚本） #

通过分组提取有效数据并合并，确保列数不变：

# 原始数据（可替换为读取CSV文件的逻辑）
lines_list = [
    "Oct_04_09:19:08, PE_INT_DESC, pe_node1_ge-1/2/2.20, TO_PE3, up_up, , , , , , , , , , , WA_WE_PI_960_B_Pier",
    "Oct_04_09:19:09, PE_IPV4_ADDRESS, pe_node1_ge-1/2/2.20, , , , 192.168.20.2/24, 192.168.20.1, vrrp-group_1, , , , , , , WA_WE_PI_960_B_Pier",
    "Oct_04_09:19:18, PE_ROUTING_VRRP, pe_node1_ge-1/2/2.20, , , , , , , , master, 192.168.20.2, vr-uptime:14w2d11:30_mr-uptime:14w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier",
    "Oct_04_09:19:19, PE_INT_DESC, pe_node2_ge-1/1/1.10, TO_PE2, up_up, , , , , , , , , , , WA_WE_PI_960_B_Pier",
    "Oct_04_09:19:20, PE_IPV4_ADDRESS, pe_node2_ge-1/1/1.10, , , , 192.168.10.2/24, 192.168.10.1, vrrp-group_1, , , , , , , WA_WE_PI_960_B_Pier",
    "Oct_04_09:19:28, PE_ROUTING_VRRP, pe_node2_ge-1/1/1.10, , , , , , , , master, 192.168.10.2, vr-uptime:13w1d11:30_mr-uptime:15w2d10:31__, VRRP, , WA_WE_PI_960_B_Pier"
]

# 按接口ID分组存储三行数据
interface_groups = {}
for line in lines_list:
    columns = [col.strip() for col in line.split(',')]
    iface_id = columns[2]
    if iface_id not in interface_groups:
        interface_groups[iface_id] = []
    interface_groups[iface_id].append(columns)

# 合并每组数据
merged_result = []
for iface_id, rows in interface_groups.items():
    # 以接口描述行为基础初始化合并行
    merged_row = rows[0].copy()
    # 修改类型列为PE_INT
    merged_row[1] = "PE_INT"
    
    # 填充IPv4地址相关列（索引6、7、8）
    ipv4_cols = rows[1]
    merged_row[6] = ipv4_cols[6]
    merged_row[7] = ipv4_cols[7]
    merged_row[8] = ipv4_cols[8]
    
    # 填充VRRP路由相关列（索引10、11、12、13）
    vrrp_cols = rows[2]
    merged_row[10] = vrrp_cols[10]
    merged_row[11] = vrrp_cols[11]
    merged_row[12] = vrrp_cols[12]
    merged_row[13] = vrrp_cols[13]
    
    # 转换为CSV格式字符串
    merged_line = ', '.join(merged_row)
    merged_result.append(merged_line)

# 打印或保存结果
print("合并后的数据：")
for line in merged_result:
    print(line)

脚本说明 #

1. 分组：按接口ID（第三列）将三行数据归类到同一组，确保每个接口的完整信息被收集。
2. 初始化模板：以PE_INT_DESC行作为合并后的基础行，修改第二列为PE_INT。
3. 填充数据：分别从PE_IPV4_ADDRESS行提取IP地址相关字段，从PE_ROUTING_VRRP行提取VRRP状态、地址等字段，填充到模板对应列。
4. 输出：将合并后的行转换为CSV格式，保持16列结构不变。

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