Core & Advanced Python for Data Analytics:

GETTING STARTED

• History & need of Python
• Application of Python
• Advantages of Python
• Disadvantages of Python
• Installing Python
• Program Structure
• Interactive Shell
• Executable or Script Files
• Userinterface or IDE

PYTHON FUNDAMENTALS

• Working with Interactive Mode
• Working with Script Mode
• Python Character Set
• Python Tokens, Keywords, Identifiers, Literals, Operators
• Variables and Assignments
• Input and Output in Python

DATA HANDLING

• Data Types: Numbers, Strings, Lists, Tuples, Dictionary, Set, Frozenset, Bool
• Mutuable and Immutable

PYTHON FUNDAMENTALS (Part 2)

• Introduction to Python String
• Accessing individual Elements
• String Operators
• String Slices
• String Functions and Methods

LIST MANIPULATION

• Introduction to Python List
• Creating List
• Accessing List
• Joining List
• Replicating List
• List Slicing

TUPLES

• Introduction to Tuple
• Creating Tuples
• Accessing Tuples
• Joining Tuples
• Replicating Tuples

DICTIONARIES

• Introduction to Dictionary
• Accessing values in dictionaries
• Working with dictionaries
• Properties

SET & FROZENSET

• Introduction to Set & Frozenset
• Creating Set and Frozenset
• Accessing and Joining
• Replicating and Slicing

OPERATORS

• Arithmetic Operators
• Relational Operators
• Logical Operators
• Membership Operators
• Identity Operators
• Bitwise Operators
• Assignment Operators
• Operators Precedence
• Evaluating Expression
• Type Casting

PROGRAM CONTROL FLOW

• Conditional Statements: The if Statement, The if-else Statement, The if-elif Statement, Nested if Statement, Python Indentation
• Looping & Iteration: The for Loop, The While Loop, Loop Else Statement, Nested Loops, Break and Continue
• The Range Function

INTRODUCTION TO FUNCTIONS

• Built-In Functions
• Introduction to Functions
• Using a Function
• Python Function Types
• Structure of Python Functions
• User Defined Functions
• Structure of Python Program w.r.t. UDF
• Types of Functions
• Invoking UDF
• Arguments and Parameters
• Default Arguments, Named Arguments
• Scope of Variables
• Lambda Variables
• Recursion Function

MODULES AND PACKAGES

• Built-In Modules
• Importing Modules in Python Programms
• Working with Random Modules
• User Defined Functions
• Structure of Python Modules
• Text and Bytes Files
• MS Excel Files

CLASSES AND OBJECTS

• Classes as User Defined Data Type
• Objects as Instances of Classes
• Creating Class and Objects
• Variables & Methods in Class

EXCEPTION HANDLING

• Default Exception & Errors
• Catching Exceptions
• Raise an Exception
• Try... Except Statement
• Raise, Assert, Finally Blocks
• User Defined Exception

INTRODUCTION TO OOPS

• Procedural vs Modular Programming
• The Object Oriented Programming
• Data Abstraction
• Data Hiding
• Encapsulation
• Modularity
• Inheritance
• Polymorphism

DATABASE

• Introduction to MySQL
• PYMYSQL Connections
• Executing Queries
• Transactions
• Handling Error

GUI PROGRAMMING

• Introduction
• Tkinter Programming
• Tkinter Widgets
• Frame, Button, Label, Entry

TURTLE PROGRAMMING

• Introduction to Turtle
• Controlling Turtle
• Animation Programming

MULTITHREADING

• Thread and Process
• Starting a Thread
• Threading Module
• Synchronizing Threads
• Multithreaded Priority Queue

REGULAR EXPRESSIONS

• Match Function
• Search Function
• Grouping
• Matching at Beginning or End
• Match Objects
• Flags

ADVANCED CONCEPTS

• Decorators
• Generators
• Iterators
• Co-Routines

INTRODUCTION TO DATA ANALYTICS

• Why Analytics?
• Traditional Data Management
• Analytical Tools
• Types of Analytics
• Dimensions and measures
• Why Learn Python for Data Analysis?

LIBRARIES FOR DATA ANALYTICS

• Anaconda
• Numpy
• Pandas
• Matplotlib
• Seaborn

JUPYTER NOTEBOOK

• Create Documentation
• Code Mode
• Markdown Mode

STATISTICS

• Mean, Median, Mode
• Z-Scores
• Bias-Variance Dichotomy
• Sampling t-tests
• Sample vs Population statistics
• Random Variables
• Probability Distribution function
• Expected Value
• Binomial Distributions

NUMPY

