What does section 21.1, Welcome to ETL, cover?

Introduces extract-transform-load (ETL) as a way to describe getting data from files into a useful form and storing the result. Explain what extracting, transforming, and loading data mean when processing data files.

What does section 21.2, Reading text files, cover?

Introduces file reading as the extract step of ETL and explains why large files may need to be processed in smaller pieces. Explain that the extract part of ETL opens a file and reads its contents, and that a file too large to fit in memory must be handled in smaller segments, possibly one line at a time.

What does section 21.2.1, Text encoding: ASCII, Unicode, and others, cover?

This section explains why text-file encodings can cause reading errors, why UTF-8 is a useful default, and how Python can handle invalid characters. Explain why reading a text file as ASCII can fail and why a byte outside the ASCII range cannot be interpreted reliably without knowing the file’s encoding. Justify the section’s recommendation to try UTF-8 when reading text files, including what happens to files containing only ASCII characters. Describe how the `errors` parameter of Python’s `open` handles invalid characters, distinguishing the default behavior from options that discard, mark, or preserve problem data.

What does section 21.2.2, Unstructured text, cover?

This section shows how to choose useful units in unstructured text and normalize words before counting them. Explain why processing unstructured text begins by choosing a logical unit, such as a line or paragraph, and matching the way the file is divided to that choice. Describe how the example splits paragraphs at blank lines and why it normalizes case and punctuation before counting words.

What does section 21.2.3, Delimited flat files, cover?

This subsection explains how delimiters give text files fields that Python can split into values, while leaving type conversion to a later step. Explain how putting related values on one line and separating them with a delimiter makes individual fields easier to identify. Describe the pipe-delimited temperature example and recognize commas and tabs as other common delimiters. Describe how to use a known delimiter with the string split() method to turn each line into a list of fields, and explain why the resulting values may need conversion before further processing.

What does section 21.2.4, The csv module, cover?

This section shows why Python’s csv module is preferable to hand-written splitting when reading delimited files, especially files with quoted fields. Explain why the csv module is a useful alternative to hand-written code for reading delimited files, even when the file is simple. Explain why splitting on every delimiter fails for quoted fields and what the csv module does with those fields.

What does section 21.2.5, Reading a csv file as a list of dictionaries, cover?

This subsection shows how csv.DictReader returns rows keyed by field name and explains when that convenience is worth its reading cost. Explain that csv.DictReader returns each row as a dictionary keyed by field name, using field names supplied as a parameter or read from the first line of the data. State that the fields retain their original order. Justify using DictReader when complex data calls for manipulating specific fields, and describe its possible reading-speed cost on a large dataset.

What does section 21.3, Excel files, cover?

This section explains how to extract data from a simple Excel spreadsheet and when CSV is a better format for processing data. Explain why saving an Excel spreadsheet as CSV can be the easiest way to extract its data, and when reading Excel files directly is more practical. Trace the steps for extracting cell values from a simple one-sheet Excel file, including the need for an external Python module. Explain why spreadsheet data may need more careful handling than CSV data, and describe the limits or automatic formatting problems that favor delimited files.

What does section 21.4, Data cleaning, cover?

This section defines dirty data in text-based files and names the process of dealing with it data cleaning. Explain what dirty data means in text-based files and why dealing with it is called data cleaning.

What does section 21.4.1, Cleaning, cover?

This subsection shows how missing-value markers and formatted numbers in data files can interfere with calculations, and describes two ways to handle missing values. Explain why apparently numeric file columns may not be ready for calculations, using the temperature data’s 'Missing' heat-index values and percentage-suffixed coverage values as examples. Describe the two approaches given for handling 'Missing' entries when calculating with heat-index data.

What does section 21.4.2, Sorting, cover?

This subsection explains why sorting data files helps processing and how to choose a sorting approach based on memory, sort requirements, and performance. Explain how sorting a text file can make duplicate values easier to spot and bring related rows together for processing. Distinguish sorting a file's lines in memory with a list's sort method from using an external utility for a simple sort of data larger than memory. Explain why sorted() may be less suitable when memory matters. Describe how a Python sort key can change what part or form of each line is compared, and why a complex key can slow a large sort.

