Data cleaning method, system and data center platform

Abstract

The present application relates to the field of data processing technology, and specifically to a data cleaning method, system, and data center platform. The method comprises: obtaining information data sets of different users; calculating the parameter correlation of each category in the information data sets; determining the relative difference between any two users; clustering all users; constructing a parameter matrix corresponding to various parameters in each cluster; determining the abnormality of each column in the parameter matrix, and cleaning the data in the parameter matrix corresponding to various parameters in all clusters to obtain cleaned information data sets of different users. The present application can more accurately identify abnormal data, improve the quality of data cleaning, and enhance the accuracy of data mining and analysis.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data cleaning method, system and data center platform. Background Art

[0002] In the era of rapid development of IoT technology, advanced IoT sensing devices are widely used to collect various types of data. These rich data resources provide a solid data foundation and strong support for optimizing product performance, improving service quality, and expanding business areas.

[0003] However, IoT data comes from a wide range of sources, has complex formats, and requires high real-time performance, presenting numerous challenges for data processing. Numerous data quality issues within the raw data can impact the accuracy and reliability of data analysis results. Traditional data cleaning methods typically perform anomaly analysis on a single parameter for a single user. This approach ignores anomalies between users with similar data characteristics, making it difficult to accurately identify and process anomalies. This results in poor data cleaning quality and hinders in-depth data mining and analysis. Summary of the Invention

[0004] In order to solve the above technical problems, a data cleaning method, system and data center platform are provided to solve the existing problems.

[0005] The solution to the technical problem of this application is to provide a data cleaning method, system and data center platform, including the following steps:

[0006] In a first aspect, an embodiment of the present application provides a data cleaning method, the method comprising the following steps:

[0007] Acquire information data sets of different users and preprocess the information data sets, wherein the information data sets contain data of various parameters corresponding to each category at different times;

[0008] Analyzing the correlation between local fluctuations of different parameters corresponding to each category in the information dataset, and calculating the parameter correlation degree of each category in the information dataset;

[0009] Determine the relative difference between any two users by comparing the data difference of the same parameter in the information data set between the two users and the parameter correlation; and cluster all users based on the relative difference.

[0010] Based on the data of all users at different times under various parameters in each cluster, a parameter matrix corresponding to various parameters in each cluster is constructed; the abnormality degree of each column in each row of the parameter matrix and the data abnormality of the user corresponding to each row are used to determine the abnormality degree of each column in the parameter matrix. The data in the parameter matrix corresponding to various parameters in all clusters are cleaned to obtain the cleaned information data sets of different users.

[0011] Preferably, the calculating of the parameter correlation degree of each category in the information data set includes:

[0012] Combining the data of various parameters at all times in the information data set into a time parameter sequence;

[0013] Using a moving standard deviation algorithm to obtain the moving standard deviation of each element in the time parameter sequence;

[0014] Calculate the correlation degree of the moving standard deviation of all elements in the time parameter sequence between any two parameters corresponding to each category in the information data set, and take the mean of the correlation degrees of all any two parameters corresponding to each category as the parameter correlation degree of each category in the information data set.

[0015] Preferably, determining the relative difference between any two users includes:

[0016] Analyzing the difference in the parameter correlation between any two users in all categories in the information data set to obtain the correlation difference between the any two users;

[0017] No. User and The relative difference between users The calculation formula is: ,in, For the User and The difference in said associations between users; For the The information data set of users The time parameter sequence corresponding to the parameters, For the The information data set of users The time parameter sequence corresponding to the parameters, To calculate the distance, is the number of all parameters corresponding to all categories in the information dataset.

[0018] Preferably, the association difference is a metric distance of the parameter associations of all categories in the information dataset between any two users.

[0019] Preferably, the parameter matrix is ​​constructed as follows:

[0020] The time parameter sequences corresponding to all users under various parameters in each cluster are combined into a parameter matrix, where each row element corresponds to a user.

[0021] Preferably, a further measurement method for the abnormality degree is: performing abnormality detection on all column elements in each row of the parameter matrix respectively to obtain an abnormality score of each column element in each row.

[0022] Preferably, determining the abnormality of each column in the parameter matrix includes:

[0023] Calculating the weight corresponding to each row in the parameter matrix through multi-criteria decision analysis;

[0024] The calculation formula for the abnormality of each column in the parameter matrix is: ,in, is the parameter matrix The abnormality of the column, is the parameter matrix Rank Anomaly score of column elements, is the parameter matrix The weight corresponding to the row, is the number of all rows in the parameter matrix.

