Getting Started with pandasql in Rodeo: Running SQL on Pandas DataFrames

Getting Started with pandasql in Rodeo

pandasql is a small (358‑line) Python library that lets you execute SQL statements directly on pandas DataFrames, offering a familiar SQL interface for users coming from R's sqldf or a SQL background.

01. Download Rodeo

Download Rodeo, an open‑source, free IDE for Mac, Windows, or Linux, from the Yhat website. Rodeo provides a RStudio‑like experience for Python development.

02. Install pandasql

Install pandasql using Rodeo’s package manager by searching for pandasql and clicking Install, or install from the command line:

! pip install pandasql

03. View Built‑in Datasets

pandasql includes two built‑in datasets:

meat : US Department of Agriculture data on livestock, dairy, and poultry production.

births : United Nations statistics on monthly live births.

Run the provided code snippet in Rodeo to load and display these datasets.

04. Plotting

Plots generated by pandasql appear both in the console and in the Plot tab (bottom‑right). They can be popped out to a separate window for better visibility on multiple monitors.

05. Basic Usage

Write SQL statements that treat DataFrames as tables. pandasql creates a temporary SQLite database, loads the DataFrames, runs the query, and returns the result as a new DataFrame.

06. Aggregation

pandasql supports aggregation functions and allows column aliases or column numbers in GROUP BY clauses.

07. locals() and globals()

To avoid repeatedly passing locals() when executing many queries, add a helper function that sets globals() for the session.

08. Joins

You can join DataFrames using standard SQL join syntax.

09. WHERE Clause

Standard WHERE conditions are supported.

10. Full SQL Capability

Because pandasql is powered by SQLite, most SQL features—including subqueries, ordering, grouping, functions, and UNION—are available.

Final Thoughts

pandas is an incredibly powerful data‑analysis tool, and pandasql provides a familiar SQL interface that can accelerate learning for Python and pandas newcomers, especially those transitioning from R's sqldf.