Data Analysis using Excel and Python

Teacher

Amit Kumar

Category

Core Courses

Course Attendees

Still no participant

Course Reviews

Still no reviews

Course Name :Data Analysis using Excel and Python

 

Code(Credit) : CUTM1241  (0+2+2)

Course Objectives

  1. To build a strong understanding on the Basics of Excel and Python
  2. To understand data crunching and data presentation

Learning Outcomes

Course Outcome Program Outcome Mapped
1. To create dynamic reports by mastering one of the most popular tools in excels - PivotTables, Pivot chart, If, Vlookup, Index, and Match etc. PO3 - Problem-Solving and Innovation

PO2- Critical Thinking and Analytical skills

 

2. To do different operation in python with set, dict. tuples, list etc PO3 - Problem-Solving and Innovation

PO2- Critical Thinking and Analytical skills

Course Syllabus

Module 1  - Introduction to Excel

Course objective, outcome, methodology and assessment, Excel Introduction, , Basic Navigation Tab, Concept of Cell and Cell address , row Column concept, Sum, SumIf, Sumifs, Count number, CountA (Text and Number), Count if, Countifs, Average, AverageA, Averageif, Averageifs

 

Module 2  - Understanding different Formula

Round, Roundup, Round Down, Concatenate, Min, Max, Trim, Lower,Upper, Proper,Left, Right, Mid,,Exact, Randbetwen, Rand, Len (Length of character) Paste special, SQRT, If function with Example of IF, More function like And, OR with their example, Conditional Formatting basic and advance level with OR, AND, Nested IF function, Index, Offset, Match

 

Module  3  - Data analysis and Data presentation

Vlookup, Hlookup, Trace Precedents, Trace Dependents, show formula, Text to Column, Data Validation, Filter,Duplicate Removal, Sort, Filter, Wrap Test, What if Analysis, with detail example Merge Cell, Shapes, Median, Mode, Slandered Deviation (S), Correlation, Large, Small, Pivot Table, Pivot Charts, Slicing, Sparkling

 

Module 4  - Python Basics

Data Types, Loops, Inbuilt functions,List, Tuples, Dictionary,IF, Else, Numpy one Dimensional, Numpy two Dimensional. Loading and working on data with Python

 

Project -

Preparation of automated attendance sheet

Preparation of Dashboard

Numpy One Dimensional and two Dimensional Array

Case study  -  GTIDS csp payout

Online Reference links - 

https://www.youtube.com/watch?v=L3-uVawPLKc

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

https://www.youtube.com/watch?v=rBuiBNZWjE4&t=1511s

Session Plan

Session 1

Course objective, outcome, methodology and assessment, Excel Introduction, , Basic Navigation Tab, Concept of Cell and Cell address , row Column concept

https://www.youtube.com/watch?v=L3-uVawPLKc

Session 2

Sum, SumIf, Sumifs, Count number, CountA (Text and Number), Count if, Countifs, Average, AverageA, Averageif, Averageifs

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 3 (Practice)

Practice -

Students Demonstration

Session 4

Round, Roundup, Round Down, Concatenate, Min, Max, Trim, Lower,Upper, Proper,Left, Right, Mid,,Exact, Randbetween, Rand, Len (Length of character) Paste special, SQRT, If function with Example of IF, More function like And, OR with their example

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 5 (Practice)

Practice -

Students Demonstration

Session 6

Internal Test - 1st

Session 7

Conditional Formatting basic and advance level with OR, AND, Nested IF function, Index, Offset, Match

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 8 (Practice)

Practice -

Student’s demonstration

Session 9

Case Study Discussion

GTIDS CSP Payout

Case Study Gtids CSP payout

Session 10

Vlookup, Hlookup, Trace Precedents, Trace Dependents, show formula

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 11

Text to Column, Data Validation, Filter,Duplicate Removal, Sort, Filter, Wrap Test, Merge Cell, Shapes

 

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 12

Internal Test - 2nd

Session 13

Excel Quiz-

Class will be divided into 3-4 broad groups and quiz will be conducted.

(Each group has to prepare 10 short practical questions before the quiz day to play)

Session 14 (Practice)

Practice -

Student’s demonstration

Session 15

Pivot Table, Pivot Charts, Slicing, Sparkling

Video for Pivot Table

https://www.youtube.com/watch?v=4PWVFBiFVVU

Video for Pivot Chart

https://www.youtube.com/watch?v=mc7xO8F8Pj8

Session 16 (Practice)

Practice -

Student’s demonstration

Session 17

Median, Mode, Slandered Deviation (S), Correlation, Large, Small,

(Brief discussion on Dashboard presentation, and group allocation.)

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 18

What if Analysis, with detail example

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 19

Project -

Preparation of automated attendance sheet (To be submitted in a week Time)

https://www.youtube.com/watch?v=D4oqM2tOvTI

Session 20 (Practice)

Practice -

Student’s demonstration

Session 21 - 25

Project-

Dashboard Presentation

Session 26

Python

Environmental setup - Anaconad and Jupyter

Python Installation Guide

Session 27

Data Types of Python - Int, String, Float, None, Boolean

https://www.youtube.com/watch?v=afJ2CuFbHKo

Session 28 (Practice)

Session 30 (Practice)

Practice project-

List and Tuple

1559282529_lists

1559282514_tuples

Session 32 (Practice)

Practice project-

Dict and Set

1559282567_sets

1559282593_dictionaries

Session 33

Session 34 (Practice)

Practice project-

Condition and Branching

1559282660_conditions

Session 36 (Practice)

Practice project 

Loops

1559282692_loops

Session 38 (Practice)

Practice project-

Functions

1559282723_functions

Session 39

Session 40 (Practice)

Practice project-

Object and Class

1559282753_classes

Session 41

Reading and Writing files 

https://www.youtube.com/watch?v=qjOPsKHfC24

Session 42 (Practice)

Practice project-

Reading and writing files

1559282799_readfile

1559282846_writefile

Session 43

Loading and working on Data with Panda

https://www.youtube.com/watch?v=vJkJ7dFru7c

Session 44 (Practice)

Practice project-

Working on Data with Panda

1559282875_loaddata

Session 45

Numpy One Dimensional Arrays

https://www.youtube.com/watch?v=119bNu4E8R4

Session 46

Practice project-

Numpy One Dimensional Arrays

1559282909_numpy1d

Session 47

Numpy Two Dimensional Arrays

https://www.youtube.com/watch?v=-FkeGlLQW88

Session 48 (Practice)

Practice project-

Numpy Two Dimensional Arrays

1559282949_numpy2d

Session 49 and 50

Project Presentation

Case Studies

GTIDS CSP Pay

Case Study Gtids CSP payout

Our Main Teachers