csv imploder w/ example

implode_csv.py

from pathlib import Path
from csv import DictReader
from collections import defaultdict


class DupelessDict(dict):
    """Duplicate entries may not pass silently."""
    def __setitem__(self, key, value):
        if key in self:
            raise ValueError(f"Duplicate {key=} encountered.")
        super().__setitem__(key, value)


def csv_to_nested_dict(path: Path, nesting_keys: list[str], result_keys: list[str]) -> dict:
    """Implodes a csv into a dictionary.

    The resulting format of the dictionary can be consumed as-is by a pydantic class
    representing the nested/imploded format.
    
    Example:
        On a csv with the columns a,b,c and d, assuming that a and b are nesting keys,
        and d and e are the result keys, the resulting structure given the sample
        
            a,b,c,d,e
            1,2,3,10,5
            1,3,3,20,5
            2,2,4,30,5
            2,3,4,30,4
        
        would be
        
            {
                "a": {
                    "1": {
                        "b": {
                            "2": { "d": "10", "e": "5" },
                            "3": { "d": "20", "e": "5" }
                        }
                    },
                    "2": {
                        "b": {
                            "2": { "d": "30", "e": "5" },
                            "3": { "d": "30", "e": "4" }
                        }
                    }
                }
            }
        
        Which is represented by the following pydantic class structure
        
            class TopLevel(pydantic.BaseModel):
                a: dict[int, B]

            class B(pydantic.BaseModel):
                b: dict[int, Result]

            class Result(pydantic.BaseModel):
                d: int
                e: int
    
    Notes:
        - keys that don't get mentioned as either nesting key or result key get ignored
        - all values are parsed as strings
        - duplicate nesting key sets in the csv lead to an error, e.g. if the csv in the
          example had another line ` 2,3,5,40,6`; because a and b are the sole nesting keys
          and a=2 and b=3 had already been seen, the csv file is ambiguous and needs to
          be fixed.
    """
    try:
        *keys, last_key = nesting_keys
    except ValueError:
        print("error", "Need keys in order to generate nesting levels.")
        raise

    # apply level of nesting to initialise a pydantic-consumable nested dictionary
    ret_code = f'{{"{last_key}": DupelessDict()}}'
    for key in reversed(keys):
        ret_code = f'{{"{key}": defaultdict(lambda: {ret_code})}}'
    print("debug", ret_code)
    ret = eval(ret_code, dict(defaultdict=defaultdict, DupelessDict=DupelessDict))

    # parse file
    with open(path) as csv_file:
        for idx, row in enumerate(DictReader(csv_file)):
            cur = ret
            for key in keys:
                cur = cur[key][row[key]]
            try:
                cur[last_key][row[last_key]] = {k: row[k] for k in result_keys}
            except ValueError:
                print("error", f"Got duplicate entry in row #{idx} with content {row}.")
                raise

    return ret

nested_structure.py

"""Pydantic class structures, given the shape of the csv at hand."""
from __future__ import annotations

from pathlib import Path
from typing import TypeAlias, Literal
from decimal import Decimal

import pydantic

FooValues: TypeAlias = Literal[1, 2, 3, 4, 5, 6, 7, 8]
BazValues: TypeAlias = Literal[12, 24, 36, 48, 60, 72, 84]
QuxValues: TypeAlias = Literal["New", "Old", "Repeat"]

BAR_LIMIT_MIN = 10
BAR_LIMIT_MAX = 100


class Nested(pydantic.BaseModel):
    """Toplevel."""
    foo: dict[FooValues, Bar]

    @pydantic.field_validator("foo", mode="before")
    def coerce_to_int(cls, v):
        return {int(key): value for key, value in v.items()}


