ENH: Make DataFrame.filter accept filters in new formats

[datapythonista]

I think it'd be very nice for users to get this working:

df.filter(df["age"] > 18)  # same as `df[df["age"] > 18`

df.filter("age > 18")  # same as `df.query("age > 18")`, I think `.query` should be deprecated if this is implemented in `.filter`

df.filter(lambda df: df["age"] > 18)  # same as `df[df['age'].apply(lambda x: x > 18)]`, useful for method chaining

I think implementing this is reasonably simple. I think the main challenge is how to design the API in a way that filter can be intuitive and still work with the current parameters. And particularly, keeping backward compatibility. But personally, I think this would be so useful, that worth finding a solution.

CC: @rhshadrach

[rhshadrach]

Partial proposal:

• Accept items (will maybe want to rename this argument?) of type:
  • Series (will align on index)
  • Non-Series list-likes (must be same length as df)
  • strings a la query
  • UDFs to be discussed.
• Deprecate like, regex; offer no alternatives.
• Deprecate axis=1 but add DataFrame.select (somewhat talked about in ENH: Improve Filter function with Filter_Columns and Filter_Rows #55289).

For UDFs, it seems to me that the usage in the OP can be readily handled by pipe. I would more expect passing a UDF to filter would operate row-wise similar to apply(..., by_row=True).

Another question is how strict we are on the values that will be filtered. Do we require these to be bool/np.bool_, or do we allow any value an internally pandas will evaluate the truthyness of it. I would lean toward the latter.

[datapythonista]

I like the idea.

If I understand correctly, the main use of df.filter(cond) where cond is a Series will be equivalent to now use df[cond]. I think implementing the like and regex behaviors would be trivial with df.filter(df["col"].str.contains("xxx")) and same for regex, right? It does feel we're offering a very reasonable alternative.

I see your point for using .pipe to filter, and in a way kind of agree. But it feels like df.filter(lambda x: x["age"] > 18) will be together with df.filter(df["age"] > 18) the most common used case by far. While df.pipe(lambda x: x[x["age"] > 18) may seem a reasonable alternative, I think it will really make users' life easier to support the former, as I think it's way more intuitive.

In any case, what you propose seems like a great improvement.

[rhshadrach]

I think implementing the like and regex behaviors would be trivial with df.filter(df["col"].str.contains("xxx")) and same for regex, right? It does feel we're offering a very reasonable alternative.

Agreed - I should have said no new alternatives. 😆

For UDFs, one reason not to have df.filter(lambda x: x["age"] > 18) operate by row is that it is effectively a transpose (x being a Series means it can only have one dtype), one of the behaviors I would love to remove from pandas across the board. Another is that agg, apply, transform all pass columns (vertical) objects into the UDF. While it doesn't make sense for filter to act column-by-column, passing the entire DataFrame seems closer in behavior than operating horizontally.

However I do not find it intuitive that in df.filter(lambda x: x["age"] > 18) the x is the same as df. I agree in the utility of having this for method chaining, but I immediately think x as being a component (element / column / row) of df instead of the entire thing. Perhaps that's just me?

A bit of restatement of my previous post, but it seems like df.filter(lambda x: ...) acting by row provides new functionality otherwise not readily available (I think?) where as having x be all of df is very close to duplicating pipe.

Finally, if we are to have x be the same as df in this case, what is the validation on the result? Must it be a Series with the same index as df, or are we going to allow alignment. Can users returns list-likes of the same length?

Overall, I lean toward operate by-row here, but not strongly.

I think .query should be deprecated if this is implemented in .filter

I agree, but desire the deprecation would be slow. That is, first introduce filter and change the docs to discourage the use of query. Then after 1 or 2 years, start the deprecation process.

cc @pandas-dev/pandas-core for any thoughts.

[simonjayhawkins]

Overall, I lean toward operate by-row here, but not strongly.

DataFrame.filter does not filter a Dataframe on its contents, the filter is applied to the labels of the index. The suggestion in the OP is to essentially add value based conditional filtering to this method.

If you operate by row, (or by column if the axis argument is retained), then if you passed a Series with the Series.name set to the index label then it would be easier to filter based on the index label and thereby potentially justify the removal of like and regex and offer no alternatives?

[datapythonista]

I see your point @rhshadrach, and I think what you propose is very reasonable and maybe even thr best option in theory.

In practice, I would be very surprised if most users don't find the pyspark-like API of the function receiving the whole dataframe more intuitive. See this example in their docs:

df.filter(df.age > 3).show()

We can't compare directly with a lazy API, but I think what I propose is quite similar to this.

Also, it was discussed before about adding pandas.col("my_col") to avoid the lambda. I guess that would look like:

df.filter(pd.col('age') > 3)

Personally if filter will accept both this expression and a lambda, I think it's way more clear and intuitive that the lambda works the way I described.

Let's see what other people think, maybe what's clear and intuitive to me it's not to others.

[Dr-Irv]

Maybe I'm missing something, but why deprecate query(). I have LOTS of code that uses that.

Why not leave filter as is - it operates on labels - and maybe expand query() to take expressions as proposed here.

So that df.query(df["age"] > 18) and df.query("age > 18") would do the same thing

[datapythonista]

That's a reasonable option. I think filter is more clear, and is what everybody else is using. If we were to implement the API from scratch now, I think it would be the obvious choice. For backward compatibility query may be better, and we can surely consider it. But I would rather have a very long deprecation timeline, than keep the API IMHO wrong because of a choice we did that now is not ideal.