基于正则匹配在PySpark或Koalas中拆分混合数据类型的字符串列

Got it, let's fix this problem once and for all. The core issue here is distinguishing between two distinct formats in the duration column: HH:MM:SS timestamps and numeric KB values. Your earlier split attempts weren't the right approach because we don't need to split a single string—we need to categorize each row and populate the new columns accordingly.

Koalas Implementation #

Since you started with Koalas, let's go with that first. We'll use conditional logic to populate call_duration and data based on the format of each duration value:

import databricks.koalas as ks

# Assume your data is already loaded into a Koalas DataFrame called ks_preprocess_df
# Step 1: Populate call_duration - keep values that match HH:MM:SS, else null
ks_preprocess_df['call_duration'] = ks_preprocess_df['duration'].where(
    ks_preprocess_df['duration'].str.match(r'^\d{2}:\d{2}:\d{2}$'),
    None
)

# Step 2: Populate data - convert numeric strings to integers, else 0
ks_preprocess_df['data'] = ks_preprocess_df['duration'].apply(
    lambda x: int(x) if x.isdigit() else 0
)

# Step 3: Clean up - drop original duration column and reorder columns to match your desired output
final_df = ks_preprocess_df.drop('duration', axis=1)[['data', 'call_duration', 'Class']]

Why This Works: #

• The where method keeps the original duration value only if it matches the HH:MM:SS regex pattern (^\d{2}:\d{2}:\d{2}$), otherwise it sets the value to None.
• The apply check uses isdigit() to identify numeric strings, converting them to integers; non-numeric values get set to 0 as per your expected output.

Spark Alternative #

If you want to switch to full Spark for even better memory efficiency, here's the equivalent implementation using Spark SQL functions:

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

# Initialize Spark session
spark = SparkSession.builder.appName("DurationSplit").getOrCreate()

# Assume your data is loaded into a Spark DataFrame called spark_preprocess_df
# Step 1: Add call_duration column
spark_preprocess_df = spark_preprocess_df.withColumn(
    "call_duration",
    when(regexp_match(col("duration"), r'^\d{2}:\d{2}:\d{2}$'), col("duration")).otherwise(None)
)

# Step 2: Add data column - try casting to int; if that fails, set to 0
spark_preprocess_df = spark_preprocess_df.withColumn(
    "data",
    when(col("duration").cast("int").isNotNull(), col("duration").cast("int")).otherwise(0)
)

# Step 3: Reorder columns and drop original duration
final_spark_df = spark_preprocess_df.drop("duration").select("data", "call_duration", "Class")

# View results
final_spark_df.show()

Key Notes: #

• Spark's regexp_match works similarly to Koalas' str.match to identify timestamp strings.
• Using cast("int").isNotNull() is a robust way to check if the string is a valid number (better than checking digits alone, since it handles edge cases like leading zeros if needed).

Both implementations will produce exactly the DataFrame structure you requested, with minimal memory overhead compared to Pandas—perfect for large datasets.

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