Data Analyst with Python — Full Learning Roadmap with Subtopics
Stage 1: Python Programming Fundamentals
⏱ Duration: 1–2 Weeks
Goal: Build your foundation in Python programming
Subtopics:
• Data Types: int, float, str, bool
• Variables & Assignments
• Operators: Arithmetic, logical, comparison
• Control Flow: if, elif, else
• Loops: for, while, break, continue
• Functions: def, return values, parameters, scope
• List Comprehensions
• Data Structures:
o list, tuple, set, dict
o Operations: append, pop, insert, keys, values, etc.
• File Handling: open(), read(), write(), with
• Exception Handling: try, except, finally
Stage 2: NumPy — Numerical Computation
⏱ Duration: 1 Week
Goal: Work with numeric data efficiently
Subtopics:
• Array Creation:
o [Link](), [Link](), [Link]()
o [Link](), [Link](), [Link](), [Link]()
• Array Attributes:
o .shape, .dtype, .ndim, .size
• Array Indexing & Slicing
• Mathematical Operations:
o Element-wise: +, -, *, /
o Functions: [Link](), [Link](), [Link](), [Link](), etc.
o Axis-based ops: [Link](arr, axis=1)
• Broadcasting
• Reshaping Arrays:
o .reshape(), .flatten(), .ravel(), .transpose()
• Stacking and Splitting:
o [Link](), [Link](), [Link](), [Link]()
• Random Module:
o [Link](), rand(), randint(), choice()
• Linear Algebra:
o [Link](), [Link](), det(), eig()
Stage 3: Pandas — Data Manipulation
⏱ Duration: 2 Weeks
Goal: Clean, transform, and explore tabular data
Subtopics:
Creating Data:
• [Link](), [Link]()
• Load files: read_csv(), read_excel(), read_json()
Inspecting Data:
• .head(), .tail(), .shape, .columns, .index
• .info(), .describe(), .dtypes
Selecting & Filtering:
• Column access: df['col'], [Link]
• Row access: .loc[], .iloc[], .at[], .iat[]
• Filtering rows with conditions
Handling Missing Data:
• .isnull(), .notnull()
• .dropna(), .fillna()
Data Cleaning:
• .replace(), .rename()
• .astype() for type conversions
• .duplicated(), .drop_duplicates()
String and Date Processing:
• .[Link](), .[Link](), .[Link]()
• Convert to datetime: pd.to_datetime()
• .dt accessor: .[Link], .[Link], etc.
Sorting and Ranking:
• sort_values(), sort_index(), rank()
Grouping and Aggregating:
• groupby(), .agg(), .sum(), .mean()
• pivot_table(), crosstab()
Merging and Joining:
• merge(), concat(), join()
Exporting Data:
• to_csv(), to_excel(), to_json()
Stage 4: Data Visualization
⏱ Duration: 1 Week
Goal: Present and understand data through plots
Matplotlib
• [Link](), [Link](), [Link](), [Link](), [Link]()
• Titles and labels: [Link](), xlabel(), ylabel()
• Legends and grids: [Link](), [Link]()
• Subplots: [Link](), [Link]()
• Saving figures: [Link]()
Seaborn
• Plot Types:
o [Link](), [Link](), [Link](), [Link]()
o [Link](), [Link](), [Link](), [Link]()
• Advanced Visuals:
o [Link](), [Link](), [Link]()
• Styling:
o sns.set_style(), sns.set_palette(), sns.set_theme()
Stage 5: Exploratory Data Analysis (EDA)
⏱ Duration: 1–2 Weeks
Goal: Draw insights from raw data
Subtopics:
• Summarize: .describe(), .info(), .value_counts()
• Univariate Analysis:
o Distribution plots, histograms, boxplots
• Bivariate Analysis:
o Scatter plots, correlation heatmaps
• Missing value analysis
• Outlier detection
• Grouped summaries
• Feature Engineering (e.g., create new columns from existing ones)
Stage 6: Statistics for Data Analysis
⏱ Duration: 1 Week
Goal: Support data decisions with statistical understanding
Subtopics:
• Descriptive Statistics:
o Mean, Median, Mode, Variance, Std. Deviation
• Probability Basics
• Distributions: Normal, Binomial, Poisson
• Sampling Methods: Random, Stratified
• Hypothesis Testing:
o Null & alternate hypotheses
o p-values, significance level
o t-test, chi-square test, ANOVA
• Confidence Intervals
• Correlation vs. Causation
Stage 7: SQL for Data Analysts
⏱ Duration: 1 Week (Can run parallel)
Goal: Extract and manipulate data from databases
Subtopics:
• SELECT, WHERE, ORDER BY
• Aggregate functions: COUNT(), AVG(), SUM()
• GROUP BY and HAVING
• JOINS: INNER JOIN, LEFT JOIN, RIGHT JOIN
• Subqueries
• CASE WHEN statements
• Working with dates
• Using SQL in Pandas: pd.read_sql()
Stage 8: Final Projects & Reporting
⏱ Duration: 2 Weeks
Goal: Apply all skills to real-world projects
Subtopics:
• Select domain-specific datasets (sales, healthcare, finance)
• Perform:
o Data cleaning
o EDA
o Visualization
o Summary reporting
• Share project in:
o Jupyter Notebook
o Tableau / Power BI (optional)
o GitHub Portfolio
Bonus: Machine Learning for Data Analysts (Optional)
Goal: Use ML for predictive analysis
Subtopics:
• train_test_split
• Models: LinearRegression, LogisticRegression, KMeans
• Metrics: accuracy_score, confusion_matrix, MSE, R²
• Data Preprocessing: LabelEncoder, StandardScaler
• Sklearn pipelines and model evaluation
EXCEL
⏱ Duration: 1–2 Weeks
Goal: Perform basic to intermediate data analysis using Excel's built-in tools
Stage 1: Excel Basics
Subtopics:
• Workbook structure: Sheets, rows, columns, cells
• Data types: Text, number, date/time
• Basic formatting: font, color, borders, alignment
• Data entry shortcuts
• Copy-paste techniques (Paste Special, Transpose)
• Freeze panes, split view
Stage 2: Data Handling & Cleaning
Subtopics:
• Sorting data (single and multi-level)
• Filtering data (AutoFilter, advanced filter)
• Remove duplicates
• Text functions:
o LEFT(), RIGHT(), MID(), LEN(), FIND(), TEXT(), TRIM(), CLEAN()
• Date functions:
o TODAY(), NOW(), DATEDIF(), TEXT()
• Logical functions:
o IF(), AND(), OR(), IFERROR(), ISBLANK()
Stage 3: Formulas and Functions
Subtopics:
• Arithmetic operations: +, -, *, /
• Aggregation:
o SUM(), AVERAGE(), MIN(), MAX(), COUNT(), COUNTA()
• Lookup functions:
o VLOOKUP(), HLOOKUP(), INDEX(), MATCH(), XLOOKUP() (modern)
• Nested formulas
• Absolute vs relative cell referencing ($A$1 vs A1)
Stage 4: Data Analysis Tools
Subtopics:
• Conditional Formatting (rules, color scales, data bars)
• Data Validation (drop-down lists, input restrictions)
• Flash Fill
• Goal Seek
• What-If Analysis
• Named ranges
Stage 5: Pivot Tables and Charts
Subtopics:
Pivot Tables:
• Creating Pivot Tables
• Drag fields into Rows, Columns, Values
• Grouping data (by dates, numbers)
• Filtering, Slicers
• Calculated Fields
Charts:
• Bar, Column, Line, Pie, Area
• Combo Charts
• Chart customization: title, axis, labels
• Dynamic charts with named ranges
Stage 6: Excel Dashboards
Subtopics:
• Design principles: Layout, readability
• Interactive elements: Slicers, drop-downs
• Linking pivot charts and tables
• KPI indicators and visual cues
• Using formulas to drive visuals
Stage 7: Excel Power Tools (Optional)
Subtopics:
• Power Query:
o Import and clean data from multiple sources
o Merge and append queries
o Data transformations (unpivot, transpose, split)
• Power Pivot:
o Data Model and relationships
o DAX basics: CALCULATE(), RELATED(), FILTER()
• Importing from external sources: Web, databases, etc.
SQL
⏱ Duration: 1–2 Weeks
Goal: Query, join, filter, and summarize relational data
Stage 1: SQL Basics
Subtopics:
• Relational database concepts
• Tables, rows, columns, primary keys
• SELECT statements
• Filtering data with WHERE
• Logical operators: AND, OR, NOT
• Aliasing with AS
Stage 2: Sorting and Filtering
Subtopics:
• ORDER BY ascending/descending
• LIMIT or TOP (based on SQL dialect)
• BETWEEN, IN, LIKE, IS NULL
• Wildcards with LIKE: %, _
Stage 3: Aggregate Functions
Subtopics:
• COUNT(), SUM(), AVG(), MIN(), MAX()
• GROUP BY clause
• HAVING vs WHERE
• Filtering after grouping
Stage 4: Joins and Relationships
Subtopics:
• Types of Joins:
o INNER JOIN
o LEFT JOIN
o RIGHT JOIN
o FULL OUTER JOIN (if supported)
• Joining on multiple conditions
• Table aliasing in joins
• NULL behavior in joins
Stage 5: Subqueries and Set Operations
Subtopics:
• Subqueries in:
o SELECT
o FROM
o WHERE
• Correlated subqueries
• Set operators:
o UNION, UNION ALL
o INTERSECT, EXCEPT
Stage 6: Window Functions (Advanced but useful)
Subtopics:
• ROW_NUMBER(), RANK(), DENSE_RANK()
• LEAD(), LAG()
• OVER(PARTITION BY ...)
• Running totals and moving averages
Stage 7: Data Definition & Manipulation (Optional)
Subtopics:
• CREATE TABLE, DROP TABLE
• INSERT INTO, UPDATE, DELETE
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
Stage 8: SQL in Python (Optional but powerful)
Subtopics:
• Using sqlite3 or SQLAlchemy in Python
• Writing SQL queries inside Pandas with pd.read_sql()
• Connecting to real databases: PostgreSQL, MySQL, etc.