使用 pyspark 和 aws 胶水进行数据转置

Question

我是pyspark的新手，在数据转置方面面临一些挑战。我正在使用 aws 胶水来运行作业。当前数据如下所示：

+-----------------+-----+------+-----+
|  Country        |Code |1969  |1979 |
+-----------------+------------------+
|  United States  | USA | 1234 | 4569|
--------------------------------------

我需要将数据转置为：

+-----------------+-----+-------+----------+
|Country          |Code | Year | Population| 
+-----------------+-------------------------
|United States.   |USA  | 1969 | 1234.     |
--------------------------------------------
|United States.   |USA  | 1970 | 4569.     |
--------------------------------------------

我尝试尝试使用胶水映射功能，但这比这要复杂得多。任何帮助将不胜感激。

Answer 1

我认为您在这里需要的是相当于熊猫融化的Pyspark：

from typing import Iterable
from pyspark.sql import functions as F
from pyspark.sql import DataFrame

def melt(
df: DataFrame, 
id_vars: Iterable[str], value_vars: Iterable[str], 
var_name: str="variable", value_name: str="value") -> DataFrame:
"""Convert :class:`DataFrame` from wide to long format."""
# Create array<struct<variable: str, value: ...>>
_vars_and_vals = array(*(
struct(lit(c).alias(var_name), col(c).alias(value_name)) 
for c in value_vars))
# Add to the DataFrame and explode
_tmp = df.withColumn("_vars_and_vals", explode(_vars_and_vals))
cols = id_vars + [
col("_vars_and_vals")[x].alias(x) for x in [var_name, value_name]]
return _tmp.select(*cols)

然后

melt(df, id_vars=['Country', 'Code'], value_vars=['1969', '1979']
var_name=['Year'], value_name=['Population'] ).show()