Data Analytics Career Track

Learn the Best Utilization of Excel, SQL, and Python for A-Z Data Analysis and Become a Successful Data Analyst in 2024.

What you’ll learn
You will gain proficiency in Excel, SQL, and Python for data analysis. Prepare for a career as a data analyst with essential professional skills and knowledge.
You will work on practical data analysis projects to apply learned skills. Enhance problem-solving abilities through hands-on data analysis exercises.
You will learn facts and theories for data analysis, statistical analysis, hypothesis testing, and machine learning for foundations of data analytics.
You will learn A-Z data cleaning and manipulation methods, sorting, sorting and conditional filtering, formulas, and functions, graphs and charts in Excel.
You will learn advanced analysis in PIVOT tables and charts, Data Analysis ToolPak for statistical analysis and interactive dashboard in Excel.
You will learn RDBMS fundamentals, covering key concepts such as primary and foreign keys, data types, and the various types of RDBMS and more.
You will learn full stack manipulation of tables, columns, constraints, indices, null values, filtering, joining methods in MySQL or structured query language.
You will learn the important Python programming basics such as variables naming, data types, lists, dictionaries, dataframes, sets, loops, functions etc.
You will master a range of methods and techniques for data cleaning, sorting, filtering, data manipulation, transformation, and data preprocessing in Python.
You will learn to use Python for data visualizations, exploratory data analysis, statistical analysis, hypothesis testing methods and machine learning models.
You will pass 50+ practical assignments, 140+ coding exercises, 10 quizzes with 100+ questions, on all the topics over the entire career track.
You will accomplish two capstone projects on Bank data analysis and Sport data analysis at the end to get the full view of data analysis workflow.

Requirements
Access to computer and internet
Basic computer literacy
No coding experience required
Dedication, patience and perseverance

Description
Are you eager to embark on a rewarding journey into the world of data analytics? Welcome to the Data Analytics Career Track, where you’ll gain a comprehensive skill set and invaluable knowledge to thrive as a data analyst.Course Overview: In this meticulously crafted course, you’ll delve into the core tools and techniques of data analysis: Excel, SQL, and Python. From foundational concepts to advanced methodologies, each module is designed to equip you with the expertise needed to excel in the dynamic field of data analytics.Key Objectives:Proficiency in Essential Tools: Master Excel, SQL, and Python for data analysis, providing you with a versatile toolkit for tackling real-world challenges.Hands-on Experience: Engage in practical data analysis projects and coding exercises, honing your problem-solving skills through immersive learning experiences.Foundational Knowledge: Gain insights into data analysis theories, statistical methods, hypothesis testing, and machine learning fundamentals, laying a solid groundwork for your career.Data Manipulation Mastery: Learn A-Z data cleaning and manipulation techniques, including sorting, filtering, conditional formatting, and advanced analysis with pivot tables and charts.Database Fundamentals: Acquire a deep understanding of relational database management systems (RDBMS), covering key concepts such as primary keys, foreign keys, and SQL manipulation.Python Proficiency: Explore Python programming basics and advanced data analysis techniques, including data visualization, exploratory data analysis, and machine learning model implementation.Practical Assignments: Challenge yourself with over 50 practical assignments, 140 coding exercises, and 10 quizzes spanning the breadth of the course curriculum.Capstone Projects: Apply your newfound skills to real-world scenarios with two comprehensive capstone projects focused on bank data analysis and sports data analysis, providing a holistic view of the data analytics workflow.Benefits of the Course:Career Readiness: Prepare for a successful career as a data analyst with essential professional skills and practical knowledge.Versatility: Gain proficiency in multiple tools and techniques, making you adaptable to diverse data analysis scenarios and industry demands.Problem-solving Skills: Enhance your analytical and critical thinking abilities through hands-on data analysis exercises and coding challenges.Industry-Relevant Learning: Stay ahead of the curve with up-to-date insights into data analysis methodologies and best practices.Portfolio Enhancement: Build a robust portfolio showcasing your expertise through practical projects and assignments, demonstrating your readiness for the job market.Join us on the Data Analytics Career Track and unlock endless possibilities in the world of data analysis. Whether you’re a seasoned professional or a novice enthusiast, this course is your gateway to a fulfilling and prosperous career in data analytics. Enroll today and embark on your journey to success!

Overview
Section 1: Phase 1 – Data Analytics Fundamentals

Lecture 1 My instructions for this phase

Lecture 2 Extra note on analytical world of data

Section 2: All You Need to Know about Data Analysis

Lecture 3 Data analysis definition, types and examples