• Creating Numpy-Arrays
• Indexing and Slicing in Numpy
• Downloading and Parsing Data
• Creating Multidimensional arrays
• Numpy Data Types
• Array Tributes
• Creating arrays views copies
• Manipulating Arrays Shapes I/O

PANDAS

• Using Multilevel Series
• Series and DataFrames
• Grouping, Aggregating
• Merge Dataframes
• Generate Summary Tables
• Group Data Into Logical Pieces
• Manipulate Dates
• Creating Metrics for Analysis
• Data Wrangling
• Merging and Joining
• Analytics Vidhya Dataset- Loan Prediction Problem
• Data mugging using Pandas
• Building Predictive Model

MATPLOTLIB

• Scatter Plot
• Bar Charts, Histogram
• Stack Charts
• Legend Title Style
• Figures and Subplots
• Plotting Function in Pandas
• Labelling and Arranging Figures
• Save Plots

SEABORN

• Style Functions
• Color Palettes
• Distribution Plots
• Categorical Plots
• Regression Plots
• Axis Grid Objects

WEB SCRAPING

• Scraping Webpages
• Beautifulsoup Packages
• Real time project

INTRODUCTION TO ML

• What is ML? and Why ML?
• Introduction to Supervised ML
• Introduction to Unsupervised ML
• ML Glossary-Variable Types, k-fold
• Data Split & Hyper Parameter

Basic to Advance EXCEL

VBA VISUAL BASIC APPLICATION

Microsoft Excel Fundamentals

• Microsoft excel startup screen
• Customizing the excel quick access toolbar
• More on the excel interface
• Understanding the structure of an excel workbook
• Saving an excel document
• Opening an existing excel document
• Common excel shortcut keys

Entering & Editing Text Formulas

• Entering text to create spreadsheet titles
• Working with numeric data in excel
• Entering data values in excel
• Working with cell references
• Creating basic formulas in excel
• Relative versus absolute cell references in formulas

Working with Excel Worksheet

• Working with the SUM() function
• Working with the MIN() and MAX() function
• Working with the AVERAGE() function
• Working with the COUNT() function
• Adjacent cells error in excel calculations
• Using the auto sum command
• Excel's auto sum shortcut key
• Using the autofill command to copy formulas

Modifying in Excel Worksheet

• Moving and copying data in an excel worksheet
• Inserting and deleting rows and columns
• Changing the width and height of cells
• Hiding and unhiding excel rows and columns
• Renaming an excel worksheet
• Deleting an excel worksheet
• Moving and copying an excel worksheet

Formatting Data in an Excel Sheet

• Working with font formatting commands
• Changing the background colour of a cell
• Adding borders to cells
• Excel cell borders continued
• Formatting data as currency values
• Formatting percentages
• Using excel's format painter
• Creating styles to format data
• Merging and centering cells
• Using conditional formatting
• Editing excel conditional formatting

Inserting Images and Shapes in to an Excel Worksheet

• Inserting Images
• Inserting excel shapes
• Formatting excel shapes
• Working with excel SmartArt

Creating Basic Charts in excel

• Creating an excel column charts
• Working with the excel chart ribbon
• Adding and modifying data on an excel chart
• Formatting an excel chart
• Moving a chart to another worksheet
• Working with excel pie charts

Printing Document

Working with Excel List

• Sorting a List Using Single Level Sort
• Sorting a List Using Multi-Level Sorts
• Using Custom Sorts in an Excel List
• Filter an Excel List Using the AutoFiller Tool
• Creating Subtotals in a List
• Format a List as a Table
• Using Conditional Formatting to Find Duplicates
• Removing Duplicates

Excel List Function

• Introduction to Excels Function: DSUM()
• Excecl DSUM Function Single Criteria Continued
• Excel DSUM Function with OR Criteria
• Excel DSUM Function with AND Criteria
• Excel Function: DAVERAGE()
• Excel Function: DCOUNT()
• Excel Function: SUBTOTAL()

Data Validation

• Creating an Excel Data Validation List
• Excel Decimal Data Validation
• Adding a Custom Excel Data Validation Error
• Dynamic Formulas by Using Excel Data Validation Techniques

Importing & Exporting Data

• Importing Data into Microsoft Excel
• Importing Data from Text Files
• Importing Data from Microsoft Access
• NEW VERSION -- Import data From Text Files into Excel
• NEW VERSION -- Import Data From a Database into Excel
• Microsoft Excel Legacy Import Options for New Excel Versions
• Exporting Data to a Text File

Excel Pivot Table

• Creating an Excel PivotTable
• Modifying Excel PivotTable Calculations
• Formatting PivotTable Data
• Modifying PivotTable Calculations
• Drilling Down into PivotTable Data
• Creating Pivot Charts
• Filtering PivotTable Data Filtering with the Slicer Tool

