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 cleaning

Developing a data cleaning mindset

Applying functions for string cleaning

Pattern matching

Classifying parking violations by time of day

Masking identifying information with regular expressions

Matching similar strings

Matching inconsistent color names

Standardizing color names

Standardizing multiple colors

Formatting text for colleagues

Data Cleaning Basics

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.

Ontbrekende gegevens verwerken

Volledigheid kwantificeren

Een invulwaarde gebruiken

Onvolledige records analyseren

Omgaan met dubbele data

Dubbele parkeerboetes

Onpartijdige duplicaten oplossen

Ongeldige waarden detecteren

Ongeldige waarden detecteren met reguliere expressies

Voertuigbouwjaren buiten het bereik identificeren

Inconsistente gegevens opsporen

Ongeldige parkeerboetes opsporen

Ongeldige overtredingen met nachtelijke parkeerbeperkingen

Verwijderde gegevens herstellen

Missing, Duplicate, and Invalid 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. 

Data type conversions

Type conversion with a CASE clause

Applying aggregate functions to converted values

Date parsing and formatting

Cleaning invalid dates

Converting and displaying dates

Timestamp parsing and formatting

Extracting hours from a time value

A parking violation report by day of the month

Risky parking behavior

Converting  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().

Combining columns

Tallying corner parking violations

Creating a TIMESTAMP with concatenation

Splitting column data

Extracting time units with SUBSTRING()

Extracting house numbers from a string

Splitting data with delimiters

Splitting house numbers with a delimiter

Mapping parking restrictions

Creating pivot tables

Selecting data for a pivot table

Using FILTER to create a pivot table

Aggregating film categories

Course wrap-up

Transforming Data

Parking violations in NYC

Restaurant inspections in NYC

Film permits in NYC

Als je veel data scientists en data-analisten zou vragen welke taken het vaakst terugkomen in hun werkdag, dan staat data opschonen bijna altijd op het lijstje. Dat komt doordat echte data rommelig is. In deze cursus leer je hoe je data die is opgeslagen in een PostgreSQL-database kunt opschonen. Je leert veelvoorkomende problemen oplossen, zoals rommelige strings schoonmaken, omgaan met lege waarden, de overeenkomst tussen strings vergelijken, en nog veel meer. Je gaat hiermee praktisch aan de slag met interessante (maar rommelige) gegevenssets uit het Open Data-programma van New York City. Ben je klaar om die rommelige data strak te trekken?

Data Manipulation in SQL

Leer ruwe, rommelige data in PostgreSQL te temmen voor nauwkeurige inzichten.

Data opschonen in PostgreSQL-databases

Leer hoe je je ruwe, rommelige data in een PostgreSQL-database kunt ordenen om nauwkeurige inzichten te krijgen.

Ongeldige waarden detecteren

Create Your Free Account