JP5797583B2 - Data cleansing system and program - Google Patents

Description

  The present invention relates to an information processing technique for data analysis. In particular, the present invention relates to a technique of data cleaning (data cleansing or the like) in data analysis.

  In data analysis, correct analysis is based on correct data, and therefore, data cleaning (data cleansing) operation / process, which is an operation / process for converting data from original data into a data set that can be analyzed, is important.

  Conventionally, in a data cleaning operation / processing, if an item (field, column) in a certain data set (row) is illegal, the data set (row) is discarded and not used for analysis. .

  As a prior art example, there is JP 2008-537266 A (Patent Document 1). Patent Document 1 (“adaptive data cleaning”) describes that optimization and standardization of a data cleansing process is realized.

JP 2008-537266 A

  Conventionally, there has been no mechanism (system) for storing a history in data cleaning operation / processing for making the original data related to data analysis into a data set that can be analyzed.

  Due to the lack of a mechanism for storing the history, as described above, in the conventional data cleaning operation / processing, even one item (field, column) is illegal in a certain data set (row). If so, the dataset (row) was discarded (deleted) and not used (utilized) for analysis. That is, items other than some of the invalid items (fields, columns) in the data set (row) are discarded even if there are no frauds, and the data after the discard data set is removed. It was used for data analysis with a reduced amount of information. Of the data information that was discarded due to fraud, data information that was not fraudulent could not be used for data analysis.

  Note that Patent Document 1 is a method for improving a data cleansing process, and is not a method for improving data availability.

  In view of the above, the main object of the present invention is to provide a mechanism for storing and referring to the history of data cleaning operations / processing, etc., for data cleaning (data cleansing) related to data analysis. It is to provide a technique that can use (utilize) data items that have been discarded in the past without any fraud.

  A typical embodiment of the present invention is a data cleansing system that performs processing including data cleansing processing for data analysis, which is realized by using information processing of a computer, and has the following configuration. Features.

  In the system of this embodiment, for data analysis, a first process for performing a data cleansing (data cleaning) operation / process for converting the original data (first data) into analyzable data (second data). A part (including a process of deleting (removing) a data set (row) including an item (field, column) having a potential fraud from the original data), and a data cleansing operation / process of the first processing part And a second processing unit that performs processing for storing the history in a table and making it referable.

  The system of the present embodiment refers to the history data information stored by the second processing unit, and in the data set (row) deleted from the original data, there are items that are illegal, A third processing unit that performs a process for determining (guaranteeing) that there is no fraud in the other items, and a process for extracting an item that has been confirmed (guaranteed) that there is no fraud And a fourth processing unit to be performed. Items (data) that have been confirmed to have no fraud can be used for data analysis (particularly, calculation for each item).

  According to a typical embodiment of the present invention, regarding a technique such as data cleaning related to data analysis (data cleansing), a mechanism for storing and referring to a history of data cleaning operation / processing, etc. is provided. Even data items that have been discarded in the past can be used (utilized) for data analysis.

  By making data items that were conventionally overlooked as information available for analysis, analysis accuracy can be improved.

1 is a diagram showing an overall configuration of a system according to an embodiment of the present invention (a data cleansing system including a data cleansing server including a processing history storage system). 2 is a flowchart of the entire process of the system according to the present embodiment. The flowchart of the detailed process of a cleansing process (S202) is shown. The flowchart of the detailed process of a missing value confirmation process (S301) is shown. The flowchart of the detailed process of illegal character processing (S302) is shown. The flowchart of the detailed process of an abnormal value process (S303) is shown. The flowchart of the detailed process of a unit alignment process (S304) of a value is shown. A flow diagram of detailed processing of value format conversion (unification of all half-width characters) processing (S305) is shown. The flowchart of the detailed process of the value check process (S306) is shown. The flowchart of the detailed process of the value normalization (zip code) process (S307) is shown. The flowchart of the detailed process of the value normalization (address) process (S308) is shown. The flowchart of the detailed process of the value normalization (name) process (S309) is shown. A flow chart of detailed processing of value normalization (telephone number) processing (S310) is shown. The flowchart of the detailed process of the data comprehensive determination process for analysis (S203) is shown. The flowchart of the detailed process of the data load process for analysis (S204) is shown. It is a figure which shows the system configuration example in the case of performing the analysis data table process (analysis process using an analysis data table) (S205). It is a flowchart which shows the detailed process example (example of an analytical data extraction method / analysis process) of cleansing history utilization analysis execution processing (S206). It is a figure which shows the system configuration example in the case of performing a cleansing log | history utilization analysis analysis process (S206). It is a figure which shows the outline | summary (function) structure of the system (data cleansing system) of this Embodiment. It is a figure which shows the example of a definition of original data (original data table). It is a figure which shows the example of the value of original data (original data table). It is a figure which shows the example of a (a) definition of a cleansing master table, (b) the value of a cleansing master table. (A) Definition of analysis data field management table, (b) It is a figure which shows the example of the value of an analysis data field management table. It is a figure which shows the definition of a cleansing policy master table. It is a figure which shows the example of the value of a cleansing policy master table. It is a figure which shows the definition of a cleansing result table. It is a figure which shows the example of the value of a cleansing result table. It is a figure which shows the example of a definition of (a) cleansing result detailed table, and (b) cleansing result detailed table. It is a figure which shows the definition of the data table for analysis. It is a figure which shows the example of the value of the data table for analysis. It is a figure which shows the definition of a contrast table. It is a figure which shows the example of the value of a contrast table.

  Hereinafter, embodiments of the present invention will be described in detail with reference to the drawings. Note that components having the same function are denoted by the same reference symbols throughout the drawings for describing the embodiment, and the repetitive description thereof will be omitted. “Cleansing” and “cleaning” have the same meaning.

