Clever Geek Handbook

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


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))

enter image description here



Less confusing:

d1, d2 = d1.align(d2)
pd.np.fmin(d1, d2)
+5


source


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


source


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


source






More articles:


All Articles