Polars Dataframes

pip install polars
# Optional extras:
pip install polars[all]          # All I/O backends
pip install polars[pandas]       # Pandas interop
pip install polars[numpy]        # NumPy interop
pip install connectorx sqlalchemy  # Database connectivity

import polars as pl

# Create DataFrame
df = pl.DataFrame({
    "name": ["Alice", "Bob", "Charlie", "Diana"],
    "dept": ["Sales", "Eng", "Sales", "Eng"],
    "salary": [70000, 85000, 72000, 90000],
})

# Expression-based pipeline
result = (
    df.filter(pl.col("salary") > 71000)
    .with_columns(bonus=pl.col("salary") * 0.1)
    .group_by("dept")
    .agg(
        pl.col("salary").mean().alias("avg_salary"),
        pl.len().alias("count"),
    )
)
print(result)
# shape: (2, 3)
# ┌───────┬────────────┬───────┐
# │ dept  ┆ avg_salary ┆ count │
# ├───────┼────────────┼───────┤
# │ Eng   ┆ 87500.0    ┆ 2     │
# │ Sales ┆ 72000.0    ┆ 1     │
# └───────┴────────────┴───────┘

import polars as pl

df = pl.DataFrame({
    "id": [1, 2, 3, 4, 5],
    "name": ["Alice", "Bob", "Charlie", "Diana", "Eve"],
    "age": [25, 30, 35, 28, 32],
    "score": [88.5, 92.0, 76.3, 95.1, 84.7],
})

# Select columns (with computed expressions)
selected = df.select(
    "name",
    pl.col("age"),
    (pl.col("score") / 100).alias("score_pct"),
)
print(selected.shape)  # (5, 3)

# Filter rows (multiple conditions → implicit AND)
filtered = df.filter(
    pl.col("age") > 27,
    pl.col("score") > 80,
)
print(filtered.shape)  # (3, 4) — Bob, Diana, Eve

# Add columns (preserves existing)
enriched = df.with_columns(
    grade=pl.when(pl.col("score") >= 90).then(pl.lit("A"))
           .when(pl.col("score") >= 80).then(pl.lit("B"))
           .otherwise(pl.lit("C")),
    age_months=pl.col("age") * 12,
)
print(enriched.columns)
# ['id', 'name', 'age', 'score', 'grade', 'age_months']

# Sort
df.sort("score", descending=True).head(3)

import polars as pl

sales = pl.DataFrame({
    "region": ["East", "West", "East", "West", "East", "West"],
    "product": ["A", "A", "B", "B", "A", "B"],
    "revenue": [100, 150, 200, 180, 120, 210],
    "units": [10, 15, 20, 18, 12, 21],
})

# Basic group_by
summary = sales.group_by("region").agg(
    pl.col("revenue").sum().alias("total_rev"),
    pl.col("revenue").mean().alias("avg_rev"),
    pl.len().alias("n_transactions"),
)
print(summary)

# Multiple keys + conditional aggregation
by_rp = sales.group_by("region", "product").agg(
    pl.col("revenue").sum(),
    (pl.col("units") > 15).sum().alias("large_orders"),
)
print(by_rp)

# Window functions with over() — add group stats without collapsing rows
enriched = sales.with_columns(
    region_avg=pl.col("revenue").mean().over("region"),
    rank_in_region=pl.col("revenue").rank(descending=True).over("region"),
    pct_of_region=pl.col("revenue") / pl.col("revenue").sum().over("region"),
)
print(enriched.select("region", "product", "revenue", "region_avg", "rank_in_region"))

import polars as pl

customers = pl.DataFrame({
    "cid": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Charlie", "Diana"],
})
orders = pl.DataFrame({
    "oid": [101, 102, 103, 104],
    "cid": [1, 2, 1, 5],
    "amount": [100, 200, 150, 300],
})

# Inner join — only matching rows
inner = customers.join(orders, on="cid", how="inner")
print(inner.shape)  # (3, 4) — cid 1 (×2), cid 2

# Left join — all left rows, nulls where no match
left = customers.join(orders, on="cid", how="left")
print(left.shape)  # (4, 4) — Charlie and Diana have null amount

# Anti join — left rows WITHOUT a match in right
no_orders = customers.join(orders, on="cid", how="anti")
print(no_orders["name"].to_list())  # ['Charlie', 'Diana']

# Join on different column names
customers.join(orders, left_on="cid", right_on="cid", suffix="_order")

# Asof join — match to nearest timestamp (time-series alignment)
quotes = pl.DataFrame({
    "time": [1.0, 2.0, 3.0, 4.0],
    "price": [100, 101, 102, 103],
}).cast({"time": pl.Float64})

