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 pddf = 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 pd3 
----> 4 df = pd.read_csv('../Datasets/CSV数据行的列数大于表头字段个数/2024_06_05_21.csv', index_col=0)5 6 df.shaped:\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 wrapperd:\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 chunks226                 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 pddf = pd.read_csv('../Datasets/CSV数据行的列数大于表头字段个数/modified_file.csv', index_col=0)

df.shape

(6428, 471)