ENH: Make DataFrame.filter accept filters in new formats

[datapythonista]

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

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.

[rhshadrach]

Why not leave filter as is - it operates on labels

Because it's at odds with other DataFrame libraries.

• PySpark: https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrame.filter.html
• Polars: https://docs.pola.rs/api/python/stable/reference/dataframe/api/polars.DataFrame.filter.html
• ibis: https://ibis-project.org/reference/expression-tables.html#ibis.expr.types.relations.Table.filter

The exceptions are Modin and dask, but I think they were designed to model the pandas API.

In addition, I would argue query is an odd choice of a name for a filtering method.

I'm fine with leaving query as it is for a long time, including indefinitely.

[Dr-Irv]

Why not leave filter as is - it operates on labels

Because it's at odds with other DataFrame libraries.

But those libraries were introduced after pandas ! So shouldn't THEY be modifying their API's? (I say this somewhat facetiously)

In addition, I would argue query is an odd choice of a name for a filtering method.

Then maybe you think that SQL (Structured Query Language) should be called SFL (Structured Filtering Language) ? (definitely said facetitously)

To me query is appropriate if you think of it from the perspective of how SQL works.

[datapythonista]

For a method that returns a subset of rows based on a condition, I think the standard terminology is filter. Query seems more appropriate for a more complex expression that can get data doing operations that not only involve a filter. I think SQL is consistent with this, since it allows to do more than the WHERE clause. And I think query feels inappropriate. df.where would be consistent with SQL, but to me filter is clearly the right choice.

[rhshadrach]

To me query is appropriate if you think of it from the perspective of how SQL works.

Queries in SQL can do so much more than filter. DataFrame.query can only filter. I think this is supporting my contention that query is an odd name.

[simonjayhawkins]

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

That makes sense to me as we would not be mixing label based "filtering" with value based "filtering"

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.

This also makes sense to me.

So the issue is how to make this transition. If we don't mix the label based and value based "filtering" this surely makes the transition path more difficult.

I'm still not clear how we keep the current label based filtering functionality if we "Deprecate like, regex; offer no alternatives." When we deprecate we say something like "x is deprecated. use ... instead". @rhshadrach can you clarify what the ... would be?

[datapythonista]

When we deprecate we say something like "x is deprecated. use ... instead". @rhshadrach can you clarify what the ... would be?

df.filter(df["col"].index.str.contains("xxx")) (or same with square brackets)... this was discussed above in one of the many replies, Richard meant no new specific alternative, and all the existing filter method funcionality is already possible (and personally I'd bet that the alternative using a boolean mask based on the index attribute may already be more popular than the filter method.