Reference

Appendix F: Python / R / SQL Rosetta Stone

Overview

This appendix maps the most common data-manipulation operations across three ecosystems that practitioners encounter daily: pandas (Python), dplyr/tidyr (R), and SQL (ANSI-compatible, with notes on dialect variations). The goal is a fast lookup — you know what you want to do, you just need the syntax in the language you are currently working in.

Conventions used throughout:

  • The example dataset is a sales table with columns order<em>id, customer</em>id, product, category, amount, quantity, order<em>date, and region.
  • Python snippets assume import pandas as pd and a DataFrame named df.
  • R snippets assume library(dplyr); library(tidyr); library(lubridate) and a tibble named df.
  • SQL snippets target a table named orders. Dialect notes call out PostgreSQL, MySQL, or SQLite where they diverge.

    • Selecting Columns

Choose a specific subset of columns from a dataset.

PYTHON
# pandas
result = df[["order_id", "customer_id", "amount"]]

# equivalently, using .loc
result = df.loc[:, ["order_id", "customer_id", "amount"]]

R
# dplyr
result <- df |> select(order_id, customer_id, amount)

# drop a column with minus-sign
result <- df |> select(-region)

SQL
-- SQL
SELECT order_id, customer_id, amount
FROM orders;

Selecting by pattern — useful when a dataset has many columns:

PYTHON
# pandas: all columns that start with "order"
result = df.loc[:, df.columns.str.startswith("order")]

R
# dplyr: helper functions
result <- df |> select(starts_with("order"))
result <- df |> select(contains("date"))

SQL
-- SQL has no wildcard column selection beyond SELECT *;
-- use a metadata query to build a dynamic column list in your driver code.
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders'
  AND column_name LIKE 'order%';

Filtering Rows

Retain rows that satisfy one or more conditions.

PYTHON
# pandas: boolean indexing
high_value = df[df["amount"] > 500]

# multiple conditions — use & | ~ with parentheses
west_high = df[(df["region"] == "West") & (df["amount"] > 500)]

R
# dplyr
high_value <- df |> filter(amount > 500)
west_high  <- df |> filter(region == "West", amount > 500)

SQL
SELECT *
FROM orders
WHERE amount > 500
  AND region = 'West';

Filtering on a set of values:

PYTHON
# pandas
df[df["category"].isin(["Electronics", "Books"])]

R
df |> filter(category %in% c("Electronics", "Books"))

SQL
SELECT * FROM orders
WHERE category IN ('Electronics', 'Books');

Pitfall: In pandas, chained boolean conditions require parentheses around each sub-expression and bitwise operators (&amp;, |, ~), not Python keywords (and, or, not). Using and raises a ValueError on Series objects.

Sorting Rows

PYTHON
# pandas: ascending by default; descending with ascending=False
df.sort_values("amount", ascending=False)

# multi-column sort
df.sort_values(["region", "amount"], ascending=[True, False])

R
df |> arrange(desc(amount))
df |> arrange(region, desc(amount))

SQL
SELECT * FROM orders
ORDER BY amount DESC;

-- multi-column
SELECT * FROM orders
ORDER BY region ASC, amount DESC;

Mutating / Deriving New Columns

Add or overwrite a column, often as a function of existing columns.

PYTHON
# pandas
df["revenue"] = df["amount"] * df["quantity"]
df["discount"] = df["amount"] * 0.1

# non-destructive version with assign (returns a new DataFrame)
df2 = df.assign(
    revenue=lambda x: x["amount"] * x["quantity"],
    discount=lambda x: x["amount"] * 0.1,
)

R
# dplyr
df <- df |>
  mutate(
    revenue  = amount * quantity,
    discount = amount * 0.1
  )

SQL
-- SQL: computed in SELECT, or stored in a new column via ALTER/UPDATE
SELECT *,
       amount * quantity          AS revenue,
       amount * 0.1               AS discount
FROM orders;

Conditional derivation (equivalent to CASE WHEN / np.where / if</em>else):

PYTHON
import numpy as np
df["tier"] = np.where(df["amount"] >= 1000, "Premium", "Standard")

# or with pd.cut for multiple bins
df["tier"] = pd.cut(
    df["amount"],
    bins=[0, 250, 750, float("inf")],
    labels=["Low", "Mid", "High"],
)

R
df <- df |>
  mutate(tier = case_when(
    amount >= 1000 ~ "Premium",
    amount >= 500  ~ "Mid",
    TRUE           ~ "Standard"
  ))

SQL
SELECT *,
  CASE
    WHEN amount >= 1000 THEN 'Premium'
    WHEN amount >= 500  THEN 'Mid'
    ELSE 'Standard'
  END AS tier