class Bar(pydantic.BaseModel):
    """Maps its key to a range through the accessor `get_bar_range`.

    The `bar` field itself should not be read directly.
    """
    bar: dict[tuple[int, int], Baz]

    def get_bar_range(self, key: int):
        for (min, max), v in self.bar.items():
            if min < key <= max:
                return v
        raise ValueError(
            f"{key=} is out of bounds, please stick to the limits between "
            f"{BAR_LIMIT_MIN=} and {BAR_LIMIT_MAX=}."
        )

    @pydantic.field_validator("bar", mode="before")
    def coerce_to_int_tuple(cls, v):
        split = lambda x: tuple(map(int, (x.split("-", 1)))) if isinstance(x, str) else x
        return {split(key): value for key, value in v.items()}

    @pydantic.field_validator("bar", mode="after")
    def no_gaps_and_no_overlap(cls, v):
        keys = sorted(v.keys())
        if not keys:
            raise ValueError("At least one boundary needs to be set.")
        min_min, max_max = keys[0][0], keys[-1][1]
        if min_min != BAR_LIMIT_MIN:
            raise ValueError(
                f"Lowest value {keys[0][0]} doesn't match {BAR_LIMIT_MIN=}."
            )
        if max_max != BAR_LIMIT_MAX:
            raise ValueError(
                f"Highest value {keys[-1][1]} doesn't match {BAR_LIMIT_MAX=}."
            )
        last = keys[0][1]
        for min, max in keys[1:]:
            if last != min:
                raise ValueError(f"Got a gap between {last=} and {min=}.")
            last = max
        return v


class Baz(pydantic.BaseModel):
    """Yet another nesting level."""
    baz: dict[BazValues, Qux]

    @pydantic.field_validator("baz", mode="before")
    def coerce_to_int(cls, v):
        return {int(key): value for key, value in v.items()}


class Qux(pydantic.BaseModel):
    """And the final nesting, which maps to the actual entries."""
    qux: dict[QuxValues, Result]


class Result(pydantic.BaseModel):
    """The result we're looking for."""
    a: int
    b: Decimal
    c: Literal["strategy_1", "strategy_2", "strategy_3"]


Nested.model_rebuild()

sample_data.csv

test.py

from implode_csv import csv_to_nested_dict
from nested_structure import Nested


data = csv_to_nested_dict(
    Path("sample_data.csv"),
    nesting_keys=["foo", "bar", "baz", "qux"],
    result_keys=["a", "b", "c"],
)
model = Nested(**data)
print(model.foo[7].get_bar_range(15).baz[24].qux["New"])  # a=0 b=Decimal('0.5336343397996824') c='strategy_1'
print(model.foo[3].get_bar_range(80).baz[12].qux["Old"])  # a=3 b=Decimal('0.9521579798265745') c='strategy_2'

timings.sh

# test suite:
#  - the first entry at foo=1, bar=10-30, baz=12, qux=New
#  - the last entry at foo=8, bar=90-100, baz=84, qux=Repeat
#  - 1000 consecutive extractions in the middle at foo=4, bar=90-100, baz=84, qux=Old
#  - 10 "random" consecutive extractions from
#    - foo=1, bar=90-100, baz=12, qux=New
#    - foo=2, bar=90-100, baz=12, qux=New
#    - foo=3, bar=10-30, baz=12, qux=New
#    - foo=3, bar=90-100, baz=12, qux=New
#    - foo=4, bar=90-100, baz=12, qux=New
#    - foo=5, bar=90-100, baz=12, qux=New
#    - foo=6, bar=10-30, baz=12, qux=New
#    - foo=6, bar=90-100, baz=12, qux=New
#    - foo=7, bar=90-100, baz=12, qux=New
#    - foo=8, bar=90-100, baz=12, qux=New

# setup code
read -r -d '' pydantic_setup << EOM
from pathlib import Path
from implode_csv import csv_to_nested_dict
from nested_structure import Nested
data = csv_to_nested_dict(
  Path("sample_data.csv"),
  ["foo", "bar", "baz", "qux"],
  ["a", "b", "c"]
)
model=Nested(**data)
EOM

read -r -d '' pandas_setup << EOM
import pandas as pd
df = pd.read_csv("sample_data.csv")
df[["bar_a", "bar_b"]] = df["bar"].str.split("-",expand=True).astype(int)
EOM

read -r -d '' littletable_setup << EOM
import littletable as lt
model = lt.csv_import(
    "sample_data.csv",
    transforms={
        "foo": int,
        "baz": int,
        "a": int,
        "b": float,
    }
)("model")
model.add_field("bar_min", lambda rec: int(rec.bar.split("-")[0]))
model.add_field("bar_max", lambda rec: int(rec.bar.split("-")[1]))
model.create_index("foo")
model.create_index("baz")
model.create_index("qux")
EOM

