CLASSIFICATION METHOD OF DATA PATTERN AND CLASSIFICATION SYSTEM OF DATA PATTERN

Abstract

To easily find columns containing information to be shared between databases found when the databases (DBs) are combined.

Description

TECHNICAL FIELD

The present invention relates to a data-pattern classification method and a classification system, by which the candidates of precise rules are filtered from information in a database if the type of information inputted in a column of the database is not unified in the column.

BACKGROUND ART

An ordinary database (hereinafter referred to as “DB”) contains information about a piece of data, the information being inputted to a dedicated column. In columns such as a remarks column where items to be inputted are not definitely determined, various types of information may be inputted. When a DB including such a remarks column is combined with another DB, information to be shared between the DBs in the remarks column may interfere with proper combination.

Such DBs will be described below using specific examples.

ID	School name	Prefecture	City town and village	House number	Telephone number	Position		Remarks
1	A University	Tokyo	Minator-ward	1-11-11	5676	S		1-11-11, Minato-ward Tokyo
2	B High school	Aichi	Nagoya	2-22	6789	S		2-22, Nagoya-city
3	C Technical College	Osaka	Osaka	33-33-3	1234	S		7890-1234
indicates text missing or illegible when filed

Table 1 indicates an example of a DB where character strings regularly overlap in remarks columns. In the remarks columns, one of (1) address and (2) telephone number is inputted. In the case of (1) address, information in columns “prefecture,” “city, town and village,” and “house number” is shared. However, “prefecture” is not inputted in the remarks column of “ID:2” data. In this way, the expressions of addresses are not unified in the columns.

ID	Address	Completion :	Area (m2)	Maintenance company	Remarks
1	O-O-O, Minato-ward, Tokyo	2000/1/1	1000	□ □, Inc.	School
2	x-x, Nagoya-city, Aichi	1995/1/1	1200	XX maintenance	Prefecture high school

Table 2 indicates an example of a DB about buildings. When the DB in table 1 and the DB in table 2 are combined, an address is a common item serving as a trigger. However, table 1 does not have any column corresponding to an address, and part of the data contains telephone numbers, so that a school name or a telephone number in table 1 and building information in table 2 cannot be associated with each other.

To address this problem, a conventional technique is proposed, in which rules are preregistered to determine which one of the rules is abided by an inputted character string (e.g., PTLs 1 and 2).

Citation List

Patent Literatures

• PTL 1] Japanese Patent Application Publication No. 2016-136341
• PTL 2] Japanese Patent Application Publication No. 2014-219833

SUMMARY OF THE INVENTION

Technical Problem

In table 1, types of information (addresses or telephone numbers) are inputted in the remarks columns. An input to each of the remarks columns indicates one type of information. Although address information is inputted in the remarks columns of “ID:1” and “ID:2” in table 1, an address in the remarks column of “ID:1” is inputted from a prefecture name, whereas an address in “ID:2” is inputted from the name of a city, town or village. In this way, the expressions of information are not unified. This is because a human error has occurred or an input style for addresses is not specified.

If information to be inputted to a remarks column is not specified, the related arts described in PTL 1 and PTL 2 cannot cope with such information.

The present invention has been devised in view of such circumstances. An object of the present invention is to provide a data-pattern classification method and a classification system, by which columns containing information to be shared between databases can be easily found when the databases are combined. [Means for Solving the Problem]

An aspect of the present invention is a data-pattern classification method for a database containing character strings regularly overlapping between the input values of mixed columns and information inputted in another specific column, the mixed column including types of information, the method including: extracting means for extracting a branch column for changing the type of information to be inputted to the mixed column, the branch column being extracted by one of a heuristic first technique and a second technique, the first technique using timing to change the pattern of the mixed column in the database including the mixed column, the pattern referring to a column where a character string overlaps the input value of the mixed column, the second technique using a statistical technique of a likelihood test; and classification to obtain the number of types of information stored in the mixed columns, by grouping, according to information indicated by the patterns, the patterns obtained from the mixed columns based on the extracted branch column.

Effects of the Invention

