pandas: filter rows of DataFrame with operator chaining

Just want to add a demonstration using loc to filter not only by rows but also by columns and some merits to the chained operation.

The code below can filter the rows by value.

df_filtered = df.loc[df['column'] == value]

By modifying it a bit you can filter the columns as well.

df_filtered = df.loc[df['column'] == value, ['year', 'column']]

So why do we want a chained method? The answer is that it is simple to read if you have many operations. For example,

res =  df\
    .loc[df['station']=='USA', ['TEMP', 'RF']]\
    .groupby('year')\
    .agg(np.nanmean)

This solution is more hackish in terms of implementation, but I find it much cleaner in terms of usage, and it is certainly more general than the others proposed.

https://github.com/toobaz/generic_utils/blob/master/generic_utils/pandas/where.py

You don't need to download the entire repo: saving the file and doing

from where import where as W

should suffice. Then you use it like this:

df = pd.DataFrame([[1, 2, True],
                   [3, 4, False], 
                   [5, 7, True]],
                  index=range(3), columns=['a', 'b', 'c'])
# On specific column:
print(df.loc[W['a'] > 2])
print(df.loc[-W['a'] == W['b']])
print(df.loc[~W['c']])
# On entire - or subset of a - DataFrame:
print(df.loc[W.sum(axis=1) > 3])
print(df.loc[W[['a', 'b']].diff(axis=1)['b'] > 1])

A slightly less stupid usage example:

data = pd.read_csv('ugly_db.csv').loc[~(W == '$null$').any(axis=1)]

By the way: even in the case in which you are just using boolean cols,

df.loc[W['cond1']].loc[W['cond2']]

can be much more efficient than

df.loc[W['cond1'] & W['cond2']]

because it evaluates cond2 only where cond1 is True.

DISCLAIMER: I first gave this answer elsewhere because I hadn't seen this.

So the way I see it is that you do two things when sub-setting your data ready for analysis.

• get rows
• get columns

Pandas has a number of ways of doing each of these and some techniques that help get rows and columns. For new Pandas users it can be confusing as there is so much choice.

Do you use iloc, loc, brackets, query, isin, np.where, mask etc...

Method chaining

Now method chaining is a great way to work when data wrangling. In R they have a simple way of doing it, you select() columns and you filter() rows.

So if we want to keep things simple in Pandas why not use the filter() for columns and the query() for rows. These both return dataframes and so no need to mess-around with boolean indexing, no need to add df[ ] round the return value.

So what does that look like:-

df.filter(['col1', 'col2', 'col3']).query("col1 == 'sometext'")

You can then chain on any other methods like groupby, dropna(), sort_values(), reset_index() etc etc.

By being consistent and using filter() to get your columns and query() to get your rows it will be easier to read your code when coming back to it after a time.

But filter can select rows?

Yes this is true but by default query() get rows and filter() get columns. So if you stick with the default there is no need to use the axis= parameter.

query()

query() can be used with both and/or &/| you can also use comparison operators > , < , >= , <=, ==, !=. You can also use Python in, not in.

You can pass a list to query using @my_list

Some examples of using query to get rows

df.query('A > B')

df.query('a not in b')

df.query("series == '2206'")

df.query("col1 == @mylist")

df.query('Salary_in_1000 >= 100 & Age < 60 & FT_Team.str.startswith("S").values')

filter()

So filter is basicly like using bracket df[] or df[[]] in that it uses the labels to select columns. But it does more than the bracket notation.

filter has like= param so as to help select columns with partial names.

df.filter(like='partial_name',)

filter also has regex to help with selection

df.filter(regex='reg_string')

So to sum up this way of working might not work for ever situation e.g. if you want to use indexing/slicing then iloc is the way to go. But this does seem to be a solid way of working and can simplify your workflow and code.

You can also leverage the numpy library for logical operations. Its pretty fast.

df[np.logical_and(df['A'] == 1 ,df['B'] == 6)]