DATA ANALYTICS METAM IT SOLUTIONS

DATA ANALYTICS

1. What is Data Analytics?

Definition:
Data Analytics is the process of examining datasets to find useful information, discover
patterns, and support decision-making.
Key Purpose:
 Identify trends.

 Improve decision-making.
 Optimize business processes.
 Forecast future outcomes.

Types of Data Analytics

1. Descriptive Analytics (What happened?)
o Example: A company checks last year’s sales report to know total revenue.

2. Diagnostic Analytics (Why did it happen?)

o Example: Sales dropped in March → Analyze if it’s due to fewer marketing
campaigns or seasonal demand.
3. Predictive Analytics (What will happen?)
o Example: Predicting sales for the upcoming festive season using past sales
trends.
4. Prescriptive Analytics (What should be done?)
o Example: Suggesting which product should be given discounts to boost sales.

Applications of Data Analytics=

 Business: Customer insights, sales forecasting.

 HR: Employee performance evaluation, retention analysis.

 Finance: Risk assessment, fraud detection, budgeting.
 Healthcare: Patient treatment plans, disease prediction.
 Sports: Player performance analysis, game strategy building.

2. Role of Excel in Data Analytics

Excel is one of the most powerful and accessible tools for beginners and professionals in
analytics.
DATA ANALYTICS METAM IT SOLUTIONS

Why Excel?
 Simple interface, widely available.

 Handles structured data (rows & columns).

 Provides formulas (SUM, AVERAGE, IF, VLOOKUP, etc.) for quick calculations.
 Pivot Tables & Charts for summarizing and visualizing data.
 Useful for data cleaning (removing duplicates, formatting, handling missing values).
 Can integrate with Power BI, SQL, Python for advanced analytics.
📌 Example:
 A sales manager can use Excel to calculate monthly revenue, find top-performing
regions, and visualize growth with a chart.

3. Exploring Sample Datasets in Excel

To practice analytics, we often use real-world datasets. Here are three common ones:

A. Sales Dataset
Sample Columns: Customer Name, Product, Region, Quantity, Sales Amount.
📊 Applications:
 Find top-selling products.

 Compare sales across regions.

 Analyze customer purchasing patterns.
Example:
 If Region A made ₹5,00,000 and Region B made ₹3,50,000 → Region A is
performing better.
 A bar chart can show region-wise sales clearly.

B. HR Dataset
Sample Columns: Employee ID, Department, Age, Salary, Years of Experience,
Performance Rating.
📊 Applications:
 Track employee performance.

 Identify departments with high attrition.

 Salary comparisons across roles.
Example:
 HR finds that employees with over 5 years of experience have higher performance
ratings.
 A pie chart can show % of employees in each department.
DATA ANALYTICS METAM IT SOLUTIONS

C. Finance Dataset
Sample Columns: Expense Type, Amount, Month, Revenue, Profit/Loss.
📊 Applications:
 Monitor expenses.

 Calculate profit margins.

 Compare budgets vs. actuals.
Example:
 If monthly revenue = ₹2,00,000 and expenses = ₹1,50,000 → Profit = ₹50,000.

 A pie chart can show expense distribution (e.g., salaries, rent, marketing).

4. How Excel Helps with These Datasets

 Sorting & Filtering: Quickly find top sales or highest salaries.

 Formulas: Calculate totals, averages, profit margins.

 Pivot Tables: Summarize data (e.g., sales by region, average salary by department).
 Charts: Visualize trends (line chart for monthly revenue, bar chart for product sales).

5. Summary
 Data Analytics helps organizations make better decisions using data.

 Excel is a beginner-friendly yet powerful tool for analytics.

 Sales, HR, and Finance datasets are common starting points for learning analytics.
 With Excel, we can clean, analyze, and visualize data to derive actionable insights.
Excel Basics – Interface, Workbook, Worksheets, Formatting & Shortcuts

1. Excel Interface
When you open Excel, you see the interface which includes:
 Title Bar: Shows file name.

 Ribbon: Tabs (Home, Insert, Page Layout, Formulas, Data, Review, View).
 Quick Access Toolbar: Save, Undo, Redo.
 Worksheet Area: Grid of rows & columns where data is entered.
 Formula Bar: Displays contents or formulas of the selected cell.
 Status Bar: Shows status (Ready, Editing) and summary info (sum, average).
📌 Application: Helps users navigate and use Excel features efficiently.
DATA ANALYTICS METAM IT SOLUTIONS

2. Workbook & Worksheets

 Workbook: An Excel file (extension: .xlsx).

 Worksheets: Individual sheets inside a workbook (Sheet1, Sheet2, etc.).

 Cells: The intersection of rows & columns (e.g., A1).
 Range: A group of cells (e.g., A1:A10).
📌 Application:
 Workbooks allow multiple related datasets (e.g., Sales in Sheet1, HR in Sheet2,
Finance in Sheet3).
 Easy to organize large projects in one file.

3. Formatting in Excel
Formatting makes data readable, clear, and professional.
Common Formatting Options
 Cell Formatting: Change font style, size, color.

 Alignment: Left, Center, Right, Top, Middle, Bottom.

 Borders & Shading: Add cell borders, background colors.
 Number Formatting: Currency (₹, $), Percentage, Date, Decimal places.
 Conditional Formatting: Highlight cells based on rules (e.g., sales > 50,000 in
green).
📌 Example:
 In a Sales dataset, highlight top sales values in bold with a green background.

 In HR data, format salary column as “₹ currency.”

4. Excel Shortcuts
Shortcuts save time and make work efficient.
Most Useful Shortcuts
 Ctrl + N → New workbook

 Ctrl + S → Save workbook

 Ctrl + O → Open workbook
 Ctrl + C / Ctrl + V → Copy / Paste
 Ctrl + Z / Ctrl + Y → Undo / Redo
 Ctrl + A → Select all
 Ctrl + F → Find
 Ctrl + H → Replace
DATA ANALYTICS METAM IT SOLUTIONS

 Ctrl + Arrow Keys → Move to edges of data

 Ctrl + Shift + L → Apply/remove filter
 Alt + = → AutoSum
📌 Application: Increases speed, especially when working with large datasets.

5. Advantages of Learning Excel Basics

 Universal tool – widely used in business, education, finance, and research.

 Enhances productivity by handling large amounts of data quickly.

 Builds a strong foundation for advanced analytics (Pivot Tables, Charts, Power BI).
 Improves presentation of data (formatted reports, dashboards).
 Saves time with automation (shortcuts, formulas).

6. Applications of Excel Basics

 Business: Creating formatted sales reports, invoices, and inventory lists.

 HR: Maintaining employee records with professional formatting.

 Finance: Preparing budgets, expense reports, profit & loss statements.
 Education: Student scorecards, attendance sheets.
 Personal Use: Household budgets, travel planning.
📌 Example:
 A finance team prepares a monthly expense sheet:

o Use currency formatting for money.

o Apply borders to make a neat table.

o Highlight overspending using conditional formatting.

o Save and share workbook with the team.

7. Summary
 Excel interface includes ribbon, formula bar, and worksheets for data entry.

 Workbooks store multiple worksheets for organizing data.

 Formatting improves clarity and professionalism of reports.
 Shortcuts speed up tasks and boost efficiency.
 Excel basics are essential for business, HR, finance, education, and personal use.
DATA ANALYTICS METAM IT SOLUTIONS

Case Studies on Excel Basics

Case Study 1: Sales Report Formatting
Scenario:
A retail store manager wants to prepare a monthly sales report for three product categories
(Electronics, Clothing, Groceries).
Steps in Excel:
1. Create a workbook → Add a worksheet named “Sales Report – August 2025”.
2. Enter sales data in a table (Product, Quantity, Sales Amount).
3. Apply formatting:
o Use bold headings and background color.

o Format Sales Amount column as currency (₹).

o Apply borders to make it look neat.

4. Use Ctrl + S to save the file as Sales_August.xlsx.

Application:
 Professional, easy-to-read report for management.

 Quick formatting ensures accuracy and clarity.

Case Study 2: HR Employee Database
Scenario:
An HR manager maintains a list of employees with details like Employee ID, Name,
Department, Salary, and Joining Date.
Steps in Excel:
1. Create a workbook named HR_Database.xlsx.
2. Add multiple worksheets:
o Sheet1: Current Employees

o Sheet2: Ex-Employees

3. Apply formatting:
o Use date format for Joining Date.

o Format Salary column in ₹ currency.

o Center-align Employee IDs.

o Highlight employees with Salary > 50,000 using conditional formatting.

4. Use shortcuts:
o Ctrl + F to quickly find an employee.

o Ctrl + Arrow keys to navigate large data.

DATA ANALYTICS METAM IT SOLUTIONS

Application:
 Helps HR quickly analyze and manage employee records.

 Conditional formatting highlights high salaries for budget analysis.

Case Study 3: Personal Monthly Budget
Scenario:
A student wants to manage their monthly expenses (Food, Rent, Travel, Entertainment).
Steps in Excel:
1. Create a workbook named Monthly_Budget.xlsx.
2. Add a worksheet “August 2025”.
3. Enter expense categories in Column A and amounts in Column B.
4. Apply formatting:
o Use borders for neatness.

o Apply **AutoSum (Alt + =) ** to calculate Total Expenses.

o Format expenses as currency.

o Highlight the highest expense category in red.

5. Use shortcuts:
o Ctrl + C / Ctrl + V to copy previous month’s template.

o Ctrl + Z if wrong data is entered.

Application:
 Provides a clear picture of where money is spent.

 Easy to adjust and re-use for future months.

📊 Data Entry, Sorting, Filtering, and Conditional Formatting in Excel

1. Data Entry
Data entry is the process of adding raw information into Excel.
 You can type directly into cells or copy-paste from other sources (e.g., Word, CSV,
web).
 Excel supports numbers, text, dates, formulas, and symbols.
🔹 Practical Example
Suppose you are maintaining a Sales Data Sheet:
Customer Product Quantity Price Date
Ramesh Laptop 2 45000 12-08-2025
Anitha Mouse 5 500 15-08-2025
Kiran Mobile 1 25000 18-08-2025
DATA ANALYTICS METAM IT SOLUTIONS

Customer Product Quantity Price Date

sMeena Laptop 3 46000 22-08-2025
Here, data is entered manually or copied from another source.

2. Sorting Data
Sorting helps to arrange data in a meaningful order.
 Ascending (A–Z or Smallest to Largest)

 Descending (Z–A or Largest to Smallest)

🔹 Practical Example
 Sort Product A–Z → Laptop, Mobile, Mouse.

 Sort Price Largest to Smallest → 46000, 45000, 25000, 500.

This makes it easier to analyze the most expensive or cheapest items quickly.
g
3. Filtering Data
Filtering allows you to display only the data you need while hiding the rest.
🔹 Practical Example
 Apply a Filter on Product column → Select only Laptop.

 Excel will show only rows where the product is Laptop.

Customer Product Quantity Price Date

Ramesh Laptop 2 45000 12-08-2025
Meena Laptop 3 46000 22-08-2025
This way, you can focus only on specific items or customers.

4. Conditional Formatting
Conditional formatting automatically highlights key data based on rules.
 Helps identify trends, outliers, or important values.

🔹 Practical Examples
1. Highlight sales above 40,000 → Cells in Price greater than 40,000 will turn green.
2. Apply a Color Scale → Higher prices show darker green, lower prices lighter shade.
3. Highlight duplicates in Customer column.
4. Use Icon Sets → Show arrows (⬆️⬇️➡️) to represent sales increase/decrease.

✅ Advantages of Using These Features

 Data Entry → Organizes raw information into structured format.

 Sorting → Quickly finds highest/lowest values, alphabetical order.

 Filtering → Focus on only required information.
DATA ANALYTICS METAM IT SOLUTIONS

 Conditional Formatting → Provides visual insights without extra formulas.

📌 Real-Life Applications
 Sales Tracking: Identify top customers or high-value products.

 HR Records: Filter employees by department, sort by joining date.

 Finance: Highlight expenses above budget.
 Education: Sort students by marks, highlight those scoring below pass mark.

📑 Conditional Formatting – Case Studies (10 Q&A)

Q1. Student Grades

A teacher has marks of students in Science.
Student Marks
Ramesh 92
Anitha 36
Kiran 75
Meena 28
👉 Question: How can the teacher highlight all students who scored below 40?
✅ Answer: Apply conditional formatting → Highlight Cells Rule → Less Than → 40 →
Red Fill.

Q2. Sales Target Achievement

Sales Target
Executive
(₹) (₹)
Raj 1,20,000 1,00,000
Priya 80,000 1,00,000
Kiran 1,10,000 1,00,000
👉 Question: How can we highlight executives who achieved or exceeded their target?
✅ Answer: Use conditional formatting → Formula Rule → =B2>=C2 → Green Fill.

Q3. Attendance Monitoring

Employee Working Days Present Days
Anita 22 15
Meena 22 20
👉 Question: Highlight employees with attendance below 75%.
✅ Answer: Use Formula Rule → =C2/B2<0.75 → Orange Fill.

Q4. Monthly Expenses

DATA ANALYTICS METAM IT SOLUTIONS

Budget Expense
Category
(₹) (₹)
Food 15,000 17,500
Transport 5,000 4,500
👉 Question: How to highlight expenses that exceed budget?
✅ Answer: Formula Rule → =C2>B2 → Red Fill.

Q5. Inventory Levels

Product Stock
Laptop 12
Mouse 3
Printer 1
👉 Question: Highlight products with stock below 5.
✅ Answer: Conditional Formatting → Less Than → 5 → Red Fill.

Q6. Employee Performance Rating

Employee Rating
Raj 4.8
Kiran 2.5
Meena 3.0
👉 Question: Highlight ratings below 3.0.
✅ Answer: Conditional Formatting → Highlight Cells Rule → Less Than → 3 → Yellow
Fill.

Q7. Duplicate Entries

