Positional Extraction

The simplest form of substring extraction works by specifying the starting position and ending position that correspond to the substring that you want to extract from a set of record fields. This is called positional extraction.

When you’re working with data, extracting substrings based on a consistent position is common when dealing with date-time fields or fixed-width fields. Both of these field types have known elements located at specific positions, so there is generally little to no inconsistency in the structure of the field.

Let’s look at an example of a field for which positional extraction might be a valuable structuring technique. In the Individual Contributions dataset, column 14 contains the transaction date for each campaign contribution. In this example, we want to extract the day of the month for each individual contribution into a new column. This will allow us to create a field that we can use to determine if individual campaign contributions were more frequent in certain times of the month.

The following table shows four example record fields from column 14:

If you look at the source data, you can see that the discrete elements of the field always align: the month occurs first, the day of the month follows after exactly two characters, and the year follows after exactly four characters. Each individual record field is homogeneously structured. This means that positional extraction will allow us to easily identify the block of text that represents the month of the contribution.

To identify the starting and ending positions of the substring that represent the day of the month, we can map each individual character in the source record field to a position. You can see how this will work here:

By counting the individual characters, including spaces, we can see that the day of the month starts at position 3 and ends at position 4.

In a sentence, we could describe our desired transformation by saying, “From source column 14, extract the characters located from position 3 to position 4.” If we were to perform this transformation on the dataset, we would produce the following output:

A more complex version of positional substring extraction can pluck a substring from a record field when the starting and ending positions of the substring differ from record to record. Address fields are an excellent example of record fields for which complex positional extraction can be utilized effectively.

To perform this type of positional extraction, you will want to use functions that can search for a particular sequence of characters within the original record field. These functions return either the start position of the searched-for sequence or the length of the sequence. You can then pass the values returned by these functions through to one of the basic positional extraction functions. This will produce a complex nested function.

Looking again at the Individual Contributions dataset, you can see that column 8 contains the name of the person or organization who made each campaign contribution. For records that represent individual people, commas separate the person’s first name and last name. A sample of data from column 8 is shown here:

You can see that the last name element has an inconsistent length in each record. In the first record, the last name is 6 characters long (ARNOLD), whereas in the third record, the last name is 7 characters long (ROSSMAN). Simple positional extraction would not work in this case because the ending positions of the last name differ from record to record. However, because the first name and last names in each record are all separated by a common delimiter—the comma—we can use complex positional extraction functions to identify the position of the comma and then extract the appropriate substring.

Pattern Extraction

Pattern-based extraction is another common method that you can use to extract substrings into a new column. This method uses rules to describe the sequence of characters that you want to extract. To explain what we mean, let’s look at another sample of data from the Individual Contributions dataset. According to the FEC’s data dictionary, column 20 contains free text that describes each contribution. A sample of data from this column is below:

In this case, we want to extract the monthly contribution amount into a new column. It’s fairly easy to describe the desired transformation in a sentence: “From column 20, extract the first sequence of digits, followed by a period, followed by another sequence of digits.” In this sentence, the pattern that defines the street name reads, “first sequence of digits, followed by a period, followed by another sequence of digits.” You can often use regular expressions to represent patterns in code. Regular expressions are also supported by most data wrangling software products.

As you can see, pattern-based extraction can be a useful method to identify substrings that conform to the same generic pattern but are not identical.

Complex Structure Extraction

Sometimes, when you are wrangling data, you might need to extract elements from within complex hierarchical structures. We commonly see this type of complex structure extraction required when wrangling JSON files or other semistructured formats. (You can refer to our discussion of structure in Chapter 2 for a refresher on the differences between structured and semistructured data.) JSON-formatted data often originates from automated systems; if you are working with machine-generated data, it’s likely that your data contains JSON structures.

Users who are wrangling JSON data generally encounter two types of complex structures: maps and arrays. These structures are common in semistructured data because they allow datasets to include a variable number of records and fields. They are described here:

JSON array

A JSON array represents an ordered sequence of values. JSON arrays are enclosed in square brackets. Elements in arrays are separated by commas and enclosed in double quotes.

