How to create a column which flags (1 - weight loss;0 - same weight a) weight loss (8% or more) from previous measurement based on groupby of id?

user_14739761 0
Viewed 26

I have a dataframe df:

import pandas as pd
df = pd.DataFrame({"CLIENT_ID": [8222, 8222, 8222, 8222, 8300, 8300, 8300, 8300, 8300],
                   "ENCOUNTER_DATE": ['2020-01-01', '2020-03-02', '2020-04-18', '2020-07-31', '2017-06-10', '2017-09-11', '2018-02-01', '2018-04-01', '2018-05-31'],
                   "WEIGHT_KG": [56, 58, 50, 54, 71, 72, 74, 75, 65]})

which is sorted by CLIENT_ID and ENCOUNTER_DATE

CLIENT_ID ENCOUNTER_DATE WEIGHT_KG
8222 2020-01-01 56
8222 2020-03-02 58
8222 2020-04-18 50
8222 2020-07-31 54
8300 2017-06-10 71
8300 2017-09-11 72
8300 2018-02-01 74
8300 2018-04-01 75
8300 2018-05-31 65

I want to create a WEIGHT_LOSS flag column which is 1, if the current WEIGHT_KG is at least 10% lower than the previous measurement and 0 if it is not, for each CLIENT_ID resulting in the table below:

CLIENT_ID ENCOUNTER_DATE WEIGHT_KG WEIGHT_LOSS
8222 2020-01-01 56 0
8222 2020-03-02 58 0
8222 2020-04-18 50 1
8222 2020-07-31 54 0
8300 2017-06-10 71 0
8300 2017-09-11 72 0
8300 2018-02-01 74 0
8300 2018-04-01 75 0
8300 2018-05-31 65 1

There probably is easy answer with df.assign, np.where or list comprehension.

pandas apply dataframe where-clause python
1 Answers

You cangroupby client and use pct_change on the "WEIGHT_KG" column:

df['WEIGHT_LOSS'] = (df.groupby('CLIENT_ID')
                       ['WEIGHT_KG']
                       .pct_change() # calculate percent change
                       .lt(-0.1)     # loss if lower than -0.1 (-10%)
                       .astype(int)  # convert True/False to 1/0
                     )

output:

   CLIENT_ID ENCOUNTER_DATE  WEIGHT_KG  WEIGHT_LOSS
0       8222     2020-01-01         56            0
1       8222     2020-03-02         58            0
2       8222     2020-04-18         50            1
3       8222     2020-07-31         54            0
4       8300     2017-06-10         71            0
5       8300     2017-09-11         72            0
6       8300     2018-02-01         74            0
7       8300     2018-04-01         75            0
8       8300     2018-05-31         65            1
user_163434640
Related