Skip to content

Conditional logic, conditional aggregation, ratios, and rounding

Problem

  1. How to create categories?
  2. How to count only rows that satisfy a condition?
  3. How to compute percentages?

Notions: Conditional logic, conditional aggregation, ratios, and rounding

SQL and Pandas syntax

SELECT
    id,
    CASE
        WHEN amount >= 100 THEN 'high'
        WHEN amount >= 50 THEN 'medium'
        ELSE 'low'
    END AS amount_category
FROM t;

SELECT
    key,
    SUM(CASE WHEN status = 'ok' THEN 1 ELSE 0 END) AS ok_count,
    ROUND(100.0 * SUM(CASE WHEN status = 'ok' THEN 1 ELSE 0 END) / COUNT(*), 2) AS ok_pct
FROM t
GROUP BY key;

-- PostgreSQL alternatives
SELECT
    key,
    COUNT(*) FILTER (WHERE status = 'ok') AS ok_count,
    ROUND((100.0 * COUNT(*) FILTER (WHERE status = 'ok') / COUNT(*))::numeric, 2) AS ok_pct
FROM t
GROUP BY key;

df["amount_category"] = "low"
df.loc[df["amount"] >= 50, "amount_category"] = "medium"
df.loc[df["amount"] >= 100, "amount_category"] = "high"

df["is_ok"] = df["status"].eq("ok")

out = (
    df.groupby("key", as_index=False)
    .agg(
        ok_count=("is_ok", "sum"),
        total_count=("is_ok", "size"),
        ok_pct=("is_ok", lambda s: round(100 * s.mean(), 2))
    )
)

Example

import sqlite3
import pandas as pd

## SQL

con = sqlite3.connect(":memory:")

con.executescript("""
CREATE TABLE payments (
    id INTEGER,
    user_id INTEGER,
    amount REAL,
    status TEXT
);

INSERT INTO payments VALUES
    (1, 10, 20.0, 'paid'),
    (2, 10, 150.0, 'failed'),
    (3, 20, 75.0, 'paid'),
    (4, 20, 125.0, 'paid');
""")

sql = """
-- Count paid payments and compute the paid percentage per user.
WITH labeled AS (
    SELECT
        *,
        CASE
            WHEN amount >= 100 THEN 'high'
            WHEN amount >= 50 THEN 'medium'
            ELSE 'low'
        END AS amount_category
    FROM payments
)
SELECT
    user_id,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    COUNT(*) AS total_count,
    ROUND(100.0 * SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*), 2) AS paid_pct
FROM labeled
GROUP BY user_id
ORDER BY user_id;
"""

pd.read_sql_query(sql, con)
#    user_id  paid_count  total_count  paid_pct
# 0       10           1            2      50.0
# 1       20           2            2     100.0

## Pandas

payments = pd.DataFrame({
    "id": [1, 2, 3, 4],
    "user_id": [10, 10, 20, 20],
    "amount": [20.0, 150.0, 75.0, 125.0],
    "status": ["paid", "failed", "paid", "paid"]
})

payments["amount_category"] = "low"
payments.loc[payments["amount"] >= 50, "amount_category"] = "medium"
payments.loc[payments["amount"] >= 100, "amount_category"] = "high"
payments["is_paid"] = payments["status"].eq("paid")
payments
#    id  user_id  amount  status amount_category  is_paid
# 0   1       10    20.0    paid             low     True
# 1   2       10   150.0  failed            high    False
# 2   3       20    75.0    paid          medium     True
# 3   4       20   125.0    paid            high     True

(
    payments.groupby("user_id", as_index=False)
    .agg(
        paid_count=("is_paid", "sum"),
        total_count=("is_paid", "size"),
        paid_pct=("is_paid", lambda s: round(100 * s.mean(), 2))
    )
)
#    user_id  paid_count  total_count  paid_pct
# 0       10           1            2      50.0
# 1       20           2            2     100.0