Logical with count in Pyspark

Question

I'm new to Pyspark and I have a problem to solve.

I have a dataframe with 4 columns, being customers, person, is_online_store and count:

customer	PersonId	is_online_store	count
afabd2d2	4	true	1
afabd2d2	8	true	2
afabd2d2	3	true	1
afabd2d2	2	false	1
afabd2d2	4	false	1

I need to create according to the following rules:

If PersonId count(column) has 1 or + Trues and None False then Online
If PersonId count(column) has 1 or + False and None True then offline
If the PersonId count(column) has at least 1 False AND 1 True then Hybrid

How it should look:

customer	PersonId	New_Column
afabd2d2	4	Hybrid
afabd2d2	8	Online
afabd2d2	3	Online
afabd2d2	2	Offline

blackbishop · Accepted Answer · 2022-02-11 09:59:53Z

1

Group by customer + personId and use when expression to check if all values in is_online_store column are true / false or a mix of the 2, using for example bool_and function:

from pyspark.sql import functions as F

df1 = df.groupBy("customer", "PersonId").agg(
    F.when(F.expr("bool_and(is_online_store)"), "Online")
     .when(F.expr("bool_and(!is_online_store)"), "Offline")
     .otherwise("Hybrid").alias("New_Column")
)

df1.show()
#+--------+--------+----------+
#|customer|PersonId|New_Column|
#+--------+--------+----------+
#|afabd2d2|       2|   Offline|
#|afabd2d2|       8|    Online|
#|afabd2d2|       4|    Hybrid|
#|afabd2d2|       3|    Online|
#+--------+--------+----------+

answered Feb 11, 2022 at 9:59

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caroline Leite Over a year ago

Thanks for the help! It worked, I just had to convert the column to Boolean before.

Caroline Leite Over a year ago

"When I went up to production, it started giving this error: Undefined function: 'bool_and'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.; line 1 pos 0"

Collectives™ on Stack Overflow

Logical with count in Pyspark

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related