Working with Excel Power Pivot Table

• Introduction to Excel Power Pivot
• Why PowerPivot?
• Activating the Excel PowerPivot AddIn
• Creating Data Models with PowerPivot
• Excel Power Pivot Data Model Relationships
• Creating PivotTables based on Data Models

Working with Large Sets of Excel Data

• Using the Freeze Panes Tool
• Grouping Data (Columns and/or Rows)
• Print Options for Large Sets of Data Linking Worksheets (3D Formulas)
• Consolidating Data From Multiple Worksheets

Working with Excel's Conditional Function

• Working with Excel Name Ranges
• Advantages and Disadvantages of Excel Name Ranges
• Editing an Excel Name Range
• Using Excel's IF() Function
• Excel's IF() Function with a Name Range
• Nesting Functions with Excel
• Nesting Excels AND() Function within the IF() Function
• Using Excel's COUNTIF() Function
• Using Excel's SUMIF() Function
• Using Excel's IFERROR() Function

Working with Excel Lookup Function

• Microsoft Excel LOOKUP() Function
• Microsoft Excel VLOOKUP() Function
• Micrososft Excel HLOOKUP() Function
• Microsoft Excel XLOOKUP() Function
• Microsoft Excel INDEX() Function
• Microsoft Excel MATCH() Function
• Microsoft Excel INDEX() and MATCH() Function Combined
• Microsoft Excel INDEX() and MATCH() Function Combined Continued
• Creating a Dynamic HLOOKUP() with the MATCH() Function

Working with Excel Text Based Function

• Using Excel's LEFT(), RIGHT() and MID() Functions
• Using Excel's LEN() Function
• Using Excel's SEARCH() Function
• Using Excel's CONCATENATE() Function

Auditing an Excel Workbook

• Tracing Precedents in Excel Formulas
• Tracing Dependents in Excel Formulas Working with the Watch Window
• Showing Formulas

Protecting Excel Worksheet & WORKBOOK

• Protecting Specific Cells in a Worksheet
• Protecting the Structure of a Workbook
• Adding a Workbook Password

What IF? Tool

• Working with Excel's Goal Seek Tool
• Working with Excel's Solver Tool Building Effective Data Tables in Excel
• Creating Scenarios in Excel

MACRO

• Understanding Excel Macros
• Activating the Developer Tab in Excel
• Creating a Macro with the Macrp Recorder
• Editing a Macro with VBA
• Creating Buttons to Run Macros

Working with VBA in Excel

• Excel Function for data Analytics course
• Introduction
• UDF and and INBUILT function - Begin with VLOOKUP

What is VBA?

• Introduction to Another Languages
• VBA: An Event Driven Programming
• VBA: An Object Based Programming
• Advantages And Disadvantages of VBA
• Objects, Procedures And Properties

VBA Ide

• Opening The Excel VBA Ide
• Menu Bar
• Toolbar
• Project Window
• Code Window
• Immediate Window
• Customizing The VBA

Getting Started with Macro

• Recording The Macro
• Examining The Macro
• Saving Workbooks That Contain Macros

Fundamentals of VBA Language

• Variable & Constants, Data Types
• Keywords, Arguments
• Procedures: Sub And Function
• Local Vs Global Variable Declaration
• Procedures: Public Or Private, Comments

Control Flow & Loops Statements

• Relational And Logical Operators
• If....Then, If....Then....Else, If....Then....Elseif....Else Selse Case ( With To And With Is )
• Do While....Loop, Do loop....While, Do Until....Loop
• Do Loop....Until
• For....Next, For Each....Next
• While....Wend
• The Exit statement

Using VBA And Worksheet Function

• VB Functions
• Excel Functions
• The Workbooks Collection
• The Sheets Collection
• Activate and Select
• Range Property
• Cells Property
• Offset Property
• Resize Property
• Current region Property
• Columns and Rows Properties

Working with Dialog Boxes and Form Objects

• Working With Dialog Boxes and Form Objects
• Using Lables, Using Text Boxes
• Using The Command Button Control

(SQL)

Unit 1: Introduction to Data, Databases and SQL

Section 1

• Define and explain the purpose of data and the most widely used data types.
• Understand the hierarchy of units used to calculate data size.
• Calculate the size of your own data.
• Define and explain the purpose of databases.
• Understand relational vs. non-relational databases.
• Use design and ethics principles to guide database use, including referential integrity and ACID.
• Learn about the Structured Query Language (SQL) and its history.
• Understand the main differences between various SQL flavors: PostgreSQL, MySQL, and SQLite.
• Learn further details about PostgreSQL, which will be used in this course.

