Display a column when a desired value is missing while grouping in Pandas dataframe

user_11392722 0
Viewed 54

Good day, I have a dataframe with region, customer and some deliveries. There is this column used as type of purchase and the first and last purchase are marked as 'first' and 'last' and sometimes we have in-between deliveries marked as "delivery". I need to flag the customers and region that don't have any in-between deliveries at all, as a column in the desired output. Marking an in-between delivery on a row ain't hard but the whole group customer-region needs to be marked.

    import pandas as pd  
    data = [['NY', 'A','FIRST', 10], ['NY', 'A','DELIVERY', 20], ['NY', 'A','DELIVERY', 30], ['NY', 'A','LAST', 25],
           ['NY', 'B','FIRST', 15], ['NY', 'B','DELIVERY', 10], ['NY', 'B','LAST', 20],
           ['FL', 'A','FIRST', 15], ['FL', 'A','DELIVERY', 10], ['FL', 'A','DELIVERY', 12], ['FL', 'A','DELIVERY', 25], ['FL', 'A','LAST', 20],
           ['FL', 'C','FIRST', 15], ['FL', 'C','LAST', 10],
           ['FL', 'D','FIRST', 10], ['FL', 'D','DELIVERY', 20], ['FL', 'D','LAST', 30],
           ['FL', 'E','FIRST', 20], ['FL', 'E','LAST', 20]
           ] 
      
    # Create the pandas DataFrame 
    df = pd.DataFrame(data, columns = ['region', 'customer', 'purchaseType', 'price']) 
      
    # print dataframe. 
    df

Print:

   region customer purchaseType  price
0      NY        A        FIRST     10
1      NY        A     DELIVERY     20
2      NY        A     DELIVERY     30
3      NY        A         LAST     25
4      NY        B        FIRST     15
5      NY        B     DELIVERY     10
6      NY        B         LAST     20
7      FL        A        FIRST     15
8      FL        A     DELIVERY     10
9      FL        A     DELIVERY     12
10     FL        A     DELIVERY     25
11     FL        A         LAST     20
12     FL        C        FIRST     15
13     FL        C         LAST     10
14     FL        D        FIRST     10
15     FL        D     DELIVERY     20
16     FL        D         LAST     30
17     FL        E        FIRST     20
18     FL        E         LAST     20

Desired output:

   region customer purchaseType  price noDeliveryFlag
0      NY        A        FIRST     10              0
1      NY        A     DELIVERY     20              0
2      NY        A     DELIVERY     30              0
3      NY        A         LAST     25              0
4      NY        B        FIRST     15              0
5      NY        B     DELIVERY     10              0
6      NY        B         LAST     20              0
7      FL        A        FIRST     15              0
8      FL        A     DELIVERY     10              0
9      FL        A     DELIVERY     12              0
10     FL        A     DELIVERY     25              0
11     FL        A         LAST     20              0
12     FL        C        FIRST     15              1
13     FL        C         LAST     10              1
14     FL        D        FIRST     10              0
15     FL        D     DELIVERY     20              0
16     FL        D         LAST     30              0
17     FL        E        FIRST     20              1
18     FL        E         LAST     20              1

Thank you so much!

pandas-groupby pandas dataframe flags python
3 Answers

I think I figured this out

df['noDeliveryFlag'] = df['purchaseType'] != 'DELIVERY'
df['noDeliveryFlag'] = df.groupby(['region','customer'])['noDeliveryFlag'].transform('min').astype(int)
print(df)

If someone has got a more efficient way I appreciate it.

user_113927220

First we figure out the delivery status by region and customer. for that we groupby by region, customer and then in each group we check if 'DELIVERY' is included in the part of the purchaseType series that is in that group. We assign 1 to the group if no delivery, 0 otherwise (could be more natural to use True/False here but sticking to the question)

delivery_status = (df.groupby(['region', 'customer'], sort=False)['purchaseType']
                    .apply(lambda d: 1*('DELIVERY' not in d.values))
                    .rename('noDeliveryFlag')
                    )
delivery_status

this produces

region  customer
NY      A           0
        B           0
FL      A           0
        C           1
        D           0
        E           1
Name: noDeliveryFlag, dtype: int64

Then we just merge this into the original df

(df.set_index(['region', 'customer'])
    .join(delivery_status,how = 'left', sort=False)
    .reset_index()
)

to get

    region    customer    purchaseType      price    noDeliveryFlag
--  --------  ----------  --------------  -------  ----------------
 0  FL        A           FIRST                15                 0
 1  FL        A           DELIVERY             10                 0
 2  FL        A           DELIVERY             12                 0
 3  FL        A           DELIVERY             25                 0
 4  FL        A           LAST                 20                 0
 5  FL        C           FIRST                15                 1
 6  FL        C           LAST                 10                 1
 7  FL        D           FIRST                10                 0
 8  FL        D           DELIVERY             20                 0
 9  FL        D           LAST                 30                 0
10  FL        E           FIRST                20                 1
11  FL        E           LAST                 20                 1
12  NY        A           FIRST                10                 0
13  NY        A           DELIVERY             20                 0
14  NY        A           DELIVERY             30                 0
15  NY        A           LAST                 25                 0
16  NY        B           FIRST                15                 0
17  NY        B           DELIVERY             10                 0
18  NY        B           LAST                 20                 0

Note the solution does not check that there is no DELIVERY between FIRST and LAST -- it just checks that there is no DELIVERY for that region/customer at all.

user_145514260

you can use transform and size with a groupby operation.

This approach assumes that anyone with only 2 purchaseTypes has had no delivery, it does not account for deliveries in progress.

df['noDeliveryFlag'] = np.where(df.groupby(['customer','region'])
                                          ['purchaseType'].transform('size').eq(2),1,0)


   region customer purchaseType  price  noDeliveryFlag
0      NY        A        FIRST     10               0
1      NY        A     DELIVERY     20               0
2      NY        A     DELIVERY     30               0
3      NY        A         LAST     25               0
4      NY        B        FIRST     15               0
5      NY        B     DELIVERY     10               0
6      NY        B         LAST     20               0
7      FL        A        FIRST     15               0
8      FL        A     DELIVERY     10               0
9      FL        A     DELIVERY     12               0
10     FL        A     DELIVERY     25               0
11     FL        A         LAST     20               0
12     FL        C        FIRST     15               1
13     FL        C         LAST     10               1
14     FL        D        FIRST     10               0
15     FL        D     DELIVERY     20               0
16     FL        D         LAST     30               0
17     FL        E        FIRST     20               1
18     FL        E     DELIVERY     20               1
user_93751020
Related