[Summary]
In the present embodiment, a processing history storage system or the like in data cleansing processing for making analysis data highly available is provided. In FIG. 1, a data cleansing server 103 or the like (a data cleansing system including the data cleansing server 103) including the processing history storage system 116 is provided. Correspondingly, FIG. 19 shows an outline (function) configuration of the present system (data cleansing system 1 including a history management function).

  Previously, there was no mechanism (means) for saving and referring to the history of data cleansing operations / processing, so items (fields, columns) other than the invalid items (fields, columns) in the target data set (rows) It was not possible to guarantee (confirm) that there was no fraud in the above history. Moreover, based on the guarantee (confirmation), items other than the item with the fraud could not be used for data analysis.

  On the other hand, this embodiment has a mechanism (means) for managing (saving and referring to) a history of data cleansing operation / processing (cleansing history). Also, means for ensuring (confirming) that there are no frauds in the data items (fields, columns) of the target data (data set), and data items (fields, columns) that are guaranteed (confirmed) that there are no frauds. Means for extracting (data conventionally discarded) as a data item usable for analysis. In FIG. 19, the cleansing history management unit 12, the fraud presence / absence / usability determination unit 13, and the analysis data extraction unit 14 correspond to means (functions).

  In the present embodiment, by referring to the history, items (data) that can be used for analysis and items (data) that cannot be used for analysis among the analysis target data (pre-cleansing data 21 and post-cleansing data 22). This is a method for identifying (classifying) and extracting items (data) that can be used for analysis and making them available for analysis. In other words, usable data information that has been discarded in the past (some fields (columns) in the data set (row) are invalid and other fields (columns) that are discarded together (not illegal) are discarded). )) Can be used for analysis.

  In FIG. 19, the data cleansing unit 11 performs data cleansing processing (including processing for deleting a data set (row) with fraud) on the pre-cleansing data 21 based on the setting (10, 20) and the post-cleansing data 22. To do. The cleansing history management unit 12 stores and makes it possible to refer to data cleansing process / operation history information by the data cleansing unit 11 (cleansing history 23). The fraud presence / absence / usability judging unit 13 refers to the target data (21, 22) and the cleansing history 23 and confirms whether or not each item (field, column) in the data set (row) deleted by the cleansing process is fraudulent. (Guaranteed) and determine whether or not it can be used (determination data 24) Based on the determination (24) of the determination unit (13), the analysis data extraction unit 14 is free of data items (deleted data sets (rows)) that can be used from the target data (21, 22). Are identified (guaranteed), and are used as analysis data 25. The user can perform data analysis using the analysis data 25.

