๐Ÿ“‚

Creating & Loading

Read from CSV, JSON, Excel, databases, or create DataFrames from scratch.

pd.read_csv('file.csv')
Load CSV file into DataFrame
pd.read_csv('file.csv', sep=';')
Load CSV with custom delimiter
pd.read_csv('file.csv', header=None)
Load CSV without header row
pd.read_csv('file.csv', dtype={'col': 'str'})
Specify column data types
pd.read_json('file.json')
Load JSON file
pd.read_excel('file.xlsx')
Load Excel file
pd.read_sql('SELECT * FROM table', conn)
Load data from SQL database
pd.DataFrame({'col1': [1,2,3], 'col2': [4,5,6]})
Create DataFrame from dict
pd.DataFrame(data, columns=['a','b','c'])
Create DataFrame with column names
๐Ÿ”Ž

Inspecting

Look at your data: shape, types, nulls, unique values, basic stats.

df.head()
First 5 rows
df.tail(10)
Last 10 rows
df.shape
Row and column count (rows, cols)
df.info()
Column names, types, non-null counts
df.dtypes
Data type of each column
df.describe()
Summary stats (mean, std, min, max, etc.)
df.isnull().sum()
Count of missing values per column
df.nunique()
Unique value count per column
df['col'].value_counts()
Frequency of each unique value
df.memory_usage(deep=True).sum()
Total memory usage of DataFrame
๐Ÿ“

Selecting

Pick columns, rows by label or position, or specific cells.

df['col']
Select single column as Series
df[['col1', 'col2']]
Select multiple columns as DataFrame
df.loc[5]
Select row by label (index)
df.iloc[5]
Select row by position (0-indexed)
df.loc[5, 'col']
Select specific cell by label
df.iloc[5, 2]
Select specific cell by position
df.loc[2:5]
Select rows 2 to 5 inclusive by label
df.iloc[2:5]
Select rows 2 to 4 (5 exclusive) by position
df.at[5, 'col']
Fast scalar access by label
df.iat[5, 2]
Fast scalar access by position
๐Ÿงช

Filtering

Keep rows that match conditions. Combine with `&`, `|`, `~`.

df[df['col'] > 1000]
Rows where col > 1000
df[(df['col1'] > 5) & (df['col2'] < 10)]
Multiple AND conditions
df[(df['col1'] > 5) | (df['col2'] == 'A')]
Multiple OR conditions
df[~(df['col'] > 100)]
NOT operator (negation)
df[df['col'].between(10, 100)]
Inclusive range filter
df[df['col'].isin([1,2,3])]
Membership filter (in list)
df[df['col'].str.contains('text')]
String contains filter
df[~df['col'].isna()]
Drop nulls in one column
df.query("amount > 1000 and region == 'KE'")
Filter via query string (readable)
df[df['col'].duplicated()]
Find duplicate rows
๐Ÿงน

Cleaning

Handle missing values, duplicates, data types, whitespace.

df.dropna()
Drop rows with any null
df.dropna(subset=['col1', 'col2'])
Drop rows with null in specific columns
df.fillna(0)
Fill nulls with 0
df.fillna(df.mean())
Fill nulls with mean of column
df.fillna(method='ffill')
Forward fill (propagate last value)
df.drop_duplicates()
Remove duplicate rows
df.drop_duplicates(subset=['col'], keep='first')
Remove duplicates, keep first occurrence
df['col'] = df['col'].astype('int')
Convert column to int
df['col'] = pd.to_datetime(df['col'])
Convert to datetime
df['col'] = df['col'].str.strip()
Remove leading/trailing whitespace
df = df.rename(columns={'old': 'new'})
Rename columns
df.columns = df.columns.str.lower()
Lowercase all column names
๐Ÿ“

Strings & Dates

Manipulate text columns and extract date components.

