Five methods for Filtering data with multiple conditions in Python (opens in new tab)

TileDB[1] offers an embedded SQL experience for python[2]. We use MariaDB built in embedded mode to allow running sql against TileDB Arrays. This can be combined with pandas.read_sql to load the results directly into a pandas array. I wrote this implementation for the embedded SQL, so happy to answer any questions on it. The embedded SQL in its early stages, but should be fully functional for any queries that MariaDB itself supports. TileDB and the embedded SQL are both open source, TileDB is MIT licensed and the embedded SQL is under GPL.

[1] https://tiledb.com/

[2] https://docs.tiledb.com/developer/api-usage/embedded-sql

(disclosure: TileDB, inc. employee)

I'd love if someone took the next step from Pandas being influenced by R to port dplyr-esque syntax to it.

Isn't that just `pd.DataFrame.query` as outlined in the article? Or do you mean actual full-fat SQL?

You could try dumping your dataframes to an in-memory SQLite instance using the to_sql method and then running queries on that. Not sure how performant the to_sql bit would be, but I'd expect joins in SQLite to be blazing fast compared to Panda's joins.

Something like this:

https://en.wikipedia.org/wiki/Language_Integrated_Query

I do the same without the parenthesis.

My guess is that eval and query have the worst performance, since they need to be interpreted first. As for the other methods, I'm not too sure how they rank. Personally, for style I agree with the first comment on the linked page, which is to split up the conditions for readability:

  f1 = (df[“col1”] == condition1)

  f2 = (df[“col2”] == condition2)

  df[f1 & f2]

I wonder if this could be improved with SQL

If you supply a function to `.loc`, you can use it in pipes pretty easily. E.g.

``` my_dataframe.loc[lambda df: df['col'] > 0.8] ```

Would this help work around your issue?