Lecture 4 Key components of data analysis

Lecture 5 Tools and technologies for data analysis

Lecture 6 Real-world application of data analysis

Section 3: Data Collection: Methods and Considerations

Lecture 7 Various sources of collecting data

Lecture 8 Population v/s sample and its methods

Lecture 9 Consideration for effective data collection

Section 4: Understand Data Cleaning and Its Methods

Lecture 10 Why you cannot ignore cleaning your data

Lecture 11 Various aspects of data cleaning

Lecture 12 Consideration for effective data cleaning

Section 5: Explore Joining and Concatenating Methods

Lecture 13 Various aspects of Joining datasets

Lecture 14 Adding extra data with concatenation

Section 6: Complete Picture of Exploratory Data Analysis

Lecture 15 EDA for generating significant insights

Lecture 16 Methods of exploratory data analysis Part 1

Lecture 17 Methods of exploratory data analysis Part 2

Lecture 18 Methods of exploratory data analysis Part 3

Lecture 19 Consideration for effective EDA

Section 7: Everything about Statistical Data Analysis

Lecture 20 The application of statistical test

Lecture 21 Types of statistical data analysis

Lecture 22 Statistical test v/s Exploratory data analysis

Lecture 23 A Recap on descriptive statistics methods

Lecture 24 Inferential statistics Part 1 – T-tests and ANOVA

Lecture 25 Inferential statistics Part 2 – Relationships measures

Lecture 26 Inferential statistics Part 3 – Linear regression

Lecture 27 Consideration for effective statistical analysis

Section 8: Concepts of Probabilities in Data Analysis

Lecture 28 Probability in data analysis

Lecture 29 Classical probability

Lecture 30 Empirical probability

Lecture 31 Conditional probability

Lecture 32 Joint probability

Section 9: Hypothesis Testing in Statistical Analysis

Lecture 33 Hypothesis testing for inferential statistics

Lecture 34 Selecting statistical test and assumption testing

Lecture 35 Confidence level, significance level, p-value

Lecture 36 Making decision and conclusion on findings

Lecture 37 Complete statistical analysis and hypothesis testing

Section 10: Explore Data Transformation and Its Methods

Lecture 38 Transforming data for improved analysis

Lecture 39 Techniques for data transformation Part 1

Lecture 40 Techniques for data transformation Part 2

Lecture 41 Consideration for effective data transformation

Section 11: Machine Learning for Predictive Efficiency

Lecture 42 ML for data analysis and decision-making

Lecture 43 Widely used ML methods in the data analytics

Lecture 44 Steps in developing machine learning model

Section 12: Explore Data Visualizations and Its Methods

Lecture 45 Visualizing data for the best insight delivery

Lecture 46 Several methods of data visualization Part 1

Lecture 47 Several methods of data visualization Part 2

Lecture 48 Several methods of data visualization Part 3

Lecture 49 Considerations for effective data visualization

Section 13: Phase 2 – Data Analytics in Microsoft Excel

Lecture 50 My instructions for this phase

Lecture 51 Extra note on functions and shortcuts

Section 14: Excel – Data Cleaning and Formatting

Lecture 52 Identifying and removing duplicates

Lecture 53 Dealing with missing values

Lecture 54 Dealing with outliers

Lecture 55 Finding and imputing inconsistent values

Lecture 56 Text-to-columns for data separation

Section 15: Excel – Data Sorting and Filtering

Lecture 57 Applying sorts & filters to narrow down data

Lecture 58 Advanced filtering with custom criteria

Section 16: Excel – Apply Conditional Formatting

Lecture 59 Highlighting cells based on criteria

Lecture 60 Findings top and bottom insights

Lecture 61 Creating color scales and color bars

Section 17: Excel – Formulas and Functions for Data Analysis

Lecture 62 SUM, AVERAGE, MIN, and MAX functions

Lecture 63 SUMIF, and AVERAGEIF functions

Lecture 64 COUNT, COUNTA, and COUNTIF functions

Lecture 65 YEAR, MONTH and DAY for date manipulation

Lecture 66 IF STATEMENTs for conditional operation

Lecture 67 VLOOKUP for column-wise insight search

Lecture 68 HLOOKUP for row-wise insight search

Lecture 69 XLOOKUP for robust & complex insight search

Section 18: Excel – Graphs and Charts for Data Visualization

Lecture 70 Analyze data with Stacked and cluster bar charts

Lecture 71 Analyze data with Pie chart and line chart

Lecture 72 Analyze data with Area chart and TreeMap

