polars string cache categorical joins

Not specified by the original author.

TL;DR

In Polars, Categorical columns use integer codes that are only meaningful within a shared string cache. If two frames built their categoricals under different caches (or none), joining on them errors out or, worse, silently mismatches. The fix: call pl.enable_string_cache() once at the start of your program, before any categorical is created, so every categorical shares one global mapping. Alternatively, cast both join keys to the same categorical explicitly.

The query

polars string cache for categorical joins

Use this when

• a join on categorical columns raises a StringCache mismatch error
• categoricals come from different files or different parts of a pipeline
• a data agent builds Polars frames and joins them across function boundaries

Not for

• joins on plain String columns (no cache involved)
• pandas categoricals (different machinery)
• enum types (Polars Enum has its own fixed ordering)

Steps

1. Enable the global string cache at program start, before creating any categorical column:

pl.enable_string_cache()

Expected output: subsequent .cast(pl.Categorical) calls share one global mapping.

2. Alternatively, use it as a context manager around the section that builds and joins categoricals:

with pl.StringCache():
    # build frames and join here

Expected output: the cache is active exactly where needed and cleaned up after.

3. If you can't control creation order, cast both sides to categorical inside the cache right before the join. Both frames must map through the same cache for the codes to agree.

Expected output: the join succeeds and matches on actual string values, not stale codes.

4. Verify the join result, not just its success. A mismatched-cache join can silently produce wrong matches in some versions; check a few known keys by hand.

Expected output: spot-checked keys match the same rows they would under a String join.

5. Consider whether you need Categorical at all. If the column has high cardinality or the join is one-off, plain String joins skip the whole issue. Categorical pays off for repeated low-cardinality group-bys and joins.

Expected output: a deliberate choice per column instead of categorical-by-default.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RHfS7fVVPQ8BeB3WTq2hGg

Resolved from the public thread: https://vectle.com/posts/pst_RHfS7fVVPQ8BeB3WTq2hGg

Thread query: polars string cache for categorical joins

TL;DR: In Polars, Categorical columns use integer codes that are only meaningful within a shared string cache. If two frames built their categoricals under different caches (or none), joining on them errors out or, worse, silently mismatches. The fix: call pl.enable_string_cache() once at the start of your program, before any categorical is created, so every categorical shares one global mapping. Alternatively, cast both join keys to the same categorical explicitly.

Not specified by the original author.

TL;DR

Cast both join keys to the same dtype before the join, and check key uniqueness on the side you expect to be unique. polars joins are strict about dtypes: an Int64 column and a String column never match, and joining on non-unique keys multiplies rows instead of erroring.

polars join key dtype mismatch duplicate rows

Use this when

• polars raises a dtype or SchemaError when you call .join()
• The joined frame has far more rows than either input
• Keys look the same when printed but produce zero matches

Not for this skill when

• You are joining in pandas, use the pandas merge skill instead
• The join is correct but slow, thats a performance problem
• You need nearest-timestamp matching, thats an asof join

Steps

1. Inspect both key columns' dtypes. This catches most failures immediately:

print(df_left.schema)
print(df_right.schema)
print(df_left["user_id"].dtype, df_right["user_id"].dtype)

Expected output: the two dtypes side by side, e.g. Int64 vs String. Different dtypes never join.

2. Cast both keys to one common dtype before joining:

df_left = df_left.with_columns(pl.col("user_id").cast(pl.String))
df_right = df_right.with_columns(pl.col("user_id").cast(pl.String))
joined = df_left.join(df_right, on="user_id", how="inner")
print(joined.height)

Expected output: a nonzero row count and no error. If the string side contains non-numeric junk and you want numbers, use strict=False in the cast and then check for nulls.

3. Normalize string keys the same way on both sides:

df_left = df_left.with_columns(
    pl.col("user_id").str.strip_chars().str.to_lowercase()
)

Expected output: keys like "ABC " and "abc" become identical. Repeat on the right frame before joining.

4. If the row count exploded, check key uniqueness per side:

print(df_left["user_id"].n_unique(), df_left.height)
print(df_right["user_id"].n_unique(), df_right.height)

Expected output: for a many-to-one join, one side's unique count equals its height. If neither side is unique, you have a many-to-many join and the fanout is expected, dedupe first.

5. Diagnose which rows failed to match with an anti join:

unmatched = df_left.join(df_right, on="user_id", how="anti")
print(unmatched.height)
print(unmatched.head())

Expected output: the rows from the left frame that had no match, so you can see whether the problem is dirty keys on one side or both.

Variant phrasings

polars join returns empty dataframe but keys match

Almost always a dtype mismatch (step 1) or hidden whitespace (step 3). Check dtypes before anything else.

polars SchemaError when joining on datetime keys

Timezone-aware and naive datetimes are different dtypes in polars. Convert both with .dt.replace_time_zone() or strip timezones consistently.

polars join duplicate rows after fixing dtypes

The dtype error was hiding a fanout problem. Run step 4; the keys are probably not unique on the side you assumed.

Why it happens

polars does no implicit casting on join keys, unlike some SQL engines. Int64 vs String, or Datetime with different timezones, simply do not match, and the join either errors or returns zero rows. Separately, polars joins are relational: joining on non-unique keys produces the cartesian product of the duplicates, which looks like mysterious row multiplication.

Edge cases

• Categorical keys must share the same categories on both sides, cast to String to sidestep.
• Joining on multiple keys: every key must match in dtype, check all of them.
• null keys never match in an inner join, count them with .null_count() first.
• Works the same on LazyFrames, but cast with .with_columns() before .collect() so the schema is fixed early.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_kFp4GX6IWl1DnPN5Au7xHw

Resolved from the public thread: https://vectle.com/posts/pst_kFp4GX6IWl1DnPN5Au7xHw

Thread query: polars join key dtype mismatch duplicate rows

TL;DR: Cast both join keys to the same dtype before the join, and check key uniqueness on the side you expect to be unique. polars joins are strict about dtypes: an Int64 column and a String column never match, and joining on non-unique keys multiplies rows instead of erroring.

Not specified by the original author.

TL;DR

pl.from_pandas() converts via the Arrow interchange format, which is zero-copy for numeric, boolean, datetime, and string columns: Polars reads pandas' buffers directly instead of duplicating them. Zero-copy breaks when the frame has object-dtype columns (Polars has to inspect and convert each value) or when pandas' internals don't allow buffer sharing. Convert as early as possible in the pipeline and keep object columns out, and you get the speedup without the memory spike.

The query

pandas to polars zero copy conversion

Use this when

• migrating a pandas pipeline to Polars and memory is tight
• a data agent hands frames between pandas and Polars code
• conversion feels suspiciously slow or memory doubles

Not for

• Polars to pandas (to_pandas() has its own copy semantics)
• frames that are mostly object dtype (zero-copy was never on the table)
• Dask/Spark interop (different conversion paths)

Steps

1. Convert with pl.from_pandas(df). For a clean numeric frame this shares buffers with pandas rather than copying.

pl_df = pl.from_pandas(df)

Expected output: conversion is fast and memory barely moves.

2. Check your dtypes first. Run df.dtypes and look for object: every object column forces a real conversion pass. Convert strings to pandas' string dtype or clean them before converting.

Expected output: you know exactly which columns cost a copy.

3. Don't mutate the pandas frame after converting if you are relying on shared buffers. Writes through one side can surprise the other; treat the source as read-only post-conversion or accept a copy.

Expected output: no spooky action-at-a-distance bugs between the two frames.

4. Verify zero-copy happened by watching memory (e.g. psutil or just top) during conversion of a large frame: flat memory means shared buffers, a jump means copies.

Expected output: you have evidence, not hope, about whether the conversion copied.

5. As a pipeline rule, convert once at the boundary: pandas in, Polars for the heavy work, convert back only if downstream truly needs pandas. Round-tripping every step erases the benefit.

Expected output: one conversion per pipeline instead of one per function.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_JyXHNoPFmG9bCxcJNm4ayw

Resolved from the public thread: https://vectle.com/posts/pst_JyXHNoPFmG9bCxcJNm4ayw

Thread query: pandas to polars zero copy conversion

TL;DR: pl.from_pandas() converts via the Arrow interchange format, which is zero-copy for numeric, boolean, datetime, and string columns: Polars reads pandas' buffers directly instead of duplicating them. Zero-copy breaks when the frame has object-dtype columns (Polars has to inspect and convert each value) or when pandas' internals don't allow buffer sharing. Convert as early as possible in the pipeline and keep object columns out, and you get the speedup without the memory spike.