read -r -d '' polars_setup << EOM
import polars as pl
df = pl.scan_csv("sample_data.csv").with_columns(
    pl.col("bar").str.split("-").list.to_struct(n_field_strategy="max_width")
).unnest("bar").collect().select(
    pl.col("foo").cast(pl.Int32),
    pl.col("field_0").cast(pl.Int32).alias("bar_a"),
    pl.col("field_1").cast(pl.Int32).alias("bar_b"),
    pl.col("baz").cast(pl.Int32),
    pl.col("qux"),
    pl.col("a").cast(pl.Int32),
    pl.col("b").cast(pl.Float32),
    pl.col("c"),
)
EOM

read -r -d '' duckdb_setup << EOM
import duckdb as ddb
ddb.sql(
"""CREATE OR REPLACE TABLE sample(
    foo INT, bar VARCHAR, baz INT, qux VARCHAR,
    a INT, b FLOAT, c VARCHAR,
    bar_a INT GENERATED ALWAYS AS (split_part(bar, '-', 1)) VIRTUAL,
    bar_b INT GENERATED ALWAYS AS (split_part(bar, '-', 2)) VIRTUAL
  );"""
)
ddb.sql("INSERT INTO sample SELECT * FROM read_csv('sample.csv', AUTO_DETECT=TRUE);")
EOM

# benchmark code
printf "hashmaps w/ pydantic\n"
python -m timeit -u usec -s "$pydantic_setup" 'model.foo[1].get_bar_range(15).baz[12].qux["New"]'
python -m timeit -u usec -s "$pydantic_setup" 'model.foo[8].get_bar_range(95).baz[84].qux["Repeat"]'
python -m timeit -u usec -s "$pydantic_setup" 'for _ in range(1000): model.foo[4].get_bar_range(95).baz[84].qux["Old"]'
python -m timeit -u usec -s "$pydantic_setup" \
  'model.foo[1].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[2].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[3].get_bar_range(15).baz[12].qux["New"]' \
  'model.foo[3].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[4].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[5].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[6].get_bar_range(15).baz[12].qux["New"]' \
  'model.foo[6].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[7].get_bar_range(95).baz[12].qux["New"]' \
  'model.foo[8].get_bar_range(95).baz[12].qux["New"]'

printf "\npandas\n"
python -m timeit -u usec -s "$pandas_setup" 'df[["a", "b", "c"]][(df["foo"] == 1) & (df["bar_a"] <= 15) & (df["bar_b"] > 15) & (df["baz"] == 12) & (df["qux"] == "New")]'
python -m timeit -u usec -s "$pandas_setup" 'df[["a", "b", "c"]][(df["foo"] == 8) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 84) & (df["qux"] == "Repeat")]'
python -m timeit -u usec -s "$pandas_setup" 'for _ in range(1000): df[["a", "b", "c"]][(df["foo"] == 4) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 84) & (df["qux"] == "Old")]'
python -m timeit -u usec -s "$pandas_setup" \
  'df[["a", "b", "c"]][(df["foo"] == 1) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 2) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 3) & (df["bar_a"] <= 15) & (df["bar_b"] > 15) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 3) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 4) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 5) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 6) & (df["bar_a"] <= 15) & (df["bar_b"] > 15) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 6) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 7) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]' \
  'df[["a", "b", "c"]][(df["foo"] == 8) & (df["bar_a"] <= 95) & (df["bar_b"] > 95) & (df["baz"] == 12) & (df["qux"] == "New")]'

printf "\nlittletable\n"
python -m timeit -u usec -s "$littletable_setup" 'model.where(foo=1, baz=12, qux="New").where(lambda rec: rec.bar_min <= 15 < rec.bar_max)[0]'
python -m timeit -u usec -s "$littletable_setup" 'model.where(foo=8, baz=84, qux="Repeat").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]'
python -m timeit -u usec -s "$littletable_setup" 'for _ in range(1000): model.where(foo=4, baz=84, qux="Old").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]'
python -m timeit -u usec -s "$littletable_setup" \
  'model.where(foo=1, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=2, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=3, baz=12, qux="New").where(lambda rec: rec.bar_min <= 15 < rec.bar_max)[0]' \
  'model.where(foo=3, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=4, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=5, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=6, baz=12, qux="New").where(lambda rec: rec.bar_min <= 15 < rec.bar_max)[0]' \
  'model.where(foo=6, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=7, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]' \
  'model.where(foo=8, baz=12, qux="New").where(lambda rec: rec.bar_min <= 95 < rec.bar_max)[0]'

