如何在PySpark DataFrame中拆分含多空格的value字段

我有一个PySpark DataFrame，包含字段value，数据示例如下：

+----------------------------------------------------------------------------------------------------------------------------+
|value                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  ADDR    Fargo-Wahpeton, ND-MN   61.30   98.52   433.23                   |
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  STATE   TX  43.38   74.61   380.82                                       |
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  ADDR    Kalamazoo-Battle Creek-Portage, MI  30.19   49.06   266.33       |
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  STATE   TN  11.87   19.92   946.73                                       |
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  ADDR    New York-Newark, NY-NJ-CT-PA    32.24   55.95   322.16           |
|2023-03-05 MONTH   2020M03 2020-03-01  2020-03-31  LOCATION    New England 22.277  42.56   202.76                           |
+----------------------------------------------------------------------------------------------------------------------------+

需要将value字段拆分为以下结构化字段：

+-----------+----------+-----------+--------------+-------------+-------------------+---------------------------------------+---------+--------------+------------------+
|insert_dt  |sub_type  |ret_month  |month_start   |month_end    |area_class         |area_details                           |sub_paid |sub_pending   |sub_annual_amt    |
+-----------+----------+-----------+--------------+-------------+-------------------+---------------------------------------+---------+--------------+------------------+
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |addr               |Fargo-Wahpeton, ND-MN                  |61.30    |98.52         |433.23            |
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |state              |TX                                     |43.38    |74.61         |380.82            |
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |addr               |Kalamazoo-Battle Creek-Portage, MI     |30.19    |49.06         |266.33            |
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |state              |TN                                     |11.87    |19.92         |946.73            |
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |addr               |New York-Newark, NY-NJ-CT-PA           |32.24    |55.95         |322.16            |
|2023-03-05 |MONTH     |2020M03    |2020-03-01    |2020-03-31   |location           |New England                            |22.27    |42.56         |202.76            |
+-----------+----------|-----------+--------------+-------------+-------------------+---------------------------------------+---------+--------------+------------------+

我尝试用regex_extract但失败了，代码如下：

regex_pattern = 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+)$'

df.select(regexp_extract('value', regex_pattern, 1).alias('insert_dt'),
          regexp_extract('value', regex_pattern, 2).alias('sub_type'),
          regexp_extract('value', regex_pattern, 3).alias('ret_month'),
          regexp_extract('value', regex_pattern, 4).alias('month_start'),
          regexp_extract('value', regex_pattern, 5).alias('month_end'),
          regexp_extract('value', regex_pattern, 6).alias('area_class'),
          regexp_extract('value', regex_pattern, 7).alias('area_details'),
          regexp_extract('value', regex_pattern, 8).alias('sub_paid'),
          regexp_extract('value', regex_pattern, 9).alias('sub_pending'),
          regexp_extract('value', regex_pattern, 10).alias('sub_annual_amt')
          ).display()

之前的正则表达式问题在于：

• 错误匹配了过多的\S+，导致字段结构混乱
• 未正确处理area_details包含空格的情况，也没确保最后三个数值字段是连续的非空字符串

正确正则表达式 #

^(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.*?)\s+(\S+)\s+(\S+)\s+(\S+)$

解释：

1. ^(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+：匹配前6个无空格字段（insert_dt到area_class）
2. (.*?)\s+：使用非贪婪模式匹配area_details，确保不会包含后面的数值字段
3. (\S+)\s+(\S+)\s+(\S+)$：匹配最后三个数值字段（sub_paid、sub_pending、sub_annual_amt）

对应PySpark代码 #

from pyspark.sql.functions import regexp_extract, round, col, lower

# 定义正则模式
regex_pattern = r'^(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.*?)\s+(\S+)\s+(\S+)\s+(\S+)$'

# 提取并转换字段
result_df = df.select(
    regexp_extract('value', regex_pattern, 1).alias('insert_dt'),
    regexp_extract('value', regex_pattern, 2).alias('sub_type'),
    regexp_extract('value', regex_pattern, 3).alias('ret_month'),
    regexp_extract('value', regex_pattern, 4).alias('month_start'),
    regexp_extract('value', regex_pattern, 5).alias('month_end'),
    # 转为小写匹配目标格式
    lower(regexp_extract('value', regex_pattern, 6)).alias('area_class'),
    regexp_extract('value', regex_pattern, 7).alias('area_details'),
    # 处理小数位数，如22.277转为22.27
    round(regexp_extract('value', regex_pattern, 8).cast('double'), 2).alias('sub_paid'),
    regexp_extract('value', regex_pattern, 9).cast('double').alias('sub_pending'),
    regexp_extract('value', regex_pattern, 10).cast('double').alias('sub_annual_amt')
)

result_df.display()

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