Lecture 73 Analyze data with Boxplot and Histogram

Lecture 74 Analyze data with Scatter plot and Combo chart

Lecture 75 Adjusting and decorating graphs and charts

Section 19: Excel – Data Analysis in PivotTables and PivotCharts

Lecture 76 PivotTables for GROUP data analysis PART 1

Lecture 77 PivotTables for CROSSTAB data analysis PART 2

Lecture 78 PivotCharts and Slicers for interactivity

Section 20: Excel – Data Analysis ToolPack for Statistical Analysis

Lecture 79 Descriptive statistics and analysis

Lecture 80 Independent sample t-test for two samples

Lecture 81 Paired sample t-test for two samples

Lecture 82 Analysis of variance – One way ANOVA

Lecture 83 Correlation analysis for relationship

Lecture 84 Multiple linear regression analysis

Section 21: Excel – Creating Interactive Dashboard

Lecture 85 Accumulating relevant information

Lecture 86 Creating a canvas for dashboard

Lecture 87 Developing the complete dashboard

Lecture 88 Final touch up for dashboard decoration

Section 22: Excel Project – Bank Churn Data Analysis

Section 23: Phase 3 – Database Management in MySQL

Lecture 89 My instructions for this phase

Lecture 90 Extra note on functions of MySQL

Section 24: Necessary Fundamentals of RDBMS

Lecture 91 RDBMS: example and importance

Lecture 92 Key features of RDBMS

Lecture 93 Primary key v/s Foreign key

Lecture 94 Types of relationship in RDBMS

Lecture 95 Data types in RDBMS

Section 25: Introduction to SQL for RDBMS

Lecture 96 Introduction to SQL language

Lecture 97 Various platforms of SQL

Section 26: Installing & Loading data in MySQL Interface

Lecture 98 Installing MySQL in Windows and Mac

Lecture 99 Loading CSV dataset in MySQL

Section 27: SQL – Getting Started: Database Management

Lecture 100 Creating database

Lecture 101 Selecting database

Lecture 102 Modifying database

Lecture 103 Deleting database

Lecture 104 SQL query for database management

Section 28: SQL – Fundamental Queries in SQL

Lecture 105 SELECT….FROM: select data from table

Lecture 106 DISTINCT: selecting unique values for column

Lecture 107 AS: selecting columns based on aliases

Lecture 108 WHERE: selecting data based on condition

Lecture 109 Basic SQL Queries

Section 29: SQL – Managing Tables in Database System

Lecture 110 CREATE: creating table

Lecture 111 NOT NULL: limiting null values

Lecture 112 UNIQUE: limiting duplicates

Lecture 113 INSERT INTO: adding values in columns

Lecture 114 UPDATE: updating values based on condition

Lecture 115 DELETE: deleting values based on condition

Lecture 116 TRUNCATE: deleting all the values except table

Lecture 117 DROP: removing entire table

Lecture 118 CHECK: limiting specific values in columns

Lecture 119 Managing Tables in SQL

Section 30: SQL – Working with Columns and Constraint

Lecture 120 ADD COLUMN: adding new column

Lecture 121 MODIFY COLUMN: replacing data types

Lecture 122 RENAME COLUMN: changing column names

Lecture 123 DROP COLUMN: deleting columns

Lecture 124 ADD CONSTRAINT: adding primary key

Lecture 125 ADD CONSTRAINT….REFERENCES: adding foreign key

Lecture 126 DROP CONSTRAINT: deleting keys

Lecture 127 Working with Columns and Constraint

Section 31: SQL – Working with Indexing Operation

Lecture 128 CREATE INDEX: creating new index

Lecture 129 CREATE UNIQUE INDEX: creating index without duplicates

Lecture 130 DROP INDEX: deleting existing index

Lecture 131 Working with Indexing Operation

Section 32: SQL – Dealing with NULL/MISSING values

Lecture 132 IS NULL: filtering the actual values out

Lecture 133 IS NOT NULL: filtering the missing values out

Lecture 134 Dealing with NULL values

Section 33: SQL – Various Aspects of Filtering Data

Lecture 135 AND: combining two or more conditions

Lecture 136 OR: flexible logical operator

Lecture 137 NOT: excluding values from filteration

Lecture 138 BETWEEN…AND: filtering ranges of values

Lecture 139 LIKE: filtering based on pattern

Lecture 140 IN: precise logic for multiple conditions

Lecture 141 LIMIT: filtering with limited data

Lecture 142 Various Aspects of Filtering Data

Section 34: SQL – IMPORTANT MySQL String Functions

