Chapter 25 — Data Handling with CSV and JSON

Section 1: What CSV Data Looks Like

CSV stands for comma-separated values, and it is one of the simplest ways to store tabular data, meaning data arranged in rows and columns, like a spreadsheet table. In a CSV file, each line is usually one row, and within each line, commas separate the values for each column. The first line often holds headers, which are names for the columns, such as name, age, and city, and the lines that follow hold one record per line, with values in the same order as the headers. That simplicity is CSV's strength: it can be read by people, by spreadsheets, and by programs in many languages, without special software, and it is easy to create, inspect, and share, which is why it remains widely used for exchanging tabular data, despite its limitations for more complex structures.

CSV's simplicity also creates its first challenge: values themselves may contain commas, line breaks, or quotation marks, which would confuse a naive reading that simply splits each line at every comma. To handle that, CSV uses quoting: a value containing a comma, or a line break, is wrapped in quotation marks, so that a reader knows the comma inside the quotes is part of the value, not a separator between columns. A quotation mark inside a quoted value is usually represented by doubling it, so that a reader can distinguish a quote that is part of the data from a quote that ends the quoted value. Those quoting rules are standardised in practice, though variations exist, and a proper CSV reader, rather than manual splitting, is essential for handling them correctly, because manual splitting will break on quoted commas, while a reader that understands quoting will parse them correctly. That conversion step is where many CSV bugs arise, because a value that looks like a number to a human, such as 1,000 with a comma as a thousands separator, will be misparsed as two columns if quoting is not used, or will fail conversion if the comma is included in the text. Understanding that CSV is text-only, and that typing is the program's responsibility, helps you design CSV handling that validates and converts carefully, rather than assuming values arrive in the right type.

Finally, CSV files vary in details that matter: some use a different separator, such as a semicolon or a tab, especially in regions where commas are used as decimal separators, some use different line endings, some include a header row and some do not, and some use different text encodings, which affect how non-English characters are stored. Those variations mean that reading a CSV file requires knowing, or detecting, its specific format details, rather than assuming one universal CSV format exists. A robust CSV handler will allow the separator, header presence, and encoding to be specified or detected, will handle quoting correctly, and will validate the resulting rows, checking that each row has the expected number of columns and that values convert as expected. That robustness, built on understanding CSV's simple structure and its variations, is the foundation for the rest of this chapter, which covers reading, writing, cleaning, and combining CSV data in Python programs.

Check: Why should you use a proper CSV reader instead of simply splitting each line at commas?

Section 2: Reading CSV Rows

Reading CSV data means turning a file's lines into rows your program can work with, typically as lists of values or as dictionaries keyed by header names, depending on how you want to access columns. Reading as lists is simple: each row becomes a list, in column order, and you access values by position, such as the first value, the second value, and so on. That positional access is fast to write but fragile, because if the file's column order changes, your code will read the wrong columns without any obvious error, since positions still exist but now mean different things. Reading as dictionaries, keyed by headers, is more robust: each row becomes a mapping from header name to value, and you access values by name, such as the value for name or for age, so that column order changes do not break your code, provided header names stay the same. That robustness makes dictionary-style reading preferable for most real CSV work, especially when files come from other people or systems whose column order you do not control.

When reading, you should first decide how to handle the header row, if present. If the file has headers, you can use them as dictionary keys, and you should also validate them, by checking that expected headers are present and that there are no unexpected duplicates or misspellings that would cause key errors later. An empty line, or a line with only whitespace, should usually be skipped, rather than treated as a row of empty values, because it carries no data. A row with extra columns may indicate a quoting error or an unexpected field, and it should be flagged rather than silently truncated, because truncation could hide data loss. Those handling decisions, made explicitly and logged where appropriate, turn CSV reading from a fragile operation that crashes on the first messy row into a robust one that processes good rows, reports bad rows, and completes with a clear summary of what was read and what was skipped.

Finally, reading CSV should be done in a way that handles large files efficiently, by processing rows one at a time, rather than loading the entire file into memory at once, when the file is large. Streaming row by row, as a reader naturally does, keeps memory use low and allows processing to start before the whole file is read, which is important for files with many thousands or millions of rows, where loading everything at once could exhaust memory or slow the program unnecessarily. For small files, loading all rows at once is fine and often simpler, because you can then sort, filter, or analyse the whole dataset in memory, but for large files, streaming, combined with filtering or aggregating as you go, is the scalable approach. Choosing between loading and streaming, based on file size and on whether you need random access to all rows or only sequential processing, is a design decision that affects both performance and simplicity, and it should be made deliberately, not by defaulting to loading everything without considering size.

Check: Why is reading CSV rows as dictionaries keyed by headers more robust than reading them as lists by position?

Section 3: Writing CSV Files

Writing CSV data is the reverse of reading: turning your program's rows, whether lists or dictionaries, into lines in a file, with values separated by the chosen delimiter, headers written first if desired, and quoting applied where needed for values containing delimiters, quotes, or line breaks. Writing correctly requires using a CSV writer, rather than manually joining values with commas, for the same reason reading requires a reader: manual joining will break on values containing commas or quotes, producing files that other readers will misparse, while a writer that understands quoting will wrap such values correctly, ensuring the file can be read back accurately. That round-trip property, writing a file that can be read back to the same data, is the test of correct CSV writing, and it is achieved only by using a proper writer, not by string joining, which may appear to work for simple values but will fail for realistic values containing punctuation, spaces, or special characters.

When writing, decide on headers, delimiter, and line endings explicitly, and document those choices, so that readers of your file, whether people or programs, know how to interpret it. Headers should match the field names your program uses, in a consistent order, and they should be written as the first line, unless the file's specification says otherwise, because headers make a file self-describing, allowing a reader to understand its columns without separate documentation, and allowing dictionary-style reading by name, as discussed earlier. Line endings should follow the platform or the specification, and a writer that handles them correctly, rather than manual line writing that may produce inconsistent endings, ensures the file is readable on different systems without extra conversion.

Writing also involves handling data types and missing values, because, as with reading, CSV stores everything as text, so numbers, dates, and true-or-false values must be converted to text before writing, in a format that readers can parse back correctly. That consistency in writing, numbers plain, dates standard, missing as empty, makes files easier to read, easier to validate, and easier to convert back to typed values, because the reader can apply a single, predictable conversion rule to each column, rather than handling multiple formats for the same kind of value.

Finally, writing should be verified by reading the file back, at least during development and testing, to confirm that the written data matches the original data, including values with special characters, empty values, and values at the extremes of your data's range. That read-back test, comparing original rows with rows read from the newly written file, catches writing bugs, such as incorrect quoting, wrong header order, or inconsistent missing-value handling, before the file is shared with others, where such bugs would cause confusion or data loss for recipients who trust the file to be correct. Writing CSV is therefore not just about producing a file, but about producing a file that is readable, consistent, and verifiable, with headers, delimiter, quoting, and type formatting all chosen deliberately and documented, so that the file serves as a reliable exchange format, not just as a dump of text that happens to look tabular.

Check: How can you verify that CSV writing is correct, and why is reading the file back a good test?

Section 4: Headers, Order and Consistency

Headers are the names at the top of a CSV file that label its columns, and they play a crucial role in making data understandable, robust, and maintainable, beyond merely being the first line. Good headers are clear, concise, and consistent, using names that describe the column's content without ambiguity, such as customer_name rather than name, when a file contains multiple kinds of names, or order_total rather than total, when totals could refer to different things. Consistent naming, such as using lower-case letters with underscores, or another single convention throughout a file and across related files, makes headers easier to use in code, because dictionary keys derived from headers will follow that convention, and it makes files easier to combine, because related files will use the same header names for the same concepts, rather than varying spellings that require mapping. Header consistency is therefore a data design decision, not just a cosmetic choice, and it should be made deliberately at file creation, rather than left to chance.

Column order matters less when reading by header name, but it still matters for human readability, for files read by position, and for consistency across versions of a file. Those ordering considerations, though they seem minor, are part of maintaining a file format as a stable interface, similar to maintaining a function's signature, where changes should be made carefully and communicated, because readers depend on the format's stability.

Consistency across rows is equally important: every row should have the same number of columns, in the same order, with values of the expected type for each column, or with a consistent representation for missing values, as discussed earlier. Validation, checking row length, header presence, and value formats, should be part of both reading and writing, to catch inconsistencies early, when they are easier to fix, rather than later, when they have affected results. A file that is consistent in headers, order, and row shape is a reliable file, while one that is inconsistent, even in small ways, is a source of bugs that are hard to trace, because they arise from data shape, not from code logic, and they may only appear for certain rows, making them intermittent and confusing.

Finally, document a CSV file's format, including its headers, their meanings, their types, its delimiter, its header presence, and its handling of missing values, in a readme or in a header comment, where the format allows it, so that future readers, including your future self, do not need to guess at the format from examples alone. Documentation of format is especially important when a file is shared with others, or when it will be used long after its creation, because the original author's knowledge of the format will fade, and without documentation, the file's meaning may become ambiguous, leading to misinterpretation, such as mistaking a column's units, or a date's format, or a code's meaning. Format documentation, kept alongside the file or in the project's documentation, turns a CSV file from a bare table of values into a self-explanatory dataset, whose columns, types, and conventions are clear, and whose use is safe for analysis, for combining with other data, and for long-term archiving, which is the goal of good data handling, not just of good code.

Check: Why should column order stay stable across versions of a CSV file, even when reading by header name?

Section 5: Quoting, Delimiters and Special Characters

Quoting and delimiters are the mechanics that make CSV work for realistic data, and understanding them is essential for reading and writing files that contain commas, quotes, line breaks, or other special characters in their values. The delimiter is the character that separates columns, most commonly a comma, but sometimes a semicolon, a tab, or another character, chosen to avoid conflicting with characters that appear frequently in the data. For example, data that frequently contains commas, such as addresses or descriptions, may be better stored with a semicolon or tab delimiter, to reduce the need for quoting, though quoting is still needed for values containing the chosen delimiter, whatever it is. Choosing a delimiter is therefore a trade-off: a comma is standard and widely expected, but a less common delimiter may be more practical for data rich in commas, provided the choice is documented and consistently used, so that readers know what to expect and can parse the file correctly, rather than assuming a comma and misparsing semicolon-separated data as a single column.

Quoting rules handle values that contain the delimiter, a quote, or a line break, by wrapping the entire value in quotation marks, most commonly double quotes, so that a reader knows the delimiter inside the quotes is data, not a separator, and the line break inside the quotes is data, not a row ending. A proper CSV reader handles all of that automatically, which is why manual splitting, which ignores quoting state, fails for realistic data, while a reader succeeds, and why you should always use a reader, even for files that look simple, because they may contain a quoted comma in a row you have not yet seen, which manual splitting would mishandle silently.

Special characters beyond delimiters and quotes also require care, particularly text encoding, which determines how characters outside the basic English alphabet, such as accented letters, characters from other writing systems, or symbols, are stored as bytes in the file. Encoding errors are a common source of CSV problems for international data, and they are often mistaken for parsing errors, because garbled text looks like corrupted data, when in fact the parsing was correct but the decoding was wrong, a distinction that checking encoding, rather than structure, will reveal.

Finally, test quoting, delimiter, and encoding handling with realistic, tricky values, including values with commas, quotes, line breaks, leading and trailing spaces, and non-English characters, to confirm that your reading and writing round-trip correctly, meaning data written and then read back matches the original exactly, including those tricky values. That round-trip test, with a small, deliberately tricky dataset, is the best verification for CSV mechanics, because it exercises quoting, delimiter handling, and encoding in combination, revealing bugs that simple values, such as plain words and numbers, would not expose. A CSV handler that passes a tricky round-trip test can be trusted for real data, which will inevitably contain such values, while one that only passes simple tests will fail on the first realistic file it encounters, with a quoting or encoding bug that could have been caught by a tricky test during development, when fixing it is cheap, rather than after sharing, when it affects recipients' data.

Check: How does quoting allow a CSV value to contain a comma without being split into two columns?

Section 6: Converting Text to Types

Converting text to types is a central task in CSV handling, because CSV stores everything as text, but programs need numbers for calculation, dates for comparison, and true-or-false values for decisions, and converting incorrectly, or not at all, leads to bugs that are subtle and hard to spot. For example, adding two CSV values that look like numbers, but are actually text, may concatenate them as strings, producing 1020 instead of 30 for 10 and 20, if the program does not convert them to numbers first, or it may raise a type error, depending on the language and the operation, both of which are wrong results, though in different ways. That example illustrates why type conversion must be explicit and deliberate: you must decide, for each column, what type its values should become, and convert each value accordingly, with validation, rather than assuming that text that looks numeric will behave numerically, because in Python, text and numbers are different types with different behaviours, even when their printed forms look similar.

Conversion for numbers involves parsing text into integers or decimals, handling formats such as decimal points, negative signs, and, where relevant, thousands separators, which should be removed or handled consistently before parsing, as discussed earlier, to avoid misparsing. Conversion should also handle empty values, which cannot be parsed as numbers, by deciding whether an empty value means zero, missing, or an error, and handling it accordingly, rather than letting parsing fail with a confusing error, or silently treating it as zero when it actually means unknown, which would distort calculations. For true-or-false values, conversion involves mapping text representations, such as yes, no, true, false, 1, and 0, to boolean values, with a clear, documented mapping, because different files use different representations, and assuming one mapping for all files will misinterpret others.

Conversion errors should be handled gracefully, with clear reporting, rather than crashing the entire processing on the first bad value, unless the data's integrity requires strict failure, in which case crashing with a clear message is appropriate, but it should still identify the row and column that failed, so that the bad data can be fixed. That error collection, similar to logging data processing errors, turns conversion from a fragile, all-or-nothing operation into a resilient one that handles real-world messy data, where a few bad values are expected, and where losing good data because of them would be wasteful and frustrating for users who need results for the good rows even while bad rows are being corrected.

Finally, conversion should be tested with edge cases, including empty values, values with whitespace, values with unexpected formats, values at the extremes of the expected range, and values that look valid but are not, such as a number with a trailing letter, or a date with a swapped day and month, which may parse as a valid date in a different format but with the wrong meaning, a particularly insidious error because it produces a plausible but wrong result, rather than an obvious failure. Type conversion, done carefully, with explicit formats, graceful error handling, and thorough testing, turns CSV text into reliable, typed data that programs can calculate with, compare, and analyse correctly, which is the goal of data handling, not just of parsing.