[System configuration]
FIG. 1 shows an overall configuration of a system according to the present embodiment (a data cleansing system including a data cleansing server 103 including a processing history storage system 116). The entire system has a configuration in which a terminal device (analyzer operation terminal) 101 operated by a data analyst (U) and a data cleansing server 103 are connected via a network 102. A data analyst (U) performs data analysis using the terminal device 101. A data analyst (U) accesses the data cleansing server 103 from the terminal device 101 via the network 102 and accesses data and the like necessary for analysis. The data cleansing server 103 includes a server device and software program processing thereof, and includes a processing history storage system 116 (a corresponding program of the present embodiment, etc.) unique to the present embodiment. Note that 103 may be regarded as a data cleansing system. The processing history storage system 116 is a system for storing a history of data cleansing processing, and is realized by a computer device, software program processing, or the like.

  The processing history storage system 116 includes an analysis data creation policy setting processing unit 104, a cleansing processing unit 105, an analysis data comprehensive determination processing unit 106, and an analysis unit as processing units (corresponding functions, devices / programs, etc.) of components. This configuration includes a data load processing unit 107, an analysis data table processing unit 108, a cleansing history utilization analysis execution processing unit 109, and the like. In 116, these processing units include, as data information (corresponding storage units), an original data table 110, a cleansing master table 111, a cleansing policy master table 112, a cleansing result table 113, a cleansing result detail table 114, and analysis data. Processing is performed by operating the table 115 or the like.

  Each unit (function) constituting the above-described 116 may be configured by an individual device or a program module and linked together, or may be configured by appropriately separating and integrating functions. Each of the data information may be configured by a device, a table, a DB, or the like (storage unit) that stores and manages the data information, or may be managed by appropriately separating and integrating the data information.

  The analysis data creation policy setting processing unit 104 performs processing for setting (by U) a policy for data cleansing processing.

  The cleansing processing unit 105 cleanses the original data according to the analysis policy.

  The analysis data comprehensive determination processing unit 106 performs a process of determining whether or not each data string can be used from the cleansing process history.

  The analysis data load processing unit 107 performs a process of extracting only a data string that can be used for analysis.

  The analysis data table processing unit 108 performs analysis processing using only a data string that can be used for analysis.

  The cleansing history utilization analysis execution processing unit 109 refers to the history of the cleansing process and performs an analysis process that extracts and utilizes fields that can be used for analysis from a data string that cannot be used for analysis.

  The original data table 110 is original data to be analyzed before the cleansing process.

  The cleansing master table 111 defines a process list for cleansing processing.

  In the cleansing policy master table 112, a policy (cleansing policy, analysis policy) in the cleansing process is set.

  The cleansing result table 113 is a table in which cleansing result data is stored, has the same table schema as the original data (110), and is added to the cleansed field.

  The cleansing result detail table 114 stores all the cleansing process history for the fields numbered in the cleansing result table 113.

  The analysis data table 115 stores only data strings (data items) that can be used for analysis from the history of cleansing processing.

[System processing]
FIG. 2 shows an overall processing flow of the system of FIG. 1 (S201 and the like represent processing steps). Details of each processing step will be described later.

  (S201) In S201, the system first performs analysis data creation policy setting in step S104. A policy is set in the cleansing policy master table 112.

  (S202) After that, in S202, cleansing processing by 105 is performed according to the policy of S201 (→ FIG. 3).

  (S203) After the cleansing process of S202, an analysis data comprehensive determination process 106 is performed in S203 (→ FIG. 14).

  (S204) According to the determination in S203, the analysis data load process in 107 is performed in S204 (→ FIG. 15).

  (S205) When analysis is performed using the analysis data table after S204, analysis data table processing, which is processing of the analysis data loaded in S204, is performed in S205 (see FIG. 16). ).

  (S206) Alternatively, when the analysis is performed using the cleansing history, the cleansing history utilization analysis process utilizing the “original data” and the “cleansing history” is performed in 109 in S206 (→ FIGS. 17 and 18). . This realizes highly available data utilization.

[S202]
FIG. 3 shows a flow of an example of detailed processing contents of the cleansing process (S202) of FIG. This cleansing process (S202) is processed by 105 in the order of the following processing steps. Each of these processes is shown in the cleansing master table 111 shown in FIGS. 22 (a) and 22 (b). Details of each processing step will be described later.

(S301) Missing value confirmation processing (→ FIG. 4)
(S302) Illegal character string processing (→ FIG. 5)
(S303) Abnormal value processing (→ Fig. 6)
(S304) Value alignment processing (→ Fig. 7)
(S305) Value format conversion (unification of all half-width characters) (→ Figure 8)
(S306) Value check processing (→ FIG. 9)
(S307) Value normalization (zip code) processing (→ Fig. 10)
(S308) Value normalization (address) processing (→ FIG. 11)
(S309) Value normalization processing (→ FIG. 12)
(S310) Value normalization (telephone number) processing (→ FIG. 13).

  FIG. 22A shows the definition (table schema) of the cleansing master table 111 (defining cleansing processing). (B) shows a value (example) of the table (111). CID is a cleansing master ID. CName is a cleansing process name. The table (111) is an example, and there are various rows in the actual data cleansing process. The cleansing process (S301 and the like) shown below is based on the definitions and values of the table (111) in FIGS. 22 (a) and 22 (b).

[S201]
In the analysis data creation policy setting process (S201) in FIG. 2, the cleansing policy is set (input) in the cleansing policy master table 112 by 104.

  FIG. 24 shows the definition (table schema) of the cleansing policy master table 112 (table for defining the cleansing policy). FIG. 25 shows values (examples) of the table (112). In the tables (112) of FIGS. 24 and 25, all processing performed on each field (FID) is managed and stored. 24 and 25 have the “analysis data field management table” defined in FIG. 23A and the values shown in FIG. 23B. CPID is the cleansing policy master ID, CID is the above (FIG. 22 (a)), FID is the following (FIG. 23 (a)), PDID is the cleansing processing method details ID, CPPro is the cleansing processing method details, CPFLAG is the cleansing processing method decision A flag CPCRITICAL is a cleansing process result availability flag.

  FIG. 23A shows a definition (table schema) of an analysis data field management table (a table for managing a field of “original data”). FIG. 23B shows an example of values in the table (illustrated in accordance with FIG. 20). FID is an analysis table field ID. FName is an analysis table field name.

