Skip to main content

Cleaning Data in PostgreSQL Databases

Learn to tame your raw, messy data stored in a PostgreSQL database to extract accurate insights.

Start Course for Free
4 Hours15 Videos49 Exercises5,032 Learners

Create Your Free Account

GoogleLinkedInFacebook

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

Loved by learners at thousands of companies

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?
  1. 1

    Data Cleaning Basics

    Free

    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.

    Play Chapter Now
    Introduction to data cleaning
    50 xp
    Developing a data cleaning mindset
    50 xp
    Applying functions for string cleaning
    100 xp
    Pattern matching
    50 xp
    Classifying parking violations by time of day
    100 xp
    Masking identifying information with regular expressions
    100 xp
    Matching similar strings
    50 xp
    Matching inconsistent color names
    100 xp
    Standardizing color names
    100 xp
    Standardizing multiple colors
    100 xp
    Formatting text for colleagues
    100 xp
  2. 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.

    Play Chapter Now
    Handling missing data
    50 xp
    Quantifying completeness
    50 xp
    Using a fill-in value
    100 xp
    Analyzing incomplete records
    100 xp
    Handling duplicated data
    50 xp
    Duplicate parking violations
    100 xp
    Resolving impartial duplicates
    100 xp
    Detecting invalid values
    50 xp
    Detecting invalid values with regular expressions
    100 xp
    Identifying out-of-range vehicle model years
    100 xp
    Detecting inconsistent data
    50 xp
    Identifying invalid parking violations
    100 xp
    Invalid violations with overnight parking restrictions
    100 xp
    Recovering deleted data
    100 xp
  3. 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.

    Play Chapter Now
    Data type conversions
    50 xp
    Type conversion with a CASE clause
    100 xp
    Applying aggregate functions to converted values
    100 xp
    Date parsing and formatting
    50 xp
    Cleaning invalid dates
    100 xp
    Converting and displaying dates
    100 xp
    Timestamp parsing and formatting
    50 xp
    Extracting hours from a time value
    100 xp
    A parking violation report by day of the month
    100 xp
    Risky parking behavior
    100 xp
  4. 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().

    Play Chapter Now
    Combining columns
    50 xp
    Tallying corner parking violations
    100 xp
    Creating a TIMESTAMP with concatenation
    100 xp
    Splitting column data
    50 xp
    Extracting time units with SUBSTRING()
    100 xp
    Extracting house numbers from a string
    100 xp
    Splitting data with delimiters
    50 xp
    Splitting house numbers with a delimiter
    100 xp
    Mapping parking restrictions
    100 xp
    Creating pivot tables
    50 xp
    Selecting data for a pivot table
    100 xp
    Using FILTER to create a pivot table
    100 xp
    Aggregating film categories
    100 xp
    Course wrap-up
    50 xp

Datasets

Parking violations in NYCRestaurant inspections in NYCFilm permits in NYC

Collaborators

Amy Peterson
Maggie Matsui

Prerequisites

Data Manipulation in SQL
Darryl Reeves Ph.D HeadshotDarryl Reeves Ph.D

Industry Assistant Professor, NYU Tandon School of Engineering

Darryl is a computational scientist with expertise in utilizing data-driven approaches to solve complex problems in both academic and business settings. He worked for a number of years in a variety of technical roles including software development and technology-based client services mostly within start-up organizations in the finance and online advertising industries. He has a love for technology and education and enjoys solving interesting problems across diverse domains.
See More

What do other learners have to say?

    Join over 11 million learners and start Cleaning Data in PostgreSQL Databases today!

    Create Your Free Account

    GoogleLinkedInFacebook

    or

    By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.