FROM orders;

Grouping and Aggregation

Compute summary statistics per group — the bread-and-butter of analytical queries.

PYTHON
# pandas: groupby + agg
summary = (
    df.groupby("category")
    .agg(
        total_revenue=("amount", "sum"),
        avg_order=("amount", "mean"),
        n_orders=("order_id", "count"),
    )
    .reset_index()
)

R
summary <- df |>
  group_by(category) |>
  summarise(
    total_revenue = sum(amount),
    avg_order     = mean(amount),
    n_orders      = n()
  )

SQL
SELECT
  category,
  SUM(amount)   AS total_revenue,
  AVG(amount)   AS avg_order,
  COUNT(*)      AS n_orders
FROM orders
GROUP BY category;

Filtering after aggregation (HAVING in SQL, filter after summarise in dplyr, boolean mask in pandas):

PYTHON
summary[summary["total_revenue"] > 10000]

R
summary |> filter(total_revenue > 10000)

SQL
SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 10000;

Pitfall: In pandas, groupby by default drops NaN keys. Pass dropna=False to keep them. In SQL, NULL keys are also excluded from GROUP BY results unless you coalesce them first.

Joining Tables

Combine rows from two tables based on a key. The example adds a customers table with customer<em>id and loyalty</em>tier.

PYTHON
# pandas: merge is the universal join
# inner join (default)
merged = df.merge(customers, on="customer_id")

# left join
merged = df.merge(customers, on="customer_id", how="left")

# different key names
merged = df.merge(customers,
                  left_on="customer_id",
                  right_on="cust_id",
                  how="inner")

R
# dplyr: *_join family
merged <- df |> inner_join(customers, by = "customer_id")
merged <- df |> left_join(customers, by = "customer_id")

# different key names
merged <- df |>
  left_join(customers, by = c("customer_id" = "cust_id"))

SQL
-- inner join
SELECT o.*, c.loyalty_tier
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- left join
SELECT o.*, c.loyalty_tier
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Join type summary (use bullet list, not a table):

  • INNER / innerjoin — keep only rows with matching keys in both sides.
  • LEFT / leftjoin — keep all rows from the left; fill unmatched right columns with NULL / NA.
  • RIGHT / rightjoin — keep all rows from the right.
  • FULL OUTER / fulljoin — keep all rows from both; fill gaps with NULL / NA.
  • CROSS — Cartesian product; every row of left paired with every row of right.
  • ANTI / antijoin — rows in left with no match in right (useful for "what's missing" queries).

    • Pivoting and Reshaping

    Wide to Long (unpivot / melt)

PYTHON
# pandas: melt
long = df.melt(
    id_vars=["order_id", "category"],
    value_vars=["amount", "quantity"],
    var_name="metric",
    value_name="value",
)

R
# tidyr: pivot_longer
long <- df |>
  pivot_longer(
    cols      = c(amount, quantity),
    names_to  = "metric",
    values_to = "value"
  )

SQL
-- SQL: UNION ALL is the portable approach
SELECT order_id, category, 'amount'   AS metric, amount   AS value FROM orders
UNION ALL
SELECT order_id, category, 'quantity' AS metric, quantity AS value FROM orders;
-- PostgreSQL also supports UNPIVOT via crosstab or lateral joins.

Long to Wide (pivot)

PYTHON
# pandas: pivot_table
wide = df.pivot_table(
    index="customer_id",
    columns="category",
    values="amount",
    aggfunc="sum",
    fill_value=0,
)

R
# tidyr: pivot_wider
wide <- df |>
  pivot_wider(
    id_cols     = customer_id,
    names_from  = category,
    values_from = amount,
    values_fn   = sum,
    values_fill = 0
  )

SQL
-- SQL: conditional aggregation (portable)
SELECT
  customer_id,
  SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS Electronics,
  SUM(CASE WHEN category = 'Books'        THEN amount ELSE 0 END) AS Books,
  SUM(CASE WHEN category = 'Clothing'     THEN amount ELSE 0 END) AS Clothing
FROM orders
GROUP BY customer_id;
-- PostgreSQL CROSSTAB and SQL Server PIVOT offer cleaner syntax for known categories.

Window Functions

Window functions compute a value for each row using a sliding or partitioned "window" of rows, without collapsing the result like GROUP BY does. They are among the most powerful SQL features and have direct equivalents in pandas and dplyr.

Running Total and Rank

PYTHON
# pandas: transform and expanding/rolling
df = df.sort_values(["customer_id", "order_date"])
df["running_total"] = df.groupby("customer_id")["amount"].cumsum()
df["rank_in_group"] = (
    df.groupby("customer_id")["amount"]
    .rank(method="min", ascending=False)
)

