Dataframe - Create new column based on condition

Question

I am dealing with a dataframe which has several thousands of rows & has several columns. The columns of interest is called customer_csate_score & group_csate_score

The data looks like below

customer_csate_score group_csate_score
    0.000                   -0.15
    -0.4                     0.12
    0                        0.13 
    0.578                    0.81   
    0.418                    0.96
    -0.765                   0.1
    0.89                     -0.87

What I'm trying to do is create 2 new columns in the dataframe called customer_group_csate_score_toggle_status & is_customer_perf_above_group_perf. customer_group_csate_score_toggle_status will be true only when customer_csate_score & group_csate_score cross over & False when there is no crossover. is_customer_perf_above_group_perf is True if customer_csate_score is above group_csate_score & False if customer_csate_score is below group_csate_score

Expected Output for toggle status column

 customer_group_csate_score_toggle_status  is_customer_perf_above_group_perf
        False                                        True
        True                                         False
        False                                        False
        False                                        False
        False                                        False
        False                                        False
        True                                         True

I've tried this code so far -

def check_cust_group_crossover(df, df_key1, def_key2):
    return np.where(
        (
            (df[df_key1] > df[def_key2]) & \
            (df[df_key1].shift() < df[def_key2].shift())
        ),
        True, False
    )

I'm struggling to implement the entire functionality. Could I please request guidance to implement this?

It would help if you could provide a minimal working example with sample data and easier (shorter) variable/column names. — buhtz
– buhtz, Commented Sep 7, 2021 at 11:03

SeaBean · Accepted Answer · 2021-09-07 11:33:54Z

You can set boolean masks for the crossover conditions. As there are 2 possible crossover cases, we or them to get the final conditions for customer_group_csate_score_toggle_status

For is_customer_perf_above_group_perf just compare whether customer_csate_score > group_csate_score:

m1 = (df['customer_csate_score'] > df['group_csate_score']) & (df['customer_csate_score'].shift() < df['group_csate_score'].shift())  
m2 = (df['customer_csate_score'] < df['group_csate_score']) & (df['customer_csate_score'].shift() > df['group_csate_score'].shift())  
df['customer_group_csate_score_toggle_status'] = m1 | m2

df['is_customer_perf_above_group_perf'] = df['customer_csate_score'] > df['group_csate_score']

Note: We don't need special handling for the first row for the mask on checking the previous row. It will always return False when comparing either (df['customer_csate_score'].shift() < df['group_csate_score'].shift()) or (df['customer_csate_score'].shift() > df['group_csate_score'].shift()) for the first row since we are comparing NaN with NaN. Hence comparing (NaN > NaN) or (NaN < NaN) will always return False as expected.

Result:

print(df)

   customer_csate_score  group_csate_score  customer_group_csate_score_toggle_status  is_customer_perf_above_group_perf
0                 0.000              -0.15                                     False                               True
1                -0.400               0.12                                      True                              False
2                 0.000               0.13                                     False                              False
3                 0.578               0.81                                     False                              False
4                 0.418               0.96                                     False                              False
5                -0.765               0.10                                     False                              False
6                 0.890              -0.87                                      True                               True

Thank you very much @SeaBean. This works for me. Also, a huge thank you for the comments.

mozway · Accepted Answer · 2021-09-07 11:17:13Z

You can first compute "is_customer_perf_above_group_perf", which is a simple comparison of the initial two columns. Then compute the diff on the new column, which will be True when the value changes (i.e., when there is "crossing"). This fillna(False) is to set the first value as it has no previous row to compare with.

(df.eval('is_customer_perf_above_group_perf = customer_csate_score > group_csate_score')
   .assign(customer_group_csate_score_toggle_status=lambda d: d['is_customer_perf_above_group_perf'].diff().fillna(False))
)

output:

   customer_csate_score  group_csate_score  is_customer_perf_above_group_perf  customer_group_csate_score_toggle_status
0                 0.000              -0.15                               True                                     False
1                -0.400               0.12                              False                                      True
2                 0.000               0.13                              False                                     False
3                 0.578               0.81                              False                                     False
4                 0.418               0.96                              False                                     False
5                -0.765               0.10                              False                                     False
6                 0.890              -0.87                               True                                      True

Collectives™ on Stack Overflow

Dataframe - Create new column based on condition

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related