python pandas-possible to compare 3 dfs of same shape using where(max())? is this a masking issue?
<p>I have a dict containing 3 dataframes of identical shape. I would like to create:</p>

<ol>
<li>a 4th dataframe which identifies the largest value from the original 3 at each coordinate - so dic['four'].ix[0,'A'] = MAX( dic['one'].ix[0,'A'], dic['two'].ix[0,'A'], dic['three'].ix[0,'A'] )</li>
<li><p>a 5th with the second largest value</p>

<pre><code>dic = {}
for i in ['one','two','three']:
    dic[i] = pd.DataFrame(np.random.randint(0,100,size=(10,3)), columns=list('ABC'))
</code></pre></li>
</ol>

<p>I cannot figure out how to use .where() to compare the original 3 dfs. Looping through would be inefficient for ultimate data set. </p>

<p>consider the <code>dict</code> <code>dfs</code> which is a dictionary of <code>pd.DataFrame</code>s</p>

<pre><code>import pandas as pd
import numpy as np

np.random.seed([3,1415])
dfs = dict(
    one=pd.DataFrame(np.random.randint(1, 10, (5, 5))),
    two=pd.DataFrame(np.random.randint(1, 10, (5, 5))),
    three=pd.DataFrame(np.random.randint(1, 10, (5, 5))),
)
</code></pre>

<p>the best way to handle this is with a <code>pd.Panel</code> object, which is the higher dimensional object analogous to <code>pd.DataFrame</code>.</p>

<pre><code>p = pd.Panel(dfs)
</code></pre>

<p>then the answers you need are very straighforward</p>

<p><strong><em>max</em></strong><br>
<code>p.max(axis='items')</code> or <code>p.max(0)</code></p>

<p><strong><em>penultimate</em></strong><br>
<code>p.apply(lambda x: np.sort(x)[-2], axis=0)</code></p>

python pandas numpy max where