Data table similarity comparison method based on effective numerical values

Abstract

The invention belongs to the technical field of data table similarity comparison, particularly relates to a data table similarity comparison method based on effective numerical values, and aims to solve the problem that whether two tables are similar or not is difficult to accurately recognize in the prior art because the numerical values in the tables are regularly tampered. The invention provides a definition of effective numerical values of a data table. The method comprises the following steps of: performing table data comparison, data cleaning and region block division on a data table on the basis of the definition of effective numerical values, further performing dynamic normalization calculation on characteristics of each region block, comprehensively calculating the similarity W according to the similarity W1... Wn of each region block in combination with dynamically endowed weight coefficients a1... an, and comparing the W with a threshold value b to judge whether the table is similar or not. According to the method, the similarity of the numerical table can be effectively analyzed, the comparison result is flexibly adjusted and corrected, multiple similarity comparison indexes are integrated, and the accuracy of the recognition result is remarkably improved.

Description

Background Art

[0002] When performing similarity comparison between two data tables, the common method requires comparing the corresponding row and column data items. When the corresponding rows, columns, or main parts are similar, the two tables can be judged as similar; otherwise, they are dissimilar.

[0003] Common methods include: 1. Direct comparison of data items: Determine whether two data tables are similar by comparing each data item in each row and each column of the two data tables one by one; 2. Comparison based on numerical hashing: First, extract data from the two data tables, then convert the data into text strings, then call the hash function for the data in the form of text strings for each row and each column to generate corresponding hash values, and finally judge whether the two data tables are similar by comparing the hash values; 3. Convert data values to text for distance comparison: First, convert the table data into text strings, then convert the text strings into numerical vectors by calculating TF-IDF, and finally judge whether the two data tables are similar by calculating the cosine similarity to measure the similarity of the text.

[0004] When performing similarity comparison of data tables, when the numerical values in the tables are tampered with regularly, such as deliberately magnifying or reducing, or swapping rows or columns, the existing methods are difficult to accurately identify whether the two tables are similar. For example, for Table A and Table B, the statistical unit of Table A is yuan, and the statistical unit of Table B is ten thousand yuan, and then the rows or columns of Table B are swapped. Obviously, although the numerical values of these two tables are different, the tables essentially describe the same piece of data.

[0005] To better solve such problems, the present invention proposes a definition of effective numerical values for data tables and a new method for similarity comparison of data tables based on effective numerical values. Through this method, it can accurately and effectively identify whether two data tables are similar. Summary of the Invention

[0006] To solve the above problems in the prior art, that is, when the numerical values in the table are tampered with regularly, the prior art is difficult to accurately identify whether the two tables are similar. In the first aspect of the present invention, a method for similarity comparison of data tables based on effective numerical values is proposed. The method includes the following steps: S1. Obtain the tables to be compared for similarity, denoted as Table A and Table B; respectively extract the numerical data in Table A and Table B to generate a numerical matrix Ma and a numerical matrix Mb; S2. Perform data preprocessing on Ma and Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; S3. Take each element in the Ma1 and Mb1 as the first element respectively, and construct a region block with a preset size of N×N corresponding to each first element. When the elements in each region block are insufficient, fill them up with the global default constant. S4. Divide each element in each of the region blocks by the first element value to generate a ratio relationship matrix based on the first element. S5. Perform numerical precision standardization data preprocessing on each of the ratio relationship matrices in sequence, and extract the data feature vectors of each region block. S6. Calculate the feature similarity of the data feature vectors of the region blocks corresponding to the Ma1 and the data feature vectors of the region blocks corresponding to the Mb1 to obtain the similarity of each region block; perform dynamic weighting on the similarity based on the weight coefficients of each region block to obtain the comprehensive similarity W. S7. Compare the comprehensive similarity W with the preset threshold b. If the W is greater than the b, it is determined that the table A and the table B are similar; otherwise, it is determined that they are not similar.

[0007] In some preferred embodiments, the data preprocessing includes data cleaning. The data cleaning includes: removing the 0, decimal point, exponent part, positive and negative signs, and non-effective digit symbols before and after the valid value.

