Python为3500万行CSV添加表头后列错位问题求助
2026-8-6
CSV添加表头后列错位问题解决
问题背景
尝试用以下Python代码给3500万行的CSV文件添加表头：
import csv with open('E:\\Dataset\\dataset1.csv') as infile: text = infile.read() header = ['User IP','Top-level domain', 'Timestamp', 'Is Attack', 'Request', 'Len(request) withou TLD', 'Subdomains_count', 'w_count', 'w_max', 'entropy', 'w_max_ratio', 'w_count_ratio', 'digits_ratio', 'uppercase_ratio', 'time_avg', 'time_stdev', 'size_avg', 'size stdev', 'throughput', 'unique', 'entropy_avg' 'entropy_stdev'] with open('E:\\Dataset\\dataset2.csv', 'w') as outfile: # join the headers into a string with commas and add a newline outfile.write(f"{','.join(header)}\\n") outfile.write(text)
添加后出现表头列错位，原数据第一列被当作索引，导致数据与表头完全不匹配。原数据（无表头）头部：
186.169.253.58 surbl.org 1624438272607 False \ 0 186.169.253.58 surbl.org 1624438272607 False 1 186.169.253.58 spamhaus.org 1624438273058 False 2 186.169.253.58 spamhaus.org 1624438273058 False 3 186.169.253.58 spamhaus.org 1624438273059 False 4 186.169.253.58 spamhaus.org 1624438273059 False h.surbl.org 1 1.1 0 0.1 -0.0 0.0 \ 0 f.surbl.org 1 1 0 0 -0.000000 0.000000 1 118.141.11.106.sbl.spamhaus.org 18 5 0 0 2.633731 0.000000 2 118.141.11.106.zen.spamhaus.org 18 5 1 3 2.633731 0.166667 3 128.141.11.106.sbl.spamhaus.org 18 5 0 0 2.863826 0.000000 4 128.141.11.106.zen.spamhaus.org 18 5 1 3 2.863826 0.166667 0.0.1 0.0.2 0.0.3 3.4444444444444446 9.59311095410544 1.5 \ 0 0.000000 0.000000 0.0 0.222222 0.440959 1.0 1 0.000000 0.611111 0.0 55.555556 165.542375 17.2 2 0.055556 0.611111 0.0 0.333333 0.500000 17.2 3 0.000000 0.611111 0.0 0.333333 0.500000 17.3 4 0.055556 0.611111 0.0 0.333333 0.500000 17.4 1.5811388300841898 468.75 0.4444444444444444 0.25849625007211563 \ 0 0.000000 3333.333333 0.555556 0.000000 1 0.421637 343.313373 0.000000 3.048277 2 0.421637 43000.000000 0.000000 2.983547 3 0.483046 43250.000000 0.000000 2.959741 4 0.516398 43500.000000 0.000000 2.935936 0.81743691684035 0 0.000000 1 0.177285 2 0.199622 3 0.198131 4 0.193400
添加表头后的错误数据头部：
User IP Top-level domain Timestamp \ 186.169.253.58 surbl.org 1624438272607 False 186.169.253.58 surbl.org 1624438272607 False 186.169.253.58 spamhaus.org 1624438273058 False 186.169.253.58 spamhaus.org 1624438273058 False 186.169.253.58 spamhaus.org 1624438273059 False Is Attack Request \ 186.169.253.58 h.surbl.org 1 186.169.253.58 f.surbl.org 1 186.169.253.58 118.141.11.106.sbl.spamhaus.org 18 186.169.253.58 118.141.11.106.zen.spamhaus.org 18 186.169.253.58 128.141.11.106.sbl.spamhaus.org 18 Len(request) withou TLD Subdomains_count w_count w_max \ 186.169.253.58 1 0 0 -0.000000 186.169.253.58 1 0 0 -0.000000 186.169.253.58 5 0 0 2.633731 186.169.253.58 5 1 3 2.633731 186.169.253.58 5 0 0 2.863826 entropy w_max_ratio w_count_ratio digits_ratio \ 186.169.253.58 0.000000 0.000000 0.000000 0.0 186.169.253.58 0.000000 0.000000 0.000000 0.0 186.169.253.58 0.000000 0.000000 0.611111 0.0 186.169.253.58 0.166667 0.055556 0.611111 0.0 186.169.253.58 0.000000 0.000000 0.611111 0.0 uppercase_ratio time_avg time_stdev size_avg \ 186.169.253.58 3.444444 9.593111 1.5 1.581139 186.169.253.58 0.222222 0.440959 1.0 0.000000 186.169.253.58 55.555556 165.542375 17.2 0.421637 186.169.253.58 0.333333 0.500000 17.2 0.421637 186.169.253.58 0.333333 0.500000 17.3 0.483046 size stdev throughput unique entropy_avgentropy_stdev 186.169.253.58 468.750000 0.444444 0.258496 0.817437 186.169.253.58 3333.333333 0.555556 0.000000 0.000000 186.169.253.58 343.313373 0.000000 3.048277 0.177285 186.169.253.58 43000.000000 0.000000 2.983547 0.199622 186.169.253.58 43250.000000 0.000000 2.959741 0.198131
问题根源
- 原文件非标准逗号分隔CSV：实际是空格分隔的表格，且每行开头包含索引列（0、1、2等），但代码用逗号拼接表头，分隔符不匹配导致错位。
- Header列表语法错误：
'entropy_avg'和'entropy_stdev'之间缺少逗号，被合并为一个字段，表头列数比数据列少1，加剧错位。 - 大文件内存处理不当：一次性读取3500万行到内存，极易引发内存溢出。
解决方案
方案1：流式处理（内存友好，适合超大型文件）
逐行读取原文件，跳过索引列，转换为逗号分隔格式后写入新文件：
# 修正表头：补全逗号，修正拼写错误 header = [ 'User IP','Top-level domain', 'Timestamp', 'Is Attack', 'Request', 'Len(request) without TLD', 'Subdomains_count', 'w_count', 'w_max', 'entropy', 'w_max_ratio', 'w_count_ratio', 'digits_ratio', 'uppercase_ratio', 'time_avg', 'time_stdev', 'size_avg', 'size stdev', 'throughput', 'unique', 'entropy_avg', 'entropy_stdev' ] # 流式读写，避免内存占用过高 with open('E:\\Dataset\\dataset1.csv', 'r') as infile, open('E:\\Dataset\\dataset2.csv', 'w', newline='') as outfile: # 写入表头 outfile.write(','.join(header) + '\n') for line in infile: stripped_line = line.strip() # 跳过空行 if not stripped_line: continue # 分割行内容，跳过第一个元素（索引） parts = stripped_line.split() if len(parts) > 1: # 用逗号连接数据部分并写入 cleaned_line = ','.join(parts[1:]) outfile.write(cleaned_line + '\n')
方案2：Pandas快速处理（适合内存充足的场景）
若机器内存足够（建议16G以上），用Pandas直接处理索引列并添加表头：
import pandas as pd # 修正后的表头 header = [ 'User IP','Top-level domain', 'Timestamp', 'Is Attack', 'Request', 'Len(request) without TLD', 'Subdomains_count', 'w_count', 'w_max', 'entropy', 'w_max_ratio', 'w_count_ratio', 'digits_ratio', 'uppercase_ratio', 'time_avg', 'time_stdev', 'size_avg', 'size stdev', 'throughput', 'unique', 'entropy_avg', 'entropy_stdev' ] # 读取文件，指定第一列为索引并自动丢弃 df = pd.read_csv('E:\\Dataset\\dataset1.csv', sep='\s+', header=None, index_col=0) # 设置表头 df.columns = header # 写入新CSV，不保留索引 df.to_csv('E:\\Dataset\\dataset2.csv', index=False)
关键说明
- 原表头中的
Len(request) withou TLD拼写错误，已修正为Len(request) without TLD，可根据实际需求调整。 - 流式处理方案无需加载全量数据，适合3500万行这类超大型文件，内存占用极低。
- Pandas方案代码更简洁，但对内存要求较高，需确保机器有足够内存容纳全量数据。
内容的提问来源于stack exchange，提问作者Eman Bany salameh
相关产品推荐
相关产品推荐
