A method for detecting changes in data tables during data cleansing processes

Abstract

The application discloses a method for detecting data table change in a data cleaning process, which first summarizes a data table change space according to changes caused by various data conversion operations on the data table, wherein the data table change space comprises two dimensions, namely a data object and a change attribute, the data object comprises a table, a row, a column and a cell, and the change attribute comprises a quantity attribute, a sequence attribute, a relationship attribute, a value attribute and a type attribute; then, changes of a data input table and a data output table in the data cleaning process are compared based on the data table change space. According to the comparison of changes of the data table in various change attributes of different data objects, the detection result of the data table change is more detailed and comprehensive, and the method can be applied to many scenes such as inferring semantics of data cleaning code and visualizing changes of the data table, so that the applicability of the detection method is stronger.

Description

Technical Field

[0001] This invention belongs to the field of data comparison, and in particular relates to a method for detecting changes in data tables during data cleaning. Background Technology

[0002] Two-dimensional tables are an effective means of organizing and arranging data, and people widely use various forms of tables in communication, scientific research, and data analysis. Because raw tables often contain "dirty" data, or the data format and content do not meet the expected goals, data workers must perform data cleaning. Data cleaning is a process of organizing complex and messy data into an ideal data format through data transformation operations (such as filling in missing values ​​and removing duplicate rows).

[0003] During data cleaning, data workers often need to compare changes in data tables to confirm whether the specified data transformation operations have been successfully performed, or to determine what data transformation operations should be performed next based on the changes in the current data tables. However, because data tables may contain a large amount of rows and columns of data, and data transformation operations can cause a wide variety of changes to the data tables, it is difficult for data workers to manually compare the changes in data tables before and after data cleaning.

[0004] While much work focuses on comparing time-series and graphical data, techniques for comparing tabular data are scarce. Existing data comparison tools such as ExcelCompare, DiffKit, Daff, and Compare can identify and calculate differences between two input data tables based on certain indicators (e.g., table size, cell content, number of unique rows and columns). Additionally, visualization tools like VisDB, iHAT, VisBricks, and TACO use heatmaps to visualize differences between data tables. However, these methods only compare differences between two data tables in general scenarios, with limited dimensions of detection and insufficient indicator richness. They fail to capture the impact of data transformation operations on data tables and are therefore unsuitable for describing changes in data tables during data cleaning. Summary of the Invention

[0005] The purpose of this invention is to address the shortcomings of existing technologies by providing a method for detecting changes in data tables during data cleaning.

[0006] The objective of this invention is achieved through the following technical solution: a method for detecting changes in data tables during data cleaning, characterized by comprising the following steps:

[0007] Step S11: Summarize the data table change space based on the impact of various data transformation operations on the data table. The data table change space includes two dimensions: data objects and change attributes. The data objects include tables, rows, columns, and cells. The change attributes include quantity attributes, order attributes, relational attributes, value attributes, and type attributes.

[0008] Step S12: Based on the change space of the data table, compare the changes of the data input table and the data output table during the data cleaning process.

[0009] Furthermore, the quantity attribute is used to describe the change in quantity of the data object after performing a data transformation operation.

[0010] Furthermore, the order attribute is used to describe the positional changes of the rows and columns in the data table.

[0011] Furthermore, the relational attributes are used to describe the arithmetic and set relationships between different data objects.

[0012] Furthermore, the value attribute is used to describe whether a specific value exists in the data object.

[0013] Furthermore, the type attribute is used to describe the changes in the data type of the data object.

[0014] Further, step S12 includes the following sub-steps:

[0015] Step S121: Compare the changes in the quantity attribute of the table, row, and column data objects in the data input table and the data output table respectively;

[0016] Step S122: Compare the changes in the order attribute of the row and column data objects in the data input table and the data output table respectively;

[0017] Step S123: Compare the changes in the row, column, and cell data objects of the data input table and the data output table in terms of the relational attributes;

[0018] Step S124: Compare the changes in the value attributes of the table, row, column, and cell data objects in the data input table and the data output table respectively;

[0019] Step S125: Compare the changes in the type attribute of the column data objects in the data input table and the data output table.