df['col'].str.upper()
Uppercase string
df['col'].str.split(',')
Split string by delimiter
df['col'].str.replace('old', 'new')
Replace text in string
df['col'].str.len()
String length
df['col'].str.extract(r'(\d+)')
Extract via regex
df['date'].dt.year
Extract year from date
df['date'].dt.month
Extract month
df['date'].dt.day_name()
Day name (Monday, Tuesday...)
df['date'].dt.strftime('%Y-%m-%d')
Format date as string
pd.to_datetime(df['col']).dt.quarter
Extract quarter (1-4)
โš™๏ธ

Apply / Map / Transform

Apply functions to columns or rows. Vectorized when possible.

df['col'].apply(lambda x: x * 2)
Apply function to each element
df.apply(lambda row: row['a'] + row['b'], axis=1)
Apply function to each row
df['col'].map({'A': 1, 'B': 2})
Map values via dict
df['col'] = df['col'].transform('sqrt')
Transform with named function
df['category'] = pd.cut(df['amount'], bins=[0,100,500,1000])
Bin continuous values into categories
df['col'] = np.select([df['x']>5, df['x']<2], ['high', 'low'], default='mid')
Multi-condition assignment
df['col'].applymap(lambda x: x**2)
Apply to every element in DataFrame
df['col'] = df['col'].str.replace(r'[0-9]', '', regex=True)
Regex replace on string column
๐Ÿ“Š

GroupBy

Split-apply-combine. Group by column(s), aggregate, and analyze subsets.

df.groupby('region').sum()
Sum numeric columns by region
df.groupby('region')['amount'].mean()
Mean of one column by group
df.groupby('region').agg({'amount': 'sum', 'count': 'mean'})
Multiple aggregations by column
df.groupby(['region', 'month']).size()
Count by multiple columns
df.groupby('region').transform('sum')
Transform: return shape of original df
df.groupby('region').filter(lambda x: len(x) > 10)
Keep groups with >10 rows
df.groupby('region')['amount'].nlargest(3)
Top 3 amounts per region
df.groupby('region').apply(lambda x: x.nlargest(1, 'amount'))
Full row of max amount per group
df.groupby('region', as_index=False).sum()
GroupBy without group as index
3 patterns working analysts use: (1) .agg(dict) for multiple column-specific aggregations, (2) .transform() to broadcast results back to original shape, (3) .filter() to keep/drop entire groups by condition.
๐Ÿ“‹

Pivot & Crosstab

Reshape data from long to wide format. Useful for summary tables.

df.pivot(index='date', columns='region', values='amount')
Pivot: rows, columns, values
df.pivot_table(index='date', columns='region', values='amount', aggfunc='sum')
Pivot with aggregation (handles duplicates)
pd.crosstab(df['region'], df['status'])
Cross-tabulation: frequency table
pd.crosstab(df['region'], df['status'], margins=True)
Crosstab with row/col totals
df.unstack()
Unstack index level to columns
df.stack()
Stack columns to index
๐Ÿ”—

Joining & Merging

Combine multiple DataFrames. INNER, LEFT, RIGHT, OUTER joins.

pd.merge(df1, df2, on='key')
Inner join on key column
pd.merge(df1, df2, on='key', how='left')
Left join (keep all left rows)
pd.merge(df1, df2, how='outer')
Outer join (all rows from both)
pd.merge(df1, df2, left_on='key1', right_on='key2')
Join on different column names
pd.merge(df1, df2, on=['key1', 'key2'])
Join on multiple keys
df1.join(df2, lsuffix='_left', rsuffix='_right')
Join on index (+ suffixes for overlap)
pd.concat([df1, df2], axis=0)
Stack DataFrames vertically
pd.concat([df1, df2], axis=1)
Concatenate side-by-side
๐Ÿ—“๏ธ

Time-series

Resample, shift, lag, and analyze temporal data.

df.set_index('date').resample('D').sum()
Resample to daily, sum
df.set_index('date').resample('M').mean()
Resample to monthly, average
df['col'].shift(1)
Shift up by 1 row (lag)
df['col'].diff()
Day-over-day change
df.set_index('date').between_time('09:00', '17:00')
Filter time range
df[df['date'] >= '2024-01-01']
Filter after date
df['year_month'] = df['date'].dt.to_period('M')
Extract year-month period
๐Ÿ”„

