Group by ID and apply a condition on the value of one column

user_15779666 0
Viewed 921

I am new to pandas library and I would appreciate your help. This is my input

ID DATE LABEL STOCK
AA 01/26/2020 TRUE 100
AA 01/27/2020 FALSE 200
BB 01/28/2020 FALSE 300
BB 01/29/2020 TRUE 500
BB 01/30/2020 FALSE 100
CC 01/26/2020 TRUE 200
CC 01/27/2020 FALSE 300
CC 01/28/2020 FALSE 100
CC 01/29/2020 TRUE 400

I would like to look at the Label column and for the first "TRUE" label that is found for each ID I need to copy the value of stock in a new column. If an ID has more than one TRUE I need to consider only the first one.

Here is the output i want to obtain:

ID DATE LABEL STOCK NEW
AA 01/26/2020 TRUE 100 100
AA 01/27/2020 FALSE 200
BB 01/28/2020 FALSE 300
BB 01/29/2020 TRUE 500 500
BB 01/30/2020 FALSE 100
CC 01/26/2020 TRUE 200 200
CC 01/27/2020 FALSE 300
CC 01/28/2020 FALSE 100
CC 01/29/2020 TRUE 400

Thank you for your help!

jupyter-notebook pandas dataframe python
2 Answers

use a double boolean with .duplicated()

df.loc[df['LABEL'].eq(True) & 
      df.duplicated(subset=['ID'],keep='last'),'NEW'] = df['STOCK']


print(df.fillna(''))

   ID        DATE  LABEL  STOCK  NEW
0  AA  01/26/2020   True    100  100
1  AA  01/27/2020  False    200     
2  BB  01/28/2020  False    300     
3  BB  01/29/2020   True    500  500
4  BB  01/30/2020  False    100     
5  CC  01/26/2020   True    200  200
6  CC  01/27/2020  False    300     
7  CC  01/28/2020  False    100     
8  CC  01/29/2020   True    400

looking at each boolean, we only want the values where both are true.

df.assign(ky1=df['LABEL'].eq(True),
         ky2=df.duplicated(subset=['ID'],keep='last')
         )


   ID        DATE  LABEL  STOCK    NEW    ky1    ky2
0  AA  01/26/2020   True    100  100.0   True   True
1  AA  01/27/2020  False    200    NaN  False  False
2  BB  01/28/2020  False    300    NaN  False   True
3  BB  01/29/2020   True    500  500.0   True   True
4  BB  01/30/2020  False    100    NaN  False  False
5  CC  01/26/2020   True    200  200.0   True   True
6  CC  01/27/2020  False    300    NaN  False   True
7  CC  01/28/2020  False    100    NaN  False   True
8  CC  01/29/2020   True    400    NaN   True  False
user_93751020

Another version, using .groupby():

m = df.index.isin(
    df[df["LABEL"] == True].reset_index().groupby("ID").index.first()
)
df.loc[m, "NEW"] = df.loc[m, "STOCK"]
print(df)

Prints:

   ID        DATE  LABEL  STOCK    NEW
0  AA  01/26/2020   True    100  100.0
1  AA  01/27/2020  False    200    NaN
2  BB  01/28/2020  False    300    NaN
3  BB  01/29/2020   True    500  500.0
4  BB  01/30/2020  False    100    NaN
5  CC  01/26/2020   True    200  200.0
6  CC  01/27/2020  False    300    NaN
7  CC  01/28/2020  False    100    NaN
8  CC  01/29/2020   True    400    NaN
user_100359850
Related