According to an aspect of the present invention, columns containing information to be shared between databases can be easily found when the databases are combined.

DESCRIPTION OF EMBODIMENTS

An embodiment of the present invention applied to a data-pattern classification method will be described below.

Outline

The outline of the present embodiment will be described below. Columns in which types of information are inputted as in a remarks column in Table 1 are referred to as mixed columns in the present specification. A DB in the present embodiment is provided for character strings regularly overlapping between the input values of mixed columns and information inputted in another specific column. A combination of the input value of the mixed column and a column containing a character string overlap is referred to as a pattern.

If input values with identical patterns are simply handled as a group indicating the same information so that the input values of the remarks columns are grouped according to patterns obtained from the mixed columns, groups may be excessively generated.

In the present embodiment, patterns obtained from the mixed columns are grouped according to information indicated by the patterns. In the example of table 1, different patterns are obtained from the remarks columns of ID: 1 and ID:2 but are classified as the same group because address information is inputted in both of the columns.

In the present embodiment, it is assumed that a branch column is present. Specifically, the branch column indicates a column for changing information about the input value of the remarks column according to an inputted character string. For example, in a DB containing a mixed column, like the classification column, if a character string X (e.g., “address”) is inputted in a branch column as in a “classification” column, an input is made in the remarks column under a rule A (information about an address is inputted to the remarks column), whereas if another character string Y (e.g., “telephone”) is inputted in the branch column, an input is made in the remarks column under a rule B (information about a telephone number is inputted to the remarks column). Thus, the type of an input value in the branch column is identical to the type of information in the mixed column.

For finding a branch column, two methods are used:

• A) A heuristic method using the timing of a pattern change of the mixed column
• B) A method using a statistical technique of a likelihood test

The branch column is found as described above, so that patterns are grouped and the number of types of information stored in the mixed columns is determined. When the mixed columns are grouped, the mixed columns are classified by the type of information, facilitating a search for columns containing information to be shared between DBs when the DBs are combined.

Additionally, in combination with a technique of extracting a representative pattern from patterns belonging to one group, the order of superiority is determined among the patterns. This can unify input values according to the best pattern.

Method Steps

The specific steps of a data-pattern classification method will be described below.

It is assumed that a known column corresponds to a mixed column. Pieces of data stored in a DB is denoted as d_i (ie {1, 2, ..., n})(n: the total number of pieces of stored data), and D denotes the data sets of mixed columns that are not blank.

For d_i, information about i-th data is listed except for mixed columns.

For example, in the DB of table 1, d_i = [“A university”, “Tokyo,” “Minato Ward,” “1-11-1,” and “5678”] is obtained.

At this point, a_j denotes a mixed column corresponding to data d_j that is an element of a set D.

Hereinafter, for character strings str1 and str2, a set of common characters is denoted as str1∩str2.

Step 0 (Preprocessing: Pattern Extraction)

From among inputted information other than mixed columns, indexes including character strings shared with a_j are all extracted. Specifically, an index x is found such that a_j∩d_j [x]≠φ (x≠y) is established. φ indicates an empty set.

However, a search for the index x needs to satisfy all the following conditions i) to iii):

• i) A common character string is as long as or longer than a half of the length of a character string d_j[x].
  • If numbers or symbols (e. g., :, ;, -, +, @) are shared, the common character string requires a length of 2 or more, or d_j [x] needs to include characters other than numbers and symbols.
• ii) the common character string is identical to a character string obtained by deleting the first character from the character string d_j [x] or deleting the last character from the character string. For example, in the case of d_j[x] = “Fukushima Koriyama,” the condition ii) is satisfied when a character string shared with d_j[y] is “Fukushima” or “Koriyama,” whereas “Fukuyama” does not satisfy the condition ii).
• iii) Common character strings should not overlap one another among the elements of different indexes. For example, in the case of a_j = “Fukushima Koriyama,” the condition iii) is satisfied when d_j[x] = “Fukushima” and d_j[z] = “Koriyama” are obtained, whereas the condition iii) is not satisfied when d_j[x] = “Fukushima” and d_j [z] = “Fukushima Koriyama” are obtained.