Section 2

• Examine the structure of a SQL database.
• Use the SELECT statement to display all columns of a table.
• Understand that SQL is case-insensitive.
• Understand the purpose of the keyword NULL and SQL's three-valued logic.
• Order query results using ORDER BY.
• Limit the number of results using the keyword LIMIT.

Section 3

• Use the SELECT statement to only display one column of interest.
• Use the SELECT statement to display two or more columns.
• Change the order of the displayed columns if needed.
• Skip some rows in the output using the keyword OFFSET.
• Order multiple columns at the same time.

Section 4

• Filter the results of queries using the keyword WHERE.
• Access a table in a schema by typing schema.table.
• Remove duplicate results with SELECT DISTINCT.

Section 5

• Use aggregate functions:
• Function COUNT() to count results. Functions MIN(), MAX() to find minimum and maximum values. Functions SUM(), AVG() to calculate the sum and average of values, respectively.
• Narrow down queries using WHERE ... LIKE ... and WHERE ... BETWEEN ...
• Combine multiple conditions in the search using the keywords AND, OR, NOT.

Unit 2: SQL Queries

Section 6

• Practice basic SQL queries by exploring the Northwind database and in particular the table Employees.

Section 7

• Practice basic SQL queries by exploring the table Products of the Northwind database.

Section 8

• Practice basic SQL queries by exploring the table Products of the Northwind database.
• Use SQL for calculations.
• Use the WHERE ... IN ... clause.

Section 9

• Practice basic SQL queries by exploring the table Order Details of the Northwind database.
• Create new columns and name them using the keyword AS.
• Group results for better readability using the GROUP BY ... and GROUP BY ... HAVING ... clauses.

Section 10

• Practice basic SQL queries by exploring the Customers table of the Northwind database.

Unit 3: Creating and Managing Tables

Section 11

• Explain the different data types in the SQL standard and in PostgreSQL.
• Create and drop schemata.
• Learn about the two schemata in NCLab where you can store your own data.
• Create tables.

Section 12

• Insert complete rows (without using column names).
• Insert incomplete rows (without using column names).
• Insert incomplete rows (using the names of columns).

Section 13

• Insert multiple rows at once.
• Create a new table by copying an existing table.
• Create a new empty table which has the same structure as an existing table.
• Create a new table by copying selected rows from an existing table.

Section 14

• Insert selected rows from a table into an existing table.
• Use the powerful statement ALTER TABLE to modify tables, such as:
• rename tables, add / rename / drop (= delete) columns, change data types of columns, etc.
• Delete all rows from a table, selected rows, or entire tables.
• Delete selected rows from a table.
• Delete entire tables.
• Understand that the result of the VALUES clause and of the SELECT statement is a TABLE.

Section 15

• Define constraints and specify default values.

Unit 4 – Joining Tables

Section 16

• Combine data from two different tables using the inner join operation.
• Initially, the tables are matched based on a shared column of the same name.
• Write inner joins in three simple steps:
• Basic SELECT query followed by the columns one wants to display,
• Add INNER JOIN followed by the second table name, Add USING
• followed by the name of the shared column in parentheses.
• Join three and more tables, and that joining multiple tables is equally simple as joining just two.

Section 17

• Review the structure of the tables in the schema World.
• Perform an inner join of tables based on columns of different names, using the keyword ON.
• Abbreviate table names using the keyword AS.
• Understand that some keywords (for example, AS) can be omitted.
• Write inner joins using an alternative (implicit) syntax without the keywords INNER JOIN and USING/ON.

Section 18

• Practice inner joins by solving practical tasks related to the schema World.

Section 19

• Review the schema Northwind, and then perform calculations which require combining data from various tables in this schema.
• Combine inner joins with filtering, grouping and other basic SQL techniques.

Section 20

• Practice inner joins performing calculations which combine data from various tables in schema Northwind.
• Combine inner joins with filtering, grouping and other basic SQL techniques.

Unit 5 – Conditional Expressions

Section 21

• Understand the difference between a statement and an expression.
• Insert “intelligent” conditional expressions into your queries using the keyword CASE.
• Use both the “searched version” of the CASE expression which is more versatile, and the “simple (switch) version”.
• Combine a CASE expression with an inner join.

Section 22

• Learn about additional important applications of conditional expressions including:
• How to use them to split values into different columns.
• How to count non-NULL values in the columns of a SELECT statement.
• How to use conditional expressions in aggregate functions COUNT, SUM, MIN, MAX, AVG.
• Understand the difference between using conditional expressions and the WHERE clause.

Section 23