Check: Why must CSV values be converted from text to numbers or dates explicitly before calculations?

Section 7: Validating CSV Data

Validating CSV data means checking that a file's contents meet the expectations your program has for it, before using the data for calculations, analysis, or storage, so that bad data is caught early, with a clear report, rather than causing a wrong result or a crash later, when its cause is harder to trace. Validation checks can include: does the file have the expected headers, does each row have the expected number of columns, are required values present and non-empty, are values in the expected format and range, such as a quantity being positive, an age being within a plausible range, or a code matching an expected pattern, and are values unique where uniqueness is required, such as an identifier that should not repeat? Each check corresponds to a rule about the data, and together, those rules define what valid data looks like for your program, forming a specification that can be documented, tested, and enforced consistently, rather than left implicit in code that assumes data is valid and fails confusingly when it is not.

Validation should be done in a way that reports all problems found, or at least a useful summary, rather than stopping at the first problem, because a file with many bad rows is more efficiently fixed if the user sees all its problems at once, rather than fixing one, re-running validation, finding the next, and repeating, which is tedious and slow, especially for large files. A validation report that lists, for each problem, the row number, the column, the value, and the rule violated, lets a user fix data systematically, by row, or by rule, and lets a program decide whether to reject the entire file, to skip bad rows, or to accept good rows and report bad ones, depending on the data's importance and on whether partial data is useful. That decision, reject all versus accept good rows, should be made explicitly, based on the use case: for a file that will be used for financial calculations, rejecting all on any error may be safer, because partial data could produce misleading totals, while for a file used for analysis, accepting good rows and reporting bad ones may be more useful, because most data is still valuable even if a few rows are bad. That balance, strict enough to catch errors, lenient enough to accept real diversity, requires thinking about real data, not just about textbook examples, and it requires testing validation with diverse, realistic data, including edge cases, to ensure it behaves correctly for the full range of legitimate inputs, not just for typical ones.

Finally, validation should be separated from processing, so that validation code can be tested independently, reused for different files with the same format, and updated when rules change, without affecting processing logic, which should assume, after validation, that data meets the rules, and can therefore proceed without redundant checks, or with only minimal defensive checks for safety. Validating CSV data thoroughly, with clear rules, comprehensive reporting, and separation from processing, turns data handling from a fragile hope that data is correct into a verified assurance that it meets expectations, which is essential for reliable programs that depend on external data.

Check: Why should validation report all problems found, rather than stopping at the first bad row?

Section 8: Cleaning Messy Data

Cleaning messy data means transforming raw data, which may contain inconsistencies, errors, and formatting variations, into a consistent, usable form, without changing its meaning, and it is a necessary step for most real-world CSV files, which are rarely as clean as textbook examples. Common messiness includes: extra whitespace at the start or end of values, inconsistent capitalisation, such as Lagos, lagos, and LAGOS for the same city, inconsistent formats for the same kind of value, such as dates written as 2026-01-15 in some rows and 15/01/2026 in others, missing values represented inconsistently, such as empty, dash, or the word none, and duplicate rows, which may be exact duplicates or near-duplicates differing only by whitespace or capitalisation. Cleaning addresses each of those, by trimming whitespace, by normalising capitalisation where appropriate, by converting formats to a single standard, by unifying missing-value representations, and by removing duplicates, so that subsequent processing, such as grouping, counting, or comparing, works correctly, rather than treating Lagos and lagos as different cities, or counting duplicates as separate records.

Cleaning should be done carefully, with an awareness that it changes data, and that changes, if done incorrectly, can distort meaning, such as normalising capitalisation for names, where capitalisation may be significant, or converting dates without knowing their original format, where day and month could be swapped, producing a wrong date that looks valid. Cleaning rules should be tested on examples, including edge cases, to confirm they produce the intended standard form without distorting data, and cleaning should be logged, with counts of how many values were changed and how, so that the cleaning process is transparent and its effects can be audited, rather than being a hidden transformation that future readers cannot trace.

Cleaning and validation, discussed earlier, are related but distinct: validation checks whether data meets rules, while cleaning transforms data to meet rules, where transformation is safe and meaning-preserving. That judgement, documented per column, makes cleaning principled, rather than ad hoc, and it prevents cleaning from becoming a source of errors itself, where well-intentioned transformations distort data in ways that are harder to detect than the original messiness, because cleaned data looks tidy while being wrong.

Finally, cleaning should be done on a copy of the data, or in a way that preserves the original, at least until cleaning is verified, so that mistakes can be undone by re-cleaning from the original, rather than by trying to reverse a cleaning step that may have lost information, such as lowercasing a name, which cannot be reversed to restore original capitalisation. Cleaning, done thoughtfully, with documented standards, tested rules, logged changes, and preserved originals, turns messy real-world data into reliable data that programs can process correctly, which is a significant part of data handling work, often taking more time than the analysis itself, but essential for trustworthy results, because analysis on messy data, without cleaning, produces misleading conclusions, no matter how sophisticated the analysis, if its input is inconsistent or incorrect.

Check: When should messy data be cleaned, and when should it instead be flagged or rejected?

Section 9: Filtering and Selecting Rows

Filtering means selecting a subset of rows from a dataset based on a condition, such as rows where a column meets a criterion, and it is one of the most common data handling operations, used to focus analysis on relevant data, to remove irrelevant or invalid rows, or to extract a specific group for further processing. Filtering conditions can be simple, such as selecting rows where a city equals Lagos, or where a quantity is greater than ten, or they can be combined, such as selecting rows where a city equals Lagos and a quantity is greater than ten, or where either of two conditions holds, using logical combinations that must be expressed carefully, to avoid selecting the wrong rows due to operator precedence or misunderstanding of and versus or, as discussed for conditions in earlier chapters. Testing filters with small examples, including rows that should be selected, rows that should not, and rows at the boundary of the condition, ensures the filter behaves as intended, rather than selecting too many or too few rows, which would distort subsequent analysis without an obvious error, because filtering produces a plausible subset even when it is wrong, unlike a crash, which announces itself.

Filtering should be done with an awareness of what is excluded, not just what is included, because excluded rows may contain important information, such as a pattern in why rows were excluded, or a bias introduced by filtering, where the subset is not representative of the whole dataset, leading to conclusions that do not generalise. For example, filtering to only rows with complete data may exclude rows from a particular group that is more likely to have missing values, biasing analysis towards the group with complete data, without the analyst realising it, unless they check the excluded rows' characteristics. To guard against that, log filtering results, including how many rows were input, how many were selected, and how many were excluded, and, where relevant, summarise excluded rows, such as by checking whether exclusion correlates with another column, so that filtering's impact is visible, rather than hidden. Column selection, combined with row filtering, lets you shape a dataset to exactly what a task needs, producing a focused, manageable dataset from a larger, messier input, which is often the first step in a data handling pipeline, before aggregation, sorting, or analysis.

Finally, filtering and selection should be reversible, or at least documented, so that the original dataset can be recovered, or the filtering criteria can be reapplied, if the filtered result is questioned or if a different subset is later needed. Filtering by modifying the original file in place, without a backup, risks losing excluded rows permanently, which may be needed later, and should be avoided, unless the original is backed up elsewhere, such as in version control, though data files, especially large ones, may not be version-controlled, making in-place filtering particularly risky. Filter to a new output, document the criteria, log counts, and preserve the original, and filtering becomes a safe, transparent, and repeatable operation, rather than a destructive one that cannot be undone or audited.

Check: Why should you log how many rows were selected and excluded when filtering a dataset?

Section 10: Sorting and Ordering Rows

Sorting rows means arranging them in a specific order, based on one or more columns, such as alphabetically by name, numerically by quantity, or chronologically by date, and it is useful for presentation, for finding extremes, such as the largest or smallest values, and for preparing data for further processing that assumes ordered input, such as grouping or merging. Sorting in Python, for CSV rows, typically involves specifying a key, which is the column or combination of columns to sort by, and a direction, ascending or descending, and it requires that values in the sort key be comparable, meaning they must be of the same type, or converted to a comparable type, before sorting, because sorting text that represents numbers, as text, will order 10 before 2, since text comparison is character by character, not numeric, a classic sorting bug that arises from forgetting to convert CSV text to numbers before sorting, as discussed for type conversion. That conversion-before-sorting step is essential, and it should be tested with values that expose text-versus-numeric ordering differences, such as 2, 10, and 100, to confirm sorting is numeric where intended, not textual.

Sorting by multiple columns, such as sorting by city, then by name within each city, requires specifying keys in priority order, where the first key is the primary sort, the second breaks ties within the first, and so on, and it is useful for producing organised outputs, such as a report grouped by city with names alphabetically within each group. Understanding stability helps you design multi-stage sorts correctly, rather than assuming a sort will preserve order when it may not, depending on the sorting method used, though Python's default sorting is stable, which is a useful property to know and to rely on deliberately, not accidentally.

Sorting should also handle missing values and mixed types carefully, because a missing value in a sort key cannot be compared straightforwardly with present values, and it must be placed consistently, either first or last, depending on the intended meaning, rather than causing a comparison error or an inconsistent order. Similarly, values of different types in the same column, such as numbers and text mixed, should be cleaned or converted to a single type before sorting, because comparing different types may fail or produce an arbitrary order, depending on the language and the values, and a sort that works for clean data but fails for messy data is not robust for real-world use, where mixed types, though they indicate a data quality problem, will occur and should be handled gracefully, either by cleaning, by flagging, or by sorting with a key that handles mixed types explicitly.

Finally, sorting large datasets requires considering performance and memory, because sorting typically requires loading all rows into memory, or using an external sort that processes data in chunks, for very large files that do not fit in memory, which is beyond the scope of small projects but worth knowing as a limit: sorting is not a streaming operation, like filtering, which can process rows one by one, but a whole-dataset operation, which needs all rows, or at least a significant portion, to determine order.A quick sanity check is to sort a tiny sample by hand first, so you can see immediately whether the order matches what you intended.

Check: Why must CSV text values be converted to numbers before sorting numerically, rather than sorting them as text?

Section 11: Aggregating Data: Counts and Sums

Aggregating data means summarising many rows into fewer, more meaningful values, such as counts, sums, averages, minimums, and maximums, grouped by a category, and it is the core of most data analysis, turning raw records into insights. For example, from a CSV file of sales, with columns for product, quantity, and price, aggregation can answer: how many sales were there in total, what was the total revenue, what was the average sale value, and how do those totals break down by product, by city, or by month? Each of those questions involves grouping rows, by the relevant category, and then applying a summarising function to each group, such as counting rows, summing a numeric column, or averaging it. Grouping and summarising together transform a long list of individual records into a short table of group totals, which is easier to read, to compare, and to act on, than the raw list, and which reveals patterns, such as which product sells most, or which city generates most revenue, that are invisible in individual rows.

Aggregation requires careful handling of types and missing values, as with other CSV operations, because summing text values, or averaging values that include missing entries, will produce wrong results or errors, unless conversion and missing-value handling are done first, as discussed earlier. Documenting aggregation definitions, including how missing values were treated, is therefore essential for trustworthy summaries, because two aggregations of the same data, with different missing-value handling, can give different totals, and a reader needs to know which definition was used to interpret the result correctly.

Grouping also requires choosing grouping keys thoughtfully, because grouping by too fine a key, such as by individual day, may produce many small groups that are hard to compare, while grouping by too coarse a key, such as by year, may hide important variation within groups, such as monthly trends within a year. Testing aggregation with small, hand-calculated examples, where you can verify group totals by hand, ensures the grouping and summarising logic is correct, before applying it to a large dataset where hand verification is impossible, and where a grouping bug, such as grouping by the wrong column, or by a column with inconsistent values due to capitalisation differences, would produce wrong group totals that look plausible but are actually misgrouped, because Lagos and lagos, if not cleaned, would form separate groups, splitting a city's total across two groups and understating each.

Finally, aggregation results should themselves be handled as data, with clear headers, consistent types, and documentation of how they were computed, so that they can be written to a new CSV file, shared, or used for further analysis, without losing the information about their definitions. That care turns aggregation from a one-off calculation, whose results are hard to reproduce or to verify, into a documented, repeatable step in a data pipeline, where input, grouping, summarising, and output are all explicit, and where a future analyst can reproduce the same totals from the same input, or update them when new data arrives, by reapplying the same aggregation logic, which is the goal of reliable data handling, not just of producing a single summary, but of producing a summary that can be trusted, explained, and reproduced.

Check: Why must missing values be excluded, not treated as zero, when summing or averaging a column?

Section 12: Grouping Rows by Category

Grouping rows by category is the operation that underlies aggregation, where rows are collected into groups sharing a common value, such as the same city, the same product, or the same month, so that each group can be summarised, counted, or analysed separately. Grouping can be done by sorting rows by the grouping key and then collecting consecutive rows with the same key, or by using a dictionary keyed by the grouping value, where each key maps to a list of rows in that group, or to a running summary for that group, updated as rows are processed. Either approach, implemented correctly, produces the same groups, and the choice between them depends on whether sorted output is needed, on memory constraints, and on personal preference, but the result, groups keyed by category, should be the same, and testing grouping with small examples, where groups and their members can be listed by hand, verifies that the implementation groups correctly, without missing rows or misassigning them to the wrong group due to key inconsistencies, such as capitalisation or whitespace differences, which, if not cleaned, will split a single category into multiple groups, as discussed for aggregation.

Grouping by multiple categories, such as by city and by product together, creates groups for each combination, such as Lagos and product A, Lagos and product B, and so on, which allows analysis of interactions, such as which products sell best in which cities, rather than just overall product totals or overall city totals. Testing multi-category grouping with examples that include all combinations, and with examples where one category is the same but another differs, ensures the composite key distinguishes correctly, because a bug in key construction, such as using only one category instead of both, would produce groups that are too coarse, merging distinct combinations, while a bug that includes an irrelevant column would produce groups that are too fine, splitting a single combination into many small groups, both of which would distort analysis without an obvious error, because the output would still look like grouped data, just with wrong group sizes.

Grouping also raises questions about group order and about empty groups. Empty groups, meaning categories that have no rows in the input, such as a product that had no sales in a period, will not appear in grouping output if grouping is based only on rows present, because there are no rows to form such a group, yet for some analyses, empty groups should appear with zero counts, to show that a category had no activity, rather than being omitted, which could be misinterpreted as the category not existing. Deciding whether to include empty groups, and, if so, how to know which categories should appear even when empty, requires a list of all possible categories, from a separate source, such as a product catalogue, not just from the data itself, and that decision should be documented, so that readers know whether a missing group means no activity or a missing category.