[S301]
FIG. 4 shows the detailed processing contents of the missing value confirmation processing (S301) in the cleansing processing (S202). This process (S301) is based on CID = 1 in FIG. 22B in the definition of FIG. Although the description of the processing subject is omitted, it is 105 or the like.

  (S401) First, the original data table (110) is read, and the target column is read.

  FIG. 20 shows the definition (table schema) of the original data (110), and FIG. 21 shows an example of the value of the table (110). FIG. 20 is an example, and actually includes different items (fields and columns). In the example of the values in FIG. 21, five data sets (5 rows) are shown, but the actual data analysis covers many data sets (rows). BID is the original table ID.

  (S402) In the cleansing policy master table 112 (FIGS. 24 and 25), the “FID” line that matches “missing value processing” is read, and the cleansing policy is read.

  (S403) The target field (column) in the read original data table 110 (row) is read again.

  (S404) It is determined whether the field value is “missing value”. That is, it is determined whether the field that should originally have a value has a value. If the field that should originally have a value has a value (Y), the process ends. If there is no value in the field that should originally have a value (N), processing is performed according to the field processing policy.

  (S405) If the field processing policy is “Replace with average value” (Y), (S406) The average value of the column to which the field belongs is calculated and inserted (replaced) in the field. If the field processing policy is “leave blank” (N) (S408), leave the field blank.

  In the case of other processing policies (S407-N), (S409) an alert for manual input is displayed to the user (U), and (S410) manual input is made by the user (U).

  As described above, after the missing value processing is completed, the cleansing processing result is stored. (S411) When the corresponding field of the cleansing result table 113 is blank (Y), (S412) a serial number is assigned to the cleansing result table 113. When the cleansing result table 113 already has a value (S411-N), the value is used as it is.

  FIG. 26 shows an example of the definition (table schema) of the cleansing result table 113 (table for leaving the cleansing result). FIG. 27 shows an example of values in the table (113). This schema is obtained by adding a “final decision” field (indicating whether or not to use for analysis) to the original data (110).

  (S413) Then, the cleansing result is inserted (described) in the cleansing result detail table 114.

  FIG. 28A shows the definition (table schema) of the cleansing result detail table 114 (the table that keeps the details of the cleansing result history). FIG. 28B shows an example of values in the table (114). In this table (114), all cleansing processes performed on the HIDs (numbers assigned with cleansing results) numbered in the cleansing result table 113 of FIGS. 26 and 27 are managed and stored. The DID is a cleansing result detail table ID. BID is the number given above (FIG. 20), and HID is the number assigned to the cleansing result. CPID is as described above (FIG. 24) (records the cleansing method), and CPCRITICAL is as described above (FIG. 24).

[S302]
Next, FIG. 5 shows the illegal character string processing (S302). This process (S302) is based on the definition of FIG. 22A and the example of CID = 2 in FIG.

  (S501) First, “illegal character strings” (list) are defined. A typical illegal character string is “!“ # $% &’+; * :? \ | ".

  (S502) Next, in the cleansing policy master table 112 (FIGS. 24 and 25), the line “FID” that matches “illegal character string processing” is read, and the cleansing policy is read.

  (S503) The target field of the original data table 110 (FIGS. 20 and 21) is read.

  (S504) It is determined whether or not the field value does not match the “illegal character string”. That is, character comparison is performed to determine whether or not the defined illegal character string exists in the corresponding field. As a result of the character comparison, if there is no character that matches the illegal character string in the corresponding field (Y), the process is terminated. If there is a character that matches the illegal character string in the corresponding field (N), processing is performed according to the field processing policy.

  (S505) If the field processing policy is “remove only illegal characters” (Y), (S506) remove only illegal characters in the field. When the field processing policy is “blank” (Y) (S508), the field is blanked. In the case of other processing policies (S507-N), (S509) an alert to the effect of manual input is displayed, and (S510) manual input is performed.

  As described above, after the illegal character (string) processing is completed, the cleansing processing result is stored in S511 to S513 (similar to S411 to S413).

[S303]
Next, FIG. 6 shows the abnormal value processing (S303). This process (S303) is based on the definition of FIG. 22A and the example of CID = 3 in FIG. 22B.

  (S601) First, the “FID” line that matches the “abnormal value processing” is read in the cleansing policy master table 112 (FIGS. 24 and 25).

  (S602) Next, the cleansing policy is read.

  (S603) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S604) A predetermined test process is performed for the determination of S605 (determination of an abnormal value). In the present embodiment, “Smirnov-Grubbs test” is used as a method for testing this abnormal value.

  (S605) Based on the test of S604, it is determined whether the field value is “abnormal value” (outlier). If it is not an outlier (Y), the process ends. In the case of an outlier (N), the corresponding field is processed according to the processing policy.

  (S606) When the field processing policy is “replace with average value” (Y), (S607) the average value of the column is calculated and the average value is inserted into the field. If the field processing policy is “blank” (Y) (S609), the field is blanked. In the case of other processing policies (S608-N), (S610) an alert for manual input is displayed, and (S611) manual input is performed.

  As described above, after the abnormal value processing is completed, the cleansing processing result is stored in S612 to S614 (similar to S411 to S413).

