Data cleaning typically refers to finding and correcting identifiable errors in data files, including checking for data consistency, processing invalid and missing values, and so on. After the user completes the data input, they can perform further data cleaning operations on the input data. This article will introduce the functions of the data cleaning operator for you.

2.1 Field Settings #

• Field Settings supports selecting reserved fields, setting field types, setting field names, setting field sorting, and batch synchronization of upstream field descriptions to target field names.
  
• The "Upstream Field Description" column in the field list is used to view the description of the upstream field and does not support direct editing. At present, only read field descriptions from upstream nodes are supported, and other upstream operators are not supported for the time being.
  Check the fields that need to be processed, and click "Upstream Field Description Synchronized as Target Field Name" after "Show Duplicate Fields Only" to write the description of the selected field into the corresponding target field name. Unchecked fields are not affected. After the synchronization is completed, the system will prompt the number of synchronized fields; when the fields are not checked, it will prompt to select the fields that need to be synchronized first.

  tip

  The target field name only supports Chinese, upper and lower case letters, numbers, and underscores, and is no longer than 64 characters in length. After manual input or synchronization, the system will automatically filter the special characters at the beginning and end, and replace the special characters such as spaces and punctuation in the middle with underscores; when the length limit is exceeded, it prompts "the number of characters does not exceed 64 characters".

  
• And it provides advanced configuration functions, supporting one-click modification of the target field name format, such as one-click to capital, one-click lowercase, one-click repair of illegal characters, upstream node field names A to Z ascending order, upstream node field names A to Z descending order, target field name A to Z ascending order, and target field name A to Z descending order.
  

2.2 Connection #

Support for creating joins between multiple tables.

• Select a connection method
• Set connection field
• Select a reserved field

2.3 Merging rows #

Used to merge multi-table data.
Operation panel
Click Configure the matching relationship of multiple table fields. Note that only fields in the same format support matching. If the field formats are different, please add operators such as field settings or calculated columns to process the field formats and merge them.
Processing example
Drag different tables into the canvas and click on one of the tables that needs to be merged to add the merged row node.

Click the + button on the right side of other tables that need to be merged, and drag it to the left side of the merge row operator;

Configure the match at the bottom of the page and click Execute to save configuration.

2.4 Aggregation #

The aggregate calculation of detailed data is realized by grouping.

• Select Group, drag and drop fields to "Group"
• Select the aggregation field and method: drag and drop the field to "aggregation" to change the aggregation method and set the aggregated field name.

2.5 Calculated columns #

Calculate column operators, support custom expressions, use Spark functions to process upstream fields and add new fields. The configuration flow for computed columns can clearly display new fields in table form.

2.6 Filter rows #

Select a field, confirm the filter conditions, support two layers and/or logical relationships.

2.7 Column transfer #

Convert the multi-column indicator of the header into one column for display, and turn a wide table into a high table.
Achieved effect
For example, students' subject scores, Chinese, English, and Mathematics are each a field.

By switching columns, you can combine the grades of three courses into a single "grade" field, and add a "subject" field to distinguish the grades of each subject.

Processing example
Add a column transition node after the model.

Check the fields that need to be converted, and select "Chinese", "Mathematics", and "English" in this case.

Set the converted column names for the original field names and field values, in this case "discipline" and "achievement" respectively.

Execute the node to preview the data.

2.8 Row to column #

Converts a column of field values to a header column, and a high table to a wide table.
Achieved effect
For example, the student's subject score sheet, with subject and grade fields, records the grades of each subject.

Through row and column conversion, subject grades can be divided into three fields: "Chinese grades", "Mathematics grades", and "English grades", which become a wide table of students' granularity.

Processing example
Add row to column nodes after the model.

Select the fields that need to be row-to-column and the corresponding value fields, and put the other reserved fields into the Reserved Fields column. Then, click Configure to add a new column, that is, which fields need to be split into "Subjects", and fill in the corresponding "Subject" values.
This step also supports simultaneous aggregation of value fields.


The content of the red box in the figure above is the table structure after row conversion. Execute the node to preview the data.

2.9 Data Splitting #

The data splitting and classification operator includes: data splitting, field splitting operator, etc., which can realize the splitting of a piece of data according to certain conditions and obtain the effect of multiple pieces of data. For operator specific introduction, please see Data Splitting Operator Category.

2.10 Indexing of strings #

A type conversion operator that maps the value of a specified property to a numeric index so that an operator that can only process numeric data can also process the property. This operator is generally used for data preprocessing, and is not suitable for performing it on columns containing continuous data, such as ID columns.

