# The classic pandas dilemma
df = pd.read_csv('big_file.csv')      # 10GB file → Out of Memory
df = df[df['amount'] > 0]              # Must load everything before filtering
result = df.groupby('category').sum()  # Aggregation is painfully slow

import duckdb
import pandas as pd

# Assume you already have a pandas DataFrame
df = pd.read_parquet('sales.parquet')

# Register directly as a table — zero copy
con = duckdb.connect()
con.register('sales', df)

# Process with SQL, convert results back to DataFrame
result = con.execute("""
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total,
        COUNT(*) AS orders
    FROM sales
    WHERE status = 'completed'
    GROUP BY 1
    ORDER BY 1
""").fetchdf()

print(result)

# Query Parquet files from S3 in one step
df_result = con.execute("""
    SELECT * FROM read_parquet('s3://bucket/data/*.parquet')
    WHERE date >= '2026-01-01'
""").fetchdf()

import duckdb
import polars as pl

# Polars DataFrame → DuckDB (via Arrow, zero copy)
pl_df = pl.read_parquet('sales.parquet')
con = duckdb.connect()

# Method A: Pass Arrow table directly
arrow_table = pl_df.to_arrow()
con.register('sales_arrow', arrow_table)

result = con.execute("SELECT category, AVG(amount) FROM sales_arrow GROUP BY 1").fetchdf()

# Method B: Convert DuckDB results directly back to Polars
duckdb_result = con.execute("""
    SELECT category, SUM(amount) AS total_sales
    FROM sales_arrow
    GROUP BY 1
    HAVING total_sales > 10000
    ORDER BY total_sales DESC
""").fetch_arrow_table()

polars_result = pl.from_arrow(duckdb_result)
print(polars_result)

import duckdb

con = duckdb.connect()

# Query large files on disk directly, return only what you need
top_categories = con.execute("""
    SELECT 
        category,
        SUM(amount) AS total_sales,
        COUNT(*) AS order_count,
        AVG(amount) AS avg_order_value
    FROM read_csv_auto('/data/sales_2026.csv', header=true)
    GROUP BY category
    ORDER BY total_sales DESC
    LIMIT 10
""").fetchdf()

print(top_categories)

# ❌ Pandas: Load the entire file into memory first
import pandas as pd
df = pd.read_csv('/data/sales_2026.csv')  # 10GB file → OOM
result = df.groupby('category').agg(
    total_sales=('amount', 'sum'),
    order_count=('amount', 'count'),
    avg_order_value=('amount', 'mean')
).nlargest(10, 'total_sales')

import duckdb

con = duckdb.connect()

# CSV
con.execute("SELECT * FROM read_csv_auto('data.csv')")

# Excel
con.execute("SELECT * FROM read_xlsx('data.xlsx')")

# JSON
con.execute("SELECT * FROM read_json_auto('data.json')")

# Parquet
con.execute("SELECT * FROM read_parquet('data.parquet')")

# Multiple Parquet files auto-merged
con.execute("SELECT * FROM read_parquet('s3://bucket/data/*.parquet')")

# Directly query SQLite database
con.execute("SELECT * FROM sqlite_scan('legacy.db', 'users')")

# Remote MySQL/PostgreSQL queries
con.execute("SELECT * FROM mysql_scan('host=localhost', 'database', 'users')")
con.execute("SELECT * FROM postgres_scan('dbname=sales host=localhost', 'orders')")

import duckdb

con = duckdb.connect()

# Your boss sends an Excel file with 5 worksheets
sheets = con.execute("""
    SELECT sheet_name 
    FROM read_xlsx_auto('monthly_report.xlsx')
""").fetchall()

# Query a specific worksheet
jan_data = con.execute("""
    SELECT * 
    FROM read_xlsx('monthly_report.xlsx', sheet='January')
""").fetchdf()

# Merge multiple months
feb_data = con.execute("""
    SELECT * 
    FROM read_xlsx('monthly_report.xlsx', sheet='February')
""").fetchdf()

combined = jan_data.vstack(feb_data) if hasattr(jan_data, 'vstack') else pd.concat([jan_data, feb_data])

import duckdb
import pandas as pd

# Traditional slow pandas operation
# df.groupby('category').sum() is very slow on large datasets

# Accelerate with DuckDB
con = duckdb.connect()
con.register('df', your_dataframe)

fast_result = con.execute("""
    SELECT 
        category,
        SUM(sales) AS total_sales,
        AVG(sales) AS avg_sales,
        COUNT(DISTINCT user_id) AS unique_users
    FROM df
    GROUP BY category
""").fetchdf()

import duckdb
import polars as pl

con = duckdb.connect()

# Convert Polars DataFrame to Arrow then register
arrow_table = pl_df.to_arrow()
con.register('polars_df', arrow_table)

# Use DuckDB for complex SQL, Polars for column transformations
sql_result = con.execute("""
    WITH monthly AS (
        SELECT 
            DATE_TRUNC('month', date) AS month,
            SUM(amount) AS revenue
        FROM polars_df
        GROUP BY 1
    )
    SELECT 
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 
              / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
    FROM monthly
""").fetch_arrow_table()

final_df = pl.from_arrow(sql_result)