How to pass through a list of queries to a pandas dataframe, and output the list of results?
<p>When selecting rows whose column value <code>column_name</code> equals a scalar, <code>some_value</code>, we use <code>==:</code></p>

<pre><code>df.loc[df['column_name'] == some_value]
</code></pre>

<p>or use <code>.query()</code> </p>

<pre><code>df.query('column_name == some_value')
</code></pre>

<p>In a concrete example:</p>

<pre><code>import pandas as pd
import numpy as np
df = pd.DataFrame({'Col1': 'what are men to rocks and mountains'.split(),
                   'Col2': 'the curves of your lips rewrite history.'.split(),
                   'Col3': np.arange(7),
                   'Col4': np.arange(7) * 8})

print(df)

         Col1      Col2  Col3  Col4
0       what       the     0     0
1        are    curves     1     8
2        men        of     2    16
3         to      your     3    24
4      rocks      lips     4    32
5        and   rewrite     5    40
6  mountains  history      6    48
</code></pre>

<p>A query could be</p>

<pre><code>rocks_row = df.loc[df['Col1'] == "rocks"]
</code></pre>

<p>which outputs</p>

<pre><code>print(rocks_row)
    Col1  Col2  Col3  Col4
4  rocks  lips     4    32
</code></pre>

<p>I would like to pass through a list of values to query against a dataframe, which outputs a list of "correct queries". </p>

<p>The queries to execute would be in a list, e.g.</p>

<pre><code>list_match = ['men', 'curves', 'history']
</code></pre>

<p>which would output all rows which meet this condition, i.e. </p>

<pre><code>matches = pd.concat([df1, df2, df3]) 
</code></pre>

<p>where </p>

<pre><code>df1 = df.loc[df['Col1'] == "men"]

df2 = df.loc[df['Col1'] == "curves"]

df3 = df.loc[df['Col1'] == "history"]
</code></pre>

<p>My idea would be to create a function that takes in a </p>

<pre><code>output = []
def find_queries(dataframe, column, value, output):
    for scalar in value: 
        query = dataframe.loc[dataframe[column] == scalar]]
        output.append(query)    # append all query results to a list
    return pd.concat(output)    # return concatenated list of dataframes
</code></pre>

<p>However, this appears to be exceptionally slow, and doesn't actually take advantage of the pandas data structure. What is the "standard" way to pass through a list of queries through a pandas dataframe? </p>

<p>EDIT: How does this translate into "more complex" queries in pandas? e.g. <code>where</code> with an HDF5 document? </p>

<pre><code>df.to_hdf('test.h5','df',mode='w',format='table',data_columns=['A','B'])

pd.read_hdf('test.h5','df')

pd.read_hdf('test.h5','df',where='A=["foo","bar"] &amp; B=1')
</code></pre>

<p>If I understood your question correctly you can do it either using boolean indexing as <a href="http://stackoverflow.com/a/39989023/5741205">@uhjish has already shown in his answer</a> or using <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#the-query-method-experimental" rel="nofollow">query()</a> method:</p>

<pre><code>In [30]: search_list = ['rocks','mountains']

In [31]: df
Out[31]:
        Col1      Col2  Col3  Col4
0       what       the     0     0
1        are    curves     1     8
2        men        of     2    16
3         to      your     3    24
4      rocks      lips     4    32
5        and   rewrite     5    40
6  mountains  history.     6    48
</code></pre>

<p><code>.query()</code> method:</p>

<pre><code>In [32]: df.query('Col1 in @search_list and Col4 &gt; 40')
Out[32]:
        Col1      Col2  Col3  Col4
6  mountains  history.     6    48

In [33]: df.query('Col1 in @search_list')
Out[33]:
        Col1      Col2  Col3  Col4
4      rocks      lips     4    32
6  mountains  history.     6    48
</code></pre>

<p>using boolean indexing:</p>

<pre><code>In [34]: df.ix[df.Col1.isin(search_list) &amp; (df.Col4 &gt; 40)]
Out[34]:
        Col1      Col2  Col3  Col4
6  mountains  history.     6    48

In [35]: df.ix[df.Col1.isin(search_list)]
Out[35]:
        Col1      Col2  Col3  Col4
4      rocks      lips     4    32
6  mountains  history.     6    48
</code></pre>

<p><strong>UPDATE:</strong> using function:</p>

<pre><code>def find_queries(df, qry, debug=0, **parms):
    if debug:
        print('[DEBUG]: Query:\t' + qry.format(**parms))
    return df.query(qry.format(**parms))

In [31]: find_queries(df, 'Col1 in {Col1} and Col4 &gt; {Col4}', Col1='@search_list', Col4=40)
    ...:
Out[31]:
        Col1      Col2  Col3  Col4
6  mountains  history.     6    48

In [32]: find_queries(df, 'Col1 in {Col1} and Col4 &gt; {Col4}', Col1='@search_list', Col4=10)
Out[32]:
        Col1      Col2  Col3  Col4
4      rocks      lips     4    32
6  mountains  history.     6    48
</code></pre>

<p>including debugging info (print query):</p>

<pre><code>In [40]: find_queries(df, 'Col1 in {Col1} and Col4 &gt; {Col4}', Col1='@search_list', Col4=10, debug=1)
[DEBUG]: Query: Col1 in @search_list and Col4 &gt; 10
Out[40]:
        Col1      Col2  Col3  Col4
4      rocks      lips     4    32
6  mountains  history.     6    48
</code></pre>

python python-3.x pandas dataframe