[0008] In some preferred embodiments, when the N×N region block has a 3×3 structure and the first element is located at the edge of the normalization matrix and is the central element of the region block, fill in the adjacent existing elements in row-major order, and fill the vacant positions with the default constant.

[0009] In some preferred embodiments, for each region block, use the method of dynamic normalization operation to divide each element in the region block by the first element value to generate a ratio relationship matrix based on the first element.

[0010] In some preferred embodiments, performing numerical precision standardization data preprocessing on each of the ratio relationship matrices in sequence and extracting the data feature vectors of each region block includes: Perform rounding operation on each element in each of the ratio relationship matrices to obtain the operation result round(number, ndigits), where ndigits is the preset precision and Number is the value to be rounded. Perform data preprocessing on the operation result to retain the valid value and generate the data feature vectors of each region block.

[0011] In some preferred embodiments, the weight coefficient is dynamically assigned according to the number of supplemented default global constants.

[0012] In some preferred embodiments, the comprehensive similarity W is: ; where represents the similarity of each regional block, represents the weight coefficient of each regional block.

[0013] In the second aspect of the present invention, a data table similarity comparison system based on effective values is proposed. Based on the above-mentioned data table similarity comparison method based on effective values, the system includes a numerical value extraction module, a data cleaning module, a regional block construction module, a relative value conversion module, a precision standardization and feature extraction module, a similarity calculation and weighting module, and a similarity determination module. The numerical value extraction module is configured to: obtain the tables to be compared for similarity, denoted as table A and table B; respectively extract the numerical data in table A and table B to generate a numerical matrix Ma and a numerical matrix Mb; The data cleaning module is configured to: perform data preprocessing on Ma and Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; The regional block construction module is configured to: use each element in Ma1 and Mb1 as the first element respectively to construct a regional block with a preset size of N×N corresponding to each first element. Wherein, when the elements of each regional block are insufficient, they are filled with a global default constant; The relative value conversion module is configured to: divide each element in each regional block by the first element value to generate a proportional relationship matrix based on the first element; The precision standardization and feature extraction module is configured to: perform numerical precision standardization data preprocessing on each proportional relationship matrix in sequence, and extract the data feature vectors of each regional block; The similarity calculation and weighting module is configured to: calculate the feature similarity of the data feature vectors of the regional blocks corresponding to Ma1 and the data feature vectors of the regional blocks corresponding to Mb1 to obtain the similarity of each regional block; dynamically weight the similarity based on the weight coefficients of each regional block to obtain the comprehensive similarity W; The similarity determination module is configured to: compare the comprehensive similarity W with a preset threshold b. If W is greater than b, it is determined that table A and table B are similar, otherwise it is determined that they are not similar.

[0014] In the third aspect of the present invention, an electronic device is proposed, including: At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the processor, and the instructions are used to be executed by the processor to implement the method for comparing the similarity of data tables based on valid values.

[0015] The fourth invention of the present invention proposes a computer-readable storage medium storing computer instructions, and the computer instructions are used to be executed by the computer to implement the method for comparing the similarity of data tables based on valid values.

[0016] Advantages of the present invention: A definition of valid values for data table is proposed: the numbers "1-9" and single or multiple "0" between the numbers "1-9" are valid values; Based on the definition of valid values, table data comparison is performed. Specifically, data cleaning (removing 0, decimal point, and exponent part before and after valid values 1-9, and other non-valid value symbols), dividing the data table into region blocks, then performing dynamic normalization calculation of features for each region block, and according to Combined with the dynamically assigned weights , comprehensively calculate the similarity W, compare W with the threshold b to determine whether the tables are similar; Through the above method, the following remarkable effects are achieved: 1. Powerful recognition function: This method can effectively analyze the similarity of numerical tables. Even if the table values undergo regular tampering, numerical scaling, or row and column swapping, it can accurately recognize.

[0017] 2. Flexible and controllable recognition results: In actual application scenarios, according to specific requirements, the comparison results can be flexibly adjusted and corrected by adjusting the weights α and the threshold b.

[0018] 3. Higher recognition accuracy: Compared with common methods, this method combines multiple similarity comparison indicators and, with the flexible adjustment of the weights α and the threshold b, significantly improves the accuracy of the recognition results. Description of the Drawings

