Data Analytics Masterclass: Excel, Python, PowerBI & ChatGPT

Open the first analysis environment in Jupyter notebook. Write your first Python code using the print function to display hello world, and learn how to run the cell with shift+enter.

Master Python variables, assign values with the assignment operator, and follow naming conventions using letters, numbers, and underscores, noting that names cannot start with a number.

Master Python data types, including integers, floats, strings (or objects), and booleans, through practical examples that show type checks, whole versus decimal numbers, and text versus numeric values.

Learn how to convert data types in Python using int and float, handle strings, and ensure correct numeric operations with type conversion for data analysis.

Explore Python arithmetic operators, including plus, minus, multiplication, division, modulus and exponential, with practical examples of calculating sums, differences, remainders, and powers.

Explore Python comparison operators such as greater than, less than, greater than or equal to, less than or equal to, equal to, and not equal, and learn how they return boolean results.

Explore how and, or, and not create boolean outcomes by combining conditions in Python. Learn to test comparisons, evaluate true or false results, and apply not to invert logic.

Create Python lists, indexing and slicing items, and modify lists using append, insert, remove, and replace to manage strings, numbers, booleans, and mixed data.

Explore conditional statements in Python using if, elif, and else to control output based on comparisons such as greater than or less than, with practical data analysis examples.

Explore how to use nested logical expressions within Python conditional statements to classify ages and temperatures, employing and, or, and not operators for real-world automation.

Master Python looping structures with for loops and while loops to iterate lists. Build and filter lists to identify even numbers using modulus, and stop iterations with break.

Define, create, and call a custom Python function to calculate the percentage from total respondents and category counts, demonstrated with male and female examples.

Learn to prepare a Jupyter notebook, install and import pandas and numpy, and load data with read_csv or read_excel to perform data cleaning and analysis.

Identify missing values in each dataset column using Python in a Jupyter notebook. Use isnull and sum to count per-column missing values and reveal income and city gaps.

Impute missing values for income and city using simple imputer from sklearn.impute, applying mean or median for numeric data and most_frequent for categorical data; fit and transform.

Learn to inspect and assign correct data types for each dataframe column using Python, fixing inconsistent data and converting object, int, float, and date time types for accurate insights.

Learn to assign correct data types to data columns using the astype method, converting values like customer lifespan to integers and date of purchase to date time, with dtype checks.

Identify duplicated customer records in the data to ensure unique information and unbiased insights. Apply the duplicated method to locate duplicates and drop them with inplace=True on the original data.

Sort data by a given column and order using Python's sort_values, and control ascending or descending with the by and ascending attributes.

Filter data with boolean indexing by applying conditional operators to derive column values. Use examples like ages higher than 25 and male gender, and purchases above five.

Apply the dot query Python method to filter data by column, operator, and value, enabling precise selections such as churned male customers with frequency greater than five.

Use the pandas isin method to filter a data table by column values, such as cities like New York and Los Angeles, by deriving the column and applying the filter.

Learn to slice a dataframe with the dot loc and dot iloc methods, filtering rows and columns by index values or column names to extract the first five customers.

Filter data with two or more conditions using or and and combinators, exploring age thresholds and life span ranges like between 20 and 50 years old and 2 to 5 months.

Learn to perform frequency analysis in Python using value_counts on categorical data, compute counts and percentages for channels and gender, and compare category distributions.

Apply Python to compute descriptive statistics for numeric variables using final_data.describe, round results, and interpret mean, standard deviation, min, max, and percentiles to identify inconsistencies and insights.

Apply Python to perform group by analysis to summarize numeric values by a categorical column, like city, using mean or median; derive insights to target high-revenue cities.

Master pivot table basics in Python to summarize data by mean, median, frequency, and percentages, compare with group by, and rename outputs for churn status insights (recency, frequency, monetary score).

Learn to generate cross tabulations with Python pandas, using two categorical variables to compute cross frequencies and reveal insights across region, purchase channel, and city.

Explore correlation analysis for numeric data by building a correlation matrix, identifying positive or negative relationships, and judging strength from numeric variables like income, age, frequency, and monetary score.