[0020] The beneficial effects of this invention are that it detects changes in data tables during data cleaning based on the table change space. This change space includes four types of data objects and five change attributes, totaling 20 comparison domains. This overcomes the bottlenecks of traditional methods, which suffer from limited detection dimensions and a lack of difference indicators when comparing data table differences. This allows for a more detailed and comprehensive description of the impact of data transformation operations on data tables. Furthermore, this invention is highly modular and can be applied to different work scenarios for data workers, making the detection method more versatile. Attached Figure Description

[0021] Figure 1 This is a flowchart of an embodiment of the present invention;

[0022] Figure 2 This is a schematic diagram illustrating the effective combinations and specific change characteristics of the table change space under four data objects and five change attributes in an embodiment of the present invention.

[0023] Figure 3 This is a schematic diagram illustrating the data transformation operation performed in this embodiment of the invention, which splits the composite values ​​in the "Level" column into new rows based on the delimiter.

[0024] Figure 4 This is a flowchart of step S12 in an embodiment of the present invention. Detailed Implementation

[0025] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.

[0026] The terminology used in this application is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. The singular forms “a,” “the,” and “the” used in this application and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to any and all possible combinations comprising one or more of the associated listed items.

[0027] It should be understood that although the terms first, second, third, etc., may be used in this application to describe various information, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, without departing from the scope of this application, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to determination."

[0028] It should be noted that the following embodiments can be combined where there is no conflict.

[0029] Please see Figure 1 This invention provides a method for detecting changes in a data table during data cleaning. The method, which detects changes in a data table during the data cleaning process, includes the following steps:

[0030] Step S11: Summarize the changes in the data table caused by various data transformation operations, forming a data table change space. The data table change space includes two dimensions: data objects and change attributes. Data objects include tables, rows, columns, and cells, while change attributes include quantity attributes, order attributes, relational attributes, value attributes, and type attributes.

[0031] It should be understood that data transformation operations include, but are not limited to, merging tables, deleting rows, inserting rows, inserting columns, etc. Different data transformation operations have different effects on the data table. It is advisable to summarize the potential changes in the data table as much as possible.

[0032] In this embodiment of the invention, the data object is the object whose data table changes during the data transformation operation, including tables, rows, columns, and cells. Rows and columns are the direct units that constitute a table, while cells are the direct units that constitute rows and columns.

[0033] In this embodiment of the invention, the change attributes describe the attributes of the data table that change during the data transformation operation, including the quantity attribute (Number), order attribute (Order), relation attribute (Relation), value attribute (Value), and type attribute (Type). Different data objects correspond to a series of data table change characteristics under different change attributes. For example, the change characteristics of "rows" and "quantity" refer to whether the number of rows in the data input table and data output table changes (e.g., increases, decreases, or remains unchanged) after the data transformation operation is performed. It should be noted that some data objects do not have change characteristics under certain change attributes. For example, a "cell" is a component of "rows" and "columns" in a table. The number of cells cannot be added or reduced independently; it must depend on the number of rows and columns. Therefore, the "cell" data object does not have a "quantity" change attribute. The effective combination of the table change space under four data objects and five change attributes in this embodiment of the invention and its specific change characteristics are referenced. Figure 2 In this embodiment, the quantity attribute describes the change in the quantity of data objects after performing data transformation operations. For example, table concatenation operations (such as left join and inner join) will reduce the number of tables, while filtering operations (such as filtering out students who passed the exam) will reduce the number of rows, but the number of columns will remain unchanged. The quantity attribute is only valid for tables, rows, and columns.

[0034] Among them, for table data objects, the change characteristics of their quantity attribute can be divided into 5 types: from no table to table (i.e., creating a table), from table to no table (i.e., deleting a table), both data input tables and data output tables exist and the number of data output tables is equal to the number of data input tables (i.e., modifying a table), both data input tables and data output tables exist but the number of data output tables is greater than the number of data input tables (i.e., splitting a table), and both data input tables and data output tables exist but the number of data output tables is less than the number of data input tables (i.e., merging a table).

