CN107741990B

CN107741990B - Data cleaning integration method and system

Info

Publication number: CN107741990B
Application number: CN201711059055.XA
Authority: CN
Inventors: 高霞光; 刘军
Original assignee: Shenzhen Wesonton's Science And Technology Co ltd
Current assignee: Shenzhen Wesonton's Science And Technology Co ltd
Priority date: 2017-11-01
Filing date: 2017-11-01
Publication date: 2023-05-16
Anticipated expiration: 2037-11-01
Also published as: CN107741990A

Abstract

The invention discloses a data cleaning and integrating method and a system, wherein the method comprises the following steps: acquiring data to be cleaned; identifying and determining formula data and non-formula data of the data to be cleaned; calling a formula editor to identify the formula data and converting the formula data into a document in a non-formula format; and executing data cleaning on the document in the non-formula format and the non-formula data to obtain cleaned data, restoring the cleaned document in the non-formula format into a formula editor format, and inserting the document in the corresponding position to clean the whole data. The technical scheme provided by the invention has the advantage that the formula can be processed.

Description

Data cleaning integration method and system

Technical Field

The present invention relates to the field of data processing, and in particular, to a data cleansing integration method and system.

Background

Data cleansing—a process of re-examining and checking Data, aimed at deleting duplicate information, correcting errors that exist, and providing Data consistency.

Data cleansing is also known by name as "washing" of "dirty" and refers to the last procedure to find and correct identifiable errors in a data file, including checking for data consistency, handling invalid and missing values, etc. Because the data in the data warehouse is a collection of data that is subject to a certain topic, which is extracted from multiple business systems and contains historical data, it is avoided that none of the data is erroneous data, that some of the data conflicts with each other, and that erroneous or conflicting data is obviously unwanted, called "dirty data". We need to "wash out" dirty data according to certain rules, which is data cleansing. The task of data cleaning is to filter out data which does not meet the requirements, and the filtered result is delivered to the business administration department to confirm whether the data is filtered out or is corrected by the business unit and then extracted. The data which does not meet the requirements mainly comprises incomplete data, erroneous data and repeated data. Data cleansing is different from questionnaire auditing. The existing data cleaning cannot integrate and adjust the formulas.

Disclosure of Invention

The application provides a data cleaning integration method. The technical scheme solves the defect that the technical scheme in the prior art cannot clean the formula.

In one aspect, a data cleansing integration method is provided, the method comprising the steps of:

acquiring data to be cleaned; identifying and determining formula data and non-formula data of the data to be cleaned;

calling a formula editor to identify the formula data and converting the formula data into a document in a non-formula format;

and executing data cleaning on the document in the non-formula format and the non-formula data to obtain cleaned data, restoring the cleaned document in the non-formula format into a formula editor format, and inserting the document in the corresponding position to clean the whole data.

Optionally, the calling the formula editor identifies the formula data and converts the formula data into a document in a non-formula format, specifically including:

and extracting the data except the symbols in the formula data and the sequence of the symbols, and converting the data except the symbols into non-formula data.

Optionally, the identifying the data to be cleaned to determine the formula data and the non-formula data includes:

the data is identified to determine the format of the data, e.g., the format is a non-document format, as formula data.

Optionally, the method further comprises:

the data cleaning includes: processing of invalid and missing values or consistency checks.

In a second aspect, there is provided a data cleansing integration system, the system comprising:

the acquisition unit is used for acquiring data to be cleaned;

the processing unit is used for identifying the data to be cleaned and determining formula data and non-formula data; calling a formula editor to identify the formula data and converting the formula data into a document in a non-formula format; and executing data cleaning on the document in the non-formula format and the non-formula data to obtain cleaned data, restoring the cleaned document in the non-formula format into a formula editor format, and inserting the document in the corresponding position to clean the whole data.

Optionally, the processing unit further converts the data other than the symbol into non-formula data by extracting the data other than the symbol in the formula data and the sequence of the symbols.

Optionally, the processing unit is specifically configured to

Optionally, the data cleaning includes: processing of invalid and missing values or consistency checks.

In a third aspect, there is provided a computer program product comprising a non-transitory computer readable storage medium storing a computer program operable to cause a computer to perform the method of the first aspect.

A computer-readable storage medium storing a computer program for electronic data exchange, wherein the computer program causes a computer to perform the method of the first aspect.

The technical scheme provided by the invention converts the formula into non-formula data, and then converts the formula into formula data after cleaning, thereby realizing the cleaning of the formula data.

Drawings

In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for the description of the embodiments will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a flowchart of a data cleansing integration method according to a first preferred embodiment of the present invention;

fig. 2 is a block diagram of a data cleansing integration system according to a second preferred embodiment of the present invention.

