Skip to content

Final projection and Pandas cleanup

Problem

  1. How to return exactly the requested output columns in the requested order?

Notions: Final projection and Pandas cleanup

SQL and Pandas syntax

SELECT
    old_col AS new_col,
    another_col
FROM t
ORDER BY new_col ASC;

out = (
    df.rename(columns={"old_col": "new_col"})
    [["new_col", "another_col"]]
    .sort_values("new_col")
    .reset_index(drop=True)
)

Useful Pandas finishers:

out = out.rename(columns={"old_name": "new_name"})

out = out[["col1", "col2"]]

out = out.sort_values(
    ["col1", "col2"],
    ascending=[True, False],
    na_position="last"
)

out = out.reset_index(drop=True)

Example

import sqlite3
import pandas as pd

## SQL

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

con.executescript("""
CREATE TABLE raw_scores (
    person TEXT,
    points INTEGER,
    date TEXT
);

INSERT INTO raw_scores VALUES
    ('Ben', 80, '2024-01-01'),
    ('Ava', 95, '2024-01-02'),
    ('Cam', 70, '2024-01-03');
""")

sql = """
-- Rename raw_scores columns and sort from highest to lowest.
SELECT
    person AS name,
    points AS score
FROM raw_scores
ORDER BY score DESC, name ASC;
"""

pd.read_sql_query(sql, con)
#   name  score
# 0  Ava     95
# 1  Ben     80
# 2  Cam     70

## Pandas

raw_scores = pd.DataFrame({
    "person": ["Ben", "Ava", "Cam"],
    "points": [80, 95, 70],
    "date": ["2024-01-01", "2024-01-02", "2024-01-03"]
})

(
    raw_scores.rename(columns={
        "person": "name",
        "points": "score"})
    [["name", "score"]]
    .sort_values(["score", "name"], ascending=[False, True])
)
#   name  score
# 1  Ava     95
# 0  Ben     80
# 2  Cam     70

(
    raw_scores.rename(columns={
        "person": "name",
        "points": "score"})
    [["name", "score"]]
    .sort_values(["score", "name"], ascending=[False, True])
    .reset_index(drop=True)
)
#   name  score
# 0  Ava     95
# 1  Ben     80
# 2  Cam     70