Finally, grouping is a step towards many other analyses, including sorting groups by their summaries, filtering groups by a condition, such as groups with more than a threshold number of rows, and visualising group summaries, as covered in a later chapter on visualisation concepts, where group totals become chart data.

Check: How can inconsistent capitalisation in a grouping column split one category into multiple groups?

Section 13: Joining Two Datasets

Joining two datasets means combining rows from one file with rows from another, based on a shared key, such as a customer identifier that appears in both a customers file and an orders file, so that each combined row contains information from both sources, such as an order together with its customer's name and city, rather than just an order with a customer identifier that must be looked up separately. Joining requires a shared key that uniquely identifies the entity being joined, such as a customer, a product, or a date, and it requires that the key's values match exactly between files, after cleaning, because a key that differs by whitespace, capitalisation, or format between files will fail to match, even though it refers to the same entity, producing missing joins that look like data absence, rather than like a key mismatch, which is a subtle and common joining bug.

Join types describe what happens to rows that do not match in the other file. Documenting the join type and its handling of unmatched rows is essential, because different join types produce different row counts and different results, and a reader who does not know which type was used cannot interpret the output correctly, or reproduce it.

Joining also requires handling duplicate keys, where the same key appears multiple times in one or both files, which can cause a join to produce more rows than either input, through a combinatorial effect, where each matching row in one file pairs with each matching row in the other, multiplying rows in a way that may be intended, such as joining orders with order items, where one order has many items, or may be unintended, such as joining on a non-unique key like city, where many customers share a city and many orders share a city, producing a huge, meaningless cross-product of rows that share only a city, not a specific customer. Checking key uniqueness before joining, by counting how many times each key appears in each file, and by confirming that the key is appropriate for the intended join, meaning it identifies the entity you intend to match, not just a shared attribute, prevents such multiplication errors, which are a classic joining pitfall, where a join that looks correct produces an explosion of rows that distorts counts and sums, because each original row is counted multiple times in the joined result, inflating totals without an obvious error, unless row counts are checked before and after joining, as a basic sanity check that should always be performed.

Finally, joining should be verified by checking row counts, by sampling joined rows and comparing them with source rows, to confirm that matched information is correct, and by checking unmatched rows, to confirm that their handling, discarding or blank-filling, matches the intended join type and is documented.

Check: Why should you check row counts before and after a join, and what does an unexpected explosion in rows suggest?

Section 14: JSON Basics in Practice

JSON, which was introduced in an earlier chapter on dates, times, and JSON, is a text format for structured data, using objects, which are collections of named values, arrays, which are ordered lists of values, and simple values, such as text, numbers, true-or-false, and null for missing, to represent data that is more complex than a flat table, such as nested records, lists within records, or records with varying fields. A JSON file for a task manager, for example, might contain an array of task objects, each with a title, a status, a list of tags, and a nested object for dates, a structure that CSV, with its flat rows and columns, cannot represent without flattening or splitting into multiple files, while JSON represents it naturally, in a single file, with nesting preserved, which is why JSON is often chosen for application data, configuration, and data exchange between programs, where structure matters more than tabular simplicity.

Practical JSON handling starts with reading a file into Python data structures, where JSON objects become dictionaries, arrays become lists, and simple values become their Python equivalents, such as text becoming strings, numbers becoming integers or decimals, and null becoming a special missing value, and that mapping, though straightforward, requires care with types, because JSON numbers may be integers or decimals, and Python must preserve that distinction where it matters, such as for money or for counts, and because JSON's null, representing missing or unknown, must be handled explicitly in code, rather than being treated as an empty string or zero, which would distort meaning, similar to missing-value handling in CSV, where missing must be distinguished from zero or empty. Reading JSON also requires handling errors, such as malformed JSON, where a file is not valid JSON due to a missing comma, an unmatched bracket, or a trailing comma, which should be reported with a clear message identifying the file and, where possible, the location of the error, so that the file can be fixed, rather than crashing with a cryptic message that gives no clue about which file or which part failed, especially in programs that read multiple JSON files, where identifying the faulty file is essential for fixing it.

Writing JSON involves converting Python data structures back to JSON text, with choices about formatting, such as whether to indent the output for human readability, with line breaks and spaces that make structure visible, or to write it compactly, without extra whitespace, to save space, especially for large files or for network transmission, where size matters more than readability.

In practice, keep JSON files small and readable for application data, and avoid storing large binary content or deeply nested structures that make files hard to inspect by hand. When a JSON structure grows complex, document its shape in the project readme, listing top-level keys and the type of each value, so that future readers do not need to infer structure from examples alone. That documentation, kept alongside the file, makes JSON data maintainable over time, even as the program evolves and new fields are added.

Check: Why is JSON better than CSV for nested data like a task with a list of tags?

Section 15: Reading and Writing JSON Files

Reading a JSON file in a Python program typically involves opening the file, parsing its text into Python data structures, and then working with those structures, such as accessing dictionary keys, iterating over lists, and extracting values for processing, with error handling for files that are missing, unreadable, or malformed, as discussed earlier. A robust JSON reading routine should distinguish between those error types: a missing file may mean the program should create a new, empty dataset, or report that required data is absent, depending on whether the file is expected to exist, while a malformed file indicates data corruption or an editing error, which should be reported clearly, with the file name and, where possible, the error location, so that it can be fixed, rather than silently treated as empty, which would lose data and hide corruption. That distinction, missing versus malformed, is important, because the appropriate response differs: missing may be normal for a first run, while malformed is always a problem that needs attention, and conflating them, by treating both as empty, would hide corruption and risk data loss, where a program overwrites a malformed file with empty data, destroying the original, corrupted but potentially recoverable, contents.

Writing a JSON file involves the reverse: converting Python data structures to JSON text and writing that text to a file, with attention to atomicity, meaning that a write should either complete fully or not at all, rather than partially, leaving a file that is truncated and therefore malformed, if the program crashes or is interrupted during writing. Atomic writing can be achieved by writing to a temporary file first, then replacing the original file with the temporary one in a single, atomic operation, so that readers always see either the old, complete file or the new, complete file, never a half-written file, which is especially important for application data files, such as PyDay's data file, where a crash during writing could corrupt the user's tasks and notes, if writing were done directly to the original file. That atomic pattern, write temporary then replace, is a standard technique for safe file updates, and it should be used for JSON data files that are important, even in small programs, because the cost of implementing it is low, while the cost of a corrupted data file, in lost user data and trust, is high, and because crashes during writing, though rare, do occur, especially on mobile devices or on systems that lose power unexpectedly.

JSON writing also involves handling data types that JSON does not support directly, such as dates, which, as discussed in the earlier JSON chapter, must be converted to text in a standard format before writing, and converted back to dates after reading, with consistent formatting, so that dates round-trip correctly, without losing information or changing meaning, such as a date written in a locale-specific format that cannot be parsed back reliably.

Check: Why should JSON data files be written via a temporary file and then replaced, rather than written directly?

Section 16: Choosing Between CSV and JSON

Choosing between CSV and JSON for a dataset depends on the data's structure, its intended use, and its audience, and making that choice deliberately, rather than by habit, ensures the format serves the data, rather than forcing the data into an ill-fitting format that complicates handling. CSV is best for flat, tabular data, where each record has the same fields, in the same order, and where the data will be viewed in a spreadsheet, analysed with tabular tools, or exchanged with systems that expect tables, because CSV's simplicity, its spreadsheet compatibility, and its row-per-record structure make it ideal for such data, and because its flatness is not a limitation when data is genuinely flat, but a virtue, keeping files simple and readable. JSON is best for structured, nested, or variable data, where records may have different fields, where fields may contain lists or nested objects, or where data will be used by programs that need to preserve structure, because JSON's objects, arrays, and nesting represent such data naturally, without flattening or splitting, and because its type system, distinguishing numbers, text, true-or-false, and null, preserves type information that CSV, as text-only, loses, requiring conversion on reading, as discussed earlier.

Audience also matters in format choice: if the audience is primarily people using spreadsheets, CSV is more accessible, because it opens directly in spreadsheet software, with columns and rows visible, without needing a JSON viewer or a program to parse it, while if the audience is primarily programs, JSON is more convenient, because programs can parse it directly into structured data, without needing to infer types or to handle flatness limitations. If the audience includes both, you may need to provide both formats, by converting between them, with documented transformation rules, as discussed earlier, so that each audience gets a format suited to its tools, rather than forcing one audience to use an ill-fitting format. That dual-format provision, though it adds maintenance, is sometimes necessary for shared datasets, where different users have different tools and needs, and where providing only one format would exclude or inconvenience a significant group, reducing the dataset's usefulness and adoption.

Data size and performance also influence format choice, though less dramatically than structure for small datasets. For most small projects, size differences are negligible, and structure and audience should dominate the choice, but for large datasets, size and parsing speed, which also differ between formats, with CSV parsing generally faster for flat data due to its simplicity, should be considered, though they should not override structure suitability, because a format that is fast but cannot represent the data correctly is not a viable choice, regardless of its speed.

Finally, format choice should be documented, with reasons, so that future readers understand why CSV or JSON was chosen, and so that they do not change the format casually, without considering the structure and audience factors that motivated the original choice. Choosing between CSV and JSON thoughtfully, based on structure, audience, and size, and documenting that choice, is a mark of professional data handling, ensuring that data files serve their purpose reliably, for their intended users, over time.

Check: For what kinds of data is CSV a better choice than JSON, and vice versa?

Section 17: Transforming Between CSV and JSON

Transforming between CSV and JSON involves converting data from one format's structure to the other's, which requires decisions about how to handle differences in their models, as discussed earlier, and those decisions should be made explicitly and documented, because different transformations produce different outputs from the same input, and a transformation that is not documented cannot be reliably reversed or reproduced. That type conversion, done per column based on documented types, is a crucial step in CSV-to-JSON transformation, because without it, JSON output will contain all values as text, requiring readers to convert them again, which is inefficient and error-prone, and because it makes the JSON file less useful for programs that expect typed values, such as numbers for calculation, which would fail or misbehave if given text.

Converting JSON to CSV involves the reverse challenges, particularly flattening nested structures and lists, which CSV cannot represent directly, into flat rows, by either selecting specific fields to include, discarding nested or list fields that cannot be flattened meaningfully, or by expanding nested structures into multiple rows or multiple files, such as creating one CSV file for parent records and another for nested items, linked by a shared identifier, similar to joining datasets, where the identifier connects parent and child rows. Choosing a flattening strategy depends on the CSV's purpose: if it is for spreadsheet viewing of top-level fields, selecting fields may suffice, while if it is for complete data exchange, multiple files with linking identifiers may be necessary, and that purpose should be documented alongside the transformation, so that readers understand the CSV's scope and limitations.

Transformation also involves handling missing values, empty lists, and empty objects, which appear differently in CSV and JSON, and which must be mapped consistently, such as JSON null becoming an empty CSV field, and an empty CSV field becoming JSON null, or becoming an empty string, depending on the column's type and on whether missing is distinct from empty in the data's meaning, a distinction that should be documented, because conflating missing and empty, as discussed for CSV, can distort analysis, where missing means unknown and empty means known to be empty, which are different states with different implications for calculations, such as averages, where missing should be excluded and empty, if it means zero, should be included, though in practice, empty CSV fields are often interpreted as missing, and that interpretation should be consistent across transformations, to avoid a value changing meaning when converted from one format to the other, which would be a transformation bug that is hard to detect, because the file would still parse correctly, but its meaning would have changed silently.

Finally, transformations should be tested with round-trip checks, where data converted from CSV to JSON and back to CSV, or vice versa, is compared with the original, to see what information is lost or changed, and whether that loss is acceptable for the transformation's purpose, or indicates a bug, such as a field dropped unintentionally, a type changed incorrectly, or a nested structure flattened in a way that cannot be reversed, losing information that was intended to be preserved.Documenting the chosen mapping in a short note beside the conversion script saves future readers from guessing what was dropped or changed.

Check: What information can be lost when converting nested JSON to flat CSV, and how should that loss be documented?

Section 18: Handling Large Data Files

Handling large data files, which may contain hundreds of thousands or millions of rows, requires different techniques from handling small files, because loading a large file entirely into memory, as is convenient for small files, may exhaust memory, slow the program, or make it unresponsive, especially on computers with limited memory, such as laptops or phones, where memory is shared with other applications. Streaming, processing rows one at a time, or in small batches, rather than loading all rows at once, is the key technique for large files, because it keeps memory use constant, regardless of file size, by holding only the current row or batch in memory, processing it, and then discarding it, before reading the next, which allows programs to handle files far larger than memory, provided they do not need random access to all rows simultaneously, but only sequential processing, such as filtering, counting, summing, or writing a transformed output, all of which can be done streaming, row by row, without holding the entire dataset. Streaming is therefore the default approach for large CSV files, and it should be used whenever the task allows sequential processing, which includes many common tasks, such as cleaning, filtering, aggregating by group with a dictionary of summaries, and converting formats, all of which can be done with bounded memory, by updating summaries as rows arrive, rather than storing all rows.

For tasks that require random access, sorting, or grouping that cannot be done streaming with bounded memory, such as sorting a large file, which, as discussed earlier, typically requires all rows, or joining two large files, which may require indexing one file, large file handling becomes more complex, and may require external sorting, where data is sorted in chunks that fit in memory and then merged, or database techniques, where data is stored in a database that can sort, index, and join efficiently, using disk storage and optimised algorithms, rather than in-memory Python structures, as covered in the next chapter on databases. For most learning projects, files are small enough for in-memory handling, and streaming is sufficient for large-file awareness, but understanding the limits, and knowing that databases exist for larger scales, prepares you for real-world data work, where file sizes vary widely and the right tool depends on scale.

Large JSON files pose similar challenges, with an additional complication: JSON structure is nested, and parsing a large JSON file typically requires loading its entire structure into memory, because the parser must read the whole file to understand its nesting, unlike CSV, where rows can be streamed line by line, though streaming JSON parsers exist that can process JSON incrementally, by reading objects or array elements one at a time, without loading the whole file, which is useful for large JSON arrays of records, where each element can be processed independently, similar to CSV rows.

Check: Why is streaming rows one at a time better than loading a whole large CSV file into memory?

