I have a dataframe in which the weight of each client is not well kept resulting in:

The table is sorted by CLIENT_ID and DATE_ENCOUNTER. How can I replace the values for each CLIENT_ID where the WEIGHT_KG = 0.0 by first forward filling the entries, then back filling them? (Depending on the entries for each CLIENT_ID This would result in a dataframe shown below:

Here is code to generate the df:

df = pd.DataFrame({"CLIENT_ID": [16081, 16081, 16081, 16081, 20011, 20011, 20011, 20011, 20011,20020],
                   "ENCOUNTER_DATE": ['2018-12-17', '2019-03-19', '2019-04-18', '2019-06-07', '2020-02-27', '2020-03-27', '2020-04-27', '2020-06-07', '2020-07-07','2020-01-01'],
                   "WEIGHT_KG": [70, 0, 0, 0, 0, 0, 57, 0, 60,0]})

CodePudding user response：

Idea is replace 0 to missing values and then per groups use forward and backfilling missing values, last replace NaN to 0:

df['WEIGHT_KG'] = (df['WEIGHT_KG'].replace(0, np.nan)
                                  .groupby(df['CLIENT_ID'])
                                  .transform(lambda x: x.ffill().bfill())
                                  .fillna(0))

Or:

df['WEIGHT_KG'] = (df['WEIGHT_KG'].where(df['WEIGHT_KG'].ne(0))
                                  .groupby(df['CLIENT_ID'])
                                  .transform(lambda x: x.ffill().bfill())
                                  .fillna(0))
print (df)
   CLIENT_ID ENCOUNTER_DATE  WEIGHT_KG
0      16081     2018-12-17       70.0
1      16081     2019-03-19       70.0
2      16081     2019-04-18       70.0
3      16081     2019-06-07       70.0
4      20011     2020-02-27       57.0
5      20011     2020-03-27       57.0
6      20011     2020-04-27       57.0
7      20011     2020-06-07       57.0
8      20011     2020-07-07       60.0
9      20020     2020-01-01        0.0