How to find the same index between two dataframes and combine it to a new dataframe, Python 3

user_4772565 0
Viewed 4322

I have two pandas.dataframes df1 and df2. Some of their index are equal. I want to find those index and combine the corresponding rows into a new dataframe.

df1 =
        A    B
Name
apple   1    5
orange  2    6
banana  3    7


df2 =
        A    B
Name
apple  -1   10
audi   -2   11
bmw     0   12
banana  2    8
vw     -3    6

The new dataframe that I want is: 1) find the rows with the same index; 2) calculate the average value of the corresponding rows in column 'A'.

df_new = 
        A_average   
Name
apple   0   
banana  2.5

This is because: df1 and df2 both have the index apple and banana. The average value of apple in column 'A' is (1-1)/2=0, and the average value of banana in column 'A' is (3+2)/2=2.5.

Do you know how to use Python3 to achieve this? Please note that, in my real application, there can be many more rows than the example I showed above.

Thanks!

pandas dataframe python
1 Answers

Option 1
You could concatenate the two dataframes and group by columns.

pd.concat([df1, df2], 1).dropna().mean(axis=1, level=0)

          A    B
apple   0.0  7.5
banana  2.5  7.5

If it's just A you want, then this should suffice - 

pd.concat([df1, df2], 1).dropna()['A'].mean(axis=1, level=0)

          A
apple   0.0
banana  2.5

Option 2
An alternative would be to find the intersecting indices with index.intersection and index with loc -

i = df1.index.intersection(df2.index)

df1.loc[i, ['A']].add(df2.loc[i, ['A']]).div(2)

          A
Name       
apple   0.0
banana  2.5
user_49090870
Related