If multiple indexes are extracted, a combination thereof is referred to as a pattern as described above. Patterns are extracted for all d_j∈D. The set of the patterns is denoted as P, and the i-th pattern is denoted as p_i∈P. An index is stored for each of the patterns. For example, p_i = [1,4, 6] indicates a common part shared with character strings inputted in the first, fourth, sixth columns. By performing such preprocessing, the following processing steps for grouping can be efficiently performed.

First, technique A), the heuristic method will be described below.

Step 1A

The step 0 is performed to extract patterns.

Step 2A

The total number of the extracted patterns is denoted as | P |. Columns are selected according to the following two conditions:

• (1) The number of types of character strings inputted to the columns is |P| or less.
• (2) The columns do not contain any elements of the extracted patterns.

Step 3A

For each of the selected columns, the following value s is calculated: s:=(error_c/c)+(error_f/f)

(when vertically adjacent pieces of data are compared with each other, c:the number of times the input pattern of the mixed column changes, error_c:the number of times the input of the column is not changed even when the input pattern of the mixed column changes, f:the number of times the input pattern of the mixed column is kept, error_f:the number of times the input of the column changes even when the input pattern of the mixed column does not change.)

As the conditions of the branch column, the pattern of the mixed column may change concurrently with a change of an input character string, whereas the pattern may be unchanged when the character string does not change. Thus, the number of times the conditions are not satisfied and the ratio are indicated.

Step 4A

Columns are selected to obtain minimum s. The selected columns are denoted as h_j (j ∈ {1, 2, ...}), and a set of h_j is denoted as H. At this point, the pattern of the mixed column is the data of p_j, and a character string with the highest frequency of occurrence is denoted as mi from among character strings inputted to h_j.

However, it is necessary to satisfy the condition “the number of times m_i occurs exceeds a half of the number of data pieces in the pattern pi.” If the condition is not satisfied, h_j is not assumed to be the branch column and is excluded from the set H.

If m_i is determined for each pattern pi(i∈{1, 2, ..., |P|}), the column h_j is assumed to serve as the branch column.

Step 5A

If the number of types of character strings inputted in the branch column h_j is smaller than the total number |P| of patterns, the patterns are grouped. Specifically, patterns with the same m_i are grouped as patterns indicating identical information. A representative pattern is selected from the grouped patterns.

A technique B), a statistical method according to a likelihood will be described below.

Step 1B

The step 0 is performed to extract patterns.

Step 2B

The total number of the extracted patterns is denoted as |P|. Columns are selected according to the following two conditions:

• (1) The number of types of character strings inputted to the columns is |P| or less.
• (2) The columns do not contain any elements of the extracted patterns.

Step 3B

A set of the selected columns is denoted as H. For each column h_j of the set H, the pattern inputted in the mixed column is the data of pi, and a character string with the highest frequency of occurrence is selected from among character strings inputted to h_j and is denoted as m_ji. However, in the case of multiple candidates for m_ji, that is, multiple character strings having a mode, h_j is not assumed to be the branch column and is excluded from the set H.

Step 4B

Regarding the column h_j of the set H, a numeric value corresponding to each x_k1 of a two-dimensional tabulation in Table 3 is obtained, for each of p_i and m_ji, from the data. x_k1: the number of pieces of data in which m_ji is inputted in a pattern p_k.

However, in the presence of k and 1 (k≠l, k≠i, l≠i) satisfying m_jk = m_jl in the pattern p_i,

x_ik = x_il = M/2 (M is the total number of pieces of data in which m_jk (or m_jl) is inputted) is inputted to obtain equalization.

	Pattern pi	Pattern pi+1	Total
. . .
j (i+1) input	xii	x(i+1)i	${\displaystyle \sum _k{x}_{ki}}$
m(i+i) input	xi(i+1)	x(i+1)(i+1)	${\displaystyle \sum _k{x}_{k(i+1)}}$
...
Total	${\displaystyle \sum _l{x}_{il}}$	${\displaystyle \sum _l{x}_{(i+1)l}}$	${\displaystyle \sum _l{\displaystyle \sum _k{x}_{kl}}}$

