CODEWITHSUNDEEP
python-3.xpandas
fukiburi
fukiburi

Reputation: 643

Filter data iteratively in Python data frame

I'm wondering about existing pandas functionalities, that I might not been able to find so far.

Bascially, I have a data frame with various columns. I'd like to select specific rows depending on the values of certain colums (FYI: i was interested in the value of column D, that had several parameters described in A-C).

E.g. I want to know which row(s) have A==1 & B==2 & C==5?

df
   A  B  C  D
0  1  2  4  a
1  1  2  5  b
2  1  3  4  c

df_result
1  1  2  5  b

So far I have been able to basically reduce this:

import pandas as pd

df = pd.DataFrame({'A': [1,1,1],
                   'B': [2,2,3],
                   'C': [4,5,4],
                   'D': ['a', 'b', 'c']})
df_A = df[df['A'] == 1]
df_B = df_A[df_A['B'] == 2]
df_C = df_B[df_B['C'] == 5]

To this:

parameter = [['A', 1],
             ['B', 2],
             ['C', 5]]

df_filtered = df
for x, y in parameter:
    df_filtered = df_filtered[df_filtered[x] == y]

which yielded the same results. But I wonder if there's another way? Maybe without loop in one line?

Upvotes: 0

Views: 1914

Answers (2)

Patrick the Cat
Patrick the Cat

Reputation: 2158

Just for the information if others are interested, I would have done it this way:

import numpy as np
matched = np.all([df[vn] == vv for vn, vv in parameters], axis=0)
df_filtered = df[matched]

But I like the query function better, now that I have seen it @John Galt.

Upvotes: 0

Zero
Zero

Reputation: 76917

You could use query() method to filter data, and construct filter expression from parameters like

In [288]: df.query(' and '.join(['{0}=={1}'.format(x[0], x[1]) for x in parameter]))
Out[288]:
   A  B  C  D
1  1  2  5  b

Details

In [296]: df
Out[296]:
   A  B  C  D
0  1  2  4  a
1  1  2  5  b
2  1  3  4  c

In [297]: query = ' and '.join(['{0}=={1}'.format(x[0], x[1]) for x in parameter])

In [298]: query
Out[298]: 'A==1 and B==2 and C==5'

In [299]: df.query(query)
Out[299]:
   A  B  C  D
1  1  2  5  b

Upvotes: 1

Related Questions