[Python] Pandas - Merge DataFrame, keep non-null values on common columns, keep average on...

I am working with two DataFrames, defined as such:

import pandas as pd
df1 = pd.DataFrame([[1, 'a', 0.95], [2, 'b', 0.92], [3, 'c', 0.91]], columns=['id','value','similarity'])

df2 = pd.DataFrame([[3, 'c', 0.93], [4, 'd', 0.92], [5, 'e', 0.99]], columns=['id','value','similarity'])



df1

id name similarity
1 a 0.95
2 b 0.92
3 c 0.91


df2

id name similarity
3 c 0.93
4 d 0.92
5 e 0.99


I now want to combine both DataFrames by the key attribute id, while avoiding the creation of NaNs in the name column. For this, I use pd.combine_first, as suggested in this answer, to get this:

df3 = df1.set_index('id').combine_first(df2.set_index('id'))
df3

id name similarity
1 a 0.95
2 b 0.92
3 c 0.91
4 d 0.92
5 e 0.99


However, I have an additional requirement. When a similarity value exists on both sets, I want to take their average as the new value. So for instance my desired output would be something like:

id name similarity
1 a 0.95
2 b 0.92
3 c 0.92 <-- (0.91 + 0.93 / 2)
4 d 0.92
5 e 0.99


Preferably using pandas or numpy, how can I average similarities when both DataFrames have a value?

Continue reading...