By using a chi[χ]-square test for the data, a hypothesis “Compliance with each pattern pi and the input of m_ji accidentally occur at the same time” is tested. An expected frequency is indicated in table 4.

	Pattern p¡
...
mji input	$y_{il}=\frac{{\displaystyle \sum k_{}{x}_{ki}}{\displaystyle \sum l_{}{x}_{il}}}{{\displaystyle \sum l_{}}{\displaystyle \sum k_{}{x}_{kl}}}$	${\displaystyle \sum _k{x}_{ki}}$
...
Total	${\displaystyle \sum _l{x}_{il}}$	${\displaystyle \sum _l{\displaystyle \sum _k{x}_{kl}}}$

By the expected frequency table in table 4, a test statistic χi² is determined according to the following expression:

${\chi }_i^2={\displaystyle \sum _l{\displaystyle \sum _k\frac{{\left(x_{kl}-y_{kl}\right)}^2}{y_{kl}}}}$

The foregoing operation is performed on each h_i.

The calculated test statistic χi² is converted into a p value at a degree (|P|-1)² of freedom of a test amount. If the p value is larger than a significance level a, the hypothesis is rejected and h_i is outputted as the branch column. In the presence of multiple h_i, h_i with a maximum p value is used as the branch column.

Step 5B

If the number of types of character strings inputted in the branch column h_j is smaller than the total number |P| of patterns, the patterns are grouped. Specifically, patterns with the same m_i are grouped as patterns indicating identical information. A representative pattern is selected from the grouped patterns.

Example

A specific example based on the steps of the data-pattern classification method will be described below.

ID	School name	Prefecture	City, town and village	House number	Telephone number	Position		Remarks
1	A University	Tokyo	Minato-ward	1-11-11	5678	S		1-11-11, Minato-ward, Tokyo
2	B High school	Aichi	Nagoya	2-22	6789	S		2-22 Nagoya-city
3	C Technical College	Osaka	Osaka	33-33-3	1234	S		7890-1234
4	D University	Kanagawa	Yokohama-city	4-4-4	890	S		4-4-4, Yokohama-city, Kanagawa
5	E University	Mie	Matsuzaka-city	5-55-5	4567	S		5-55-5, Matsuzaka-city, Mie
6	F High school	Hyogo	Kobe-city	66-6	9012	R		66-6, Kobe-city, Hyogo
7	G High school	Saitama	Kasape-city	7-77-7	234	R		049-012-234
8	H Junior high school	Gifu	Gifu-city	888	123	R		058-90-123
9	I Junior high school	Kyoto	Kyoto-city	9-99	3456	R		9-99, Kyoto-city, Kyoto
10	J Technical school	Chiba	Ichlama-city	00-000	789	R		00-000, Chiba
indicates text missing or illegible when filed

Table 5 indicates an example of a DB including input rules. In the remarks columns, one of (1) address and (2) telephone number is inputted. In the case of (1) address, information in columns “prefecture,” “city, town and village,” and “house number” is shared.

Step 0

Table 6 indicates extracted combinations of the columns including character strings shared with the remarks columns.

ID Common column
1  Prefecture, city, town, village, house number
2  City, town and village, house number
3  Telephone number
4  Prefecture, city, town, village, house number
5  Prefecture, city, town, village, house number
6  Prefecture, city, town, village, house number
7  Telephone number
8  Telephone number
9  Prefecture, city, town, village, house number
10 Prefecture, house number

In table 6, “ID” is not a common item because the common character corresponds to one digit of a number, which does not satisfy the condition i). Table 6 indicates four types of patterns as follows:

• p₁ = [prefecture, city, town and village, house number]
• p₂ = [telephone number]
• p₃ = [city, town and village, house number]
• p₄ = [prefecture, city, house number]

Technique A) Heuristic Method

Step 1A

The step 0 is performed to extract patterns.

Step 2A

Columns are selected so as to satisfy the following two conditions:

• (1) The number of types of character strings inputted in the columns is four or less.
• (2) The columns are not any elements of the extracted patterns.

