pandas pitfalls and performance

import json
import pandas as pd

cust = pd.json_normalize(json.load(open("customers.json")))
orders = pd.read_csv("orders.csv")
df = orders.merge(cust[["customer_id", "address.city"]],
                  on="customer_id")

by_city = df.groupby("address.city")["amount"].sum()
print(len(by_city), "cities, total", by_city.sum())
print(df["amount"].sum(), "<- real total")
print(df.groupby("address.city", dropna=False)["amount"]
        .sum().tail(2))

4 cities, total 21270.0
23268.0 <- real total
address.city
Pune    5096.0
NaN     1998.0
Name: amount, dtype: float64

import pandas as pd

df = pd.read_csv("orders.csv")
returned = df.status == "returned"

df[returned]["amount"] = 0       # chained: edits a copy
print(df.loc[returned, "amount"].tolist())

df.loc[returned, "amount"] = 0   # one .loc call: works
print(df.loc[returned, "amount"].tolist())

dup = pd.DataFrame({"customer_id": [1, 1],
                    "tier": ["gold", "new"]})
try:
    df.merge(dup, on="customer_id", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", e)

[798.0]
[0.0]
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
  customer_id
           1 ...

import time
import numpy as np
import pandas as pd

n = 200_000
rng = np.random.default_rng(0)
df = pd.DataFrame({"qty": rng.integers(1, 5, n), "price": 799.0})

def ms(fn):
    start = time.perf_counter(); fn()
    return (time.perf_counter() - start) * 1000

row_fn = lambda r: r.qty * r.price
print(f"vectorized {ms(lambda: df.qty * df.price):8.1f} ms")
print(f"apply      {ms(lambda: df.apply(row_fn, axis=1)):8.1f} ms")
loop = lambda: [row_fn(r) for _, r in df.iterrows()]
print(f"iterrows   {ms(loop):8.1f} ms")

s = pd.Series(["delivered", "cancelled", "returned"] * 100_000)
as_text = s.memory_usage(deep=True) // 1024
as_cat = s.astype("category").memory_usage(deep=True) // 1024
print(as_text, "KB as text,", as_cat, "KB as category")

vectorized      2.1 ms
apply        3157.4 ms
iterrows     8050.6 ms
4882 KB as text, 293 KB as category