Cheatsheet

import pandas as pd
import numpy as np

# ===== Load =====
df = pd.read_csv('data.csv', parse_dates=['date'], dtype={'status': 'category'},
                 na_values=['', 'NA'], engine='pyarrow')
df = pd.read_parquet('data.parquet')
df = pd.read_json('data.jsonl', lines=True)
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
df = pd.read_sql('SELECT * FROM orders', conn)

# ===== Inspect =====
df.shape; df.info(memory_usage='deep'); df.describe(include='all').T
df.head(); df.tail(); df.sample(5)
df.isna().sum().sort_values(ascending=False)
df.dtypes; df.memory_usage(deep=True)

# ===== Select =====
df['col']                     # Series
df[['a','b']]                 # DataFrame
df.loc[10:20, ['a','b']]      # label-based
df.iloc[:5, :3]               # position-based
df.at[10, 'col'] = 'x'        # scalar set
df.query('a > 5 and b in ["x","y"]')

# ===== Filter =====
df[df['status'] == 'paid']
df[df['email'].str.contains('@example.com', na=False)]
df[df['date'].between('2026-06-01', '2026-06-30')]
df[df['id'].isin([1, 2, 3])]
df.dropna(subset=['email'])
df.drop_duplicates(subset=['order_id'])

# ===== Create / mutate =====
df['total_aud'] = df['total_cents'] / 100
df['bucket']    = pd.cut(df['total_aud'], bins=[0, 50, 200, 1000], labels=['s','m','l'])
df = df.assign(month=lambda x: x['date'].dt.to_period('M'),
               is_high=lambda x: x['total_aud'] >= 100)

# ===== Group + aggregate =====
df.groupby('status', observed=True)['total_cents'].sum()
(df.groupby(['status','month'], observed=True)
   .agg(n=('id','count'), revenue=('total_cents','sum'),
        unique_users=('customer_id','nunique')))

# Pivot
df.pivot_table(index='customer_id', columns='status', values='total_cents',
               aggfunc='sum', fill_value=0)

# ===== Join =====
df.merge(customers, left_on='customer_id', right_on='id', how='left',
         indicator=True)               # use _merge to QA the join
df.join(other.set_index('id'), on='customer_id')

# ===== Time series =====
df = df.set_index('date').sort_index()
daily   = df['total_cents'].resample('D').sum()
weekly  = df['total_cents'].resample('W').sum()
roll7   = daily.rolling(7).mean()
yoy     = daily.pct_change(365)
shifted = daily.shift(1)
slice_  = df.loc['2026-06':'2026-07']

# ===== Strings =====
df['email'] = df['email'].str.strip().str.lower()
df[['first','last']] = df['name'].str.split(' ', n=1, expand=True)
df['name'].str.extract(r'(?P<first>\w+)\s+(?P<last>\w+)')

# ===== Apply (last resort — vectorise first) =====
df['len'] = df['name'].str.len()                        # vectorised, fast
df['custom'] = df.apply(lambda r: r['a'] + r['b'], axis=1)   # row-wise, slow

# ===== NumPy basics =====
a = np.array([1, 2, 3]); b = np.zeros((3, 4)); c = np.linspace(0, 1, 11)
a.shape; a.dtype; a.reshape(3, 1); a[a > 1]
np.where(a > 1, 'big', 'small')
np.maximum(a, b); a @ b.T              # matrix multiply
np.random.default_rng(42).uniform(0, 1, 1000)

# ===== Plot =====
import matplotlib.pyplot as plt
daily.plot(figsize=(9,3)); plt.tight_layout(); plt.savefig('out.png'); plt.close()

# ===== Export =====
df.to_csv('out.csv', index=False)
df.to_parquet('out.parquet', engine='pyarrow', compression='zstd')
df.to_json('out.jsonl', orient='records', lines=True)
with pd.ExcelWriter('report.xlsx') as xl:
    df.to_excel(xl, sheet_name='Data')

# ===== Performance =====
# - dtypes upfront (read_csv dtype=) avoid the inference pass
# - category for low-cardinality strings (100x memory reduction)
# - pyarrow-backed string dtype: 'string[pyarrow]'
# - .eval / .query for compiled expressions
# - groupby(...).agg([...]) once instead of multiple aggregations
# - polars / duckdb when pandas hits its ceiling

# ===== Pitfalls =====
# - df['a'][i] = x   triggers SettingWithCopyWarning; use df.at[i, 'a'] = x
# - groupby drops NaN by default; pass dropna=False
# - inplace=True is mostly deprecated; reassign instead
# - merge without checking the join key shape -> silent row loss
# - parse_dates infers FORMAT; pass format=... for speed on big files

# np.array reshape mean sum random.normal | pd.read_csv groupby merge to_parquet