Skip to content

03_Study_Material_Syllabus.md

Latest commit

 

History

History
570 lines (463 loc) · 15.4 KB

03_Study_Material_Syllabus.md

File metadata and controls

570 lines (463 loc) · 15.4 KB
Raw

📚 Complete Data Analysis Study Material

Your Learning Roadmap from Beginner to Job-Ready

🎯 Learning Path Overview

Week 1-2     Week 3-4      Week 5-6     Week 7-8      Week 9-10
┌────────┐  ┌──────────┐  ┌─────────┐  ┌──────────┐  ┌──────────┐
│ Python │→│ Libraries │→│   SQL   │→│  Excel   │→│ Projects │
│ Basics │  │  NumPy   │  │ Queries │  │ & Stats  │  │ Portfolio│
│        │  │  Pandas  │  │  Joins  │  │  Pivot   │  │          │
└────────┘  └──────────┘  └─────────┘  └──────────┘  └──────────┘

📋 Table of Contents

Part 1: Foundations

  1. What is Data Analysis?
  2. Data Analyst Skills
  3. Tools You'll Learn

Part 2: Python for Data Analysis

  1. Python Basics Syllabus
  2. Python Libraries Syllabus

Part 3: SQL for Data Analysis

  1. SQL Complete Syllabus

Part 4: Excel & Statistics

  1. Excel Skills
  2. Statistics Basics

Part 5: Practical Application

  1. Project Ideas
  2. Interview Preparation

1. What is Data Analysis?

Definition

Data Analysis is the process of:

  1. Collecting data from various sources
  2. Cleaning messy or incomplete data
  3. Transforming data into useful format
  4. Analyzing to find patterns and insights
  5. Visualizing results through charts
  6. Reporting findings to stakeholders

Real-World Examples

Industry Data Analysis Use Case
E-commerce Sales trends, Customer behavior, Inventory optimization
Healthcare Patient outcomes, Disease patterns, Resource allocation
Finance Risk assessment, Fraud detection, Investment analysis
Marketing Campaign performance, Customer segmentation, ROI analysis
HR Employee retention, Salary benchmarking, Hiring patterns

Data Analysis Process

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  1. DEFINE  │ →  │  2. COLLECT │ →  │  3. CLEAN   │
│  Question   │    │    Data     │    │    Data     │
└─────────────┘    └─────────────┘    └─────────────┘
                                            ↓
┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  6. REPORT  │ ←  │ 5. VISUALIZE│ ←  │  4. ANALYZE │
│  Findings   │    │   Results   │    │    Data     │
└─────────────┘    └─────────────┘    └─────────────┘

2. Data Analyst Skills

Technical Skills (Hard Skills)

Skill Importance Learn In
Python ⭐⭐⭐⭐⭐ Weeks 1-4
SQL ⭐⭐⭐⭐⭐ Weeks 5-6
Excel ⭐⭐⭐⭐ Week 7
Statistics ⭐⭐⭐⭐ Week 8
Data Visualization ⭐⭐⭐⭐ Throughout
Power BI/Tableau ⭐⭐⭐ After basics

Soft Skills

Skill Why Important
Problem Solving Break down complex problems
Communication Explain findings to non-technical people
Attention to Detail Catch errors and anomalies
Critical Thinking Question data quality and results
Business Understanding Know what questions to ask

3. Tools You'll Learn

Programming & Analysis

┌─────────────────────────────────────────────────────────────────┐
│                     PYTHON ECOSYSTEM                             │
├─────────────┬──────────────┬──────────────┬───────────────────┤
│   NumPy     │    Pandas    │  Matplotlib  │    Seaborn        │
│   Arrays    │  DataFrames  │    Charts    │  Statistical      │
│   Math      │   Cleaning   │    Plots     │  Visualization    │
└─────────────┴──────────────┴──────────────┴───────────────────┘

Databases

┌─────────────────────────────────────────────────────────────────┐
│                        DATABASES                                 │
├─────────────┬──────────────┬──────────────────────────────────┤
│   SQLite    │    MySQL     │         PostgreSQL                │
│  (Learning) │   (Common)   │        (Advanced)                 │
└─────────────┴──────────────┴──────────────────────────────────┘

Other Tools

Tool Purpose
VS Code Code editor
Jupyter Notebook Interactive analysis
Git/GitHub Version control
Excel Quick analysis, reporting
Power BI Business dashboards

4. Python Basics Syllabus

Module 1: Getting Started (2-3 days)

📁 01_Python_Basics/01_variables_datatypes.py
  • Installing Python
  • Setting up VS Code
  • First Python program
  • Variables and naming rules
  • Data types (int, float, str, bool)
  • Type conversion
  • Basic input/output

Module 2: Data Structures (3-4 days)