printf "\npolars\n"
python -m timeit -u usec -s "$polars_setup" 'df.filter((pl.col("foo") == 1) & (pl.col("bar_a") <=15) & (pl.col("bar_b") >15) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))'
python -m timeit -u usec -s "$polars_setup" 'df.filter((pl.col("foo") == 8) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 84) & (pl.col("qux") == "Repeat"))'
python -m timeit -u usec -s "$polars_setup" 'for _ in range(1000): df.filter((pl.col("foo") == 4) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 84) & (pl.col("qux") == "Old"))'
python -m timeit -u usec -s "$polars_setup" \
  'df.filter((pl.col("foo") == 1) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 2) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 3) & (pl.col("bar_a") <=15) & (pl.col("bar_b") >15) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 3) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 4) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 5) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 6) & (pl.col("bar_a") <=15) & (pl.col("bar_b") >15) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 6) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 7) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))' \
  'df.filter((pl.col("foo") == 8) & (pl.col("bar_a") <=95) & (pl.col("bar_b") >95) & (pl.col("baz") == 12) & (pl.col("qux") == "New"))'

printf "\nduckdb\n"
python -m timeit -u usec -s "$duckdb_setup" "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=1 AND bar_a <= 15 AND bar_b > 15 AND baz=12 AND qux='New'\")"
python -m timeit -u usec -s "$duckdb_setup" "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=8 AND bar_a <= 95 AND bar_b > 95 AND baz=84 AND qux='Repeat'\")"
python -m timeit -u usec -s "$duckdb_setup" "for _ in range(1000): ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=4 AND bar_a <= 95 AND bar_b > 95 AND baz=84 AND qux='Old'\")"
python -m timeit -u usec -s "$duckdb_setup" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=1 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=2 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=3 AND bar_a <= 15 AND bar_b > 15 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=3 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=4 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=5 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=6 AND bar_a <= 15 AND bar_b > 15 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=6 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=7 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")" \
  "ddb.sql(\"SELECT a, b, c FROM sample WHERE foo=8 AND bar_a <= 95 AND bar_b > 95 AND baz=12 AND qux='New'\")"

Or, with pandas,

>>> import pandas as pd
>>> df = pd.read_csv("sample_data.csv ")
>>> df[['bar_a', 'bar_b']] = df['bar'].str.split('-',expand=True).astype(int)
>>> df[["a", "b", "c"]][(df["foo"] == 7) & (df["bar_a"] <= 15) & (df["bar_b"] > 15) & (df["baz"] == 24) & (df["qux"] == "New")]
     a         b           c
759  0  0.533634  strategy_1
>>> df[["a", "b", "c"]][(df["foo"] == 3) & (df["bar_a"] <= 80) & (df["bar_b"] > 80) & (df["baz"] == 12) & (df["qux"] == "Old")]
     a         b           c
337  3  0.952158  strategy_2

Using littletable would look like this:

import littletable as lt
from decimal import Decimal

model = lt.csv_import(
    "https://gist.githubusercontent.com/a-recknagel/e7bc969512853866becb8bc90cc093d7/raw/0f5174258c94749beddb33e73656709d4476c161/sample_data.csv",
    transforms={
        **{}.fromkeys("foo baz a".split(), int),
        "b": Decimal,
    }
)("model")

# for range checks
model.add_field("bar_min", lambda rec: int(rec.bar.split("-")[0]))
model.add_field("bar_max", lambda rec: int(rec.bar.split("-")[1]))

print(model.info())
print(model[0])

model.create_index("foo")
model.create_index("baz")
model.create_index("qux")

# each slice is a new littletable Table, so they can be chained together
# the final slice is presented as a pretty output
output_fields = "a b c".split()
model.by.foo[7].where(lambda rec: rec.bar_min <= 15 <= rec.bar_max).by.baz[24].by.qux["New"].present(output_fields)
model.by.foo[3].where(lambda rec: rec.bar_min <= 80 <= rec.bar_max).by.baz[12].by.qux["Old"].present(output_fields)
# or just this
# model.where(foo=3, baz=12, qux="Old").where(lambda rec: rec.bar_min <= 80 <= rec.bar_max).present(output_fields)

model[:5].present()

