如何读取无分隔符、宽度不规则的文本文件并清洗为DataFrame

这种文件属于带层级关联的固定宽度文本，不适合用普通分隔符读取，推荐用「行分类+关联填充」的思路处理，以下分别提供Python和R的可运行方案：

Python实现（pandas+正则） #

思路是逐行读取后用正则匹配区分物料行和投标人行，动态关联后转DataFrame：

import re
import pandas as pd

# 定义正则规则
# 物料行匹配规则：分组对应[物料编码, 物料描述, 数量, 单位]
item_pat = re.compile(r'^\s{4,}(X\d+)\s{2,}(.*?)\s{2,}(\d+\.?\d*)\s{2,}(.*)$')
# 投标人行匹配规则：分组对应[投标人ID, 投标人名称, 单价, 投标人总价, 计算总价]
bid_pat = re.compile(r'^\s{0,3}(\d+)\s{2,}(.*?)\s{2,}([\d,]+\.?\d*)\s{2,}([\d,]+\.?\d*)\s{2,}([\d,]+\.?\d*)$')

records = []
current_item = None

# 批量读取所有文件的话，外层加遍历文件路径的逻辑即可
with open('你的文件路径.txt', 'r', encoding='utf-8') as f:
    lines = f.readlines()
    # 跳过前3行表头+空行，过滤空白行
    for line in lines[3:]:
        line = line.strip('\n')
        if not line.strip():
            continue
        # 匹配物料行，更新当前物料信息
        item_match = item_pat.match(line)
        if item_match:
            current_item = {
                'ITEM_NBR': item_match.group(1),
                'ITEM_DESC': item_match.group(2).strip(),
                'QUANTITY': item_match.group(3),
                'UNIT_MEASURE': item_match.group(4).strip()
            }
            continue
        # 匹配投标人行，和当前物料拼接成记录
        bid_match = bid_pat.match(line)
        if bid_match and current_item:
            record = current_item.copy()
            record.update({
                'BIDR_NBR': bid_match.group(1),
                'BIDDER_NAME': bid_match.group(2).strip(),
                'UNIT_PRICE': bid_match.group(3),
                'BIDDER_EXT': bid_match.group(4),
                'CALCULATED_EXT': bid_match.group(5)
            })
            records.append(record)

# 转DataFrame，可后续做数据类型转换（比如去掉千分符转数值）
df = pd.DataFrame(records)
print(df.head())

R实现（tidyverse家族） #

逻辑和Python一致，用正则分组提取字段后关联：

library(tidyverse)

# 读取文件，跳过前3行
lines <- readLines("你的文件路径.txt", warn = F) %>% 
  tail(-3) %>% 
  str_trim(side = "right") %>% 
  .[str_length(.) > 0]

# 区分物料行和投标人行，填充关联
df <- tibble(line = lines) %>% 
  mutate(
    # 标记行类型
    line_type = ifelse(str_detect(line, "^\\s{4,}X\\d+"), "item", "bid"),
    # 提取物料字段
    ITEM_NBR = ifelse(line_type == "item", str_extract(line, "^\\s{4,}(X\\d+)", group = 1), NA_character_),
    ITEM_DESC = ifelse(line_type == "item", str_extract(line, "^\\s{4,}X\\d+\\s{2,}(.*?)\\s{2,}\\d+\\.?\\d*", group = 1) %>% str_squish(), NA_character_),
    QUANTITY = ifelse(line_type == "item", str_extract(line, "(\\d+\\.?\\d*)\\s{2,}[A-Z ]+$", group = 1), NA_character_),
    UNIT_MEASURE = ifelse(line_type == "item", str_extract(line, "\\d+\\.?\\d*\\s{2,}(.*)$", group = 1) %>% str_squish(), NA_character_)
  ) %>% 
  # 向下填充物料信息到所有投标人行
  fill(ITEM_NBR, ITEM_DESC, QUANTITY, UNIT_MEASURE, .direction = "down") %>% 
  # 仅保留投标人行，提取投标人字段
  filter(line_type == "bid") %>% 
  mutate(
    BIDR_NBR = str_extract(line, "^\\s{0,3}(\\d+)", group = 1),
    BIDDER_NAME = str_extract(line, "^\\s{0,3}\\d+\\s{2,}(.*?)\\s{2,}[\\d,]+\\.?\\d*", group = 1) %>% str_squish(),
    UNIT_PRICE = str_extract(line, "([\\d,]+\\.?\\d*)\\s{2,}[\\d,]+\\.?\\d*\\s{2,}[\\d,]+\\.?\\d*$", group = 1),
    BIDDER_EXT = str_extract(line, "[\\d,]+\\.?\\d*\\s{2,}([\\d,]+\\.?\\d*)\\s{2,}[\\d,]+\\.?\\d*$", group = 1),
    CALCULATED_EXT = str_extract(line, "[\\d,]+\\.?\\d*$", group = 1)
  ) %>% 
  select(-line, -line_type)

head(df)

后续优化提示 #

• 批量处理多文件只需要在外层加路径遍历逻辑，把所有文件的记录合并即可
• 数值类字段可以额外处理千分符后转为数值格式，方便后续计算

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