[S304]
Next, FIG. 7 shows “value unit alignment processing” (S304). This process S304 is based on the definition of FIG. 22A and the example of CID = 4 in FIG.

  (S701) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value unit alignment processing” is read.

  (S702) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S703) Define “available units”. In the present embodiment (specific example), “circle” is defined as a unit.

  (S704) “Unusable unit” is defined. In the present embodiment (specific example), “10,000 yen” is defined as “unusable unit”.

  (S705) Then, a comparison table of the above “available units” and “unusable units” is created.

  FIG. 31 shows the definition of the comparison table, and FIG. 32 shows an example of the values of the comparison table. SID is a comparison table ID. CID indicates the aforementioned format (FIG. 22A), UDF indicates an available format, and UUSF indicates an unusable format. The processing example of S304 is represented by a row of CID = 4 in the comparison table. That is, the table becomes “10,000 yen” = “10,000 yen”. The comparison table (FIGS. 31 and 32) is used as a comparison table in the process of FIG. 7 (S304), the process of FIG. 8 (S305), the process of FIG. 10 (S307), and the process of FIG. 13 (S310).

  (S706) It is determined whether the field value is “available unit”. If it is “available unit” (Y), the process is terminated. If it is not “available unit” (N), (S707) if the field processing policy is “convert to“ unusable unit ”” (Y), use (S708) comparison table to “use” Convert to possible units.

  If neither the “usable unit” nor the “unusable unit” is present (S707-N), the processing is performed according to the field processing policy. (S709) If the field processing policy is “blank” (Y), (S710) the field is blank. In the case of other processing policies (S709-N), (S711) an alert for manual input is displayed, and (S712) manual input is performed.

  As described above, after the value unit alignment process is completed, the cleansing process result is stored in S713 to S715 (similar to S411 to S413).

[S305]
Next, FIG. 8 shows “value format conversion (unification of all half-widths)” (S305). This processing (S305 is based on the definition of FIG. 22A and the example of CID = 5 in FIG. 22B.

  (S801) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value format conversion (unification of all half-widths)” is read. FIG. 24 shows a cleansing policy master table, and FIG. 25 shows an example of the values.

  (S802) The target column field in the original data table 110 (FIGS. 20 and 21) is read.

  (S803) Next, a target character string is defined. In the present embodiment (specific example), a target full-width character string is defined as “12345567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghhijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrst.

  (S804) The next available full-width half-width character is defined. In this embodiment (specific example), it is defined as “12345567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghhijklmnopqrstuvwxyz”.

  (S805) Next, unusable full-width half-width characters are defined. In the present embodiment (specific example), it is defined as “1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz” as a full-width character that cannot be used.

  (S806) Then, the above-mentioned “available half-width” and “unusable full-width” comparison table is created. In the comparison table of this processing example (FIGS. 31 and 32), the row has CID = 5. That is, a table such as “1 (full-width)” = “1 (half-width)” is obtained.

  (S807) It is determined whether the value of the field includes “target full-width half-width character”. If the value of the field does not include “target full-width characters” (N), the process ends.

  (S808) It is determined whether or not the field value matches the “available full-width half-width character” format. If it matches with “available full-width half-width characters” (Y), the process is terminated.

  In the case of “unavailable double-byte characters” (S808-N), (S809) when the field processing policy is “convert to“ available double-byte characters ”using the comparison table” (Y), (S810) Convert to “available full-width half-width characters” according to the comparison table.

  (S811) If the field processing policy is “blank” (Y), (S812) the corresponding character is blanked. In the case of other processing policies (S811-N), (S812) an alert for manual input is displayed, and (S814) manual input is performed.

  As described above, after the full-width character processing (value format conversion (unification of all half-width) processing) is finished, the cleansing processing result is stored in S815 to S817 (similar to S411 to S413).

[S306]
Next, FIG. 9 shows the “value check process” (S306). This process (S306) is based on the definition of FIG. 22A and the example of CID = 6 in FIG. 22B.

  (S901) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line “FID” that matches the “value check process” is read.

  (S902) The target column field in the original data table 110 (FIGS. 20 and 21) is read. In the present embodiment (specific example), “gender = male” and “occupation = housewife” are set as “齟齬” in the original data “sex” and “occupation” (BID = 2). .

  (S903) It is determined whether there is a flaw between the fields. For example, “gender = male” and “occupation = housewife”, and there is a discrepancy between the fields. If there is no defect (Y), the process is terminated.

  (S904) If the field processing policy is "do nothing" (Y), no operation is performed (proceeds to 909).

  (S905) If the field processing policy is “blank” (Y), (S906) the field is blanked. In the case of other processing policies (S905-N), (S907) an alert to the effect of manual input is displayed, and (S908) manual input is performed.

  As described above, after the value check process is completed, the cleansing process result is stored in S909 to S911 (similar to S411 to S413).

[S307]
Next, FIG. 10 shows “value normalization (zip code) processing” (S307). This process (S307) is based on the definition of FIG. 22A and the example of CID = 7 in FIG.

  (S1001) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value normalization (zip code) processing” is read.

  (S1002) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S1003) Define available postal codes (canonical format). In the present embodiment (specific example), “1234567” is defined as an available postal code.

  (S1004) Define an unusable postal code (non-canonical format). In this embodiment (specific example), “123-4567” is defined as an unusable postal code.

  (S1005) Then, a comparison table of the above-mentioned “available postal codes” and “unusable postal codes” is created. In the comparison table of this processing example (FIGS. 31 and 32), the row with CID = 7 corresponds. That is, the table becomes “123-4567” = “1234567”.

  (S1006) It is determined whether the field value is an available postal code (regular format). If it matches the regular format (Y), the process ends.

  (S1007) It is determined whether the field value is an unusable postal code (non-regular format). If it matches the non-regular format (Y), the process is performed according to the field processing policy (the process proceeds to S1010 if they do not match).

  (S1008) If the field processing policy is “Convert to“ available postal codes ”(regular format) using the comparison table” (Y), (S1009) Use the comparison table to normalize the postal code format Convert to

  If neither the “available zip code” nor the “unusable zip code” is used, processing is performed according to the field processing policy. (S1010) If the field processing policy is “blank” (Y), (S1011) the field is blank. In the case of other processing policies (S1010-N), (S1012) an alert to the effect of manual input is displayed, and (S1013) manual input is performed.

  As described above, when the postal code processing is completed, the cleansing processing result is stored in S1014 to S1016 (similar to S411 to S413).