What does section 21.4.3, Data cleaning problems and pitfalls, cover?

This subsection identifies common hidden causes of data-loading problems and suggests ways to isolate errors during cleaning. Explain how whitespace, null characters, and punctuation can disrupt loading or processing even when a text file looks acceptable. Describe how separating cleaning operations and reducing the data being tested can make errors easier to locate.

What should I know before reading chapter 21?

Bytes (chapter 6): The distinction between bytes and text.; Opening files and file objects (chapter 13): Opening files with Python’s open function.; Functions to read and write text or binary data (chapter 13): Reading text from a file; The split and join string methods (chapter 6): Using the string split method; Sorting lists (chapter 5): List sorting methods and the sorted() function.; Custom sorting (chapter 5): Custom list sorting.; lambda expressions (chapter 9): Lambda expressions used as short functions.

Chapter 21: Processing data files

What you will learn in chapter 21 of The Quick Python Book, Fourth Edition

The introduction explains why processing text files matters: data may be unstructured or delimited into records, and datasets may be large and dirty.

After this chapter you will be able to

Text file data challenges. Explain the range of data found in text files and why it can be challenging to use, from unstructured text to delimited records and large, incomplete, or dirty datasets.
Three stages of ETL. Explain what extracting, transforming, and loading data mean when processing data files.
File extraction and size limits. Explain that the extract part of ETL opens a file and reads its contents, and that a file too large to fit in memory must be handled in smaller segments, possibly one line at a time.
ASCII limits and ambiguity. Explain why reading a text file as ASCII can fail and why a byte outside the ASCII range cannot be interpreted reliably without knowing the file’s encoding.
UTF-8 reading default. Justify the section’s recommendation to try UTF-8 when reading text files, including what happens to files containing only ASCII characters.
Encoding error handling options. Describe how the `errors` parameter of Python’s `open` handles invalid characters, distinguishing the default behavior from options that discard, mark, or preserve problem data.
Logical units in text. Explain why processing unstructured text begins by choosing a logical unit, such as a line or paragraph, and matching the way the file is divided to that choice.
Paragraph splitting and word normalization. Describe how the example splits paragraphs at blank lines and why it normalizes case and punctuation before counting words.
Delimited records and fields. Explain how putting related values on one line and separating them with a delimiter makes individual fields easier to identify. Describe the pipe-delimited temperature example and recognize commas and tabs as other common delimiters.
Splitting lines and converting values. Describe how to use a known delimiter with the string split() method to turn each line into a list of fields, and explain why the resulting values may need conversion before further processing.
CSV module for delimited files. Explain why the csv module is a useful alternative to hand-written code for reading delimited files, even when the file is simple.
Quoted fields and embedded delimiters. Explain why splitting on every delimiter fails for quoted fields and what the csv module does with those fields.
Dictionary rows from CSV. Explain that csv.DictReader returns each row as a dictionary keyed by field name, using field names supplied as a parameter or read from the first line of the data. State that the fields retain their original order.
Excel and CSV format choice. Explain why saving an Excel spreadsheet as CSV can be the easiest way to extract its data, and when reading Excel files directly is more practical.
Simple spreadsheet reading sequence. Trace the steps for extracting cell values from a simple one-sheet Excel file, including the need for an external Python module.
Dirty data and cleaning. Explain what dirty data means in text-based files and why dealing with it is called data cleaning.
Non-numeric values in numeric fields. Explain why apparently numeric file columns may not be ready for calculations, using the temperature data’s 'Missing' heat-index values and percentage-suffixed coverage values as examples.
Handling missing heat-index values. Describe the two approaches given for handling 'Missing' entries when calculating with heat-index data.
Benefits of sorting file data. Explain how sorting a text file can make duplicate values easier to spot and bring related rows together for processing.
Memory based sorting choices. Distinguish sorting a file's lines in memory with a list's sort method from using an external utility for a simple sort of data larger than memory. Explain why sorted() may be less suitable when memory matters.
Hidden characters and punctuation. Explain how whitespace, null characters, and punctuation can disrupt loading or processing even when a text file looks acceptable.
Stepwise data cleaning debugging. Describe how separating cleaning operations and reducing the data being tested can make errors easier to locate.
Output files and specifications. Explain that the last part of ETL often writes transformed data to files for people or other applications to use, and that an output specification typically defines the fields, their names, formats, and constraints.
Delimited file writing. Explain how writing a delimited file with the csv module relates to reading one: specify the delimiter, then let the module handle fields that contain that delimiter.
Dictionary row output. Describe how to write dictionary-based rows with DictWriter, including the required field-name list and the optional header-writing method.
Excel spreadsheet writing sequence. Describe the sequence for writing a spreadsheet file: create a workbook, create one or more sheets, then write data into the appropriate cells.
Compressed archive packaging choice. Explain why several related data files or large files might be packaged in a compressed archive, and why a zip file is a practical format for sharing them.
Cook County weather extraction. Describe how to turn the Illinois weather observations into one CSV or spreadsheet file for Chicago (Cook County), including the specified value conversions, stopping point, and check of the output.
AI suitability and prompt clarity. Explain why this problem is fairly suitable for an AI solution and why the prompt must clearly include all necessary information.

Cover of The Quick Python Book, Fourth Edition

liveBook manning.com

Read chapter 21 on liveBook Get the book at manning.com

What it builds on

Bytes (chapter 6) · The distinction between bytes and text.
Opening files and file objects (chapter 13) · Opening files with Python’s open function.
Functions to read and write text or binary data (chapter 13) · Reading text from a file
The split and join string methods (chapter 6) · Using the string split method
Sorting lists (chapter 5) · List sorting methods and the sorted() function.
Custom sorting (chapter 5) · Custom list sorting.
lambda expressions (chapter 9) · Lambda expressions used as short functions.

Section by section

Introduction

The introduction explains why processing text files matters: data may be unstructured or delimited into records, and datasets may be large and dirty.

Text file data challenges: Explain the range of data found in text files and why it can be challenging to use, from unstructured text to delimited records and large, incomplete, or dirty datasets.
Why it matters: Recognizing these variations helps explain why a single approach will not suit every text dataset.

Key terms: unstructured text, record, fields, delimited, dirty

21.1 Welcome to ETL

Introduces extract-transform-load (ETL) as a way to describe getting data from files into a useful form and storing the result.

Three stages of ETL: Explain what extracting, transforming, and loading data mean when processing data files.
Why it matters: These three stages give you a simple way to describe a data-file processing task.

Key terms: extract-transform-load (ETL), extraction, transformation, loading

21.2 Reading text files

Introduces file reading as the extract step of ETL and explains why large files may need to be processed in smaller pieces.

File extraction and size limits: Explain that the extract part of ETL opens a file and reads its contents, and that a file too large to fit in memory must be handled in smaller segments, possibly one line at a time.
Why it matters: File size affects how you structure the code that reads data.

Key terms: ETL, extract, memory, smaller segments, one line at a time

21.2.1 Text encoding: ASCII, Unicode, and others

This section explains why text-file encodings can cause reading errors, why UTF-8 is a useful default, and how Python can handle invalid characters.

ASCII limits and ambiguity: Explain why reading a text file as ASCII can fail and why a byte outside the ASCII range cannot be interpreted reliably without knowing the file’s encoding.
Why it matters: A text file’s bytes do not always tell you which characters they represent.
UTF-8 reading default: Justify the section’s recommendation to try UTF-8 when reading text files, including what happens to files containing only ASCII characters.
Why it matters: UTF-8 is a practical starting choice without sacrificing the ability to read ASCII-only files.
Encoding error handling options: Describe how the `errors` parameter of Python’s `open` handles invalid characters, distinguishing the default behavior from options that discard, mark, or preserve problem data.
Why it matters: Choosing an error policy determines whether troublesome file data stops processing, disappears, or remains recoverable.

Key terms: ASCII encoding, 7-bit values, 8-bit bytes, Unicode, UTF-8, ASCII characters, open, errors, strict, ignore

21.2.2 Unstructured text

This section shows how to choose useful units in unstructured text and normalize words before counting them.

Logical units in text: Explain why processing unstructured text begins by choosing a logical unit, such as a line or paragraph, and matching the way the file is divided to that choice.
Why it matters: Choosing the unit determines which pieces of text your code processes together.
Paragraph splitting and word normalization: Describe how the example splits paragraphs at blank lines and why it normalizes case and punctuation before counting words.
Why it matters: These simple preparations make paragraph processing and word counts better match the text’s meaning.

