Pandas how to fill missing values in one column if the values in another column are equal

Question

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)

Answer 1

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.

In [117]: df['paid_date'] = pd.to_datetime(df['paid_date'], errors='coerce')

In [118]: df
Out[118]:
   index     id  paid_date
0      6  25220 2017-01-05
1      9  30847        NaT
2     11  30847        NaT
3     14  29369 2017-06-21
4     17  31232 2017-08-31
5     20  26196 2017-02-20
6     21  26196        NaT
7     24  28303 2017-05-09
8     25  28303        NaT

In [119]: df.groupby('id').ffill()
Out[119]:
   index     id  paid_date
0      6  25220 2017-01-05
1      9  30847        NaT
2     11  30847        NaT
3     14  29369 2017-06-21
4     17  31232 2017-08-31
5     20  26196 2017-02-20
6     21  26196 2017-02-20
7     24  28303 2017-05-09
8     25  28303 2017-05-09

---

If it's not guaranteed, then we can do this:

In [111]: df['paid_date'] = pd.to_datetime(df['paid_date'], errors='coerce')

In [112]: df
Out[112]:
   index     id  paid_date
0      6  25220 2017-01-05
1      9  30847        NaT
2     11  30847        NaT
3     14  29369 2017-06-21
4     17  31232 2017-08-31
5     20  26196 2017-02-20
6     21  26196        NaT
7     24  28303 2017-05-09
8     25  28303        NaT

In [113]: df.loc[df.paid_date.isnull(), 'paid_date'] = \
              df.loc[df.paid_date.isnull(), 'id'].map(df.loc[df.paid_date.notnull()] \
                .set_index('id')['paid_date'])

In [114]: df
Out[114]:
   index     id  paid_date
0      6  25220 2017-01-05
1      9  30847        NaT
2     11  30847        NaT
3     14  29369 2017-06-21
4     17  31232 2017-08-31
5     20  26196 2017-02-20
6     21  26196 2017-02-20
7     24  28303 2017-05-09
8     25  28303 2017-05-09