[S308]
Next, FIG. 11 shows “value normalization (address) processing” (S308). This process (S308) is based on the definition in FIG. 22A and the example of CID = 8 in FIG.

  (S1101) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value normalization (address) processing” is read.

  (S1102) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S1103) Define available addresses. Define a list of “available regular addresses (prefectures)” in “address (prefecture)”. In this embodiment (specific example), “Hokkaido, Aomori Prefecture,..., Okinawa Prefecture” (47 prefectures) is defined as this address (regular).

  (S1104) It is determined whether the value of the field matches the character string of the usable address (regular). If they match (Y), the process ends. If they do not match (N), that is, if the address is not available, processing is performed according to the field processing policy.

  (S1105) When the field processing policy is “blank” (Y), (S1106) the field is blank. In the case of other processing policies (N), (S1107) an alert to the effect of manual input is displayed, and (S1108) manual input is performed.

  As described above, after the address processing is completed, the cleansing processing result is stored in S1109 to S1111 (similar to S411 to S413).

[S309]
Next, FIG. 12 shows “value normalization (name) processing” (S309). This process (S309) is based on the definition of FIG. 22A and the example of CID = 9 in FIG.

  (S1201) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value normalization (name) processing” is read.

  (S1202) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S1203) Define a “name” character string that can be used. In the present embodiment (specific example), “aiueokaki ...” (all hiragana) is defined as a usable “name” character string.

  (S1204) It is determined whether the value of the field is composed of the above-mentioned “name” character string that can be used. If it is composed of an available “name” character string (Y), the process ends. If there is a character other than the “name” character string that can be used (N), processing is performed in accordance with the field processing policy in S1205 to S1208 (similar to S1105 to S1108). When the above processing is completed, the cleansing processing result is stored in S1209 to S1211 (similar to S411 to S413).

