PySpark：如何替代fillna实现字符串/正则匹配式值替换？

Got it, let's solve this problem where you need to standardize values in your location column (like US, New York, USA, United States) to a single value United States—no need to handle NA values, just pattern-based replacement. Here are two practical, easy-to-implement approaches:

Approach 1: Use when() with rlike (Flexible Regex Matching) #

This is the most straightforward method for your use case. It lets you define regex patterns to target any variation of your desired values, then replace them uniformly. The rlike function supports regular expressions, so you can handle case insensitivity and partial matches effortlessly.

Example Code: #

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

# Set up your Spark session
spark = SparkSession.builder.appName("LocationStandardization").getOrCreate()

# Sample input data to test with
sample_data = [
    ("United States",),
    ("US",),
    ("New York, USA",),
    ("California, US",),
    ("Canada",),
    ("London, UK",)
]
df = spark.createDataFrame(sample_data, ["location"])

# Standardize the location column
cleaned_df = df.withColumn(
    "standardized_location",
    # Match any string containing "US", "USA", or "United States" (case-insensitive)
    when(col("location").rlike(r"(?i)us|united states"), "United States")
    # Keep original values for entries that don't match the pattern
    .otherwise(col("location"))
)

# Check the result
cleaned_df.show(truncate=False)

Quick Tips: #

• The (?i) flag makes the regex case-insensitive, so it will catch us, US, Us, USA, United states, etc.
• If you want to avoid matching words where "US" is a substring (like USSR), add word boundaries to your regex: r"(?i)\bus\b|united states"—the \b ensures "US" is treated as a standalone word.

Approach 2: Use regexp_replace (For Partial/Full String Replacement) #

While the when() method is more direct for your needs, regexp_replace is useful if you ever need to tweak partial matches. For your current goal (replacing the entire matching string with United States), here's how it works:

Example Code: #

from pyspark.sql.functions import regexp_replace

# Replace any string matching the pattern with "United States"
cleaned_df = df.withColumn(
    "standardized_location",
    regexp_replace(
        col("location"),
        r"(?i).*(us|united states).*",
        "United States"
    )
)

cleaned_df.show(truncate=False)

Key Note: #

• The regex (?i).*(us|united states).* matches any string that contains "US" or "United States" (case-insensitive), no matter what text comes before or after it. This replaces the entire string with United States if the pattern is found—just like the when() method.

Expected Output #

Either approach will give you a DataFrame where all matching location entries are standardized, while non-matching values stay the same:

+----------------+---------------------+
|location        |standardized_location|
+----------------+---------------------+
|United States   |United States        |
|US              |United States        |
|New York, USA   |United States        |
|California, US  |United States        |
|Canada          |Canada               |
|London, UK      |London, UK           |
+----------------+---------------------+

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