[0019] By reading the detailed description of the non-limiting embodiments with reference to the following drawings, other features, purposes, and advantages of the present application will become more obvious: Figure 1 It is a flowchart of the steps of a method for comparing the similarity of data tables based on valid values of the present invention. Detailed Embodiments

[0020] The present application will be further described in detail below with reference to the accompanying drawings and embodiments. It can be understood that the specific embodiments described herein are only used to explain the related invention, rather than limiting the invention. In addition, it should be noted that for the convenience of description, only parts related to the relevant invention are shown in the drawings.

[0021] It should be noted that, without conflict, the embodiments in the present application and the features in the embodiments can be combined with each other. The present application will be described in detail below with reference to the drawings and embodiments.

[0022] For a clearer description of a method for realizing a distributed seat free layout of the present invention, the following will be combined with Figure 1 to elaborate on each step in the embodiments of the present invention.

[0023] A definition of valid numerical values in a data table is proposed: the numbers "1-9" and single or multiple numbers "0" between the numbers "1-9" are valid numerical values; Based on the definition of valid numerical values, a method for comparing the similarity of data tables based on valid numerical values in the first embodiment of the present invention is shown in Figure 1 , and the method includes the following steps: S1. Obtain the tables to be compared for similarity, denoted as table A and table B; respectively extract the numerical data in the table A and the table B to generate a numerical matrix Ma and a numerical matrix Mb; table A, table B, numerical matrix Ma, and numerical matrix Mb are shown in the following table:

[0024]

[0025] The operation of converting table data into matrix form lays a foundation for subsequent systematic and comprehensive analysis of table data; S2. Perform data preprocessing on the Ma and the Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; In this embodiment, the data preprocessing includes data cleaning; The data cleaning includes: removing 0, decimal points, exponent parts, plus and minus signs, and non-valid digit symbols before and after valid numerical values; for example: ±6.05960 2 After cleaning, it is 60596; Ma1 and Mb1 are shown in the following table:

