Most efficient way to perform this task in Pyspark

Jordan H 0
Viewed 44

Say I have a list of customer_id as well as the name of the product they have purchased product_name.

product_list = ['hat','pants','shoes','tie'] in A-Z order

customer_id product_name
001 shoes
001 tie
001 hat
002 shoes
002 tie

What is the most efficient way in Pyspark to make a cross sell table so it looks like this?

hat pants shoes tie
hat 1 0 1 1
pants 0 0 0 0
shoes 1 0 2 2
tie 1 0 2 2

The way to interpret that is reading from the first column: of all the customers who bought a hat this is how many also bought the item in the other columns

So customers who bought a hat also in total bought 1 tie

pyspark apache-spark-sql
1 Answers

You can use crosstab with join. It generates the crosstab based on the values in the provided columns.

data_sdf. \
    join(data_sdf.withColumnRenamed('prod', 'prod2'), ['cust_id'], 'left'). \
    crosstab('prod', 'prod2'). \
    show()

# +----------+---+-----+---+
# |prod_prod2|hat|shoes|tie|
# +----------+---+-----+---+
# |       hat|  1|    1|  1|
# |       tie|  1|    2|  2|
# |     shoes|  1|    2|  2|
# +----------+---+-----+---+
samkart0
Related