DURGA SOFTWARE SOLUTIONS

Courses Offered:

SCJP SCWCD Design patterns EJB CORE JAVA AJAX Adv. Java XML STRUTS Web services SPRING HIBERNATE

DATA ANALYST Course Details

Subcribe and Access : 5200+ FREE Videos and 21+ Subjects Like CRT, SoftSkills, JAVA, Hadoop, Microsoft .NET, Testing Tools etc..

Batch Date: Jan 28th @8:00AM

Faculty: Mr. N. Vijay Sunder Sagar (20+ Yrs of Exp,..)

Duration: 3 Months

Venue :
DURGA SOFTWARE SOLUTIONS,
Flat No : 202, 2nd Floor,
HUDA Maitrivanam,
Ameerpet, Hyderabad - 500038

Ph.No: +91- 9246212143, 80 96 96 96 96

Syllabus:

DATA ANALYST

Data Analyst Project Life Cycle

Phase 1 - Data Collection

After carefully evaluating the business case in a particular domain, data will be collected surrounding it.

Phase 2 - Data Preparation

Using SQL, a database will be created to store the data collected in the previous step.

Phase 3 - Insights Generation And Dashboard Building

Establish a connection between the database and Tableau/Python/R tools to extract the required data. Generate user-friendly reports according to the business needs and develop the dashboard using Tableau/Power BI.

Topics To Be Covered

Module-I. Python Programming

Module-II. Excel

Module-III. MYSQL

Module-IV. POWER BI

Module-V. Statistical Data Analysis

Module-VI. Exploratory Data Analysis (EDA)

Module-VII. Chat GPT

Module-VIII. Projects

Module-I. Python Programming

1. Python Basics

What is Python

Why Python?

History of python

Features of Python

Advantages of Python

Versions of Python

Installation of Python

Flavors of Python

2. Python Operations

Python Modes of Execution

Interactive mode of Execution

Batch mode of Execution

Python Editors and IDEs

Python Data Types

Python Constants

Python Variables

Comments in python

Output Print(),function

Input() Function :Accepting input

Type Conversion

Type(),Id() Functions

Comments in Python

Escape Sequences in Python

Strings in Python

String indices and slicing

3. Operators in Python

Arithmetic Operators

Comparision Operators

Logical Operators

Assignment Operators

Short Hand Assignment Operators

Bitwise Operators

Membership Operators

Identity Operators

4. Python IDE’s

Pycharm IDE Installation

Working with Pycharm

Pycharm components

Installing Anaconda

What is Conda?

Anaconda Prompt

Anaconda Navigator

Jupyter Notebook

Jupyter Features

Spyder IDE

Spyder Featueres

Conda and PIP

5. Flow Control statements

Block/clause

Indentation in Python

Conditional Statements

if stmt

if…else statement

if…elif…statement

6. Looping Statements

while loop,

while … else,

for loop

Range() in for loop

Nested for loop

Break statememt

Continue statement

Pass statement

7. Strings in Python

Creating Strings

String indexing

String slicing

String Concatenation

String Comparision

String splitting and joining

Finding Sub Strings

String Case Change

Split strings

String methods

8. Collections in Python

Introduction

Lists

Tuples

Sets

Dictionaries

Operations on collections

Functions for collections

Methods of collection

Nested collections

Differences b/w list tuple and set and Dictionary

9. Python Lists

List properties

List Creation

List indexing and slicing

List Operations

List addresses

List functions

Different ways of creating lists

Nested Lists

List modification

List insertion and deletion

List Methods

10. Python Tuples

Tuple properties

Tuple Creation

Tuple indexing and slicing

Different ways of creating tuples

Tuple Operations

Tuple Addresses

Tuple Functions

Nested Tuples

Tuple Methods

Differences b/w List and Tuple

11. Python Sets

Set properties

Set Creation

Set Operations

Set Functions

Set Addresses

Set Mathematical Operations

Set Methods

Insertion and Deletion operation

12. Python Dictionary

Dictionary properties

Dictionary Creation

Dictionary Operations

Dictionary Addresses

Nested Dictionaries

Dictionary Methods

Insertion and Deletion of elements

Differences b/w list tuple and set and Dictionary

13. Functions in Python

Defining a function

Calling a function

Properties of Function

Examples of Functions

Categories of Functions

Argument types

default arguments

non-default arguments

keyword arguments

non keyword arguments

Variable Length Arguments

Variables scope

Call by value and Call by Reference

Passing collections to function

Local and Global variables

Recursive Function

Boolean Function

Passing functions to function

Anonymous or Lamda function

Filter() and map() functions

Reduce Function

14. Modules in Python

What is a module?

Different types of module

Creating user defined module

Setting path

The import statement

Normal Import

From … Import

Module Aliases

Reloading a module

Dir function

Working with Standard modules -Math, Random, Date time and os modules,

15. Packages

Introduction to packages

Defining packages

Importing from packages

--init--.py file

Defining sub packages

Importing from sub packages

16. Errors and Exception Handling

Types of errors

Compile-Time Errors

Run-Time Errors

What is Exception?

Need of Exception handling

Predefined Exceptions

Try,Except, finally blocks

Nested blocks

Handling Multiple Exceptions

User defined Exceptions

Raise statement

17. File Handling

Introduction

Types of Files in Python

Opening a file

Closing a file

Writing data to files

Tell( ) and seek( ) methods

Reading a data from files

Appending data to files

With open stmt

Various functions

18. OOPs Concepts

OOPS Features

Encapsulation

Abstraction

Class

Object

Static and non static variables

Defining methods

Diff b/w functions & methods

Constructors

Parameterized Constructors

Built –in attributes

Object Reference count

Destructor

Garbage Collection

Inheritance

Types of Inheritances

Object class

Polymorphism

Over riding

Super() statement

19. Regular Expressions

What is regular expression?

Special characters

Forming regular expression

Compiling regular expressions

Grouping

Findall() function

Finditer() function

Sub() function

Match() function

Search() function

Matching vs searching

Splitting a string

Replacing text

validations

20. Database Connection

Introduction

Installing mysql database

Creating database users,

Installing Oracle Python modules

Establishing connection with mysql

Closing database connections

Connection object

Cursor object

Executing SQL queries

Retrieving data from Database.

Using bind variables executing

SQL queries

Transaction Management

Handling errors

21. Python Date and Time

How to Use Date &DateTime Class

Time and date Objects

Calendar in Python

The Time Module

Python Calendar Module

22. Excel Workbook

Installing and working with Xlsx writer

Creating Excel Work book

Inserting into excel sheet

Insetting data into multiple excel sheets

Creating headers

Installing and working with xlrd module

Reading a specific cell or row or column

Reading specific rows and columns

23. Data Analytics

Introduction

pandas module

Numpy module

Matplotlib module

Working Examples

24. Python Pandas

Introduction to Pandas

Creating Pandas Series

Creating Data Frames

Pandas Data Frames from dictionaries

Pandas Data Frames from list

Pandas Data Frames from series

Pandas Data Frames from CSV, Excel

Pandas Data Frames from JSON

Pandas Data Frames from Databases

Pandas Data Functionality

Pandas Timedelta

Creating Data Frames from Timedelta

Pandas Groupings and Aggregations

Converting Data Frames from list

Creating Functions

Converting Different Formats

Pandas and Matplotlib

Pandas usecases

25. Python Numpy

Introduction to Numpy

Numpy Arrays

Numpy Array Indexing

2-D and 3Dimensional Arrays

Numpy Mathematical operations

Numpy Flattening and reshaping

Numpy Horizontal and Vertical Stack

Numpy linespace and arrange

Numpy asarray and Random numbers

Numpy iterations and Transpose

Numpy Array Manipulation

Numpy and matplotlib

Numpy Linear Algebra

Numpy String Functions

Numpy operations and usecases

Numpy Working Examples

26. Python Matplotlib

Introduction to matplotlib

Installing matplotlib

Generating graphs

Normal plottings

Generating Bargraphs

Histograms

Scatter plots

Stack plots

Pie plots

Matplotlib working examples

Module-II. Excel

Introduction

MS office Versions(similarities and differences)

Interface(latest available version)

Row and Columns

Keyboard shortcuts for easy navigation

Data Entry(Fill series)

Find and Select

Clear Options

Ctrl+Enter

Formatting options(Font,Alignment,Clipboard(copy, paste special))

Referencing, Named ranges,Uses,Arithemetic Functions

Mathematical calculations with Cell referencing(Absolute,Relative,Mixed)

Functions with Name Range

Arithmetic functions (SUM,SUMIF,SUMIFS,COUNT,COUNTA,COUNTIFS,
AVERAGE,AVERAGEIFS,MAX,MAXIFS,MIN,MINIFS)

Logical Functions

Logical functions:IF,AND,OR,NESTED IFS,NOT,IFERROR

Usage of Mathematical and Logical functions nested together

Referring data from different tables: Various types of Lookup, Nested IF

LOOKUP

VLOOKUP

NESTED VLOOKUP

HLOOKUP

INDEX

INDEX WITH MATCH FUNCTION

INDIRECT

OFFSET

Advanced Functions

Combination of Arithmatic

Logical

Lookup functions

Data Validation(with Dependent drop down)

Date and Text Functions

Date Functions: DATE,DAY,MONTH,YEAR,YEARFRAC,DATEDIFF,EOMONTH

