python - comparing series/dataframe with multi-index dataframe
<p>I would like to compare (i.e. > or &lt;) the values in one column of a dataframe, which has a single, non-unique index (<code>df1</code>), to the values of another dataframe (<code>df2</code>) which has a unique, multi-index. The comparison should be such that the values in <code>df1</code> are matched against each value in <code>df2</code> with the corresponding index-value of the first level. </p>

<p>E.g. if the value in df1 is smaller than any value in df2 where the first level index matches with the index of the value in df1, the result should display `True'.
An example-code will make this clear</p>

<pre><code>index_a = [1,2,2,3,3,3]
index_b = [0,0,1,0,1,2]
index = pd.MultiIndex.from_arrays([index_a,index_b], names=('a','b'))
df1 = pd.DataFrame(np.random.rand(4,), index = [1,2,3,3], columns=['p'])    
&gt;&gt;&gt; df1
          p
a          
1  0.672379
2  0.130578
3  0.128918
3  0.346115

df2 = pd.DataFrame(np.random.rand(6,), index=index, columns=['p'])
&gt;&gt;&gt; df2
            p
a b          
1 0  0.187448
2 0  0.596792
  1  0.075301
3 0  0.784842
  1  0.256178
  2  0.691007
</code></pre>

<p>What I want can be achived as follows:</p>

<pre><code>dfexp = df2.unstack('b')
&gt;&gt;&gt; dfexp
          p                    
b         0         1         2
a                              
1  0.187448       NaN       NaN
2  0.596792  0.075301       NaN
3  0.784842  0.256178  0.691007

&gt;&gt;&gt; comp = dfexp.ge(df1.p,axis=0)
&gt;&gt;&gt; comp
       p              
b      0      1      2
a                     
1  False  False  False
2   True  False  False
3   True   True   True
3   True  False   True
&gt;&gt;&gt; comp.any(axis=1)
a
1    False
2     True
3     True
3     True
dtype: bool
</code></pre>

<p><strong>Can this be achived without unstacking <code>df2</code>?</strong> The problem is, that the second-level labels can be quite large for some of the first-level indices, making the operation very slow and the <code>expdf</code> dataframe unnecessarily large. At the same time the index of <code>df1</code> can be very large as well, such that I would like to avoid to solve the task by looping over that index unless that loop could be made very, very fast.</p>