A method for similarity comparison of data tables based on effective values

Abstract

The present application belongs to the technical field of data table similarity comparison, and particularly relates to a data table similarity comparison method based on effective values, aiming to solve the problem that existing technologies are difficult to accurately identify whether two tables are similar when the values in the tables are regularly tampered with. The present application proposes a definition of effective values of data tables; based on the definition of effective values, table data comparison, data cleaning, division of regional blocks of data tables, dynamic normalization calculation of features of each regional block, comprehensive calculation of similarity W according to the similarity W1...Wn of each regional block and the dynamically assigned weight coefficients a1...an, comparison of W with a threshold value b, and judgment of whether the tables are similar. The present application can effectively analyze the similarity of value tables, flexibly adjust and correct comparison results, and comprehensively consider multiple similarity comparison indexes, thereby significantly improving the accuracy of identification results.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of data table similarity comparison, and particularly relates to a data table similarity comparison method and system based on effective values, an electronic device and a computer readable storage medium. BACKGROUND

[0002] When two data tables are compared for similarity, a commonly used method needs to compare 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 not similar.

[0003] The commonly used method includes:

[0004] 1. Direct comparison of data items: whether two data tables are similar is determined by comparing each item of data in each row and each column of the two data tables one by one;

[0005] 2. Comparison based on numerical hash: first, data is extracted from the two data tables, then the data is converted into a text string, then a hash function is called to generate a corresponding hash value for each row and each column of the text string format data, and finally, whether the two data tables are similar is determined by comparing the hash values;

[0006] 3. Convert data values to text for distance comparison: first, the table data is converted into a text string, then the text string is converted into a numerical vector by calculating TF-IDF, and finally, the similarity of the text is measured by calculating the cosine similarity, so as to determine whether the two data tables are similar.

[0007] When comparing the similarity of data tables, the numerical values in the tables are tampered with in a regular manner, for example, deliberately enlarged or reduced, or rows or columns are exchanged. The existing method is difficult to accurately identify whether two tables are similar. For example, table A and table B, the statistical unit of table A is yuan, and the statistical unit of table B is ten thousand yuan. Then, the rows or columns of table B are exchanged. Obviously, although the numerical values of the two tables are different, the tables essentially describe the same data.

[0008] In order to better solve such problems, the present application proposes a definition of effective numerical values of data tables and a new data table similarity comparison method based on effective numerical values, which can accurately and effectively identify whether two data tables are similar. SUMMARY

[0009] In order to solve the above-mentioned problems in the prior art, i.e., the regular tampering of numerical values in the table, and the difficulty of the prior art to accurately identify whether two tables are similar, the present application proposes a data table similarity comparison method based on effective numerical values, which includes the following steps:

[0010] S1, obtaining tables to be compared for similarity, denoted as table A and table B; extracting numerical data in the table A and the table B respectively to generate a numerical matrix Ma and a numerical matrix Mb;

[0011] S2, performing data preprocessing on the Ma and the Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1;

[0012] S3, taking each element in the Ma1 and the Mb1 as a first element respectively, and constructing a region block with a preset size of N x N corresponding to each first element, wherein when the elements of each region block are insufficient, a global default constant is used to fill in the gaps;

[0013] S4, dividing each element in each region block by the first element value to generate a proportional relationship matrix with the first element as the reference;

[0014] S5, performing numerical accuracy standardization data preprocessing on each proportional relationship matrix in turn to extract the data feature vector of each region block;

[0015] S6, calculating the feature similarity between the data feature vector of each region block corresponding to the Ma1 and the data feature vector of each region block corresponding to the Mb1 to obtain the similarity of each region block; and dynamically weighting the similarity based on the weight coefficient of each region block to obtain a comprehensive similarity W;

[0016] S7, comparing the comprehensive similarity W with a 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.

[0017] In some preferred embodiments, the data preprocessing includes data cleaning.

[0018] The data cleaning includes removing 0 before and after the valid numerical value, decimal point, exponent part, sign, and non-valid numerical symbol.

[0019] In some preferred embodiments, when the N x N region block is a 3 x 3 structure, the first element is located at the edge of the normalized matrix and is the center element of the region block, and the adjacent elements actually existing are filled in order according to the row priority, and the default constant is used to fill in the vacant positions.

[0020] In some preferred embodiments, for each region block, a dynamic normalization operation is used to divide each element in the region block by the first element value to generate a proportional relationship matrix with the first element as the reference.

[0021] In some preferred embodiments, the numerical precision standardization data preprocessing is sequentially performed on each proportion relationship matrix, and data feature vectors of each regional block are extracted, including:

[0022] Each element in the proportion relationship matrix is subjected to a rounding operation to obtain an operation result round(number, ndigits), wherein ndigits is a preset precision, and Number is a numerical value that needs to be rounded;

[0023] The operation result is subjected to data preprocessing, and valid numerical values are retained to generate data feature vectors of each regional block.

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

[0025] In some preferred embodiments, the comprehensive similarity W is:

[0026] ;

[0027] Wherein, represents the similarity of each regional block, represents the weight coefficient of each regional block.

[0028] In the second aspect of the present application, a data table similarity comparison system based on valid numerical values is provided, which is based on the data table similarity comparison method based on valid numerical values. The system includes a numerical 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 extraction module is configured to: obtain tables to be compared for similarity, denoted as table A and table B; extract numerical data in the table A and the table B respectively to generate numerical matrices Ma and Mb;

[0029] The data cleaning module is configured to: perform data preprocessing on the Ma and the Mb to obtain normalized matrices Ma1 and Mb1;

[0030] The regional block construction module is configured to: take each element in the Ma1 and the Mb1 as a first element respectively, and 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, global default constants are filled to make up for the deficiency;

[0031] The relative value conversion module is configured to: divide each element in each regional block by the first element value to generate a proportion relationship matrix with the first element as the reference;

[0032] The precision standardization and feature extraction module is configured to perform numerical precision standardization data preprocessing on each proportion relationship matrix in sequence, and extract data feature vectors of each regional block.

[0033] The similarity calculation and weighting module is configured to calculate the feature similarity between the data feature vectors of each regional block corresponding to the Ma1 and the data feature vectors of each regional block corresponding to the Mb1, and obtain the similarity of each regional block; and dynamically weight the similarity based on the weight coefficients of each regional block to obtain a comprehensive similarity W.

[0034] The similarity determination module is configured to compare the comprehensive similarity W with a preset threshold b, and 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.

[0035] The third application of the application provides an electronic device, comprising:

[0036] At least one processor; and a memory connected in communication with 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 realize the data table similarity comparison method based on effective values.

[0037] The fourth application of the application provides a computer readable storage medium, which stores computer instructions, and the computer instructions are used to be executed by the computer to realize the data table similarity comparison method based on effective values.

[0038] The application has the following beneficial effects:

[0039] A definition of data table effective values is proposed: numbers "1-9" and single or multiple numbers "0" between numbers "1-9" are effective values;

[0040] Based on the definition of effective values, table data comparison is performed, specifically, data cleaning (removing 0, decimal point, exponential part before and after the effective values 1-9, other non-effective value symbols), dividing the data table into regional blocks, then calculating the dynamic normalization of the features of each regional block, and calculating the similarity W based on the dynamic weight Combined with the dynamically assigned weight The comprehensive similarity W is calculated, and the W is compared with the threshold value b to determine whether the tables are similar.

[0041] Through the above method, the following significant effects are achieved:

[0042] 1, strong recognition function: the method can effectively analyze the similarity of numerical tables, and can accurately identify even if the table values are regularly tampered with, scaled, or rows and columns are exchanged.

[0043] 2. Flexible and controllable recognition results: In actual application scenarios, the comparison results can be flexibly adjusted and corrected by adjusting the weight a and the threshold b according to specific needs.

[0044] 3. Higher recognition accuracy: Compared with common methods, the present method comprehensively considers multiple similarity comparison indexes and significantly improves the accuracy of the recognition results by means of flexible adjustment of the weight a and the threshold b. BRIEF DESCRIPTION OF DRAWINGS

[0045] Other features, objects and advantages of the present application will become more apparent from the following detailed description of non-limiting embodiments with reference to the attached drawings:

[0046] Figure 1 is a step flow chart of a data table similarity comparison method based on effective values according to the present application. DETAILED DESCRIPTION

[0047] 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 application, but not to limit the application. In addition, it should be noted that only the parts related to the application are shown in the drawings for the convenience of description.

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

[0049] In order to more clearly describe the implementation method of the distributed seat free layout according to the present application, the following will be described in combination with Figure 1 The steps in the embodiments of the present application will be described in detail.

[0050] A definition of effective values of data tables is proposed: numbers "1-9" and single or multiple numbers "0" between numbers "1-9" are effective values;

[0051] Based on the definition of effective values, a data table similarity comparison method based on effective values according to the first embodiment of the present application is described with reference to Figure 1 The method comprises the following steps:

[0052] S1, obtaining tables to be compared in similarity, denoted as table A and table B; extracting numerical data in the table A and the table B respectively to generate numerical matrix Ma and numerical matrix Mb; the table A, the table B, the numerical matrix Ma and the numerical matrix Mb are shown in the following table:

[0053]

[0054]

[0055] The operation of converting the table data into a matrix form lays a foundation for subsequent systematic and comprehensive analysis of the table data;

[0056] S2, data preprocessing is performed on the Ma and the Mb to obtain a normalized matrix Ma1 and a normalized matrix Mb1;

[0057] In this embodiment, the data preprocessing includes data cleaning;

[0058] The data cleaning includes removing 0, decimal point, exponential part, positive and negative signs, and non-effective number signs before and after the effective values; for example, ±6.05960 2 After cleaning, it is 60596;

[0059] Ma1 and Mb1 are shown in the following table:

[0060]

[0061] Through the data cleaning step, the interference information is removed, the data is more standardized and unified, a reliable data foundation is provided for subsequent multi-dimensional similarity analysis, and the recognition accuracy is improved;

[0062] S3, each element in the Ma1 and the Mb1 is taken as a first element respectively, and a region block with a preset size of N*N corresponding to each first element is constructed, wherein when the elements of each region block are insufficient, a global default constant is used for filling to make up;

[0063] In this embodiment, when the N*N region block is a 3*3 structure, the first element is located at the edge of the normalized matrix and is the center element of the region block, the adjacent elements actually existing are sequentially filled in row priority order, and the default constant is used to make up the vacancy position;

[0064] For example, the adjacent numbers of 24 of Ma1 are 13, 26, 5, 12, 4, 11, 2, and 4; the adjacent numbers of 13 of Ma1 are less than 8, assuming that the default value of the default constant is 2, then the 8 adjacent numbers of 13 are 2, 2, 2, 2, 26, 2, 12, and 24;

[0065] The table data can be finely analyzed, the overall similarity judgment is disassembled into multiple local analyses, even if the table data is tampered or transformed, the similarity can be accurately recognized from the local characteristics, and meanwhile, a foundation is laid for flexible allocation of weights according to the adjacent element filling condition, so that the recognition result is flexible and controllable;

[0066] S4, each element in each region block is divided by the first element value to generate a proportional relationship matrix taking the first element as a reference;

[0067] In this embodiment, for each region block, a dynamic normalization operation is used to divide each element in the region block by the value of the first element to generate a proportional relationship matrix based on the first element.

[0068] For example, if Ma1 is 24, and its adjacent numbers are 13, 26, 5, 12, 4, 11, 2, 4, then during the operation, each number is divided by 24; it is no longer the conventional fixed maximum or minimum value normalization, but the value of the current normalization operation is the center number of each region block, which is dynamic.

[0069] Transforming data into relative proportions eliminates the impact of differences in data magnitude, effectively identifies data scaling transformations, i.e., deliberate magnification or reduction, improves recognition capabilities, and provides a unified and comparable data format for calculating comprehensive similarity, thereby enhancing recognition accuracy.

[0070] S5, perform numerical precision standardization data preprocessing on each proportional relationship matrix in sequence, and extract the data feature vector of each region block;

[0071] In this embodiment, numerical precision normalization data preprocessing is performed sequentially on each proportional relationship matrix to extract the data feature vector of each region block, including:

[0072] Round each element in each proportional relationship matrix to obtain the result round(number, ndigits), where ndigits is the preset precision and Number is the value to be rounded; for example: round(6.5648,1) means: round 6.5648 to one decimal place, and the result is 6.6.

[0073] The calculation results are preprocessed to retain valid values ​​and generate data feature vectors for each region block; the data feature vectors for each region block corresponding to Ma1. Data feature vectors of each region block corresponding to Mb1 ;

[0074] By standardization and feature vector extraction, 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 recognizing complex transformed tabular data, and further enhance the recognition function.

[0075] 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, and obtain the similarity of each region block. Based on the weight coefficients of each region block The similarity is dynamically weighted to obtain a comprehensive similarity W; wherein the similarity calculation can be a common technique described in the background technology section, and the cosine similarity and the hash value comparison are not described in detail herein;

[0076] In the embodiment, the weight coefficient The weight is dynamically assigned according to the number of supplemented default global constants; the number of supplemented constants represents the boundary of the data table, and is not the original table content for the convenience of the algorithm and uniform format, therefore, the more the supplemented part, the lower the weight of the similarity conclusion obtained during comparison;

[0077] The comprehensive similarity W is:

[0078] wherein, represents the similarity of each region block, represents the weight coefficient of each region block;

[0079] The comprehensive similarity is calculated by dynamic weighting, fully considering the actual situation of data distribution, and the calculation method can be adjusted according to different data characteristics, the comparison result can be flexibly adjusted and corrected by adjusting the weight α, the recognition result is flexibly controllable, and the recognition accuracy is significantly improved by comprehensively comparing multiple similarity comparison indexes;

[0080] S7, comparing the comprehensive similarity W with a preset threshold b, if W>b, it is determined that the table A and the table B are similar, otherwise it is determined that they are not similar;

[0081] By setting the preset threshold b, a clear standard is provided for table similarity judgment, the threshold b can be adjusted according to actual needs, the comparison result can be flexibly adjusted and corrected, the recognition result is flexibly controllable, and the recognition accuracy is improved by combining the above comprehensive similarity calculation with the standard setting, and the recognition function is enhanced;

[0082] After completing the division of the region blocks, the similarity of each region block is calculated, as long as it is not completely inconsistent, even if the rows and columns are exchanged, or rows and columns are added, the corresponding region block will still have part of the similarity, finally, through comprehensive comparison and adjustment of the weight and threshold, the influence of row and column exchange and addition of rows and columns is effectively reduced, and even if the table data is tampered by row and column exchange, the similarity can be accurately recognized.

[0083] Although the above steps are described in the above order in the above embodiment, those skilled in the art can understand that, in order to achieve the effect of the embodiment, different steps do not have to be executed in such order, they can be executed simultaneously (in parallel) or in reverse order, and these simple changes are within the protection scope of the present application. ​

[0084] In a second embodiment of the present application, a system for similarity comparison of data tables based on effective values is provided, based on the method for similarity comparison of data tables based on effective values, the system comprising a value extraction module, a data cleaning module, a region 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 value extraction module is configured to: obtain tables to be compared for similarity, denoted as table A and table B; and extract numerical data in the table A and the table B respectively to generate a value matrix Ma and a value matrix Mb.

[0085] 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.

[0086] The region block construction module is configured to: take each element in the Ma1 and the Mb1 as a first element respectively, and construct a region block of a preset size N x N corresponding to each first element, wherein when the elements of each region block are insufficient, global default constants are filled to make up for the deficiency.

[0087] The relative value conversion module is configured to: divide each element in each region block by the first element value to generate a proportion relationship matrix taking the first element as a reference.

[0088] The precision standardization and feature extraction module is configured to: perform numerical precision standardization data preprocessing on each proportion relationship matrix in turn, and extract a data feature vector of each region block.

[0089] The similarity calculation and weighting module is configured to: calculate the similarity of the data feature vectors of each region block corresponding to the Ma1 and the data feature vectors of each region block corresponding to the Mb1, to obtain the similarity of each region block; and dynamically weight the similarity based on the weight coefficients of each region block to obtain a comprehensive similarity W.

[0090] The similarity determination module is configured to: compare the comprehensive similarity W with a preset threshold b, and 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.

[0091] It should be noted that the above embodiment provides a data table similarity comparison system based on effective values, and only the division of the above functional modules is exemplified, and in actual application, the above functions can be completed by different functional modules according to needs, that is, the modules or steps in the embodiment of the application are further decomposed or combined, for example, the modules of the above embodiment can be combined into one module, or can be further split into multiple sub-modules to complete all or part of the functions described above. The names of the modules and steps involved in the embodiments of the application are only for distinguishing the respective modules and steps, and are not considered as improper limitation of the application.

[0092] The third embodiment of the application provides an electronic device, comprising:

[0093] at least one processor; and a memory connected with the at least one processor in communication; 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 effective values.

[0094] The fourth embodiment of the application provides a computer readable storage medium, and the computer readable storage medium stores computer instructions, and the computer instructions are used to be executed by the computer to implement the data table similarity comparison method based on effective values.

[0095] Those skilled in the art can clearly understand the specific working process of the electronic device and the computer readable storage medium described above and the related description for the convenience and brevity of description, which can refer to the corresponding process in the foregoing method embodiment, and will not be repeated here.

[0096] Those skilled in the art should realize that the modules and method steps of the examples described in combination with the embodiments disclosed herein can be realized by electronic hardware, computer software or combination of the two. The programs corresponding to the software modules and method steps can be placed in random access memory (RAM), memory, read only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, register, hard disk, removable disk, CD-ROM or any other form of storage medium known in the art. In order to clearly illustrate the interchangeability of electronic hardware and software, the composition and steps of each example have been described in the above description. Whether the functions are realized by 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 realize the described functions for each specific application, but such implementation should not be considered beyond the scope of the application.