[0026] Through this data cleaning step, interference information is removed, making the data more standardized and unified, providing a reliable data basis for subsequent multi-dimensional similarity analysis, and improving the recognition accuracy; S3. Take each element in the Ma1 and Mb1 as the first element respectively, and construct a region block with a preset size of N×N corresponding to each first element. When the elements in each region block are insufficient, fill them up with the global default constant; In this embodiment, when the N×N region block is a 3×3 structure and the first element is located at the edge of the normalization matrix and is the central element of the region block, fill the actually existing adjacent elements in row-major order, and fill the vacant positions with the default constant; For example: for 24 in Ma1, its adjacent numbers are 13, 26, 5, 12, 4, 11, 2, 4; for 13 in Ma1, the number of adjacent numbers is less than 8. Assuming the default value of the default constant is 2, then the 8 adjacent numbers of 13 are 2, 2, 2, 2, 26, 2, 12, 24; It can perform refined analysis on tabular data, break down the overall similarity judgment into multiple local analyses, ensure that even if the tabular data is tampered with or transformed, the similarity can be accurately identified from the local features, and at the same time lay a foundation for flexibly assigning weights according to the adjacent element filling situation in the follow-up, realizing flexible control of the recognition results; S4. Divide each element in each of the region blocks by the first element value to generate a proportional relationship matrix based on the first element; In this embodiment, for each region block, use the method of dynamic normalization operation to divide each element in the region block by the first element value to generate a proportional relationship matrix based on the first element; For example: for 24 in Ma1, its adjacent numbers are 13, 26, 5, 12, 4, 11, 2, 4, then when calculating, each number is divided by 24; it is no longer the conventional fixed maximum normalization or minimum normalization. The value for the current normalization operation is the central number of each region block and is dynamic; Convert the data into a relative proportional relationship, eliminate the influence brought by the data magnitude difference, effectively identify the numerical scaling data transformation situation, that is, deliberately magnify or reduce, improve the recognition function, and at the same time provide a unified comparable data form for the calculation of the comprehensive similarity, enhancing the recognition accuracy; S5. Perform numerical precision standardization data preprocessing on each proportional relationship matrix in turn, and extract the data feature vectors of each region block; In this embodiment, performing numerical precision standardization data preprocessing on each proportional relationship matrix in turn and extracting the data feature vectors of each region block includes: Round each element in the proportionality relationship matrices to obtain the operation result round(number, ndigits), where ndigits is the preset precision and Number is the value to be rounded; for example: round(6.5648, 1), that is, round 6.5648 and retain 1 decimal place, and the result is 6.6; Perform data preprocessing on the operation result, retain the valid values, and generate the data feature vectors of each region block; the data feature vectors of each region block corresponding to Ma1 And the data feature vectors of each region block corresponding to Mb1 ; Through normalization and feature vector extraction, the key features of the data can be extracted, redundant information can be removed, the accuracy of subsequent similarity calculation can be improved, support can be provided for identifying tabular data after complex transformation, and the recognition function can be further enhanced; S6. Calculate the feature similarity between the data feature vectors of each region block corresponding to Ma1 and the data feature vectors of each region block corresponding to Mb1 to obtain the similarity of each region block ; Based on the weight coefficients of each region block Dynamically weight the similarity to obtain the comprehensive similarity W; among them, the similarity calculation can be the common techniques described in the background art, such as calculating the cosine similarity and comparing the hash values, which will not be elaborated here; In this embodiment, the weight coefficient Is dynamically assigned according to the supplemented default global constant quantity; the supplemented constant values represent the boundaries of the data table. For the convenience of the algorithm to unify the format, they do not belong to the content of the original table. Therefore, the more supplemented parts there are, the lower the weight of the similarity conclusion obtained during comparison; The comprehensive similarity W is: ; Wherein, Represents the similarity of each region block, Represents the weight coefficient of each region block; By dynamically weighting to calculate the comprehensive similarity, fully considering the actual situation of data distribution, the calculation method can be adjusted according to different data features, the comparison result can be flexibly adjusted and corrected by adjusting the weight α, the flexibility and controllability of the recognition result can be realized, and at the same time, multiple similarity comparison indexes are integrated, significantly improving the recognition accuracy; S7. Compare the comprehensive similarity W with the preset threshold b. If W > b, it is determined that table A and table B are similar, otherwise it is determined that they are not similar; By setting a preset threshold b, a clear standard is provided for table similarity judgment. The threshold b can be adjusted according to actual needs, flexibly adjusting and correcting the comparison results, and achieving flexible control of the recognition results. At the same time, the setting of this standard is combined with the comprehensive similarity calculation mentioned above to improve the recognition accuracy and enhance the recognition function; After the division of the regional blocks is completed, the similarity of each regional block is calculated. As long as it is not completely inconsistent, even if there are row and column exchanges, or rows and columns are added, there will still be some similarities in the corresponding regional blocks. Finally, through comprehensive comparison and adjustment of weights and thresholds, the impacts brought by row and column exchanges and the addition of rows and columns are effectively reduced, ensuring that even if the table data is tampered with by row and column exchanges, the similarity can be accurately recognized.

[0027] Although the various steps are described in the above order in the above embodiments, those skilled in the art can understand that in order to achieve the effects of this embodiment, different steps do not have to be executed in such an order. They can be executed simultaneously (in parallel) or in reverse order, and these simple changes are all within the protection scope of the present invention.

[0028] In the second embodiment of the present invention, a data table similarity comparison system based on effective values is proposed. Based on the above-mentioned data table similarity comparison method based on effective values, the system includes a numerical value extraction module, a data cleaning module, a regional block construction module, a relative value conversion module, a precision standardization and feature extraction module, a similarity calculation and weighting module, and a similarity determination module. The numerical value extraction module is configured to: obtain the tables to be compared for similarity, denoted as table A and table B; respectively extract the numerical data in table A and table B to generate a numerical matrix Ma and a numerical matrix Mb; The data cleaning module is configured to: perform data preprocessing on Ma and Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; The regional block construction module is configured to: use each element in Ma1 and Mb1 as the first element respectively to construct a regional block with a preset size of N×N corresponding to each first element. When the elements in each regional block are insufficient, they are filled with a global default constant; The relative value conversion module is configured to: divide each element in each regional block by the first element value to generate a proportional relationship matrix based on the first element; The precision standardization and feature extraction module is configured to: perform numerical precision standardization data preprocessing on each proportional relationship matrix in sequence to extract the data feature vectors of each regional block; The similarity calculation and weighting module is configured to: calculate the feature similarity of the data feature vectors of the respective regional blocks corresponding to Ma1 and the data feature vectors of the respective regional blocks corresponding to Mb1 to obtain the similarity of each regional block; dynamically weight the similarity based on the weight coefficients of the respective regional blocks to obtain a comprehensive similarity W; The similarity determination module is configured to: compare the comprehensive similarity W with a preset threshold b. If W is greater than b, it is determined that Table A and Table B are similar; otherwise, it is determined that they are not similar.

