从自定义生成数据的URL读取到Pandas DataFrame遇解析错误求助

此前用类似代码读取NOAA固定数据URL可正常运行：

bst = pd.read_csv('https://psl.noaa.gov/data/correlation/censo.data', skiprows=1, 
skipfooter=2,index_col=[0], header=None,
             engine='python', # c engine doesn't have skipfooter
             delim_whitespace=True)

但读取自定义生成的时间序列URL时失败：

zwnd = pd.read_csv('https://psl.noaa.gov/cgi-bin/data/timeseries/timeseries.pl?ntype=1&var=Zonal+Wind&level=1000&lat1=50&lat2=25&lon1=-135&lon2=-65&iseas=0&mon1=0&mon2=0&iarea=0&typeout=1&Submit=Create+Timeseries', skiprows=1, skipfooter=2,index_col=[0], header=None,
                 engine='python', # c engine doesn't have skipfooter
                 delim_whitespace=True)

完整报错信息：

pd.read_csv('https://psl.noaa.gov/cgi-bin/data/timeseries/timeseries.pl?ntype=1&var=Zonal+Wind&level=1000&lat1=50&lat2=25&lon1=-135&lon2=-65&iseas=0&mon1=0&mon2=0&iarea=0&typeout=1&Submit=Create+Timeseries', skiprows=1, skipfooter=2,index_col=[0], header=None,
                 engine='python', # c engine doesn't have skipfooter
                 delim_whitespace=True)
Traceback (most recent call last):

  Cell In[240], line 1
    pd.read_csv('https://psl.noaa.gov/cgi-bin/data/timeseries/timeseries.pl?ntype=1&var=Zonal+Wind&level=1000&lat1=50&lat2=25&lon1=-135&lon2=-65&iseas=0&mon1=0&mon2=0&iarea=0&typeout=1&Submit=Create+Timeseries', skiprows=1, skipfooter=2,index_col=[0], header=None,

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\util\_decorators.py:211 in wrapper
    return func(*args, **kwargs)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\util\_decorators.py:331 in wrapper
    return func(*args, **kwargs)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\readers.py:950 in read_csv
    return _read(filepath_or_buffer, kwds)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\readers.py:611 in _read
    return parser.read(nrows)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\readers.py:1778 in read
    ) = self._engine.read(  # type: ignore[attr-defined]

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\python_parser.py:282 in read
    alldata = self._rows_to_cols(content)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\python_parser.py:1045 in _rows_to_cols
    self._alert_malformed(msg, row_num + 1)

  File ~\Anaconda3\envs\Stats\lib\site-packages\pandas\io\parsers\python_parser.py:765 in _alert_malformed
    raise ParserError(msg)

ParserError: Expected 2 fields in line 133, saw 3. Error could possibly be due to quotes being ignored when a multi-char delimiter is used.

解决方法 #

1. 先排查异常行原因 #

直接访问目标URL，查看第133行内容，通常是该行存在额外空格、注释或格式异常，导致按空格拆分后字段数不符。

2. 调整读取参数 #

方案一：跳过错误行

临时跳过格式异常的行，优先加载正常数据：

zwnd = pd.read_csv(
    'https://psl.noaa.gov/cgi-bin/data/timeseries/timeseries.pl?ntype=1&var=Zonal+Wind&level=1000&lat1=50&lat2=25&lon1=-135&lon2=-65&iseas=0&mon1=0&mon2=0&iarea=0&typeout=1&Submit=Create+Timeseries',
    skiprows=1,
    skipfooter=2,
    index_col=[0],
    header=None,
    engine='python',
    delim_whitespace=True,
    on_bad_lines='skip'
)

方案二：手动预处理数据

先获取原始文本，清理异常行后再加载，确保数据完整性：

import requests
import pandas as pd
from io import StringIO

url = 'https://psl.noaa.gov/cgi-bin/data/timeseries/timeseries.pl?ntype=1&var=Zonal+Wind&level=1000&lat1=50&lat2=25&lon1=-135&lon2=-65&iseas=0&mon1=0&mon2=0&iarea=0&typeout=1&Submit=Create+Timeseries'
response = requests.get(url)
lines = response.text.splitlines()

# 跳过开头1行、结尾2行，过滤字段数不为2的行
cleaned_lines = []
for line in lines[1:-2]:
    parts = line.strip().split()
    if len(parts) == 2:
        cleaned_lines.append('\t'.join(parts))

# 从清理后的文本加载DataFrame
zwnd = pd.read_csv(StringIO('\n'.join(cleaned_lines)), sep='\t', index_col=0, header=None)

方案三：优化分隔符与注释处理

如果异常行是注释，可通过指定注释符号跳过；同时用sep='\s+'替代delim_whitespace=True，提升分隔稳定性：

zwnd = pd.read_csv(
    url,
    skiprows=1,
    skipfooter=2,
    index_col=[0],
    header=None,
    engine='python',
    sep='\s+',
    comment='#'
)

3. 验证数据 #

加载完成后，用zwnd.head()、zwnd.tail()检查数据格式是否正常，确认关键信息未丢失。

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