[0097] The terms "first", "second", etc. are used to distinguish similar objects, rather than to describe or indicate a particular order or sequence.

[0098] The term "comprising" or any other similar term is intended to encompass the inclusion of non-exclusive inclusion, so that a process, method, article or equipment / device including a series of elements not only includes those elements, but also includes other elements not explicitly listed, or also includes elements inherent to the process, method, article or equipment / device.

[0099] So far, the technical solutions of the present application have been described in combination with the preferred embodiments shown in the drawings, but those skilled in the art can easily understand that the protection scope of the present application is obviously not limited to these specific embodiments. Those skilled in the art can make equivalent changes or replacements to the related technical features without departing from the principles of the present application, and the technical solutions after the changes or replacements will all fall within the protection scope of the present application.

Claims

1. A method for similarity comparison of data tables based on significant 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 in the table A and the table B respectively to generate 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 a first element respectively, and constructing a region block of a preset size N*N corresponding to each first element, wherein when the elements of each region block are insufficient, a global default constant is used for filling; S4, dividing each element in each region block by the first element value to generate a proportional relationship matrix taking the first element as a reference; S5, performing numerical accuracy standardization data preprocessing on each proportional relationship matrix in turn to extract a data feature vector of each region block; S6, calculating the feature similarity between the data feature vector of each region block corresponding to the Ma1 and the data feature vector of each region block corresponding to the Mb1 to obtain the similarity of each region block; and performing dynamic weighting on the similarity based on the weight coefficient of each region block to obtain a comprehensive similarity W; S7, comparing the comprehensive similarity W with a preset threshold b, and if the W is greater than the b, determining that the table A and the table B are similar, otherwise, determining that they are not similar; The data preprocessing comprises data cleaning; The data cleaning comprises removing 0 before and after the valid numerical value, decimal point, exponent part, sign, and non-valid numerical symbol; The comprehensive similarity W is: wherein, represents the similarity of each region block, represents the weight coefficient of each region block.

2. The method of claim 1, wherein, When the N*N region block is a 3*3 structure, the first element is located at the edge of the normalized matrix and is the center element of the region block, and the existing adjacent elements are filled in turn in row priority order, and the default constant is used to fill the vacancy position.

3. The method of claim 1, wherein, For each region block, a dynamic normalization operation method is used to divide each element in the region block by the first element value to generate a proportional relationship matrix taking the first element as a reference.

4. The method of claim 1, wherein, The numerical accuracy standardization data preprocessing is performed on each proportional relationship matrix in turn to extract a data feature vector of each region block, comprising: Performing a rounding operation on each element in each proportional relationship matrix to obtain an operation result round (number, ndigits), wherein ndigits is a preset accuracy, and Number is a numerical value to be rounded; Performing data preprocessing on the operation result to retain valid numerical values to generate a data feature vector of each region block.

5. The method of claim 1, wherein, the weight coefficients Dynamically assigned according to the number of supplemental default global constants.

6. A system for similarity comparison of data tables based on significant values, based on the method for similarity comparison of data tables based on significant values according to any one of claims 1 to 5, characterized in that, The system comprises 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, denoted as table A and table B; and extract numerical data in the table A and the table B respectively to generate a numerical matrix Ma and a 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: take each element in the Ma1 and the Mb1 as a first element respectively, and construct a region block with a preset size of N*N corresponding to each first element, wherein when the elements of each region block are insufficient, global default constants are used for filling. The relative value conversion module is configured to: divide each element in each region block by a first element value to generate a proportional relationship matrix with the first element as a reference for each region block. 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 a data feature vector of each region block. The similarity calculation and weighting module is configured to: calculate the similarity of the data feature vectors of each region block corresponding to the Ma1 and the data feature vectors of each region block corresponding to the Mb1, to obtain the similarity of each region block; and dynamically weight the similarity based on the weight coefficients of each region block to obtain a comprehensive similarity W.

7. An electronic device, comprising: The similarity determination module is configured to: compare the comprehensive similarity W with a preset threshold b, and if the W is greater than the b, determine that the table A and the table B are similar, otherwise, determine that they are not similar. It comprises: at least one processor; and a memory connected in communication with 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 of claim 1-5.

8. A computer readable storage medium, the computer readable storage medium stores computer instructions, the computer instructions are used to be executed by the computer to implement the method of claim 1-5.

Images