Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท IO and Formats

Interoperability: pandas, NumPy, Arrow and SQL

Move data between Polars and pandas, NumPy, Arrow, Python dicts and databases. Learn from_pandas, to_numpy, zero-copy rules, SQLContext and how to read SQL tables.

Polars rarely works alone. Your machine-learning library wants a NumPy array, a colleague's notebook uses pandas, a chart library expects pandas or Arrow, and the data lives in a database. This lesson shows how data moves in and out of Polars, what it costs, and how to run SQL directly on DataFrames.

Converting to pandas, NumPy or Arrow needs those libraries installed (pip install pandas pyarrow numpy). In the browser runner, Polars notices at import time which of them are present, so every example below imports numpy, pandas, pyarrow before polars (this is only needed here, not on a normal installation). Database drivers (read_database, write_database) need network or files and are shown read-only.

From Python objects

You already know pl.DataFrame({...}). More constructors worth knowing:

Python Dicts, rows, lists of dicts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Going out to Python objects is for small results, tests and hand-offs. Loops such as for row in df.iter_rows() are many times slower than expressions; use them for output, not for computation (see fast patterns).

pandas

pl.from_pandas(df) and df.to_pandas() convert in either direction. Polars is column-oriented with Arrow memory, so numeric columns without nulls can often be shared without copying; strings and nulls usually need a copy.

Python pandas round trip

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two pandas habits to remember: Polars has no index, so if the index carries information, reset it first or pass include_index=True. And Polars has real null, while pandas floats use NaN. Differences like these are the subject of the migration chapter.

In this browser build, string columns that were created from a pandas string column cannot be exported again with to_pandas() or to_arrow() (the build panics with "capacity overflow"). On a normal installation this works, and the example above exports only the numeric and boolean columns to stay clear of it.

NumPy

df.to_numpy() returns a 2-D array (a common supertype for all columns); series.to_numpy() returns a 1-D array. pl.DataFrame(np_array, schema=[...]) and pl.Series(np_array) go the other way. NumPy functions also accept Series directly in most cases.

Python NumPy and scikit-learn style hand-off

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If the columns have different types (for example integers and strings), to_numpy() falls back to an object array. Select only the numeric columns first: df.select(pl.col(pl.NUMERIC_DTYPES)), or in newer code df.select(cs.numeric()) with import polars.selectors as cs.

Arrow

Arrow is the lingua franca: Polars is built on Arrow's memory layout, so df.to_arrow() and pl.from_arrow(table) are fast and often zero-copy. Use them to talk to DuckDB, Parquet writers, Spark, or any library that accepts Arrow.

Python Arrow table in, Arrow table out

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same applies to the dataframe interchange protocol and the Arrow C Data Interface: libraries that implement them can read a Polars frame without caring that it is Polars.

Running SQL on DataFrames

Polars includes a SQL front end. pl.SQLContext registers frames as tables, and execute runs a query. The result is a LazyFrame by default, so collect() it (or pass eager=True).

Python SQL on DataFrames

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

SQLContext supports a large subset of SQL: joins, grouping, window functions, CTEs, CASE, many string and date functions. It translates the SQL into the same lazy plan an expression query would use, so you get the same optimizer. This is a gentle bridge for SQL-literate colleagues, and a pragmatic way to port a query. Mix freely: run SQL for the join-and-aggregate part, then continue with expressions.

Databases (read-only)

Polars reads from a database through a connection string or an existing connection. These need a driver, a reachable database, and are not runnable in the browser:

Python
df = pl.read_database_uri(
    "SELECT * FROM orders WHERE amount > 100",
    "postgresql://user:pass@host/shop",
    engine="connectorx",           # or "adbc"
)

df = pl.read_database("SELECT * FROM orders", connection)   # SQLAlchemy / DB-API connection
df.write_database("orders_copy", connection_uri, if_table_exists="replace")

Do as much filtering and aggregation as you can in the SQL, then continue in Polars: moving fewer rows over the wire is the biggest speed-up.

Excel (read-only)

pl.read_excel("book.xlsx", sheet_name="Sales") needs an extra engine such as fastexcel, and df.write_excel("out.xlsx") needs xlsxwriter. Excel reading is best for the last mile; keep your own data in Parquet or CSV.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to a pandas index when you call pl.from_pandas(pdf)?

    Polars has no index concept. The index is discarded by default; include_index=True keeps it as a column.
  2. 2. What does df.to_numpy() return for a frame with only float columns?

    A DataFrame becomes a 2-D array (rows by columns). A single Series gives a 1-D array.
  3. 3. What does ctx.execute('SELECT ...') return by default?

    SQLContext.execute returns a LazyFrame unless you pass eager=True, so the query goes through the lazy optimizer.
  4. 4. Why prefer filtering in the database query when using read_database?

    Transferring rows dominates the cost. Push filters and aggregates into SQL, then finish in Polars.

Summary

  • Build frames from dicts, rows or lists of dicts; export with to_dict, to_dicts, rows, to_list.
  • from_pandas / to_pandas convert; the index is dropped by default and nulls replace NaN-style gaps.
  • to_numpy gives arrays for ML libraries; from_arrow / to_arrow are fast bridges to the Arrow ecosystem.
  • pl.SQLContext and df.sql() run SQL on DataFrames through the lazy optimizer.
  • Database and Excel I/O need drivers and are used from a normal Python environment.

Next: Data Types and Memory