1 基础数据
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('JupyterPySpark').enableHiveSupport().getOrCreate()
import pyspark.sql.functions as F
# 原始数据
test = spark.createDataFrame([('2018-01','项目1',100), ('2018-01','项目2',200), ('2018-01','项目3',300),
('2018-02','项目1',1000), ('2018-02','项目2',2000), ('2018-03','项目4',999),
('2018-05','项目1',6000), ('2018-05','项目2',4000), ('2018-05','项目4',1999)
], ['月份','项目','收入'])
test.show()
+-------+-----+----+
| 月份| 项目|收入|
+-------+-----+----+
|2018-01|项目1| 100|
|2018-01|项目2| 200|
|2018-01|项目3| 300|
|2018-02|项目1|1000|
|2018-02|项目2|2000|
|2018-03|项目4| 999|
|2018-05|项目1|6000|
|2018-05|项目2|4000|
|2018-05|项目4|1999|
+-------+-----+----+
2 列转行
2.1 pivot 实现
按照不需要转换的字段分组,本例中是年月;
使用pivot
函数进行透视,透视过程中可以提供第二个参数来明确指定使用哪些数据项,(可以指定不再DataFrame中schema的字段);
汇总数字字段,本例中是收入;
pivot只能跟在groupby之后
test_pivot = test.groupBy('月份') \
.pivot('项目', ['项目1', '项目2', '项目3', '项目4']) \
.agg(F.sum('收入')) \
.fillna(0)
test_pivot.show()
+-------+-----+-----+-----+-----+
| 月份|项目1|项目2|项目3|项目4|
+-------+-----+-----+-----+-----+
|2018-03| 0| 0| 0| 999|
|2018-02| 1000| 2000| 0| 0|
|2018-05| 6000| 4000| 0| 1999|
|2018-01| 100| 200| 300| 0|
+-------+-----+-----+-----+-----+
2.2 collect_set间接实现
coin_df = spark.createDataFrame([('1','1',100), ('1','2',200), ('1','3',300),
('1','4',1000), ('1','5',2000), ('2','1',999),
('2','2',6000), ('2','3',4000), ('2','4',1999),('2','5',6999)
], ['match','id','coin'])
coin_df.show()
+-----+---+----+
|match| id|coin|
+-----+---+----+
| 1| 1| 100|
| 1| 2| 200|
| 1| 3| 300|
| 1| 4|1000|
| 1| 5|2000|
| 2| 1| 999|
| 2| 2|6000|
| 2| 3|4000|
| 2| 4|1999|
| 2| 5|6999|
+-----+---+----+
from pyspark.sql import *
from pyspark.sql.functions import *
from pyspark.sql.types import *
import datetime
#通过collect_set函数对数据进行转换
coin_new=coin_df.groupBy("match").agg(array_sort(collect_set("id")).alias("player_num"))
coin_new.show()
+-----+---------------+
|match| player_num|
+-----+---------------+
| 1|[1, 2, 3, 4, 5]|
| 2|[1, 2, 3, 4, 5]|
+-----+---------------+
后续可以通过sparksql对player_num
这个数组进行解析 player_num[0] player_num[1] player_num[2] 等语句对playernum字段进行拆分,最终实现数据的行列互转。为了实现sql语法,我们将coin_new
储存为临时表,然后通过数组拆解的方法实现行列互转,语法如下。
coin_new.createOrReplaceTempView("coin_final")
coin_player=spark.sql("""
select match,
player_num[0] as player_1,
player_num[1] as player_2,
player_num[3] as player_3,
player_num[4] as player_4
from coin_final
""")
coin_player.show()
+-----+--------+--------+--------+--------+
|match|player_1|player_2|player_3|player_4|
+-----+--------+--------+--------+--------+
| 1| 1| 2| 4| 5|
| 2| 1| 2| 4| 5|
+-----+--------+--------+--------+--------+
3 行转列
3.1 selectExpr以及stack实现数据的逆透视
Spark没有提供内置函数来实现unpivot
操作,不过我们可以使用Spark SQL提供的stack
函数来间接实现需求。有几点需要特别注意:
- 使用
selectExpr
在Spark中执行SQL片段; - 如果字段名称有中文,要使用反引号 ` 把字段包起来
# 逆透视Unpivot
unpivot_test =test_pivot.selectExpr("`月份`",
"stack(4, '项目1', `项目1`,'项目2', `项目2`, '项目3', `项目3`, '项目4', `项目4`) as (`项目`,`收入`)") \
.filter("`收入` > 0 ") \
.orderBy(["`月份`", "`项目`"]) \
unpivot_test.show()
+-------+-----+----+
| 月份| 项目|收入|
+-------+-----+----+
|2018-01|项目1| 100|
|2018-01|项目2| 200|
|2018-01|项目3| 300|
|2018-02|项目1|1000|
|2018-02|项目2|2000|
|2018-03|项目4| 999|
|2018-05|项目1|6000|
|2018-05|项目2|4000|
|2018-05|项目4|1999|
+-------+-----+----+