๐
Read from CSV, JSON, Excel, databases, or create DataFrames from scratch.
Load CSV file into DataFrame
pd.read_csv('file.csv', sep=';')
Copy
Load CSV with custom delimiter
pd.read_csv('file.csv', header=None)
Copy
Load CSV without header row
pd.read_csv('file.csv', dtype={'col': 'str'})
Copy
Specify column data types
pd.read_json('file.json')
Copy
Load JSON file
pd.read_excel('file.xlsx')
Copy
Load Excel file
pd.read_sql('SELECT * FROM table', conn)
Copy
Load data from SQL database
pd.DataFrame({'col1': [1,2,3], 'col2': [4,5,6]})
Copy
Create DataFrame from dict
pd.DataFrame(data, columns=['a','b','c'])
Copy
Create DataFrame with column names
Look at your data: shape, types, nulls, unique values, basic stats.
Row and column count (rows, cols)
Column names, types, non-null counts
Summary stats (mean, std, min, max, etc.)
Count of missing values per column
Unique value count per column
Frequency of each unique value
df.memory_usage(deep=True).sum()
Copy
Total memory usage of DataFrame
Pick columns, rows by label or position, or specific cells.
Select single column as Series
Select multiple columns as DataFrame
Select row by label (index)
Select row by position (0-indexed)
Select specific cell by label
Select specific cell by position
Select rows 2 to 5 inclusive by label
Select rows 2 to 4 (5 exclusive) by position
Fast scalar access by label
Fast scalar access by position
Keep rows that match conditions. Combine with `&`, `|`, `~`.
df[(df['col1'] > 5) & (df['col2'] < 10)]
Copy
Multiple AND conditions
df[(df['col1'] > 5) | (df['col2'] == 'A')]
Copy
Multiple OR conditions
df[df['col'].between(10, 100)]
Copy
Inclusive range filter
df[df['col'].isin([1,2,3])]
Copy
Membership filter (in list)
df[df['col'].str.contains('text')]
Copy
String contains filter
df.query("amount > 1000 and region == 'KE'")
Copy
Filter via query string (readable)
df[df['col'].duplicated()]
Copy
Find duplicate rows
Handle missing values, duplicates, data types, whitespace.
df.dropna(subset=['col1', 'col2'])
Copy
Drop rows with null in specific columns
Fill nulls with mean of column
df.fillna(method='ffill')
Copy
Forward fill (propagate last value)
df.drop_duplicates(subset=['col'], keep='first')
Copy
Remove duplicates, keep first occurrence
df['col'] = df['col'].astype('int')
Copy
Convert column to int
df['col'] = pd.to_datetime(df['col'])
Copy
Convert to datetime
df['col'] = df['col'].str.strip()
Copy
Remove leading/trailing whitespace
df = df.rename(columns={'old': 'new'})
Copy
Rename columns
df.columns = df.columns.str.lower()
Copy
Lowercase all column names
Manipulate text columns and extract date components.
Split string by delimiter
df['col'].str.replace('old', 'new')
Copy
Replace text in string
df['col'].str.extract(r'(\d+)')
Copy
Extract via regex
Day name (Monday, Tuesday...)
df['date'].dt.strftime('%Y-%m-%d')
Copy
Format date as string
pd.to_datetime(df['col']).dt.quarter
Copy
Extract quarter (1-4)
Apply functions to columns or rows. Vectorized when possible.
df['col'].apply(lambda x: x * 2)
Copy
Apply function to each element
df.apply(lambda row: row['a'] + row['b'], axis=1)
Copy
Apply function to each row
df['col'].map({'A': 1, 'B': 2})
Copy
Map values via dict
df['col'] = df['col'].transform('sqrt')
Copy
Transform with named function
df['category'] = pd.cut(df['amount'], bins=[0,100,500,1000])
Copy
Bin continuous values into categories
df['col'] = np.select([df['x']>5, df['x']<2], ['high', 'low'], default='mid')
Copy
Multi-condition assignment
df['col'].applymap(lambda x: x**2)
Copy
Apply to every element in DataFrame
df['col'] = df['col'].str.replace(r'[0-9]', '', regex=True)
Copy
Regex replace on string column
Split-apply-combine. Group by column(s), aggregate, and analyze subsets.
df.groupby('region').sum()
Copy
Sum numeric columns by region
df.groupby('region')['amount'].mean()
Copy
Mean of one column by group
df.groupby('region').agg({'amount': 'sum', 'count': 'mean'})
Copy
Multiple aggregations by column
df.groupby(['region', 'month']).size()
Copy
Count by multiple columns
df.groupby('region').transform('sum')
Copy
Transform: return shape of original df
df.groupby('region').filter(lambda x: len(x) > 10)
Copy
Keep groups with >10 rows
df.groupby('region')['amount'].nlargest(3)
Copy
Top 3 amounts per region
df.groupby('region').apply(lambda x: x.nlargest(1, 'amount'))
Copy
Full row of max amount per group
df.groupby('region', as_index=False).sum()
Copy
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.
Reshape data from long to wide format. Useful for summary tables.
df.pivot(index='date', columns='region', values='amount')
Copy
Pivot: rows, columns, values
df.pivot_table(index='date', columns='region', values='amount', aggfunc='sum')
Copy
Pivot with aggregation (handles duplicates)
pd.crosstab(df['region'], df['status'])
Copy
Cross-tabulation: frequency table
pd.crosstab(df['region'], df['status'], margins=True)
Copy
Crosstab with row/col totals
Unstack index level to columns
Combine multiple DataFrames. INNER, LEFT, RIGHT, OUTER joins.
pd.merge(df1, df2, on='key')
Copy
Inner join on key column
pd.merge(df1, df2, on='key', how='left')
Copy
Left join (keep all left rows)
pd.merge(df1, df2, how='outer')
Copy
Outer join (all rows from both)
pd.merge(df1, df2, left_on='key1', right_on='key2')
Copy
Join on different column names
pd.merge(df1, df2, on=['key1', 'key2'])
Copy
Join on multiple keys
df1.join(df2, lsuffix='_left', rsuffix='_right')
Copy
Join on index (+ suffixes for overlap)
pd.concat([df1, df2], axis=0)
Copy
Stack DataFrames vertically
pd.concat([df1, df2], axis=1)
Copy
Concatenate side-by-side
Resample, shift, lag, and analyze temporal data.
df.set_index('date').resample('D').sum()
Copy
Resample to daily, sum
df.set_index('date').resample('M').mean()
Copy
Resample to monthly, average
df.set_index('date').between_time('09:00', '17:00')
Copy
Filter time range
df[df['date'] >= '2024-01-01']
Copy
Filter after date
df['year_month'] = df['date'].dt.to_period('M')
Copy
Extract year-month period
Melt, transpose, and reshape data between long/wide formats.
df.melt(id_vars=['id'], value_vars=['col1','col2'])
Copy
Wide to long format
Transpose (swap rows/cols)
Pivot last index level to columns
Order rows by values, assign ranks.
Sort by one column (ascending)
df.sort_values(['col1', 'col2'], ascending=[True, False])
Copy
Sort by multiple columns, mixed direction
df['rank'] = df['col'].rank()
Copy
Rank values (1, 2, 3...)
df['rank'] = df['col'].rank(method='dense')
Copy
Dense rank (no gaps on tie)
df['percentile'] = df['col'].rank(pct=True)
Copy
Percentile rank (0-1)
Rolling, expanding, exponential: compute aggregates over windows of data.
df['ma_7'] = df['col'].rolling(window=7).mean()
Copy
7-day moving average
df['sum_7'] = df['col'].rolling(7).sum()
Copy
Rolling 7-day sum
df['cumsum'] = df['col'].cumsum()
Copy
Cumulative sum
df['expanding'] = df['col'].expanding().mean()
Copy
Expanding mean (all prior rows)
df['ewm'] = df['col'].ewm(span=10).mean()
Copy
Exponential weighted mean
df['stddev'] = df['col'].rolling(window=10).std()
Copy
Rolling standard deviation
Save your work: CSV, Excel, JSON, parquet, database.
df.to_csv('output.csv', index=False)
Copy
Save to CSV without index
df.to_excel('output.xlsx', index=False)
Copy
Save to Excel
df.to_json('output.json')
Copy
Save to JSON
df.to_parquet('output.parquet')
Copy
Save to Parquet (fast, compressed)
df.to_sql('table_name', conn, if_exists='replace')
Copy
Save to SQL database
df.to_html('output.html')
Copy
Export to HTML table
Real-world patterns every analyst uses repeatedly.
df.groupby('group').head(3)
Copy
Top 3 per group
df['cumsum'] = df.groupby('group')['col'].cumsum()
Copy
Cumulative sum per group
df['pct_of_total'] = df['col'] / df['col'].sum()
Copy
Percentage of total
df.loc[df.groupby('id')['date'].idxmax()]
Copy
Latest row per group
df[df['col'].between(q1, q3)]
Copy
Interquartile range (Q1-Q3)
df['col_clipped'] = df['col'].clip(lower=0, upper=100)
Copy
Clip outliers to bounds
df['ma'] = df.groupby('id')['col'].transform('mean')
Copy
Group mean added back to df
df['change'] = df.groupby('id')['col'].pct_change()
Copy
Percent change per group
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 โ