Lecture 143 CHAR_LENGTH: finding the length of text

Lecture 144 CONCAT: adding different strings together

Lecture 145 LOWER: converting into lowercase

Lecture 146 UPPER: converting into uppercase

Lecture 147 TRIM: removing unnecessary gaps

Lecture 148 REPLACE: replacing old value by new value

Lecture 149 IMPORTANT MySQL String Functions

Section 35: SQL – IMPORTANT MySQL Arithmetic Functions

Lecture 150 ABS: negative to positive value

Lecture 151 SUM: calculating the total value

Lecture 152 AVG: calculating the average value

Lecture 153 COUNT: counting total items

Lecture 154 DIV: dividing numeric data

Lecture 155 MIN: finding the lowest value

Lecture 156 MAX: finding the highest value

Lecture 157 MySQL Arithmetic Functions

Section 36: SQL – IMPORTANT MySQL Transformation Functions

Lecture 158 POWER: multiple multiplications

Lecture 159 ROUND: decreasing the decimals

Lecture 160 SQRT and LOG: transformation functions

Lecture 161 MySQL Transformation Functions

Section 37: SQL – IMPORTANT MySQL Datetime Functions

Lecture 162 DATEFORMAT: formatting the date shape

Lecture 163 DATEDIFF: finding the date difference

Lecture 164 DAY/MONTH/YEAR: extracting parts of dates

Lecture 165 MySQL Datetime Functions

Section 38: SQL – Grouping and Sorting data in SQL

Lecture 166 ORDER BY: sorting data based on a column

Lecture 167 GROUP BY: group data analysis with functions

Lecture 168 Grouping and Sorting data

Section 39: SQL – JOINS for Data Retrievals in SQL

Lecture 169 INNER JOIN: joining on common values

Lecture 170 LEFT JOIN: joining on left table values

Lecture 171 RIGHT JOIN: joining on right table values

Lecture 172 CROSS JOIN: joining all values from tables

Lecture 173 JOINS for Data Retrievals

Section 40: SQL – Advanced Functions and Operations

Lecture 174 HAVING: advanced conditional format

Lecture 175 EXISTS: nested filtering between tables

Lecture 176 ANY: nested filtering between tables

Lecture 177 CASE: finding the conditional outcomes

Lecture 178 Advanced Functions and Operations

Section 41: SQL – Stored Procedure and Comments

Lecture 179 SQL comments systems

Lecture 180 Storing and executing procedures

Lecture 181 Stored Procedure and Comments

Section 42: Phase 4 – Data Analytics A-Z in Python

Lecture 182 My instructions for this phase

Lecture 183 Extra note on python data analysis

Lecture 184 Resources used in the course

Section 43: Setting Up Python and Jupyter Notebook

Lecture 185 Installing Python and Jupyter Notebook – Mac

Lecture 186 Installing Python and Jupyter Notebook – Windows

Lecture 187 More alternative methods – Check the article

Section 44: Python – Starting with Variables to Data Types

Lecture 188 Getting started with first python code

Lecture 189 Assigning variable names correctly

Lecture 190 Various data types and data structures

Lecture 191 Converting and casting data types

Lecture 192 Starting with Variables to Data Types

Section 45: Python – Operators in Python Programming

Lecture 193 Arithmetic operators (+, -, *, /, %, **)

Lecture 194 Comparison operators (>, <, >=, <=, ==, !=)

Lecture 195 Logical operators (and, or, not)

Lecture 196 Operators in Python Programming

Section 46: Python – Dealing with Data Structures

Lecture 197 Lists: creation, indexing, slicing, modifying

Lecture 198 Sets: unique elements, operations

Lecture 199 Dictionaries: key-value pairs, methods

Lecture 200 Several data structures

Section 47: Python – Conditionals Looping and Functions

Lecture 201 Conditional statements (if, elif, else)

Lecture 202 Nested logical expressions in conditions

Lecture 203 Looping structures (for loops, while loops)

Lecture 204 Defining, creating, and calling functions

Lecture 205 Conditionals Looping and Functions

Section 48: Python – Sequential Cleaning and Modifying Data

Lecture 206 Preparing notebook and loading data

Lecture 207 Identifying missing or null values

Lecture 208 Method of missing value imputation

Lecture 209 Exploring data types in a dataframe

Lecture 210 Dealing with inconsistent values

Lecture 211 Assigning correct data types

Lecture 212 Dealing with duplicated values

Lecture 213 Sequential data cleaning and modifying

Section 49: Python – Various Methods of Data Manipulation