[0035] For row and column data objects, the characteristics of their quantity attributes change are similar. Since the data output table or data input table does not exist when a table is created or deleted, we only consider the row and column quantity change characteristics in three cases: one-to-one table modification, one-to-many table splitting, and many-to-one table merging, which includes a total of 17 types. Specifically, for example, when modifying a table one-to-one, the quantity of row and column data objects can be divided into three types: increase, decrease, or remain unchanged. When splitting a table into one-to-many, the number of rows and columns in the output table needs to be aggregated before comparing the output and input tables. Aggregation functions include minimum (min), maximum (max), and sum (sum). These aggregated values ​​divide the entire numerical space into seven intervals: less than minimum (min), equal to minimum (min), greater than minimum (min) but less than maximum (max), equal to maximum (max), greater than maximum (max) but less than sum (sum), equal to sum (sum), and greater than sum (sum). The aggregated values ​​are then compared with the number of rows and columns in the input table. Based on these seven intervals, seven variation characteristics are obtained, such as the number of rows and columns in the input table being less than the minimum number of rows and columns in the output table, the number of rows and columns in the input table being equal to the minimum number of rows and columns in the output table, the number of rows and columns in the input table being greater than the minimum number of rows and columns in the output table but less than the maximum number of rows and columns in the output table, and so on. Similarly, there are 7 variations when merging tables in a many-to-one relationship, except that the data object of the aggregate function is the number of rows and columns of the data input table.

[0036] In this embodiment, the order attribute is used to describe changes in the position of rows and columns in a data table, such as manually changing the position of a column or sorting the values ​​in a column to change the position of a row. Therefore, it is only valid for row and column data objects. The change characteristics of the order attribute are considered based on the row and column index and the sorting status of the values ​​in the row and column. There are a total of 5 types. The row and column indexes of the data input and output tables can be divided into two types: unchanged and changed. The value sorting status is divided into three types: disorder, ascending, and descending.

[0037] In this embodiment, relational attributes are used to describe arithmetic relationships between different data objects (e.g., "average score" is the average of scores in all subjects), set relationships (e.g., "date of birth" is part of "ID number"), etc. Since the computational space involved in arithmetic relationships is too large to exhaustively cover, this embodiment only considers set relationships, which can be further subdivided into subsets. Superset There are 5 types: equal (=) and others (≠). Relational attributes are only valid for row, column, and cell data objects.

[0038] Specifically, for row and column data objects, the five set relationships correspond to five change characteristics, namely, whether a certain row / column in the output table has a set relationship with a certain row / column in the input table. For example, in the column extraction operation, the values ​​in the "Date of Birth" column of the output table are a subset of the values ​​in the "ID Number" column of the input table. Besides comparing the set relationships of row / column objects in the input and output tables, it is also necessary to compare whether different row / column objects in the same table have a set relationship. For example, if there are rows with identical values ​​in the input table (i.e., duplicate rows), but not in the output table, it can be inferred that a duplicate row deletion operation was performed in the data table.

[0039] Furthermore, for cell data objects, the five set relationships also correspond to five change characteristics, namely, whether a certain set relationship exists between a cell in the output table and a cell in the input table. For example, in the operation of splitting composite values ​​in the "Grade" column into new rows based on the delimiter "," (such as the separate_rows function in the tidyr package of R, as illustrated in the diagram below). Figure 3 As shown, the cell values ​​in the first two rows of the "Grade" column in the output table are a subset of the cell values ​​in the first row of the "Grade" column in the input table. It also compares whether there are cells with the same value in the same row or column. For example, in the operation of removing duplicate cells from the "Student ID" column, there are cells with the same value in the "Student ID" column of the input table, but not in the "Student ID" column of the output table.

[0040] In this embodiment, the value attribute is used to describe whether a specific value exists in a data object, and the value attribute is valid for all data objects. A missing value is a common specific value, and many data conversion operations are related to missing values, such as deleting rows with missing values, filling in missing values, etc. Therefore, it is possible to detect whether a missing value exists in a table, a certain row, a certain column, or a certain cell in the data input table and the data output table respectively. In addition, the concatenation symbol for the column merging operation, the delimiter for the column splitting operation, the replacement value for the replacement operation, etc. are specific values defined by the user. It is possible to detect whether a table, a certain row, or a certain column in the data output table contains a user-defined specific value, and to detect whether a certain cell in the data table is modified from one value to another user-defined specific value. For example, in the operation of replacing "Zhejiang University" with "Zhejiang University" in the "University" column, all cells with the value of "Zhejiang University" in the "University" column are modified to "Zhejiang University". In this operation, both "Zhejiang University" and "Zhejiang University" are user-defined specific values.

