pyspark - Pyspark：如何用数组中的值替换每一行的值

Question

我将用 monthList 数组中的值更改日期列中的数字。

月列表数组

monthList = ["None","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

pyspark 代码

d = df.select(col('InvoiceDate'),col('TotalSales')/1000000).groupBy(month('InvoiceDate')).sum()
d = d.select(col('month(InvoiceDate)').alias('date'),col('sum((TotalSales / 1000000))').alias('value')).orderBy('date')
d = d.select(col('date'),round(col('value'),2).alias('value'))
d.show()

结果

+----+-----+
|date|value|
+----+-----+
|   1|19.75|
|   2|15.51|
|   3|20.66|
+----+-----+

我会试试这个，但它不起作用。是错误'DataFrame' object has no attribute 'apply'

d.date = d.select('date').apply(lambda x: monthList[x]) 

谢谢你的帮助。

标签： pysparkapache-spark-sql

Answer 1

一种方法是date_lookup使用monthlist. 这个df可以broadcasted用于性能。然后你可以用实际的df执行左连接。

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

 spark = SparkSession.builder \
.appName('practice')\
.getOrCreate()

 sc= spark.sparkContext

monthList = ["None","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

lookup_list = []

for i in range(len(monthList)):
   lookup_list.append((i,monthList[i]))


date_lookup = sc.parallelize(lookup_list).toDF(["date_num", "date_label"])

date_lookup.show()

 +--------+----------+
 |date_num|date_label|
 +--------+----------+
 |       0|      None|
 |       1|       Jan|
 |       2|       Feb|
 |       3|       Mar|
 |       4|       Apr|
 |       5|       May|
 |       6|       Jun|
 |       7|       Jul|
 |       8|       Aug|
 |       9|       Sep|
 |      10|       Oct|
 |      11|       Nov|
 |      12|       Dec|
 +--------+----------+

 df= sc.parallelize([
 (1,19.75), (2,15.51)]).toDF(["date", "value"])

 +----+-----+
 |date|value|
 +----+-----+
 |   1|19.75|
 |   2|15.51|
 +----+-----+

 df1  = df.join(F.broadcast(date_lookup),df.date==date_lookup.date_num, how='left').select('date_label','value')

 df1.show()

 +----------+-----+
 |date_label|value|
 +----------+-----+
 |       Jan|19.75|
 |       Feb|15.51|
 +----------+-----+