Detailed Description

The following description of the embodiments of the present invention will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.

Referring to fig. 1, fig. 1 is a data cleaning integration method according to a first preferred embodiment of the present invention, and the method is shown in fig. 1, and includes the following steps:

step S101, acquiring data to be cleaned;

step S102, identifying and determining formula data and non-formula data of the data to be cleaned;

the implementation method of the above steps may specifically be that the data is identified to determine the format of the data, for example, the format is a non-document format, and is determined to be formula data. .

Step S103, calling a formula editor to identify the formula data and converting the formula data into a document in a non-formula format.

The method of implementing the above steps may be to extract data other than symbols in the formula data and the order of the symbols, and convert the data other than symbols into non-formula data.

Step S104, data cleaning is carried out on the document in the non-formula format and the non-formula data to obtain cleaned data, and the cleaned document in the non-formula format is restored to a formula editor format and then inserted into a corresponding position to clean the whole data.

The method for cleaning the data can be specifically as follows:

consistency check

The consistency check (consistency check) is to check whether the data is satisfactory or not according to the reasonable value range and the interrelationship of each variable, and find out the data which is out of the normal range, logically unreasonable or contradictory. For example, a variable measured on a scale of 1-7 has a value of 0 and a negative weight should be considered to be outside the normal range. Computer software such as SPSS, SAS, excel and the like can automatically identify each out-of-range variable value according to a defined value range. Answers with logical inconsistencies may appear in several forms: for example, many panelists say themselves drive to work and report that there is no car; or the panelist reports itself as a heavy purchaser and user of a brand, but at the same time gives a very low score on the familiarity scale. When inconsistent is found, the questionnaire serial number, the record serial number, the variable name, the error category and the like are listed, so that further verification and correction are facilitated.

Processing of invalid and missing values

Due to investigation, coding and logging errors, there may be some invalid and missing values in the data, which need to be given appropriate processing. The usual treatment methods are: estimating, whole case deletion, variable deletion and paired deletion.

Estimation (estimate). The simplest approach is to replace the invalid and missing values with the sample mean, median or mode of a certain variable. This approach is simple, but does not take into account the information already in the data adequately, and the error may be large. Another approach is to estimate the answers to other questions by correlation analysis or logical inference between variables based on the panelist. For example, the possession of a product may be related to household income, and the likelihood of possession of the product may be inferred from the household income of the panelist.

The whole deletion (case wise deletion) is to discard samples containing missing values. Since many questionnaires may have missing values, the result of this may be a significant reduction in the effective sample size, failing to make full use of the data already collected. Therefore, it is only suitable for the case that the critical variable is missing, or that the sample containing the invalid value or missing value has a small specific gravity.

Variable delete (variable deletion). If the invalid and missing values of a variable are numerous and the variable is not particularly important to the problem under study, then the variable may be considered for deletion. This reduces the number of variables for analysis, but does not change the sample size.

Paired delete (pair wise deletion) is to represent invalid and missing values with a special code (typically 9, 99, 999, etc.) while retaining all variables and samples in the dataset. However, in a specific calculation, only samples with complete answers are used, so that the effective sample size can be different according to the different analysis related variables. This is a conservative approach, which maximally preserves the available information in the dataset.

The use of different treatments may have an impact on the analysis results, especially when the missing values are not random in appearance and there is a clear correlation between the variables. Therefore, invalid values and missing values should be avoided as much as possible in the investigation, and the integrity of the data is ensured.

Incomplete data

This type of data is mainly some information missing that should exist, such as the name of the provider, the name of the branch company, the regional information missing of the customer, the fact that the main table and the detail table in the business system cannot be matched, etc. And filtering out the data, writing the data into different Excel files according to the missing content, and submitting the data to clients, wherein the data are required to be completed in a specified time. The data warehouse is written after completion.

Error data

The reasons for this type of error are that the business system is not sound enough, and the business system is not judged to directly write into the background database after receiving the input, for example, numerical data is input into full-angle numerical characters, character string data is followed by a carriage return operation, the date format is incorrect, the date is out of range, and the like. The data is classified, and for the problems similar to full-angle characters and invisible characters before and after the data, the problems can only be found out by writing SQL sentences, and then the clients are required to extract after the correction of a service system. The ETL operation failure can be caused by incorrect date format or date out-of-limit type errors, the type of errors need to be picked up by a service system database in an SQL mode, and the errors are submitted to a service administration department to require limit correction, and the errors are extracted after correction.

Repeating data

For this type of data, particularly as may occur in dimension tables, all fields of the duplicate data record are derived for validation and sorting by the customer.