[0025] Preferably, the cleaning of the data in the parameter matrix corresponding to various parameters in all clusters includes:

[0026] A column of data corresponding to the abnormality degree greater than a preset threshold in the parameter matrix of various parameters in each cluster is regarded as abnormal data, all abnormal data in the parameter matrix are removed, and missing values ​​are filled in the removed data.

[0027] In a second aspect, an embodiment of the present application also provides a data cleaning system, which includes a memory, a processor, and a computer program stored in the memory and running on the processor. When the processor executes the computer program, it implements the steps of any one of the above-mentioned data cleaning methods.

[0028] In a third aspect, an embodiment of the present application further provides a data center platform, which performs visual analysis on the cleaned information data set.

[0029] This application has at least the following beneficial effects:

[0030] This application analyzes the change correlation between different parameters under each category in the information data set of a single user, and calculates the parameter correlation of each category. Its beneficial effect is that it takes into account the fluctuation of data in different time periods to reflect the response of data changes between different parameters under each category; secondly, through the data difference changes between any two users under the same parameter and the difference in parameter correlation between any two users, the relative difference between the two users is calculated, and all users are clustered. Its beneficial effect is that it takes into account the difference in data features between the two users, and then through clustering, users with similar data features are divided into one category, so that the data changes of all users in the same category can be analyzed for anomalies later; various The method constructs a parameter matrix based on the data of all users at different times under the parameters, analyzes the abnormal changes of the elements in each row and column of the parameter matrix, and calculates the abnormality degree of each column in the parameter matrix. The beneficial effect is that it takes into account the data abnormality of all users in the same cluster under the same parameter, avoids performing abnormal processing and analysis on the data of one parameter of a single user, and causes errors in the identification and processing of abnormal data; and then removes and fills the abnormal data in the parameter matrix through the abnormality degree. The beneficial effect is that by considering the data changes of multiple users in the same cluster, the abnormal data of various parameters of each user are analyzed, which can more accurately identify abnormal data, improve the quality of data cleaning, and enhance the accuracy of data mining and analysis. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] A data cleaning method of the present application is further described in detail below with reference to the accompanying drawings.

[0032] Figure 1 A flowchart of the steps of a data cleaning method provided in an embodiment of the present application;

[0033] Figure 2 A flowchart of the steps of the method for obtaining the parameter correlation degree of each category in the information data set provided in an embodiment of the present application. DETAILED DESCRIPTION

[0034] In order to make the purpose, technical solutions and advantages of this application more clearly understood, the following, in conjunction with the accompanying drawings and implementation examples, further describes in detail a data cleaning method, system and data center platform proposed in this application. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0035] Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs.

[0036] See also Figure 1, which shows a flowchart of a data cleaning method provided by an embodiment of the present application, the method comprising the following steps:

[0037] Step 1: Acquire information data sets of different users and preprocess the information data sets.

[0038] Obtain information datasets of different users;

[0039] In this embodiment, by performing data cleaning on the energy usage data provided by different users, the power data, water resource data, and gas data of each user are obtained through smart electricity meters, smart water meters, and smart gas meters, respectively. The power data includes real-time power, accumulated power, voltage, and current parameters; the water resource data includes instantaneous water flow and accumulated water consumption, and the gas data includes instantaneous gas flow and accumulated gas consumption, etc., and finally form an information data set. The collection time interval is 15 minutes. As other implementation methods, the implementer can set it according to the actual situation.

[0040] Therefore, the information data set obtained for each user contains data of various parameters corresponding to each category at different times, among which each category includes electricity data, water resources data, and gas data, and various parameters include real-time power, accumulated electricity, voltage, current, instantaneous water flow, accumulated water consumption, instantaneous gas flow, and accumulated gas consumption.

[0041] Furthermore, the information data set is preliminarily preprocessed, and missing values ​​and data type conversion are performed on the information data set; in this embodiment, the mean filling method is used to fill missing values, wherein the mean filling method is a well-known technology and will not be described in detail here. As other implementation methods, the implementer may adopt other methods of the existing technology, such as the majority filling method, the KNN algorithm filling method, etc., and this embodiment does not impose special restrictions on this; secondly, for character data, it is converted into numerical data, wherein the data type conversion is a well-known technology and will not be described in detail here.

[0042] At this point, the information datasets of different users are obtained after preprocessing.

[0043] Step 2: Analyze the correlation between local fluctuations of different parameters corresponding to each category in the information dataset, and calculate the parameter correlation degree of each category in the information dataset.