Student Name
Ramesh
Anitha
Kiran
Ramesh
👉 Question: How can we highlight duplicate names?
✅ Answer: Conditional Formatting → Highlight Cells Rule → Duplicate Values.

Q8. Top 10 Performers

Student Marks
Raj 92
Meena 85
Kiran 72
Anita 40
👉 Question: Highlight Top 10% scorers.
✅ Answer: Conditional Formatting → Top/Bottom Rules → Top 10% → Green Fill.
DATA ANALYTICS METAM IT SOLUTIONS

Q9. Heat Map for Sales

Sales
Month
(₹)
Jan 50,000
Feb 70,000
Mar 1,20,000
👉 Question: How to create a color scale to show high to low sales?
✅ Answer: Conditional Formatting → Color Scales → Choose Red-Yellow-Green scale.

Q10. Project Deadlines

Project Days Left
Alpha 2
Beta 10
Gamma 0
👉 Question: Highlight deadlines where days left ≤ 2.
✅ Answer: Conditional Formatting → Less Than or Equal To → 2 → Red Fill.

📊 Formulas & Functions in Excel

1. SUM
 Adds values together.

 Example 1: =SUM (10, 20, 30) → 60

 Example 2: =SUM (A1:A5) (if A1:A5 = 5, 8, 12, 15, 20) → 60
 Example 3: =SUM(A1+A2+A3+A4+A5)

2. AVERAGE
 Finds the mean (total ÷ number of values).

 Example 1: =AVERAGE(15, 25, 35) → 25

 Example 2: =AVERAGE(B1:B4) (if B1:B4 = 40, 50, 60, 70) → 55

3. MIN
 Returns the smallest value.

 Example 1: =MIN(18, 25, 5, 40) → 5

 Example 2: =MIN(C1:C6) (if C1:C6 = 12, 18, 5, 25, 30, 20) → 5
DATA ANALYTICS METAM IT SOLUTIONS

4. MAX
 Returns the largest value.

 Example 1: =MAX(10, 50, 75, 100) → 100

 Example 2: =MAX(D1:D5) (if D1:D5 = 35, 20, 15, 55, 45) → 55

5. COUNT
 Counts only numbers.

 Example 1: =COUNT(10, "Hello", 20, "World") → 2

 Example 2: =COUNT(E1:E6) (if E1:E6 = 5, Apple, 7, Mango, 10, Banana) → 3

6. COUNTA
 Counts everything that is not empty (numbers, text, dates, etc.).

 Example 1: =COUNTA(10, "Hello", 20, "") → 3

 Example 2: =COUNTA(F1:F5) (if F1:F5 = A, 15, Mango, , 40) → 4

🏢 Case Studies
📌 Case Study 1: Sales Data
A shop records daily sales for a week:
1200, 1500, 1800, 900, 2000, 1750, 1600
 Total Sales: =SUM(A1:A7) → 10,750

 Average Sales: =AVERAGE(A1:A7) → 1,536

 Highest Sale (Best Day): =MAX(A1:A7) → 2000
 Lowest Sale (Slowest Day): =MIN(A1:A7) → 900
 Number of Days Recorded: =COUNT(A1:A7) → 7
✅ Business Insight: Manager can see average performance and identify the best/worst days.

📌 Case Study 2: Student Marks

Marks of 5 students:
85, 70, 95, 60, 75
 Total Marks: =SUM(B1:B5) → 385

 Average Marks: =AVERAGE(B1:B5) → 77

 Highest Mark: =MAX(B1:B5) → 95
 Lowest Mark: =MIN(B1:B5) → 60
 Number of Students: =COUNT(B1:B5) → 5
DATA ANALYTICS METAM IT SOLUTIONS

✅ Educational Insight: Teacher can quickly analyze class performance.

📌 Case Study 3: Attendance Register

Class attendance sheet:
Present, Absent, Present, Present, Absent, Present
 Total Entries: =COUNTA(C1:C6) → 6

 Total “Present” Count (using COUNTIF): =COUNTIF(C1:C6, "Present") → 4

 Total “Absent” Count (using COUNTIF): =COUNTIF(C1:C6, "Absent") → 2
✅ HR/Education Insight: Attendance percentage can be tracked.

📌 Case Study 4: Inventory Stock

Items in warehouse:
Shoes=50, Shirts=75, Pants=100, Caps=20, Jackets=40
 Total Stock: =SUM(D1:D5) → 285

 Average Stock: =AVERAGE(D1:D5) → 57

 Lowest Stock Item: =MIN(D1:D5) → 20 (Caps)
 Highest Stock Item: =MAX(D1:D5) → 100 (Pants)
 Total Items Counted: =COUNT(D1:D5) → 5
✅ Business Insight: Helps identify stock shortages & best-selling products.
🔹 Problem 1: Sales Report Analysis
A company has sales for 5 products in different regions:
Product North South East West
Shoes 1200 1500 1100 1000
Shirts 800 950 700 650
Pants 1500 1800 1700 1600
Caps 300 400 250 200
Jackets 900 1000 850 750
Tasks:
1. Find total sales of Shoes across all regions.
👉 =SUM(B2:E2) → 4800

2. Find average sales of Pants across all regions.

👉 =AVERAGE(B4:E4) → 1650

3. Find region with minimum sales for Jackets.

👉 =MIN(B6:E6) → 750

4. Find region with maximum sales for Shirts.

👉 =MAX(B3:E3) → 950
DATA ANALYTICS METAM IT SOLUTIONS

5. Count how many regions recorded more than 1000 sales for Shoes.
👉 =COUNTIF(B2:E2, ">1000") → 3

🔹 Problem 2: Student Performance

Student marks (out of 100):
Student Math Science English History
A 85 90 78 88
B 70 75 80 72
C 95 88 92 90
D 60 65 58 62
Tasks:
1. Find total marks of Student A.
👉 =SUM(B2:E2) → 341

2. Find average marks of Student C.

👉 =AVERAGE(B4:E4) → 91.25

3. Find highest marks in Science subject.

👉 =MAX(C2:C5) → 90

4. Find lowest marks in English subject.

👉 =MIN(D2:D5) → 58

5. Count how many students scored more than 80 in Math.

👉 =COUNTIF(B2:B5, ">80") → 2

🔹 Problem 3: Employee Attendance

Attendance record for 1 week:
Employee Mon Tue Wed Thu Fri
John P P A P P
Mary A P P A P
Alex P P P P P
Rina P A P P A
Tasks:
1. Count total working days recorded for John.
👉 =COUNTA(B2:F2) → 5

2. Count number of Absents for Mary.

👉 =COUNTIF(B3:F3, "A") → 2

3. Count number of Presents for Alex.

👉 =COUNTIF(B4:F4, "P") → 5

4. Find employee with maximum absents (using MAX on absents).

👉 =MAX(G2:G5) (if you calculate absents separately in column G).
DATA ANALYTICS METAM IT SOLUTIONS

LOGICAL FUNCTIONS
Logical functions are used to test conditions and return results automatically (like Pass/Fail,
Yes/No, etc.).

🔹 1. IF Function
👉 Tests a condition and returns one value if TRUE and another if FALSE.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
Marks = 60, Pass mark = 50
=IF(A1>=50,"Pass","Fail") → Pass

🔹 2. AND Function
👉 Returns TRUE if all conditions are TRUE, else FALSE.
Syntax:
=AND(condition1, condition2, …)
Example:
Check if a student scored more than 50 in both Math and English:
=AND(B2>=50, C2>=50) → TRUE/FALSE
Often used inside IF:
=IF(AND(B2>=50, C2>=50),"Pass","Fail")

🔹 3. OR Function
👉 Returns TRUE if any one condition is TRUE.
Syntax:
=OR(condition1, condition2, …)
Example:
Check if a student passed in at least one subject:
=OR(B2>=50, C2>=50) → TRUE/FALSE
Inside IF:
=IF(OR(B2>=50, C2>=50),"Pass","Fail")

🔹 4. IFERROR Function
👉 Catches and replaces errors with a custom value.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1,"Error: Division by Zero")
 If B1 = 0 → Output = "Error: Division by Zero"

 If B1 = 5 → Output = A1/5
DATA ANALYTICS METAM IT SOLUTIONS

🔹 5. Nested IF
👉 Multiple IF statements combined for complex decisions.
Syntax:
=IF(condition1, result1, IF(condition2, result2, result3))
Example:
Grading system:
 =80 → "A"

 =60 → "B"
 <60 → "C"
 <21 - FAIL
Formula:
=IF(A1>=80,"A",IF(A1>=60,"B","C"))
=IF(A1>=80,"A",IF(A1>=60,"B",IF(A1<=21,”FAIL”,C)

🔹 What is a Pivot Table?

A Pivot Table in Excel (or similar tools like Google Sheets, Power BI) is a powerful tool that
lets you summarize, analyze, and present large datasets in a structured way.
It can quickly show totals, averages, counts, and comparisons without using formulas.
📊 Pivot Tables – Explained Clearly
🔹 What is a Pivot Table?
A Pivot Table is a tool in Excel (and Google Sheets, Power BI, etc.) that allows you to
summarize, analyze, explore, and present large datasets quickly without writing
formulas.
Think of it as a data summarizer – you can take thousands of rows and instantly find totals,
averages, counts, and comparisons.

🔹 Key Parts of a Pivot Table

When you create a Pivot Table, you work with four main areas:
1. Rows → Categories you want to list (e.g., Region, Product).
2. Columns → Subcategories that appear across the top (e.g., Year, Month).
3. Values → Numbers you want to calculate (e.g., Sales, Quantity, Count).
4. Filters → Fields you want to filter (e.g., show only 2024 sales).

🔹 How to Create a Pivot Table in Excel

1. Select your dataset (including column headers).
2. Go to Insert → PivotTable.
DATA ANALYTICS METAM IT SOLUTIONS

3. Choose where to place the Pivot Table (new worksheet is best).

4. Drag fields into Rows, Columns, Values, Filters as needed.

🔹 Types of Summaries You Can Do

 Sum → Total sales or revenue

 Average → Average salary, average marks

 Count → Number of transactions/orders
 Max/Min → Largest or smallest value
 % of Total → Share of each item in overall data
 Running Total → Cumulative figures over time

🔹 Grouping in Pivot Tables

You can group data to analyze it better:
 Dates → By Year, Quarter, Month, Week.

 Numbers → Into ranges (e.g., 0–1000, 1001–5000).

 Text → Combine categories (e.g., "East + West = Domestic").

🔹 Example Dataset
Region Product Sales
East Laptop 50,000
East Mobile 30,000
West Laptop 40,000
West Mobile 25,000
North Laptop 60,000
North Mobile 35,000

Example 1: Region-wise Sales

Pivot Table → Rows = Region, Values = Sum of Sales
Region Sales
East 80,000
West 65,000
North 95,000
Total 240,000

Example 2: Product-wise Sales

Pivot Table → Rows = Product, Values = Sum of Sales
DATA ANALYTICS METAM IT SOLUTIONS

Product Sales
Laptop 150,000
Mobile 90,000
Total 240,000

Example 3: Region vs Product Sales

Pivot Table → Rows = Region, Columns = Product, Values = Sum of Sales
Region Laptop Mobile Total
East 50,000 30,000 80,000
West 40,000 25,000 65,000
North 60,000 35,000 95,000
Total 150,000 90,000 240,000

🔹 Advantages of Pivot Tables

✔ Summarize huge data instantly
✔ No complex formulas required
✔ Easy to filter and drill dhown into details
✔ Helps in comparisons (region-wise, product-wise, etc.)
✔ Flexible – can change layout anytime

🔎 Lookup Functions in Excel

1. VLOOKUP (Vertical Lookup)
👉 Used when data is arranged vertically (lookup values in a column).
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
 lookup_value → The value you want to find.

 table_array → The data range.

 col_index_num → The column number from which to return the value.
 range_lookup → FALSE for exact match, TRUE for approximate.
📌 Example:
Find the Price of Product ID 102.
Product ID Product Price
101 Pen 10
102 Book 50
103 Bag 200
Formula:
DATA ANALYTICS METAM IT SOLUTIONS

=VLOOKUP(102, A2:C4, 3, FALSE)

✅ Result → 50

2. HLOOKUP (Horizontal Lookup)

👉 Used when data is arranged horizontally (lookup values in a row).
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
📌 Example:
Find the Sales for Q2.
Quarter Q1 Q2 Q3 Q4
Sales 500 800 600 900
Formula:
=HLOOKUP("Q2", A1:E2, 2, FALSE)
✅ Result → 800

3. XLOOKUP (Modern Replacement)

👉 More powerful, flexible, and removes many VLOOKUP/HLOOKUP limitations.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],
[search_mode])
📌 Example:
Find the Salary of "Ravi".
Name Salary
Raj 30,000
Ravi 40,000
Rani 35,000
Formula:
=XLOOKUP("Ravi", A2:A4, B2:B4, "Not Found")
✅ Result → 40,000
✨ Advantages over VLOOKUP:
 Can search left/right/up/down.

 No need for column index numbers.

 Allows default result if not found.

4. INDEX + MATCH (Power Combo)

👉 A flexible alternative to VLOOKUP.
INDEX: Returns a value from a table given row & column number.
=INDEX(array, row_num, [column_num])
MATCH: Returns the position of a value in a range.
=MATCH(lookup_value, lookup_array, [match_type])
📌 Example (using same Product table):
Find the Price of Product ID 103.
DATA ANALYTICS METAM IT SOLUTIONS

Formula:
=INDEX(C2:C4, MATCH(103, A2:A4, 0))
✅ Result → 200
✨ Advantages over VLOOKUP:
 Works even if lookup column is not the first.

 Faster for large datasets.

 More flexible for complex lookups.

🔑 Summary
 VLOOKUP → Quick for vertical lookups.

 HLOOKUP → Quick for horizontal lookups.

 XLOOKUP → Modern, powerful, flexible (best choice in Excel 365/2021).
 INDEX+MATCH → Advanced alternative, highly efficient.

🔎 More Lookup Function Examples

