I have 2 dataframes. I would like to broadcast a divide operation
df1= pd.DataFrame([[1.,2.,3.,4.], [5.,6.,7.,8.], [9.,10.,11.,12.]],
columns=['A','B','C','D'], index=['x','y','z'])
df2= pd.DataFrame([[0.,1.,2.,3.]], columns=['A','B','D','C'], index=['q'])
Notice that the columns are aligned slightly differently in df2.
I would like to divide df1 by df2 where the row is broadcast but the column labels are respected.
A B C D
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12
A B D C
q 0 1 2 3
This would be wrong.
df1.values/df2.values
[[ inf 2. 1.5 1.33333333]
[ inf 6. 3.5 2.66666667]
[ inf 10. 5.5 4. ]]
Answer I desire is:
A B C D
x inf 2 1 2
y inf 6 2.33 4
z inf 10 3.66 6
If you divide by a Series (by selecting that one row of the second dataframe), pandas will align this series on the columns of the first dataframe, giving the desired result:
In [75]: df1 / df2.loc['q']
Out[75]:
A B C D
x inf 2 1.000000 2
y inf 6 2.333333 4
z inf 10 3.666667 6
If you don't know/want to use the name of that one row, you can use squeeze
to convert the one-column dataframe to a series: df1 / df2.squeeze()
(see answer of @EdChum).