Parsing dates, strings and structured fields

Why this matters

Parsing means reading structure out of a value: a date out of a string, a product code out of a description, fields out of a log line, columns out of a nested JSON record. It is one of the transformations the exam names, and it is where the quietest errors live, because a parser that guesses wrong usually produces a plausible value instead of an error.

This lesson covers date formats and time zones, splitting and extracting from strings, regular expressions, flattening nested data, and the practice of counting parse failures rather than letting them disappear.

The lesson

Date formats, time zones and the ambiguous 03/04

Dates are written in many formats, and the most dangerous difference is the order of day and month. 03/04/2025 is 3 April in the United Kingdom and most of the world, and March 4 in the United States. Nothing in the value says which.

The course's raw customer file shows what happens. Thirty signup dates are written day first, such as 15/03/2024. If a tool reads them as month first:

• 19 fail outright, because their day is above 12 and there is no month 15;
• 9 convert successfully to the wrong date, because their day is 12 or less, so 05/08/2023 becomes 8 May instead of 5 August;
• 2 come out right by coincidence, because their day and month are equal.

The 19 loud failures are useful: they reveal the format. The 9 silent errors are the danger: they are valid dates, they sort and filter normally, and they are wrong. The rule is to establish a column's format from values that can only be read one way, then parse the whole column with that format stated explicitly, never with a tool's automatic guess.

The safest format for storing and exchanging dates is ISO 8601: YYYY-MM-DD, such as 2025-03-04, with time as 2025-03-04T14:30:00. It is unambiguous, and it sorts correctly even as text.

Time zones add a second layer. A time stamp of 23:30 in New York is 04:30 the next day in London, so the same event falls on different dates depending on the zone. Systems should store times in UTC (Coordinated Universal Time), with the offset recorded where the local time matters, and convert only for display. Daylight saving changes create hours that happen twice in autumn and hours that do not exist in spring, which breaks naive hourly counts.

Splitting and extracting from strings

Many fields contain several pieces of information in one string, and parsing them apart is routine.

• Splitting on a delimiter separates parts: a full name on a space, an email address on @ to get the domain, a path on /. Splitting assumes the delimiter appears the expected number of times; names such as "Mary Ann de la Cruz" break a rule that expects exactly one space.
• Positional extraction takes characters by position, such as the first three characters of a code, with functions like LEFT, RIGHT and SUBSTRING in SQL and spreadsheets, or slicing in Python. It works only when every value has the same layout.
• Finding and extracting uses the position of a marker: the text after "Order:" or between brackets.
• Concatenation is the reverse, joining fields into one, such as building a full address for a mailing list.

Each method should be checked against the values that do not fit the expected pattern, because there are always some.

Regular expressions for the parsing an analyst actually does

A regular expression (regex) is a pattern that describes text, and it is the most powerful general tool for parsing strings. SQL dialects, Python, spreadsheets and most data tools support them.

A few building blocks cover most analytical use:

• \d matches a digit, \w a letter, digit or underscore, \s a space.
• + means one or more, * zero or more, ? optional, and {3} exactly three.
• ^ and $ anchor the pattern to the start and end of the value.
• Square brackets list allowed characters, as in [A-Z] for a capital letter.
• Parentheses capture part of a match so it can be extracted.

For example, ^P\d{3}$ matches the practice data's product codes exactly (P followed by three digits), and @(.+)$ captures everything after the @ in an email address. The course's web server log is parsed with a single regular expression that captures the client address, timestamp, request, status code and size from each line.

Regular expressions are also used for validation: counting how many values in a column fail to match the expected pattern is a quick quality check. Their weakness is readability. Complex patterns are hard to review, so they should be kept as simple as the job allows and tested against both matching and non-matching examples.

Flattening JSON and nested fields into columns

Data from APIs and document databases arrives nested: objects inside objects, and lists inside records. Analysis tools want flat tables, so nested data must be flattened.

Nested objects become columns with compound names. Each response in the course's survey export contains a scores object with satisfaction and recommend inside it; flattened, those become two columns, often named scores.satisfaction and scores.recommend. Tools such as pandas' json_normalize do this automatically.

Nested lists are harder, because one record contains many items. An order record with a list of three line items can be flattened in two ways: exploded into three rows, one per item, repeating the order's fields on each; or kept as one row with the list aggregated, such as a count of items. Exploding changes the grain of the data, so a total of an order-level field summed over the exploded rows is multiplied by the number of items, the same fan-out problem as a join.

Missing keys are common in nested data, because documents need not share a structure. A flattened column will be missing for records that lacked the key, and that absence should be counted like any other missing value.

Parsing failures: counting them instead of ignoring them

Every parse has three possible outcomes for each value: parsed correctly, parsed wrongly, and failed. Tools often hide the third by setting the value to missing, and cannot see the second at all.

The discipline is to count failures every time. After parsing a column, report how many values could not be parsed, and look at a sample of them. The failures usually fall into a few patterns, each of which becomes a new rule or a documented exception. A parse step that reports "2,000 rows parsed, 0 failures" has been checked; one that reports nothing has not.

For errors that parse successfully, such as the nine wrong dates above, the defences are validation rules and cross-checks: a signup date after the customer's first order, or a date in the future, reveals a value that parsed without complaint and is still wrong.