[0041] In this embodiment, the type attribute is used to describe the change in the data type of a data object. Since the data types of cells within a row in a table are usually different, while the data types of cells within a column are usually required to be consistent, only the data types of cells within a column are considered in the embodiments of this application. Data types can be divided into three categories, namely nominal type (or categorical type, such as the "Country" column), numerical type (such as the "Score" column), and time type (such as the "Date of Birth" column). Based on these three data types, it is possible to detect whether the data type of a certain column in the data input table and the data output table has changed, and specifically what data type it has changed from and to what other data type.

[0042] Step S12: Based on the data table change space, compare the changes between the data input table and the data output table during the data cleaning process.

[0043] As Figure 4 shown, this step S12 includes the following sub-steps:

[0044] Step S121: Compare the changes in the quantity attributes of the table, row, and column data objects in the data input table and the data output table respectively.

[0045] Step S122: Compare the changes in the order attributes of the row and column data objects in the data input table and the data output table respectively.

[0046] Step S123: Compare the changes in the relationship attributes of the row, column, and cell data objects in the data input table and the data output table respectively.

[0047] Step S124: Compare the changes in the value attributes of the table, row, column, and cell data objects in the data input table and the data output table respectively.

[0048] Step S125: Compare the changes in the type attributes of the column data objects in the data input table and the data output table.

[0049] It should be understood that the change detection process for data tables is very time-consuming because it involves many value comparisons based on cell content. For example, when detecting whether there is a set relationship between a column in the data input table and another column in the data output table, it is necessary to compare the value content of each cell in both columns.

[0050] In some embodiments, two pruning strategies are employed to accelerate the detection process, specifically:

[0051] Change pruning: Most change detection methods require both the input and output tables to exist. Therefore, if neither input nor output table exists, only the quantity attribute of the table's data objects needs to be checked. For example, for table creation operations, attributes such as order and relational properties do not need to be checked.

[0052] Data object pruning: Data cleaning experience shows that changes in data tables are primarily caused by newly added or modified data objects in the output table. Therefore, when detecting changes in cell content based on relational or value attributes, it's possible to focus solely on the relationship between newly added or modified data objects in the output table and data objects in the input table.

[0053] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the disclosure herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein. The specification and embodiments are to be considered exemplary only, and the true scope and spirit of this application are indicated by the claims.

[0054] It should be understood that this application is not limited to the precise structure described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this application is limited only by the appended claims.

Claims

1. A method for detecting changes in data tables during data cleaning, characterized in that, Includes the following steps: Step S11: Summarize the data table change space based on the impact of various data transformation operations on the data table. The data table change space includes two dimensions: data objects and change attributes. The data objects include tables, rows, columns, and cells. The change attributes include quantity attributes, order attributes, relational attributes, value attributes, and type attributes. Step S12: Based on the changes in the data table, compare the changes in the data input table and the data output table during the data cleaning process; Step S12 includes the following sub-steps: Step S121: Compare the changes in the quantity attribute of the table, row, and column data objects in the data input table and the data output table respectively; Step S122: Compare the changes in the order attribute of the row and column data objects in the data input table and the data output table respectively; Step S123: Compare the changes in the row, column, and cell data objects of the data input table and the data output table in terms of the relational attributes; Step S124: Compare the changes in the value attributes of the table, row, column, and cell data objects in the data input table and the data output table respectively; Step S125: Compare the changes in the type attribute of the column data objects in the data input table and the data output table.

2. The method for detecting changes in data tables during data cleaning according to claim 1, characterized in that, The quantity attribute is used to describe the change in quantity of the data object after performing a data transformation operation.

3. The method for detecting changes in data tables during data cleaning according to claim 1, characterized in that, The order attribute is used to describe the changes in the position of rows and columns in the data table.

4. The method for detecting changes in data tables during data cleaning according to claim 1, characterized in that, The relational attributes are used to describe the arithmetic and set relationships between different data objects.

5. The method for detecting changes in data tables during data cleaning according to claim 1, characterized in that, The value attribute is used to describe whether a specific value exists in the data object.

6. The method for detecting changes in data tables during data cleaning according to claim 1, characterized in that, The type attribute is used to describe the changes in the data type of the data object.

Images