Fill missing values from another Pandas dataframe with different shape

Question

I have 2 tables of different sizes which I would like to merge in the following way in Python using Pandas:

UID Property    Date
1   A           10/02/2016
2   B           NaN
3   A           10/02/2016
4   C           NaN
5   C           NaN
6   A           10/02/2016

Table 1 contains information about Property transactions and a Date related to the Property. As some of the dates are NaNs, I would like to proxy them from another table (Table 2) containing information solely about properties, but not replacing any dates in Table 1:

Property    DateProxy
A           01/01/2016
B           03/04/2016
C           16/05/2016

In the end I would like to obtain the following:

UID Property    Date
1   A           10/02/2016 (kept from T1)
2   B           03/04/2016 (imported from T2)
3   A           10/02/2016 (kept from T1)
4   C           16/05/2016 (imported from T2)
5   C           16/05/2016 (imported from T2)
6   A           10/02/2016 (kept from T1)

Matteo Felici · Accepted Answer · 2016-09-15 09:31:39Z

1

First let's merge the two datasets: we don't overwrite the original date:

df_merge = pandas.merge(T1, T2, on='Property')

then we replace the missing values copying them from the 'DateProxy' field:

df_merge.Date = df_merge.apply(
    lambda x: x['Date'] + ' (kept from T1)' if x['Date'] == x['Date']
                                            else x['DateProxy'] + ' (imported from T2)',
    axis=1
)

(the x['Date'] == x['Date'] is to check that it isn't NaN, NaN is not equal to itself). Finally we can drop the proxy column:

df_final = df_merge.drop('DateProxy', axis=1)

answered Sep 15, 2016 at 9:31

Matteo Felici

1,10711 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Silviu Tofan Over a year ago

Thanks a lot, I've been struggling with this for quite some time now and it was all there. I also should have added # before the parenthesis in the last table, sry bout that.

Collectives™ on Stack Overflow

Fill missing values from another Pandas dataframe with different shape

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related