Pandas处理Excel时Port列无法按Sender字段填充问题

我编写了一段Python脚本处理Excel文件，逻辑如下：

• 检查Backend列的URL，若包含端口号（如:8080）则提取端口写入Port列
• 若URL无端口号，根据Sender列的值判断：HTTP对应8080，HTTPS或https-squid-proxy-sender对应443

目前从URL提取端口的功能正常，但从Sender列获取端口时，Port单元格始终为空。通过print(sender, port)验证，脚本能正常读取Sender值，但Port仍未被正确填充。

输入Excel示例 #

执行后异常结果 #

我的代码 #

import pandas as pd

def get_port_from_url(url):
    if ":" in url:
        return url.split(":")[-1].split("/")[0]
    return None

def get_port_from_sender(sender):
    if sender == "https":
        return "443"
    elif sender == "http":
        return "8080"
    elif sender == "https-squid-proxy-sender":
        return "443"
    return None

def process_excel(input_file, output_file):
    # Read the input Excel file
    df = pd.read_excel(input_file)

    # Create a list to store the processed data
    processed_data = []

    # Iterate through each row of the input DataFrame
    for index, row in df.iterrows():
        service_group = row["Service Group"]
        primary_support_group = row["Primary Support Group"]
        backend = row["Backend"]
        sender = row["Sender"]
        load_balancer = row["Load Balancer"]

        # Extract the port from the backend URL or determine the port based on the sender value
        port = get_port_from_url(backend)
        if port is None:
            port = get_port_from_sender(sender)

        # Append the processed row to the list
        processed_row = [service_group, primary_support_group, backend, sender, load_balancer, port]
        processed_data.append(processed_row)
        sender = repr(sender)
        print(sender, port)

    # Create a DataFrame from the list
    headers = ["Service Group", "Primary Support Group", "Backend", "Sender", "Load Balancer", "Port"]
    processed_df = pd.DataFrame(processed_data, columns=headers)

    # Write the processed data to the output Excel file
    processed_df.to_excel(output_file, index=False)

if __name__ == "__main__":
    input_file_name = "dev_extract.xlsx"  # Replace with the actual input file name
    output_file_name = "extract_output.xlsx"  # Replace with the desired output file name

    process_excel(input_file_name, output_file_name)

问题原因及修复方案 #

核心问题

1. URL端口提取逻辑缺陷：原get_port_from_url仅通过冒号判断，若URL包含协议（如http://）或其他非端口冒号，会错误提取无效内容，导致port不为None，跳过Sender匹配逻辑
2. Sender匹配容错性差：未处理Excel中可能存在的空格、大小写差异或空值，导致匹配失败返回None

修复后的代码

import pandas as pd
import re

def get_port_from_url(url):
    # 正则匹配冒号后紧跟数字的端口部分，避免误识别协议冒号
    port_match = re.search(r':(\d+)(?:/|$)', str(url))
    if port_match:
        return port_match.group(1)
    return None

def get_port_from_sender(sender):
    # 处理空值、去除前后空格并统一转小写，提升匹配容错性
    if pd.isna(sender):
        return None
    sender_clean = sender.strip().lower()
    if sender_clean in ["https", "https-squid-proxy-sender"]:
        return "443"
    elif sender_clean == "http":
        return "8080"
    return None

def process_excel(input_file, output_file):
    df = pd.read_excel(input_file)
    processed_data = []

    for index, row in df.iterrows():
        service_group = row["Service Group"]
        primary_support_group = row["Primary Support Group"]
        backend = row["Backend"]
        sender = row["Sender"]
        load_balancer = row["Load Balancer"]

        port = get_port_from_url(backend)
        if port is None:
            port = get_port_from_sender(sender)

        processed_row = [service_group, primary_support_group, backend, sender, load_balancer, port]
        processed_data.append(processed_row)
        print(repr(sender), port)

    headers = ["Service Group", "Primary Support Group", "Backend", "Sender", "Load Balancer", "Port"]
    processed_df = pd.DataFrame(processed_data, columns=headers)
    processed_df.to_excel(output_file, index=False)

if __name__ == "__main__":
    input_file_name = "dev_extract.xlsx"
    output_file_name = "extract_output.xlsx"
    process_excel(input_file_name, output_file_name)

修复说明

• 改用正则表达式提取端口，仅匹配冒号后跟随数字的有效端口
• 对Sender值做空值处理、空格去除和大小写统一，覆盖Excel中常见的格式问题
• 确保URL无有效端口时，能正确匹配Sender值并填充对应端口

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