CSV数据行(取值)的列数多于表头字段数-Pandas无法正常读取

CSV数据行(取值)的列数多于表头字段数-Pandas无法正常读取

问题描述：在使用Pandas正常读取csv文件时，报错提示“ ParserError: Error tokenizing data. C error: Expected 460 fields in line 3363, saw 472”。也就是数据行的值个数多于表头字段个数。处理过程记录如下，完整代码和测试数据可以从Github仓库Useful-Python-Scripts获取，也可以在jupyter nbviewer中在线浏览。

# 读取一个测试数据.   数据可以在Datasets文件夹获取.
import pandas as pd

df = pd.read_csv('../Datasets/CSV数据行的列数大于表头字段个数/2024_06_05_21.csv', index_col=0)

df.shape

---------------------------------------------------------------------------

ParserError                               Traceback (most recent call last)

<ipython-input-2-6be17fa04a2a> in <module>
      2 import pandas as pd
      3 
----> 4 df = pd.read_csv('../Datasets/CSV数据行的列数大于表头字段个数/2024_06_05_21.csv', index_col=0)
      5 
      6 df.shape


d:\installation\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
    309                     stacklevel=stacklevel,
    310                 )
--> 311             return func(*args, **kwargs)
    312 
    313         return wrapper


d:\installation\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, error_bad_lines, warn_bad_lines, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)
    584     kwds.update(kwds_defaults)
    585 
--> 586     return _read(filepath_or_buffer, kwds)
    587 
    588 


d:\installation\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py in _read(filepath_or_buffer, kwds)
    486 
    487     with parser:
--> 488         return parser.read(nrows)
    489 
    490 


d:\installation\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py in read(self, nrows)
   1045     def read(self, nrows=None):
   1046         nrows = validate_integer("nrows", nrows)
-> 1047         index, columns, col_dict = self._engine.read(nrows)
   1048 
   1049         if index is None:


d:\installation\Anaconda3\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py in read(self, nrows)
    222         try:
    223             if self.low_memory:
--> 224                 chunks = self._reader.read_low_memory(nrows)
    225                 # destructive to chunks
    226                 data = _concatenate_chunks(chunks)


d:\installation\Anaconda3\lib\site-packages\pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader.read_low_memory()


d:\installation\Anaconda3\lib\site-packages\pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._read_rows()


d:\installation\Anaconda3\lib\site-packages\pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._tokenize_rows()


d:\installation\Anaconda3\lib\site-packages\pandas\_libs\parsers.pyx in pandas._libs.parsers.raise_parser_error()


ParserError: Error tokenizing data. C error: Expected 460 fields in line 3363, saw 472

"""
报错表明在处理CSV文件时，第3363行的字段数量与预期的不符。预期应该有460个字段，但实际看到了472个值.
正常情况下，pandas无法按照标准的二维表进行读取.
"""

"""
一种解决方案是：可以使用Python内置的csv模块， CSV对象工具包逐行读取CSV文件.
"""

# 这是一个示例脚本.
import csv

# 指定要读取的CSV文件路径
csv_file_path = '../Datasets/CSV数据行的列数大于表头字段个数/2024_06_05_21.csv'

# 逐行读取CSV文件
with open(csv_file_path, mode='r', encoding='utf-8') as csvfile:
    csv_reader = csv.reader(csvfile)

    for line_number, row in enumerate(csv_reader, start=1):
        try:
            # 处理每一行数据
            print(f"行号 {line_number} 的数据: {row}")
            # 这里可以根据需要进行数据处理

        except Exception as e:
            print(f"第 {line_number} 行处理出错: {e}")

一种解决方案

import csv

# 指定输入和输出文件路径.
input_csv_file = '../Datasets/CSV数据行的列数大于表头字段个数/2024_06_05_21.csv'
output_csv_file = '../Datasets/CSV数据行的列数大于表头字段个数/modified_file.csv'

# 初始化最大列数.
max_columns = 0
rows = []

# 逐行读取CSV文件.
with open(input_csv_file, mode='r', encoding='utf-8') as csvfile:
    csv_reader = csv.reader(csvfile)
    
    # 获取表头.
    header = next(csv_reader)
    rows.append(header)
    
    # 遍历每一行.
    for row in csv_reader:
        rows.append(row)
        # 更新最大列数.
        max_columns = max(max_columns, len(row))  # 统计数据行的最大列数.

# 生成新的表头.
modified_header = header[:]  # 先复制一份原始表头.
while len(modified_header) < max_columns:  # 添加“Missing”直到最大列数.
    modified_header.append("Missing")

# 写入新的CSV文件.
with open(output_csv_file, mode='w', newline='', encoding='utf-8') as csvfile:
    csv_writer = csv.writer(csvfile)
    
    # 写入修改后的表头.
    csv_writer.writerow(modified_header)
    
    # 写入未修改的数据行（原样写入）.
    for row in rows[1:]:  # 跳过原始表头.
        csv_writer.writerow(row)

print("文件处理完成，已输出到", output_csv_file)

文件处理完成，已输出到 ../Datasets/CSV数据行的列数大于表头字段个数/modified_file.csv

# 再次读取测试,正常读取.
import pandas as pd

df = pd.read_csv('../Datasets/CSV数据行的列数大于表头字段个数/modified_file.csv', index_col=0)

df.shape

(6428, 471)