Skip to content

Modulo, parity, and swapping adjacent rows

Problem

  1. How to use odd and even positions to label rows or swap neighboring row values?

Notions: Modulo, parity, and swapping adjacent rows

SQL and Pandas syntax

SELECT
    id,
    CASE
        WHEN id % 2 = 0 THEN 'even'
        ELSE 'odd'
    END AS parity
FROM t;

SELECT
    id,
    CASE
        WHEN id % 2 = 1 THEN LEAD(value, 1, value) OVER (ORDER BY id)
        ELSE LAG(value) OVER (ORDER BY id)
    END AS swapped_value
FROM t
ORDER BY id;

df["parity"] = "odd"
df.loc[df["id"] % 2 == 0, "parity"] = "even"

tmp = df.sort_values("id").reset_index(drop=True).copy()

tmp["swapped_value"] = tmp["value"]
tmp.loc[tmp.index[0::2], "swapped_value"] = tmp["value"].shift(-1)
tmp.loc[tmp.index[1::2], "swapped_value"] = tmp["value"].shift(1)
tmp["swapped_value"] = tmp["swapped_value"].fillna(tmp["value"])

Example

import sqlite3
import pandas as pd

## SQL

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

con.executescript("""
CREATE TABLE seats (
    id INTEGER,
    label TEXT
);

INSERT INTO seats VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'D'),
    (5, 'E');
""")

sql = """
-- Swap labels in adjacent seat pairs, leaving the last odd seat unchanged.
SELECT
    id,
    CASE
        WHEN id % 2 = 0 THEN 'even'
        ELSE 'odd'
    END AS parity,
    CASE
        -- If we put only LEAD(label) the last swapped_label
        -- value on an odd row would be NULL.  Here we just
        -- keep its label value.
        WHEN id % 2 = 1 THEN LEAD(label, 1, label) OVER (ORDER BY id)
        ELSE LAG(label) OVER (ORDER BY id)
    END AS swapped_label
FROM seats
ORDER BY id;
"""

pd.read_sql_query(sql, con)
#    id parity swapped_label
# 0   1    odd             B
# 1   2   even             A
# 2   3    odd             D
# 3   4   even             C
# 4   5    odd             E

## Pandas

seats = pd.DataFrame({
    "id": [1, 2, 3, 4, 5],
    "label": ["A", "B", "C", "D", "E"]
})

tmp = seats.sort_values("id").reset_index(drop=True).copy()
tmp["parity"] = "odd"
tmp.loc[tmp["id"] % 2 == 0, "parity"] = "even"
tmp
#    id label parity
# 0   1     A    odd
# 1   2     B   even
# 2   3     C    odd
# 3   4     D   even
# 4   5     E    odd

tmp.loc[tmp.index[0::2], "swapped_label"] = tmp["label"].shift(-1)
tmp.loc[tmp.index[1::2], "swapped_label"] = tmp["label"].shift(1)
tmp["swapped_label"] = tmp["swapped_label"].fillna(tmp["label"])
tmp[["id", "parity", "swapped_label"]]
#    id parity swapped_label
# 0   1    odd             B
# 1   2   even             A
# 2   3    odd             D
# 3   4   even             C
# 4   5    odd             E