[0029] It should be noted that the data table similarity comparison system based on valid values provided in the above embodiments is only illustrated by dividing the above functional modules. In actual applications, the above functions can be allocated to different functional modules according to needs, that is, the modules or steps in the embodiments of the present invention can be further decomposed or combined. For example, the modules in the above embodiments can be combined into one module, or further split into multiple sub-modules to complete all or part of the functions described above. For the names of the modules and steps involved in the embodiments of the present invention, they are only used to distinguish each module or step and are not regarded as an improper limitation of the present invention.

[0030] A third embodiment of the present invention provides an electronic device, including: At least one processor; and a memory communicatively connected to at least one of the processors; wherein, the memory stores instructions executable by the processor, and the instructions are used to be executed by the processor to implement the data table similarity comparison method based on valid values as described above.

[0031] A fourth embodiment of the present invention provides a computer-readable storage medium storing computer instructions for being executed by a computer to implement the data table similarity comparison method based on valid values as described above.

[0032] Those skilled in the art can clearly understand that for the convenience and brevity of description, the specific working processes and related descriptions of the above-described electronic device and computer-readable storage medium can refer to the corresponding processes in the foregoing method embodiments and will not be repeated here.

[0033] Those skilled in the art should be able to realize that the modules and method steps of each example described in combination with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of both. The programs corresponding to the software modules and method steps can be placed in a random access memory (RAM), internal memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disk, removable disk, CD-ROM, or any other form of storage medium well-known in the technical field. To clearly illustrate the interchangeability of electronic hardware and software, the composition and steps of each example have been generally described according to functions in the above description. Whether these functions are executed in the form of electronic hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the present invention.

[0034] The terms "first", "second", etc. are used to distinguish similar objects, rather than to describe or represent a specific order or sequence.

[0035] The term "comprising" or any other similar term is intended to cover non-exclusive inclusion, so that a process, method, article, or device / equipment including a series of elements not only includes those elements, but also includes other elements not explicitly listed, or also includes elements inherent in these processes, methods, articles, or devices / equipment.

[0036] So far, the technical solution of the present invention has been described in combination with the preferred embodiments shown in the drawings. However, it is easy for those skilled in the art to understand that the protection scope of the present invention is obviously not limited to these specific embodiments. Without departing from the principle of the present invention, those skilled in the art can make equivalent changes or substitutions to the relevant technical features, and the technical solutions after these changes or substitutions will fall within the protection scope of the present invention.

Claims

1. A data table similarity comparison method based on valid numerical values, characterized in that: The method comprises the following steps: S1, obtaining tables to be compared for similarity, denoted as Table A and Table B; extracting numerical data from Table A and Table B respectively, generating a numerical matrix Ma and a numerical matrix Mb; S2, performing data preprocessing on the Ma and the Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; S3, taking each element in the Ma1 and the Mb1 as the first element, respectively, to construct a region block of a preset size of N×N corresponding to each first element, wherein when the elements of each region block are insufficient, a global default constant is used to fill the gap; S4, for each of the area blocks, dividing each element therein by the first element value to generate a proportional relationship matrix based on the first element; S5, performing numerical precision standardization data preprocessing on each proportional relationship matrix in turn, and extracting the data feature vector of each regional block; S6, calculating the feature similarity between the data feature vectors of each regional block corresponding to Ma1 and the data feature vectors of each regional block corresponding to Mb1, and obtaining the similarity of each regional block; dynamically weighting the similarity based on the weight coefficient of each regional block, and obtaining the comprehensive similarity W; S7, comparing the comprehensive similarity W with a preset threshold value b, if W is greater than b, it is determined that the table A and the table B are similar, otherwise it is determined that they are not similar.