R
df <- df |>
  group_by(customer_id) |>
  arrange(order_date, .by_group = TRUE) |>
  mutate(
    running_total = cumsum(amount),
    rank_in_group = min_rank(desc(amount))
  ) |>
  ungroup()

SQL
SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)
               AS running_total,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC)
               AS rank_in_group
FROM orders;

Lag / Lead

Access the previous or next row's value — essential for time-series deltas.

PYTHON
df["prev_amount"] = df.groupby("customer_id")["amount"].shift(1)
df["next_amount"] = df.groupby("customer_id")["amount"].shift(-1)

R
df <- df |>
  group_by(customer_id) |>
  mutate(
    prev_amount = lag(amount),
    next_amount = lead(amount)
  ) |>
  ungroup()

SQL
SELECT
  order_id,
  customer_id,
  amount,
  LAG(amount,  1) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  LEAD(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;

Row Number (deduplicate / top-N per group)

PYTHON
# pandas: keep the top-1 row per customer by amount
df["rn"] = df.groupby("customer_id")["amount"].rank(method="first", ascending=False)
top1 = df[df["rn"] == 1].drop(columns="rn")

R
df |>
  group_by(customer_id) |>
  slice_max(amount, n = 1) |>
  ungroup()

SQL
-- Using a CTE for clarity
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

String Operations

Contains / Pattern Match

PYTHON
# pandas: Series.str accessor
mask = df["product"].str.contains("Pro", case=False, na=False)
df[mask]

R
df |> filter(str_detect(product, regex("Pro", ignore_case = TRUE)))

SQL
SELECT * FROM orders WHERE product ILIKE '%Pro%';         -- PostgreSQL
SELECT * FROM orders WHERE LOWER(product) LIKE '%pro%';   -- portable

Extract Substring

PYTHON
# pandas
df["sku"] = df["product"].str.extract(r"([A-Z]{2}\d{4})")

R
df <- df |> mutate(sku = str_extract(product, "[A-Z]{2}\\d{4}"))

SQL
-- PostgreSQL
SELECT product, (REGEXP_MATCH(product, '[A-Z]{2}[0-9]{4}'))[1] AS sku
FROM orders;

Replace

PYTHON
df["region"] = df["region"].str.replace("N.", "North ", regex=True)

R
df <- df |> mutate(region = str_replace(region, "N\\.", "North "))

SQL
SELECT REPLACE(region, 'N.', 'North ') AS region FROM orders;  -- literal
-- PostgreSQL regex replace:
SELECT REGEXP_REPLACE(region, 'N\.', 'North ') AS region FROM orders;

Date and Time Operations

Parse and Extract Parts

PYTHON
# pandas: parse once, then use .dt accessor
df["order_date"] = pd.to_datetime(df["order_date"])
df["year"]  = df["order_date"].dt.year
df["month"] = df["order_date"].dt.month
df["dow"]   = df["order_date"].dt.day_name()

R
# lubridate
df <- df |>
  mutate(
    order_date = ymd(order_date),
    year       = year(order_date),
    month      = month(order_date),
    dow        = wday(order_date, label = TRUE)
  )

SQL
SELECT
  order_date,
  EXTRACT(YEAR  FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  TO_CHAR(order_date, 'Day')     AS dow   -- PostgreSQL
FROM orders;
-- MySQL: YEAR(), MONTH(), DAYNAME()
-- SQLite: strftime('%Y', order_date), strftime('%m', ...), strftime('%w', ...)

Date Arithmetic

PYTHON
from datetime import timedelta
df["due_date"] = df["order_date"] + pd.Timedelta(days=30)
df["days_since"] = (pd.Timestamp.today() - df["order_date"]).dt.days

R
df <- df |>
  mutate(
    due_date   = order_date + days(30),
    days_since = as.integer(today() - order_date)
  )

SQL
-- PostgreSQL
SELECT order_date + INTERVAL '30 days' AS due_date,
       CURRENT_DATE - order_date       AS days_since
FROM orders;
-- MySQL: DATE_ADD(order_date, INTERVAL 30 DAY), DATEDIFF(CURDATE(), order_date)
-- SQLite: DATE(order_date, '+30 days'), JULIANDAY('now') - JULIANDAY(order_date)

Deduplication

PYTHON
# pandas: keep first occurrence of duplicate order_id
df_clean = df.drop_duplicates(subset=["order_id"])

# keep the row with the highest amount per customer
df_best = (
    df.sort_values("amount", ascending=False)
    .drop_duplicates(subset=["customer_id"])
)

R
df_clean <- df |> distinct(order_id, .keep_all = TRUE)

df_best <- df |>
  arrange(desc(amount)) |>
  distinct(customer_id, .keep_all = TRUE)

SQL
-- Distinct on one column (PostgreSQL DISTINCT ON)
SELECT DISTINCT ON (order_id) * FROM orders ORDER BY order_id;

-- Top row per customer by amount using ROW_NUMBER
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Missing Values

PYTHON
# pandas: detect, drop, fill
df.isnull().sum()                           # count NAs per column
df_clean = df.dropna(subset=["amount"])     # drop rows where amount is NA
df["amount"] = df["amount"].fillna(0)       # fill with constant
df["amount"] = df["amount"].fillna(df["amount"].median())  # fill with median

R
# dplyr / base R
colSums(is.na(df))
df_clean <- df |> filter(!is.na(amount))
df <- df |> mutate(amount = replace_na(amount, 0))
df <- df |> mutate(amount = coalesce(amount, median(amount, na.rm = TRUE)))

SQL
-- SQL: IS NULL for detection, COALESCE for fill
SELECT * FROM orders WHERE amount IS NULL;
SELECT COALESCE(amount, 0) AS amount FROM orders;

-- Fill with group median requires a subquery or window function:
SELECT o.*,
  COALESCE(o.amount,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o2.amount)
  ) AS amount_filled
FROM orders o
JOIN orders o2 ON o.category = o2.category  -- same-category median
GROUP BY o.order_id, o.amount, o.category;

Chaining Multiple Operations

Real-world queries combine several steps. Idiomatic chaining keeps code readable.

PYTHON
# pandas: method chaining with assign, query, pipe
result = (
    df
    .query("region == 'West'")
    .assign(revenue=lambda x: x["amount"] * x["quantity"])
    .groupby("category")
    .agg(total=("revenue", "sum"), orders=("order_id", "count"))
    .reset_index()
    .sort_values("total", ascending=False)
    .head(5)
)

R
result <- df |>
  filter(region == "West") |>
  mutate(revenue = amount * quantity) |>
  group_by(category) |>
  summarise(total = sum(revenue), orders = n()) |>
  arrange(desc(total)) |>
  slice_head(n = 5)

SQL
WITH west_orders AS (
  SELECT *, amount * quantity AS revenue
  FROM orders
  WHERE region = 'West'
),
category_totals AS (
  SELECT category,
         SUM(revenue) AS total,
         COUNT(*)     AS orders
  FROM west_orders
  GROUP BY category
)
SELECT *
FROM category_totals
ORDER BY total DESC
LIMIT 5;

CTEs in SQL serve the same readability role as |&gt; pipes in R and method chains in pandas: each named block is a small, testable unit.

Quick Lookup: Operation Cross-Reference

  • Select columns — df[[cols]] / select() / SELECT col1, col2
  • Filter rows — df[mask] or .query() / filter() / WHERE
  • Sort — sort</em>values() / arrange() / ORDER BY
  • Derive column — df[&quot;col&quot;] = expr or .assign() / mutate() / expr AS col
  • Group + aggregate — .groupby().agg() / group<em>by() |&gt; summarise() / GROUP BY + aggregate functions
  • Having (post-agg filter) — boolean mask on aggregated DataFrame / filter() after summarise() / HAVING
  • Inner join — .merge(how=&quot;inner&quot;) / inner</em>join() / INNER JOIN
  • Left join — .merge(how=&quot;left&quot;) / left<em>join() / LEFT JOIN
  • Anti join — .merge(indicator=True) then filter / anti</em>join() / LEFT JOIN ... WHERE right.key IS NULL
  • Wide to long — melt() / pivot<em>longer() / UNION ALL
  • Long to wide — pivot</em>table() / pivot<em>wider() / CASE WHEN + GROUP BY
  • Running total — .cumsum() inside groupby / cumsum() inside group</em>by / SUM() OVER (PARTITION BY ... ORDER BY ...)
  • Lag / lead — .shift() inside groupby / lag() / lead() / LAG() / LEAD()
  • Rank — .rank() inside groupby / min<em>rank() / dense</em>rank() / RANK() / DENSE<em>RANK()
  • Dedup — drop</em>duplicates() / distinct() / DISTINCT or ROW<em>NUMBER()
  • String contains — .str.contains() / str</em>detect() / LIKE / ILIKE
  • Extract with regex — .str.extract() / str<em>extract() / REGEXP</em>MATCH()
  • Date part — .dt.year etc. / year() etc. (lubridate) / EXTRACT()
  • Date arithmetic — + pd.Timedelta() / + days() (lubridate) / + INTERVAL
  • Fill NA — fillna() / replace_na() / coalesce()
  • Count NAs — isnull().sum() / colSums(is.na()) / COUNT(*) - COUNT(col)