Pandasdrill / Cheat sheet
pandas cheat sheet
A hundred entries in ten groups, written for pandas 2.x with the code you actually type, the gotcha next to it, and a drill for most. Print the page or get the PDF by email.
The ones worth learning first
| What | How |
|---|
| Read a CSV | df = pd.read_csv('sales.csv', parse_dates=['date']) |
| Filter rows | df[df['total'] > 100] |
| Pick columns | df[['name', 'total']] |
| Group and sum | df.groupby('region')['total'].sum() |
| Merge | orders.merge(customers, on='customer_id', how='left') |
| Sort | df.sort_values(['region', 'total'], ascending=[True, False]) |
| Missing values | df['qty'] = df['qty'].fillna(0) |
| New column from others | df['net'] = df['price'] * (1 - df['discount']) |
| Dates | df['date'] = pd.to_datetime(df['date']); df['month'] = df['date'].dt.to_period('M') |
| Quick look | df.head(); df.info(); df.describe() |
Load and inspect
| CSV, Excel, JSON | pd.read_csv('f.csv') pd.read_excel('f.xlsx', sheet_name='Data') pd.read_json('f.json') |
| From a dict | pd.DataFrame({'a': [1, 2], 'b': ['x', 'y']}) |
| Shape, columns, types | df.shape df.columns df.dtypes |
| First rows, summary | df.head(10) df.info() df.describe() |
| Missing per column | df.isna().sum() |
| Unique values and counts | df['region'].unique() df['region'].value_counts() |
| Save | df.to_csv('out.csv', index=False) df.to_excel('out.xlsx', index=False) |
| Memory and sampling | df.memory_usage(deep=True).sum() df.sample(5) |
Select and filter
| One column (Series) | df['total'] |
| Several columns | df[['name', 'total']] drill |
| Boolean mask | df[df['total'] > 100] drill |
| Two conditionsParentheses are required; use & | ~ not and/or/not. | df[(df['total'] > 100) & (df['region'] == 'North')] |
| Rows and columns by label | df.loc[df['total'] > 100, ['name', 'total']] drill |
| By position | df.iloc[:5, 0:2] drill |
| In a list | df[df['region'].isin(['North', 'East'])] drill |
| BetweenBoth ends included. | df[df['total'].between(10, 50)] drill |
| queryVariables: @threshold. | df.query("total > 100 and region == 'North'") drill |
| Sort | df.sort_values(['region', 'total'], ascending=[True, False]) drill |
| Top nnsmallest for the other end. | df.nlargest(3, 'salary') drill |
| Not null rows | df[df['email'].notna()] |
| Set and reset the index | df.set_index('id') df.reset_index(drop=True) |
Clean
| Fill missing | df['qty'] = df['qty'].fillna(0) drill |
| Fill with the median | df['age'] = df['age'].fillna(df['age'].median()) |
| Drop rows missing a field | df = df.dropna(subset=['email']) drill |
| Text to numbererrors='coerce' turns junk into NaN instead of failing. | df['price'] = pd.to_numeric(df['price'], errors='coerce') drill |
| Change type | df['qty'] = df['qty'].astype(int) |
| Duplicates | df = df.drop_duplicates(subset=['email'], keep='first') drill |
| Find duplicates | df[df.duplicated(subset=['email'], keep=False)] |
| Rename columns | df = df.rename(columns={'Total ($)': 'total'}) drill |
| Clean all column names | df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_') |
| Replace values | df['status'] = df['status'].replace({'N/A': None, 'paid ': 'paid'}) |
| Map to new values | df['region'] = df['code'].map({'N': 'North', 'S': 'South'}) |
| Drop columns | df = df.drop(columns=['tmp', 'notes']) |
| Clip outliers | df['total'] = df['total'].clip(lower=0, upper=10000) |
Strings
| Strip and lower | df['name'] = df['name'].str.strip().str.lower() drill |
| Contains | df[df['product'].str.contains('pro', case=False, na=False)] drill |
| Starts / ends with | df['sku'].str.startswith('AB') df['email'].str.endswith('.edu') |
| Extract with regex | df['num'] = df['code'].str.extract(r'(\d+)').astype(int) drill |
| Replace | df['phone'] = df['phone'].str.replace(r'\D', '', regex=True) |
| Split into columns | df[['first', 'last']] = df['name'].str.split(' ', n=1, expand=True) |
| Length, title case | df['name'].str.len() df['name'].str.title() |
| Join columns | df['full'] = df['first'] + ' ' + df['last'] |
| Pad | df['id'].astype(str).str.zfill(6) |
Dates
| Parse | df['date'] = pd.to_datetime(df['date'], errors='coerce') drill |
| Parse with a format | pd.to_datetime(df['date'], format='%d/%m/%Y') |
| Parts | df['date'].dt.year .dt.month .dt.day_name() |
| Month period | df['month'] = df['date'].dt.to_period('M') |
| Difference in days | (df['end'] - df['start']).dt.days |
| Add days | df['due'] = df['date'] + pd.Timedelta(days=30) |
| Filter a range | df[(df['date'] >= '2026-01-01') & (df['date'] < '2026-02-01')] |
| Resample to months'MS' month start, 'W' weekly, 'QS' quarter. | df.set_index('date')['total'].resample('MS').sum() drill |
| Date range | pd.date_range('2026-01-01', periods=12, freq='MS') |
| Business days between | np.busday_count(start.values.astype('datetime64[D]'), end.values.astype('datetime64[D]')) |
Group and aggregate
| Sum per group | df.groupby('region')['total'].sum() drill |
| As a flat table | df.groupby('region', as_index=False)['total'].mean() drill |
| Count rowscount() counts non-null per column; size() counts rows. | df.groupby('region').size() drill |
| Named aggregations | df.groupby('region').agg(revenue=('total', 'sum'), orders=('id', 'count'), avg=('total', 'mean')) drill |
| Two keys | df.groupby(['region', 'rep'])['total'].sum().reset_index() drill |
| Several stats | df.groupby('region')['total'].agg(['sum', 'mean', 'max']) drill |
| Pivot table | df.pivot_table(index='region', columns='month', values='total', aggfunc='sum', fill_value=0) drill |
| Crosstab | pd.crosstab(df['region'], df['status']) drill |
| Shares | df['region'].value_counts(normalize=True) drill |
| Group total next to each row | df['region_total'] = df.groupby('region')['total'].transform('sum') |
| Share within group | df['share'] = df['total'] / df.groupby('region')['total'].transform('sum') |
| Top row per group | df.sort_values('total', ascending=False).groupby('region').head(1) |
| Filter groups | df.groupby('region').filter(lambda g: g['total'].sum() > 1000) |
| Cumulative within group | df.groupby('region')['total'].cumsum() |
Merge and reshape
| Inner merge | orders.merge(customers, on='customer_id') drill |
| Left mergeAdd validate='m:1' to catch duplicate keys. | orders.merge(customers, on='customer_id', how='left') drill |
| Different key names | orders.merge(customers, left_on='cust', right_on='id') drill |
| Rows with no match | m = a.merge(b, on='k', how='left', indicator=True); m[m['_merge'] == 'left_only'] |
| Stack tables | pd.concat([jan, feb], ignore_index=True) drill |
| Side by side | pd.concat([a, b], axis=1) |
| Merge twice then aggregate | items.merge(orders, on='order_id').merge(products, on='product_id').groupby('category')['qty'].sum() drill |
| Wide to long | df.melt(id_vars=['region'], var_name='month', value_name='total') drill |
| Long to widepivot needs unique index/column pairs; pivot_table aggregates. | df.pivot(index='region', columns='month', values='total') drill |
| Stack and unstack | df.set_index(['region', 'month'])['total'].unstack() drill |
| Columns into rows | df.set_index('region').stack() drill |
| Join on index | a.join(b, how='left') |
Time series and ranks
| Rolling average | df['ma7'] = df['total'].rolling(7).mean() drill |
| Change from previous | df['diff'] = df['total'].diff() drill |
| Percent change | df['mom'] = df['total'].pct_change() drill |
| Running total | df['cum'] = df['total'].cumsum() drill |
| Previous row | df['prev'] = df['total'].shift(1) |
| Rankmethod='dense' for no gaps. | df['rank'] = df['score'].rank(method='min', ascending=False) drill |
| Quartiles | df['q'] = pd.qcut(df['total'], 4, labels=['Q1', 'Q2', 'Q3', 'Q4']) drill |
| Bands | df['band'] = pd.cut(df['age'], bins=[0, 18, 35, 65, 120], labels=['child', 'young', 'adult', 'senior']) drill |
| Expanding max | df['peak'] = df['total'].expanding().max() |
| Exponential average | df['ewm'] = df['total'].ewm(span=10).mean() |
Apply and vectorize
| Classify with a function | df['size'] = df['total'].apply(lambda x: 'big' if x > 100 else 'small') drill |
| Vectorized instead of applyMuch faster than apply on large frames. | np.where(df['total'] > 100, 'big', 'small') |
| Several conditions | np.select([df['t'] > 100, df['t'] > 50], ['big', 'mid'], default='small') |
| Row-wise functionPrefer df['price'] * df['qty']. | df.apply(lambda r: r['price'] * r['qty'], axis=1) |
| Assign in a chain | df.assign(net=lambda d: d['price'] * (1 - d['discount'])) |
| Pipe | df.pipe(clean).pipe(enrich) |
| Iterate (last resort)Avoid iterrows in loops over large data. | for row in df.itertuples(): ... |
| Where / mask | df['total'].where(df['total'] > 0, 0) |
Gotchas
| SettingWithCopyWarning | Use df.loc[mask, 'col'] = value, not df[mask]['col'] = value |
| Chained comparisons | (df['a'] > 1) & (df['b'] < 5), never df['a'] > 1 and df['b'] < 5 |
| NaN is not equal to NaN | Use .isna(), not == np.nan |
| Integer column with NaN becomes float | Use the nullable 'Int64' dtype: df['qty'].astype('Int64') |
| groupby drops NaN keys | df.groupby('region', dropna=False) |
| merge duplicates rows | Check keys are unique: validate='one_to_one' or 'many_to_one' |
| Copy vs view | df2 = df.copy() before modifying a subset |
| Show all columns | pd.set_option('display.max_columns', None) |
pandasdrill.com: practice every one of these with instant checking. 105 entries.
Questions
Which pandas operations come up most in data interviews?
Filtering with boolean masks, groupby with named aggregations, merge with how='left', pivot_table, value_counts, handling missing values, string cleaning, and date parsing with resample. Window-style questions (running totals, month over month, rank per group) use cumsum, pct_change, shift and groupby transform.
apply or vectorized?
Vectorized. np.where, np.select and column arithmetic run in C and are often a hundred times faster than apply with a lambda. Use apply only when there is no vectorized form.
merge or join?
merge for columns as keys (the usual case), join for index-aligned frames. They do the same thing underneath.
What version is this for?
pandas 2.x. The code also runs on 1.5 except for a few dtype details; the nullable Int64 and string dtypes are the main differences.
A cheat sheet does not make them stick
Doing them with instant checking does. The free exercises need no account.
Start practicing
Pandasdrill