analytics-hand-skill

Data Analytics Expert Knowledge

pandas Quick Reference

Data Loading

import pandas as pd

# CSV
df = pd.read_csv('data.csv')
df = pd.read_csv('data.csv', parse_dates=['date_col'], index_col='id')

# JSON
df = pd.read_json('data.json')
df = pd.read_json('data.json', orient='records')

# Excel
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# From dict
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})

Data Inspection

df.shape              # (rows, columns)
df.dtypes             # Column types
df.info()             # Summary including memory usage
df.describe()         # Statistical summary
df.head(10)           # First 10 rows
df.isnull().sum()     # Missing values per column
df.duplicated().sum() # Number of duplicate rows
df.nunique()          # Unique values per column

Data Cleaning

# Handle missing values
df.dropna()                          # Drop rows with any NaN
df.fillna(0)                         # Fill NaN with 0
df.fillna(df.mean())                 # Fill with column means
df['col'].interpolate()              # Interpolate missing values

# Remove duplicates
df.drop_duplicates()
df.drop_duplicates(subset=['col1', 'col2'])

# Type conversion
df['col'] = df['col'].astype(int)
df['date'] = pd.to_datetime(df['date'])
df['cat'] = df['cat'].astype('category')

# Outlier removal (IQR method)
Q1 = df['col'].quantile(0.25)
Q3 = df['col'].quantile(0.75)
IQR = Q3 - Q1
df = df[(df['col'] >= Q1 - 1.5*IQR) & (df['col'] <= Q3 + 1.5*IQR)]

Aggregation & Grouping

# Group by
df.groupby('category').agg({'value': ['mean', 'sum', 'count']})

# Pivot table
pd.pivot_table(df, values='value', index='row_cat', columns='col_cat', aggfunc='mean')

# Cross tabulation
pd.crosstab(df['cat1'], df['cat2'])

# Rolling statistics
df['rolling_mean'] = df['value'].rolling(window=7).mean()

# Percentage change
df['pct_change'] = df['value'].pct_change()

Time Series

# Set datetime index
df.set_index('date', inplace=True)

# Resample
df.resample('W').mean()   # Weekly average
df.resample('M').sum()    # Monthly sum
df.resample('Q').count()  # Quarterly count

# Date range
pd.date_range(start='2025-01-01', periods=30, freq='D')

# Shift/Lag
df['prev_value'] = df['value'].shift(1)
df['next_value'] = df['value'].shift(-1)