Discover how to visualize the outputs of exploratory data analysis in Python by using matplotlib and seaborn to plot bar charts, pie plots, and labeled figures.

Learn to create bar charts in Python using matplotlib and seaborn, set figure size, perform frequency analysis, and visualize category frequencies such as online vs in-store channels.

Learn to create stacked and clustered bar charts in Python with Matplotlib and Seaborn, using cross-tabulation and hue to compare churn status by city.

Plot histograms in Python using matplotlib or seaborn to explore the distribution of average purchase amounts, customize bins, and reveal spending ranges.

Learn to plot a scatter plot in python using matplotlib and seaborn to reveal a strong positive relationship between frequency of purchases and average purchase amount, via a rake plot.

Explore visualizing a correlation matrix with a Seaborn heat map in Python, including plotting the matrix, configuring the figure, and optionally displaying correlation coefficients.

Create box plots to visualize numeric data distributions, showing median, quartiles, min and max, and outliers, using Seaborn's boxplot with a numeric x and optional categorical y.

Learn to visualize the distribution of numeric variables with CD plots in Python, assess normal distribution for hypothesis testing across multiple variables, and apply transformations when needed.

Apply the square root transformation in Python to correct skewness, using age as an example, then recalculate the Shapiro-Wilk test and visualize a cdf plot for the transformed data.

Apply log transformation to numeric data and compare it with square root, using a log_transformation function to create an age_log column, run the Shapiro-Wilk test, and display a CD plot.

Apply the Box-cox power transformation to convert positive numeric data to a normal distribution using scipy.stats, create a transformed column, and verify with the Shapiro-Wilk test and a CD plot.

Apply the Yeo-Johnson power transformation to negative and positive data using SciPy, compare with Box-Cox and square root, and select the best transform via Shapiro-Wilk p-values for final preprocessing.

Apply the one sample t test to compare the current average purchase amount with the predefined mean of 68 using Python with SciPy at 0.05 significance.

Explore how to conduct a chi square test for independence between region and purchase channel, using cross tabulation, scipy's chi2_contingency, and p value to conclude no significant association.

Perform a Pearson correlation analysis to assess the relationship between purchase frequency and average purchase amount, including assumptions of normality and linearity, and interpreting the p-value.

Apply linear regression to link frequency of purchases to average purchase amount using Python and statsmodels, with hypothesis testing, normality checks, and interpretation of r-squared, coefficients, and p-values.

Learn how to engineer new features from existing data, creating total purchase amount and customer lifetime value using purchase frequency, average purchase amount, and customer lifespan, with Python examples.

Extract year, month, and day from a date time variable to create new features for predictive modeling, using the dt accessor and Python methods, then drop the original date column.

Encode ordinal categorical variables into numeric features with the level encoder from scikit-learn, preparing data for machine learning. Demonstrates encoding churn status yes/no to 1/0 via fit_transform for classification models.

Convert numeric variables into categorical features using pandas cut to create an engagement level from customer lifespan in months, defining bins and labels for low, moderately engaged, and highly engaged.

Explore encoding ordinal engagement levels with level encoder and the Python dot map method, mapping categories from low to highly engaged into numeric values for modeling.

Convert non-ordinal categorical features into numeric dummy variables using Pandas get_dummies and pd.concat, loading gender, city, region, and purchase channel into the preprocessed data.

Scale your features with the standard scaler from sklearn.preprocessing in Python and apply fit_transform to prepare data for regression and classification models.

Learn how to apply the min max scaler to scale features not normally distributed for a classification machine learning model, transforming data to a 0–1 range using sklearn preprocessing.

Apply principal component analysis to reduce feature dimensionality, compute explained variance ratio to select optimal components, and plot the results using scikit-learn and Python.

Split the data into training and test sets using train_test_split, with 80/20 proportions and random_state 42, producing x_train, y_train, x_test, and y_test for model evaluation, using scaled features.

Learn to build a linear regression machine learning model in Python using scikit-learn to predict a numeric target like customer lifetime value, and evaluate it with mean squared error.

Leverage logistic regression to predict churn status from features, train on data, test predictions, and assess accuracy with a confusion matrix and heatmap visualization.

