Pivot String column on Pyspark Dataframe

by

Assuming that (id |type | date) combinations are unique and your only goal is pivoting and not aggregation you can use first (or any other function not restricted to numeric values):

from pyspark.sql.functions import first

(df_data
    .groupby(df_data.id, df_data.type)
    .pivot("date")
    .agg(first("ship"))
    .show())

## +---+----+------+------+------+
## | id|type|201601|201602|201603|
## +---+----+------+------+------+
## |  2|   C|  DOCK|  null|  null|
## |  0|   A|  DOCK|  PORT|  PORT|
## |  1|   B|  PORT|  DOCK|  null|
## +---+----+------+------+------+

If these assumptions is not correct you’ll have to pre-aggregate your data. For example for the most common ship value:

from pyspark.sql.functions import max, struct

(df_data
    .groupby("id", "type", "date", "ship")
    .count()
    .groupby("id", "type")
    .pivot("date")
    .agg(max(struct("count", "ship")))
    .show())

## +---+----+--------+--------+--------+
## | id|type|  201601|  201602|  201603|
## +---+----+--------+--------+--------+
## |  2|   C|[1,DOCK]|    null|    null|
## |  0|   A|[1,DOCK]|[1,PORT]|[1,PORT]|
## |  1|   B|[1,PORT]|[1,DOCK]|    null|
## +---+----+--------+--------+--------+

More Related Contents:

  1. How to add a constant column in a Spark DataFrame?
  2. Spark Dataframe distinguish columns with duplicated name
  3. Pyspark: Split multiple array columns into rows
  4. How do I add a new column to a Spark DataFrame (using PySpark)?
  5. How to change a dataframe column from String type to Double type in PySpark?
  6. Count number of non-NaN entries in each column of Spark dataframe with Pyspark
  7. Retrieve top n in each group of a DataFrame in pyspark
  8. Updating a dataframe column in spark
  9. Dividing complex rows of dataframe to simple rows in Pyspark
  10. Filter Pyspark dataframe column with None value
  11. PySpark converting a column of type ‘map’ to multiple columns in a dataframe
  12. How to explode multiple columns of a dataframe in pyspark
  13. Spark add new column to dataframe with value from previous row
  14. PySpark: multiple conditions in when clause
  15. Pyspark: Replacing value in a column by searching a dictionary
  16. Count number of non-NaN entries in each column of Spark dataframe in PySpark
  17. Create Spark DataFrame. Can not infer schema for type
  18. Filtering DataFrame using the length of a column
  19. Multiple condition filter on dataframe
  20. How to return a “Tuple type” in a UDF in PySpark?
  21. How to change dataframe column names in pyspark?
  22. How to export a table dataframe in PySpark to csv?
  23. How can we JOIN two Spark SQL dataframes using a SQL-esque “LIKE” criterion?
  24. Pyspark ‘NoneType’ object has no attribute ‘_jvm’ error
  25. Why is Apache-Spark – Python so slow locally as compared to pandas?
  26. PySpark create new column with mapping from a dict
  27. Create single row dataframe from list of list PySpark
  28. How do I convert an array (i.e. list) column to Vector
  29. Add column sum as new column in PySpark dataframe
  30. How to drop all columns with null values in a PySpark DataFrame?

Leave a Comment