Text Functions:
TEXT,UPPER,LOWER,PROPER,LEFT,RIGHT,SEARCH,FIND,MID,TTC, Flash Fill

Data Handling::Data cleaning, Data type identification, Remove Duplicates, Formatting and Filtering

Number Formatting(with shortcuts)

CTRL+T(Converting into an Excel Table)

Formatting Table

Remove Duplicate

SORT

Advanced Sort

FILTER

Advanced Filter

Data Visualization: Conditional Formatting, Charts

Conditional formatting (icon sets/Highlighted colour sets/Data bars/custom formatting)

Charts: Bar,Column,Lines,Scatter,Combo,Gantt,Waterfall,pie

Data Summarization: Pivot Report and Charts

Pivot Reports:Insert,Interface,Crosstable Reports;Filter,Pivot Charts

Slicers: Add,Connect to multiple reports and charts

Calculated field, Calculated item

Data Summarization: Dashboard Creation, Tips and Tricks

Dashboard:Types,Getting reports and charts together, Use of Slicers.

Design and placement: Formatting of Tables,Charts,Sheets,Proper use of Colours and Shapes

Connecting to Data: Power Query, Pivot, Power Pivot within Excel

Power Query: Interface, Tabs

Connecting to data from other excel files, text files, other sources

Data Cleaning

Transforming

Loading Data into Excel Query

Connecting to Data: Power Query, Pivot, Power Pivot within Excel

Using Loaded queries

Merge and Append

Insert Power Pivot

Similarities and Differences in Pivot and Power Pivot reporting

Getting data from databases, workbooks, webpages

VBA and Macros

View Tab

Add Developer Tab

Record Macro:Name,Storage

Record Macro to Format table(Absolute Ref)

Format table of any size(Relative ref)

Play macro by button

shape

as command(in new tab)

Editing Macros

VBA:Introduction to the basics of working with VBA for Excel: Subs, Ranges, Sheets

Comparing values and conditions

if statements and select cases

Repeat processes with For loops and Do While or Do Until Loops

Communicate with the end-user with message boxes and take user input with input boxes, User Form

Module-III. MySQL

Introduction to Mysql

Introduction to Databases

Introduction to RDBMS

Explain RDBMS through normalization

Different types of RDBMS

Software Installation(MySQL Workbench)

SQL Commands and Data Types

Types of SQL Commands (DDL,DML,DQL,DCL,TCL) and their applications

Data Types in SQL (Numeric, Char, Datetime)

DQL & Operators

SELECT

LIMIT

DISTINCT

WHERE AND

OR

IN

NOT IN

BETWEEN

EXIST

ISNULL

IS NOT NULL

Wild Cards

ORDER BY

Case When Then and Handling NULL Values

Usage of Case When then to solve logical problems and handling NULL Values (IFNULL, COALESCE)

Group Operations & Aggregate Functions

Group By

Having Clause

COUNT

SUM

AVG

MIN

MAX

COUNT String Functions

Date & Time Function

Constraints

NOT NULL

UNIQUE

CHECK

DEFAULT

Primary key

Foreign Key (Both at column level and table level)

Joins

Inner

Left

Right

Cross

Self Joins

Full outer join

DDL

Create

Drop

Alter

Rename

Truncate

Modify

Comment

DML & TCL Commands

DML

Insert

Update & Delete

TCL

Commit

Rollback

Savepoint

Data Partitioning

Indexes and Views

Indexes (Different Type of Indexes)

Views in SQL

Stored Procedures

Procedure with IN Parameter

Procedure with OUT parameter

Procedure with INOUT parameter

Function, Constructs

User Define Function

Window Functions

Rank

Dense Rank

Lead

Lag

Row_number

Union, Intersect, Sub-query

Union, Union all

Intersect

Sub Queries, Multiple Query

Exception Handling

Handling Exceptions in a query

CONTINUE Handler

EXIT handler

Triggers

Triggers - Before | After DML Statement

Module-IV. POWER BI

Power BI Introduction and Installation

Understanding Power BI Background

Installation of Power BI and check list for perfect installation

Formatting and Setting prerequisits

Understanding the difference between Power BI desktop & Power Query

The Power BI user interface, including types of data sources and visualizations

Getting familiar with the interface BI Query & Desktop

Understanding type of Visualisation

Loading data from multiple sources

Data type and the type of default chart on drag drop.

Geo location Map integration

Sample dashboard with Animation Visual

Finanical sample data in Power BI

Preparing sample dashboard as get started

Map visual Types and usages in different variation

Understanding scatter Plot chart with Play axis and the parameters

Power BI artificial intelligence Visual

Understanding the use of AI in power BI

AI analysis in power bi using chart

Q&A chat bot and the use in real life

Hirarchy tree

Power BI Visualization

Understanding Column Chart

Understanding Line Chart

Implementation of Conditional formating

