After doing these courses, I feel confident creating professional visualizations and dashboards
Shoebur RahmanProcurement Specialist, Giz Bangladesh
Course Description
If you surveyed a large number of data scientists and data analysts about which tasks are most common in their workday, cleaning data would likely be in almost all responses. This is the case because real-world data is messy. To help you tame messy data, this course teaches you how to clean data stored in a PostgreSQL database. You’ll learn how to solve common problems such as how to clean messy strings, deal with empty values, compare the similarity between strings, and much more. You’ll get hands-on practice with these tasks using interesting (but messy) datasets made available by New York City's Open Data program. Are you ready to whip that messy data into shape?
Prerequisites
Curriculum
Course outline
1
Data Cleaning Basics
In this chapter, you’ll gain an understanding of data cleaning approaches when working with PostgreSQL databases and learn the value of cleaning data as early as possible in the pipeline. You’ll also learn basic string editing approaches such as removing unnecessary spaces as well as more involved topics such as pattern matching and string similarity to identify string values in need of cleaning.
- Introduction to data cleaning50 XP
- Developing a data cleaning mindset50 XP
- Applying functions for string cleaning100 XP
- Pattern matching50 XP
- Classifying parking violations by time of day100 XP
- Masking identifying information with regular expressions100 XP
- Matching similar strings50 XP
- Matching inconsistent color names100 XP
- Standardizing color names100 XP
- Standardizing multiple colors100 XP
- Formatting text for colleagues100 XP
2
Missing, Duplicate, and Invalid Data
You’ll learn how to write queries to solve common problems of missing, duplicate, and invalid data in the context of PostgreSQL database tables. Through hands-on exercises, you’ll use the COALESCE() function, SELECT query, and WHERE clause to clean messy data.
3
Converting Data
Sometimes you need to convert data stored in a PostgreSQL database from one data type to another. In this chapter, you’ll explore the expressions you need to convert text to numeric types and how to format strings for temporal data.
4
Transforming Data
In the final chapter, you’ll learn how to transform your data and construct pivot tables. Working with real-world postal data, you’ll discover how to combine and split addresses into city, state, and zip codes using a multitude of powerful functions including CONCAT(), SUBSTRING(), and REGEXP_SPLIT_TO_TABLE().
S
Cleaning Data in PostgreSQL Databases
Course
Complete