Section 19: Data Errors and How to Report Them

Data errors, such as malformed rows, invalid values, missing required fields, or duplicate identifiers, should be reported clearly, with enough context for a user to fix them, rather than being silently ignored, which hides problems, or crashing the program with a cryptic message, which frustrates users and gives them no guidance on how to fix the data. A good data error report includes, for each error, the file name, the row number, the column or field name, the offending value, and the rule violated, such as a quantity that is negative when it should be positive, or a date that is in the wrong format, so that a user can locate the error in the source file, understand why it is an error, and correct it, without needing to guess or to search through code. That report format, file, row, column, value, rule, is a standard for data validation reporting, and it should be used consistently, whether errors are reported to the terminal, to a log file, or to a separate error file, so that users learn to read reports quickly and to fix data efficiently, rather than struggling with varying report formats that require relearning for each program.

Error reporting should also summarise, at the end of processing, how many rows were processed, how many succeeded, how many failed, and how many errors of each type occurred, so that a user can assess the overall data quality at a glance, without needing to count individual error lines, and so that they can decide whether to fix all errors and reprocess, or whether the good rows are sufficient for their purpose, with bad rows reported for later fixing. Summaries also help with trend analysis, where error counts over time, or across files, reveal whether data quality is improving or worsening, which is useful for data providers who need to improve their data collection or export processes, based on error patterns, rather than on anecdotal reports of individual errors.

Errors should be categorised by severity, where possible, distinguishing between errors that prevent processing, such as a malformed row that cannot be parsed, and warnings for data that is unusual but usable, such as a value that is within range but at an extreme, or a field that is missing but optional, so that users can prioritise fixing blocking errors first, while noting warnings for later review, rather than treating all issues as equally urgent, which can overwhelm users and cause them to ignore all reports, including critical ones, a phenomenon similar to warning fatigue in logging, where too many warnings desensitise readers to genuine problems. That consistency, in error reporting as in other aspects of data handling, makes programs easier to use and data easier to fix, because users develop familiarity with the report format and with the severity categories, rather than facing a new format for each dataset.

Finally, error reports should be saved, where appropriate, to a file, rather than only displayed on the terminal, so that they can be reviewed later, shared with data providers, or used as a checklist for fixing data, without needing to re-run processing to regenerate them, which may be time-consuming for large files, and which may produce different reports if data changes in the meantime.

Check: What information should a data error report include for each bad row, and why?

Section 20: Data Handling in the PyDay Capstone

PyDay, the capstone project, uses JSON for its data file, as described in the outline, storing tasks and notes in a single JSON file, with tasks and notes as arrays of objects, each with fields for its content and status, and that choice, JSON over CSV, reflects the data's structure, as discussed for format choice: tasks and notes have varying fields, such as a task having a completion status that a note does not, and notes having text that may contain commas, line breaks, and other characters that CSV quoting would handle but that JSON handles more naturally, with nesting and with type preservation for true-or-false completion status, which CSV would store as text and require conversion. That format choice, documented in PyDay's design, illustrates the principles from this chapter applied to a real, though small, project, where format is chosen based on structure, not by habit, and where JSON's advantages, nesting, typing, and readability for small files, outweigh CSV's advantages, spreadsheet compatibility and compactness for flat data, which PyDay does not need, because its data is not flat and is not primarily viewed in a spreadsheet, but is managed through its own command-line interface, which reads and writes JSON directly.

PyDay's data handling also illustrates atomic writing, error handling, and validation, as discussed in this chapter, though in simplified form appropriate for a small project. It validates input, such as ensuring a task title is non-empty, before adding it, and it handles a missing data file gracefully, by treating it as empty, while handling a malformed data file by reporting an error, rather than silently overwriting it with empty data, which would lose the user's tasks and notes, a distinction between missing and malformed, discussed earlier, that is critical for user data safety, even in a small project, where data loss would be distressing to the user, even if the data volume is small. Those handling decisions, though simple, reflect professional data handling principles, applied at capstone scale, where correctness and safety matter as much as for larger projects, because user data, even in a personal tool, deserves protection against loss and corruption.

PyDay's data handling also shows how CSV and JSON can coexist in a single program, even when the main data file is JSON, because PyDay could, in a future extension, export its tasks to CSV for spreadsheet viewing, or import tasks from CSV, by transforming between formats, as discussed earlier, with documented transformation rules, such as how task fields map to CSV columns, how completion status is represented in CSV, and how missing values are handled, so that export and import are reliable and reversible where possible.

PyDay also shows why data handling must be tested with the actual file, not just with in-memory examples. A test that creates a temporary data file, adds tasks through the program's functions, reloads the file, and checks that tasks persist, verifies the whole storage cycle, not just the logic for adding to a list. That cycle test catches file-format bugs, such as a field written with the wrong name, that in-memory tests would miss, because they never touch the file.

Check: Why does PyDay use JSON rather than CSV for its task and notes data file?

Section 21: Dates and Times in Data Files

Dates and times in data files require consistent formatting, because they can be written in many ways, such as year-month-day, day-month-year, month-day-year, with different separators, with or without time, and with or without timezone information, and a program that reads dates must know which format to expect, or must be able to detect it reliably, otherwise it will misinterpret dates, such as swapping day and month, producing a wrong date that looks valid, as discussed for conversion errors, where a swapped date may still be a valid calendar date, but with the wrong meaning, leading to incorrect sorting, filtering, or analysis based on dates, without an obvious error to alert the user. To prevent that, choose a single, unambiguous date format for a dataset, preferably the year-month-day format, which sorts correctly as text as well as a date, because its most significant component, the year, comes first, followed by month, then day, making chronological order match alphabetical order, a useful property for sorting and for human reading, where year-first dates are clear and unambiguous, unlike day-first or month-first formats, which are ambiguous without knowing the locale or the convention used, and which can be misread by readers from different regions who assume a different order.

Times add further complexity, with formats for hours, minutes, and seconds, with 12-hour versus 24-hour clocks, with timezone offsets, and with daylight saving transitions, all of which can affect interpretation, especially for data collected across timezones, where a time recorded without timezone information is ambiguous, because it could refer to different absolute moments depending on the timezone assumed, and where a time recorded during a daylight saving transition may be ambiguous or non-existent, depending on local rules. For most small datasets, times can be stored as simple text in a consistent format, such as 24-hour time without timezone, if all times are in the same timezone and timezone is documented, but for datasets spanning timezones, or for applications where exact moments matter, such as logs or events, timezone information should be included, either as an offset, such as +01:00, or as a named timezone, with a documented convention, so that times can be compared and converted correctly, rather than assumed to be in the reader's local timezone, which may differ from the writer's, causing systematic errors in time-based analysis, such as events appearing to occur at the wrong time, or in the wrong order, when sorted by local time without timezone conversion.

Date and time validation should check not only format, but also plausibility, such as whether a date exists on the calendar, whether a time is within a valid range, and whether a date falls within an expected range for the dataset, such as not in the future for historical data, or not before a project's start date, where such constraints are known, because format-valid dates can still be implausible, such as a birth date in the future, or a transaction date before a business existed, which likely indicates a data entry error, such as a typo in the year, that should be flagged, rather than accepted as valid because its format is correct.

Check: Why is year-month-day a better date format for data files than day-month-year or month-day-year?

Section 22: Handling Empty and Missing Data

Empty data is not a single problem, because it takes several different forms and each form calls for its own response. An empty file has no rows, while an empty row is a blank line in a file that otherwise holds data. An empty field is one missing value in an otherwise complete row, and a missing column means a whole header is absent. Treating all these cases the same way will cause trouble, since some are harmless and others signal serious failure. Your first job, therefore, is to work out exactly which case you are facing before you decide what to do.

An empty field can mean unknown, not applicable to that row, or simply not supplied yet. Those meanings call for different handling, so a blank should never be given one fixed meaning for every column. An unknown value should usually be left out of calculations, rather than counted as zero, because zero is a real value that would change a total. A value that does not apply should be recorded separately from an unknown value, so readers can tell them apart. A late value can simply be left blank until it arrives, provided the program can cope with the gap. Treating every blank as zero will distort your totals without warning, while treating every blank as an error will reject rows that are otherwise useful. Decide what a blank means for each column, and write that decision down so that it is applied consistently.

Missing columns and missing files also need a decision based on whether the item is required or optional. A required missing column should stop processing with a clear message naming it, since no correct result is possible without it. A missing optional column can be skipped, with a warning that the output is incomplete in that respect. The same distinction applies to a missing file, and it is the key to the question of first runs versus data loss. On a first run, a task manager may have no data file yet, because the user has not added any tasks. In that case, start with an empty dataset and create the file when the first record is saved. If a file that should already exist is suddenly missing, however, that is a different situation, and the program should report it as an error rather than silently starting again. To tell the two cases apart, check whether the file has ever existed, for example by looking for a backup or a log entry from an earlier run.

import csv