The data cleaning is an iterative process, which cannot be completed within a few days, and only the problem is continuously found, so that the problem is solved. Whether filtering or not, whether correcting generally requires customer confirmation, and for filtered data, writing Excel files or writing the filtered data into a data table, sending mails of the filtered data to a business unit every day in the early stage of ETL development, so that the business unit can correct errors as soon as possible, and meanwhile, the business unit can be used as a basis for verifying the data in the future. Data cleansing requires care that the useful data is not filtered out, verification is carefully performed for each filtering rule, and user confirmation is required.

Method for solving incomplete data (namely value missing)

In most cases, the missing values must be filled in manually (i.e., manually cleaned). Of course, some missing values may be derived from the present data source or other data sources, which may replace the missing values with average, maximum, minimum, or more complex probability estimates, thereby achieving clean-up.

Error value detection and solution method

Statistical analysis methods to identify possible erroneous or outliers, such as bias analysis, identifying values that do not follow the distribution or regression equations, simple rule bases (common sense rules, business specific rules, etc.) may also be used to examine the data values, or constraints between different attributes, external data may be used to detect and clean up the data.

Method for detecting and eliminating repeated record

Records in the database with identical attribute values are considered as duplicate records, and whether the records are equal is detected by judging whether the attribute values between the records are equal, and the equal records are combined into one record (i.e. combined/cleared). Merging/clearing is a basic method for eliminating duplicate.

Method for detecting and solving inconsistency (inside data source and between data sources)

The data integrated from multiple data sources may have semantic conflicts, integrity constraints may be defined for detecting inconsistencies, and connections may be found by analyzing the data so that the data remains consistent. Data cleaning tools currently developed can be broadly divided into three categories.

The data cleansing tool uses domain-specific knowledge (e.g., postal addresses) to cleanse the data. They typically employ parsing and fuzzy matching techniques to clean up multi-data source data. Some tools may indicate the "relative cleanliness" of the source. Tools Integrity and Trillum belong to this class.

Referring to fig. 2, fig. 2 is a data cleansing integration system according to a second preferred embodiment of the present invention, the system includes:

an acquisition unit 201 for acquiring data to be cleaned;

a processing unit 202, configured to identify the data to be cleaned, and determine formula data and non-formula data; calling a formula editor to identify the formula data and converting the formula data into a document in a non-formula format; and executing data cleaning on the document in the non-formula format and the non-formula data to obtain cleaned data, restoring the cleaned document in the non-formula format into a formula editor format, and inserting the document in the corresponding position to clean the whole data.

Optionally, the processing unit is specifically configured to

It should be noted that, for simplicity of description, the foregoing method embodiments are all expressed as a series of action combinations, but it should be understood by those skilled in the art that the present invention is not limited by the order of action described, as some steps may be performed in other order or simultaneously according to the present invention. Further, those skilled in the art will also appreciate that the embodiments described in the specification are all preferred embodiments, and that the acts and modules referred to are not necessarily required for the present invention.

In the foregoing embodiments, the descriptions of the embodiments are focused on, and for those portions of one embodiment that are not described in detail, reference may be made to the related descriptions of other embodiments.

Those of ordinary skill in the art will appreciate that all or part of the steps in the various methods of the above embodiments may be implemented by a program to instruct related hardware, the program may be stored in a computer readable storage medium, and the storage medium may include: flash disk, read-Only Memory (ROM), random access Memory (Random Access Memory, RAM), magnetic disk or optical disk.

The foregoing has described in detail the method for downloading content and the related devices and systems provided by the embodiments of the present invention, and specific examples have been applied to illustrate the principles and embodiments of the present invention, where the foregoing examples are only for aiding in understanding the method and core ideas of the present invention; meanwhile, as those skilled in the art will have variations in the specific embodiments and application scope in accordance with the ideas of the present invention, the present description should not be construed as limiting the present invention in view of the above.

Claims

1. A data cleansing integration method, the method comprising the steps of:

2. The method of claim 1, wherein the calling the formula editor recognizes the formula data and converts the formula data to a document in a non-formula format, comprising:

3. The method of claim 1, wherein identifying the data to be cleaned to determine formula data and non-formula data comprises:

4. The method according to claim 1, wherein the method further comprises:

the data cleansing includes: processing of invalid and missing values or consistency checks.

5. A data cleansing integration system, the system comprising:

the acquisition unit is used for acquiring data to be cleaned;

6. The system of claim 5, wherein the processing unit further converts the data other than the symbols into non-formula data by extracting the data other than the symbols in the formula data and the order of the symbols.

7. The system according to claim 5, wherein the processing unit is in particular configured to

8. The system of claim 5, wherein the system further comprises a controller configured to control the controller,

9. A computer-readable storage medium, characterized in that it stores a computer program for electronic data exchange, wherein the computer program causes a computer to perform the method according to any one of claims 1-4.