[0044] Secondly, due to the influence of the equipment itself and environmental interference during the collection process, the collected data quality is poor. Moreover, due to the differences in data collected at different time periods, anomaly analysis based solely on data collected from the same user may result in large errors. By considering the similarity characteristics of data between different users, users with similar data characteristics can be compared and analyzed, allowing for accurate analysis and identification of anomalies in data from different users.

[0045] First, analyze the response of the changes between different parameters of the same user and calculate the parameter correlation. The step flow chart of the method for obtaining the parameter correlation of each category in the information data set provided in the embodiment of the present application is as follows: Figure 2 As shown, specifically including:

[0046] Combining the data of various parameters at all times in the information data set into a time parameter sequence;

[0047] In this embodiment, the real-time power, cumulative power, voltage, current, instantaneous water flow, cumulative water consumption, instantaneous gas flow and cumulative gas consumption at all moments in the information data set are respectively combined into time parameter sequences, thereby obtaining the time parameter sequences corresponding to the real-time power, cumulative power, voltage, current, instantaneous water flow, cumulative water consumption, instantaneous gas flow and cumulative gas consumption.

[0048] Using a moving standard deviation algorithm to obtain the moving standard deviation of each element in the time parameter sequence;

[0049] It should be noted that the moving standard deviation algorithm is a well-known technology and will not be described in detail here; secondly, the moving standard deviation reflects the fluctuation of various parameters within a local time range.

[0050] Calculating the correlation between the moving standard deviations of all elements in the time parameter sequence between any two parameters corresponding to each category in the information data set; taking the mean of the correlation between all any two parameters corresponding to each category as the parameter correlation degree of each category in the information data set;

[0051] In this embodiment, the degree of correlation is measured by calculating the Pearson correlation coefficient of the moving standard deviation of all elements in the time parameter sequence between any two parameters under each category in the information data set, wherein the calculation of the Pearson correlation coefficient is a well-known technology and will not be repeated here. As other implementation methods, the implementer may adopt other methods of the existing technology, such as the Spearman correlation coefficient, etc. This embodiment does not impose special restrictions on this. For example, the Pearson correlation coefficient of the moving standard deviation of all elements in the time parameter sequence corresponding to any two parameters in the real-time power, accumulated electricity, voltage, and current in the power data in the information data set is calculated.

[0052] It should be noted that the parameter correlation reflects the correlation between the changes in different parameters corresponding to each category. The larger the absolute value of the parameter correlation is, the more correlation there is in the data changes between the various parameters corresponding to the category, and there are responsive changes in the data between different parameters.

[0053] At this point, the parameter correlation degree of each category in the information data set is obtained.

[0054] Step 3: Determine the relative difference between any two users by comparing the data difference of the same parameter in the information data set between the two users with the parameter correlation; and cluster all users based on the relative difference.

[0055] Furthermore, the data differences between different users are analyzed through the parameter correlation, and the relative differences are calculated, specifically:

[0056] Calculate the difference in the parameter correlation between any two users for all categories in the information dataset, and record it as correlation difference;

[0057] In this embodiment, the Euclidean distance of the parameter correlation of all categories in the information data set between any two users is calculated and recorded as the correlation difference. The calculation of the Euclidean distance is a well-known technology and will not be repeated here. As other implementation methods, the implementer can adopt other methods of the existing technology, such as DTW distance, Manhattan distance, etc., and this embodiment does not impose special restrictions on this. For example, the Euclidean distance between the parameter correlation corresponding to the power data, water resources data, and gas data between any two users is calculated.

[0058] The calculation formula for the relative difference between any two users is:

[0059]

[0060] in, For the User and The relative difference between users, For the User and The difference in said associations between users; For the The information data set of users The time parameter sequence corresponding to the parameters, For the The information data set of users The time parameter sequence corresponding to the parameters, To calculate the distance, in this embodiment, By calculation and The DTW distance between them, wherein the calculation of the DTW distance is a well-known technology and will not be repeated here. is the number of all parameters corresponding to all categories in the information dataset.

[0061] It should be noted that the obtained relative difference reflects the data difference characteristics between different users by combining the differences between electricity data, water resources data, and gas data. The larger the relative difference, the greater the difference in data characteristics between the two users.

[0062] Furthermore, based on the relative difference, different users are divided into:

[0063] Based on the relative difference, clustering all users to obtain multiple clusters;