trades = pl.DataFrame({
    "time": [1.5, 3.2],
    "qty": [50, 75],
}).cast({"time": pl.Float64})

result = trades.join_asof(quotes, on="time", strategy="backward")
print(result)
# time=1.5 matched price=100, time=3.2 matched price=102

import polars as pl

# --- Pivot (long → wide) ---
long = pl.DataFrame({
    "date": ["Jan", "Jan", "Feb", "Feb"],
    "product": ["A", "B", "A", "B"],
    "sales": [100, 150, 120, 160],
})
wide = long.pivot(values="sales", index="date", columns="product")
print(wide)
# date | A   | B
# Jan  | 100 | 150
# Feb  | 120 | 160

# --- Unpivot (wide → long) ---
back_to_long = wide.unpivot(
    index="date", on=["A", "B"],
    variable_name="product", value_name="sales",
)
print(back_to_long.shape)  # (4, 3)

# --- Explode list columns ---
nested = pl.DataFrame({
    "id": [1, 2],
    "tags": [["a", "b", "c"], ["d", "e"]],
})
flat = nested.explode("tags")
print(flat.shape)  # (5, 2)

import polars as pl

# --- CSV ---
df = pl.read_csv("data.csv")
df.write_csv("output.csv")

# --- Parquet (recommended for performance) ---
df = pl.read_parquet("data.parquet")
df.write_parquet("output.parquet", compression="zstd")

# --- JSON / NDJSON ---
df = pl.read_ndjson("data.ndjson")
df.write_ndjson("output.ndjson")

# --- Excel ---
df = pl.read_excel("data.xlsx", sheet_name="Sheet1")
df.write_excel("output.xlsx")

# --- Lazy scan (preferred for large files) ---
lf = pl.scan_csv("large.csv")
result = lf.filter(pl.col("value") > 0).select("id", "value").collect()
print(result.shape)

# --- Database ---
df = pl.read_database_uri(
    "SELECT * FROM users WHERE age > 25",
    uri="postgresql://user:pass@localhost/db",
)

# --- Cloud storage (S3, GCS, Azure) ---
df = pl.read_parquet("s3://bucket/data.parquet")
df = pl.scan_parquet("gs://bucket/data/*.parquet").collect()

# --- Partitioned Parquet (Hive-style) ---
df.write_parquet("output_dir", partition_by=["year", "month"])
lf = pl.scan_parquet("output_dir/**/*.parquet")

import polars as pl
from datetime import date

df = pl.DataFrame({
    "text": ["Hello World", "foo bar", "POLARS"],
    "dt": [date(2023, 1, 15), date(2023, 6, 30), date(2024, 12, 1)],
    "values": [[1, 2, 3], [4, 5], [6]],
})

# String operations
strings = df.select(
    lower=pl.col("text").str.to_lowercase(),
    length=pl.col("text").str.len_chars(),
    contains_o=pl.col("text").str.contains("o"),
    split=pl.col("text").str.split(" "),
)
print(strings)

# Datetime operations
dates = df.select(
    year=pl.col("dt").dt.year(),
    month=pl.col("dt").dt.month(),
    weekday=pl.col("dt").dt.weekday(),
    quarter=pl.col("dt").dt.quarter(),
)
print(dates)

# List operations
lists = df.select(
    list_len=pl.col("values").list.len(),
    list_sum=pl.col("values").list.sum(),
    first=pl.col("values").list.first(),
)
print(lists)

# Conditional expressions (when/then/otherwise)
df = pl.DataFrame({"score": [45, 72, 88, 95, 60]})
result = df.with_columns(
    grade=pl.when(pl.col("score") >= 90).then(pl.lit("A"))
           .when(pl.col("score") >= 80).then(pl.lit("B"))
           .when(pl.col("score") >= 70).then(pl.lit("C"))
           .otherwise(pl.lit("F")),
)
print(result)

# Null handling
df2 = pl.DataFrame({"x": [1, None, 3, None, 5]})
filled = df2.with_columns(
    filled=pl.col("x").fill_null(0),
    forward=pl.col("x").fill_null(strategy="forward"),
    is_null=pl.col("x").is_null(),
)
print(filled)

# Multi-column operations with regex selector
df3 = pl.DataFrame({"val_a": [1, 2], "val_b": [3, 4], "name": ["x", "y"]})
doubled = df3.select(pl.col("^val_.*$") * 2)
print(doubled)

import polars as pl

# Lazy mode: build plan, optimize, then execute
lf = pl.scan_csv("large_dataset.csv")