Key terms: unstructured text, basic logical unit, line, paragraph, split(), newline, normalization, lower, replace, translate

21.2.3 Delimited flat files

This subsection explains how delimiters give text files fields that Python can split into values, while leaving type conversion to a later step.

Delimited records and fields: Explain how putting related values on one line and separating them with a delimiter makes individual fields easier to identify. Describe the pipe-delimited temperature example and recognize commas and tabs as other common delimiters.
Why it matters: Recognizing the record and field boundaries lets you choose how to read the file.
Splitting lines and converting values: Describe how to use a known delimiter with the string split() method to turn each line into a list of fields, and explain why the resulting values may need conversion before further processing.
Why it matters: Separating fields is only the first step if later work requires dates or numeric values.

Key terms: delimited, field, pipe, comma-separated values (CSV), split(), list of values, strings

21.2.4 The csv module

This section shows why Python’s csv module is preferable to hand-written splitting when reading delimited files, especially files with quoted fields.

CSV module for delimited files: Explain why the csv module is a useful alternative to hand-written code for reading delimited files, even when the file is simple.
Why it matters: Using the module can save work and avoid parsing bugs.
Quoted fields and embedded delimiters: Explain why splitting on every delimiter fails for quoted fields and what the csv module does with those fields.
Why it matters: Quoted fields are where a simple split can silently produce the wrong data.

Key terms: csv module, delimited files, trailing newline character, quoted fields, delimiter

21.2.5 Reading a csv file as a list of dictionaries

This subsection shows how csv.DictReader returns rows keyed by field name and explains when that convenience is worth its reading cost.

Dictionary rows from CSV: Explain that csv.DictReader returns each row as a dictionary keyed by field name, using field names supplied as a parameter or read from the first line of the data. State that the fields retain their original order.
Why it matters: Named fields let you refer to CSV values without relying solely on their positions.
DictReader convenience and cost: Justify using DictReader when complex data calls for manipulating specific fields, and describe its possible reading-speed cost on a large dataset.
Why it matters: The convenience of named fields has a performance tradeoff worth considering for large files.

Key terms: DictReader, field name, dictionary, specific fields

21.3 Excel files

This section explains how to extract data from a simple Excel spreadsheet and when CSV is a better format for processing data.

Excel and CSV format choice: Explain why saving an Excel spreadsheet as CSV can be the easiest way to extract its data, and when reading Excel files directly is more practical.
Why it matters: The number of files can change which approach is simpler overall.
Simple spreadsheet reading sequence: Trace the steps for extracting cell values from a simple one-sheet Excel file, including the need for an external Python module.
Why it matters: An Excel file requires a few more steps than a CSV file before its data is available.
Spreadsheet complexity and data risks: Explain why spreadsheet data may need more careful handling than CSV data, and describe the limits or automatic formatting problems that favor delimited files.
Why it matters: A spreadsheet can appear readable while silently changing a value or requiring interpretation that a simple cell-reading pass misses.

Key terms: Excel files, CSV files, delimited files, standard library, openpyxl, workbook, sheet, rows, cells, formatting

21.4 Data cleaning

This section defines dirty data in text-based files and names the process of dealing with it data cleaning.

Dirty data and cleaning: Explain what dirty data means in text-based files and why dealing with it is called data cleaning.
Why it matters: Recognizing these problems helps you see when a data file needs cleaning before it can be processed easily.

Key terms: dirty data, data cleaning, null values, encoding, extra whitespace, unsorted

21.4.1 Cleaning

This subsection shows how missing-value markers and formatted numbers in data files can interfere with calculations, and describes two ways to handle missing values.

Non-numeric values in numeric fields: Explain why apparently numeric file columns may not be ready for calculations, using the temperature data’s 'Missing' heat-index values and percentage-suffixed coverage values as examples.
Why it matters: A column that looks numeric may still contain strings that prevent straightforward calculations.
Handling missing heat-index values: Describe the two approaches given for handling 'Missing' entries when calculating with heat-index data.
Why it matters: Either approach keeps a missing reading from being treated as a number in a calculation.