Example array: [“Sally”,”Bob”,”Alon”,”Georgia”]

JSON map

A JSON map contains a set of key-value pairs. In each key-value pair, the key represents the name of a property and the value represents the value that describes that property. JSON maps are enclosed in curly brackets. Key-value pairs are separated by commas. Keys and values are both enclosed in double quotes.

Example map: {“product”:”Trifacta Wrangler”,”price”:”free”,”category”:”wrangling tool”}

In a given dataset, an array in one record might be a different length from an array in another record. You might see this in a dataset containing customer orders, where each record represents a unique customer’s shopping cart. In the first record an array of orders might include two elements, whereas in the next record, an array of orders might include three elements.

Similarly, maps also support variability across records. Looking at the shopping cart example, each cart might contain a variety of possible properties—say, “gift_wrapped”, “shipping_address”, “billing_address”, “billing_name”, and “shipping_name”. Ideally, every record will contain all of the possible properties. However, it’s more likely that some shopping carts only contain a subset of possible properties. Representing the properties and their associated values in a JSON map allows us to avoid creating a very sparsely populated table.

Of course, JSON format, although ideal for storing data efficiently, is often not structured ideally for use in analytics tools. These tools commonly expect tabular data as input. Consequently, when working with a JSON array or map, you might need to pluck a single element into a new column, or fold the multiple elements contained in an array down into multiple records. This will allow you to convert JSON-formatted data into the rectangular structure needed for downstream analytics.

Simple Aggregations

In the first group, simple aggregations, each input record maps to one and only one output record, whereas each output record combines one or more input records. For simple aggregations, the output record fields are simple aggregations (sum, mean, min, list concatenation, etc.) of the input record fields.

We can perform a basic aggregation on the Individual Contributions dataset. Perhaps we want to manipulate the granularity of the dataset so that each row summarizes the campaign contributions made to each campaign committee. We are interested in creating three new columns:

• One column that contains the average contribution made to each campaign committee
• One column that contains the sum of contributions made to each campaign committee
• One column that counts the number of contributions made to each campaign committee

In this example, we will be performing this basic aggregation on the following limited sample of data from the Individual Contributions dataset:

Remember, based on the FEC’s data dictionary, column 1 contains the campaign committee and column 15 contains each contribution amount. After aggregating the values in column 15 by campaign committee, we end up with the following output:

Column-to-Row Pivots

In the second group, column-to-row pivots, each input record maps to multiple output records, and each output record maps to one and only one input record. Input record field values become the defining characteristics of the output records. In other words, the output records contain a subset of the input record fields.

This type of column-to-row pivot is commonly referred to as “unpivoting” or “denormalizing” data. It is particularly useful when your source data contains multiple columns that represent the same type of data. For example, you may have a transactions file that contains the total sales numbers per region, per year. The data could be formatted as shown in the following table:

Note that in this example, the sales figures for each year are contained in a different column. We want to restructure this dataset so that a single row contains the sales for a single unique combination of region and year. The result of this column-to-row pivot will look like the following table:

Generally, column-to-row pivots are used when you want to create a dataset that allows you to more easily summarize values. Compared to the original sales dataset in our example, the resulting dataset is structured to facilitate calculations across years and regions.

Row-to-Column Pivots

In the final group, output records sourced from multiple input records and input records might support multiple output records. Output record fields might involve simple aggregations (e.g., sum or max) or involve more complex expansions based on the field values. This type of pivot is called a row-to-column pivot.

As an example, let’s return to the Individual Contributions dataset. We want to create a refined dataset that shows the sum of contributions made to each campaign committee, broken out by contribution type. In this case, we want to create one new column for each contribution type.

A subset of the Individual Contributions dataset contains the following data:

Based on the FEC’s data dictionary, column 1 represents the campaign committee, column 7 represents the contribution type (individual, political action committee, corporate, and so on), and column 15 represents the contribution amount. After performing a row-to-column pivot, our subset of the Individual Contributions dataset will look like the following table:

As a result of the row-to-column pivot, we have created a new column for each of the unique values in source column 7. Each row in this new dataset summarizes contributions made to a single campaign committee.