3
$\begingroup$

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)
$\endgroup$
2
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.