[0064] In this embodiment, the relative difference is used as the distance between different users, and the DBSCAN clustering algorithm is used to cluster all users. The DBSCAN clustering algorithm is a well-known technology and will not be described here. As other implementation methods, the implementer can adopt other methods of the existing technology, such as hierarchical clustering algorithm, etc. This embodiment does not impose any special restrictions on this.

[0065] At this point, multiple clusters are obtained.

[0066] Step 4: Based on the data of all users at different times under various parameters in each cluster, a parameter matrix corresponding to various parameters in each cluster is constructed; the abnormality of each column in each row of the parameter matrix is ​​determined by the abnormality of the elements in each column and the abnormality of the data of the user corresponding to each row; the data in the parameter matrix corresponding to various parameters in all clusters is cleaned to obtain the cleaned information data set of different users.

[0067] Furthermore, the data changes of the same parameter of all users in each cluster are compared to analyze the abnormalities, specifically:

[0068] The time parameter sequences corresponding to the same parameters of all users in each cluster are combined into a parameter matrix, where each row element corresponds to one user;

[0069] In this embodiment, assuming that the current parameter in the power data is taken as an example, the time parameter sequence corresponding to the current of all users in each cluster is composed of a parameter matrix corresponding to the current parameter in each cluster; taking the cumulative gas consumption parameter in the gas data as an example, the time parameter sequence corresponding to the cumulative gas consumption parameter of all users in each cluster is composed of a parameter matrix corresponding to the cumulative gas consumption parameter in each cluster.

[0070] Perform anomaly detection on all column elements in each row of the parameter matrix to obtain anomaly scores for each column element in each row;

[0071] In this embodiment, a local outlier factor (LOF) algorithm is used for anomaly detection. The LOF anomaly detection algorithm is a well-known technology and will not be described in detail here.

[0072] Calculating the weight corresponding to each row in the parameter matrix through multi-criteria decision analysis;

[0073] In this embodiment, the multi-criteria decision analysis adopts the CRITIC weight method to calculate the weight corresponding to each row in the parameter matrix. The CRITIC weight method is a well-known technology and will not be described here. As other implementation methods, the implementer can adopt other methods of the existing technology, such as the entropy weight method, the TOPSIS method, etc. This embodiment does not impose any special restrictions on this.

[0074] It should be noted that, the larger the weight is, the more significant the data difference characteristics of the same parameter of different users in the corresponding cluster are.

[0075] Furthermore, based on the anomaly score and the weight, the anomaly degree corresponding to each column in the parameter matrix is ​​calculated, specifically:

[0076] The calculation formula for the abnormality corresponding to each column in the parameter matrix of various parameters in each cluster is:

[0077]

[0078] in, is the parameter matrix The abnormality corresponding to the column, is the parameter matrix Rank Anomaly score of column elements, is the parameter matrix The weight corresponding to the row, is the number of all rows in the parameter matrix.

[0079] It should be noted that the greater the abnormality, the greater the The greater the possibility that the data of all users corresponding to the column are abnormal, the abnormality degree is calculated by analyzing the data changes of different users under the same parameters to avoid deviations in the abnormal identification of data changes of a single user, which may lead to reduced accuracy of data cleaning.

[0080] Furthermore, based on the abnormality degree, the data in the parameter matrix corresponding to various parameters are subjected to abnormal elimination, specifically:

[0081] A column of data corresponding to the abnormality degree greater than a preset threshold in the parameter matrix of various parameters in each cluster is regarded as abnormal data, all abnormal data in the parameter matrix are removed, and the removed data are filled to obtain a cleaned information data set for each user;

[0082] In this embodiment, the preset threshold value is 1. As other implementation methods, the implementer can set it according to actual conditions. Secondly, the eliminated data is filled with the mean filling method, wherein the mean filling method is a well-known technology and will not be described here. As other implementation methods, the implementer can adopt other methods of the existing technology, such as the majority filling method, the KNN algorithm filling method, etc. This embodiment does not impose any special restrictions on this.

[0083] The cleaned information data set is transferred to the data center platform for storage;

[0084] The data center platform uses the PowerBI tool to perform visual analysis on information data sets to conduct data mining.

[0085] It should be noted that the PowerBI tool is a well-known technology and will not be described in detail here. As other implementation methods, implementers can adopt other visualization tools of the existing technology, such as Tableau tools, etc. This embodiment does not impose any special restrictions on this.

[0086] An embodiment of the present application also provides a data cleaning system, including a memory, a processor, and a computer program stored in the memory and running on the processor, wherein when the processor executes the computer program, the steps of any one of the above-mentioned data cleaning methods are implemented.