[S310]
Next, FIG. 13 shows “value normalization (telephone number) processing” (S310). This process (S310) is based on the definition of FIG. 22A and the example of CID = 10 in FIG.

  (S1301) First, in the cleansing policy master table 112 (FIGS. 24 and 25), the line of “FID” that matches “value normalization (telephone number) processing” is read.

  (S1302) The target column field of the original data table 110 (FIGS. 20 and 21) is read.

  (S1303) Define available phone numbers (regular format). In this embodiment (specific example), “123456790” and “12345678901” are defined as available telephone numbers.

  (S1304) Define an unusable telephone number (non-regular format).

  (S1305) Then, a comparison table of the above-mentioned “available telephone numbers” and “unusable telephone numbers” is created. In the comparison table of this processing example (FIGS. 31 and 32), the row with CID = 10 corresponds. That is, the table becomes “123-4567” = “1234567”. That is, the table becomes “12-3456-7890” = “1234567890”.

  (S1306) It is determined whether the field value is an available telephone number (regular format). If it matches the regular format (Y), the process ends.

  (S1307) It is determined whether the field value is an unusable telephone number (non-regular format). If it matches the non-regular format (Y), the process is performed according to the field processing policy (the process proceeds to S1310 if they do not match).

  (S1308) If the field processing policy is “Convert to“ Available phone numbers ”(regular format) using the comparison table” (Y), (S1309) Use the comparison table to normalize the phone number format. Convert to

  If neither the “available phone number” nor the “unusable phone number” is described above, processing is performed in accordance with the field processing policy in S1310 to 1313 (similar to S1010 to S1013). When the processing of the telephone number is completed, the cleansing processing result is stored in S1314 to S1316 (similar to S411 to S413).

[S203]
Next, FIG. 14 shows a flow of the analysis data comprehensive determination process (S203). In the analysis data comprehensive determination process (S203), the cleansing result detail table 114, which is the cleansing process history, is referred to and divided into a data set (row) usable for analysis and an unusable data set (row).

  (S1401) First, as an initialization process, “Y” is entered as an initial value in the “final determination” field (column) of the cleansing result table 113 (FIGS. 26 and 27).

  (S1402) Next, in each row of the cleansing result table 113, when the column field is numbered instead of blank (Y), it corresponds to the numbering (HID) from the cleansing result detail table 114 (FIG. 28). Retrieve a row.

  (S1403) When “N” is present in the “CPCRITICAL” field in the extracted row (Y), (S1404) “N” is displayed in the “final judgment” field of the “BID” row of the corresponding cleansing result table 113. ".

[S204]
Next, FIG. 15 shows a flow of analysis data loading processing (S204). In the analysis data load process (S204), a cleansing result detail table 114, which is a cleansing process history, is referred to and a data set (row) usable for analysis is extracted.

  (S1501) First, the original data (original data table 110) (FIGS. 20 and 21) is read.

  (S1502) Next, the cleansing result table 113 (FIGS. 26 and 27) is read.

  (S1503, S1504) In the row of the cleansing result table 113, only the row of “original data” corresponding to the row number (BID) of the row whose “final determination” field is “Y” is stored in the analysis data table (FIG. 29, FIG. 29). Copy to FIG.

  FIG. 29 shows the definition (table schema) of the analysis data table 115. FIG. 30 shows an example of values in the table (115). BID is as described above (FIG. 26). This table (115) is a table that stores a data set that has been cleansed and can be used for analysis, and has the same definition as the original data (FIG. 20). This table (115) does not store the data set (row) if there is any illegal item (field, column) in the data set (row) in the data cleaning process.

[S205]
Next, in the case of analysis processing (analysis data table processing) (S205) using the analysis data table 115 of FIG. 2, the analysis data table 115 is read (loaded) by 108 and analyzed (analysis processing). The flow is to execute.

  FIG. 16 shows a system configuration example corresponding to the analysis process (S205). The configuration shown in FIG. 16 may be integrated with the system configuration shown in FIG. The analyst (U) performs analysis with reference to only the analysis data table 1603. The analyst (U) connects to the analysis data table 1603 (corresponding to 115) (such as a device that stores the data) from the terminal device 1601 via the network 1602, and executes analysis (analysis processing).

