使用pandas读取无表头CSV文件并保留第4列内空格的问题求解

现有无表头CSV格式数据文件，无法重新生成，需使用pandas读取，且第4列包含的空格要完整保留。

最初尝试的读取代码 #

df = pd.read_csv('datasource.csv', sep='\s+', engine='python')

数据源样例 #

202001YYYY 0101   S5387140320101                FUSELA-CANAL18 aaaa   20200116AIT1206000016           201252552588252525          01 01002  8558569641      0010 0001  00002RATTR00000        170.00 0000000        340.00 109200217378
202001YYYY 0101   S5517000290210                FUSELA-CONICAL SERE   20200120AIT1206000117           214782523363200113          01 01002  8558569642      0010 0001  00008RATTR00000       2912.82 0000000      23302.56 109200229739
202001YYYY 0101   S5517000290210                FUSELA-CONICAL AZER   20200120AIT1206000118           296385241152200113          01 01002  8558569643      0010 0001  00003RATTR00000       2912.82 0000000       8738.46 109200233248
202001YYYY 0101   S5387140320101                FUSELAA               20200117AIT1206000020           201258963255558588          01 01002  8558569644      0010 0001  00003RATTR00000        170.00 0000000        510.00 109200217378
202001YYYY 0101   S5337500122202                FUSELA TYHNN          20200120AIT1206000119           201478933663636363          01 01002  8558569645      0010 0001  00003RATTR00000        814.41 0000000       2443.23 109200218022
202001YYYY 0101   S5337500122202                FUSELA RTGYU          20200120AIT1206000120           278965412333333625          01 01002  8558569646      0010 0001  00004RATTR00000        814.41 0000000       3257.64 109200222120
202001YYYY 0101   S5367140300400                FUSELAEE SERVICE DOOR 20200120AIT1206000129           202010048420200113          01 01002  9200235812      0010 0001  00001EAUSD00000       1700.00 0000000       1700.00 109200235812
202001YYYY 0101   S5517200000852                FUSELATYHJJ EDGE ASSY-LH20200117AIT1206000076         202010022320200110          01 01002  9200222563      0010 0001  00001EAUSD00000      10474.90 0000000      10474.90 109200222563

期望输出格式 #

name1      name2  name3           name4                 name5                  name6               name7 name9  name10          name11 name12 name13                name14 name15         name16 name17
202001YYYY 0101   S5387140320101  FUSELA-CANAL18 aaaa   20200116AIT1206000016  201252552588252525  01    01002  8558569641      0010   0001  00002RATTR00000        170.00 0000000        340.00 109200217378
202001YYYY 0101   S5517000290210  FUSELA-CONICAL SERE   20200120AIT1206000117  214782523363200113  01    01002  8558569642      0010   0001  00008RATTR00000       2912.82 0000000      23302.56 109200229739
202001YYYY 0101   S5517000290210  FUSELA-CONICAL AZER   20200120AIT1206000118  296385241152200113  01    01002  8558569643      0010   0001  00003RATTR00000       2912.82 0000000       8738.46 109200233248
202001YYYY 0101   S5387140320101  FUSELAA               20200117AIT1206000020  201258963255558588  01    01002  8558569644      0010   0001  00003RATTR00000        170.00 0000000        510.00 109200217378
202001YYYY 0101   S5337500122202  FUSELA TYHNN          20200120AIT1206000119  201478933663636363  01    01002  8558569645      0010   0001  00003RATTR00000        814.41 0000000       2443.23 109200218022
202001YYYY 0101   S5337500122202  FUSELA RTGYU          20200120AIT1206000120  278965412333333625  01    01002  8558569646      0010   0001  00004RATTR00000        814.41 0000000       3257.64 109200222120
202001YYYY 0101   S5367140300400  FUSELAEE SERVICE DOOR 20200120AIT1206000129  202010048420200113  01    01002  9200235812      0010   0001  00001EAUSD00000       1700.00 0000000       1700.00 109200235812
202001YYYY 0101   S5517200000852  FUSELATYHJJ EDGE ASSY-LH20200117AIT12060000  202010022320200110  01    01002  9200222563      0010   0001  00001EAUSD00000      10474.90 0000000      10474.90 109200222563

原有尝试代码 #

import re
import pandas as pd

data = {
  'c1': [],
  'c2': [],
  'c3': [],
  'c4': [],
  'c5': [],
  'c6': [],
  'c7': [],
  'c8': [],
  'c9': [],
  'c10': [],
  'c11': [],
  'c12': [],
  'c13': [],
  'c14': [],
  'c15': [],
  'c16': [],
  'c17':[],
}
with open('datasource.csv', 'r') as f:
    for line in f:
        c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17= re.split('\s+', line.strip())
        data['c1'].append(c1)
        data['c2'].append(c2)
        data['c3'].append(c3)
        data['c4'].append(' '.join(c4))
        data['c5'].append(c5)
        data['c6'].append(c6)
        data['c7'].append(c7)
        data['c8'].append(c8)
        data['c9'].append(c9)
        data['c10'].append(c10)
        data['c11'].append(c11)
        data['c12'].append(c12)
        data['c13'].append(c13)
        data['c14'].append(c14)
        data['c15'].append(c15)
        data['c16'].append(c16)
        data['c17'].append(c17)

df = pd.DataFrame.from_dict(data)
df

该文件本质是固定宽度格式文件，并非普通空白字符分隔的CSV。原代码使用\s+作为分隔符拆分时，会把第4列内部的空格也识别为列分隔符，导致拆分后的字段总数超过17，触发变量解包报错。同时' '.join(c4)的写法错误，拆分后的c4是单个字符串，执行join会将字符串按字符拆分后拼接，结果不符合预期。

方案1：使用pandas内置read_fwf读取（推荐） #

pandas的read_fwf专门用于读取固定宽度格式文件，无需手动逐行处理：

import pandas as pd

# 根据样例的列对齐情况手动指定每列宽度
col_widths = [10, 5, 17, 24, 26, 22, 3, 6, 12, 5, 5, 20, 9, 8, 11, 13]
# 读取无表头的固定宽度文件，自定义列名
df = pd.read_fwf('datasource.csv', widths=col_widths, header=None)
df.columns = [f'c{i+1}' for i in range(17)]

方案2：修正正则逐行匹配逻辑 #

如果要保留逐行处理的写法，改用正则分组捕获的方式提取字段：

import re
import pandas as pd

# 正则分组规则：前3列匹配非空白字符，第4列匹配任意字符（含内部空格），后13列匹配非空白字符
pattern = re.compile(r'^(\S+)\s+(\S+)\s+(\S+)\s+(.+?)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)$')
data = {f'c{i+1}': [] for i in range(17)}

with open('datasource.csv', 'r', encoding='utf-8') as f:
    for line in f:
        line = line.rstrip('\n')
        match_res = pattern.match(line)
        if not match_res:
            continue
        groups = match_res.groups()
        for idx in range(17):
            # 第4列保留原始空格，其余列去除首尾多余空白
            val = groups[idx] if idx == 3 else groups[idx].strip()
            data[f'c{idx+1}'].append(val)

df = pd.DataFrame(data)

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