Import the decision tree classifier from sklearn, train on x_train and y_train, and predict on x_test; evaluate with accuracy_score and the confusion matrix, then view a heat map.

Apply the random forest classification model to predict customer churn, importing from sklearn.ensemble, fitting on train data, and evaluating with accuracy score and a confusion matrix heat map.

Learn to perform cluster data analytics with K-means in Python, compute within-cluster sum of squares, and use the elbow method to choose the optimal number of clusters for customer segmentation.

Apply the elbow method to determine the optimal number of clusters for k-means, using a line plot of wcs scores across a range of k to identify elbow break point.

Develop and evaluate a two-cluster k-means model to segment customers using recency, frequency, and monetary scores, then label loyal and regular groups.

Identify and remove duplicate values in a data set using Excel, focusing on the employee ID as a unique identifier, and apply conditional formatting to highlight duplicates before removing them.

Identify and count missing values in the employee age column, then replace blanks with 41, and impute missing department values with the mode using Excel tools.

Identify and replace outliers in the variable cost using the g score method. Calculate mean and standard deviation to determine and substitute extreme values with the average cost.

Learn to detect and impute inconsistent values in numeric and categorical data with Excel, replacing anomalies by the average cost value and by the most frequent category using pivot tables.

Convert text data in Excel into a tabular format using text to columns. In column a, select delimited and comma to create columns for name, age, gender, occupation, and city.

Learn to filter a data set in Excel using the home tab, applying city, gender, name, and age conditions to narrow results.

Explore advanced filtering in Microsoft Excel using a criteria range to filter for multiple conditions (gender, occupation, city Los Angeles, and age) and copy results to another location.

Master conditional formatting in Excel to highlight costs, sales, using greater than 300, less than 300, and between 300 and 500, with color scales, bars, and sorting by cell color.

Use Excel's conditional formatting top/bottom rules to identify top ten and bottom ten sales by category. Filter by color to reveal above-average and below-average revenue insights for customers.

Apply conditional formatting to identify electronics customers by spending, using color scales and data bars to visualize profit, categorize costs into high, medium, and low, and highlight above-average spenders.

Apply sum, average, min, and max to revenue, cost, and refunds in excel; format usd currency and compute final profit from business data.

Learn to apply sumif and averageif in Excel to calculate total revenue and average refund by each business area, identifying unique areas like North America, Europe, South America, and Asia.

Apply excel conditional logic using if statements, including basic and nested forms, the ifs function, and and/or logic to produce outputs based on true–false tests.

Learn how to use Vlookup, a vertical lookup in Excel, to retrieve an employee name and designation by selecting an employee ID from a dropdown and matching exactly.

Learn how to use hlookup, the horizontal lookup, for row-wise insights in Excel, selecting an employee from a data validation dropdown to retrieve monthly sales across January to December.

Explore how xlookup overcomes vlookup limitations by enabling single and nested lookups to fetch employee names and designation, and sales data by employee ID and month.

Learn to visualize data in Excel by creating bar charts, including stacked and clustered variants, to compare average delivery days by product category and sales by region.

Learn to visualize pie and line charts in Excel from a sales channel dataset, compute channel percentages, and plot the average discount rates by channel using AverageIF.

Learn to plot a histogram and a box plot in Excel to analyze distribution of quantity sold, identify skewness and outliers, and read the median and 25th and 75th percentiles.

Plot a scatter plot in Excel to explore the relationship between quantity sold and delivery days, add a trendline, then create a combo chart comparing sales and quantity by region.

Learn to beautify Excel visualizations by applying an optimal layout, adding clear titles, and repositioning the legend for a professional, easy-to-read combo chart of total sales and quantity by region.

Explore pivot table analysis in Excel to compute average discount rate and profit margin by product category, then analyze regional and product-category combinations for sales and quantity.

Explore pivot tables in Excel to perform cross tabulation for product category and customer segment, calculating counts and average revenue across segments.

Learn to create interactive pivot charts and slicers from pivot tables, visualize average discount rate and profit margin by product category, and filter insights by city, region, and customer segment.

Explore how to use Excel's data analysis Toolpak to compute descriptive statistics for sales and quantity sold, including mean, median, mode, standard deviation, skewness, and kurtosis.