[S206]
Next, FIG. 17 uses the original data (110), the cleansing result (113), and the cleansing result details (114), which are detailed processing examples of the cleansing history utilization analysis execution process (S206) performed by 109 of FIG. An example of “calculation of average age” is shown as an example of the analysis data extraction method and analysis process (analysis procedure). When there is an “age” field available in the original data (110) by the procedure of FIG. 17 but not loaded in the analysis data table 115, the cleansing result (113) and the cleansing result details (114) are displayed. Indicates that it can be used by reference.

  (S1701, S1702, S1703) First, each data (table) of original data (110), cleansing result (113), and cleansing result details (114) is loaded (read). (S1704) The “age” column field of the cleansing result (113) is read, and (S1705) whether the “age” field of each row is numbered (enters a value) or blank. When the “age” field is “blank” (N), the value of the “age” field of the corresponding original data (110) is used for the calculation. When the “age” field is numbered (a value is entered) (Y), the cleansing result details (114) corresponding to the “age” field value (number) of the cleansing result (113) (S1706) ) HID field number (attachment number) corresponding to (matching) is searched for and extracted. (S1707) Refers to the “CPCRITICAL” field in the extracted row (field), and when the value of “CPCRITICAL” is only “Y” (Y), (S1708) The source corresponding to the HID in the cleansing result detail table 114 The value of the “age” field in the BID row of the data (110) (the cell value of the cleansing result table 113) is used for analysis.

  (S1709) On the other hand, when “CPCRITICAL” is “N” in the extracted row (S1707-N), the “age” field of the BID row of the original data (110) corresponding to the HID of the cleansing result detail table 114 (The value of the cell in the cleansing result table 113) is not used for analysis.

  (S1710) Then, in the “age” field of the original data (110), “average age” is calculated using values available for analysis.

  FIG. 18 shows a system configuration example corresponding to the above processing (S206). The configuration in FIG. 18 may be integrated into the system configuration in FIG. The analyst (U) performs analysis with reference to the three tables (1803, 1804, 1805). The analyst (U) connects to the original data 1803 (corresponding to 110), the cleansing result table 1804 (corresponding to 113), and the cleansing result detailed table 1805 (corresponding to 114) via the network 1802 from the terminal 1801 and analyzes ( (Analysis processing) is executed.

[Effects]
As described above, according to the present embodiment, it is ensured that there is no fraud on the basis of the history reference even for items other than the fraudulent items in the data set discarded in the conventional data cleaning ( Check) to extract the item. Thereby, the extracted data item can be used for calculation of a column for data analysis. By making it possible to use data items that were conventionally overlooked as information, the analysis accuracy can be improved.

  As mentioned above, the invention made by the present inventor has been specifically described based on the embodiment. However, the present invention is not limited to the embodiment, and various modifications can be made without departing from the scope of the invention. Needless to say.

  The present invention is applicable to data analysis system software and the like. In particular, questionnaire analysis (acquisition of consumer profiles, etc.), business transaction data analysis (analysis of consumer purchasing trends and product sales trends, etc.), customer data analysis (customer profile analysis, etc.), store data analysis (store analysis, etc.) ) And the like.

  DESCRIPTION OF SYMBOLS 1 ... Data cleansing system, 10 ... Setting part, 11 ... Data cleansing part, 12 ... Cleansing history management part, 13 ... Fraud presence / absence determination part, 14 ... Analytical data extraction part, 20 ... List of policy & cleansing process, 21 ... Data before cleansing, 22 ... Data after cleansing, 23 ... Cleansing history, 24 ... Judgment data, 25 ... Analysis data, 101 ... Terminal device, 102 ... Network, 103 ... Data cleansing server, 104 ... Analysis data creation policy Setting processing unit 105... Cleansing processing unit 106. Analyzing data comprehensive determination processing unit 107. Analyzing data load processing unit 108. Analyzing data table processing unit 109 109 Cleansing history utilization analysis execution processing unit 110. Original data table, 111 ... Cleansing master table Le, 112 ... cleansing policy master table, 113 ... cleansing result table, 114 ... cleansing result detail table, 115 ... analysis data table, 116 ... processing history storage system.

Claims (2)

A data cleansing system comprising a data cleansing server that performs processing including data cleansing process for data analysis,
The data cleansing server
A setting unit configured to set setting information including a flag of availability regarding the result of the data cleansing process, which is setting information including the definition and policy of the data cleansing process, based on a user operation;
Based on the setting information for the data analysis, performs the data cleansing process for removing a first data set containing an invalid item those of the first data is a data cleansing before data which is the original data, as a result, a second data which is data after data cleansing, and data cleansing unit,
A cleansing history management unit that performs processing for storing and referencing the history of the data cleansing process by the data cleansing unit including information on the first data set removed from the first data ;
By referring to the history table, in the first data set removed from the first data, the first item that is the illegal item and cannot be used for the data analysis; A determination unit that determines a second item that is an item other than that and that is not fraudulent and that can be used for the data analysis;
An extraction unit that extracts the second item, which is an item usable for the data analysis, from the history corresponding to the second data based on the result of the determination;
Based on the user's operation, an analysis unit that performs the data analysis using the second data and the second item extracted from the history;
A data cleansing system characterized by comprising: A program that causes the data cleansing server to execute the process in a data cleansing system including a data cleansing server that performs a process including a data cleansing process for data analysis,
As a process to be executed by the data cleansing server,
A first process for setting setting information including a flag of availability regarding the result of the data cleansing process, the setting information including the definition and policy of the data cleansing process based on a user operation;
Based on the setting information for the data analysis, performs the data cleansing process for removing a first data set containing an invalid item those of the first data is a data cleansing before data which is the original data, As a result, a second process of obtaining second data that is data after data cleansing;
A third process that enables reference first of said removed from the data including information about the first data set, and store a history of the data cleansing process by the second processing table,
Wherein by referring to the history table, in the data set that has been removed from said first data, said the first item is an item unavailable to the data analysis an invalid item, otherwise A fourth process for determining a second item that is an item that is not fraudulent and that can be used for the data analysis ;
A fifth process of extracting the second item, which is an item usable for the data analysis, from the history corresponding to the second data based on the result of the determination;
A sixth process for performing the data analysis using the second data and the second item extracted from the history based on an operation of the user;
The program characterized by having.

Images