Page tree

 

Support | BlogContact Us | 844.332.2821

 

Contents:

This documentation applies to Trifacta Wrangler. Download this free product.
Registered users of this product or Trifacta Wrangler Enterprise should login to Product Docs through the application.

Contents:


While wrangling your data, you might encounter problems with how data has been structured or formatted that you must fix prior to providing the content to your target system. You can use the methods in this section to locate problems with the content or data typing of your data. 

Locate mismatched values

When Trifacta® Wrangler evaluates a dataset sample, it interprets the values in a column against its expectations for the values. Based on the column's data type and internal pattern matching, values are categorized as valid, mismatched, or missing. These value categories are represented in a slender bar at the top of each column. 

  • mismatched value is any value that seems to be of a different data type than the type specified for the column. For example, if the value San Francisco appears in a column of Zip Code type, it would be marked as a mismatched value.

In the data quality bar, mismatched values are identified in red:

Tip: The type against which values are checked is displayed to the upper left of the data quality bar. Below, the data type is ZIP for Zip code data.


Figure: Mismatched values in red

Tip: From the Transformer page, click the mismatched values in a column's data quality bar to see their count, highlight them in the rows of the data grid, and trigger a set of suggestions for your review. See Data Grid Panel.

Mismatched values can be sourced from a variety of issues:

  • Values may be miskeyed into the source system.
  • The source system may introduce errors in output, particularly if the data is generated for export using a customized structure. 
  • Incorrect use of column delimiters may create offsets among fields in individual rows.
  • Data may be badly structured across a set of rows.
  • The column may be assigned the wrong data type. See Bad data typing below.

NOTE: When cleaning up bad data, you should look to work from bigger problems to smaller problems. If a higher percentage of a column's values have been categorized as mismatched data, it may indicate a wider problem with the data. In affected rows, verify if other columns' values are also mismatched. These rows should be reviewed and fixed first. When fixed, other mismatches may be fixed in other rows, too.

NOTE: Null values are identified as missing values in the data grid. See Manage Null Values.

Methods for fixing mismatched data

When you discover mismatched data in your dataset, you have the following basic methods of fixing it:

  1. Change the data type. If the percentage of mismatched rows is significant, you may need to change the data type for a better match. See Bad data typing below.

  2. Replace the values with constant values. This method works if it is clear to you that the values should be a single, consistent value. Select the mismatched values in the column, and then select one of the highlighted mismatched values. Use the replace transform to change the mismatched values to corrected values. 

    Tip: One easy way to fix isolated problems with mismatched values is to highlight a mismatched value in the data grid. A new set of suggestions is displayed. You can select the replace suggestion and then modify it to include the replacement value.

  3. Set values with other columns' values. You can use the set transform to fix mismatched values by replacing them with the corresponding values from other columns. See Set values using other columns below. 

  4. Use functions. Data can be fixed by using a function in conjunction with the set transform to replace mismatched values. See Use functions to fix mismatched values below. 

  5. Delete rows. Select the mismatched values and use the delete transform to remove the problematic rows. 
  6. Hide the column for now. You can remove the column from display if you want to focus on other things. Select Hide from the column drop-down. Note that hidden columns appear in any generated output. 
    1. You can choose to redisplay columns from the Column Browser. See Column Browser Panel.
  7. Drop the column. If the column data is unnecessary or otherwise unusable, you can drop the entire column from your dataset. Select Drop from the column drop-down.

Tip: Drop unnecessary columns as early as possible. Less data is easier to work with in the application and increases job execution performance.

NOTE: You might need to review and fixed mismatched data problems multiple times in your dataset. For example, if you unnest the data, additional mismatches might be discovered. Similarly, joins and lookups can reveal mismatches in data typing.

Mismatched values in transform code

In your transforms, mismatched data can be identified using row references as in the following:

set col: postal_code value: '00000' row:ISMISMATCHED(postal_code, ['Zipcode'])

Note that the single quotes are important around the value, which identifies the value as a constant.

Tip: In the above, note that the value Zipcode identifies the data type that is used for matching the column values. In this case, for greater specificity, you might want to identify the mismatched values in the column against the data type Integer, since all U.S. postal codes are positive integers. For more information on how to explicitly reference data types in your steps, see Valid Data Type Strings.

Trim data

Tip: To trim whitespace out of a column, use the following transform:

set col:column1 value:TRIM(column1)

This step may increase the number of missing values (for values that contain only whitespace characters) and the number of instances of matching values (for values that have spaces before and after an alphanumeric value).

Set values using other columns

You can use values from other columns to replace mismatched values in your current column. Using the previous example, mismatched postal codes are replaced by the corresponding value in the parent entity's postal code column (parent_postal_code):

set col: postal_code value: parent_postal_code row:ISMISMATCHED(postal_code, ['Zipcode'])

Use functions to fix mismatched values

In your transforms, you can insert a predefined function to replace mismatched data values. In the following example, the value for mismatched values in the score column are computed as the average of all values in the column:

set col: score value: AVERAGE(score) row:ISMISMATCHED(score, ['Decimal'])

Bad data typing

Tip: Particularly for dates, data is often easiest to manage as String data type. Trifacta Wrangler has a number of functions that you can deploy to manage strings. After the data has been properly formatted, you can change it to the proper data type. If you change data type immediately, you may have some challenges in reformatting and augmenting it. Do this step last.

 For columns that have a high percentage of mismatched values, the column's data type may have been mis-assigned. In the following example, a column containing data on precipitation in inches has been mis-typed as binary data:

Figure: Mis-typed column data type

To change a column's data type, click the type identifier at the top of the column and select a new type. In this case, you would select Decimal. 

NOTE: After you change the type, review the data quality bar again. If there are still mismatched values, review them to see if you can categorize the source of the mismatch.  

As you can see in the previous example, the precipitation column contains values set to T, which may be short for true. When the data type is set to Decimal, these values now register as mismatched data. To fix, you can replace all T values with 1.0 using the set transform.

Select an instance of T in the column and click the set transform card. Click Modify. For the value in the transform, enter 1.0. Your transform should look like the following:

set col: PrecipitationIn value: '1.0' row: MATCHES([PrecipitationIn], `{start}{bool}{end}`)

Tip: If possible, you should review and refer to an available schema of your dataset, as generated from the source system. If the data has also been mis-typed in the source system, you should fix it there as well, so any future exports from that system show the correct type.

Your Rating: Results: PatheticBadOKGoodOutstanding! 2 rates

This page has no comments.