Reshaping

Melt, transpose, and reshape data between long/wide formats.

df.melt(id_vars=['id'], value_vars=['col1','col2'])
Wide to long format
df.T
Transpose (swap rows/cols)
df.reset_index()
Move index to column
df.set_index('col')
Set column as index
df.unstack(level=-1)
Pivot last index level to columns
โ†•๏ธ

Sorting & Ranking

Order rows by values, assign ranks.

df.sort_values('col')
Sort by one column (ascending)
df.sort_values(['col1', 'col2'], ascending=[True, False])
Sort by multiple columns, mixed direction
df.sort_index()
Sort by index
df['rank'] = df['col'].rank()
Rank values (1, 2, 3...)
df['rank'] = df['col'].rank(method='dense')
Dense rank (no gaps on tie)
df['percentile'] = df['col'].rank(pct=True)
Percentile rank (0-1)
df.nlargest(5, 'col')
Top 5 rows by column
df.nsmallest(5, 'col')
Bottom 5 rows
๐ŸชŸ

Window Functions

Rolling, expanding, exponential: compute aggregates over windows of data.

df['ma_7'] = df['col'].rolling(window=7).mean()
7-day moving average
df['sum_7'] = df['col'].rolling(7).sum()
Rolling 7-day sum
df['cumsum'] = df['col'].cumsum()
Cumulative sum
df['expanding'] = df['col'].expanding().mean()
Expanding mean (all prior rows)
df['ewm'] = df['col'].ewm(span=10).mean()
Exponential weighted mean
df['stddev'] = df['col'].rolling(window=10).std()
Rolling standard deviation
๐Ÿ’พ

Exporting

Save your work: CSV, Excel, JSON, parquet, database.

df.to_csv('output.csv', index=False)
Save to CSV without index
df.to_excel('output.xlsx', index=False)
Save to Excel
df.to_json('output.json')
Save to JSON
df.to_parquet('output.parquet')
Save to Parquet (fast, compressed)
df.to_sql('table_name', conn, if_exists='replace')
Save to SQL database
df.to_clipboard()
Copy to clipboard
df.to_html('output.html')
Export to HTML table
๐Ÿ’ก

Common Idioms

Real-world patterns every analyst uses repeatedly.

df.groupby('group').head(3)
Top 3 per group
df['cumsum'] = df.groupby('group')['col'].cumsum()
Cumulative sum per group
df['pct_of_total'] = df['col'] / df['col'].sum()
Percentage of total
df.loc[df.groupby('id')['date'].idxmax()]
Latest row per group
df[df['col'].between(q1, q3)]
Interquartile range (Q1-Q3)
df['col_clipped'] = df['col'].clip(lower=0, upper=100)
Clip outliers to bounds
df['ma'] = df.groupby('id')['col'].transform('mean')
Group mean added back to df
df['change'] = df.groupby('id')['col'].pct_change()
Percent change per group
โšก

Performance Tips

Make your code faster. Use dtypes, categorical, vectorization.

df['col'] = df['col'].astype('category')
Compress repeated strings
pd.read_csv('file.csv', dtype={'col': 'int32'})
Use int32 instead of int64
df['result'] = df['a'] + df['b']
Vectorized (fast) not loop
df[df['col'].isin([1,2,3])]
Use isin() not list comprehension
df[mask] vs df.query('...')
Boolean mask usually faster than query
df = pd.read_csv('file.csv', usecols=['a','b'])
Load only needed columns
df.to_parquet() vs df.to_csv()
Parquet faster for large files
Rule of thumb: Use categorical for <100 unique values. Vectorized operations are 10-100x faster than loops. Parquet saves space and loads faster than CSV.
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Ready to go further?

Join the Best AI Program: The AI-Era Analyst โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst โ†’