Data Analytics with Python – Master Data Analysis, Visualisation & Business Insights

Excel Functions

• Basic Excel Function
• Mathematical Functions
• Logical Functions
• Text Functions
• Statistical Functional
• Relation Based Functions
• Financial Functions
• Date-Time Functions

Advance Paste Special Techniques

• Paste Formulas, Paste Formats
• Transpose Tables
• Paste Validations

Pivot Tables & Pivot Chart – Advance

• Creating Simple & Classic Pivot table
• Basic and Advanced Value Field Setting
• Calculated Field & Calculated Items
• Grouping based on numbers and Dates

Advanced Level Lookup Formulas

• Vlookup with Match, Hlookup with Match.
• Lookup from the Left,Mid & Right Side of Data. (Lookup, Index and Match)
• Vlookup & Hlookup with Multiple Text Functions.
• Creating Smooth user Interface Using lookup

Magic With Array Formulas:

• What are the Array Formulas, Use of the Array Formulas?
• Advanced Use of formulas with Array &Lookup functions.

Advanced Conditional Formatting and Filtering

• Apply Auto-Formatting & Conditional Formatting with Custom Data Formats and Validation
• Create custom conditional formatting rules
• Create conditional formatting rules that use formulas
• Manage conditional formatting rules

Define Named Ranges and Objects

• Manage Name cells & Name data ranges
• Name tables and Manage named ranges and objects.

Referencing Formulas

• Absolute Referencing/ Relative Cell Referencing
• Consolidating Data - With or Without Links
• Trace the Precedents and Dependents

Adding Charts,Graphics to Worksheet

• Work with Chart Styles,Layouts, Add Labels,Axis Options,Chart Title, Legends, Data-Labels
• Insert & Modifying Pictures, Group and Ungroup Data

Collaboration With Internal Or External Worksheets

• Manage Document Properties, Sharing a Workbook, Track Changes, Accept/Reject Changes,
• Mark as Final , Setting Printing Area with Margins, Headers and Footers & Scaling.
• AutoRecover Save Options, Save As Web Page & Protecting Workbooks

Formatting, Data linking and Proofing

• Object Linking and Embedding & Hyper linking data, within sheet / workbook
• Linking & Updating links between workbooks & application
• Creating / Working with an Automatic / Manual Outline

Importing & Exporting Data

• Importing & Exporting Data from Database / Text Files / Web
• Changing External Data Range

Protecting Excel- Excel Security

• File Level Protection
• Workbook, Worksheet Protection

Preparation of the Dashboards

• Different kinds of advanced-level dashboards
• Use formulas like OFFSET, MATCH, SUMIF, SUMIFS & many more to prepare the dashboards
• Use of Data Validation in charting
• Use of Sparkline in your sheet, interactive sparklines
• Working with 2-axis and 3-axis charts

Excel Dashboard - Planning & Processing

• Planning a Dashboard
• Adding Dynamic Contents to Dashboard
• Adding Tables and Charts to Dashboard

Slicers and Charts

• Using SLICERS, Filter data with Slicers
• Various Charts i.e. Bar Charts / Pie Charts / Line Charts
• Manage Primary and Secondary Axis

Complex Charting

• Being able to choose the appropriate type of chart for every occasion
• Being able to integrate many charts into one
• Utilize tools like conditional formatting charts and in-cell charts.
• Being able to create dynamic and interactive charts

Data Reporting with Pivot Tables

• Evaluate vast volumes of data with the help of Pivot tables and Data Reporting
• Relationships, multiple-table pivots, grouping, slicers, measures (Power Pivot)

Worksheet / Workbook Operations Using Macro

• Merge & Split Worksheets using Macro & VBA filters
• Worksheet copiers
• Merge multiple excel files into one-sheet

Pivot Table and Handling Of Errors In Excel

• Split Pivot from the Multiple Source of Data using Multiple Field Setting in Pivot Table.
• Split Pivot from the Multiple Data Ranges & Name Range.
• Creating Groups, Insert additional Calculated Field in Pivot Table.

Start-up with Pivot Charts With Slicer

• Insert slicer in Pivot Table and Pivot charts.
• Discussion of Errors and handling of multiple Errors & Use of Tool “Error Checking”.
• Removing Duplicates.
• Trace Precedents, Trace Dependents.

Data Analysis & Data Reporting

• Managing Extracts ManagingMetadata Visua lAn.a lytics
• Data Granularity using Marks Card
• Highlighting
• Sorting. Filtering. Grouping

Visual Analytics

• Graphical Visualization
• Forecasting Clustering Trend Lines.
• Reference Lines. Parameters

Dashboard & Stories

• Introduct on to Dashboard. Creatinga Dashboard Layout. DesigningDashboard for Devices.
• Dashboard Interaction - Using Action.
• Introduct on to Story Point.

Mapping & Publishing Report

• Introduct on to Maps.
• Editing Unrecognized Locations. Custom Geocoding.
• Polygon Maps.
• Web Mapping Services . Background Images
• Sharing & Publishing Reports to BI Teams or Clients.