📁 01_Python_Basics/02_data_structures.py
  • Lists (create, access, modify)
  • List methods (append, remove, sort)
  • Tuples (immutable sequences)
  • Dictionaries (key-value pairs)
  • Sets (unique values)
  • List comprehensions
  • Nested structures

Module 3: Control Flow (2-3 days)

📁 01_Python_Basics/03_functions_loops.py
  • if, elif, else statements
  • Comparison operators
  • Logical operators (and, or, not)
  • for loops
  • while loops
  • break, continue
  • Functions (def)
  • Parameters and return values
  • Lambda functions

Module 4: File Handling (2-3 days)

📁 01_Python_Basics/04_file_handling.py
  • Reading text files
  • Writing text files
  • Reading CSV files
  • Writing CSV files
  • Working with JSON
  • Exception handling (try/except)
  • File paths and directories

5. Python Libraries Syllabus

Module 5: NumPy (3-4 days)

📁 02_Python_Libraries/01_numpy_tutorial.py
  • Creating arrays
  • Array attributes (shape, dtype)
  • Array indexing and slicing
  • Array operations (arithmetic)
  • Broadcasting
  • Statistical functions (mean, std, sum)
  • Reshaping arrays
  • Random number generation

Module 6: Pandas (7-10 days) ⭐ Most Important

📁 02_Python_Libraries/02_pandas_tutorial.py

DataFrames Basics:

  • Creating DataFrames
  • Reading CSV/Excel files
  • DataFrame attributes
  • Viewing data (head, tail, info)
  • Selecting columns
  • Selecting rows (loc, iloc)

Data Cleaning:

  • Handling missing values
  • Removing duplicates
  • Renaming columns
  • Changing data types
  • String operations

Data Manipulation:

  • Filtering data
  • Sorting data
  • Adding/removing columns
  • Groupby operations
  • Aggregations (sum, mean, count)
  • Pivot tables
  • Merging DataFrames (join)
  • Concatenating DataFrames

Advanced:

  • Apply functions
  • Window functions
  • Time series basics
  • Exporting data

Module 7: Data Visualization (4-5 days)

📁 02_Python_Libraries/03_matplotlib_seaborn.py

Matplotlib:

  • Line plots
  • Bar charts
  • Pie charts
  • Histograms
  • Scatter plots
  • Multiple plots (subplots)
  • Customization (colors, labels, titles)
  • Saving figures

Seaborn:

  • Distribution plots
  • Box plots
  • Heatmaps
  • Pair plots
  • Categorical plots
  • Styling

6. SQL Complete Syllabus

Module 8: SQL Basics (3-4 days)

📁 03_SQL_Learning/01_basic_queries.sql
  • SELECT statement
  • WHERE clause
  • Comparison operators (=, >, <, !=)
  • AND, OR, NOT
  • ORDER BY
  • LIMIT
  • DISTINCT
  • LIKE and wildcards
  • IN operator
  • BETWEEN
  • NULL handling (IS NULL)
  • Aliases (AS)

Module 9: Joins (3-4 days)

📁 03_SQL_Learning/02_joins_and_unions.sql
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self joins
  • Multiple joins
  • UNION
  • UNION ALL

Module 10: Aggregations (2-3 days)

📁 03_SQL_Learning/03_aggregations.sql
  • COUNT
  • SUM
  • AVG
  • MIN / MAX
  • GROUP BY
  • HAVING
  • Multiple aggregations
  • Conditional aggregation

Module 11: Advanced SQL (4-5 days)

📁 03_SQL_Learning/04_subqueries.sql
  • Subqueries in WHERE
  • Subqueries in FROM
  • Subqueries in SELECT
  • Correlated subqueries
  • EXISTS operator
  • Common Table Expressions (CTEs)
  • WITH clause
  • Window functions (ROW_NUMBER, RANK)
  • LAG, LEAD
  • Running totals

7. Excel Skills

Excel Basics

📁 04_Excel_Learning/01_excel_functions_guide.md
  • Navigation and shortcuts
  • Basic formulas (SUM, AVERAGE, COUNT)
  • Cell references (relative, absolute)
  • Text functions (LEFT, RIGHT, CONCATENATE)
  • Date functions
  • Logical functions (IF, AND, OR)
  • Lookup functions (VLOOKUP, XLOOKUP)
  • Error handling (IFERROR)

Excel for Data Analysis

📁 04_Excel_Learning/02_pivot_tables_guide.md
  • Pivot Tables
  • Pivot Charts
  • Slicers
  • Conditional Formatting
  • Data Validation
  • What-If Analysis

Data Visualization in Excel

📁 04_Excel_Learning/03_data_visualization_guide.md
  • Chart types and when to use
  • Formatting charts
  • Dashboard creation
  • Sparklines

8. Statistics Basics