1. VLOOKUP – Vertical Search
Scenario: Find the Department of Employee ID 102.
Emp_ID Name Department Salary
101 Raj HR 30000
102 Ravi IT 40000
103 Rani Finance 35000
Formula:
=VLOOKUP(102, A2:D4, 3, FALSE)
✅ Result → IT

2. HLOOKUP – Horizontal Search

Scenario: Find the Q3 Marks of Sunita.
Student Anil Sunita Meena Kiran
Q1 80 85 78 90
Q2 88 92 84 86
Q3 75 89 82 95
Formula:
=HLOOKUP("Q3", A1:E4, 3, FALSE)
✅ Result → 75 (for Anil).
👉 To get Sunita’s Q3 marks, use column reference or XLOOKUP instead (better option).

3. XLOOKUP – Modern Powerful Lookup

Scenario: Find the Stock available for “Laptop”.
DATA ANALYTICS METAM IT SOLUTIONS

Product_ID Product Price Stock

201 Pen 10 200
202 Book 50 150
203 Bag 200 100
204 Laptop 50000 50
Formula:
=XLOOKUP("Laptop", B2:B5, D2:D5, "Not Found")
✅ Result → 50

4. INDEX + MATCH – Flexible Combo

Scenario: Find the Science marks of Roll No 3.
Roll_No Name Maths Science
1 Anil 80 70
2 Sunita 90 88
3 Meena 75 82
4 Kiran 85 95
Formula:
=INDEX(D2:D5, MATCH(3, A2:A5, 0))
✅ Result → 82

What is XLOOKUP?

 XLOOKUP is a modern replacement for VLOOKUP and HLOOKUP.

 It can search for a value in a row or column and return a matching value from another
row/column.
 Works both vertically and horizontally.
📖 Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],

[search_mode])

Arguments:

1. lookup_value → the value you want to search.

2. lookup_array → the range/array to search in.
3. return_array → the range/array from which to return the result.
4. [if_not_found] → (optional) what to display if no match is found.
5. [match_mode] → (optional)
DATA ANALYTICS METAM IT SOLUTIONS

 0 = exact match (default)

 -1 = exact match or next smaller
 1 = exact match or next larger
 2 = wildcard match (*, ?, ~)
6. [search_mode] → (optional)
 1 = search first-to-last (default)
 -1 = search last-to-first

✅ Example 1: Simple Lookup

Find the price of “Mango” from a list:
Fruit Price
Apple 50
Mango 80
Orange40

Formula:
=XLOOKUP("Mango", A2:A4, B2:B4)
Result → 80
✅ Example 2: With Not Found Message
=XLOOKUP("Pineapple", A2:A4, B2:B4, "Not Available")
Result → Not Available
✅ Example 3: Approximate Match
Find the grade for a score:
Score Grade
0 F
40 D
60 C
75 B
90 A

Formula for a score of 77:

=XLOOKUP(77, A2:A6, B2:B6, , -1)
Result → B

📌 Introduction to Python
1. What is Python?
 Python is a high-level, interpreted, object-oriented programming language.

 Created by Guido van Rossum in 1991.

 Known for its simplicity and readability (its syntax looks like English).
DATA ANALYTICS METAM IT SOLUTIONS

2. Key Features of Python

 ✅ Easy to learn – simple syntax, beginner-friendly.

 ✅ Interpreted – runs line by line (no need for compilation).

 ✅ Cross-platform – works on Windows, Mac, Linux.

 ✅ Free & Open Source – anyone can use and modify.

 ✅ Large standard library – has built-in modules (math, datetime, os, etc.).

 ✅ Supports multiple programming styles – procedural, object-oriented, functional.

 ✅ Huge community – widely used in industry and academics.

3. What is Python Used For?

Python is very versatile:
 🧮 Data Science & Machine Learning (NumPy, Pandas, TensorFlow)

 🌐 Web Development (Django, Flask, FastAPI)

 🤖 Artificial Intelligence & Automation

 🎮 Game Development (Pygame)

 📊 Data Visualization (Matplotlib, Seaborn, Power BI integration)

 Desktop Applications (Tkinter, PyQt)

 🧪 Scientific Computing & Research

4. Basic Python Example

# My first Python program
print("Hello, Python!")

# Variables
name = "Alex"
age = 22

# Display output
print("Name:", name)
print("Age:", age)

# Simple condition
if age >= 18:
print("You are an adult.")
else:
DATA ANALYTICS METAM IT SOLUTIONS

print("You are a minor.")

Output:
Hello, Python!
Name: Alex
Age: 22
You are an adult.

⚡ In short:
Python = Beginner-friendly + Powerful + Widely used in real-world applications 🚀

🐍 Python – Applications, Advantages & Disadvantages

1. Applications of Python
Python is widely used in many fields:
 🌐 Web Development → Django, Flask, FastAPI

 📊 Data Science & Machine Learning → NumPy, Pandas, Scikit-learn, TensorFlow

 ⚙ Automation & Scripting → Automating repetitive tasks (files, emails, testing)

 🎮 Game Development → Pygame

 📈 Data Visualization → Matplotlib, Seaborn, Plotly

 🖥 Desktop Applications → Tkinter, PyQt

 🔒 Networking & Cybersecurity tools

 🧪 Scientific Computing & Research → SciPy, SymPy

2. Advantages of Python
✅ Easy to learn and read (simple syntax like English)
✅ Large standard library and many frameworks available
✅ Cross-platform (runs on Windows, Mac, Linux)
✅ Big community support (millions of developers)
✅ Supports multiple styles (object-oriented, procedural, functional)
✅ Popular in trending fields (AI, ML, Data Science)
✅ Great for rapid development & prototyping

3. Disadvantages of Python
⚠️Slower than compiled languages (C, C++)
⚠️Uses more memory (not best for memory-critical apps)
⚠️Not ideal for mobile app development
⚠️Limited in browser-based programming
⚠️Dynamic typing may lead to runtime errors

👉 Conclusion: Python’s advantages (simplicity, power, versatility) make it a top choice

DATA ANALYTICS METAM IT SOLUTIONS

for most projects, though for mobile apps or high-performance systems, other
languages may be preferred.

1. Installing Python
 Option 1: Install directly

o Download from [Link].

o Install, then check version in terminal:

o python --version

 Option 2: Install via Anaconda (Recommended for Data/ML)

o Download Anaconda.

o It includes Python + Jupyter Notebook + many useful libraries.

2. Jupyter Notebook / Google Colab

 Jupyter Notebook

o After Anaconda install:

o jupyter notebook

o Opens in browser → Create .ipynb files.

o Cells can run Python code or Markdown text.

 Google Colab
o No installation needed → Go to [Link].

o Free cloud-based Jupyter environment.

o Saves notebooks in Google Drive.

3. Basic Python Syntax

Python is simple and readable.
(a) Print Statements
print("Hello, World!")
print(5 + 3)
(b) Comments
 Single line comment:

 # This is a comment
 print("Hello")
 Multi-line comment (using triple quotes):
DATA ANALYTICS METAM IT SOLUTIONS

 """
 This is a
 multi-line comment
 """
 print("Hi")
