I have a pyspark dataframe. It is a movie dataset. One column is the genres split by |. Each movie has multiple genres.
genres = spark.sql("SELECT DISTINCT genres FROM movies ORDER BY genres ASC")
genres.show(5)
I would like to count each genre has how many movies. And I also want to show what are those movies. Just like the following:
How should I do this?
Here's a way to do:
# sample data
d = [('Action',), ('Action|Adventure',), ('Action|Adventure|Drama',)]
df = spark.createDataFrame(d, ['genres',])
# create count
agg_df = (df
.rdd
.map(lambda x: x.genres.split('|')) # gives nested list
.flatMap(lambda x: x) # flatten the list
.map(lambda x: (x,)) # convert to tuples
.toDF(['genres'])
.groupby('genres')
.count())
agg_df.show()
+---------+-----+
| genres|count|
+---------+-----+
|Adventure| 2|
| Drama| 1|
| Action| 3|
+---------+-----+
Here is a way using only DataFrame API. First, use split function to split the genres strings then explode the result array and groupBy genres to count:
data = [["Action"], ["Action|Adventure|Thriller"], ["Action|Adventure|Drama"]]
df = spark.createDataFrame(data, ["genres"])
df = df.withColumn("genres", explode(split(col("genres"), "[|]"))) \
.groupBy("genres").count()
df.show()
Gives:
+---------+-----+
| genres|count|
+---------+-----+
| Thriller| 1|
|Adventure| 2|
| Drama| 1|
| Action| 3|
+---------+-----+
Use:
import pyspark.sql.functions as f
df.groupby("generes").agg(f.collect_set("Category"),f.count("Category")).show()
this will get the desired output.