The columns “position” and “classification” satisfy these conditions. The columns “ID” and “school name” each contain ten or more types of character strings and thus do not satisfy the condition (1).

Step 3A

For the column “position,” the following is determined:

• c = 6 (the pattern changes when the ID changes from 1 to 2, from 2 to 3, from 3 to 4, from 6 to 7, from 8 to 9, and from 9 to 10) error_c = 6 (the input character does not change at any one of the times)
• f = 3 (the pattern does not change when the ID changes from 4 to 5, from 5 to 6, and from 7 to 8)
• errorf = 1 (the pattern changes when the ID changes from 5 to 6)
• Thus, s:=(error_c/c) + (error_f/f) =1+(⅓)≈ 1.333 is obtained.

For the column “classification,” the following is determined:

• c = 6 (the pattern changes when the ID changes from 1 to 2, from 2 to 3, from 3 to 4, from 6 to 7, from 8 to 9, and from 9 to 10) error_c = 2 (the input character does not change when the ID changes from 1 to 2 and from 9 to 10)
• f = 3 (the pattern does not change when the ID changes from 4 to 5, from 5 to 6, and from 7 to 8)
• errorf = 0 (the pattern does not change at the foregoing timing)
• Thus, s:= (error_c/c) + (error_f/f) = (2/6)+0≈0.333 is obtained.

Step 4A

The value of s is small in the column “classification.” At this point, the pattern of the remarks column is the data of p_i. From among character strings inputted to the column “classification,” the following modes are obtained:

• p₁:m₁ = address (five times in five pieces of data)
• p₂:m₂ = telephone number (three times in three pieces of data)
• p₃:m₃ = address (one time in a piece of data)
• p₄:m₄ = address (one time in a piece of data)

Thus, four types of patterns are classified into p₁, p₂, _P3, and p₄.

Step 5A

The number of types of character strings inputted in the column “classification” is two, and thus the patterns are grouped into two types. In this case, the pattern that occurs most frequently in the data is regarded as a representative pattern, and the other patterns are regarded as patterns generated by erroneous inputs.

In the pattern of m_i = “address,” the occurrence frequency in the data is maximized in five times of the pattern p₁. Hence, the pattern p₁ serves as a representative pattern and is classified as a group assumed to be indicative of identical information.

A Technique B), Statistical Method According to Likelihood

Step 1B

The step 0 is performed to extract patterns.

Step 2B

Columns are selected so as to satisfy the following two conditions:

• (1) The number of types of character strings inputted in the columns is four or less.
• (2) The columns are not any elements of the extracted patterns.

The columns “position” and “classification” satisfy these conditions. The columns “ID” and “school name” each contain ten or more types of character strings and thus do not satisfy the condition (1).

Step 3B

h₁:“position” and h₂:“classification” are set. For the pattern p_i (i = 1,2,3,4), m_ji(j = 1,2) is set as indicated in table 7.

	P1	P2	P3	P4
h1	m11 =S	m12 = R	m13 =R	m14 =S
h2	m21 = Address	m22 = Telephone	m23 =Address	m24 = Address

Step 4B

For each column h_j, two-dimensional tabulations are created as indicated in table 8 and table 9.

h1
	p1	p2	p3	p4	Total
m11	3	½	0	0	7/2
m12	2	2	0	0	4
m13	0	0	1	0	1
m14	0	½	0	1	3/2
Total	5	3	1	1	10

In table 8, ½ is inputted at m₁₁:p₂ and m₁₄:p₂ because m₁₁ and m₁₄ are identical to each other with respect to a piece of data in which a character string S is inputted in the pattern p₂.

h2
	p1	p2	p3	p4	Total
m21	5	0	0	0	5
m22	0	3	0	0	3
m23	0	0	1	0	1
m24	0	0	0	1	1
Total	5	3	1	1	10

For each h_j, table 10 and table 11 of expected frequencies are created.

h1
	p1	p2	p3	p4	Total
m11	7/4	21/20	7/20	7/20	7/2
m12	2	6/5	⅖	⅖	4
m13	½	3/10	⅒	⅒	1
m14	¾	9/20	3/20	3/20	3/2
Total	5	3	1	1	10