[0087] Based on the same inventive concept as the above method, an embodiment of the present application further provides a data center platform, which performs visual analysis on the cleaned information data set.

[0088] It should be understood that although Figure 1 The steps in the flowchart are shown in sequence as indicated by the arrows, but these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified in this document, there is no strict order restriction for the execution of these steps, and these steps can be executed in other orders. In addition, Figure 1At least part of the steps may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be executed in turn or alternately with other steps or at least part of the sub-steps or stages of other steps.

[0089] The technical features of the above embodiments can be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0090] The above-described embodiments merely represent several implementation methods of the present application. While the descriptions are relatively specific and detailed, they should not be construed as limiting the present application. It should be noted that a person skilled in the art can make various modifications and improvements without departing from the spirit of the present application. Therefore, any simple modifications, equivalent variations, and modifications to the above embodiments made in accordance with the technical essence of the present application without departing from the content of the present application's technical solution fall within the scope of protection of the present application's technical solution.

Claims

1. A data cleaning method, characterized in that: The method comprises the following steps: Acquire information data sets of different users and preprocess the information data sets, wherein the information data sets contain data of various parameters corresponding to each category at different times; and form a time parameter sequence from the data of various parameters in the information data sets at all times; Analyzing the correlation between local fluctuations of different parameters corresponding to each category in the information dataset, and calculating the parameter correlation degree of each category in the information dataset; Determine the relative difference between any two users by comparing the data difference of the same parameter in the information data set between the two users and the parameter correlation; and cluster all users based on the relative difference. Based on the data of all users at different times under various parameters in each cluster, a parameter matrix corresponding to various parameters in each cluster is constructed; the abnormality degree of each column in each row of the parameter matrix is ​​determined by the abnormality of the elements in each column and the abnormality of the data of the user corresponding to each row, and the data in the parameter matrix corresponding to various parameters in all clusters is cleaned to obtain the cleaned information data set of different users; The determining the relative difference between the arbitrary two users includes: Analyzing the difference in the parameter correlation between any two users in all categories in the information data set to obtain the correlation difference between the any two users; No. User and The relative difference between users The calculation formula is: ,in, For the User and The difference in said associations between users; For the The information data set of users The time parameter sequence corresponding to the parameters, For the The information data set of users The time parameter sequence corresponding to the parameters, To calculate the distance, is the number of all parameters corresponding to all categories in the information dataset; A further method for measuring the degree of abnormality is: performing abnormality detection on all column elements in each row of the parameter matrix to obtain an abnormality score for each column element in each row; Determining the abnormality of each column in the parameter matrix includes: Calculate the weight corresponding to each row in the parameter matrix through multi-criteria decision analysis; the larger the weight, the more significant the data difference characteristics of the same parameter of different users in the corresponding cluster; The calculation formula for the abnormality of each column in the parameter matrix is: ,in, is the parameter matrix The abnormality of the column, is the parameter matrix Rank Anomaly score of column elements, is the parameter matrix The weight corresponding to the row, is the number of all rows in the parameter matrix.

2. A data cleaning method according to claim 1, characterized in that: Calculating the parameter correlation degree of each category in the information data set includes: Using a moving standard deviation algorithm to obtain the moving standard deviation of each element in the time parameter sequence; Calculate the correlation degree of the moving standard deviation of all elements in the time parameter sequence between any two parameters corresponding to each category in the information data set, and take the mean of the correlation degrees of all any two parameters corresponding to each category as the parameter correlation degree of each category in the information data set.

3. A data cleaning method according to claim 1, characterized in that: The association difference is a metric distance of the parameter associations of all categories in the information dataset between any two users.

4. A data cleaning method according to claim 1, characterized in that: The construction process of the parameter matrix is: The time parameter sequences corresponding to all users under various parameters in each cluster are combined into a parameter matrix, where each row element corresponds to a user.

5. A data cleaning method according to claim 1, characterized in that: The cleaning of the data in the parameter matrix corresponding to various parameters in all clusters includes: A column of data corresponding to the abnormality degree greater than a preset threshold in the parameter matrix of various parameters in each cluster is regarded as abnormal data, all abnormal data in the parameter matrix are removed, and missing values ​​are filled in the removed data.

6. A data cleaning system, characterized in that: The system includes a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor implements the steps of a data cleaning method according to any one of claims 1 to 5 when executing the computer program.

7. A data center platform, applying a data cleaning method according to claim 1, characterized in that: The platform performs visual analysis on the cleaned information data set.

Images