Lecture 214 Sorting data by column and order

Lecture 215 Filtering data with boolean indexing

Lecture 216 Query method for precise filtering

Lecture 217 Filtering data with isin method

Lecture 218 Slicing dataframe with loc and iloc

Lecture 219 Filtering data for many conditions

Lecture 220 Various methods of data manipulation

Section 50: Python – Merging and Concatenating Dataframes

Lecture 221 Joining dataframes horizontally

Lecture 222 Concatenate dataframes vertically

Lecture 223 Merging and joining dataframes

Section 51: Python – Applied Exploratory Data Analysis Methods

Lecture 224 Frequency and percentage analysis

Lecture 225 Descriptive statistics and analysis

Lecture 226 Group by data analysis method

Lecture 227 Pivot table analysis – all in one

Lecture 228 Cross-tabulation analysis method

Lecture 229 Correlation analysis for numeric data

Lecture 230 Applied exploratory data analysis

Section 52: Python – Exploring Data Visualisations Methods

Lecture 231 Understanding visualisation tools

Lecture 232 Getting started with bar charts

Lecture 233 Stacked and clustered bar charts

Lecture 234 Pie chart for percentage analysis

Lecture 235 Line chart for grouping data analysis

Lecture 236 Exploring distribution with histogram

Lecture 237 Correlation analysis via scatterplot

Lecture 238 Matrix visualisation with heatmap

Lecture 239 Boxplot statistical visualisation method

Lecture 240 Exploring data visualisations methods

Section 53: Python – Practical Data Transformation Methods

Lecture 241 Investigating distribution of numeric data

Lecture 242 Shapiro Wilk test of normality

Lecture 243 Starting with square root transformation

Lecture 244 Logarithmic transformation method

Lecture 245 Box-cox power transformation method

Lecture 246 Yeo-Johnson power transformation method

Lecture 247 Practical data transformation methods

Section 54: Python – Statistical Tests and Hypothesis Testing

Lecture 248 One sample t-test

Lecture 249 Independent sample t-test

Lecture 250 One way Analysis of Variance

Lecture 251 Chi square test for independence

Lecture 252 Pearson correlation analysis

Lecture 253 Linear regression analysis

Lecture 254 Statistical tests and hypothesis testing

Section 55: Python – Exploring Feature Engineering Methods

Lecture 255 Generating new features

Lecture 256 Extracting day, month and year

Lecture 257 Encoding features – LabelEncoder

Lecture 258 Categorizing numeric feature

Lecture 259 Manual feature encoding

Lecture 260 Converting features into dummy

Lecture 261 Feature engineering methods

Section 56: Python – Data Preprocessing for Machine Learning

Lecture 262 Selecting features and target

Lecture 263 Scaling features – StandardScaler

Lecture 264 Scaling features – MinMaxScaler

Lecture 265 Dimensionality reduction with PCA

Lecture 266 Splitting into train and test set

Lecture 267 Preprocessing for machine learning

Section 57: Python – Supervised Regression ML Models

Lecture 268 Linear regression ML model

Lecture 269 Decision tree regressor ML model

Lecture 270 Random forest regressor ML model

Lecture 271 Supervised regression ML models

Section 58: Python – Supervised Classification ML Models

Lecture 272 Logistic regression ML model

Lecture 273 Decision tree classification ML model

Lecture 274 Random forest classification ML model

Lecture 275 Supervised classification ML models

Section 59: Python – Segmentation with KMeans Clustering

Lecture 276 Calculating within cluster sum of squares

Lecture 277 Selecting optimal number of clusters

Lecture 278 Application of KMeans machine learning

Lecture 279 Data segmentation with KMeans clustering

Section 60: Final Project – Sports Data Analytics

Section 61: What’s Next?

Lecture 280 Your next steps – Portfolios

Lecture 281 Your next steps – LinkedIn

Section 62: Extra – Python Error Message

Lecture 282 ModuleNotFound error

Lecture 283 Syntax error

Lecture 284 Key error

Lecture 285 Index error

Lecture 286 Attribute error

Lecture 287 Value error

Lecture 288 Type error

Lecture 289 Resource

Section 63: Extra – Fasten Your Coding

Lecture 290 Diagnosing errors

Lecture 291 Debugging errors

Lecture 292 Enhancing codes

Lecture 293 ChatGPT prompt

Those who are interested in entering the field of data analytics and want to learn the complete tools and techniques used in the industry.,Those who are highly interested in learning complete data analytics using Excel, SQL and Python.,This course is NOT for those who are interested to learn data science or advanced machine learning application.