Prints:

  A   B                    C          
 ─────────────────────────────────────
  0   0.5336343397996824   strategy_1


  A   B                    C          
 ─────────────────────────────────────
  3   0.9521579798265745   strategy_2

  
  Foo   Bar     Baz   Qux      A   B                     C            Bar Min   Bar Max 
 ───────────────────────────────────────────────────────────────────────────────────────
    1   10-30    12   New      0   0.4266724448924709    strategy_1        10        30
    1   10-30    12   Old      3   0.9527535060171675    strategy_3        10        30
    1   10-30    12   Repeat   3   0.546172410319005     strategy_3        10        30
    1   10-30    24   New      0   0.34270197832762583   strategy_2        10        30
    1   10-30    24   Old      1   0.872233130556178     strategy_2        10        30

Output of bash timings.sh on my machine, with pydantic==2.3.0, pandas==2.1.0, littletable==2.2.3, polars==0.19.3, duckdb==0.8.1, average single access in usec added as comments:

hashmaps w/ pydantic
500000 loops, best of 5: 0.65 usec per loop   # 0.650 
500000 loops, best of 5: 0.832 usec per loop  # 0.832
500 loops, best of 5: 823 usec per loop       # 0.823
50000 loops, best of 5: 7.51 usec per loop    # 0.751

pandas
200 loops, best of 5: 1.12e+03 usec per loop  # 1120
200 loops, best of 5: 1.07e+03 usec per loop  # 1070
1 loop, best of 5: 1.1e+06 usec per loop      # 1100
20 loops, best of 5: 1.09e+04 usec per loop   # 1090

littletable
2000 loops, best of 5: 181 usec per loop      # 181
1000 loops, best of 5: 178 usec per loop      # 178
2 loops, best of 5: 1.82e+05 usec per loop    # 182
200 loops, best of 5: 1.88e+03 usec per loop  # 188

polars
200 loops, best of 5: 1.58e+03 usec per loop  # 1580
200 loops, best of 5: 1.6e+03 usec per loop   # 1600
1 loop, best of 5: 1.63e+06 usec per loop     # 1630
20 loops, best of 5: 1.59e+04 usec per loop   # 1590

duckdb
5000 loops, best of 5: 76.7 usec per loop     # 76.7
5000 loops, best of 5: 77.4 usec per loop     # 77.4
5 loops, best of 5: 7.57e+04 usec per loop    # 75.7
500 loops, best of 5: 767 usec per loop       # 76.7

No noteworthy variance between scenarios for any of the different frameworks, and the hashmap-based approach of the pydantic setup beats the list/array based ones by a fair bit.

I'm adding this for posterity because I misunderstood the requirement. The following code is useful if you want to minimise I/O. Originally I assumed that you would need to read the CSV on each search, which is much more efficient in polars. So, it doesn't meet your requirement, but I think it's still a useful demo to keep handy.

import pandas as pd
import polars as pl
import timeit


def build_bigger_file(n=100):
    csvs = []
    df = pd.read_csv("sample_data.csv")
    
    for x in range(n):
        csvs.append(df)
    df = pd.concat(csvs)
    df.to_csv("bigger_file.csv", index=False)


def with_pandas(filename):
    df = pd.read_csv(filename)
    df[['bar_a', 'bar_b']] = df['bar'].str.split('-',expand=True).astype(int)
    df = df[["a", "b", "c"]][
        (df["foo"] == 7) 
        & (df["bar_a"] <= 15) 
        & (df["bar_b"] > 15) 
        & (df["baz"] == 24) 
        & (df["qux"] == "New")]
    return df

def with_polars(filename):
    df = (
        pl.scan_csv(filename)
        .with_columns(
            pl.col('bar').str.split('-')
            .arr.to_struct(n_field_strategy="max_width"))
        .unnest("bar")
    )
    df = df.filter(
        (pl.col("foo") == 7) 
        & (pl.col("field_0").cast(pl.Int32) <=15) 
        & (pl.col("field_1").cast(pl.Int32) >15) 
        & (pl.col("baz") == 24) 
        & (pl.col("qux") == "New")
    )
    df = df.collect()
    
    return df
    

if __name__ == '__main__':
    filename = "sample_data.csv"
    print(timeit.timeit(lambda: with_pandas(filename), number=50))
    print(timeit.timeit(lambda: with_polars(filename), number=50))
    
    build_bigger_file()
    
    filename = "bigger_file.csv"
          
    print(timeit.timeit(lambda: with_pandas(filename), number=50))
    print(timeit.timeit(lambda: with_polars(filename), number=50))

Gives the output:

0.09021766704972833
0.059402417042292655
3.7113550830399618
0.35027558298315853

So, polars is drastically faster, and because of scan_csv giving a LazyFrame, can work on files larger than memory.