• Simplify filtering values in aggregate functions using the FILTER clause.
• Learn how integer division works in SQL, and how to prevent potential problems which it may cause.

Section 24

• Learn about various situations which can cause an error in SQL, and how to prevent them using suitable CASE expressions. In particular, learn how to prevent division-by-zero errors.
• Learn how SQL responds when NULL values are combined with numbers and text strings.
• Use CASE expressions in the ORDER BY clause.

Section 25

• Replace NULL with selected values using the function COALESCE
• Replace selected values with NULL using the function NULLIF.
• Learn about the relation between these functions and the conditional expression CASE.
• Replace values in tables by combining the COALESCE and NULLIF functions.

Unit 6 – Text Strings

Unit 7 – Sets and Subqueries

Unit 8 – Advanced Joins

Unit 9 – Introduction to Data Analysis with Python

Introduction to RDBMS

• What is Relational Database Package
• Difference between SQL & Database
• Installing MySQL Server Database

SQL Operators

• Arithmetic Operators
• Logical Operators
• Conditional Operators
• Like, between, in Operators

SQL Functions

• String Functions
• Aggregate Functions
• Date & Time Functions

SQL Basic

• DDL: Create, Alter, Drop, etc.
• DML: Insert, Update, Delete etc.
• DQL: Select
• Autoincrement Field
• SQL Comments
• SQL Aliases
• Savepoint & Rollback

SQL Clauses

• Order by
• Where
• Limit/top
• Group by
• Having

Stored Procedures & Functions

• Understanding Stored Procedures and Their key benefits
• Working with Stored Procedure
• Studying User-Defined Functions

SQL Joins

• Inner Join
• Left Join
• Right Join
• Full Join

Working with CSV Files

• How to write result to CSV files
• How to read CSV file

SQL Constraints

• Not NULL, Unique Key
• Primary Key, Check
• Default, Foreign Key

SQL View

• Creating View
• Updating View
• Fetching data From View

Python Database Connectivity

• How to write result to CSV Files
• How to read CSV File

Power BI Course Syllabus

Level 1: Get started with Power BI

• Understanding the fundamentals of Power BI
• Exploring the Power BI ecosystem: Desktop, Service, Mobile
• Learn about Power BI capabilities
• Import data from different data source: Excel, SQL, Dataverse, Web

Level 2: Data Transformation

• Basic transformation:

Use the first row as a header, Remove row, Change type, Add new columns

• Add conditional columns
• Unpivot the data
• Remove blank/null values
• Learn about best practices for data cleaning
• Start exploring M Query language

Level 3: Data Modeling

• Create relationships between multiple tables
• Learn one-to-many, one-to-one, many-to-many and other joins
• Understand cross-filtering relationships from one side or both sides

Level 4: Creating Visualizations

• Building basic visualizations: charts, graphs, tables
• Learn formatting tab and design options
• Implementing interactive elements using filters and slicers
• Create maps in Power BI

Level 5: Advanced Visualization Techniques

• Use bookmark, drill-down, edit interaction
• Mastering the art of storytelling through data
• Practice custom or advanced visuals
• Implementing drill-through functionalities

Level 6: Power BI Service and Collaboration

• Publishing reports to the Power BI service
• Creating and sharing dashboards
• Create a workspace and convert the report into a dashboard
• Publish an App using the Power BI report
• Managing data refresh schedules

Level 7: DAX and Measures

• Using Quick measures
• Create calculated columns
• Creating calculated measures with DAX
• Understand filter context
• Implementing time intelligence functions

Level 8: Performance Optimization

• Looks for best practices
• Improve performance by reducing queries
• Optimizing DAX time with a performance analyzer
• Format data, group the columns
• Custom formatting and grouping selections

Level 9: Real-World Projects and Best Practices

• Practice top Power BI projects
• Designing end-to-end solutions using Power BI
• Optimizing performance and data refresh strategies

Top Data Analyst Careers

1. Data Scientist
2. Business Analyst
3. Machine Learning Engineer
4. Financial Analyst
5. Healthcare Analyst
6. Marketing Analyst
7. Product Analyst
8. Operations Analyst
9. Risk Analyst
10. Quantitative Analyst

Tableau Course Syllabus

• Data Preparation Using Tableau Prep
• Data Connection with Tableau Desktop
• Basic Visual Analytics
• Calculations in Tableau
• Advanced Visual Analytics
• Level of Detail (LOD) Expressions in Tableau
• Geographic Visualizations in Tableau
• Advanced Charts in Tableau
• Dashboards and Stories (Self-Paced)
• Get Industry Ready (Self-Paced)
• Exploring Tableau Online
• In-Class Project