h2
	p1	p2	p3	p4	Total
m21	5/2	3/2	½	½	5
m22	3/2	9/10	3/10	3/10	3
m23	½	3/10	⅒	⅒	1
m24	½	3/10	⅒	⅒	1
Total	5	3	1	1	10

As indicated by table 8 and table 10, a chi-square test amount _X12 with respect to h₁ is _X12 ≈ 18.23

As indicated by table 9 and table 11, a chi-square test amount _X22 with respect to h₂ is _X22 = 31

In this case, a significance level α = 0.01 is determined. Since an upper significance probability of 0.01 has a value of 21.7 at 9 degrees of freedom, a hypothesis is established at h₁ but is rejected at h₂, and h₂ serves as the branch column.

In the case of a small amount of sample data as in the present example, a Fisher test is more suitable than a chi-square test. In an actual application of the technique, however, it is assumed that the number of data pieces is sufficiently large and a chi-square test is used.

Step 5B

The number of types of character strings inputted in the column “classification” of h₂ is two, and thus the patterns are grouped into two types. In this case, the pattern that occurs most frequently in the data is regarded as a representative pattern, and the other patterns are regarded as patterns generated by erroneous inputs.

Specifically, in the pattern of m_2i = “address,” the occurrence frequency in the data is maximized in five times of the pattern p₁. Hence, the pattern p₁ serves as a representative pattern, and other patterns p₃ and p₄ are classified as a group assumed to be indicative of the same information as the pattern p₁.

By using the technique A) heuristic method and the technique B) statistical method according to likelihood, patterns obtained from the mixed columns can be grouped according to information indicated by the patterns.

Effects of Embodiment

According to the present embodiment specifically described above, columns containing information to be shared between databases can be easily found when the databases are combined.

The method of the present invention can be also implemented by a computer system in which a plurality of databases are combined. A program for implementing the method steps can be recorded in a recording medium or can be provided via a network.

Furthermore, the invention of the present application is not limited to the foregoing embodiment and can be changed in various ways within the scope of the invention in the implementation of the invention. Moreover, the embodiment includes inventions at various stages. Various inventions may be extracted with a proper combination of disclosed constituent components. For example, even if some of the constituent components are deleted from all the constituent components described in the embodiment, a configuration may be extracted as the invention without the deleted constituent components as long as the invention can solve the problem described in the technical problem and obtain the effects described in the effects of the invention.

Claims

1. A data-pattern classification method for a database containing character strings regularly overlapping between input values of mixed columns and information inputted in another specific column, the mixed column including types of information, the method comprising:extracting a branch column for changing a type of information to be inputted to the mixed column, the branch column being extracted by one of a heuristic first technique and a second technique, the first technique using timing to change a pattern of the mixed column in the database including the mixed column, the pattern referring to a column where a character string overlaps the input value of the mixed column, the second technique using a statistical technique of a likelihood test; andclassification to obtain the number of types of information stored in the mixed columns, by grouping, according to information indicated by the patterns, the patterns obtained from the mixed columns based on the extracted branch column.

2. The data-pattern classification method according to claim 1, further comprising, prior to the extraction, preprocessing for extracting all indexes of columns including character strings shared with the mixed columns.

3. The data-pattern classification method according to claim 1, wherein in the classification, a representative pattern is selected from the patterns belonging to one group, an order of superiority is determined among the patterns, and the patterns are grouped with the input values unified according to the representative pattern.

4. A data-pattern classification system for a database containing character strings regularly overlapping between input values of mixed columns and information inputted in another specific column, the mixed column including types of information, the system comprising:extracting means for extracting a branch column for changing a type of information to be inputted to the mixed column, the branch column being extracted by one of a heuristic first technique and a second technique, the first technique using timing to change a pattern of the mixed column in the database including the mixed column, the pattern referring to a column where a character string overlaps the input value of the mixed column, the second technique using a statistical technique of a likelihood test; andclassifying means for obtaining the number of types of information stored in the mixed columns, by grouping, according to information indicated by the patterns, the patterns obtained from the mixed columns based on the extracted branch column.