Implementation of Formating techniques

Power Query Editor

Loading data from folder

Understanding Power Query in detail

Promote header, Split to limiter, Add columns, append, merge queries etc

Modelling with Power BI

Loading multiple data from different format

Understanding modelling (How to create relationship)

Connection type, Data cardinality, Filter direction

Making dashboard using new loaded data

Power Query Editor Filter Data

Power Query Custom Column & Conditional Column

Manage Parameter

Introduction to Filter and types of filter

Trend analysis, Future forecast

Customize the data in Power BI

Understanding Tool tip with information

Use and understanding of Drill Down

Visual interaction and customisation of visual interaction

Drill through function and usage

Button triggers

Bookmark and different use and implementation

Navigation buttons

Dax Expressions

Introduction to DAX

Table Dax, Calculated column, DAX measure and difference

Eg:- Calendar, Calendar auto, Summarize, Group by etc

Calculated Column

Related, Lookup value, switch, Datedif,Rankx,Date functions

Dax Measure and Quick Measure

Remove filters, Keep filters, All, Allselected, Time Intelligence Functions,Rolling average,YoY, Running total

Custom Visual

Custom visual and understanding the use of custom

Loading custom visual, Pinning visual

Loading to template for future use

Publishinhg Power Bi

Power BI Service

Introduction to app.powerbi.com

Schedule refresh

Data flow and use power bi from online

Download data as live in power point and more

Module-V. Statistical Data Analysis

Types of Statistics

Descriptive Statistics

Inferential Statistics

Central Tendency Measures

Mean,Median, Mode

The Story of Average

Dispersion Measures

Variance, Range

Data Distributions

Sampling

Types of Sampling

What is Hypothesis testing

Confidence Level

Correlation Analysis

Uses of Correlation

Continuous variables

Categorical variables

Module-VI. Exploratory Data Analysis (EDA)

What is EDA?

Why do we need EDA?

Goals of EDA

Types of EDA

Implementing of EDA

EDA functions

Multiple packages for data analysis

Some fancy plots

Use-cases for EDA

Module-VII. ChatGPT

Introduction to ChatGPT and AI

What is ChatGPT?

The history of ChatGPT

Applications of ChatGPT

ChatGPT vs other chatbot platforms

Industries using ChatGPT

The benefits and limitations of ChatGPT

Future developments in ChatGPT technology

Ethical considerations related to ChatGPT and AI

Types of AI and Chatgpt Architecture

What is AI?

Types of AI

What is Machine Learning?

Neural Networks

Deep Learning

Natural Language Processing (NLP)

Computer Vision

Robotics and AI

ChatGPT Functionalities and Applications

How does ChatGPT work?

ChatGPT Functionalities

Drafting emails and professional communication

Automating content creation

Resume and Cover letter creation

Research and information gathering

Brainstorming ideas and creative problem solving

Best Practices for Using ChatGPT

ChatGPT Prompt Engineering

What is Prompt Engineering?

Types of Prompts

Crafting Effective Prompts

Using ChatGPT to generate prompt

Module-VIII. Projects

Analysis Of Patient Data (Domain: Healthcare)

This project requires learners to analyze the patient data of those suffering from different diseases across various summaries. The facility, chain organizations, and dialysis stations analysis is required to be carried out where the patients are undergoing dialysis. The project also focuses on the payment mode aspect wherein if any discounts or reduction in payments have happened then those are analyzed.

Loan Of Customers (Domain: Banking And Finance)

In this project, learners analyze the loan given by a financial institution to different customers of varied grades and sub-grade levels. The analysis needs to consider the loan disbursement reasons, funded amount, and revolving balance values for every customer in different states and geolocations. The project requires the customers payment modes and the last payment values.

Employee Retention (Domain: HR Analytics)

This HR-related project considers the attrition rate of employees working at an organization at different levels. The attrition rate analysis is done with respect to different factors such as monthly income, last promotion year, job role, and work-life balance of every employee of different departments

Industrial Combustion Energy Use (Domain: Energy)

The project requires learners to analyze the usage of different fuels in different facilities in different applications by finding the MMBTu and GWHt values. The fuels used for different geo-locations and for different primary titles are also taken into consideration while doing analysis.

Flights Delay Analysis (Domain: Aviation)

The primary aim of the project is to determine the different reasons behind the delay of flights of various airlines. The analysis needs to consider the number of flights in operation, the number of flights cancelled, and the statistical summary of week-wise, state-wise, and city-wise flight distributions.

Olist Store Analysis (Domain: ECommerce)

The market for a certain product is analyzed by considering a particular retail outlet which sells these products. The project involves statistical analysis on the payment distribution from different customers with the different modes of transactions across different product categories. The feedback from customers with respect to shipping days and other factors also needs to be considered while carrying out the analysis.