Pandas share of value with condition and adding new column

user_1706052 0
Viewed 50

I'm new to pandas and got stuck a bit. Can you help me?

I have a dataframe storing orders:

| item | store_status | customer_status |
|------|--------------|-----------------|
| A    | 'dispatched' | 'received'      |
| A    | 'dispatched' | 'pending'       |
| B    | 'pending'    | 'pending'       |
| B    | 'dispatched' | 'received'      |
| B    | 'dispatched' | 'pending'       |

I want to create a new dataframe that shows what portion of each item is 'dispatched' and 'received'. So the result would be:

| item | dispatched_and_received |
|------|-------------------------|
| A    | 0.5                     |
| B    | 0.33                    |

I'm also interested in the portion of each item that is 'dispatched', regardless of the customer status and want to add it as a new column to this dataframe:

| item | dispatched_and_received | dispatched |
|------|-------------------------|------------|
| A    | 0.5                     | 1.00       |
| B    | 0.33                    | 0.66       |

Thank you!

pandas-groupby pandas dataframe
1 Answers

Create Boolean Series that check the conditions, then take the mean of those Series within each group. 

(df.assign(dispatched=df.store_status.eq('dispatched'),
           dispatched_and_received=(df.store_status.eq('dispatched')
                                    & df.customer_status.eq('received')))
   .groupby('item')[['dispatched', 'dispatched_and_received']]
   .mean()
   .reset_index())

#  item  dispatched  dispatched_and_received
#0    A    1.000000                 0.500000
#1    B    0.666667                 0.333333

The assign just creates the columns, you can split that out manually above if all of that chaining seems a bit cluttered. It's equivalent to:

df['dispatched'] = df.store_status.eq('dispatched')
df['dispatched_and_received'] = df['dispatched'] & df.customer_status.eq('received')

This is the DataFrame after the assign

  item store_status customer_status  dispatched  dispatched_and_received
0    A   dispatched        received        True                     True
1    A   dispatched         pending        True                    False
2    B      pending         pending       False                    False
3    B   dispatched        received        True                     True
4    B   dispatched         pending        True                    False
user_43333590
Related