with open("tasks.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        if not any(value.strip() for value in row.values()):
            continue
        print(row.get("title") or "Untitled")

Empty rows should usually be skipped in this way, with skipped rows counted in the final report. Handling empty and missing data well, in short, means naming each case, choosing a response for it, and making that response visible to the user.

Check: How should a program distinguish between a missing data file that is normal on first run and one that indicates data loss?

Section 23: Combining CSV and JSON in One Program

Combining CSV and JSON in one program is common, because different parts of a program may use different formats for different purposes: CSV for tabular reports, exports, or imports from spreadsheets, and JSON for application data, configuration, or structured records, and a program that handles both must manage their different structures, types, and error handling consistently, without confusing them, such as by applying CSV parsing to JSON data, or vice versa, which would fail, or by assuming that data converted between formats preserves all information, when, as discussed for transformations, conversion may lose nesting, types, or missing-value distinctions, unless handled carefully. A program that combines formats should therefore have separate, well-tested functions for each format, one set for CSV reading, writing, validation, and conversion, and another set for JSON reading, writing, validation, and conversion, with clear names indicating which format each function handles, so that callers cannot mistake one for the other, and so that format-specific logic, such as CSV quoting or JSON nesting, is contained within its own functions, rather than mixed across the program, where it would be harder to maintain and to test, and where a change to one format's handling might inadvertently affect the other.

Combining formats also involves deciding on a canonical internal representation, meaning the Python data structures that the program uses internally, regardless of input or output format, such as representing records as dictionaries with typed values, where numbers are numbers, dates are date objects, and missing values are a consistent missing marker, so that processing logic, such as filtering, sorting, aggregating, and validating, can operate on that canonical form, without needing to know whether the data came from CSV or JSON, or whether it will be output as CSV or JSON. That boundary pattern, format-specific code at input and output, format-agnostic code in the middle, is a general design principle for data programs, similar to separating core logic from command-line handling, discussed earlier, where boundaries handle format and interface details, while core logic handles the task itself, making both easier to test and to maintain, because format changes affect only boundary code, not core logic, and core logic changes affect only internal processing, not format handling.

When combining formats, also consider consistency in error handling, logging, and user messages, so that a user who encounters an error in CSV processing receives a report in the same style, with file, row, column, value, and rule, as a user who encounters an error in JSON processing, though JSON errors may be reported by object, field, or path, rather than by row and column, because JSON's structure is nested, not tabular, and its error locations must be described in terms of paths, such as tasks[2].title, rather than row and column, to pinpoint the faulty value within nested data.

Check: What is a canonical internal representation, and why does it simplify a program that handles both CSV and JSON?

Section 24: Data Quality Checklist

A data quality checklist is a set of checks that a dataset should pass before it is considered reliable for use, covering completeness, accuracy, consistency, timeliness, and validity, among other dimensions, and using such a checklist systematically, rather than relying on intuition, ensures that data quality is assessed thoroughly, not just for the problems you happen to notice, but for all important dimensions, including those that are easy to overlook, such as timeliness, where data may be accurate but outdated, or consistency, where data may be accurate in isolation but inconsistent across files, such as a customer name spelled differently in two files, causing joins to fail or to produce duplicate entries for the same customer. A checklist for CSV and JSON data might include: are all expected files present, are headers or schemas correct, are row counts plausible, are required fields present and non-empty, are values in expected formats and ranges, are identifiers unique where required, are dates valid and in order where order matters, are duplicates handled, are missing values handled consistently, and is the data current, meaning its date range covers the period it claims to cover, without gaps or outdated records that should have been updated? Each item on the checklist corresponds to a validation or a manual check, and together they form a comprehensive assessment that can be documented, with pass or fail for each item, and with actions for failures, so that data quality is not just a feeling, but a measured, reported property of a dataset.

Completeness, as a checklist dimension, asks whether data is missing in ways that affect its usefulness, such as missing rows for certain periods, missing columns for certain records, or missing values for required fields, and it should be assessed by comparing actual counts and coverage with expected counts and coverage, based on the data's source and purpose, such as expecting a daily file to contain one row per day, or expecting a customer file to contain one row per customer, with deviations flagged for investigation, rather than assumed to be normal, because a missing day or a missing customer could indicate a collection failure, an export error, or a filtering mistake, each of which affects analysis differently, and each of which should be understood before proceeding, rather than proceeding with incomplete data and drawing conclusions that do not account for its gaps, which would be misleading, especially if gaps are systematic, such as missing data for a particular region or period, which would bias analysis towards regions or periods with complete data, without the analyst realising it, unless completeness is checked explicitly.

Accuracy, as a dimension, asks whether values are correct, not just well-formatted, which is harder to check automatically, because it requires comparing data with a trusted source, or with reality, rather than just checking format and range, though format and range checks catch many accuracy problems, such as typos that produce out-of-range values, or format errors that indicate a value was entered incorrectly.

Check: What dimensions should a data quality checklist cover beyond just format correctness?

Section 25: Privacy in Data Files

Privacy in data files involves protecting personal information, such as names, addresses, phone numbers, email addresses, identification numbers, health information, and financial details, from unauthorised access, accidental disclosure, or misuse, when handling CSV and JSON files that contain such data, because data files are often shared, copied, backed up, and stored in ways that increase exposure, similar to logs, discussed earlier, where sensitive data in logs becomes a risk when logs are shared or archived. A first privacy principle for data files is minimisation, as with logs: include only personal data that is necessary for the file's purpose, and no more, so that a file for analysing sales by city, for example, does not need to include customer names or addresses, only city and sales totals, or anonymised customer identifiers, rather than full personal details, which would expose customers unnecessarily, even if the file is only shared within a team, because team members who do not need personal data should not have access to it, and because a file shared within a team may later be shared more widely, or may be included in a backup that is less protected, increasing exposure beyond its original audience. Minimisation reduces both privacy risk and file size, and it should be applied at file creation, by selecting only necessary columns, as discussed for column selection, rather than including all available columns by default, which is a common cause of unnecessary personal data exposure in data files.

A second principle is anonymisation and pseudonymisation, where personal identifiers are replaced with fictional or coded values that preserve analytical usefulness without revealing identity, such as replacing names with sequential identifiers, like customer-001, customer-002, for analysis that needs to distinguish customers but does not need to know who they are, or aggregating data to a level where individuals cannot be identified, such as reporting sales by city rather than by individual customer, which preserves city-level insights while protecting individual privacy. Anonymisation must be done carefully, because data that appears anonymous may still identify individuals when combined with other data, such as a rare combination of city, age, and purchase that uniquely identifies one person, even without a name, a phenomenon known as re-identification through quasi-identifiers, which requires considering not just direct identifiers, like names, but also indirect identifiers, like combinations of attributes that are unique, and aggregating or suppressing such combinations where they risk identification, especially for small groups, where a single record may be identifiable by its attributes alone, even without a name.

Privacy also means thinking about who receives a data file and for how long they keep it. When you share a dataset, even within a team, note its sensitivity, who should have access, and when it should be deleted, so that sharing does not become indefinite retention by default. A short note accompanying a shared file, stating its sensitivity and retention expectation, helps recipients handle it appropriately, rather than assuming it can be kept forever or forwarded freely, assumptions that increase exposure risk over time.

Check: Why should test and example data files use fictional data rather than real personal data?

Section 26: Backing Up Data Files

Backing up data files means creating copies of them in a separate location, so that they can be recovered if the original is lost, corrupted, or accidentally deleted, and it is essential for any data that would be difficult or impossible to recreate, such as user data in an application like PyDay, where tasks and notes are entered by the user over time and cannot be regenerated from code, or for datasets that were collected through effort, such as surveys or measurements, where recollection would be costly or impossible, unlike code, which, if version-controlled, can be recovered from history, data files, especially user data and collected data, are often not version-controlled, because they change frequently, are large, or contain personal information that should not be in version control, as discussed for PyDay's data file, which is ignored, meaning that without a separate backup, loss of the data file means permanent loss of the user's data, with no recovery from Git history, because Git never recorded it, a distinction that makes backups for data files even more important than for code files, which are protected by version control, while data files require their own backup strategy.

A backup strategy for data files should consider frequency, location, and verification, similar to version control for code, but adapted to data's characteristics. Frequency should reflect how often data changes and how much loss is acceptable: for a personal task manager used daily, a daily backup, or a backup on each change, such as copying the data file to a backup location whenever it is written, may be appropriate, while for a dataset that changes rarely, a backup on each change, or a weekly backup, may suffice, with the key principle being that backup frequency should match data change frequency, so that the maximum loss, if the original is lost just before a backup, is acceptable, rather than losing weeks of work because backups were infrequent, a risk that is easy to underestimate until a loss occurs, when the gap since the last backup becomes painfully clear.

A simple backup routine for a personal tool can be as basic as copying the data file to a second folder each time it changes, or at the end of each day, with a dated name so that several versions are kept. That routine, though manual, is far better than no backup, and it can later be automated, for example by a script that copies the file on a schedule, once the manual routine proves its value. The key is to start with a routine you will actually follow, even if it is simple, rather than designing an elaborate backup system that you never implement, leaving data unprotected while you plan a perfect solution that never arrives.

Check: Why is backing up data files especially important when those files are excluded from version control?

Section 27: Testing Data Handling Code

Testing data handling code involves verifying that reading, writing, cleaning, filtering, aggregating, and transforming data all behave correctly, for typical, edge, and invalid inputs, using small, hand-crafted test files that cover the cases discussed throughout this chapter, rather than relying solely on large, real files, where errors may be hard to spot and where test results may depend on data that changes over time, making tests non-repeatable. A good data handling test suite includes tests for reading, where a test file with known rows, including tricky values like quoted commas, is read and its rows compared with expected rows, to verify parsing, quoting, and type conversion; tests for writing, where data is written and then read back, to verify round-trip correctness, as discussed earlier; tests for cleaning, where messy input is cleaned and its output compared with expected cleaned output, to verify cleaning rules; tests for filtering and aggregation, where small datasets with known filter results and group totals are used, to verify logic by hand calculation, similar to testing functions with hand-calculated examples, where expected values come from independent calculation, not from running the code being tested, which would make tests pass by definition, as discussed for expected values in testing. Those tests, with small, controlled data, are fast, repeatable, and precise, because they isolate each operation's logic, without depending on large files or on external data that may change, and they catch bugs early, when they are cheap to fix, rather than after data handling is integrated into a larger program, where a bug's cause is harder to isolate among many operations.

Testing data handling also involves testing error handling, where test files with malformed rows, missing columns, invalid values, and empty files are used, to verify that the program reports errors clearly, with file, row, column, value, and rule, as discussed for error reporting, and that it handles errors as documented, whether by skipping bad rows, rejecting the file, or using defaults, rather than crashing with a cryptic message, or silently accepting bad data, both of which are failures that tests should catch, by asserting on error reports and on output counts, such as asserting that a file with one bad row and nine good rows produces nine output rows and one error report, not ten output rows including the bad one, nor zero output rows due to a crash on the bad row.

Data tests should also cover the boundaries between formats, such as a CSV value that becomes a JSON number, or a JSON list that becomes a CSV field, because those boundaries are where type and structure changes occur, and where bugs hide. A test that converts a small dataset from one format to the other and back, comparing the result with the original, reveals conversion losses quickly, before they affect real data, and it documents what the conversion preserves and what it does not, which is essential information for users of converted data.

Check: Why should data handling tests use small, hand-crafted temporary files rather than large real files?

Section 28: Logging Data Processing

Logging data processing means recording, through the logging system discussed in the debugging chapter, what a data program did, how much data it processed, what problems it encountered, and what results it produced, so that processing can be monitored, audited, and debugged, without needing to re-run it or to inspect its output files alone, which may not reveal how processing proceeded, such as which rows were skipped, why they were skipped, or how long each step took. A data program's logs should include, at info level, major steps, such as starting to read a file, finishing reading with a row count, starting cleaning, finishing cleaning with counts of cleaned values, starting aggregation, and finishing with group counts, so that a reader can follow the pipeline's progress and can see, at a glance, whether it completed normally, and, if not, at which step it failed or stopped, which is far more informative than a program that runs silently and either produces output or fails without explanation, leaving users to guess whether it succeeded, partially succeeded, or failed, and, if it failed, where and why, information that logs provide directly, without requiring re-running with debugging prints, which would be inefficient for large files, where re-running takes significant time, and where logs from the original run, if preserved, already contain the needed information, provided logging was thorough enough.

Logging for data processing should also include, at warning level, unusual but handled situations, such as rows skipped due to missing values, values cleaned due to formatting inconsistencies, or files with unexpected headers that were handled by defaults, with counts and, where relevant, examples, such as the first few skipped rows' identifiers, so that users are aware of data quality issues, even when processing succeeds, because those issues may affect results, such as a count that excludes skipped rows, and users need to know that exclusion occurred and why, to interpret results correctly, rather than assuming all input rows were included, when in fact some were skipped, leading to understated totals or biased analysis, as discussed for filtering, where exclusion must be visible, not hidden.

For data pipelines that run on a schedule, logs become the main way to know whether a run succeeded, how long it took, and whether data quality changed since the last run. Comparing log summaries across runs, such as row counts, error counts, and totals, can reveal trends, such as a gradual increase in skipped rows, indicating deteriorating source data quality, even if no single run fails outright. That trend awareness, gained from routine log review, turns logs from a debugging aid for failures into a monitoring tool for quality over time, which is valuable for any recurring data process.

Check: What should data processing logs include at info, warning, and error levels respectively?

Section 29: Common Data Handling Pitfalls

Data handling pitfalls are mistakes that recur across many programs and datasets, and learning to spot them in advance will save you from wrong results that look correct. This section covers five of the most common pitfalls and a practical way to avoid each one. The first pitfall is treating CSV text as numbers without converting it first. CSV stores every value as text, so adding two values that look like numbers may join them as strings instead, giving a string result rather than a sum. The fix is to convert each numeric column explicitly, with a clear rule for blank or invalid values, and to test the conversion with a small example whose answer you already know. The second pitfall is closely related, and it is sorting numeric values as text. Text sorting compares characters from left to right, so it places 10 before 2, which is correct for words but wrong for quantities. Avoid this by converting values to numbers before sorting, and by testing your sort with values such as 2, 10, and 100 that expose the difference.

The third pitfall is assuming that real data will be as clean as your test data. Real files often contain extra spaces, mixed capitalisation, missing values, and malformed rows, and a program that only handles clean input will crash or produce wrong totals on the first real file it meets. Avoid this by building messy examples into your tests on purpose, including rows with stray spaces, varied capitalisation, and blanks, and by cleaning and validating input before you use it. The fourth pitfall is losing information when you change formats. Flattening nested JSON into a flat CSV table, for example, can discard the nesting, and converting dates carelessly can lose the time zone or the time of day. Avoid this by checking what each conversion preserves and what it drops, and by testing a round trip from one format to the other and back. Document any loss, so that nobody treats an export as a complete copy of the original. The fifth pitfall is mishandling missing values, especially by treating every blank as zero. A blank usually means unknown, not zero, so counting it as zero will distort counts, sums, and averages without producing an error to warn you. Avoid this by excluding unknown values from calculations, by reporting how many values were missing, and by keeping missing, zero, and not applicable as three separate ideas in your code.

values = ["2", "10", "100"]
numbers = [int(value) for value in values]
print(sorted(numbers))

That short example shows the second pitfall and its fix in miniature, because converting before sorting gives the numeric order 2, 10, and 100. Pitfalls like these rarely announce themselves with a crash, which is why they are dangerous. A wrong total, a wrong order, or a silently incomplete export can all look plausible, so the defence is always the same: convert explicitly, test with revealing examples, validate messy input, and check what survives a format change.

Check: What are five common data handling pitfalls, and how can each be avoided?

Section 30: A Data Handling Workflow

A data handling workflow is a repeatable sequence of steps for processing a dataset, from obtaining it to producing a final, verified output, and following such a workflow consistently, rather than handling data ad hoc, ensures that steps are not missed, that quality is checked, and that results are reproducible, which is essential for reliable data work, whether for a small personal project or for a larger, shared analysis.

A workflow also needs a clear starting point for each run: which input file, which version, and which parameters were used, so that a run can be reproduced later if its output is questioned. Record those inputs in the log or in the output's metadata, rather than relying on memory, because memory fades and input files change, making reproduction impossible without a record. That record, even a single log line stating input file, date, and key parameters, transforms a run from an anecdote into a reproducible step that can be checked, repeated, and audited, which is essential for data work that supports decisions, where reproducibility is not a luxury but a requirement for trust.

Verification should also include comparing output against independent checks, not just against the program's own logic, such as checking a total against a known total from another source, or spot-checking a sample of output rows against source rows by hand, to catch systematic errors that internal checks, which use the same logic as the program, might miss, because they share the program's assumptions, including any wrong ones, while independent checks, based on a different method or source, can reveal those assumptions as errors, by producing a different result that prompts investigation, rather than confirming a wrong result through shared logic, a confirmation bias that internal checks alone cannot avoid, and that independent verification, even for a sample, mitigates, by providing an external anchor for correctness.

Finally, a workflow should end with a decision about what to do with its output: share it, archive it, or discard it, with each option documented, so that output does not linger in ambiguous states, such as a file in a working folder that may or may not be final, leading to confusion about which file is authoritative, a confusion that versioning and clear output naming, such as including date and version in output file names, prevents, by making final outputs identifiable at a glance, rather than requiring inspection of file contents or timestamps to guess which file is latest, a guessing game that wastes time and risks using outdated output, while clear naming and archiving, with final outputs moved to a designated output folder and intermediate outputs cleaned up, keeps a data workspace tidy and trustworthy, ready for the next run, without clutter from previous runs that obscures current work.

Check: What steps should a repeatable data handling workflow include, from obtaining data to verifying output?

Section 31: Data Formats and Standards

A data format is an agreement about how information is stored, and choosing a format also chooses who can read that information without extra work. A standard format is one that many tools already understand, so CSV serves as a standard for flat tables while JSON serves as a standard for structured records. Both formats can be opened by spreadsheets, text editors, and programs written in many different languages, which makes them easy to inspect, share, and reuse. That wide support is their main advantage, and it is the reason standards are worth adopting even when a custom layout looks simpler at first.

A custom format may fit one small program very neatly, yet it creates new work as soon as another reader needs the same data. A colleague may need to inspect the file by hand, another tool may need to import it, and a different system may need to exchange it regularly. Each new reader then needs new code, new tests, and new documentation, and each of those steps is a chance for a bug to appear.

Adopting a standard at the start avoids that growing cost, because you can use existing readers and writers instead of writing and maintaining your own parser. Those tools already handle tricky cases, such as quoting and line breaks in CSV, and nesting and escaping in JSON, which are easy to get wrong by hand. A custom format, by contrast, must be learned entirely from your own notes, which may be incomplete, outdated, or missing when they are needed most.

Changing format midway is far more expensive than choosing well at the start, because old files must be converted or kept readable. Old code must then handle both formats, and readers that expect the old layout may break silently by reading the wrong column or missing a field. You end up supporting two formats instead of one, which is why the format decision belongs in the design phase rather than being left as a detail to fix later. Choose the standard that fits your data: CSV for a flat table with the same columns in every row, and JSON for records with nested parts or lists.

Document the choice once you make it, recording the format, the text encoding, and the meaning of each field in a short note. Note how missing values are shown, which fields are required, and what units or date order are used, because those details prevent misreading even when the format itself is standard. A short readme note helps future readers and reminds you of details that fade over months. A standard needs little code, as this example shows:

import csv

with open("sales.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.DictReader(f))
print(rows[0]["product"])

That code works because CSV reading is a solved problem, and you did not need to invent a parser or explain the layout to every tool. Standards save effort in exactly this way, turning data exchange into routine work and letting you focus on what the data means. Choose a standard early, use it consistently, and change it only with a clear plan for old files and old readers.

Check: Why is it better to adopt a standard data format at project start than to invent a custom format midway?

Section 32: Exporting Reports from Data

Exporting reports from data means producing a human-readable summary, such as a table, a list, or a text report, derived from processed data, for users who need insights, not raw rows, and it differs from exporting raw data, such as writing a filtered CSV file, because a report is designed for reading and decision-making, with headings, totals, groupings, and explanations, rather than for further processing, where raw data, with headers and consistent types, is more appropriate, though reports themselves may be stored as CSV or as text, depending on their audience, with CSV reports suited to spreadsheet viewing and further analysis, and text reports suited to reading, printing, or emailing, where formatting, such as aligned columns, headings, and footnotes, matters more than machine readability, and where the report's design, including what to include, what to emphasise, and what to explain, affects how users interpret its findings, making report design a communication task, not just a data task, where clarity, accuracy, and honesty about limitations, such as noting that totals exclude missing rows, or that a period is incomplete, are essential for preventing misinterpretation, which is a common problem with reports that present numbers without context, leading users to draw conclusions that the data does not support, such as comparing totals from periods with different completeness, without noting the difference, which would be misleading, even if the numbers themselves are correct, because their context differs, and context is part of a report's truth, not just its numbers. Report generation should be automated, where possible, by writing a program that reads data, processes it, and writes a report, rather than by manually copying numbers into a document, which is error-prone, because manual copying introduces typos, outdated numbers when data changes, and inconsistencies between report and data, which automated generation avoids, by deriving report numbers directly from data, each time the report is generated, ensuring that the report always matches the current data, provided the generation program is correct, which should be tested, as with other data code, by comparing generated reports with hand-calculated expected reports for small test datasets, to verify that totals, groupings, and formatting are correct, before trusting automated reports for large datasets, where hand verification is impossible, and where a generation bug, such as a wrong grouping key or a missing filter, would produce a plausible but wrong report, which, if shared without verification, could mislead decisions, a risk that automated testing, with small, known datasets, mitigates, by catching generation bugs during development, when they are cheap to fix, rather than after reports are shared, when they have already influenced decisions, which may be difficult to reverse, especially for reports used for planning, budgeting, or public communication, where wrong numbers can have real consequences beyond embarrassment, depending on the report's purpose and audience. Report formatting should be consistent, clear, and accessible, with headings that describe each section, with numbers formatted consistently, such as with the same number of decimal places, with thousands separators where appropriate for readability, though not.

Check: Why should a report include metadata about its source, period, and limitations, not just its numbers?

Section 33: Importing Data from Users

Importing data from users differs from reading your own files, because you control your own files but you do not control a file a user supplies. A user file may use a different layout, have missing columns, or contain values your program does not expect, so a good import must check the file and guide the user. It should never leave the user guessing what went wrong, and it should never save bad data silently before the problem is understood.

The first rule is to check the file before using any of it, starting with required headers, row lengths, and value types. Do these checks early, before any data is saved, because a partial import is hard to undo and hard to explain to the user. If a check fails, stop and explain the problem clearly, rather than continuing with data that may already be wrong.

The second rule is to write error messages for the user, not for the developer, because a traceback is a debugging tool and not guidance for fixing data. A user who sees a long traceback will often give up, while a user who sees a clear message can act on it at once. Name the row and the column where the problem occurred, show the value that caused it when that is safe, and say what the value should look like. For example, report that row 12, column quantity, holds the value "ten" and needs a whole number such as 10, which tells the user where to look and how to fix it.

Keep technical detail in a log, not on the screen, because the screen should hold only what the user needs to act. A log can hold the full error for later debugging, and you can ask for it when a user reports a problem, without showing it to every user by default. It keeps the interface calm, specific, and focused on the next step, rather than overwhelming the user with information meant for developers.

Importing also needs limits and safeguards, because a very large file can exhaust memory or time, and unexpected content can cause odd behaviour. Set a reasonable size limit and state it plainly, reject files far outside the expected shape, and warn the user before discarding rows rather than dropping them silently. Tell the user how many rows were accepted and how many were rejected, because that summary builds trust even when an import fails. Treat an imported file as untrusted input as well, validating and cleaning values before use, and never running anything contained in it.

Finally, test imports with realistic user files, not only clean samples, including a file with a missing header and one with blank rows. Check that each case gives a helpful message and a clear path to retry, because importing is often the first thing a new user tries. A helpful import is one where the user can fix the file and try again without asking for help, and that standard is worth aiming for. When the import succeeds, confirm it with a short summary, so the user knows the work is done and can spot a problem such as far fewer rows than expected.

Check: Why should import error messages be written for users, with guidance, rather than showing technical tracebacks?

Section 34: Data Versioning and Change Tracking

Data versioning means keeping track of how a dataset changes over time, by saving a snapshot of the data at each important point. When you update the data, you keep the old snapshot as well as the new one, and you record what changed and why that change was made. That record is what makes recovery possible, because without it a bad update can be very hard to undo.

Consider a common failure, in which you clean a file and overwrite the original, only to notice later that the totals look wrong. Perhaps a cleaning rule changed a value it should not have changed, or a column was renamed by mistake, and the original file is now gone. Without the original, you cannot compare old and new rows to find exactly what changed, and you may have to rebuild the data from scratch.

Versioning prevents that loss in a simple way, by saving a copy of the current file before any update is applied. Give the copy a clear name that includes the date, such as sales-2026-10-01.csv, and keep it in a separate folder that is not overwritten. Then apply the update to the working copy, check the results, and keep the backup until the new version is proven. If the update proves wrong, you can restore the old copy in full, or compare the two versions row by row to locate the error.

A simple naming scheme is enough for many small projects, provided dated copies are never overwritten and each version has a short note. A note might say that duplicates were removed, that a new column was added, or that values were converted to a standard date order. That note helps future readers understand the history, and it helps you decide which version to restore when a problem appears.

For larger or shared datasets, a dedicated tool may help, because some tools track changes line by line in much the same way as code version control. Others store snapshots with metadata about each change, which makes it easier to find a particular version among many. The right tool depends on file size and team size, and a small personal project does not need a complex system. A shared dataset used by several people, however, may benefit greatly from one, because it prevents one person's update from silently overwriting another's work.

Versioning also helps you test an update before committing to it, by making the change on a copy first and checking row counts before and after. Check totals for key columns, and check a sample of rows by hand, discarding the new version if those checks fail and keeping the old one. This habit turns updates into reversible steps, so a mistake becomes a setback rather than a disaster, and it gives you confidence to improve data without fear.

A single copy is the core of the practice, as this short example shows:

import shutil

shutil.copy("sales.csv", "sales-2026-10-01.csv")
print("Backup saved before update.")

That copy, labelled and noted, is what makes a bad update recoverable, and it is a habit worth building into every data update you make.

Check: How does data versioning help you recover from an update that introduced errors?

Section 35: Documenting Data Formats

Documenting a data format means writing down what a file contains and what it means, because structure alone is not enough for safe use. A list of column names tells a reader what fields exist, but it does not tell them what the values mean, which codes are valid, or what the data cannot show. Those meanings matter just as much as the structure, and without them a reader must guess, which leads to wrong groupings, wrong counts, and wrong conclusions.

Start with the fields themselves, giving each field its name, its type, and whether it is required or optional. Say whether a value is text, a whole number, or a date, and say how missing values are shown, whether as a blank or with a special code. A reader who knows this will not mistake a blank for zero, and that distinction alone prevents many errors in totals and averages.

Next, document the codes used in the file, since many datasets use short codes to save space and to keep values consistent. A status field might use values such as open, done, and archived, while a region field might use short names for cities or areas. Without a code list, a new reader must infer meanings from examples, which may not include every code, and may include a code used in an unusual way.

Also document units and formats, because a quantity field might count items, kilograms, or boxes, and a date field might use year, month, and day in that order. A money field might be in whole units or in smaller units, and if units are not stated, two readers may interpret the same number differently. Stating units costs one line, and it saves a costly mistake that could affect decisions made from the data.

Limitations deserve equal attention, because every dataset has limits that affect how it should be used. It may cover only certain dates, miss some regions, or contain known errors that have not yet been fixed, and it may count only completed records rather than all attempts. If you hide those limits, users will overtrust the data and draw conclusions it cannot support, while stating them plainly lets users still use the data wisely. For example, a sales file covering only online orders should say so, or a reader might report total business sales and be wrong because shop sales are missing.

Keep documentation close to the data, in a readme file in the same folder or in a header comment where the format allows it. Documentation hidden in a separate system that few people check may as well not exist, and it must be updated when the format changes. An outdated document is worse than a short one, because it misleads with confidence, while a short, current note at least states what is known. Write in plain language, avoid jargon where a simple word will do, and show a sample row with its meaning explained. Structure tells a reader how data is stored, while codes, meanings, and limitations tell them how it should be used, and both are needed for trustworthy work.

Check: Why is documenting a dataset's codes, meanings, and limitations as important as documenting its structure?

Section 36: Data Ethics and Accuracy

A number can be correct and still mislead, which happens when a true value is shown without the context needed to understand it. The arithmetic may be right, yet the conclusion drawn from it may be wrong, and data ethics is partly about preventing that gap. It asks how results will be read, not only how they are computed, and it treats presentation as part of the result itself rather than as decoration.

Consider an average, such as a shop reporting an average sale of 50 units of currency, which may be accurate while hiding a wide range. Most sales might be small, with a few very large sales raising the average, so a reader who sees only the average may think most customers spend about 50. That picture is misleading, even though no calculation was wrong, and showing the range or the number of sales behind the average gives a truer picture. Context turns a bare number into honest information, and omitting it turns the same number into a trap for the reader.

Sample size matters in the same way, because a result based on ten records is weaker than one based on ten thousand, even when both are computed correctly. The smaller sample is more easily swayed by a few unusual cases, and a percentage reported without the count behind it cannot be judged for strength. Always give the count alongside a rate or an average, because that habit alone prevents many overconfident claims and helps readers weigh the evidence.

Coverage matters too, since a dataset may cover only part of a population, such as only online customers, only one city, or only one month. A result from that subset should not be presented as if it covered everyone, and you should say who is included, who is not, and which period the data covers. Readers can then judge whether the result applies to their question, while without that note they may apply it far beyond its scope. Selection can distort a true result in a similar way, because dropping rows with missing values may underrepresent a group that is more likely to have missing data.

Visual choices can mislead as well, even when the data is right, since a chart that starts far above zero can exaggerate a small change. A chart that omits a category can hide an important part of the story, and these choices are not errors in calculation but errors in presentation. Accuracy also means respecting the people behind the data, by not sharing personal data beyond its purpose and not reporting tiny groups in ways that identify individuals.

You do not need advanced training to practise this care, because three questions before sharing a result cover most cases. Ask what the number actually counts, who or what is missing from it, and what a reader might wrongly conclude from it, then answer those questions in your report. Add a short note about limits, give counts, ranges, and coverage alongside headline figures, and present the result in a way that invites correct interpretation.

Check: How can technically correct numbers still mislead if presented without context or limitations?

Section 37: Reviewing Data Code

Reviewing data code differs from reviewing general code, because general review checks clear names, sensible structure, and correct logic, while data review must also check reading, cleaning, conversion, and reporting. Many data bugs look correct in code, and they only appear when real data passes through the program, so a reviewer who knows where to look can catch them early.

Start with types, since CSV values arrive as text and must be converted to the right type before any calculation or comparison. Look for numbers used without conversion, dates compared as text, and text sorted as if it were numeric, because these errors often pass simple tests. They fail on values such as 2 and 10, where text order differs from numeric order, so ask whether the code includes a test with such revealing values.

Next, check missing values, asking how blanks are handled in each column and whether that choice is stated and consistent. Are blanks excluded from averages, or treated as zero, and is that decision documented where a future reader can find it? A silent default that turns missing into zero can distort totals without any error, so the code should handle missing values explicitly and log how many were found.

Then check grouping and joining, because grouping keys should be cleaned before grouping, or capitalisation and whitespace differences will split one category into two groups. For joins, check the key itself, asking whether it is unique in each file and whether it is the right key for the intended match. Check row counts before and after the join, since an unexpected jump in rows suggests a key problem, such as joining on a shared attribute like city instead of a unique identifier.

Validation deserves attention in review, including checks for headers, row lengths, and value ranges, and a report that names the row, column, and rule broken. Ask what happens to bad rows, whether they are skipped, flagged, or allowed through, because each choice should be deliberate and documented. Silent handling of bad rows is a warning sign, since it hides problems that users need to see and fix at the source. Output also needs review, including headers, quoting, and date formats in written files, and summary reports that include counts behind totals so results can be judged.

Finally, review the tests themselves, because data code needs tests with messy data, not only clean samples that prove little about real use. Look for tests with blank rows, quoted commas, inconsistent capitalisation, and a malformed row, and suggest a messy test for each major path if they are missing. Use a short checklist during review, so these checks are not left to memory:

# Review prompts: types converted? Missing handled?
# Keys cleaned? Join counts checked? Bad rows reported?

A checklist focuses attention on places where data bugs hide, and over time the team catches the same classes of errors earlier. That is the goal of reviewing data code: not only to fix one program, but to build a shared habit of checking data carefully before it is trusted.

Check: What data-specific issues should a code review check for beyond general code quality?

Section 38: Data Handling for Small Programs

A small program does not need the same data handling as a large system, but it also does not need none at all, because rigour should scale with size and importance. A ten-row file for personal use needs less ceremony than a shared dataset used for decisions, yet both need enough care to be trustworthy. The skill is choosing the right level for each case, avoiding both carelessness in small tasks and overbuilding in simple ones.

Start with the smallest useful set of practices, which should apply even to a tiny script, because they cost little and prevent the most common errors. Always use a proper reader and writer for CSV and JSON, always convert text to the right type before calculating, and always check that a file you read has the headers you expect. Skipping those steps saves minutes now and costs hours later, when a wrong total must be traced through code that assumed the data was correct.

Add more rigour as stakes rise, because if other people will use the output, the format should be documented, and if the data will be updated repeatedly, dated backups should be kept. If bad rows could cause harm, validation should be strict and every problem should be reported, rather than handled silently or skipped without a note. These additions are not bureaucracy, but a match to the risk, since a personal list of books needs less validation than a file of payments. The difference is the cost of being wrong, and that cost should guide how much checking you build in.

Small programs often grow, since a script written for one file may later handle many files, and a personal tool may later be shared with a team. If basic practices are already in place, growth is easier, because validation can be extended, logging can be added, and backups can be formalised without a rewrite. Building in a little rigour early is therefore an investment, not a burden, even when the program feels too small to deserve it.

Avoid the opposite error as well, because a full database, a complex pipeline, and a formal review process are too much for a one-off list. Overbuilding slows the work and makes the program harder to change, so choose tools that fit the task rather than the most impressive tools available. A useful test is to ask what happens if the data is wrong, and to let the answer guide the effort, adding stronger checks when wrong data would reach other people.

Logging and testing scale in the same way as other practices, with a small script printing a short summary of rows read, skipped, and written. Testing follows the same pattern, with a small program needing a few hand-checked examples, including one messy row and one missing value, and a larger program needing a broader suite. Keep the practices you choose consistent, using a short routine every time, and extend that routine as a program grows rather than replacing it.

Check: How should data handling rigour scale with program size and importance, rather than being the same for all programs?

Section 39: Data Handling Mistakes to Avoid

Every data handler makes mistakes, and the same mistakes appear again and again, which is why a checklist of common pitfalls is so useful. Memory alone is not reliable for this job, because you may remember a pitfall when you are thinking about it, yet forget it when you are focused on another part of the task. A written checklist does not forget, and it asks the same questions every time, even when you are tired, rushed, or confident.

The first pitfall is treating CSV text as numbers, since values from a CSV file arrive as text and must be converted before use. Adding them without conversion joins them as strings, while sorting them as text puts 10 before 2, and both errors look plausible until the results are checked. The fix is simple: convert each column to its intended type before calculating or sorting, and test with values such as 2 and 10 that reveal text order at once.

The second pitfall is assuming data is clean, because real files contain blank rows, extra spaces, and inconsistent capitalisation that clean test data does not include. A program tested only on clean data may crash on real data, or it may group Lagos and lagos as different cities, splitting a total across two groups. The fix is to clean and validate inputs, trimming spaces where appropriate, normalising capitalisation for grouping keys, and handling missing values explicitly.

The third pitfall is losing information during transformation, such as flattening nested JSON to CSV and dropping structure, rounding numbers and dropping precision, or converting dates without care and dropping detail. The transformed data may look correct, yet it is actually incomplete, and users may assume the export is complete when it is not. The fix is to test round trips where possible, comparing original and transformed data, and to document what was lost so users do not use the export for purposes it cannot serve. A checklist item about transformation loss prompts that documentation before the export is shared.

The fourth pitfall is handling bad rows silently, because a program that drops bad rows without reporting them hides problems and makes totals look complete when they are not. Users cannot fix data they do not know is bad, and the missing rows may bias the results in ways that are hard to detect later. The fix is to log every skipped row with its reason, and to report counts of accepted and rejected rows in a summary the user can see.

The fifth pitfall is trusting a format without checking it, since a file may have different headers than expected, use a different delimiter, or use a different date order. A program that assumes one format will misread the others, producing wrong values without any obvious error, which is worse than a clear failure. The fix is to verify headers and format before processing, checking row lengths and a sample of values, and failing with a clear message if the format does not match. Run the checklist before you share output, not after a problem appears, and keep it short enough to use every time, with each item as a question you can answer honestly.

Check: Why is a checklist of common data pitfalls more effective than relying on memory during development?

Section 40: Data Handling Summary and Next Steps

This chapter has covered a full set of data handling skills, from reading and writing CSV and JSON to converting types, validating data, and cleaning messy values. You have also learned to filter, sort, group, and join datasets, and to document formats, version data, and report errors clearly. Together, these skills form a routine rather than a collection of separate tricks, and the routine is what makes data work reliable.

Routine practice means building quality steps into your normal workflow, so you check headers every time you read a file and convert types before calculating without needing to be reminded. You log row counts as a matter of habit, verify output by reading it back before sharing it, and document the format and its limits as part of the task. These steps take a little time on each task, but they save much more time overall, because they catch errors early when they are cheap to fix.

Reactive practice is the opposite, in which quality checks happen only after a failure, such as a wrong total noticed by a user or a corrupted file discovered when it is needed again. At that point, fixing the problem is urgent and costly, the original data may be gone, and the error may have spread to other reports. Trust is harder to rebuild than a file, and a late check can only explain why wrong results were shared, while an early check could have prevented them. The same checks are used in both approaches, but routine checks happen before output is shared, and reactive checks happen after damage is done.

Building a routine starts small, with a short sequence used for every dataset, covering reading, validation, conversion, cleaning, logging, and verification. Read with a proper reader, validate headers and row lengths, convert types with explicit rules, clean keys before grouping, and log counts and errors. Write output, then read it back, and document the format and its limits, extending the sequence for larger datasets while keeping the core the same.

Next steps build on this foundation, since the next chapter moves toward APIs and databases, which use the same principles in new settings. APIs deliver data over a network, often as JSON, while databases store data with structure and rules enforced by the system, and both still require validation, conversion, and clear error reporting. The new topics are network behaviour, authentication, and queries, which add dimensions without replacing the basics you have learned here. Keep practising with real files, creating a messy CSV file and cleaning it, converting it to JSON and back while noting what is lost, and grouping it while checking totals by hand.

A final habit is worth naming, because it tests whether your work can be trusted by others as well as by you. When you finish a data task, ask whether someone else could reproduce your result, find the source file, follow your cleaning rules, and repeat your grouping to get the same totals. Reproducibility turns a plausible result into one others can rely on, and it is the standard to carry into APIs, databases, and every data task that follows.

Check: How does routine data quality practice, built into workflow, differ from reactive quality added only after failures?

Section 41: Working with Real-World Messy Files

Real-world files rarely match textbook examples. They arrive with inconsistent headers, extra blank lines, mixed date formats, duplicate rows, and values that change meaning halfway through a file because a source system was updated. The first task when you receive such a file is not to process it immediately, but to inspect it: look at the first rows, the last rows, and a sample from the middle, count rows, check headers, and note anything surprising. Inspection often reveals problems that a blind processing run would turn into wrong results, such as a header row repeated in the middle of the file, or a totals row at the end that would be counted as data if not excluded. A few minutes of inspection saves hours of debugging results that look plausible but are based on misunderstood input.

Messy files also teach you to design processing defensively. Instead of assuming every row has the same shape, check row length and report short or long rows. Instead of assuming a column always contains numbers, try conversion and report failures with row numbers. Instead of assuming headers are exactly as documented, compare actual headers with expected ones and report differences before processing. Defensive code does not mean distrusting every file; it means making your assumptions explicit and checking them, so that a file that breaks an assumption fails loudly with a useful message, rather than silently producing wrong output. That explicitness is especially important when files come from other people or systems, because you cannot control their quality, only your handling of it.

Another reality of messy files is that cleaning decisions often require judgement and sometimes require asking the data provider. If a date column mixes two formats, you may be able to detect the format per row, but if a value is genuinely ambiguous, guessing is worse than asking. Keep a list of ambiguities you encounter, decide what you can resolve safely from context, and raise the rest with the provider or document them as limitations. This habit protects you from inventing data, which is a serious error in data work: a guessed date, a guessed quantity, or a guessed category looks like real data in output, but it is not, and decisions based on it may be wrong. It is always better to report a value as unknown, with a count of unknowns, than to guess it and present the guess as fact.

Finally, treat messy files as a learning source. Each messiness pattern you encounter, a new delimiter, a new encoding problem, a new kind of duplicate, adds to your repertoire of checks for future files. Keep notes on patterns and on the checks that caught them, so that your next program includes those checks from the start. Over time, your data code becomes more robust not because you learned a new library, but because you learned what real data looks like and built defences against its common failures. That experience, accumulated file by file, is what separates a developer who can handle textbook data from one who can handle data in the wild, where messiness is the norm and robustness is the skill that matters most.

Check: Why should you inspect a real-world file before processing it, rather than trusting its documented format?

Section 42: Transforming Data Step by Step

Transforming data step by step means breaking a complex transformation into a sequence of small, understandable, verifiable steps, rather than attempting it in one large, opaque operation. For example, converting raw sales data into a monthly summary might involve cleaning, then filtering out test records, then converting dates, then grouping by month, then summing, then formatting output. Each step takes an input, produces an output, and can be checked independently: after cleaning, are values consistent; after filtering, is the row count as expected; after grouping, do group totals sum to the overall total. That stepwise visibility makes debugging far easier, because when final output looks wrong, you can check each intermediate output to find the first step where it diverges, rather than searching a single large transformation for the fault.

Stepwise transformation also makes code easier to test. Each step can be a small function with its own tests, using small hand-crafted inputs and expected outputs, so that the whole pipeline's correctness rests on tested parts, plus integration tests for the pipeline as a whole. A single large transformation, by contrast, can only be tested as a whole, with large expected outputs that are hard to construct by hand and hard to interpret when they fail, because a failure could be in any part of the transformation. A step that cleans should only clean, not also filter or aggregate, because mixing purposes makes the step harder to test and harder to reuse in another pipeline that needs cleaning but not filtering. A step's output shape, its headers, types, and ordering, should be documented, so that the next step can rely on it, and so that a reader can understand the pipeline without reading every step's code in detail. That discipline, single purpose and clear shape per step, is the same principle as small functions in general programming, applied to data pipelines, where it pays off in testability, readability, and reusability, because steps can be recombined for new tasks, such as reusing a cleaning step for a different analysis, without copying its code or untangling it from a larger transformation.

Finally, log each step's counts and key totals, as discussed for data logging, so that a pipeline run leaves a trace: input rows, rows after cleaning, rows after filtering, number of groups, and final totals. That trace lets you verify a run quickly, by checking that counts change as expected at each step, and it lets you diagnose a run that produced unexpected output, by seeing where counts first diverged. A pipeline without stepwise logging is a black box; one with it is transparent, and transparency is what makes data work trustworthy, because it allows others, and your future self, to audit how output was derived from input, step by step, rather than having to trust a single opaque process. Build pipelines as visible sequences, test each step, log each step, and data transformation becomes a reliable, explainable craft, not a mysterious art.

Check: How does breaking a transformation into small steps help you locate a fault in final output?

Section 43: Grouping and Summarising for Reports

Grouping and summarising for reports turns raw data into the tables that decision-makers actually read, and report grouping deserves extra care because report readers will act on its numbers. Start by clarifying the report's question: what groups does the reader need, and what summary per group answers their question. A report on sales by region needs groups by region with totals; a report on sales by region and month needs groups by both, with totals per combination. Choosing groups to match the question, rather than grouping by every available column, keeps reports focused and readable, because a report with too many groups, or with groups at too fine a level, overwhelms readers with detail they do not need, while one with too coarse groups hides differences they do need to see. Talk to the report's audience, or imagine their decision, and let that decision determine grouping, not the data's availability alone.

Report summaries should include not just the headline total, but also supporting information that helps readers interpret it: the number of records behind each total, the period covered, and any exclusions, such as rows skipped due to missing values. A total based on three records is less reliable than one based on three thousand, and a reader who does not know the count cannot judge reliability. Including counts alongside totals, and noting exclusions in a footnote or a summary line, gives readers the context they need to use totals wisely, rather than treating all totals as equally solid. Choose sorting to match the report's purpose, and state it, either through the report's ordering being self-evident or through a note, so that readers do not mistake alphabetical order for ranking, or ranking for alphabetical order. Similarly, rounding in reports should be consistent and disclosed: totals rounded to whole numbers look cleaner, but rounding can make components not sum exactly to a grand total, which readers may notice and question. Either show unrounded values, or note that totals may not sum due to rounding, so that a small discrepancy does not undermine trust in the whole report.

Finally, verify grouped report totals with checks that readers could also perform: group totals should sum to the overall total, where groups partition the data without overlap or omission, and group counts should sum to the total row count, with exclusions accounted for separately. Those checks, performed before sharing a report and ideally shown as a reconciliation line, catch grouping bugs, such as a group split by uncleaned keys, which would make group totals sum correctly but with too many groups, or a filter applied inconsistently, which would make totals not sum to the overall total. A report that reconciles, with groups summing to totals and counts accounting for all rows, is internally consistent and far more trustworthy than one whose totals cannot be reconciled, even if its individual numbers look plausible. Grouping for reports is therefore not just a technical step, but a communication step, where clarity, context, sorting, rounding, and reconciliation together determine whether a report informs or misleads.

Check: Why should a grouped report include the record count behind each total, not just the total itself?

Section 44: Searching and Finding in Datasets

Searching a dataset means finding rows that match a criterion, which may be an exact value, a partial match, a range, or a combination, and it is a fundamental operation for exploring data, for answering specific questions, and for verifying data quality. Exact search, such as finding a row with a specific identifier, is straightforward if identifiers are unique and clean, but it fails if identifiers differ by whitespace, case, or format between the search term and the stored value, which is why search should normalise both sides consistently, by trimming, by case-folding where appropriate, and by using the same format, before comparing, so that a search for a customer finds them regardless of minor formatting differences in how their identifier was stored. That normalisation, applied to both search term and stored values, prevents the frustrating experience of knowing a record exists but not finding it because of a formatting mismatch, which is a common search failure in messy data.

Partial search, such as finding rows where a name contains a substring, or where a description mentions a keyword, is useful for exploration, but it requires care about case, about word boundaries, and about performance for large datasets, where scanning every row for a substring can be slow, though acceptable for small files. Partial search should be tested with cases that should match, cases that should not, and cases at boundaries, such as a substring that appears as part of a longer word, where the searcher must decide whether that counts as a match, based on the task's needs, and document that decision, because different tasks need different matching rules: a search for a product code should probably match exactly, not partially, to avoid false positives from similar codes, while a search for a keyword in descriptions should probably match partially, to catch variations in phrasing. That task-dependent matching, exact versus partial, case-sensitive versus insensitive, whole-word versus substring, should be chosen deliberately and stated, so that search results are interpretable, rather than assumed to follow one rule when they actually follow another.

Range search, such as finding rows with dates in a period, or quantities within limits, is another common pattern, and it requires correct type conversion, as with sorting, because comparing dates as text works only for year-first formats, and comparing numbers as text gives wrong ranges, as discussed earlier. Range boundaries also matter: does a range include its endpoints, or exclude them, and is that inclusive or exclusive behaviour documented and consistent? A search for sales in January should include 1 January and 31 January if it claims to cover January, and a test with rows on those boundary dates, plus rows just outside them, verifies inclusivity, preventing the off-by-one style errors that also affect range searches, where a boundary row is wrongly included or excluded, distorting counts for the period.

Check: Why should search normalise both the search term and stored values before comparing them?

Section 45: Exporting Data for Other Tools

Exporting data for other tools means producing a file in a format that another program, such as a spreadsheet, a database loader, or a visualisation tool, can read, and it requires attention to that tool's expectations, which may differ from your program's internal representation, in headers, types, encoding, delimiter, and handling of special values. Exporting for other tools also requires considering the recipient's workflow, not just their tool's format, because a file that is technically readable may still be unusable if it arrives at the wrong time, in the wrong place, or without instructions on how to use it, such as which sheet to open, which columns to use, or how often the file will be updated, all of which affect whether the recipient can actually benefit from the export, or whether they will struggle, ignore it, or misuse it, due to missing context, a failure that is not technical but communicative, and that export documentation, including purpose, update frequency, and usage notes, prevents, by giving recipients the context they need to use the file correctly, beyond its format alone, which, though necessary, is not sufficient for successful use, because use also requires understanding purpose, scope, and limitations, all of which documentation provides, in a short accompanying note, rather than leaving recipients to infer purpose from file name alone, which may be ambiguous, especially when multiple exports exist for different purposes, with similar names, leading to confusion about which export serves which purpose, a confusion that clear naming and documentation resolve, by making each export's purpose explicit, at a glance, rather than requiring investigation. Testing an export with its recipient, by asking them to import a sample and to confirm that they see expected data, is another valuable step, because it verifies not just format, but usability, by confirming that the recipient's tool, settings, and workflow actually work with the export, as intended, rather than assuming they will, based on format specifications alone, which, though they describe the file, do not guarantee that a particular recipient, with their specific tool version and settings, will read it correctly, a guarantee that only a real import test, with the real recipient, can provide, and that, when done for a sample, before sharing large data, catches recipient-specific issues early, when they can be fixed by adjusting format or settings, rather than after large data has been shared and misread, requiring correction and re-sharing, which wastes time and damages confidence, a cost that a small recipient test, with a sample file and a confirmation, avoids, by ensuring the export works end to end, from your program to their tool, before it is relied on for real data, a end-to-end verification that is the true test of export success, beyond format correctness alone, which, though necessary, is not sufficient, as discussed earlier, for export to be considered successful, because success means correct reading by the recipient, not just correct writing by the exporter, a recipient-centred definition that this section emphasises, and that recipient testing embodies,.

Check: Why is export success defined by the recipient tool reading the file correctly, not just by your program writing it?

Section 46: Importing Data into Your Program

Importing data into your program means accepting a file from outside and turning it into data you can trust, whether it comes from a user, another program, or a download. You did not create that file, so you cannot assume it matches your expectations, and a good import must check the file first. It accepts what is valid, rejects what is not, and explains the rejection clearly, because that clarity is what separates a helpful import from a confusing one.

Start by defining what a valid file looks like, listing the required headers, the expected types for each column, and how missing values should appear. State the delimiter and encoding when those matter, because this definition is your import contract, and without it you cannot judge a file fairly. With a clear contract, every check has a reason the user can understand, and every rejection can point to a specific rule that was broken.

Next, check the file against that contract before using any of its data, starting with headers, since a missing header affects every row. Check row lengths next, then value formats and ranges, collecting problems as you go rather than stopping at the first one. A user who sees all problems at once can fix them in one pass, while a user who sees one problem at a time must repeat the import many times.

When a file fails, reject it clearly, saying that the import did not happen, why it did not happen, and what the correct value should look like. Name the row, column, and value involved, and say whether the whole file was rejected or only some rows were skipped, because that distinction matters greatly. A full rejection means no data was saved, while a partial import means some data was saved and some was not, and the user must know which happened.

Silent misreading is the danger to avoid, because a program that reads a file with the wrong delimiter may still produce rows that look like data while being wrong. A program that treats text as numbers without checking may produce totals that look plausible while being incorrect, and both failures are worse than a clear rejection. A rejection can be fixed at once, while a silent error can spread into reports and decisions before anyone notices it, which is why strict, visible checks beat lenient, quiet ones. Flexibility still has a place, but it must be deliberate, accepting harmless variations such as a different column order when reading by header name, while refusing variations that change meaning.

A small example shows the pattern in code:

import csv

with open("import.csv", newline="", encoding="utf-8") as f:
  reader = csv.DictReader(f)
  if "quantity" not in reader.fieldnames:
    raise ValueError("Missing required column: quantity")

Also report a summary when an import succeeds, stating how many rows were read, accepted, and skipped, and why any rows were skipped. Finally, test imports with files that should fail, including a missing header, a wrong type, and an unexpected delimiter, checking that each case is rejected with guidance rather than misread.

Check: Why is a clear import rejection with guidance better than silently misreading a file?

Section 47: Data Handling Review Checklist

A data review checklist is a short list of checks applied to a dataset before it is used or shared, covering format, content, and documentation. It is used for every dataset, not only for difficult ones, and that consistency is its strength, because it catches errors that experience alone would miss. Even experienced handlers make slips, especially when tired or rushed, and a checklist protects work at exactly those moments when attention is weakest.

Experience can create a false sense of safety, since after handling many files successfully it is easy to assume the next file will be fine. That assumption is risky, because each new file comes from a different source, with a different format, different codes, or different gaps, and past success does not guarantee that this file is clean. A checklist forces a fresh check for each dataset, treating every file as unproven until checked, rather than trusting it on the basis of past files.

A good checklist starts with structure, asking whether the expected headers are present with no duplicates, whether rows have the expected number of columns, and whether the encoding is correct. These checks take minutes, yet they catch format changes that would otherwise corrupt every later step, such as a renamed column that would be read by position and produce wrong values silently. A file that fails a header check is stopped at once, while a file without that check might produce plausible but wrong output that is much harder to trace.

Next, the checklist covers values, asking whether required fields are present and non-empty, whether values can be converted to their intended types, and whether numbers and dates fall in plausible ranges. These checks catch content errors, and they also reveal patterns, such as many missing values in one column, which may point to a source problem. A pattern like that deserves investigation, not silent acceptance, because it may mean that a whole group or period is underrepresented in the data.

Then check grouping and totals, asking whether grouping keys were cleaned before grouping, whether row counts match before and after filtering or joining, and whether totals match a hand-checked sample. A total can be wrong even when every row is well formed, so checking a small sample by hand is a simple way to verify the logic behind the summary. Documentation is the next section, asking whether the format is documented with codes and units stated, and whether limits, source, and date are recorded. A dataset without documentation may be correct, but it cannot be used safely by others, who will guess at meanings and limits that should have been stated.

The checklist should also cover output, asking whether the output was read back and compared with the input, whether summary reports include record counts behind totals, and whether errors and skipped rows were reported. Keep the checklist short and written down, with about ten clear questions, and share the completed list so colleagues can see what was checked. Use it even when you feel you do not need it, because that feeling is exactly when slips happen, and a checklist makes quality repeatable rather than dependent on mood or workload.

Check: Why should a data review checklist be followed for every dataset, even when you feel experienced enough not to need it?

Section 48: From Data Files to Databases

File handling works well for many datasets, because a CSV or JSON file is simple to create, share, and inspect by hand. At some point, however, a dataset may outgrow what files handle well, and that point is not marked by a single rule but by a set of signs. Recognising those signs early helps you plan a move to a database before file handling becomes painful, rather than waiting until workarounds take more time than analysis itself.

The first sign is size, since a file that is too large to open comfortably or too slow to read fully for each task is straining file handling. You may wait for a full scan just to find one record, or run out of memory when loading the whole file, while a database can find one record without reading everything. The second sign is concurrent use, because files are awkward when several people or programs need to update the same data at once, and one writer may overwrite another's changes. A file has no built-in way to prevent that clash, while a database manages concurrent access and allows safe updates from many users at once.

The third sign is the need for enforced rules, since a file accepts whatever text is written to it and cannot enforce unique identifiers on its own. It cannot prevent a duplicate customer number or a missing required field unless your code checks every time, and if that code has a bug, the bad data will be accepted silently. A database can enforce such rules at the system level, refusing a duplicate identifier even when application code has a bug, which is a valuable guarantee when correctness matters more than simplicity. The fourth sign is complex relationships, because a dataset may grow from one table into several linked tables, such as customers, orders, and products, each needing its own file.

Joining files by hand then becomes slow and error-prone, while a database is built for linked tables and can join them efficiently and safely. The fifth sign is frequent, selective queries, since file handling usually means scanning rows to find matches, which works for occasional checks but becomes costly for many different questions. A database can answer selective questions with indexed lookups, without scanning every row for every question, which makes routine reporting much faster and more reliable.

The sixth sign is the need for reliable updates, because updating a file often means rewriting it in full, and a crash midway may leave it half written. A database uses transactions to avoid that risk, so an update either completes fully or does not happen at all, which matters for data that cannot be rebuilt easily. Moving to a database does not mean abandoning file skills, since files remain useful for exchange, backup, and small tasks, and a database export to CSV is still a common way to share data. Move when the signs pile up, define the tables and identifiers carefully, migrate with validation and row-count checks, and keep the old files as a backup until the new system is proven.

Check: What signs indicate that a dataset has outgrown file handling and may need a database?

Section 49: Data Handling Habits to Keep

A habit is a practice you follow without needing to be reminded, while a technique you apply only when reminded is different in an important way. You may know a technique well, but you use it only when a checklist, a colleague, or a failure prompts you, so it is present on calm days and absent on busy ones. Internalised habits work in the opposite way, because they are part of how you approach every task, even under pressure, and that difference matters for data handling. Errors often happen when you are busy, tired, or rushed, which is exactly when a reminded technique is most likely to be skipped and a habit is most likely to hold.

Consider type conversion, which a handler who knows the technique will apply when reminded, while a handler with the habit applies automatically before any calculation. The result is the same on a calm day, but on a busy day the first handler may skip the step and the second will not, because the step is built into their routine. Habits form through repetition with a fixed routine, such as reading with a proper reader, checking headers, converting types, validating values, logging counts, and verifying output by reading it back.

Use that sequence for small files and large ones alike, and at first you may need a written list to follow it, but over time the list becomes unnecessary. The sequence then feels like the natural way to work, not an extra task imposed on top of the real work, and that feeling is the sign that a technique has become a habit. Internalised habits also scale better than reminded techniques, because a technique applied on reminder tends to stay at one level of rigour, used fully when someone checks and lightly when no one does. A habit adjusts to the task without changing its core: quick for a small file, with fuller validation and documentation for a shared dataset.

Another difference is how habits handle new tools, since a technique tied to one tool may not transfer, while a habit tied to a principle does transfer. The habit of verifying output by reading it back applies to CSV, JSON, and later to databases and APIs, and the habit of reporting skipped rows applies wherever data is filtered. Habits also make teamwork easier, because when everyone follows the same routine, work becomes predictable, a colleague can see the familiar steps, and a new member can learn the routine by watching it in use.

Building habits takes deliberate practice, starting with one habit rather than ten, such as always logging row counts for a month before adding another habit. Finally, habits should be reviewed, not just repeated, so a habit that no longer fits a new kind of data can be updated, and a routine that misses a new pitfall can gain a new check. The goal is not to work on autopilot without thought, but to make essential checks automatic, so your attention is free for judgement, unusual cases, and the meaning of the data itself.

Check: How do data handling habits, once internalised, differ from techniques you apply only when reminded?

Section 50: Preparing for APIs and Databases

APIs and databases may look like new worlds, but they rest on the principles from this chapter, and seeing that continuity makes learning them easier. An API is a way for programs to exchange data over a network, while a database is a system for storing and querying structured data, and both use formats you already know, especially JSON. Both require the same care with types, validation, and errors, so learning them is not starting again but applying proven habits to new systems.

Start with format, since API responses are often JSON objects and arrays, and reading them uses the same skills as reading a JSON file. You access fields by name, handle lists within records, and convert values to the right types before use, because a field that arrives as text must still be converted before calculation. A missing field must still be handled explicitly, rather than treated as zero or empty text, and the source changes from a file to a network response while the handling does not change.

Validation carries over directly as well, because an API can return errors, outdated data, or unexpected formats, and fields may be renamed in a new version. Check required fields, check types, and report problems with the response identified, so the cause can be traced, which is the same discipline as validating a CSV file applied to a new source. Error reporting also carries over, with one addition, since network errors need clear handling for failures, time-outs, and error statuses.

A temporary failure may be worth retrying, while a bad request needs fixing rather than retrying, and a missing record is different from a server error. Report each case in a way the user can act on, keeping technical detail in logs as you would for file errors, and showing users only what helps them decide the next step. Databases add enforcement to familiar ideas, since a table is like a CSV file with stricter rules, columns have types enforced by the system, and identifiers can be required to be unique.

Querying a database also echoes grouping and filtering from this chapter, because a query that groups rows by category and sums a column is the same idea as grouping CSV rows. If you understand grouping, counting, and joining for files, you already know to check row counts, clean grouping keys, and include counts behind totals, which gives you a head start on database queries. There are new dimensions to learn, including authentication, pagination, and rate limits for APIs, and transactions, indexes, and concurrent access for databases, and those topics belong to later chapters.

A practical way to prepare is to practise with real responses, not only ideal examples, saving a sample API response and inspecting its structure. Note which fields are optional, which types vary, and how error responses look, then write code that handles an error response as well as a success response. The core message is reassuring: read with care, convert types explicitly, validate before trusting, handle missing values openly, report errors with enough detail to fix them, and document formats and limits.

Check: How do the data handling principles from this chapter carry over to APIs and databases?

Practical Task

Take a messy CSV file you create yourself (with quoted commas, missing values, inconsistent capitalisation, and one malformed row) and a small JSON file of related records. Write a program that reads both, validates and cleans the CSV, converts types, groups the data, writes a summary CSV and a summary JSON, and logs row counts and errors — then verify the outputs by reading them back.

Chapter Questions

1. Why must CSV values be converted from text explicitly, and what goes wrong if they are not?
2. Explain how grouping keys, missing values, and join types each affect the correctness of aggregated results.
3. When should you choose CSV and when JSON, and what is lost when converting nested JSON to flat CSV?
4. How should data errors be reported, and why is silent handling of bad rows dangerous?
5. What data handling habits ensure that results are reproducible and trustworthy, not just plausible?