Pandas Compare Similar DataFrames and Get Min
Given the following data frames:
d1=pd.DataFrame({'A':[1,2,np.nan],'B':[np.nan,5,6]})
d1.index=['A','B','E']
A B
A 1.0 NaN
B 2.0 5.0
E NaN 6.0
d2=pd.DataFrame({'A':[4,2,np.nan,4],'B':[4,2,np.nan,4]})
d2.index=['A','B','C','D']
A B
A 4.0 4.0
B 2.0 2.0
C NaN NaN
D 4.0 4.0
I would like to compare them to find the lowest value in each matching row while keeping all the row indices from both. Here is the result I'm looking for:
A B
A 1.0 4.0
B 2.0 2.0
C NaN NaN
D 4.0 4.0
E NaN 6.0
Thanks in advance!
+3
Dance Party2
source
to share
3 answers
Another option has aligning
two dataframes (both index and columns), then use numpy.fmin
:
pd.np.fmin(*d1.align(d2))
Less confusing:
d1, d2 = d1.align(d2) pd.np.fmin(d1, d2)
+5
Psidom
source
to share
You can do dfs and then use groupby to save min
df = pd.concat([d1,d2])
df = df.groupby(df.index).min()
You get
A B
A 1.0 4.0
B 2.0 2.0
C NaN NaN
D 4.0 4.0
E NaN 6.0
EDIT: More concise solutions from @root and @ScottBoston
pd.concat([d1, d2]).groupby(level=0).min()
+6
Vaishali
source
to share
Use pd.Panel
with min
Also note that this generalizes to any number of data frames.
pd.Panel(dict(enumerate([d1, d2]))).min(0)
A B
A 1.0 4.0
B 2.0 2.0
C NaN NaN
D 4.0 4.0
E NaN 6.0
+4
piRSquared
source
to share