Descriptive Statistics

  • Mean, Median, Mode
  • Range, Variance, Standard Deviation
  • Percentiles and Quartiles
  • Distribution shapes (normal, skewed)

Data Analysis Concepts

  • Correlation
  • Regression basics
  • Hypothesis testing basics
  • Probability basics
  • Sampling

Python for Statistics

import numpy as np
import scipy.stats as stats

data = [23, 45, 67, 32, 54, 78, 43, 65, 87, 34]

# Descriptive stats
print("Mean:", np.mean(data))
print("Median:", np.median(data))
print("Std Dev:", np.std(data))
print("Variance:", np.var(data))

# Percentiles
print("25th percentile:", np.percentile(data, 25))
print("75th percentile:", np.percentile(data, 75))

# Correlation
x = [1, 2, 3, 4, 5]
y = [2, 4, 5, 4, 5]
correlation = np.corrcoef(x, y)[0, 1]
print("Correlation:", correlation)

9. Project Ideas

Beginner Projects

📁 05_Projects/
Project Skills Used Duration
Sales Analysis Pandas, Visualization 2-3 days
Customer Segmentation Pandas, RFM Analysis 3-4 days
Survey Analysis Pandas, Statistics 2-3 days
Web Scraping BeautifulSoup, Pandas 2-3 days

Intermediate Projects

Project Skills Used Duration
E-commerce Dashboard SQL, Python, Visualization 1 week
Stock Market Analysis API, Pandas, Time Series 1 week
COVID Data Analysis Pandas, Visualization, Maps 1 week
HR Analytics SQL, Python, Statistics 1 week

Portfolio Projects (For Resume)

  1. End-to-End Sales Analysis

    • Get data → Clean → Analyze → Visualize → Report

  2. Customer Behavior Dashboard

    • RFM Analysis → Segmentation → Recommendations

  3. Financial KPI Tracker

    • SQL queries → Python automation → Excel/Power BI

10. Interview Preparation

Common Interview Questions

Python Questions:

  1. How do you read a CSV file?
  2. What's the difference between list and tuple?
  3. How do you handle missing values in Pandas?
  4. Explain groupby in Pandas.
  5. How do you merge two DataFrames?

SQL Questions:

  1. Difference between WHERE and HAVING?
  2. Types of JOINs and when to use each?
  3. What is a subquery?
  4. Explain window functions.
  5. How to find duplicates?

Excel Questions:

  1. VLOOKUP vs INDEX-MATCH?
  2. How to create a Pivot Table?
  3. What is conditional formatting?
  4. How to remove duplicates?

Statistics Questions:

  1. Mean vs Median - when to use?
  2. What is standard deviation?
  3. Explain correlation.
  4. What is normal distribution?

Sample SQL Interview Questions

-- 1. Find top 5 customers by total purchase
SELECT customer_id, SUM(amount) as total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 5;

-- 2. Find employees earning more than average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 3. Year-over-year growth
SELECT 
    YEAR(order_date) as year,
    SUM(amount) as revenue,
    LAG(SUM(amount)) OVER (ORDER BY YEAR(order_date)) as prev_year,
    (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY YEAR(order_date))) / 
    LAG(SUM(amount)) OVER (ORDER BY YEAR(order_date)) * 100 as growth_pct
FROM orders
GROUP BY YEAR(order_date);

📅 8-Week Study Plan

Week 1-2: Python Basics

  • Day 1-3: Variables, data types
  • Day 4-6: Data structures
  • Day 7-10: Functions, loops
  • Day 11-14: File handling, practice

Week 3-4: Python Libraries

  • Day 15-18: NumPy
  • Day 19-25: Pandas (focus more time here!)
  • Day 26-28: Matplotlib & Seaborn

Week 5-6: SQL

  • Day 29-32: Basic queries
  • Day 33-36: JOINs
  • Day 37-40: Aggregations, Subqueries
  • Day 41-42: Practice on sample database

Week 7: Excel & Statistics

  • Day 43-45: Excel functions & Pivot Tables
  • Day 46-49: Statistics basics

Week 8: Projects & Review

  • Day 50-53: Complete 2 projects
  • Day 54-56: Review weak areas, interview prep

🎯 Daily Study Routine

Morning (1 hour):
├── Read theory/concepts
└── Watch tutorial (if needed)

Afternoon (1-2 hours):
├── Practice coding
└── Solve exercises

Evening (30 min):
├── Review what you learned
└── Note down questions

✅ Final Checklist

Before applying for jobs:

  • Complete all Python basics
  • Proficient in Pandas
  • Can write complex SQL queries
  • Created at least 3 projects
  • GitHub profile with projects
  • LinkedIn updated
  • Resume with skills and projects
  • Practiced interview questions

Remember: Consistency beats intensity. 1-2 hours daily is better than 8 hours once a week!

Good luck on your Data Analysis journey! 🚀