(c) Variables
name = "Alice"
age = 25
print(Name, age)
(d) Indentation (important in Python)
if age > 18:
print("You are an adult") # 4 spaces indentation
else:
print(

📖 Python Basics – Theory

1. Variables
 A variable is a named memory location used to store data.

 In Python, you don’t need to declare a variable with its type before using it (it is
dynamically typed).
 The type of a variable is determined automatically when a value is assigned.
Example:
x = 10 # integer variable
name = "Sam" # string variable

2. Data Types
Python has several built-in data types. The main ones are:
 int (Integer) → Whole numbers, positive or negative. Example: 5, -20, 1000

 float (Floating point) → Decimal or fractional numbers. Example: 3.14, -7.5

 str (String) → Sequence of characters inside quotes. Example: "Python", 'Hello'
 bool (Boolean) → Logical values True or False.
Why data types?
They tell Python how the data should be stored and what operations can be performed on it.

3. Operators
Operators are symbols that perform operations on variables and values.
(a) Arithmetic Operators
Used for mathematical calculations: + , - , * , / , // , % , **
DATA ANALYTICS METAM IT SOLUTIONS

(b) Relational (Comparison) Operators

Used to compare two values: == , != , > , < , >= , <=
(c) Logical Operators
Used with boolean values: and , or , not
(d) Assignment Operators
Used to assign values: = , += , -= , *= , /=

4. Input and Output

 Output → print() function is used to display data on the screen.
Example: print("Hello World")
 Input → input() function is used to take input from the user.
By default, input is read as a string.
Example:
 age = input("Enter your age: ") # stores string
 age = int(age) # convert to integer

5. Summary
 Variables → Store data.

 Data types → Define type of data (int, float, str, bool).

 Operators → Perform operations (arithmetic, logical, comparison).
 Input/Output → input() for taking input, print() for displaying output.

1. Variables
 A variable is a name that stores a value.

 Python doesn’t require declaring types explicitly.

x = 10 # integer
name = "Alex" # string
pi = 3.14 # float
is_active = True # Boolean

Python - Variable Names

Variable Names
A variable can have a short name (like x and y) or a more descriptive name
(age, carname, total_volume).
Rules for Python variables:
 A variable name must start with a letter or the underscore character

 A variable name cannot start with a number

DATA ANALYTICS METAM IT SOLUTIONS

 A variable name can only contain alpha-numeric characters and

underscores (A-z, 0-9, and _ )
 Variable names are case-sensitive (age, Age and AGE are three different
variables)
 A variable name cannot be any of the Python keywords.
Example
Legal variable names:
myvar = "John"
my_var = "John"
_my_var = "John"
myVar = "John"
MYVAR = "John"
myvar2 = "John"

Example
Illegal variable names:
2myvar = "John"
my-var = "John"
my var = "John"
Remember that variable names are case-sensitive
Multi Words Variable Names
Variable names with more than one word can be difficult to read.
There are several techniques you can use to make them more readable:
Camel Case
Each word, except the first, starts with a capital letter:
myVariableName = "John"

Pascal Case
Each word starts with a capital letter:
MyVariableName = "John"

Snake Case
Each word is separated by an underscore character:
my_variable_name = "John"

Python Variables - Assign Multiple Values

Many Values to Multiple Variables

DATA ANALYTICS METAM IT SOLUTIONS

Python allows you to assign values to multiple variables in one line:

Example
x, y, z = "Orange", "Banana", "Cherry"
print(x)
print(y)
print(z)
Note: Make sure the number of variables matches the number of values, or else
you will get an error.

One Value to Multiple Variables

And you can assign the same value to multiple variables in one line:
Example
x = y = z = "Orange"
print(x)
print(y)
print(z)

Unpack a Collection
If you have a collection of values in a list, tuple etc. Python allows you to
extract the values into variables. This is called unpacking.
Example
Unpack a list:
fruits = ["apple", "banana", "cherry"]
x, y, z = fruits
print(x)
print(y)
print(z)

Python - Output Variables

Output Variables
The Python print() function is often used to output variables.
Example
x = "Python is awesome"
print(x)
In the print() function, you output multiple variables, separated by a comma:
Example
x = "Python"
y = "is"
z = "awesome"
print(x, y, z)
DATA ANALYTICS METAM IT SOLUTIONS

You can also use the + operator to output multiple variables:

Example
x = "Python "
y = "is "
z = "awesome"
print(x + y + z)
Notice the space character after "Python " and "is ", without them the result
would be "Pythonisawesome".
For numbers, the + character works as a mathematical operator:
Example
x=5
y = 10
print(x + y)
In the print() function, when you try to combine a string and a number with
the + operator, Python will give you an error:
Example
x=5
y = "John"
print(x + y)
The best way to output multiple variables in the print() function is to separate
them with commas, which even support different data types:
Example
x=5
y = "John"
print(x, y)

Python - Global Variables

Global Variables
Variables that are created outside of a function (as in all of the examples in the
previous pages) are known as global variables.
Global variables can be used by everyone, both inside of functions and outside.
Example
Create a variable outside of a function, and use it inside the function
x = "awesome"

def myfunc():
print("Python is " + x)

myfunc()
If you create a variable with the same name inside a function, this variable will
be local, and can only be used inside the function. The global variable with the
same name will remain as it was, global and with the original value.
Example
DATA ANALYTICS METAM IT SOLUTIONS

Create a variable inside a function, with the same name as the global variable
x = "awesome"

def myfunc():
x = "fantastic"
print("Python is " + x)

myfunc()

print("Python is " + x)

The global Keyword

Normally, when you create a variable inside a function, that variable is local,
and can only be used inside that function.
To create a global variable inside a function, you can use the global keyword.
Example
If you use the global keyword, the variable belongs to the global scope:
def myfunc():
global x
x = "fantastic"

myfunc()

print("Python is " + x)

Also, use the global keyword if you want to change a global variable inside a
function.
Example
To change the value of a global variable inside a function, refer to the variable
by using the global keyword:
x = "awesome"

def myfunc():
global x
x = "fantastic"

myfunc()

print("Python is " + x)
DATA ANALYTICS METAM IT SOLUTIONS

2. Data Types
Main Python data types:
 int → whole numbers (e.g., 10, -5, 0)

 float → decimal numbers (e.g., 3.14, -7.5)

 str → strings/text (e.g., "Hello", 'Python')
 bool → boolean values (True, False)
a=5 # int
b = 2.5 # float
c = "Python" # str
d = True # bool
Check data type with type():
print(type(a)) # <class 'int'>
print(type(b)) # <class 'float'>
print(type(c)) # <class 'str'>
print(type(d)) # <class 'bool'>

3. Python Operators
Operators are used to perform operations on variables and values.
In the example below, we use the + operator to add together two values:
Example
print(10 + 5)
Python divides the operators in the following groups:
 Arithmetic operators

 Assignment operators
 Comparison operators
 Logical operators
 Identity operators
 Membership operators
 Bitwise operators

Python Arithmetic Operators

Arithmetic operators are used with numeric values to perform common
mathematical operations:
Operator Name Example

+ Addition x+y
DATA ANALYTICS METAM IT SOLUTIONS

- Subtraction x-y

* Multiplication x*y

/ Division x/y

% Modulus x%y

** Exponentiation x ** y

// Floor division x // y

Python Assignment Operators

Assignment operators are used to assign values to variables:
Operator Example Same As

= x=5 x=5

+= x += 3 x=x+3

-= x -= 3 x=x-3

*= x *= 3 x=x*3

/= x /= 3 x=x/3

%= x %= 3 x=x%3

//= x //= 3 x = x // 3

**= x **= 3 x = x ** 3

&= x &= 3 x=x&3

|= x |= 3 x=x|3

^= x ^= 3 x=x^3

>>= x >>= 3 x = x >> 3

<<= x <<= 3 x = x << 3

:= print(x := 3) x=3
print(x)

Python Comparison Operators

DATA ANALYTICS METAM IT SOLUTIONS

Comparison operators are used to compare two values:

Operator Name Example

== Equal x == y

!= Not equal x != y

> Greater than x>y

< Less than x<y

>= Greater than or equal to x >= y

<= Less than or equal to x <= y

Python Logical Operators

Logical operators are used to combine conditional statements:
Operator Description Example

and Returns True if both statements are true x < 5 and x < 10

or Returns True if one of the statements is true x < 5 or x < 4

not Reverse the result, returns False if the result not(x < 5 and
is true x < 10)

Python Identity Operators

Identity operators are used to compare the objects, not if they are equal, but if
they are actually the same object, with the same memory location:
Operator Description Example

is Returns True if both variables are the same x is y

object

is not Returns True if both variables are not the x is not y

same object

Python Membership Operators

Membership operators are used to test if a sequence is presented in an object:
Operator Description Example

in Returns True if a sequence with the specified value x in y

DATA ANALYTICS METAM IT SOLUTIONS

is present in the object

not in Returns True if a sequence with the specified value x not in y

is not present in the object

Python Bitwise Operators

Bitwise operators are used to compare (binary) numbers:
Operator Name Description Example

& AND Sets each bit to 1 if both bits are 1 x&y

| OR Sets each bit to 1 if one of two bits is 1 x|y

^ XOR Sets each bit to 1 if only one of two bits is 1 x^y

~ NOT Inverts all the bits ~x

<< Zero fill Shift left by pushing zeros in from the right and let x << 2
left shift the leftmost bits fall off

>> Signed Shift right by pushing copies of the leftmost bit in x >> 2
right shift from the left, and let the rightmost bits fall off

Operator Precedence
Operator precedence describes the order in which operations are performed.
Example
Parentheses has the highest precedence, meaning that expressions inside
parentheses must be evaluated first:
print((6 + 3) - (6 + 3))
Example
Multiplication * has higher precedence than addition +, and therefore
multiplications are evaluated before additions:
print(100 + 5 * 3)

The precedence order is described in the table below, starting with the highest
precedence at the top:
Operator Description

() Parentheses
DATA ANALYTICS METAM IT SOLUTIONS

** Exponentiation

+x -x ~x Unary plus, unary minus, and bitwise NOT

* / // % Multiplication, division, floor division, and

modulus

+ - Addition and subtraction

<< >> Bitwise left and right shifts

& Bitwise AND

^ Bitwise XOR

| Bitwise OR

== != > >= < <= is is not in not Comparisons, identity, and membership
in operators

not Logical NOT

and AND

or OR
If two operators have the same precedence, the expression is evaluated from left
to right.
Example
Addition + and subtraction - has the same precedence, and therefore we
evaluate the expression from left to right:
print(5 + 4 - 7 + 3)

4. Input and Output

 Output → print()

print("Hello, Python!")
 Input → input()

name = input("Enter your name: ")

print("Welcome,", name)

num = int(input("Enter a number: ")) # converting input string to integer

print("You entered:", num)

1. Numeric Types
DATA ANALYTICS METAM IT SOLUTIONS

 int → Whole numbers (positive, negative, zero).

👉 In Python, no fixed range (limited only by system memory).

o Example: x = 12345678901234567890 (valid in Python).

o In languages like C:

 int (32-bit) → –2,147,483,648 to 2,147,483,647

 short (16-bit) → –32,768 to 32,767
 float → Decimal numbers, stored as double precision (64-bit IEEE 754).
👉 Approximate range:

o ±1.7 × 10^308 (very large)

o Precision: about 15–16 decimal digits.

 complex → Complex numbers with real + imaginary part.

Example: z = 3 + 5j
Range → depends on float precision (same as above).

2. Text Type
 str → Sequence of Unicode characters.
Range: practically unlimited, depends on memory.
Example: "Hello, World!"

3. Boolean Type
 bool → Represents logical values.
Range: True or False (internally stored as 1 or 0).

4. Sequence Types
 list, tuple, range → Collections of items.
Range depends on available memory.
Example:
 my_list = [1, 2, 3, "hello"]

5. Mapping Type
 dict → Key-value pairs.
Range depends on memory.

6. Set Types
 set, frozenset → Unordered collection of unique elements.
DATA ANALYTICS METAM IT SOLUTIONS

7. Binary Types
 bytes, bytearray, memoryview → Used for binary data.
Range: Each element is from 0 to 255.

✅ Summary Table
Data Type Example Range
int 100, -5000 Unlimited (memory-bound)
float 3.14, -0.001 ±1.7 × 10^308
complex 2+3j Based on float range
str "hello" Unlimited (memory-bound)
bool True / False Only 2 values
bytes b"abc" 0–255 per element

Python Lists

mylist = ["apple", "banana", "cherry"]

List
Lists are used to store multiple items in a single variable.
Lists are one of 4 built-in data types in Python used to store collections of data,
the other 3 are Tuple, Set, and Dictionary, all with different qualities and usage.
Lists are created using square brackets:
Example
Create a List:
thislist = ["apple", "banana", "cherry"]
print(thislist)

List Items
List items are ordered, changeable, and allow duplicate values.
List items are indexed, the first item has index [0], the second item has
index [1] etc.

Ordered
When we say that lists are ordered, it means that the items have a defined order,
and that order will not change.
If you add new items to a list, the new items will be placed at the end of the list.
Note: There are some list methods that will change the order, but in general: the
order of the items will not change.
DATA ANALYTICS METAM IT SOLUTIONS

Changeable
The list is changeable, meaning that we can change, add, and remove items in a
list after it has been created.

Allow Duplicates
Since lists are indexed, lists can have items with the same value:
Example
Lists allow duplicate values:
thislist = ["apple", "banana", "cherry", "apple", "cherry"]
print(thislist)

List Length
To determine how many items a list has, use the len() function:
Example
Print the number of items in the list:
thislist = ["apple", "banana", "cherry"]
print(len(thislist))

List Items - Data Types

List items can be of any data type:
Example
String, int and boolean data types:
list1 = ["apple", "banana", "cherry"]
list2 = [1, 5, 7, 9, 3]
list3 = [True, False, False]

A list can contain different data types:

Example
A list with strings, integers and boolean values:
list1 = ["abc", 34, True, 40, "male"]

type()
From Python's perspective, lists are defined as objects with the data type 'list':
<class 'list'>
Example
What is the data type of a list?
mylist = ["apple", "banana", "cherry"]
print(type(mylist))

The list() Constructor

It is also possible to use the list() constructor when creating a new list.
Example
Using the list() constructor to make a List:
thislist = list(("apple", "banana", "cherry")) # note the double round-brackets
DATA ANALYTICS METAM IT SOLUTIONS

print(thislist)

Python Collections (Arrays)

There are four collection data types in the Python programming language:
 List is a collection which is ordered and changeable. Allows duplicate
members.
 Tuple is a collection which is ordered and unchangeable. Allows
duplicate members.
 Set is a collection which is unordered, unchangeable*, and unindexed. No
duplicate members.
 Dictionary is a collection which is ordered** and changeable. No
duplicate members.
*Set items are unchangeable, but you can remove and/or add items whenever
you like.
**As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier,
dictionaries are unordered.
When choosing a collection type, it is useful to understand the properties of that
type. Choosing the right type for a particular data set could mean retention of
meaning, and, it could mean an increase in efficiency or security.

Access Items
List items are indexed and you can access them by referring to the index
number:
Example
Print the second item of the list:
thislist = ["apple", "banana", "cherry"]
print(thislist[1])

Note: The first item has index 0.

Negative Indexing
Negative indexing means start from the end
-1 refers to the last item, -2 refers to the second last item etc.
Example
Print the last item of the list:
thislist = ["apple", "banana", "cherry"]
print(thislist[-1])

Range of Indexes
You can specify a range of indexes by specifying where to start and where to
DATA ANALYTICS METAM IT SOLUTIONS

end the range.

When specifying a range, the return value will be a new list with the specified
items.
Example
Return the third, fourth, and fifth item:
thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]
print(thislist[2:5])

Note: The search will start at index 2 (included) and end at index 5 (not
included).
Remember that the first item has index 0.
By leaving out the start value, the range will start at the first item:
Example
This example returns the items from the beginning to, but NOT including,
"kiwi":
thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]
print(thislist[:4])
By leaving out the end value, the range will go on to the end of the list:
Example
This example returns the items from "cherry" to the end:
thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]
print(thislist[2:])

Range of Negative Indexes

Specify negative indexes if you want to start the search from the end of the list:
Example
This example returns the items from "orange" (-4) to, but NOT including
"mango" (-1):
thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]
print(thislist[-4:-1])

Check if Item Exists

To determine if a specified item is present in a list use the in keyword:
Example
Check if "apple" is present in the list:
thislist = ["apple", "banana", "cherry"]
if "apple" in thislist:
print("Yes, 'apple' is in the fruits list")

Change Item Value

DATA ANALYTICS METAM IT SOLUTIONS

To change the value of a specific item, refer to the index number:

Example
Change the second item:
thislist = ["apple", "banana", "cherry"]
thislist[1] = "blackcurrant"
print(thislist)

Change a Range of Item Values

Example
Change the values "banana" and "cherry" with the values "blackcurrant" and
"watermelon":
thislist = ["apple", "banana", "cherry", "orange", "kiwi", "mango"]
thislist[1:3] = ["blackcurrant", "watermelon"]
print(thislist)

If you insert more items than you replace, the new items will be inserted where
you specified, and the remaining items will move accordingly:
Example
Change the second value by replacing it with two new values:
thislist = ["apple", "banana", "cherry"]
thislist[1:2] = ["blackcurrant", "watermelon"]
print(thislist)