Key terms: 'Missing', heat index, % Coverage, strings, None, empty string

21.4.2 Sorting

This subsection explains why sorting data files helps processing and how to choose a sorting approach based on memory, sort requirements, and performance.

Benefits of sorting file data: Explain how sorting a text file can make duplicate values easier to spot and bring related rows together for processing.
Why it matters: Sorting can simplify the work you need to do with a file, not just change its order.
Memory based sorting choices: Distinguish sorting a file's lines in memory with a list's sort method from using an external utility for a simple sort of data larger than memory. Explain why sorted() may be less suitable when memory matters.
Why it matters: Choosing a sorting approach that fits the file's size avoids unnecessary memory use.
Sort keys and performance: Describe how a Python sort key can change what part or form of each line is compared, and why a complex key can slow a large sort.
Why it matters: A sort key lets you order records by what matters, but its cost matters on large datasets.

Key terms: duplicate values, related rows, item ID, sort method, sorted() function, available memory, UNIX sort command, key function, lambda function, case insensitive

21.4.3 Data cleaning problems and pitfalls

This subsection identifies common hidden causes of data-loading problems and suggests ways to isolate errors during cleaning.

Hidden characters and punctuation: Explain how whitespace, null characters, and punctuation can disrupt loading or processing even when a text file looks acceptable.
Why it matters: A file that looks fine can still contain characters that prevent it from loading or produce misleading results.
Stepwise data cleaning debugging: Describe how separating cleaning operations and reducing the data being tested can make errors easier to locate.
Why it matters: Smaller, visible steps help you find which operation and which data caused a failure.

Key terms: whitespace, null characters, punctuation, CSV files, exceptions, print statements, logging, Python debugger

21.5 Writing data files

This section explains why transformed data is often written to files and what an output file specification typically defines.

Output files and specifications: Explain that the last part of ETL often writes transformed data to files for people or other applications to use, and that an output specification typically defines the fields, their names, formats, and constraints.
Why it matters: The intended users and file specification shape what the ETL process must write.

Key terms: ETL, transformed data, file specification, fields, format, constraints

21.5.1 CSV and other delimited files

This subsection explains how to write cleaned data to delimited files with the csv module, including how to write dictionary rows with DictWriter.

Delimited file writing: Explain how writing a delimited file with the csv module relates to reading one: specify the delimiter, then let the module handle fields that contain that delimiter.
Why it matters: You can write delimited output without handling delimiter characters inside fields yourself.
Dictionary row output: Describe how to write dictionary-based rows with DictWriter, including the required field-name list and the optional header-writing method.
Why it matters: DictWriter lets you output rows held as dictionaries into the intended CSV fields.

Key terms: csv module, delimiter, delimited files, DictWriter, field names, writeheader

21.5.2 Writing Excel files

This subsection outlines how to create an Excel spreadsheet and notes that cells can be formatted while writing it.

Excel spreadsheet writing sequence: Describe the sequence for writing a spreadsheet file: create a workbook, create one or more sheets, then write data into the appropriate cells.
Why it matters: This sequence gives you the basic structure for turning data into a spreadsheet.
Cell formatting while writing: State that formatting can be added to cells while writing a spreadsheet file.
Why it matters: Formatting is an available option when producing the spreadsheet, not a separate step the section requires.

Key terms: workbook, sheet, cells, CSV data file, formatting

21.5.3 Packaging data files

This subsection explains when to package related or large data files in a compressed archive and why a zip file is a practical choice.

Compressed archive packaging choice: Explain why several related data files or large files might be packaged in a compressed archive, and why a zip file is a practical format for sharing them.
Why it matters: It helps you choose a package that recipients are likely to be able to open.

Key terms: compressed archive, zip file

21.6 Weather observations

Sets a weather-file processing exercise: extract Cook County observations, clean selected values, and verify the resulting table.

Cook County weather extraction: Describe how to turn the Illinois weather observations into one CSV or spreadsheet file for Chicago (Cook County), including the specified value conversions, stopping point, and check of the output.
Why it matters: These requirements define what a correct, inspectable output file must contain—and where the data ends.

