I have a dataframe where I need to fill in the missing values in one column (paid_date) by using the values from rows with the same value in a different column (id). There is guaranteed to be no more than 1 non-null value in the paid_date column per id value and the non-null value will always come before the null values. For example:
index id paid_date
6 25220 2017-01-05 00:00:00
9 30847 None
11 30847 None
14 29369 2017-06-21 00:00:00
17 31232 2017-08-31 00:00:00
20 26196 2017-02-20 00:00:00
21 26196 None
24 28303 2017-05-09 00:00:00
25 28303 None
How can I replace the None
values in the paid_date
column if there is a row with a paid_date
with a matching id?
index id paid_date
6 25220 2017-01-05 00:00:00
9 30847 None
11 30847 None
14 29369 2017-06-21 00:00:00
17 31232 2017-08-31 00:00:00
20 26196 2017-02-20 00:00:00
21 26196 2017-02-20 00:00:00
24 28303 2017-05-09 00:00:00
25 28303 2017-05-09 00:00:00
I tried using fillna
with a dictionary that mapped ids to paid_dates and I tried using pd.Series.map
but neither worked.
paid_dates = df[pd.notnull(df['paid_date'])]
pds = pd.Series(data=paid_dates['paid_date'].values, index=paid_dates['id'])
pds_dict = pds.to_dict()
# doesn't work
df['paid_date'].fillna(value=pds_dict)
# also doesn't work
df['paid_date'].map(pds_dict)