Columns to rows and count for summarizing in pyspark

user_3473471 0
Viewed 50

I have the following df in pyspark:

id col1 col2
on true true
off false true
on true false
off false true
on true false
off false true

and I would like to summarize it as shown below:

col id true false
col1 on 3 0
off 0 3
col2 on 1 2
off 3 0

where the integers represent the count() of true and false instances. I've tried using unpivot unsuccessfully. Any help will be appreciated.

pyspark databricks apache-spark-sql
2 Answers

You also can pivot your table (several times, so you can also use for comprehensive as well)

from pyspark.sql import functions as F

c1 = df.groupBy('id').pivot('c1').count().withColumn('col', F.lit('c1'))
c2 = df.groupBy('id').pivot('c2').count().withColumn('col', F.lit('c2'))

c1.union(c2).fillna(0).show()

+---+---+----+-----+
|col| id|true|false|
+---+---+----+-----+
| c1| on|   3|    0|
| c1|off|   0|    3|
| c2| on|   1|    2|
| c2|off|   3|    0|
+---+---+----+-----+
user_34415100

Few mechanics. List comprehension could be of help

#Pivot, in this case, iterate the df and pivot. That will give a list of dfs

cols=df.drop('id').columns
lst = [df.crosstab('id',c).withColumn('col', lit(c)) for c in cols]

#Use unionall or unionByName to reduce the list of dfs into one df. To do this, iterate each df in the lit renaming first column

df1 = reduce(DataFrame.unionByName, [y.withColumnRenamed(y.columns[0],'id') for y in lst])

df1.show()

+---+-----+----+----+
| id|false|true| col|
+---+-----+----+----+
| on|    0|   3|col1|
|off|    3|   0|col1|
| on|    2|   1|col2|
|off|    0|   3|col2|
+---+-----+----+----+
user_89869750
Related