如何使用Python/Spark读取含多个空格分隔的文本文件

Hey there! Let's work through this whitespace-separated data issue step by step.

First, let's break down the core problem: your dataset uses multiple consecutive spaces as field separators, but some fields (like description) contain single spaces as part of their content. Plus, you ran into a gotcha with the io.StringIO object—after your first pd.read_csv() call, the cursor moves to the end of the buffer, so the second call reads nothing!

Fixing the Pandas Approach #

First, let's fix the cursor issue and then correctly parse the data with proper separator handling:

import pandas as pd
import io

data_str = """value code description version plant ing_code weight created end
NULL (Nxt) Optigroei PreStart Kruimel 170 Wan 2132.00 19.800000000010389 2020-07-27 14:15:55.000 2020-07-27 14:15:55.000
NULL (Nxt) Optigroei PreStart Kruimel 170 Wan 1808.00 0.55000000000025961 2020-07-27 14:15:55.000 2020-07-27 14:15:55.000
NULL (Nxt) Optigroei PreStart Kruimel 170 Wan 1672.00 5.9999999999999998E-2 2020-07-27 14:15:55.000 2020-07-27 14:15:55.000
NULL (Nxt) Optigroei 3 Meel 370 Wan 1253.00 1.21 2020-07-29 06:30:10.000 2020-07-29 06:30:10.000
NULL (Nxt) Optigroei 3 Meel 370 Wan 1582.00 9.9000000000061018E-3 2020-07-29 06:30:10.000 2020-07-29 06:30:10.000
NULL (Nxt) Optigroei Start Meel 570 Wan 2132.00 24.800000000000001 2020-07-29 06:32:38.000 2020-07-29 06:32:38.000"""
data_io = io.StringIO(data_str)

# Reset the buffer cursor to the start (critical for multiple read attempts!)
data_io.seek(0)

# Use regex to match 2+ consecutive spaces as separators, and use Python engine (C engine doesn't support regex separators)
df_pd = pd.read_csv(data_io, sep=r'\s{2,}', engine='python')

print(df_pd)

Why this works:

• r'\s{2,}' specifically matches 2 or more whitespace characters, so single spaces inside fields like Optigroei PreStart Kruimel are preserved instead of being treated as separators.
• engine='python' is required because Pandas' default C engine doesn't support regular expressions as separators.
• data_io.seek(0) resets the buffer's cursor to the beginning, so your second read_csv call actually reads data instead of an empty buffer.

If your data follows strict fixed-width formatting, you could also use pd.read_fwf() (fixed-width format) by defining column positions, but the regex separator method is more flexible for this case.

Spark Solution #

If you need to handle this with Spark, here's two reliable approaches:

1. Using Regex Splitting

from pyspark.sql import SparkSession
from pyspark.sql.functions import regexp_split, col

spark = SparkSession.builder.appName("WhitespaceDataReader").getOrCreate()

# Read raw text lines
df_raw = spark.read.text("your_file.txt")

# Extract header and define columns
header_line = df_raw.first()[0]
columns = [col_name.strip() for col_name in header_line.split()]

# Split data lines with 2+ spaces, then map to columns
df_spark = df_raw.filter(df_raw.value != header_line) \
    .select(regexp_split(col("value"), r"\s{2,}").alias("split_data")) \
    .select([col("split_data")[i].alias(columns[i]) for i in range(len(columns))])

df_spark.show(truncate=False)

2. Using Spark's CSV Reader with Regex Separator

df_spark = spark.read.option("header", "true") \
    .option("sep", r"\s{2,}") \
    .option("inferSchema", "true") \
    .csv("your_file.txt")

df_spark.show(truncate=False)

This leverages Spark's CSV reader but tells it to use 2+ spaces as the separator, similar to the Pandas approach.

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