如何将DataFrame列中的列表转为逗号分隔的IP字符串？

我有一个最终会输出到CSV的DataFrame，其中“Affected IP Address”列的每个值都是列表，打印出来是这样的：

['10.0.7.248']
['10.0.7.248', '10.0.8.56']
['10.0.6.72']
['10.0.6.72', '10.0.5.46']
['10.0.9.126']
['10.0.9.126', '10.0.7.248']
['10.0.9.126', '10.0.7.248', '10.0.8.56']
['10.0.6.72']
['10.0.6.72', '10.0.5.46']
['10.0.9.126']
['10.0.9.126', '10.0.7.248']
['10.0.9.126', '10.0.7.248', '10.0.8.56']

我需要把这些列表转换成不带括号和单引号的字符串，用逗号分隔IP，最终格式如下：

10.0.7.248
10.0.7.248, 10.0.8.56
10.0.6.72
10.0.6.72, 10.0.5.46
10.0.9.126
10.0.9.126, 10.0.7.248
10.0.9.126, 10.0.7.248, 10.0.8.56

以下是我的脚本：

def main():
    csv_data = open_csv()
    get_scan_results(csv_data)

def open_csv():
    #Opens CSV file
    with open(f"{csv_filename}.csv", newline='') as f:
        reader = csv.reader(f)
        data = list(reader)    
    return data

def get_scan_results(data):
    # New dictionary to be created
    new_dict = {}
    for ip, host, os, vuln_title, vuln_id, cvss2, cvss3, descr, proof, solu, cves in data[1:]:
        # Converts CVSSv3 score into a 'Risk Exposure' metric, blank values return 'Null'
        if len(cvss3.strip()):
            converted_cvss3 = float(cvss3)
            if converted_cvss3 < 4.0:
                s = "Low"
            elif converted_cvss3 >= 4 and converted_cvss3 < 7:
                s = "Moderate"
            else:
                s = "High"
        elif len(cvss2.strip()):
            converted_cvss2 = float(cvss2)
            if converted_cvss2 < 4.0:
                s = "Low"
            elif converted_cvss2 >= 4 and converted_cvss2 < 7:
                s = "Moderate"
            else:
                s = "High"
        else:
            s = "Null"
        # Populates 'new_dict' with values, the keys will also be the column names in CSV/Excel
        vuln_data = new_dict.setdefault(vuln_id, {"Name": vuln_title, "Description": descr, "Source of Discovery": csv_filename, "Vulnerability ID": vuln_id, "Affected IP Address": [], "Solution": solu, "Risk Exposure": s })
        vuln_data["Affected IP Address"].append(ip)
        print (vuln_data["Affected IP Address"])


    # Creates DF object and exports to CSV
    new_list = new_dict.values()
    df = pd.DataFrame(new_list)
    df.to_csv(f"{exported_csv_filename}.csv", index=False)

if __name__ == "__main__":
    main()

解决方案 #

方法1：构建字典时直接生成字符串（推荐） #

不需要先存列表再转换，在每次更新IP列表后，直接把列表转成逗号分隔的字符串替换原列表。修改get_scan_results函数里的对应逻辑：

找到这两行：

vuln_data["Affected IP Address"].append(ip)
print (vuln_data["Affected IP Address"])

替换成：

vuln_data["Affected IP Address"].append(ip)
# 将列表转为逗号分隔的字符串
vuln_data["Affected IP Address"] = ', '.join(vuln_data["Affected IP Address"])
print(vuln_data["Affected IP Address"])

这样new_dict里的"Affected IP Address"就直接是目标格式的字符串，生成DataFrame后写入CSV即可满足需求。

方法2：生成DataFrame后处理列 #

如果不想修改构建字典的逻辑，可以在生成DataFrame后，对目标列做转换：

在df = pd.DataFrame(new_list)之后添加：

# 将列表列转为逗号分隔的字符串
df["Affected IP Address"] = df["Affected IP Address"].apply(lambda x: ', '.join(x))

之后再执行df.to_csv(...)即可。

完整修改后的脚本示例（方法1） #

def main():
    csv_data = open_csv()
    get_scan_results(csv_data)

def open_csv():
    # 打开CSV文件
    with open(f"{csv_filename}.csv", newline='') as f:
        reader = csv.reader(f)
        data = list(reader)    
    return data

def get_scan_results(data):
    # 创建新字典
    new_dict = {}
    for ip, host, os, vuln_title, vuln_id, cvss2, cvss3, descr, proof, solu, cves in data[1:]:
        # 将CVSSv3分数转换为风险等级，空值返回Null
        if len(cvss3.strip()):
            converted_cvss3 = float(cvss3)
            if converted_cvss3 < 4.0:
                s = "Low"
            elif converted_cvss3 >= 4 and converted_cvss3 < 7:
                s = "Moderate"
            else:
                s = "High"
        elif len(cvss2.strip()):
            converted_cvss2 = float(cvss2)
            if converted_cvss2 < 4.0:
                s = "Low"
            elif converted_cvss2 >= 4 and converted_cvss2 < 7:
                s = "Moderate"
            else:
                s = "High"
        else:
            s = "Null"
        # 填充字典，键将作为CSV/Excel的列名
        vuln_data = new_dict.setdefault(vuln_id, {"Name": vuln_title, "Description": descr, "Source of Discovery": csv_filename, "Vulnerability ID": vuln_id, "Affected IP Address": [], "Solution": solu, "Risk Exposure": s })
        vuln_data["Affected IP Address"].append(ip)
        # 转换为逗号分隔的字符串
        vuln_data["Affected IP Address"] = ', '.join(vuln_data["Affected IP Address"])
        print(vuln_data["Affected IP Address"])


    # 创建DataFrame并导出到CSV
    new_list = new_dict.values()
    df = pd.DataFrame(new_list)
    df.to_csv(f"{exported_csv_filename}.csv", index=False)

if __name__ == "__main__":
    main()

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