result = (
    lf
    .select("user_id", "category", "amount", "date")  # projection pushdown
    .filter(pl.col("amount") > 100)                     # predicate pushdown
    .with_columns(pl.col("date").str.to_date())
    .group_by("category")
    .agg(
        pl.col("amount").sum().alias("total"),
        pl.col("user_id").n_unique().alias("unique_users"),
    )
    .sort("total", descending=True)
)

# Inspect the optimized plan
print(result.explain())

# Execute
df = result.collect()
print(df)

# Streaming mode for very large data
lf = pl.scan_parquet("data/*.parquet")
result = (
    lf
    .filter(pl.col("year") >= 2023)
    .group_by("region")
    .agg(pl.col("sales").sum())
    .collect(streaming=True)  # processes in batches
)
print(result)

# Sink directly to file (no full materialization)
lf.filter(pl.col("active")).sink_parquet("filtered_output.parquet")

import polars as pl

# Extract
lf = pl.scan_csv(
    "raw_data.csv",
    dtypes={"id": pl.Int64, "date": pl.Utf8, "amount": pl.Float64},
)

# Transform
cleaned = (
    lf
    .with_columns(pl.col("date").str.to_date("%Y-%m-%d"))
    .filter(pl.col("amount").is_not_null())
    .with_columns(
        year=pl.col("date").dt.year(),
        month=pl.col("date").dt.month(),
        amount_log=pl.col("amount").log(),
    )
    .drop_nulls()
)

# Load
cleaned.collect().write_parquet("clean_data.parquet", compression="zstd")
print("ETL complete")

import polars as pl

# Simulate three data sources
users = pl.DataFrame({
    "uid": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Charlie", "Diana"],
    "region": ["East", "West", "East", "West"],
})
orders = pl.DataFrame({
    "oid": range(1, 7),
    "uid": [1, 1, 2, 3, 3, 3],
    "amount": [100, 200, 150, 50, 75, 125],
})
products = pl.DataFrame({
    "oid": range(1, 7),
    "category": ["Elec", "Books", "Elec", "Books", "Elec", "Elec"],
})

# Join → aggregate
result = (
    orders
    .join(users, on="uid", how="left")
    .join(products, on="oid", how="left")
    .group_by("region", "category")
    .agg(
        pl.col("amount").sum().alias("total"),
        pl.col("amount").mean().alias("avg_order"),
        pl.len().alias("n_orders"),
    )
    .sort("total", descending=True)
)
print(result)

import polars as pl
import pandas as pd

# Convert pandas → polars
pd_df = pd.DataFrame({"col": [1, 2, 3], "group": ["a", "b", "a"]})
pl_df = pl.from_pandas(pd_df)

# Key operation mapping:
# pandas: df["col"]              → polars: df.select("col")
# pandas: df[df["col"] > 1]     → polars: df.filter(pl.col("col") > 1)
# pandas: df.assign(x=...)      → polars: df.with_columns(x=...)
# pandas: df.groupby().agg()    → polars: df.group_by().agg()
# pandas: df.groupby().transform → polars: pl.col(...).over(...)
# pandas: df.merge()            → polars: df.join()
# pandas: df.melt()             → polars: df.unpivot()

# Convert back
pd_result = pl_df.to_pandas()

import polars as pl

df = pl.DataFrame({
    "dept": ["Sales", "Eng", "Sales", "Eng", "Sales", "Eng"],
    "level": ["Jr", "Sr", "Sr", "Jr", "Jr", "Sr"],
    "salary": [50000, 95000, 75000, 70000, 55000, 100000],
})

report = (
    df.group_by("dept", "level")
    .agg(
        pl.col("salary").mean().alias("avg_sal"),
        pl.col("salary").median().alias("med_sal"),
        pl.col("salary").std().alias("std_sal"),
        pl.len().alias("count"),
    )
    .pivot(values="avg_sal", index="dept", columns="level")
    .with_columns(
        diff=pl.col("Sr") - pl.col("Jr"),
    )
)
print(report)

import polars as pl
from pathlib import Path

# Read multiple CSVs with potentially different columns
files = sorted(Path("data/").glob("*.csv"))
dfs = [pl.read_csv(f) for f in files]

# Diagonal concat handles mismatched schemas (fills nulls)
combined = pl.concat(dfs, how="diagonal")
print(f"Combined: {combined.shape}")
print(f"Columns: {combined.columns}")

# Or use lazy scan for Parquet (automatic parallel)
lf = pl.scan_parquet("data/**/*.parquet")
result = lf.filter(pl.col("date") > "2023-01-01").collect()