Scala/Spark中按品牌汇总价格的数据转换问题求助

Hey there! Let's tackle this problem step by step—you're already halfway there by loading the data into a DataFrame and creating a temp view, so let's fix the cleaning issues and get your sums calculated.

Step 1: Clean the price Field (Remove Spaces & Replace Non-Numeric Values) #

The key here is to first strip any extra whitespace from the price values, then handle those non-numeric entries (like " - ") by replacing them with 0. You can do this either using PySpark DataFrame APIs or Spark SQL, depending on what you're more comfortable with.

Option 1: Using PySpark DataFrame Functions

from pyspark.sql.functions import trim, when, col, sum

# First, trim all leading/trailing spaces from the price column
df_clean = df1.withColumn("price_trimmed", trim(col("price")))

# Replace empty strings or "-" (after trimming) with 0, then cast to Double
df_clean = df_clean.withColumn(
    "price_clean",
    when(col("price_trimmed").isin("-", ""), 0.0)
    .otherwise(col("price_trimmed").cast("double"))
)

This handles cases like " - " (trimmed to "-"), " 29 " (trimmed to "29"), and even "-35" (which stays as "-35" and casts correctly to a negative Double).

Option 2: Using Spark SQL (Since You Created a Temp View)

If you prefer working with SQL, run this query against your temp view (let's assume you named it brand_price_data):

SELECT
    brand,
    SUM(
        CASE
            WHEN TRIM(price) IN ('-', '') THEN 0.0
            ELSE CAST(TRIM(price) AS DOUBLE)
        END
    ) AS total_price
FROM brand_price_data
GROUP BY brand;

Step 2: Calculate Brand-wise Sum #

Once the price field is cleaned, grouping and summing is straightforward.

With the cleaned DataFrame from Option 1, run:

result_df = df_clean.groupBy("brand").agg(sum("price_clean").alias("total_price"))
result_df.show()

Expected Output #

For your sample data, the result should look like this:

+-----+-----------+
|brand|total_price|
+-----+-----------+
|  abc|       74.23|
| bb-c|       -1.0 |
+-----+-----------+

(Calculations: abc has 29 + 45.23 = 74.23; bb-c has 34 + (-35) = -1)

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