Configuration paraphrase
Maps the value of the specified attribute to a numeric index so that operators that can only process numeric data can also process the attribute.
Parameter setting
Select all string type properties to index:
After clicking this parameter, there is no need to manually set the columns that need to be indexed, and the system will automatically select all attribute values of string type to establish an index; if you do not click this parameter, you need to set the inputColumns (input column) parameter, click Select Attribute, add the attribute name that needs to be indexed to the selected attribute, and click Apply to select a subset of attributes.

Invalid data processing method
You can choose to ignore invalid data(skip), or report an error(error), or do no processing(keep).

Index order
The index order represents the rules for string indexing, which are divided into four types: freque ncyDesc, frequencyAsc, alphabetDesc, and alphabetAsc.

• frequencyDesc and frequencyAsc represent a string type column that is sorted by the frequency of a string, in descending and ascending order, respectively.
• alphabetDesc and alphabetAsc represent a sort in the encoded order of a string type column, descending and ascending, respectively.

2.11 Replacement of missing values #

In the data model, there may be some fields with null values. The replacement missing value operator supports replacing missing data with the maximum/minimum/average, highest frequency, or custom value of the column.

Configuration paraphrase
Replace missing data with the column's maximum/minimum/average, highest frequency, or custom value
Field settings

• Fields to be filled: Missing value filling will fill the selected fields with missing values
• Parameter Settings -- Filling Rules:
  Execute the corresponding execution rules for the selected column. Numeric columns support: 0, minimum, maximum, median, average, Mode, and custom values. Character types support minimum, maximum, and custom values.

Processing example
As shown in the figure, there are empty subject grades in the model.

Add a replacement missing value node, and select the fields and padding rules that need to be filled with missing values in the node settings below.

If the field type is selected as Numeric, the padding rule can be selected as 0, Min, Max, Median, Average, Mode, Custom value. Support character types such as minimum, maximum, and custom values; Other types such as custom value padding are supported, for instance "other".
Click Execute to preview the processed data.

2.12 Free Sorting #

2.12.1 Operator interpretation #

Select a sorting field to sort all the data or some combination of dimensions, and you can choose to intercept the data of the previous K value according to the sorting. K is the sorting number of the interception. If you take the top 100 according to the score, 100 is the K value.
Group sorting function is equivalent to the process of setting partition and sorting by window function.

2.12.2 Scenario Interpretation #

There is data including sales amount, sales dates, and sales price. First, sort the sales records of Top100 by sales price as a whole, select sales_price as the sorting field, and take the top 100 values in reverse order. If you sort the sales of each salesperson in each shopping mall and take out the sales records of Top100 separately, then add the selected shopping malls and sales personnel as the grouping fields.

2.12.3 Configuration item description #

• Sorting mode: Support global sorting and group sorting. Global sorting is to sort all the data according to the sorting field, and group sorting is to set different combinations according to the grouping field to sort the data in each combination.
• Grouping fields: Set the type of sorting, that is, sort the content under each category, such as selecting multiple fields, then define different classes for sorting according to the combined results of multiple fields.
• Sorting field: The judgment condition of sorting, such as sorting according to the score, the score is the sorting field, and two sorting methods are supported: descending/ascending:
  • Descending, that is, in reverse order according to the results of the sorting field
  • Ascending, that is, sorting according to the result order of the sorting field
• If you open the [Sort Only] button, you only need to sort the data according to the sorting method and the sorting field, and mark the specific sequence number value.
• Serial number field name: According to the sorting method, the specific serial number is stored, and the specific function of the serial number can be defined, such as the score ranking field.

2.13 Deduplicate #

Used to delete duplicate rows from a dataset.
Field settings
Fields to be deduplicated: deduplicate according to the selected field, if the number of rows in the selected field is duplicated, randomly keep one row.
If multiple fields are checked, multiple fields must be the same before the piece of data is deleted.
Processing example
As an example, the upstream data contains 6 pieces of data, three of which have the name "Zhang San", and two pieces of data have the same user name and phone number;

When we add the deduplicate operator and add the deduplicate fields for name and phone and execute the current node;

We can find that the deduplicated data contains a total of 5 pieces, of which the data with serial number 2 is deleted because the name and phone number of 1 are the same. The details are as follows:

When we add the deduplicate operator and add the deduplicate fields for name and phone and execute the current node;

We can find that the deduplicated data contains a total of 3 pieces, of which the data with serial numbers 2 and 3 are deleted because they have the same name as 1. The details are as follows: