what would be the pandas equivalent of a complex SQL query (consisting of a subquery)
<p>Can anyone tell me the pandas equivalent of the following SQL query.
Consider <code>sales_data</code> as tablename/pd.DataFrame</p>

<pre><code>SELECT store_id, store_name, sales FROM sales_data WHERE sales = (SELECT max(sales) FROM sales_data WHERE store_location = 'Beijing') and store_location = 'Beijing'
</code></pre>

<p>I know, we can achieve this in 2 steps:</p>

<pre><code>1) df = sales_data[sales_data['store_location'] == 'Beijing'][['store_id', 'store_name', 'sales']]
2) df[df['sales'] == df['sales'].max()]
</code></pre>

<p>But can we achieve it in a single step?? Is there a way.</p>

<p>I wouldn't try to do it in one step in this case, because it might be slower:</p>

<p>Demo:</p>

<p><strong>Data:</strong></p>

<pre><code>In [103]: df
Out[103]:
   a  b  c  x
0  2  1  1  b
1  2  3  2  a
2  4  1  3  c
3  3  2  3  b
4  2  1  4  c
5  1  3  1  c
6  2  3  0  a
7  2  3  2  b
8  4  2  4  a
9  4  1  1  b
</code></pre>

<p><strong>One-step solution:</strong></p>

<pre><code>In [104]: df.ix[(df.x == 'a') &amp; (df.b == df.ix[df.x == 'a', 'b'].max())]
Out[104]:
   a  b  c  x
1  2  3  2  a
6  2  3  0  a
</code></pre>

<p><strong>Timing comparison against 1M rows DF:</strong></p>

<pre><code>In [105]: big = pd.concat([df] * 10**5, ignore_index=True)

In [106]: big.shape
Out[106]: (1000000, 4)

In [111]: %%timeit
   .....: x = big.ix[big.x == 'a']
   .....: x.ix[x.b == x.b.max()]
   .....:
10 loops, best of 3: 189 ms per loop

In [112]: %timeit big.ix[(big.x == 'a') &amp; (big.b == big.ix[big.x == 'a', 'b'].max())]
1 loop, best of 3: 321 ms per loop
</code></pre>

<p><strong>Conclusion:</strong> your 2-step method is almost 2 times faster</p>

<p><strong>OLD <em>incorrect</em> answer:</strong></p>

<pre><code>In [115]: df.ix[df.x == 'a'].nlargest(1, columns=['b'])
Out[115]:
   a  b  c  x
1  2  3  2  a
</code></pre>

<p>NOTE: this answer is incorrect because it will always return only one row, even if there will be multiple rows satisfying this condition: <code>column = max(column)</code></p>

<p>Explanation:</p>

<pre><code>In [114]: df.ix[df.x == 'a']
Out[114]:
   a  b  c  x
1  2  3  2  a
6  2  3  0  a
8  4  2  4  a
</code></pre>

<p>correct answer:</p>

<pre><code>In [116]: df.ix[(df.x == 'a') &amp; (df.b == df.ix[df.x == 'a', 'b'].max())]
Out[116]:
   a  b  c  x
1  2  3  2  a
6  2  3  0  a
</code></pre>

python pandas subquery