2. A data table similarity comparison method based on valid numerical values ​​according to claim 1, characterized in that: The data preprocessing includes data cleaning; The data cleaning includes: removing zeros before and after valid values, decimal points, exponent parts, positive and negative signs, and non-valid digital symbols.

3. The method for comparing similarity of data tables based on valid numerical values ​​according to claim 1, characterized in that: When the N×N region block is a 3×3 structure, and the first element is located at the edge of the normalized matrix and is the central element of the region block, the actually existing adjacent elements are filled in sequence in row priority order, and the vacant positions are filled with default constants.

4. The method for comparing similarity of data tables based on valid numerical values ​​according to claim 1, characterized in that: For each area block, a dynamic normalization operation method is adopted to divide each element in the area block by the first element value to generate a proportional relationship matrix based on the first element.

5. The method for comparing similarity of data tables based on valid numerical values ​​according to claim 1, characterized in that: Each proportional relationship matrix is ​​subjected to numerical precision standardization data preprocessing in turn, and the data feature vector of each regional block is extracted, including: Round off each element in each proportional relationship matrix to obtain the operation result round (number,ndigits), where ndigits is the preset precision and Number is the value to be rounded off; The operation results are preprocessed to retain valid values ​​and generate data feature vectors for each area block.

6. The method for comparing similarity of data tables based on valid numerical values ​​according to claim 1, characterized in that: The weight coefficient Dynamically assigned according to the number of default global constants supplemented.

7. The method for comparing data tables similarity based on valid numerical values ​​according to claim 1, characterized in that: The comprehensive similarity W is: ； in, Indicates the similarity of each region block. Indicates the weight coefficient of each area block.

8. A data table similarity comparison system based on effective numerical values, based on a data table similarity comparison method based on effective numerical values ​​according to any one of claims 1 to 7, characterized in that: The system includes a numerical extraction module, a data cleaning module, a region block construction module, a relative value conversion module, an accuracy standardization and feature extraction module, a similarity calculation and weighting module, and a similarity determination module. The numerical extraction module is configured to: obtain tables to be compared for similarity, which are recorded as table A and table B; respectively extract numerical data in table A and table B to generate numerical matrix Ma and numerical matrix Mb; The data cleaning module is configured to: perform data preprocessing on the Ma and the Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1; The region block construction module is configured to: use each element in the Ma1 and the Mb1 as a first element, and construct a region block of a preset size of N×N corresponding to each first element, wherein when the elements of each region block are insufficient, a global default constant is used to fill the gap; The relative value conversion module is configured to: for each of the area blocks, divide each element therein by a first element value to generate a proportional relationship matrix based on the first element; The precision standardization and feature extraction module is configured to: perform numerical precision standardization data preprocessing on each proportional relationship matrix in turn, and extract data feature vectors of each regional block; The similarity calculation and weighting module is configured to: calculate the feature similarity between the data feature vectors of each regional block corresponding to Ma1 and the data feature vectors of each regional block corresponding to Mb1 to obtain the similarity of each regional block; dynamically weight the similarity based on the weight coefficient of each regional block to obtain the comprehensive similarity W; The similarity determination module is configured to compare the comprehensive similarity W with a preset threshold value b. If W is greater than b, it is determined that the table A and the table B are similar; otherwise, they are determined to be dissimilar.

9. An electronic device, characterized in that: include: at least one processor; And a memory communicatively connected to at least one of the processors; wherein the memory stores instructions executable by the processor, and the instructions are used to be executed by the processor to implement a data table similarity comparison method based on valid numerical values ​​as described in any one of claims 1-7.

10. A computer-readable storage medium storing computer instructions, wherein the computer instructions are used to be executed by the computer to implement the data table similarity comparison method based on valid numerical values ​​as described in any one of claims 1 to 7.

Images