Introduction to MySQL

• What is a database and why do we need SQL?
• Create a link to Excel spreadsheet with MySQL
• Client/Server Concepts
• Database models & ER Model

Database and Database Objects

• Import/Export
• Tools for Import/Export Data
• Restore database in SQL
• A Data Generating Procedure

Data Definition using SQL

• Databases
• Data Types
• Tables
• Primary Keys
• Null values and Not Null Values
• Auto Increment
• Basics Queries
• Removing Duplicates
• Data Filters Using Operators
• Data Sorting
• Constraints and Indexes
• Views

Basic Data Manipulation using SQL

• Recurring SQL Constructs
• Adding data / Modifying data / Removing data / Searching data

Advanced Data Manipulation using SQL

• Expressions for Grouping and Aggregate Functions
• Joining Tables

Create Query

• Use the WHERE clause
• Conditional statements
• Multiple conditions with Comparison Operators
• Logic Values
• Null Values
• Compare Column Values
• Advanced Queries
• Distinct Values

Joins

• Inner Join
• Left Join
• Full Outer Join
• Filtering Grouped Data
• Summarize Group Data
• Pivot and Unpivot Operators

User Defined Functions

• Functions vs Procedures
• User-Defined Functions

MySQL Admin

• Install and configure MySQL Server and client programs
• Recognize the key components of the MySQL architecture
• Secure your server
• Improve query performance
• Understand the benefits of MySQL Cloud

Overview To Python

• What is Python
• Why do we need PYTHON
• Features – Dynamic, Interpreted, Object-oriented, Standard libraries, Open source
• Python Different Versions & Comparison With other languages like C/C++, Java etc.

Introduction Of Python

• Why Python for Data Analysis?
• How to install Python and Set Virtual Environment in IDLE
• Variable Declaration and Memory Allocation
• Built in Data Types and Type Casting
• Python Operators

Python Language Fundamentals

• Data-types and Variables
• Constants / Literals , Keywords, Identifiers, Python Syntax

Basic Operators In Python

• Types of Operator
• Python Arithmetic, Comparison and Python Assignment Operators
• Python Bitwise, Logical Operators and Python Relation-Based Operators

Python Conditional Loop

• Python Conditional Statements — IF…Else, ELIF & Switch Case
• Python Looping statements — While, For Loop, Nested Loop, For-each Loop
• Python Control Statements Break, Continue, Pass statements

Function

• What is Function
• Python Maths Operations Functions.
• Python String & String Manipulations Functions.
• Python Date Functions.

List Collection

• What is List and Need of List collection
• Processing elements of List through Indexing and Slicing
• List object methods and Basic List Operations
• Built-in List Functions & Methods

Tuple Collection

• What is tuple
• Method of Tuple object
• List v/s Tuple
• Built-in Tuple Functions

Set Collection

• What is set
• Difference between list and set
• Union of sets and Difference and Intersection in set

• What is dictionary
• Difference between list, set, and dictionary
• Accessing & Copying and Updating dictionary
• Reading keys, Values, and items from Dictionary
• Python Dictionary Functions and methods

Python Numpy Programming

• Numerical Python - Data Wrangling, Statistics and Linear Algebra
• NumPy - Implementation & Installation of NumPy
• NumPy - Ndarray Object
• NumPy - numpy 2d,3d Array Attributes
• NumPy - Array Creation Matrix
• NumPy - Array from Numerical Ranges
• NumPy - Advanced Indexing 2d, 3d, 4d ..... Matrix Arrays
• NumPy - Advanced Slicing
• NumPy – Numpy Operations on Arrays
• NumPy - Iterating over Array
• NumPy - Reshaping Arrays
• NumPy - Splitting Arrays
• NumPy - String Functions
• NumPy - Mathematical & Arithmetic Operations
• NumPy - Statistical Operations on Arrays
• NumPy - Sort, Search & Counting Functions
• NumPy - Copies & Views
• NumPy - Matrix Library

Pandas For Data Analysis In Python

• Pandas - Python Panel Data Analysis System for Data Manipulation & Analysis
• Introduction to Pandas & How to Install Python Pandas
• import data using Pandas
• Working with CSV and Excel files
• Read data into Python
• write data using Pandas
• Select columns and rows in Pandas
• Manipulate columns in Pandas
• Rename columns, sort data & Combining Data in Pandas dataframe
• Merge and join multiple Pandas dataframes
• Correlations & Plotting

Matplotlib: Visualization With Python

• Matplotlib Cross platform, Data Visualization and Graphical Plotting Library.
• Installation of Matplotlib
• Import Matplotlib
• Checking Matplotlib Version
• Load datasets into csv, text files
• Creating Different Types of Plots (line-chart, bar-graph, etc.)
• Formatting the style of your plot
• Customize visual style and layout.
• Setting the Plot Title and Axis Labels
• Setting Axis Limits
• Saving Plots

Preparing Interview Questions

• Realted to Data Analysis process, Dataset generation, Importing Dataset
• Data, Basic Insights from Dataset.