Use the data analysis toolpak to perform an independent sample t test for two groups, comparing average profit margin between male and female customers at a 0.05 significance level.

Learn to perform one way ANOVA in Excel with the data analysis toolpak to compare regional sales under a 0.05 significance level. Understand null hypotheses, normality testing, and p-value interpretation.

Learn to perform a correlation analysis in Excel using the Data Analysis Toolpak, test for normality and linearity, and determine a weak but significant positive relationship.

Use Excel data analysis toolpak to perform multiple linear regression, measuring how sales and quantity sold influence profit margin, and interpret r-squared, p-values, and coefficients to predict margin.

Accumulate pivot findings in a single Excel dashboard by organizing data across worksheets and creating pivot charts to compare discount rates, profit margins, regional sales, and revenue by category.

Create a dashboard canvas by designating a title area, interactivity tools like slicers, and four graph regions to organize findings for an efficient, accurate visualization.

Drag and drop charts to build a business insight dashboard, customize the title and layout, set a 100% stacked bar, and add slicers for payment method, customer segment, and city.

Apply professional formatting to your Excel dashboard by adding borders, outlines, fills, and US dollar currency formatting; adjust titles, axes, and slicers to reveal revenue, profit margin, and regional insights.

Import a CSV dataset into Power BI, adjust data types in the Power Query Editor, and transform data to build a business intelligence dashboard.

Learn to adjust table and column names in the Power Query editor of Power BI, view applied steps, and rename fields like customer ID, gender, and address—without writing M code.

Set the correct data types in Power BI with the Power Query Editor, adjusting the customer ID from whole numbers to decimals, applying steps, and undoing to the original type.

Split the address column in Power Query to create city and state columns, then remove the original address, showcasing practical data manipulation in Power BI.

Replace values in a column using Power BI and the Power Query Editor to correct a mistaken tax entry by substituting Texas in a data table.

Learn text data manipulation in Power BI by splitting an address column on the comma to create city and state, reformatting case, and merging columns back into a single address.

Master numeric analysis in Power BI by applying its tools to transaction data, calculating sum, average, count distinct, min, max, median, standard deviation, and rounding with DAX and M code.

Merge customer demographics with transaction history in Power BI on the common customer ID using an inner join, then expand to reveal transaction insights.

Combine datasets in Power BI by appending two product catalogs to form a complete product data set, aligning columns like category, price, stock quantity, and inventory status.

Master data modeling by creating and managing relationships between datasets in Power BI to build interactive dashboards. Connect product catalog with transaction history to reveal accurate insights.

Create, manage, and automatically establish relationships in Power BI by linking datasets through a common column, using drag-and-drop, manage relationships, and auto detect.

Manage and edit data model relationships in Power BI. Replace auto detected links with primary and foreign keys like product ID to build reliable, dynamic dashboards.

Learn how to format columns in Power BI, set currency, data types, decimal points, and data categories to create accurate reports and map visualizations.

Create and manage state hierarchy to break down total transaction amount by states and cities, enabling deeper analysis of customer spending in Arizona, California, New York, and beyond.

Leverage DAX and M code to count categories and analyze customer demographics in Power BI, creating measures for the number of customers and percentage by gender.

Apply DAX logical functions in Power BI to create new columns, using if and switch statements to derive order type and transaction quarter from month data.

Create dashboards in Power BI by selecting data, defining metrics, and visualizing KPIs such as total sales, orders, customers, and trends across categories, cities, and quarters.

Build the first section of the business performance dashboard by creating KPI goal cards for sales and other metrics with 2024 data and 2023 targets.

Plot a line chart in Power BI to visualize the 2024 sales by date, adjust the x and y axes, explore date hierarchy, enable forecasting, and format visuals.

Create area charts to display 2024 monthly trends for total quantity sold and total orders, then rename, format the title and color, and duplicate visuals.

Build gauge charts to display speedometer-style progress for total sales, quantity sold, total orders, and customers, comparing 2024 progress to 2023 targets and per product category goals.

