Spreadsheets Tutorial: Standardizing data
Skills:
Data Literacy90%
Key Takeaways
This video tutorial by DataCamp covers standardizing data in spreadsheets, including calculating z-scores and using the standardized formula to compare variables on different scales.
Full Transcript
in this lesson you'll learn how to standardize your data why do this many real-world data sets you'll encounter will often have variables that are measured on different scales for example height might be measured in feet while weight might be measured in pounds this poses a problem because variables on different scales are harder to compare and it may lead you to misinterpret the importance of a particular column that column may appear more important simply because it has larger values than another when in reality it may actually have a very similar distribution to the column with smaller values the solution to this problem is to standardize your data so that all your variables are on the same scale in statistics standardization centers of data sets distribution around the mean of the data and calculates the number of standard deviations away from the mean each point is you can standardize your data by calculating z-scores also known as standard scores z-scores are an extension of what you've already seen in this chapter to calculate the z-score of a data point subtract the mean and divide by the standard deviation as shown in this simple example here in which we have three data points first we need to calculate the mean using the average function and then the standard deviation using STD EVP let's add this information into two new columns to calculate the z-score we then need to subtract the mean from each data point and divide by the standard deviation but you probably don't want to calculate this manually as we're doing here just as with standard deviation variance mean median and other statistics you've seen so far there's a spreadsheets formula that makes it easy to calculate Z scores in the standardized formula you need to pass in the data point the mean and the standard deviation as shown here let's say we had another set of data points that are 10 times larger as you can see here while the standard deviation and mean are different ten times larger the z-scores are exactly the same despite being ten times larger the distance of each point to their respective samples mean and standard deviation are the same as in the first column and this allows you to easily compare the two columns in the exercises you'll have the opportunity to practice standardizing your data almost done with chapter one your a stats rock star
Original Description
Want to learn more? Take the full course at https://learn.datacamp.com/courses/introduction-to-statistics-in-spreadsheets at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.
---
In this lesson, you'll learn how to standardize your data.
Why do this? Many real-world datasets you'll encounter will often have variables that are measured on different scales. For example, height might be measured in feet, while weight might be measured in pounds. This poses a problem, because variables on different scales are harder to compare, and it may lead you to misinterpret the importance of a particular column - that column may appear more important simply because it has larger values than another, when in reality, it may actually have a very similar distribution to the column with smaller values.
The solution to this problem is to standardize your data so that all your variables are on the same scale. In statistics, standardization centers a dataset's distribution around the mean of the data and calculates the number of standard deviations away from the mean each point is.
You can standardize your data by calculating z-scores, also known as standard scores. Z-scores are an extension of what you've already seen in this chapter.
To calculate the z-score of a data point, subtract the mean and divide by the standard deviation, as shown on this simple example here, in which we have 3 data points.
First, we need to calculate the mean, using the AVERAGE formula, and then the standard deviation, using STDEVP.
Let's add this information into 2 new columns.
To calculate the z-score, we then need to subtract the mean from each data point, and divide by the standard deviation.
But you probably don't want to calculate this manually, as we're doing here.
Just as with the standard deviation, variance, mean, median, and other statistics you've seen so far, there's a spreadsheets formula that makes it easy to calculate z-scores.
Watch on YouTube ↗
(saves to browser)
Sign in to unlock AI tutor explanation · ⚡30
Playlist
Uploads from DataCamp · DataCamp · 0 of 60
← Previous
Next →
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SQL Server Tutorial: Date manipulation
DataCamp
R Tutorial: Intermediate Interactive Data Visualization with plotly in R
DataCamp
R Tutorial: Adding aesthetics to represent a variable
DataCamp
R Tutorial: Moving Beyond Simple Interactivity
DataCamp
Python Tutorial: Why use ML for marketing? Strategies and use cases
DataCamp
Python Tutorial: Preparation for modeling
DataCamp
Python Tutorial: Machine Learning modeling steps
DataCamp
R Tutorial: The prior model
DataCamp
R Tutorial: Data & the likelihood
DataCamp
R Tutorial: The posterior model
DataCamp
R Tutorial: An Introduction to plotly
DataCamp
R Tutorial: Plotting a single variable
DataCamp
R Tutorial: Bivariate graphics
DataCamp
Python Tutorial: Customer Segmentation in Python
DataCamp
Python Tutorial: Time cohorts
DataCamp
Python Tutorial: Calculate cohort metrics
DataCamp
Python Tutorial: Cohort analysis visualization
DataCamp
R Tutorial: Building Dashboards with flexdashboard
DataCamp
R Tutorial: Anatomy of a flexdashboard
DataCamp
R Tutorial: Layout basics
DataCamp
R Tutorial: Advanced layouts
DataCamp
Python Tutorial: Time Series Analysis in Python
DataCamp
Python Tutorial: Correlation of Two Time Series
DataCamp
Python Tutorial: Simple Linear Regressions
DataCamp
Python Tutorial: Autocorrelation
DataCamp
R Tutorial: The gapminder dataset
DataCamp
R Tutorial: The filter verb
DataCamp
R Tutorial: The arrange verb
DataCamp
R Tutorial: The mutate verb
DataCamp
R Tutorial: What is cluster analysis?
DataCamp
R Tutorial: Distance between two observations
DataCamp
R Tutorial: The importance of scale
DataCamp
R Tutorial: Measuring distance for categorical data
DataCamp
Python Tutorial: Plotting multiple graphs
DataCamp
Python Tutorial: Customizing axes
DataCamp
Python Tutorial: Legends, annotations, & styles
DataCamp
Python Tutorial: Introduction to iterators
DataCamp
Python Tutorial: Playing with iterators
DataCamp
Python Tutorial: Using iterators to load large files into memory
DataCamp
SQL Tutorial: Introduction to Relational Databases in SQL
DataCamp
SQL Tutorial: Tables: At the core of every database
DataCamp
SQL Tutorial: Update your database as the structure changes
DataCamp
Python Tutorial: Classification-Tree Learning
DataCamp
Python Tutorial: Decision-Tree for Classification
DataCamp
Python Tutorial: Decision-Tree for Regression
DataCamp
Python Tutorial: Census Subject Tables
DataCamp
Python Tutorial: Census Geography
DataCamp
Python Tutorial: Using the Census API
DataCamp
R Tutorial: A/B Testing in R
DataCamp
R Tutorial: Baseline Conversion Rates
DataCamp
R Tutorial: Designing an Experiment - Power Analysis
DataCamp
R Tutorial: Introduction to qualitative data
DataCamp
R Tutorial: Understanding your qualitative variables
DataCamp
R Tutorial: Making Better Plots
DataCamp
SQL Tutorial: OLTP and OLAP
DataCamp
SQL Tutorial: Storing data
DataCamp
SQL Tutorial: Database design
DataCamp
Python Tutorial: Introduction to spaCy
DataCamp
Python Tutorial: Statistical Models
DataCamp
Python Tutorial: Rule-based Matching
DataCamp
More on: Data Literacy
View skill →
Related AI Lessons
🎓
Tutor Explanation
DeepCamp AI