...

Extract vs. Split

Extract and split transformations do not do the same thing:

...

Extract text or patterns

A primary use of extraction is to remove literal or patterned values of text from a column of values. Suppose your dataset included a column of LinkedIn updates. You can use one of the following methods to extract keywords from these values. 

...

The following functions can be used to extract values from a Datetime column, as long as the values are present in the formatted date:

...

...

...

You can also reformat the whole Datetime column using the DATEFORMAT function. The following reformats the column to show only the two-digit year:

Extract numeric values

You can extract numerical data from text values. In the following example, the first number is extracted from the address column, which would correspond to extracting the street number for the address:

...

...

You can also reformat the whole Datetime column using the DATEFORMAT function. The following reformats the column to show only the two-digit year:

Empty values in this new column might indicate a formatting problem with the address.

Extract components of a URL

URL components

Using functions, you can extract specific elements of a valid URL. The following transformation pulls the domain values from the myURL column:

In some cases, the function may not return values. For example, the SUBDOMAIN function returns empty values if there is no sub-domain part of the URL.

The following functions can be used to extract values from a set of URLs:

• HOST Function
• DOMAIN Function
• SUBDOMAIN Function
• SUFFIX Function
• URLPARAMS Function

Query parameters

You can extract query parameter values from an URL. The following example extracts the store_id value from the storeURL field value:

Extract object values

If your data includes sets of arrays, you can extract array elements into columns for each key, with the values written to each key column.

Suppose your restaurant dataset includes a set of characteristics in the restFeatures column in the following JSON format:

{
  "Credit": "Y",
  "Accessible": "Y",
  "Restrooms": "Y",
  "EatIn": "Y",
  "ToGo": "N",
  "AlcoholBeer": "Y",
  "AlcoholHard": "N",
  "TotalTables": "10",
  "TotalTableSeats": "36",
  "Counter": "Y",
  "CounterSeats": "8"
}

You can use the following transformation to extract the values from TotalTableSeats and CounterSeats into separate columns:

After the above is executed, you can perform a simple sum of the TotalTableSeats and CounterSeats columns to determine the total number of seats in the restaurant.

Extract array values

In some cases, your data may contain arrays of repeated key-value pairs, where each pair would exist on a separate line. Suppose you have a column called, Events, which contains date and time information about the musician described in the same row of data. The Events column might look like the following:

[{"Date":"2018-06-15","Time":"19:00"},{"Date":"2018-06-17","Time":"19:00"},{"Date":"2018-06-19","Time":"20:00"},{"Date":"2018-06-20","Time":"20:00"}]

The following transformation creates a separate row for each entry in the Events column, populating the other fields in the new rows with the data from the original row:

Extract Values into a List

You can also extract sets of values into an array list of values. 

Extract matches into array

Using 

Transformation:

Results:

...

Extract hashtags

Suppose you need to extract the hashtags from customer tweets to another column. In such cases, you can use the {hashtag} 

Source:

The following dataset contains a customer tweets across different locations.  

...

Excited to announce that we’ve transitioned Wrangler from a hybrid desktop application to a completely cloud-based service! #dataprep #businessintelligence #CommitToCleanData # London

...

Learnt more about the importance of identifying issues in your data—early and often #CommitToCleanData #predictivetransformations #realbusinessintelligence

...

Clean data is the foundation of your analysis. Learn more about what we consider the five tenets of sound #dataprep, starting with #1a prioritizing and setting targets.  #startwiththeuser #realbusinessintelligence #Paris

...

Learn how #NewYorklife

onboarded as part of their #bigdata  #dataprep initiative to unlock hidden insights and make them accessible across departments. 

...

How can you quickly determine the number of times a user ID appears in your data?#dataprep #pivot #aggregation#machinelearning initiatives #SFO

Transformation:

The following transformation extracts the hashtag messages from customer tweets.

...

Extract numeric values

You can extract numerical data from text values. In the following example, the first number is extracted from the address column, which would correspond to extracting the street number for the address:

Empty values in this new column might indicate a formatting problem with the address.

Extract components of a URL

URL components

Using functions, you can extract specific elements of a valid URL. The following transformation pulls the domain values from the myURL column:

In some cases, the function may not return values. For example, the SUBDOMAIN function returns empty values if there is no sub-domain part of the URL.

The following functions can be used to extract values from a set of URLs:

Query parameters

You can extract query parameter values from an URL. The following example extracts the store_id value from the storeURL field value:

Extract object values

If your data includes sets of arrays, you can extract array elements into columns for each key, with the values written to each key column.

Suppose your restaurant dataset includes a set of characteristics in the restFeatures column in the following JSON format:

{
  "Credit": "Y",
  "Accessible": "Y",
  "Restrooms": "Y",
  "EatIn": "Y",
  "ToGo": "N",
  "AlcoholBeer": "Y",
  "AlcoholHard": "N",
  "TotalTables": "10",
  "TotalTableSeats": "36",
  "Counter": "Y",
  "CounterSeats": "8"
}

You can use the following transformation to extract the values from TotalTableSeats and CounterSeats into separate columns:

After the above is executed, you can perform a simple sum of the TotalTableSeats and CounterSeats columns to determine the total number of seats in the restaurant.

Extract array values

In some cases, your data may contain arrays of repeated key-value pairs, where each pair would exist on a separate line. Suppose you have a column called, Events, which contains date and time information about the musician described in the same row of data. The Events column might look like the following:

[{"Date":"2018-06-15","Time":"19:00"},{"Date":"2018-06-17","Time":"19:00"},{"Date":"2018-06-19","Time":"20:00"},{"Date":"2018-06-20","Time":"20:00"}]

The following transformation creates a separate row for each entry in the Events column, populating the other fields in the new rows with the data from the original row:

Extract Values into a List

You can also extract sets of values into an array list of values. 

Extract matches into array

Using 

Transformation:

...

...

Results:

...

["#dataprep", "#businessintelligence", "#CommitToCleanData", " # London"]

...

["#CommitToCleanData",  "#predictivetransformations", "#realbusinessintelligence", "0"]

...

["#dataprep", "#startwiththeuser","#realbusinessintelligence", "# Paris"]

...

["#NewYorklife", "dataprep", "bigdata", "0"]

...

...

Results:

Extract hashtags