Create a business dashboard for key performance indicators, showing actual sales, quantity sold, orders, and customers by product category against goals, with trend insights and polished visuals.

Create a comparative dashboard in Power BI by building a bar chart of total sales by product category, configure axes, formatting, and titles to compare KPIs.

Create a donut chart showing total orders by product category and its percentage of total orders, then apply a theme and place the legend.

Create a table to compare spending, quantity sold, and total sales by customer, then filter to the top 100 and apply data bars and color scales for clarity.

Create a Power BI map showing sales by city, with bubble size representing sales, set the title to 'total sales by city', and enable zoom controls and borders for charts.

Add slicers to filter kpi and comparison dashboards by state and quarter to analyze sales by product category, city, and top customers.

Add custom navigation buttons on your dashboard: a next button to the comparison page and a back button to the KPI page, using right and left arrows with styling.

Enhance a power BI dashboard by finalizing layouts, enabling KPI card titles for actual sales per product category and total orders, and refining charts to reveal trend insights.

Create a ChatGPT account, verify your email, and upgrade to the plus to enable GPT four for rapid data analysis and reporting.

Master data analytics with ChatGPT-4 to analyze datasets without coding, perform data cleaning, identify missing values and inconsistencies, and create an appealing report with writing and visualizations.

Upload an Excel dataset, then let ChatGPT analyze it with Python and pandas to reveal missing values by variable, such as gender (3), income (3), and BMI (5).

Impute missing values for gender, income, and BMI using the simple imputer with ChatGPT-4 guidance, applying most frequent for categorical data and mean or median for numerical data, and verify.

Explore how ChatGPT-4 uses df.dtypes to reveal assigned data types in a dataset, such as id as integer 64, age object, income float, and BMI float, highlighting inconsistencies.

Identify the inconsistent age value with a ChatGPT-assisted Python approach, convert to numeric via pd.to_numeric, filter nulls, and reveal the culprit at index 78 (id 79, value triple x).

Use ChatGPT-4 to drop inconsistent values from the age variable, remove the offending index, and convert the cleaned data to a numeric age type in a pandas workflow.

Identify duplicated rows in a dataset with ChatGPT four, filter and remove them using Python's drop_duplicates, and verify that the cleaned data has no remaining duplicates.

Learn to filter datasets with ChatGPT by applying conditional criteria (age over 30, income above 50,000), view the top rows, and combine multiple conditions to refine results.

Explore inner joining; it keeps only matching ids. See how two data sets are uploaded to chatgpt four, loaded with pd.read_excel, and merged on id with pd.merge.

Learn to perform left, right, and outer joins using ChatGPT, merging two datasets while handling null values and illustrating how each join type affects result sets.

Explore exploratory data analysis with GPT-4 to perform frequency and percentage analysis on education as a categorical variable, using value counts, descriptive statistics, and bar and pie visualizations.

Explore descriptive analysis of age, income, and BMI using GPT-4 to identify insights from the distributions and generate box plots.

Apply groupby analysis in Python to compute mean income by education levels, then sort and visualize results with a line chart.

Explore performing a one-way ANOVA with ChatGPT data analyst to test if income differs by education level, interpreting f and p values to conclude no significant difference.

Apply the Pearson correlation test to age and income, interpret a near-zero correlation and non-significant p-value, confirming no meaningful relationship as suggested by the exploratory data analysis.

Perform regression analysis with Python and statsmodels to test if age influences income; results show a small coefficient but no statistical significance (p = 0.371, r² = 0.001).

Apply feature binding to convert a numeric income variable into categorical low, medium, and high using pandas qcut and quantiles, guided by Python and GPT-4.

Create dummy variables for marital_status using pandas get_dummies and concat with axis=1, then merge with the clean dataframe to yield columns like marital_status_divorce and marital_status_widowed.

Derive and scale features for a machine learning model using standard scaler on X comprising age, BMI, education numeric, and gender and marital status dummies, with income as the target.

Use scikit-learn's train_test_split to split scaled features (X) and target (y) into train and test sets, enabling training on the train data and evaluating performance on the test data.

Build and compare machine learning models, including random forest regressor and support vector regressor, train on x and y data, and evaluate with mean squared error and R-squared.