Key terms: Cook County, CSV, Missing, percentage, ---

21.6.1 Solving the problem with AI-generated code

This subsection explains why the weather-observations problem is a reasonable candidate for AI-generated code and what its prompt needs.

AI suitability and prompt clarity: Explain why this problem is fairly suitable for an AI solution and why the prompt must clearly include all necessary information.
Why it matters: It helps you judge when AI-generated code may be useful without overlooking the need for a clear prompt.

Key terms: procedural, higher-level design, AI solution, prompt

21.6.2 Solutions and discussion

21.7 Summary

Where this is used later

(chapter intro) (chapter 23) · Provides the prior context of cleaned data.
Data cleaning with a data frame (chapter 24) · Data-frame cleaning revisits the need to prepare values for analysis.

Concepts explained in this chapter

csvCSV is a Python library for reading and writing tabular data in comma-separated and other delimited formats.
data cleaningData cleaning is the process of identifying and correcting, removing, or otherwise handling irregular, invalid, missing, or inconvenient data values and formats.
textText is written language represented as a sequence of characters, including content stored in plain-text files.
attributeAn attribute is a named value or behavior associated with an object and accessed through that object.
delimiterA separator is a formatting character inserted between groups of digits in a structured text value.
replace methodreplace is a Python string method that returns a copy with occurrences of a specified substring replaced by another string, optionally limiting the number of replacements.
splitsplit is a string method that divides a string into a list of substrings according to a separator or, by default, whitespace.
parsingParsing is the analysis of input according to structural or syntactic rules to identify its components and produce a structured, usable representation.
recordA row is a horizontally aligned record of related values in a tabular data structure.
punctuationPunctuation is the set of written symbols that structures or separates language apart from letters and digits.
spreadsheetA spreadsheet file is a file that stores worksheet data, including cells, values, formulas, and related tabular structure.
conversational aiA chatbot is software that conducts conversational interactions with users through natural-language input and output.
promptA prompt is an instruction or input supplied to an AI system to elicit a particular response or generated output.
UTF-8UTF-8 is a variable-length character encoding that represents Unicode text while preserving the standard ASCII byte representations.
UnicodeUnicode is a character-encoding standard that assigns code points to characters across the world's writing systems and many symbols.
encodingencode is a string method that converts text into a bytes object using a character encoding.
code generationCode generation is the automated production of source code from prompts, specifications, templates, or other inputs.
ASCIIASCII is a character-encoding standard that assigns numeric codes to a defined set of English letters, digits, punctuation marks, and control characters.
newlineA newline is a character or character sequence that marks the boundary between consecutive lines of text.
normalizationNormalization is the process of converting data into a consistent canonical representation.
missing valueA missing value is a data item for which the intended observation is unavailable, inapplicable, or not reported.
migrationExtract-transform-load is a data-integration process that extracts data from sources, transforms it into a suitable form, and loads it into a destination system.
sorting algorithmA sorting algorithm is a procedure that arranges elements of a collection into an order determined by a specified criterion.
meanA mean is a measure of central tendency, calculated as the arithmetic average of values or, for a random variable, as the probability-weighted average of its possible values.
key functionA key function is a callable that maps each item to a value used to determine that item's ordering during sorting.
sortedsorted is a Python built-in function that returns a new list containing the items of an iterable in sorted order.
whitespace characterWhitespace is non-printing character spacing, such as spaces, tabs, and line breaks, that can separate or structure source code.
MapA map is an associative data structure that stores key-value pairs and supports retrieving values by key.
file objectA data file is a stored file whose contents provide data for retrieval, processing, or analysis.
PythonPython is a high-level general-purpose programming language with an interpreted execution model and dynamic features.
zipzip is a Python built-in function that aggregates corresponding elements from two or more iterables into tuples, stopping when the shortest iterable is exhausted.
binary modeBinary mode is a file-access mode in which file operations read or write raw bytes without text encoding or newline translation.
openopen() is Python's built-in function for opening a file and returning a file object.
translatetranslate is a Python string method that returns a copy of a string with characters transformed or removed according to a translation table.
bindingA binding is an association between an identifier and an object in a namespace.
structured dataTabular data is data organized into rows and columns representing records and fields.

These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.