Insert Items
To insert a new list item, without replacing any of the existing values, we can
use the insert() method.
The insert() method inserts an item at the specified index:
Example
Insert "watermelon" as the third item:
thislist = ["apple", "banana", "cherry"]
[Link](2, "watermelon")
print(thislist)
`
Note: As a result of the example above, the list will now contain 4 items.

Append Items
To add an item to the end of the list, use the append() method:
Example
Using the append() method to append an item:
thislist = ["apple", "banana", "cherry"]
[Link]("orange")
DATA ANALYTICS METAM IT SOLUTIONS

print(thislist)

Insert Items
To insert a list item at a specified index, use the insert() method.
The insert() method inserts an item at the specified index:
Example
Insert an item as the second position:
thislist = ["apple", "banana", "cherry"]
[Link](1, "orange")
print(thislist)
Note: As a result of the examples above, the lists will now contain 4 items.

Extend List
To append elements from another list to the current list, use
the extend() method.
Example
Add the elements of tropical to thislist:
thislist = ["apple", "banana", "cherry"]
tropical = ["mango", "pineapple", "papaya"]
[Link](tropical)
print(thislist)
The elements will be added to the end of the list.

Remove Specified Item

The remove() method removes the specified item.
Example
Remove "banana":
thislist = ["apple", "banana", "cherry"]
[Link]("banana")
print(thislist)
If there are more than one item with the specified value, the remove() method
removes the first occurrence:
Example
Remove the first occurrence of "banana":
thislist = ["apple", "banana", "cherry", "banana", "kiwi"]
[Link]("banana")
print(thislist)

Remove Specified Index

The pop() method removes the specified index.
Example
Remove the second item:
DATA ANALYTICS METAM IT SOLUTIONS

thislist = ["apple", "banana", "cherry"]

[Link](1)
print(thislist)

If you do not specify the index, the pop() method removes the last item.
Example
Remove the last item:
thislist = ["apple", "banana", "cherry"]
[Link]()
print(thislist)

The del keyword also removes the specified index:

Example
Remove the first item:
thislist = ["apple", "banana", "cherry"]
del thislist[0]
print(thislist)

The del keyword can also delete the list completely.

Example
Delete the entire list:
thislist = ["apple", "banana", "cherry"]
del thislist

Clear the List

The clear() method empties the list.
The list still remains, but it has no content.
Example
Clear the list content:
thislist = ["apple", "banana", "cherry"]
[Link]()
print(thislist)
[]

Python - Loop Lists

Loop Through a List

You can loop through the list items by using a for loop:
Example
Print all items in the list, one by one:
thislist = ["apple", "banana", "cherry"]
for x in thislist:
DATA ANALYTICS METAM IT SOLUTIONS

print(x)

Loop Through the Index Numbers

You can also loop through the list items by referring to their index number.
Use the range() and len() functions to create a suitable iterable.
Example
Print all items by referring to their index number:
thislist = ["apple", "banana", "cherry"]
for i in range(len(thislist)):
print(thislist[i])
The iterable created in the example above is [0, 1, 2].

Using a While Loop

You can loop through the list items by using a while loop.
Use the len() function to determine the length of the list, then start at 0 and loop
your way through the list items by referring to their indexes.
Remember to increase the index by 1 after each iteration.
Example
Print all items, using a while loop to go through all the index numbers
thislist = ["apple", "banana", "cherry"]
i=0
while i < len(thislist):
print(thislist[i])
i=i+1

Looping Using List Comprehension

List Comprehension offers the shortest syntax for looping through lists:
Example
A short hand for loop that will print all items in a list:
thislist = ["apple", "banana", "cherry"]
[print(x) for x in thislist]

Reverse Order
What if you want to reverse the order of a list, regardless of the alphabet?
The reverse() method reverses the current sorting order of the elements.
Example
Reverse the order of the list items:
thislist = ["banana", "Orange", "Kiwi", "cherry"]
[Link]()
DATA ANALYTICS METAM IT SOLUTIONS

print(thislist)

Use the copy() method

You can use the built-in List method copy() to copy a list.
Example
Make a copy of a list with the copy() method:
thislist = ["apple", "banana", "cherry"]
mylist = [Link]()
print(mylist)

Use the list() method

Another way to make a copy is to use the built-in method list().
Example
Make a copy of a list with the list() method:
thislist = ["apple", "banana", "cherry"]
mylist = list(thislist)
print(mylist)

Use the slice Operator

You can also make a copy of a list by using the : (slice) operator.
Example
Make a copy of a list with the : operator:
thislist = ["apple", "banana", "cherry"]
mylist = thislist[:]
print(mylist)

Join Two Lists

There are several ways to join, or concatenate, two or more lists in Python.
One of the easiest ways are by using the + operator.
Example
Join two list:
list1 = ["a", "b", "c"]
list2 = [1, 2, 3]

list3 = list1 + list2

print(list3)
Another way to join two lists is by appending all the items from list2 into list1,
one by one:
Example
Append list2 into list1:
list1 = ["a", "b" , "c"]
DATA ANALYTICS METAM IT SOLUTIONS

list2 = [1, 2, 3]

for x in list2:
[Link](x)

print(list1)
Or you can use the extend() method, where the purpose is to add elements from
one list to another list:
Example
Use the extend() method to add list2 at the end of list1:
list1 = ["a", "b" , "c"]
list2 = [1, 2, 3]

[Link](list2)
print(list1)

List Methods
Python has a set of built-in methods that you can use on lists.
Method Description

append() Adds an element at the end of the list

clear() Removes all the elements from the list

copy() Returns a copy of the list

count() Returns the number of elements with the specified value

extend() Add the elements of a list (or any iterable), to the end of the current list

index() Returns the index of the first element with the specified value

insert() Adds an element at the specified position

pop() Removes the element at the specified position

remove() Removes the item with the specified value

reverse() Reverses the order of the list

sort() Sorts the list

Python Tuples
DATA ANALYTICS METAM IT SOLUTIONS

mytuple = ("apple", "banana", "cherry")

Tuple
Tuples are used to store multiple items in a single variable.
Tuple is one of 4 built-in data types in Python used to store collections of data,
the other 3 are List, Set, and Dictionary, all with different qualities and usage.
A tuple is a collection which is ordered and unchangeable.
Tuples are written with round brackets.
Example
Create a Tuple:
thistuple = ("apple", "banana", "cherry")
print(thistuple)

Tuple Items
Tuple items are ordered, unchangeable, and allow duplicate values.
Tuple items are indexed, the first item has index [0], the second item has
index [1] etc.

Ordered
When we say that tuples are ordered, it means that the items have a defined
order, and that order will not change.

Unchangeable
Tuples are unchangeable, meaning that we cannot change, add or remove items
after the tuple has been created.

Allow Duplicates
Since tuples are indexed, they can have items with the same value:
Example
Tuples allow duplicate values:
thistuple = ("apple", "banana", "cherry", "apple", "cherry")
print(thistuple)

Tuple Length
To determine how many items a tuple has, use the len() function:
Example
Print the number of items in the tuple:
thistuple = ("apple", "banana", "cherry")
print(len(thistuple))

Create Tuple With One Item

To create a tuple with only one item, you have to add a comma after the item,
otherwise Python will not recognize it as a tuple.
Example
DATA ANALYTICS METAM IT SOLUTIONS

One item tuple, remember the comma:

thistuple = ("apple",)
print(type(thistuple))

#NOT a tuple
thistuple = ("apple")
print(type(thistuple))

Tuple Items - Data Types

Tuple items can be of any data type:
Example
String, int and boolean data types:
tuple1 = ("apple", "banana", "cherry")
tuple2 = (1, 5, 7, 9, 3)
tuple3 = (True, False, False)
A tuple can contain different data types:
Example
A tuple with strings, integers and boolean values:
tuple1 = ("abc", 34, True, 40, "male")

type()
From Python's perspective, tuples are defined as objects with the data type
'tuple':
<class 'tuple'>
Example
What is the data type of a tuple?
mytuple = ("apple", "banana", "cherry")
print(type(mytuple))

The tuple() Constructor

It is also possible to use the tuple() constructor to make a tuple.
Example
Using the tuple() method to make a tuple:
thistuple = tuple(("apple", "banana", "cherry")) # note the double round-
brackets
print(thistuple)

Python Collections (Arrays)

There are four collection data types in the Python programming language:
 List is a collection which is ordered and changeable. Allows duplicate
members.
 Tuple is a collection which is ordered and unchangeable. Allows
duplicate members.
DATA ANALYTICS METAM IT SOLUTIONS

 Set is a collection which is unordered, unchangeable*, and unindexed. No

duplicate members.
 Dictionary is a collection which is ordered** and changeable. No
duplicate members.
*Set items are unchangeable, but you can remove and/or add items whenever
you like.
**As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier,
dictionaries are unordered.
When choosing a collection type, it is useful to understand the properties of that
type. Choosing the right type for a particular data set could mean retention of
meaning, and, it could mean an increase in efficiency or security.

Python - Access Tuple Items

Access Tuple Items

You can access tuple items by referring to the index number, inside square
brackets:
Example
Print the second item in the tuple:
thistuple = ("apple", "banana", "cherry")
print(thistuple[1])
Note: The first item has index 0.

Negative Indexing
Negative indexing means start from the end.
-1 refers to the last item, -2 refers to the second last item etc.
Example
Print the last item of the tuple:
thistuple = ("apple", "banana", "cherry")
print(thistuple[-1])

Range of Indexes
You can specify a range of indexes by specifying where to start and where to
end the range.
When specifying a range, the return value will be a new tuple with the specified
items.
thistuple = ("apple", "banana", "cherry", "orange", "kiwi", "melon", "mango")
print(thistuple[:4])
By leaving out the end value, the range will go on to the end of the tuple:
Example
This example returns the items from "cherry" and to the end:
thistuple = ("apple", "banana", "cherry", "orange", "kiwi", "melon", "mango")
DATA ANALYTICS METAM IT SOLUTIONS

print(thistuple[2:])

Range of Negative Indexes

Specify negative indexes if you want to start the search from the end of the
tuple:
Example
This example returns the items from index -4 (included) to index -1 (excluded)
thistuple = ("apple", "banana", "cherry", "orange", "kiwi", "melon", "mango")
print(thistuple[-4:-1])

Check if Item Exists

To determine if a specified item is present in a tuple use the in keyword:
Example
Check if "apple" is present in the tuple:
thistuple = ("apple", "banana", "cherry")
if "apple" in thistuple:
print("Yes, 'apple' is in the fruits tuple")

Python - Update Tuples

Tuples are unchangeable, meaning that you cannot change, add, or remove
items once the tuple is created.
But there are some workarounds.

Change Tuple Values

Once a tuple is created, you cannot change its values. Tuples are unchangeable,
or immutable as it also is called.
But there is a workaround. You can convert the tuple into a list, change the list,
and convert the list back into a tuple.
Example
Convert the tuple into a list to be able to change it:
x = ("apple", "banana", "cherry")
y = list(x)
y[1] = "kiwi"
x = tuple(y)

print(x)

Add Items
Since tuples are immutable, they do not have a built-in append() method, but
there are other ways to add items to a tuple.
1. Convert into a list: Just like the workaround for changing a tuple, you can
DATA ANALYTICS METAM IT SOLUTIONS

convert it into a list, add your item(s), and convert it back into a tuple.
Example
Convert the tuple into a list, add "orange", and convert it back into a tuple:
thistuple = ("apple", "banana", "cherry")
y = list(thistuple)
[Link]("orange")
thistuple = tuple(y)

2. Add tuple to a tuple. You are allowed to add tuples to tuples, so if you want
to add one item, (or many), create a new tuple with the item(s), and add it to the
existing tuple:
Example
Create a new tuple with the value "orange", and add that tuple:
thistuple = ("apple", "banana", "cherry")
y = ("orange",)
thistuple += y

print(thistuple)

Note: When creating a tuple with only one item, remember to include a comma
after the item, otherwise it will not be identified as a tuple.

Remove Items
Note: You cannot remove items in a tuple.
Tuples are unchangeable, so you cannot remove items from it, but you can use
the same workaround as we used for changing and adding tuple items:
Example
Convert the tuple into a list, remove "apple", and convert it back into a tuple:
thistuple = ("apple", "banana", "cherry")
y = list(thistuple)
[Link]("apple")
thistuple = tuple(y)

Or you can delete the tuple completely:

Example
The del keyword can delete the tuple completely:
thistuple = ("apple", "banana", "cherry")
del thistuple
print(thistuple) #this will raise an error because the tuple no longer exists

Python - Unpack Tuples

Unpacking a Tuple
DATA ANALYTICS METAM IT SOLUTIONS

When we create a tuple, we normally assign values to it. This is called

"packing" a tuple:
Example
Packing a tuple:
fruits = ("apple", "banana", "cherry")

But, in Python, we are also allowed to extract the values back into variables.
This is called "unpacking":
Example
Unpacking a tuple:
fruits = ("apple", "banana", "cherry")

(green, yellow, red) = fruits # apple, banana, cherry

print(green)
print(yellow)
print(red)
Note: The number of variables must match the number of values in the tuple, if
not, you must use an asterisk to collect the remaining values as a list.

Using Asterisk*
If the number of variables is less than the number of values, you can add an * to
the variable name and the values will be assigned to the variable as a list:
Example
Assign the rest of the values as a list called "red":
fruits = ("apple", "banana", "cherry", "strawberry", "raspberry")

(green, yellow, *red) = fruits

print(green)
print(yellow)
print(red)
If the asterisk is added to another variable name than the last, Python will assign
values to the variable until the number of values left matches the number of
variables left.
Example
Add a list of values the "tropic" variable:
fruits = ("apple", "mango", "papaya", "pineapple", "cherry")

(green, *tropic, red) = fruits

print(green)
DATA ANALYTICS METAM IT SOLUTIONS

print(tropic)
print(red)

Python - Join Tuples

Join Two Tuples

To join two or more tuples you can use the + operator:
Example
Join two tuples:
tuple1 = ("a", "b" , "c")
tuple2 = (1, 2, 3)

tuple3 = tuple1 + tuple2

print(tuple3)

Multiply Tuples
If you want to multiply the content of a tuple a given number of times, you can
use the * operator:
Example
Multiply the fruits tuple by 2:
fruits = ("apple", "banana", "cherry")
mytuple = fruits * 2

print(mytuple)

Python - Tuple Methods

Tuple Methods
Python has two built-in methods that you can use on tuples.
Method Description

count() Returns the number of times a specified value occurs in a

tuple
index() Searches the tuple for a specified value and returns the
position of where
it was found
DATA ANALYTICS METAM IT SOLUTIONS

Python Sets

myset = {"apple", "banana", "cherry"}

Set
Sets are used to store multiple items in a single variable.
Set is one of 4 built-in data types in Python used to store collections of data, the
other 3 are List, Tuple, and Dictionary, all with different qualities and usage.
A set is a collection which is unordered, unchangeable*, and unindexed.
* Note: Set items are unchangeable, but you can remove items and add new
items.
Sets are written with curly brackets.
Example
Create a Set:
thisset = {"apple", "banana", "cherry"}
print(thisset)

Set Items
Set items are unordered, unchangeable, and do not allow duplicate values.

Unordered
Unordered means that the items in a set do not have a defined order.
Set items can appear in a different order every time you use them, and cannot be
referred to by index or key.

Unchangeable
Set items are unchangeable, meaning that we cannot change the items after the
set has been created.
Once a set is created, you cannot change its items, but you can remove items
and add new items.

Duplicates Not Allowed

Sets cannot have two items with the same value.
Example
Duplicate values will be ignored:
thisset = {"apple", "banana", "cherry", "apple"}

print(thisset)
Note: The values True and 1 are considered the same value in sets, and are
treated as duplicates:
Example
DATA ANALYTICS METAM IT SOLUTIONS

True and 1 is considered the same value:

thisset = {"apple", "banana", "cherry", True, 1, 2}

print(thisset)
Note: The values False and 0 are considered the same value in sets, and are
treated as duplicates:
Example
False and 0 is considered the same value:
thisset = {"apple", "banana", "cherry", False, True, 0}

print(thisset)

Get the Length of a Set

To determine how many items a set has, use the len() function.
Example
Get the number of items in a set:
thisset = {"apple", "banana", "cherry"}

print(len(thisset))

Set Items - Data Types

Set items can be of any data type:
Example
String, int and boolean data types:
set1 = {"apple", "banana", "cherry"}
set2 = {1, 5, 7, 9, 3}
set3 = {True, False, False}
A set can contain different data types:
Example
A set with strings, integers and boolean values:
set1 = {"abc", 34, True, 40, "male"}

type()
From Python's perspective, sets are defined as objects with the data type 'set':
<class 'set'>
Example
What is the data type of a set?
myset = {"apple", "banana", "cherry"}
print(type(myset))

The set() Constructor

It is also possible to use the set() constructor to make a set.
Example
DATA ANALYTICS METAM IT SOLUTIONS

Using the set() constructor to make a set:

thisset = set(("apple", "banana", "cherry")) # note the double round-brackets
print(thisset)

Python Collections (Arrays)

There are four collection data types in the Python programming language:
 List is a collection which is ordered and changeable. Allows duplicate
members.
 Tuple is a collection which is ordered and unchangeable. Allows
duplicate members.
 Set is a collection which is unordered, unchangeable*, and unindexed. No
duplicate members.
 Dictionary is a collection which is ordered** and changeable. No
duplicate members.
*Set items are unchangeable, but you can remove items and add new items.
**As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier,
dictionaries are unordered.
When choosing a collection type, it is useful to understand the properties of that
type. Choosing the right type for a particular data set could mean retention of
meaning, and, it could mean an increase in efficiency or security.

Python - Add Set Items

Add Items
Once a set is created, you cannot change its items, but you can add new items.
To add one item to a set use the add() method.
Example
Add an item to a set, using the add() method:
thisset = {"apple", "banana", "cherry"}

[Link]("orange")

print(thisset)

Add Sets
To add items from another set into the current set, use the update() method.
Example
Add elements from tropical into thisset:
thisset = {"apple", "banana", "cherry"}
tropical = {"pineapple", "mango", "papaya"}
DATA ANALYTICS METAM IT SOLUTIONS

[Link](tropical)

print(thisset)

Add Any Iterable

The object in the update() method does not have to be a set, it can be any
iterable object (tuples, lists, dictionaries etc.).
Example
Add elements of a list to at set:
thisset = {"apple", "banana", "cherry"}
mylist = ["kiwi", "orange"]

[Link](mylist)

print(thisset)

Python - Remove Set Items

Remove Item
To remove an item in a set, use the remove(), or the discard() method.
Example
Remove "banana" by using the remove() method:
thisset = {"apple", "banana", "cherry"}

[Link]("banana")

print(thisset)
Note: If the item to remove does not exist, remove() will raise an error.
Example
Remove "banana" by using the discard() method:
thisset = {"apple", "banana", "cherry"}

[Link]("banana")

print(thisset)
Note: If the item to remove does not exist, discard() will NOT raise an error.
You can also use the pop() method to remove an item, but this method will
remove a random item, so you cannot be sure what item that gets removed.
The return value of the pop() method is the removed item.
Example
Remove a random item by using the pop() method:
thisset = {"apple", "banana", "cherry"}
DATA ANALYTICS METAM IT SOLUTIONS

x = [Link]()

print(x)

print(thisset)
Note: Sets are unordered, so when using the pop() method, you do not know
which item that gets removed.
Example
The clear() method empties the set:
thisset = {"apple", "banana", "cherry"}

[Link]()

print(thisset)
Example
The del keyword will delete the set completely:
thisset = {"apple", "banana", "cherry"}

del thisset

print(thisset)

Symmetric Differences
The symmetric_difference() method will keep only the elements that are NOT
present in both sets.
Example
Keep the items that are not present in both sets:
set1 = {"apple", "banana", "cherry"}
set2 = {"google", "microsoft", "apple"}

set3 = set1.symmetric_difference(set2)

print(set3)
You can use the ^ operator instead of the symmetric_difference() method, and
you will get the same result.
Example
Use ^ to join two sets:
set1 = {"apple", "banana", "cherry"}
set2 = {"google", "microsoft", "apple"}

set3 = set1 ^ set2

DATA ANALYTICS METAM IT SOLUTIONS

print(set3)

Use the symmetric_difference_update() method to keep the items that are not
present in both sets:
set1 = {"apple", "banana", "cherry"}
set2 = {"google", "microsoft", "apple"}

set1.symmetric_difference_update(set2)

print(set1)

Python - Set Methods

Set Methods
Python has a set of built-in methods that you can use on sets.
Method Shortcut Description

add() Adds an element to the set

clear() Removes all the elements from the set

copy() Returns a copy of the set

difference() - Returns a set containing the difference betw

difference_update() -= Removes the items in this set that are also i

discard() Remove the specified item

intersection() & Returns a set, that is the intersection of two

intersection_update() &= Removes the items in this set that are not p

isdisjoint() Returns whether two sets have a intersectio

issubset() <= Returns whether another set contains this s

< Returns whether all items in this set is pres

issuperset() >= Returns whether this set contains another s

> Returns whether all items in other, specifie

pop() Removes an element from the set

DATA ANALYTICS METAM IT SOLUTIONS

remove() Removes the specified element

symmetric_difference() ^ Returns a set with the symmetric difference

symmetric_difference_update() ^= Inserts the symmetric differences from this

union() | Return a set containing the union of sets

update() |= Update the set with the union of this set an

Python Dictionaries

thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

Dictionary
Dictionaries are used to store data values in key:value pairs.
A dictionary is a collection which is ordered*, changeable and do not allow
duplicates.
As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier,
dictionaries are unordered.
Dictionaries are written with curly brackets, and have keys and values:
Example
Create and print a dictionary:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
print(thisdict)

Dictionary Items
Dictionary items are ordered, changeable, and do not allow duplicates.
Dictionary items are presented in key:value pairs, and can be referred to by
using the key name.
Example
Print the "brand" value of the dictionary:
thisdict = {
DATA ANALYTICS METAM IT SOLUTIONS

"brand": "Ford",
"model": "Mustang",
"year": 1964
}
print(thisdict["brand"])

Ordered or Unordered?
As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier,
dictionaries are unordered.
When we say that dictionaries are ordered, it means that the items have a
defined order, and that order will not change.
Unordered means that the items do not have a defined order, you cannot refer to
an item by using an index.

Changeable
Dictionaries are changeable, meaning that we can change, add or remove items
after the dictionary has been created.

Duplicates Not Allowed

Dictionaries cannot have two items with the same key:
Example
Duplicate values will overwrite existing values:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964,
"year": 2020
}
print(thisdict)

Dictionary Length
To determine how many items a dictionary has, use the len() function:
Example
Print the number of items in the dictionary:
print(len(thisdict))

Dictionary Items - Data Types

The values in dictionary items can be of any data type:
Example
String, int, boolean, and list data types:
thisdict = {
"brand": "Ford",
DATA ANALYTICS METAM IT SOLUTIONS

"electric": False,
"year": 1964,
"colors": ["red", "white", "blue"]
}

type()
From Python's perspective, dictionaries are defined as objects with the data type
'dict':
<class 'dict'>
Example
Print the data type of a dictionary:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
print(type(thisdict))

The dict() Constructor

It is also possible to use the dict() constructor to make a dictionary.
Example
Using the dict() method to make a dictionary:
thisdict = dict(name = "John", age = 36, country = "Norway")
print(thisdict)

Python - Access Dictionary Items

Accessing Items
You can access the items of a dictionary by referring to its key name, inside
square brackets:
Example
Get the value of the "model" key:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
x = thisdict["model"]

There is also a method called get() that will give you the same result:
Example
Get the value of the "model" key:
x = [Link]("model")
DATA ANALYTICS METAM IT SOLUTIONS

Get Keys
The keys() method will return a list of all the keys in the dictionary.
Example
Get a list of the keys:
x = [Link]()
The list of the keys is a view of the dictionary, meaning that any changes done
to the dictionary will be reflected in the keys list.
Example
Add a new item to the original dictionary, and see that the keys list gets updated
as well:
car = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

x = [Link]()

print(x) #before the change

car["color"] = "white"

print(x) #after the change

Get Values
The values() method will return a list of all the values in the dictionary.
Example
Get a list of the values:
x = [Link]()
The list of the values is a view of the dictionary, meaning that any changes done
to the dictionary will be reflected in the values list.
Example
Make a change in the original dictionary, and see that the values list gets
updated as well:
car = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

x = [Link]()
DATA ANALYTICS METAM IT SOLUTIONS

print(x) #before the change

car["year"] = 2020

print(x) #after the change

Example
Add a new item to the original dictionary, and see that the values list gets
updated as well:
car = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

x = [Link]()

print(x) #before the change

car["color"] = "red"

print(x) #after the change

Get Items
The items() method will return each item in a dictionary, as tuples in a list.
Example
Get a list of the key:value pairs
x = [Link]()
The returned list is a view of the items of the dictionary, meaning that any
changes done to the dictionary will be reflected in the items list.
Example
Make a change in the original dictionary, and see that the items list gets updated
as well:
car = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

x = [Link]()

print(x) #before the change

car["year"] = 2020
DATA ANALYTICS METAM IT SOLUTIONS

print(x) #after the change

Example
Add a new item to the original dictionary, and see that the items list gets
updated as well:
car = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}

x = [Link]()

print(x) #before the change

car["color"] = "red"

print(x) #after the change

Check if Key Exists

To determine if a specified key is present in a dictionary use the in keyword:
Example
Check if "model" is present in the dictionary:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
if "model" in thisdict:
print("Yes, 'model' is one of the keys in the thisdict dictionary")

Python - Change Dictionary Items

Change Values
You can change the value of a specific item by referring to its key name:
Example
Change the "year" to 2018:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
DATA ANALYTICS METAM IT SOLUTIONS

}
thisdict["year"] = 2018

Update Dictionary
The update() method will update the dictionary with the items from the given
argument.
The argument must be a dictionary, or an iterable object with key:value pairs.
Example
Update the "year" of the car by using the update() method:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
[Link]({"year": 2020, “color”:” yellow”})

Python - Add Dictionary Items

Adding Items
Adding an item to the dictionary is done by using a new index key and
assigning a value to it:
Example
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
thisdict["color"] = "red"
print(thisdict)

Update Dictionary
The update() method will update the dictionary with the items from a given
argument. If the item does not exist, the item will be added.
The argument must be a dictionary, or an iterable object with key:value pairs.
Example
Add a color item to the dictionary by using the update() method:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
[Link]({"color": "red"})
DATA ANALYTICS METAM IT SOLUTIONS

Python - Remove Dictionary Items

Removing Items
There are several methods to remove items from a dictionary:
Example
The pop() method removes the item with the specified key name:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
[Link]("model")
print(thisdict)
Example
The popitem() method removes the last inserted item (in versions before 3.7, a
random item is removed instead):
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
[Link]()
print(thisdict)
Example
The del keyword removes the item with the specified key name:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
del thisdict["model"]
print(thisdict)
Example
The del keyword can also delete the dictionary completely:
thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
del thisdict
print(thisdict) #this will cause an error because "thisdict" no longer exists.
Example
DATA ANALYTICS METAM IT SOLUTIONS

The clear() method empties the dictionary:

thisdict = {
"brand": "Ford",
"model": "Mustang",
"year": 1964
}
[Link]()
print(thisdict)

Python - Nested Dictionaries

Nested Dictionaries
A dictionary can contain dictionaries, this is called nested dictionaries.
Example
Create a dictionary that contain three dictionaries:
myfamily = {
"child1" : {
"name" : "Emil",
"year" : 2004
},
"child2" : {
"name" : "Tobias",
"year" : 2007
},
"child3" : {
"name" : "Linus",
"year" : 2011
}
}
Or, if you want to add three dictionaries into a new dictionary:
Example
Create three dictionaries, then create one dictionary that will contain the other
three dictionaries:
child1 = {
"name" : "Emil",
"year" : 2004
}
child2 = {
"name" : "Tobias",
"year" : 2007
}
child3 = {
DATA ANALYTICS METAM IT SOLUTIONS

"name" : "Linus",
"year" : 2011
}

myfamily = {
"child1" : child1,
"child2" : child2,
"child3" : child3
}

Access Items in Nested Dictionaries

To access items from a nested dictionary, you use the name of the dictionaries,
starting with the outer dictionary:
Example
Print the name of child 2:
print(myfamily["child2"]["name"])

Loop Through Nested Dictionaries

You can loop through a dictionary by using the items() method like this:
Example
Loop through the keys and values of all nested dictionaries:
for x, obj in [Link]():
print(x)

for y in obj:
print(y + ':', obj[y])

Python Dictionary Methods

Dictionary Methods
Python has a set of built-in methods that you can use on dictionaries.
Method Description

clear() Removes all the elements from the dictionary

copy() Returns a copy of the dictionary

fromkeys() Returns a dictionary with the specified keys and value

get() Returns the value of the specified key

items() Returns a list containing a tuple for each key value pair
DATA ANALYTICS METAM IT SOLUTIONS

keys() Returns a list containing the dictionary's keys

pop() Removes the element with the specified key

popitem() Removes the last inserted key-value pair

setdefault() Returns the value of the specified key. If the key does not exist: insert the ke

update() Updates the dictionary with the specified key-value pairs

values() Returns a list of all the values in the dictionary

🔹 Control Flow in Python

Control flow means the order in which individual instructions are executed in a
program. By default, Python executes statements sequentially from top to bottom, but with
control flow statements we can change this order based on conditions or repetitions.

1. Conditional Statements (if / else)

Conditional statements allow the program to make decisions.
 if statement → Executes a block of code when a condition is True.

 if–else statement → Provides an alternate block of code when the condition is False.
 if–elif–else statement → Used when multiple conditions are checked one after
another.
👉 Example use cases:
 Checking eligibility (age, marks, login password).

 Deciding which discount to apply in a shopping cart.

2. Loops
Loops are used to repeat a block of code multiple times without writing it again and again.
Types of Loops in Python:
1. for loop
o Used when the number of iterations is known.

o Commonly used with range() or collections like lists, tuples, dictionaries.

2. while loop
DATA ANALYTICS METAM IT SOLUTIONS

o Used when the number of iterations is not fixed.

o Keeps executing as long as the condition is True.

3. Loop Control Statements

Sometimes we need to control the flow inside loops:
 break → exits the loop immediately.

 continue → skips the current iteration and moves to the next.

 pass → does nothing (acts as a placeholder).
4. Importance of Control Flow
 Makes programs dynamic instead of fixed.

 Helps in decision making (if/else).

 Automates repetitive tasks (loops).
 Forms the foundation of problem-solving in programming.
✅ In short:
 if/else → “Should I do this or that?”

 loops → “Do this repeatedly until the condition is met.”

🔹 1. If / Else Statements
Used when you want to make decisions in your code.
Example:
age = 18

if age >= 18:

print("You are eligible to vote.")
else:
print("You are not eligible to vote.")
✅ Output → You are eligible to vote.

If-Elif-Else
marks = 75

if marks >= 90:

print("Grade: A")
elif marks >= 75:
print("Grade: B")
elif marks >= 50:
DATA ANALYTICS METAM IT SOLUTIONS

print("Grade: C")
else:
print("Fail")

🔹 2. Loops
Loops are used for repeating tasks.
For Loop
# Print numbers 1 to 5
for i in range(1, 6):
print(i)
✅ Output → 1 2 3 4 5

While Loop
# Print numbers 1 to 5
num = 1
while num <= 5:
print(num)
num += 1

🔹 3. Combining If/Else with Loops

# Print only even numbers from 1 to 10
for i in range(1, 11):
if i % 2 == 0:
print(i, "is even")
else:
print(i, "is odd")

🔹 4. Loop Control Statements

 break → exits the loop early

 continue → skips current iteration

 pass → does nothing (placeholder)
Example:
for i in range(1, 6):
if i == 3:
continue # skip 3
if i == 5:
break # stop loop at 5
print(i)
✅ Output → 1 2 4

🔹 1. Fibonacci Series
📘 Program:
DATA ANALYTICS METAM IT SOLUTIONS

# Fibonacci Series up to n terms

n = int(input("Enter number of terms: "))

a, b = 0, 1
print("Fibonacci Series:")

for i in range(n):
print(a, end=" ")
a, b = b, a + b
💡 Explanation:
 Start with first two terms a = 0, b = 1.

 Next term = sum of previous two → a + b.

 Update values in each loop.
 Example:
For n = 6 → Output: 0 1 1 2 3 5

🔹 2. Armstrong Number
📘 Program:
# Check Armstrong Number
num = int(input("Enter a number: "))
sum = 0
temp = num
while temp > 0:
digit = temp % 10
sum += digit ** 3 # cube of each digit
temp //= 10

if num == sum:
print(num, "is an Armstrong number")
else:
print(num, "is not an Armstrong number")
💡 Explanation:
 An Armstrong number = sum of the cubes of its digits equals the number itself.

 Example: 153 = 1³ + 5³ + 3³ = 153 ✅

🔹 3. Palindrome Number
📘 Program:
# Check Palindrome Number

num = int(input("Enter a number: "))

DATA ANALYTICS METAM IT SOLUTIONS

rev = 0
temp = num

while temp > 0:

digit = temp % 10
rev = rev * 10 + digit
temp //= 10

if num == rev:
print(num, "is a Palindrome")
else:
print(num, "is not a Palindrome")
💡 Explanation:
 A palindrome number reads the same forward and backward.

 Example: 121 → reversed also 121 ✅

📘 Functions, Modules, and Error Handling

1. Functions
 A function is a block of code that performs a specific task.

 It helps in reusability, modularity, and better organization of code.

 Defined using the keyword def.
Characteristics:
 Can take parameters (inputs).

 Can return a value using return.

 Can be reused multiple times.
Syntax:
def function_name(parameters):
# function body
return value
#calling function

2. Modules
 A module is a Python file (.py) containing functions, classes, or variables.

 Python has many built-in modules (like math, random, datetime).

 We can also create user-defined modules.
Types of Modules:
1. Built-in Modules → Already available in Python (math, os, sys, etc.)
DATA ANALYTICS METAM IT SOLUTIONS

2. User-defined Modules → Created by the programmer.

3. Third-party Modules → Installed using pip (like numpy, pandas).
Importing Modules:
 import module_name → imports the whole module.

 from module_name import function → imports only specific function.

 import module_name as alias → gives a short name.

3. Error Handling (Exceptions)

 Errors in Python are called Exceptions.

 If not handled, they stop the program execution.

 Common exceptions: ZeroDivisionError, ValueError, IndexError, FileNotFoundError.
Exception Handling:
 Done using try-except block.

 try → code that may cause error.

 except → handles the error.
 finally → always executes (cleanup code).
 raise → manually trigger an exception.
Syntax:
try:
# risky code
except ErrorType:
# handling code
finally:
# always executes

✅ Summary
 Functions: Reusable blocks of code (def, return).

 Modules: Collections of functions/classes (built-in or user-defined).

 Error Handling: Prevents program crash, ensures smooth execution (try-except-
finally).

Functions
# Defining a function
def greet(name):
return f"Hello, {name}!"
DATA ANALYTICS METAM IT SOLUTIONS

# Calling the function

print(greet("Maina"))
print(greet("Nikhil"))

📘 Python Function Programs

1. Function to add two numbers
def add_numbers(a, b):
return a + b

print("Sum:", add_numbers(5, 7))

2. Function to check if a number is even or odd

def check_even_odd(num):
if num % 2 == 0:
return "Even"
else:
return "Odd"
print(check_even_odd(10))
print(check_even_odd(15))

3. Function to find factorial of a number

def factorial(n):
fact = 1
for i in range(1, n + 1):
fact *= i
return fact

print("Factorial:", factorial(5))

4. Function to check if a string is palindrome

def is_palindrome(s):
return s == s[::-1]

print(is_palindrome("madam")) # True
print(is_palindrome("hello")) # False

5. Function to return maximum of three numbers

def maximum(a, b, c):
return max(a, b, c)

print("Maximum:", maximum(10, 25, 17))

DATA ANALYTICS METAM IT SOLUTIONS

6. Function to calculate simple interest

def simple_interest(p, r, t):
return (p * r * t) / 100

print("Simple Interest:", simple_interest(1000, 5, 2))

7. Function to generate Fibonacci series (n terms)

def fibonacci(n):
a, b = 0, 1
for _ in range(n):
print(a, end=" ")
a, b = b, a + b

fibonacci(10)

8. Function with default arguments

def greet(name="Guest"):
print("Hello,", name)

greet("Maina")
greet() # uses default

📘 Function Programs with Error Handling

1. Safe Division Function
def safe_divide(a, b):
try:
return a / b
except ZeroDivisionError:
return "Error: Cannot divide by zero"

print(safe_divide(10, 2)) # 5.0

print(safe_divide(10, 0)) # Error

2. Function to Convert String to Integer

def to_integer(s):
try:
return int(s)
except ValueError:
return "Error: Invalid number"

print(to_integer("123")) # 123
print(to_integer("abc")) # Error
DATA ANALYTICS METAM IT SOLUTIONS

3. Function to Open and Read a File

def read_file(filename):
try:
with open(filename, "r") as f:
return [Link]()
except FileNotFoundError:
return "Error: File not found"

print(read_file("[Link]")) # If file exists, prints content

4. Function to Find Square Root

import math

def safe_sqrt(num):
try:
if num < 0:
raise ValueError("Square root of negative number not allowed")
return [Link](num)
except ValueError as e:
return f"Error: {e}"

print(safe_sqrt(25)) # 5.0
print(safe_sqrt(-9)) # Error

5. Function with Multiple Exceptions

def calculate_average(numbers):
try:
return sum(numbers) / len(numbers)
except ZeroDivisionError:
return "Error: List is empty"
except TypeError:
return "Error: List must contain only numbers"

print(calculate_average([10, 20, 30])) # 20.0

print(calculate_average([])) # Error
print(calculate_average([10, "a", 30])) # Error
DATA ANALYTICS METAM IT SOLUTIONS

Module 1: Introduction to Business Intelligence and Power BI

1. Overview of Business Intelligence (BI)
 Business Intelligence (BI) refers to the process of collecting, analyzing, and
transforming raw data into meaningful information for business decision-making.
 It helps organizations make data-driven decisions by using data visualization,
reporting, and analytics tools.
 Key objectives of BI:
o Improve business performance
o Identify trends and insights
o Support better decision-making
 Examples of BI Tools: Power BI, Tableau, QlikView, SAP BO, etc.

2. Introduction to Power BI
 Power BI is a Business Analytics tool by Microsoft that enables users to connect to
multiple data sources, transform the data, and create interactive reports and
dashboards.
 It helps turn raw data into actionable insights through easy-to-understand visuals.
 It is widely used by businesses for data visualization, reporting, and real-time
analysis.
 Developed by: Microsoft
 Launched in: 2015

3. Components of Power BI
Power BI is made up of several components that work together to manage, analyze, and share
data:
Component Description
Power BI Desktop A free Windows application used to create reports and data models.
Power BI Service An online SaaS platform to share, publish, and collaborate on
(Cloud) reports.
Mobile apps available for Android, iOS, and Windows for viewing
Power BI Mobile
dashboards on the go.
Connects on-premises data sources to Power BI Service for
Power BI Gateway
scheduled refresh.
Power BI Report Used to host Power BI reports on-premises (for organizations not
Server using cloud).

4. Power BI Architecture
The Power BI architecture includes three major phases:
1. Data Integration:
Power BI connects to various data sources (databases, web, Excel, etc.) and collects
data.
DATA ANALYTICS METAM IT SOLUTIONS

2. Data Processing:
Data is cleaned, transformed, and modeled using Power Query and DAX.
3. Data Presentation:
Processed data is visualized using dashboards and reports for analysis.
Architecture Flow:
Data Sources → Power BI Desktop → Power BI Service → Power BI Mobile

5. Installation and Setup of Power BI Desktop

Steps:
1. Visit the official Microsoft Power BI website or Microsoft Store.
2. Download Power BI Desktop.
3. Install it on your system (Windows 10 or later).
4. Open the application and sign in using a Microsoft account.
5. Start creating reports by connecting to data sources.

6. Understanding Power BI Interface

The Power BI Desktop interface has three main views:
1. Report View – Used to create and design reports using visuals.
2. Data View – Displays the data in a tabular form (after transformation).
3. Model View – Shows relationships between different tables.
Other Key Elements:
 Ribbon: Contains tools and commands.
 Fields Pane: Lists all datasets and fields.
 Visualizations Pane: Contains available chart types.
 Canvas Area: The workspace where you design your report.

Module 2: Getting Data in Power BI

1. Connecting to Various Data Sources
Power BI allows users to connect to a wide range of data sources to import and analyze data.
These data sources can be files, databases, cloud services, or web sources.
Common Data Sources:
 Files: Excel, CSV, XML, JSON, PDF
 Databases: SQL Server, MySQL, Oracle, PostgreSQL
 Online Services: SharePoint, Google Analytics, Azure, Salesforce
 Web: Importing data directly from websites or web APIs
Steps to connect:
1. Open Power BI Desktop
2. Click Home → Get Data
3. Choose your data source (e.g., Excel or SQL)
4. Select the file or server
5. Load or transform the data

2. Importing vs. DirectQuery

DATA ANALYTICS METAM IT SOLUTIONS

Feature Import Mode DirectQuery Mode

Imports a copy of the data Connects directly to the data source without
Definition
into Power BI. storing data in Power BI.
Faster, as data is stored Slower, since each query runs on the data
Performance
locally. source.
Needs manual or scheduled
Data Refresh Always shows real-time data.
refresh.
Use Case Small or medium datasets. Large, frequently changing datasets.
Example:
 Use Import Mode for static Excel data.
 Use DirectQuery for live SQL databases.

3. Power BI Data Types

Power BI supports multiple data types to ensure accurate analysis and calculations.
Common Data Types:
 Whole Number – For integer values
 Decimal Number – For fractional numbers
 Date/Time – For date and time values
 Text – For words or strings
 Boolean (True/False) – For logical values
 Currency – For financial data
 Binary – For files or images
Tip: Choosing the right data type is important for correct filtering, sorting, and calculations.

4. Using Power Query Editor

Power Query Editor is a tool within Power BI used for data cleaning, shaping, and
transformation before loading data into reports.
To open Power Query Editor:
 Click on Transform Data in Power BI Desktop.
Main Sections of Power Query Editor:
 Queries Pane: Lists all loaded datasets.
 Data Preview: Displays data after each transformation.
 Applied Steps Pane: Shows all applied data cleaning steps.
Key Features:
 Remove or rename columns
 Merge and append tables
 Filter rows
 Replace or fill missing values
 Change data types

5. Data Cleaning and Transformation

Before analysis, data often needs to be cleaned to remove errors and inconsistencies.
Common Data Cleaning Tasks:
 Removing duplicates
DATA ANALYTICS METAM IT SOLUTIONS

 Handling missing or null values

 Changing text case (uppercase/lowercase)
 Splitting or merging columns
 Removing unwanted rows or columns
 Renaming fields for clarity
Data Transformation Tasks:
 Creating calculated columns
 Aggregating data (sum, average, count)
 Grouping and sorting data
 Pivoting and unpivoting tables
Goal:
To ensure that the dataset is accurate, consistent, and ready for visualization.

✅ Summary
 Power BI supports multiple data sources and connection modes.
 Power Query Editor is essential for cleaning and shaping data.
 Clean and well-structured data ensures accurate reporting and analysis.

Module 3: Data Modeling

1. Understanding Relationships in Power BI
 In Power BI, a relationship defines how data in one table is connected to data in
another.
 Relationships are based on common fields (keys) between tables, such as Customer
ID, Product ID, etc.
 Establishing relationships allows users to analyze data from multiple tables
together.
Example:
 A Sales table may have a CustomerID column linked to a Customers table containing
details of each customer.
Types of Relationships:
1. One-to-One (1:1): Each record in one table matches exactly one record in another.
2. One-to-Many (1:*): One record in one table relates to multiple records in another
(most common).
3. Many-to-Many (:): Multiple records in one table relate to multiple in another
(handled using bridge tables).

2. Creating and Managing Relationships Between Tables

Steps to Create a Relationship:
1. Go to Model View in Power BI Desktop.
2. Drag a field (key) from one table to the corresponding field in another.
3. Set the cardinality (relationship type) and cross-filter direction.
4. Click OK to apply.
DATA ANALYTICS METAM IT SOLUTIONS

Managing Relationships:
 Use Manage Relationships under the Modeling tab to edit or delete existing
relationships.
 You can also enable or disable auto-detect relationships in Power BI settings.

3. Star and Snowflake Schema Concepts

Star Schema
 The Star Schema is the simplest data modeling design used in Power BI.
 It consists of:
o Fact Tables → Contain numerical or measurable data (e.g., Sales, Revenue).
o Dimension Tables → Contain descriptive data (e.g., Products, Customers,
Dates).
 Fact tables connect to dimension tables through keys, forming a star shape.
Advantages:
 Simple and easy to understand
 Fast query performance
 Ideal for data visualization

Snowflake Schema
 A Snowflake Schema is a more complex version of the Star Schema.
 In this design, dimension tables are normalized — meaning they are split into
multiple related tables.
Example:
A Product table may link to a Category table, which links to a Department table.
Advantages:
 Reduces data redundancy
 More efficient data storage
Disadvantages:
 More complex relationships
 Slightly slower performance

4. Data Modeling Best Practices

 Use Star Schema wherever possible for performance and simplicity.
 Avoid circular or ambiguous relationships.
 Name tables and fields clearly and consistently.
 Reduce the number of columns and unnecessary tables.
 Create proper relationships before applying DAX calculations.
 Hide keys or unused columns from report view for cleaner design.

5. Introduction to DAX (Data Analysis Expressions)

 DAX is a formula and query language used in Power BI for creating calculations,
measures, and custom columns.
 It is similar to Excel formulas but designed for data models and relationships.
Key Uses of DAX:
DATA ANALYTICS METAM IT SOLUTIONS

 Perform calculations on data (e.g., total sales, averages).

 Create new columns or measures.
 Build time-based calculations like year-to-date or month-over-month growth.
Example DAX Formulas:
 Total Sales = SUM(Sales[Amount])
 Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])

6. Calculated Columns and Measures

Feature Calculated Column Measure
A new column added to a table using a A calculation performed on data, often
Definition
DAX formula. summarized.
Calculated only when used in visuals
Storage Stored in the data model.
(not stored).
Usage Used for row-level calculations. Used for aggregated or summarized data.
Example Profit = Sales[Revenue] - Sales[Cost] Total Sales = SUM(Sales[Revenue])
Tip:
Use Measures whenever possible — they use less memory and improve performance.

✅ Summary
 Data modeling links tables through relationships to create a logical structure.
 Star Schema is preferred for most Power BI models.
 DAX enables advanced calculations and analytics.
 Proper modeling ensures accuracy, performance, and easier report building.

Module 4: DAX (Data Analysis Expressions)

1. Introduction to DAX Syntax and Functions
 DAX (Data Analysis Expressions) is a formula language used in Power BI, Power
Pivot, and Analysis Services to perform calculations and data analysis.
 It allows users to create custom calculations, aggregations, and logic-based
expressions on data models.
 DAX works on columns, tables, and relationships in your data model.
Key Features of DAX:
 Similar to Excel formulas but more powerful for relational data.
 Works with both row-level and aggregate-level calculations.
 Used in creating Calculated Columns, Measures, and Tables.
Basic DAX Syntax:
MeasureName = FunctionName(TableName[ColumnName])
Example:
Total Sales = SUM(Sales[SalesAmount])

2. Basic DAX Functions

DAX includes a wide variety of functions to perform mathematical and statistical operations.
DATA ANALYTICS METAM IT SOLUTIONS

Function Description Example

SUM() Adds all numeric values in a column. TotalSales = SUM(Sales[Amount])
Calculates the average (mean) of a AvgSales =
AVERAGE()
column. AVERAGE(Sales[Amount])
Counts the number of non-blank rows OrderCount =
COUNT()
in a column. COUNT(Sales[OrderID])
MIN() Returns the smallest value in a column. MinPrice = MIN(Products[Price])
MAX() Returns the largest value in a column. MaxPrice = MAX(Products[Price])

3. Logical, Text, and Date Functions

a) Logical Functions
Used for decision-making or conditional operations.
Function Description Example
Checks a condition and returns ProfitStatus = IF(Sales[Profit] > 0, "Profit",
IF()
values based on TRUE/FALSE. "Loss")
AND() / Combine multiple logical IF(AND(Sales[Revenue]>1000,
OR() conditions. Sales[Profit]>200), "High", "Low")
Evaluates an expression against SWITCH(MONTH(Sales[Date]), 1, "Jan", 2,
SWITCH()
multiple values. "Feb", "Other")

b) Text Functions
Used to manipulate or format text strings.
Function Description Example
FullName =
Joins two text
CONCATENATE() CONCATENATE(Customer[FirstName],
strings.
Customer[LastName])
Extracts characters
LEFT() / RIGHT() from left or right of LEFT(Product[Code], 3)
a text.
Converts text to
UPPER() /
uppercase or UPPER(Customer[City])
LOWER()
lowercase.

c) Date Functions
Used for working with date and time data.
Function Description Example
Returns the current
TODAY() TodayDate = TODAY()
date.
YEAR(),
Extracts parts of a date. Year = YEAR(Sales[OrderDate])
MONTH(), DAY()
Calculates difference DaysBetween = DATEDIFF(Sales[OrderDate],
DATEDIFF()
between two dates. Sales[ShipDate], DAY)
DATA ANALYTICS METAM IT SOLUTIONS

4. Time Intelligence Functions

Time intelligence functions are special DAX functions that help you perform calculations
over dates and time periods — useful for trend, growth, and comparison analysis.
Common Time Intelligence Functions:
Function Description Example
Calculates SalesYTD =
TOTALYTD() Year-to-Date TOTALYTD(SUM(Sales[Amount]),
totals. 'Date'[Date])
Returns data
SalesLastYear =
for the same
SAMEPERIODLASTYEAR() CALCULATE(SUM(Sales[Amount]),
period in the
SAMEPERIODLASTYEAR('Date'[Date]))
previous year.
Shifts dates
by a given
number of SalesPrevMonth =
DATEADD() intervals CALCULATE(SUM(Sales[Amount]),
(days, DATEADD('Date'[Date], -1, MONTH))
months,
years).
Returns
parallel PrevQuarter =
period (e.g., CALCULATE(SUM(Sales[Amount]),
PARALLELPERIOD()
previous PARALLELPERIOD('Date'[Date], -1,
quarter or QUARTER))
next year).

✅ Summary
 DAX is the core calculation language in Power BI.
 It includes functions for mathematical, logical, text, and date operations.
 Time intelligence functions help analyze trends over time.
 Understanding DAX is essential for advanced reporting and analytics in Power BI.

Module 5: Data Visualization

1. Introduction to Visualizations in Power BI
 Data visualization is the graphical representation of information and data.
DATA ANALYTICS METAM IT SOLUTIONS

 In Power BI, visualizations help users understand trends, patterns, and insights
quickly.
 It converts complex data into simple, interactive visuals such as charts, graphs,
maps, and tables.
Key Benefits of Visualization:
 Simplifies data analysis
 Makes reports interactive and engaging
 Helps in better business decision-making

2. Working with Different Visuals

Power BI provides a wide range of built-in visuals that can be customized as per
requirements.
Commonly Used Visuals:
Type of Visual Description / Use Case
Bar Chart Compare values across categories (e.g., sales by product).
Column Chart Similar to bar chart but vertical.
Line Chart Display trends over time (e.g., monthly revenue).
Pie / Donut Chart Show proportion or percentage contribution.
Table / Matrix Show detailed tabular data with rows and columns.
Card Visual Display single values such as totals or KPIs.
Gauge Show performance progress against a target.
Map / Filled Map Visualize geographical data by region or country.
Slicer Add filters to reports for interactivity.
Tree Map Show hierarchical data using nested rectangles.
Tip: Use the Visualizations Pane in Power BI Desktop to insert and customize visuals
easily.

3. Formatting Visuals and Reports

Formatting makes visuals clear, professional, and easy to read.
Common Formatting Options:
 Titles and Labels: Add descriptive titles and axis labels.
 Colors and Themes: Apply consistent colors to maintain readability.
 Legends: Use legends to explain chart values.
 Tooltips: Display additional details when hovering over data points.
 Data Labels: Show exact values directly on visuals.
 Backgrounds and Borders: Improve report appearance using soft colors and
spacing.
Tip: Use the Format Pane (paint roller icon) to customize each visual’s look and feel.

4. Custom Visuals from Marketplace

 Power BI allows importing custom visuals from the Microsoft AppSource
(Marketplace).
 These visuals are created by Microsoft and third-party developers for specific data
DATA ANALYTICS METAM IT SOLUTIONS

needs.
Examples of Custom Visuals:
 Bullet Chart – Compare performance against goals.
 Histogram – Display frequency distribution.
 Word Cloud – Visualize most frequently used words.
 Gantt Chart – Project timeline visualization.
Steps to Add Custom Visuals:
1. Go to Visualizations Pane → Get more visuals
2. Browse or search the required visual.
3. Click Add to import it into your report.

5. Hierarchies and Drill-Through Reports

Hierarchies
 A hierarchy allows data to be organized from higher to lower levels — such as Year
→ Quarter → Month → Day.
 It helps users drill down to explore data in detail.
Example:
Sales → Region → State → City
Drill-Through
 The Drill-through feature enables users to right-click on a data point and navigate to
a detailed report page related to that specific data.
Use Case:
Right-clicking on “South Region” in a sales chart can open a detailed page showing only that
region’s sales data.

6. Report Layout and Design Tips

To make your Power BI reports attractive and easy to read:
 Maintain consistent color themes.
 Use clear fonts and labels.
 Avoid overcrowding visuals on a single page.
 Group similar visuals together logically.
 Highlight key insights with cards or KPIs.
 Add your company logo or title banner for a professional look.

✅ Summary
 Data visualization transforms raw data into meaningful insights.
 Power BI provides a variety of visuals for effective storytelling.
 Formatting, custom visuals, and drill-through features enhance interactivity and
design.
 Well-designed visuals improve understanding and help in better decision-making.

🐍 Python Libraries: NumPy, Pandas, and Matplotlib

DATA ANALYTICS METAM IT SOLUTIONS

1. NumPy (Numerical Python)

Introduction
 NumPy stands for Numerical Python.
 It is a powerful library for numerical computation, scientific computing, and array
manipulation.
 It provides support for multidimensional arrays (ndarray) and mathematical
functions that operate efficiently on these arrays.

Key Features
 Provides the ndarray object for fast operations.
 Supports vectorized operations (no need for explicit loops).
 Includes many built-in mathematical, statistical, and logical functions.
 Supports linear algebra, Fourier transform, and random number generation.

Example: NumPy Operations

import numpy as np

# Create NumPy arrays

a = [Link]([1, 2, 3, 4, 5])
b = [Link]([10, 20, 30, 40, 50])

# Array operations
print("Addition:", a + b)
print("Multiplication:", a * b)
print("Mean:", [Link](a))
print("Square Root:", [Link](a))
Output:
Addition: [11 22 33 44 55]
Multiplication: [10 40 90 160 250]
Mean: 3.0
Square Root: [1. 1.41421356 1.73205081 2. 2.23606798]

Array Slicing Example

arr = [Link]([10, 20, 30, 40, 50])
print(arr[1:4]) # Output: [20 30 40]

Matrix Example
matrix = [Link]([[1, 2], [3, 4]])
print("Matrix:\n", matrix)
print("Transpose:\n", matrix.T)
print("Matrix Sum:", [Link](matrix))

2. Pandas (Panel Data System)

Introduction
DATA ANALYTICS METAM IT SOLUTIONS

 Pandas is an open-source Python library used for data manipulation and analysis.
 It provides two main data structures:
o Series – One-dimensional (like a column in Excel)
o DataFrame – Two-dimensional (like a table)

Key Features
 Handles missing data easily.
 Supports data filtering, grouping, merging, and reshaping.
 Easily reads/writes data from Excel, CSV, JSON, SQL, etc.
 Built on top of NumPy.

Example: Creating Series and DataFrame

import pandas as pd

# Creating a Series
s = [Link]([10, 20, 30, 40])
print("Series:\n", s)

# Creating a DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 28],
'City': ['Delhi', 'Mumbai', 'Chennai']
}
df = [Link](data)
print("\nDataFrame:\n", df)

Example: Reading and Analyzing Data

# Reading data from CSV file
data = pd.read_csv('[Link]')

# Display first few rows

print([Link]())

# Basic data information

print([Link]())
print([Link]())

Example: Filtering and Sorting

# Filter rows where Age > 25
print(df[df['Age'] > 25])

# Sort data by Age

print(df.sort_values(by='Age'))
DATA ANALYTICS METAM IT SOLUTIONS

Example: Adding a New Column

df['Salary'] = [50000, 60000, 55000]
print(df)

3. Matplotlib
Introduction
 Matplotlib is a Python library used for data visualization.
 It helps create graphs, charts, and plots to visualize patterns and trends.
 The main module used is pyplot.

Key Features
 Supports a wide range of plots: line, bar, pie, scatter, histogram, etc.
 Highly customizable (colors, labels, titles, legends).
 Integrates well with NumPy and Pandas data.

Example 1: Line Chart

import [Link] as plt

x = [1, 2, 3, 4, 5]
y = [10, 20, 25, 30, 35]

[Link](x, y, color='blue', marker='o')

[Link]('Simple Line Chart')
[Link]('X-axis')
[Link]('Y-axis')
[Link]()

Example 2: Bar Chart

cities = ['Delhi', 'Mumbai', 'Chennai', 'Bangalore']
population = [20, 18, 10, 12]

[Link](cities, population, color='green')

[Link]('City Population')
[Link]('City')
[Link]('Population (in millions)')
[Link]()

Example 3: Pie Chart

labels = ['Python', 'Java', 'C++', 'JavaScript']
sizes = [40, 25, 20, 15]

[Link](sizes, labels=labels, autopct='%1.1f%%', startangle=90)

[Link]('Programming Language Popularity')
DATA ANALYTICS METAM IT SOLUTIONS

[Link]()

Example 4: Scatter Plot

x = [5, 7, 8, 9, 10]
y = [10, 15, 13, 17, 19]

[Link](x, y, color='red')
[Link]('Scatter Plot Example')
[Link]('X-axis')
[Link]('Y-axis')
[Link]()

✅ Summary
Library Purpose Main Use
NumPy Numerical Computation Fast array and matrix operations
Pandas Data Analysis Data cleaning, manipulation, and analysis
Matplotlib Data Visualization Creating charts and plots