Skip to content

Strings, formatting, matching, regex, and string aggregation

Problem

  1. How to clean text?
  2. How to check text patterns?
  3. How to combine grouped text values into one comma-separated string?

Notions: Strings, formatting, matching, regex, and string aggregation

SQL and Pandas syntax

SELECT
    LOWER(name) AS lower_name,
    UPPER(name) AS upper_name,
    UPPER(SUBSTR(name, 1, 1)) || LOWER(SUBSTR(name, 2)) AS fixed_name,
    SUBSTR(name, 1, 3) AS prefix,
    INSTR(name, 'a') AS position
FROM t;

SELECT *
FROM t
WHERE col LIKE 'abc%'
  AND col NOT LIKE '%xyz%';

SELECT *
FROM t
WHERE col GLOB '[A-Za-z]*';

-- SQLite REGEXP exists only if the environment provides it
SELECT *
FROM t
WHERE col REGEXP '^[A-Za-z][A-Za-z0-9_.-]*$';

-- PostgreSQL
SELECT *
FROM t
WHERE col ~ '^[A-Za-z][A-Za-z0-9_.-]*$';

SELECT
    key,
    GROUP_CONCAT(value, ',') AS values_list
FROM (
    SELECT DISTINCT
        key,
        value
    FROM t
    ORDER BY key, value
)
GROUP BY key;

-- PostgreSQL
SELECT
    key,
    STRING_AGG(DISTINCT value, ',' ORDER BY value) AS values_list
FROM t
GROUP BY key;

out = df.copy()

out["lower_name"] = out["name"].str.lower()
out["upper_name"] = out["name"].str.upper()
out["fixed_name"] = out["name"].str[0].str.upper() + out["name"].str[1:].str.lower()
out["prefix"] = out["name"].str[:3]
out["position"] = out["name"].str.find("a") + 1

matches = df.loc[
    df["col"].str.contains(r"^[A-Za-z][A-Za-z0-9_.-]*$", regex=True, na=False)
]

grouped_text = (
    df.drop_duplicates(["key", "value"])
    .sort_values(["key", "value"])
    .groupby("key", as_index=False)
    .agg(values_list=("value", lambda s: ",".join(s)))
)

Example

import sqlite3
import pandas as pd

## SQL

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

con.executescript("""
CREATE TABLE people (
    id INTEGER,
    name TEXT,
    email TEXT
);

CREATE TABLE labels (
    label_date TEXT,
    label TEXT
);

INSERT INTO people VALUES
    (1, 'aLICE', 'alice@example.com'),
    (2, 'BOB', '2bob@example.com'),
    (3, 'cara', 'cara@example.com'),
    (4, 'dan', 'dan@ex.com');

INSERT INTO labels VALUES
    ('2024-01-01', 'red'),
    ('2024-01-01', 'blue'),
    ('2024-01-01', 'red'),
    ('2024-01-02', 'green');
""")

format_sql = """
-- Normalize names and keep example.com emails that start with a letter.
SELECT
    id,
    UPPER(SUBSTR(name, 1, 1)) || LOWER(SUBSTR(name, 2)) AS fixed_name,
    email
FROM people
WHERE email LIKE '%@example.com'
  AND email GLOB '[A-Za-z]*@*.*'
ORDER BY id
"""

pd.read_sql_query(format_sql, con)
#    id fixed_name              email
# 0   1      Alice  alice@example.com
# 1   3       Cara   cara@example.com

group_sql = """
-- Deduplicate labels, then concatenate them by date.
SELECT
    label_date,
    COUNT(label) AS label_count,
    GROUP_CONCAT(label, ',') AS labels
FROM (
    SELECT DISTINCT *
    FROM labels
    ORDER BY label_date, label
)
GROUP BY label_date
ORDER BY label_date;
"""

pd.read_sql_query(group_sql, con)
#    label_date  label_count    labels
# 0  2024-01-01            2  blue,red
# 1  2024-01-02            1     green

## Pandas

people = pd.DataFrame({
    "id": [1, 2, 3, 4],
    "name": ["aLICE", "BOB", "cara", "dan"],
    "email": ["alice@example.com", "2bob@example.com",
              "cara@example.com", "dan@ex.com"]
})

labels = pd.DataFrame({
    "label_date": ["2024-01-01", "2024-01-01", "2024-01-01", "2024-01-02"],
    "label": ["red", "blue", "red", "green"]
})

formatted = people.loc[
    people["email"].str.match(r"[A-Za-z]*@example\.com", na=False),
    ["id", "name", "email"]
]
formatted["fixed_name"] = (
    formatted["name"].str[0].str.upper() +
    formatted["name"].str[1:].str.lower()
)
formatted[["id", "fixed_name", "email"]].sort_values("id")
#    id fixed_name              email
# 0   1      Alice  alice@example.com
# 2   3       Cara   cara@example.com

grouped = (
    labels.drop_duplicates(["label_date", "label"])
    .sort_values(["label_date", "label"])
    .groupby("label_date", as_index=False)
    .agg(
        label_count=("label", "count"),
        labels=("label", lambda s: ",".join(s))
    )
)
grouped
#    label_date  label_count    labels
# 0  2024-01-01            2  blue,red
# 1  2024-01-02            1     green