04/03/2025

Outline
● Tabular Data Processing
● Pivot Table

Data Pra Proses II

Week 5

Fundamentals of Data Analytics Fundamentals of Data Analytics 2

1 2

Why Databases? Persistence & Ease of Access

•Relational databases have been the primary way of persisting and
organizing data since the early days of computing [ref: Edgar Codd 1923
– 2003]

Tabular Data Processing •Advantages

1. SQL is a very expressive language
2. Concurrency is a first-class citizen
3. Atomicity, consistency, isolation and durability (ACID) compliance
4. Can be very fast

•Disadvantages:
1. They take some learning
2. Need to have pre-defined schemas.

Fundamentals of Data Analytics Fundamentals of Data Analytics 4

3 4
 04/03/2025

Database Terminology Terminology

● Field: the “smallest” item of stored data, akin to a “cell” in a spreadsheet
● Record, or Row: group of related fields and associated values
● Columns: the fields of all records
● Primary Key: a column where the field is a unique identifier for a row
● Table: collection of records that are frequently categorized for given
purpose
● Database: a collection of related tables, and other things like indexes, stored
procedures, triggers
● Schema: detailed specification of a database
● Database Management System (DBMS): a system providing control
over definition, access and manipulation of the information stored in the
database

Fundamentals of Data Analytics 5 Fundamentals of Data Analytics 6

5 6

Example of Database Relationships Among Table

Fundamentals of Data Analytics 7 Fundamentals of Data Analytics 8

7 8
 04/03/2025

Sorting, Filtering and Freezing Pane

Pivot Table

Fundamentals of Data Analytics 9 Fundamentals of Data Analytics

9 10

What is a Pivot Table?

A special kind of table that summarizes information
from selected fields of a data source.
○ Is linked to the data from which its derived
○ Data can be from an Excel sheet, or external such as from a
database file
○ Access tables can be easily imported to Excel to create pivot tables

Fundamentals of Data Analytics 11 Fundamentals of Data Analytics 12

11 12
 04/03/2025

Why Pivot Table Pivot Table Set up

● Summarizing data, i.e. finding average sales for each ● To create a pivot table, specify:
region for each product ○ Which fields you’re interested in
How you want the table organized
● Filtering, sorting, summarizing data without writing ○

○ What kinds of calculations you want to perform

any formulas
● You can:
● Transposing data ○ Rearrange it to view from alternative perspectives
● Linking data sources ○ “pivot” the dimensions – i.e. transpose column headings to row positions

Fundamentals of Data Analytics 13 Fundamentals of Data Analytics 14

13 14

● Step 1: Select the data range from which you want to make ● Step 4: Make your first Pivot Table
the pivot table. ○ The PivotTable Field list is divided

● Step 2: Go to Insert tab and click on new Pivot table option into sections.
○ select PivotTable or PivotChart option. ○ You can drag and drop the fields

you want in each area.

● Step 3: ○ The body of the table will contain
Excel will display a Pivot table wizard where you can specify three parts: Rows, Columns and
the pivot table target location etc. Select “New worksheet” Cells. You can use any fields in
option and your pivot table will be placed in new worksheet. these areas.

Fundamentals of Data Analytics 15 Fundamentals of Data Analytics 16

15 16
 04/03/2025

Fundamentals of Data Analytics 17 Fundamentals of Data Analytics 18

17 18

Add a Filter
The data is the same, only the perspective is different

Fundamentals of Data Analytics 19 Fundamentals of Data Analytics 20

19 20
 04/03/2025

Slicers Slicers

● It is not easy to see the current filtering state when you filter ● Slicers allow us to
on multiple items
● Slicers are easy-to-use filtering components with buttons that
quickly filter the
enable you to quickly filter the data in a PivotTable, without table to show only
opening drop-down lists to find the items that you want to the North region
filter. and the RapidZoo
● In addition to quick filtering, slicers also indicate the current
filtering state, which makes it easy to understand what exactly product for all
is shown in a filtered PivotTable report. Salesmen

Fundamentals of Data Analytics 21 Fundamentals of Data Analytics 22

21 22

Calculated Fields

● In a pivot table, you can create a new field that

performs a calculation on the sum of other pivot
fields. For example, we can create a calculated field
named Bonus to calculate 3% of the Total Net Sales as
a bonus for each salesperson.

Fundamentals of Data Analytics 23 Fundamentals of Data Analytics 24

23 24
 04/03/2025

Calculate Bonus for each Salesperson References

● Introduction to Data Science Fall 2015. (n.d.). [Link]
● Grus, Joel. (2015). Data Science from Scratch. California: O’Reilly Media
● McKinney, W. (2022). Data cleaning. In Python for data analysis (2nd ed.). Retrieved from
[Link]

Fundamentals of Data Analytics 25 Fundamentals of Data Analytics 26

25 26

Thank You

Fundamentals of Data Analytics

27