JP6623754B2 - Tabular data processing program, method and apparatus - Google Patents

Description

  The present invention relates to a tabular data processing program, a method and an apparatus.

  In order to manage customer information, sales information, and any other information, for example, an RDB (Relational Database) is used. When an RDB is used, by creating a normalized table, information can be centrally managed and inconsistency, loss, and redundancy can be reduced. In order to obtain a record for the same entity from one or a plurality of tables, a process called “name matching” may be performed. By performing name identification, for example, a plurality of accounts owned by the same person can be collectively handled, so that the counting result becomes more accurate.

  In addition, a technique has been proposed in which a user registers, deletes, and updates the association between a cell and automatic execution data to automatically execute a spreadsheet program without being conscious of internal data (for example, Patent Document 1). 1). Further, a technique of automatically generating a source code of a computer program based on a design document has been proposed (for example, Patent Document 2).

JP-A-5-73589 JP 2009-223843 A JP 2005-135221 A

  In order to use the RDB, table design, record registration and maintenance are required. Further, specialized techniques such as construction of a server, use of a data manipulation language, and use of an RDBMS (Relational Database Management System) may be required. Therefore, in order to easily manage data without using RDB, tabular data ("Delimiter-Separated Values", "Character-Separated Values", etc.) recorded in text represented by CSV (Comma Separated Values) Is also often used.

  However, when such tabular data is processed by spreadsheet software or the like, it takes time and effort to perform data operations such as combining tables and compiling values to be executed by an RDBMS. There's a problem. In addition, when information assets having different data structures are held for each business partner, each branch office, each department, and the like, it is necessary to develop a dedicated system for integrating and tabulating the information assets.

  Therefore, an object of the present invention is to provide a technique for supporting the operation of tabular data expressed by delimiters.

  A tabular data processing device according to the present invention is a device that processes tabular data in which a plurality of records including a plurality of elements respectively corresponding to a plurality of items are registered. A vertical combining unit that performs a vertical combining process of generating one tabular data by associating the items included in the data with each other, and using the items included in the generated one tabular data as a key based on a user operation An aggregating unit that performs an aggregating process for aggregating records included in one tabular data, and an item as re-execution information for executing a vertical joining process and an aggregating process on tabular data having the same item configuration And a control unit for storing at least the key item and the correspondence in the storage device.

  In this way, the structure of the tabular data can be reconstructed based on the operation of the user. Further, information for executing a similar operation can be stored for tabular data having a similar configuration. Therefore, it is possible to provide a technique for supporting operation of tabular data.

  In addition, the tabular data processing apparatus further includes a dictionary registration unit that receives registration of words having a relationship between the same concept, or a relationship between a superordinate concept and a subordinate concept, based on a user's operation. Records in which the elements of the items have the same concept relationship or records in which the superordinate concept and the subordinate concept have a relationship may be aggregated. This makes it possible to perform name matching based on a dictionary set by the user when aggregating records.

  A table setting unit configured to store, in a storage device, schedule information including at least re-execution information and a time at which a process is to be executed in accordance with the re-execution information, based on a user operation; And a batch processing unit that performs the vertical combination process and the aggregation process defined by the re-execution information based on the time set in the schedule information. In this way, the process can be scheduled.

  In addition, the tabular data processing device graphically displays the execution order of the vertical combination process and the aggregation process, and associates items that are settings for performing the vertical combination process or settings for performing the aggregation process. The display unit may further include a display unit that displays a key item that is or a configuration of an item of tabular data input to the vertical combination process or the aggregation process or a configuration of an item of tabular data to be output. In this way, the user can visually confirm the operation of the tabular data to be executed.

  A tabular data processing apparatus according to another aspect of the present invention includes table data in which a plurality of records including a plurality of elements corresponding to a plurality of items are registered in an input table which is input tabular data. Aggregate records and generate an output table that is tabular data to be output. Specifically, a setting processing unit that receives designation of the correspondence between the items included in the plurality of input tables and the items included in the output table, a list area that displays a list of the items included in the output table, A display control unit that displays a key designation area that displays key items used for aggregation, and a key designation unit that accepts designation of key items by a user operation of dragging an item displayed in the list area to the key designation area. An output unit that aggregates records of the input table in which the value of the designated key item satisfies a predetermined condition and generates an output table.

  In this way, the user can input a request for data operation without inputting a query in text. That is, a technique for supporting operation of tabular data can be provided.

  In addition, the tabular data processing device is configured such that, for each of the key items, a key setting unit that receives specification of a condition for aggregating records, and a display mode of the key item for which the condition is specified, that the specification of the condition is completed. And a first display change unit for changing the display mode to a predetermined display mode. With this configuration, the user can visually recognize whether or not the setting required for the data operation request for the key item has been completed.

  Further, the tabular data processing device further displays, in the display step, a tallying item area for displaying the tallying target item, and operates the tallying item by dragging the item displayed in the list area to the tallying item area. A tally item specification part that receives the specification, a tally method specification part that receives the specification of the tally method for the specified tally item, and a display mode of the tallying target item for which the tally method is specified in the tally item area. A second display change unit configured to change to a predetermined display mode indicating that the specification of the counting method has been completed may be further provided. In this way, the user can input a request for data operation related to aggregation without inputting a query in text. Further, the user can visually recognize whether or not the necessary setting for the tally item has been completed.

  In the tabular data processing device, the tabulation item designating unit receives a request to generate a new item as a tabulation item, and arranges the tabulation items in a row in the tabulation item area in a predetermined direction representing the order. The display method and the totaling method designating unit use the item in the list area and / or the totaling item area in the totaling item area as at least one of the totaling items in the order of the new item as the totaling method of the new item. The specification of a calculation expression or a conditional expression for determining the element may be accepted. In this way, the user can input a request for further counting using the counting result of the item whose order is earlier. By inputting step by step, the user can easily input a complicated tally request.

  Further, the tabular data processing device displays the designated key items in a line along a predetermined direction indicating the order in the key designation unit, and changes the order of the key items according to the drag operation of the user. The output unit may sort the records of the input table in which the values of the key items satisfy a predetermined condition by a plurality of keys according to the order of the key items to generate an output table. In this way, the user can easily specify key items.

  In addition, the tabular data processing device according to another aspect of the present invention may include a table format data in which a plurality of records including a plurality of elements corresponding to a plurality of items are registered. Aggregate the included records and generate an output table that is tabular data to be output. More specifically, based on an input table, an element associated with an item order is extracted, and item-specific data to be generated for each item to be retained for each item in association with a record order in tabular data. It has a generation unit and an output table generation unit that aggregates elements included in the itemized data and generates an output table from the aggregated itemized data.

  In this way, tabular data can be decomposed into item-specific data expressed in units of items and handled. In addition to aggregating the elements included in the itemized data, the itemized data can be combined according to the items output to the output table. That is, the item-by-item data is suitable for performing complicated processing. In addition, as the consolidation processing, for example, table joining and tabulation can be performed.

  In addition, the tabular data processing apparatus further includes an ordered set generating unit that generates a set of the order of records corresponding to a plurality of elements satisfying a predetermined condition using the item-by-item data, and the output table generating unit includes: Alternatively, the elements included in the itemized data may be aggregated based on the order belonging to the set. In this way, aggregation can be performed using a set of record orders (in other words, numbers indicating the order). Here, in order to generate a set of orders, the order of records may be classified into a set that satisfies a predetermined condition and to which each element belongs. There is no need to search for records for aggregation, and processing can be performed at high speed by using a set of record orders.

  In addition, the tabular data processing device further includes a vertical combining unit that generates a union of the item-by-item data of the plurality of tabular data, based on the data representing the correspondence of the items among the plurality of tabular data, The order set generation unit may generate a set of the order of the records using the union of the item-by-item data. This makes it possible to perform data manipulation on a plurality of tabular data.

  Further, the table format data processing device may register an element in which different values are combined in the itemized data after aggregation when elements that are aggregated based on the order belonging to the set have different values. Good. In this way, records satisfying the predetermined condition can be displayed collectively.

  In addition, the tabular data processing apparatus further includes a second ordered set generation unit that generates a set of the order of the records corresponding to the plurality of elements satisfying the second predetermined condition using the itemized data after aggregation. The output table generation unit further aggregates the elements included in the aggregated itemized data based on the order belonging to the set generated by the second ordered set generation unit, and generates an output table from the aggregated itemized data. You may make it. In this way, it is possible to further perform the aggregation processing on the itemized data after the aggregation processing such as the combination and the aggregation is performed. That is, complicated data operations can be performed step by step.

  Further, a tabular data processing device according to another aspect of the present invention processes tabular data in which a plurality of records including a plurality of elements respectively corresponding to a plurality of items are registered as input data. Specifically, the number of elements corresponding to a predetermined item classified into a group based on a predetermined rule is totaled for each group, and a totaling unit that outputs a totaling result, and a group in the totaling result from input data, An output unit that extracts and outputs a record including an element classified as any of the above, and a correction unit that receives a request to change an element included in the output record and changes the element.

  This processing corresponds to the data profiling processing in the present invention. In this way, inappropriate data such as errors, omissions, and duplication can be visualized (output) by a statistical method (totaling). That is, detection and correction of inappropriate data can be supported.

  Further, the classification based on the predetermined rule may be a classification based on an expression format that describes an element. In this way, the user can easily find the presence of an inappropriate element that can be registered in input data such as a DSV format in which no schema is defined. Specifically, the classification based on a predetermined rule includes whether an element represents a date, whether it is unique, whether it is empty, whether it is a numerical value, or a predetermined character string. It may be performed depending on whether or not, or for each element or each element data pattern.

  Further, based on the data indicating the correspondence between the items in the plurality of tabular data, the apparatus further includes a vertical combination unit that generates a union of the plurality of tabular data. You may make it input data. In this way, the data profiling process can be performed on the tabular data after the vertical combination in the present invention.

  The contents of the means for solving the above problems can be combined as much as possible without departing from the problems and technical ideas of the present invention. Further, there is provided a tabular data processing method for causing a computer to execute the contents of the means for solving the above problems, and a tabular data processing program for causing a computer to execute the steps for executing the contents of the means for solving the above problems. You can also. Further, a recording medium holding the program may be provided.

  ADVANTAGE OF THE INVENTION According to this invention, the technique for supporting the operation of the tabular data expressed by the delimiter (Delimiter) delimiter can be provided.

It is a functional block diagram showing an example of a system configuration. It is a functional block diagram showing an example of a tabular data processing device. FIG. 3 is a functional block diagram illustrating an example of a device configuration of a computer. It is a process flow which shows an example of a file reading process. FIG. 4 is a diagram for explaining tabular data (CSV file). FIG. 4 is a diagram for explaining a row list of tabular data. FIG. 3 is a diagram for explaining a key-value format map. It is a process flow which shows an example of a vertical combination setting process. FIG. 9 is a diagram for describing setting data of vertical combination. FIG. 9 is a diagram for describing setting data of vertical combination. It is a process flow which shows an example of the list generation process according to item. FIG. 4 is a diagram illustrating an example of an object for generating an itemized list. It is a figure for explaining an itemized list. It is a figure for explaining an itemized list. It is a process flow which shows an example of a vertical combination process. It is a figure for explaining the itemized list generated in the middle. It is a figure for explaining the itemized list generated in the middle. It is a figure for explaining the itemized list after longitudinal combination. It is a processing flow showing an example of output processing. It is a figure for explaining a list according to a line. FIG. 7 is a diagram for explaining a row list of tabular data to be output. FIG. 9 is a diagram for describing tabular data (CSV file) to be output. It is a process flow which shows an example of a horizontal coupling setting process. FIG. 9 is a diagram for describing a screen for setting a condition of horizontal coupling. FIG. 9 is a diagram for describing a screen for setting a condition of horizontal coupling. FIG. 9 is a diagram for describing a screen for setting a condition of horizontal coupling. It is a process flow which shows an example of a total setting process. It is a figure for explaining the screen which sets up the conditions of total processing. It is a figure for explaining the screen which sets up the conditions of total processing. It is a figure for explaining the screen which sets up the conditions of total processing. It is a figure for explaining the screen which sets up the conditions of total processing. It is a figure for explaining the screen which sets up the conditions of total processing. It is a figure for explaining the screen which sets up the conditions of total processing. It is a processing flow for explaining an example of control group generation processing. It is a processing flow for explaining an example of control group generation processing. It is a figure for explaining a control group. It is a figure for explaining a control group. It is a figure for explaining sorting. It is a figure for explaining sorting. It is a figure for explaining sorting. It is a figure for explaining sorting. It is a process flow for demonstrating an example of a horizontal joining and totaling process. FIG. 14 is a diagram for describing a screen for setting a condition of a new attribute. FIG. 14 is a diagram for describing a screen for setting a condition of a new attribute. FIG. 14 is a diagram for describing a screen for setting a condition of a new attribute. It is a process flow which shows an example of a new item generation process. It is a figure for explaining totaling processing. It is a figure for explaining totaling processing. It is a functional block diagram showing an example of a tabular data processing device. FIG. 4 is a diagram for describing an outline of a job according to the embodiment. It is a table | surface which shows an example of the input data of a vertical combination process. It is a table | surface which shows an example of the input data of a vertical combination process. It is a table | surface which shows an example of the output data of a vertical combination process. It is a figure showing an example of a setting screen of a vertical combination (vertical combination column edit screen). It is a figure showing an example of a setting screen of a vertical combination (vertical combination base file selection screen). It is a figure showing an example of a setting screen (display attribute extraction setting screen) of vertical combination. It is a table | surface which shows an example of the input data of a horizontal connection (output data of a vertical connection). It is a table | surface which shows an example of the input data of a vertical combination. It is a table | surface which shows an example of the input data of a vertical combination. It is a table | surface which shows an example of the output data of a horizontal connection. It is a figure showing an example of a setting screen (horizontal combination screen) of horizontal combination. FIG. 9 is a diagram illustrating an example of a horizontal combination setting screen (horizontal combination key setting screen). It is a table | surface which shows an example of the input data of a horizontal connection. It is a table | surface which shows the example of an output of a horizontal connection. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a table | surface which shows the other output examples of a horizontal coupling. It is a figure showing the example of a description of a user dictionary. FIG. 4 is a schematic diagram for explaining the relationship between words and phrases in a user dictionary. It is a table | surface which shows the description example of another user dictionary. It is a table | surface which shows the description example of another user dictionary. It is a table | surface which shows an example of the output data of a totaling process. FIG. 9 is a diagram illustrating an example of a setting screen (totaling screen) of a totaling process. FIG. 11 is a diagram illustrating an example of a setting screen of a tallying process (tallying key setting screen). FIG. 9 is a diagram illustrating an example of a setting screen (total attribute setting screen) of a totaling process. FIG. 11 is a diagram illustrating an example of a setting screen (total addition attribute screen) of a totaling process. It is a figure showing an example of a setting screen (calculation screen) of a totaling process. It is a figure showing an example of a setting screen (condition screen) of a totaling process. It is a figure showing an example of a setting screen (function processing screen) of a totaling process. It is a table | surface which shows an example of the input data of a grouping process. It is a table | surface which shows an example of the output data of a grouping process. It is a figure showing an example of a setting screen (grouping screen) of grouping processing. It is a table | surface which shows an example of the output data of a grouping release process. It is a figure showing an example of a setting screen (grouping key setting screen) of grouping release processing. It is a figure showing an example of a setting screen of a numbering process. It is a figure showing an example of a setting screen of a search condition. FIG. 4 is a diagram illustrating an example of the content of an XML file. FIG. 9 is a diagram for explaining an outline of batch processing. FIG. 7 is a diagram illustrating an example of a setting screen for batch processing. FIG. 14 is a process flowchart illustrating an example of a new job execution process. FIG. 14 is a diagram illustrating an example of a job setting screen. FIG. 14 is a diagram illustrating another example of a job setting screen. FIG. 14 is a diagram illustrating another example of a job setting screen. FIG. 14 is a diagram illustrating another example of a job setting screen. FIG. 14 is a diagram illustrating another example of a job setting screen. It is a process flow figure showing an example of batch definition processing. It is a figure showing an example of a setting screen of combination processing. It is a functional block diagram showing an example of a tabular data processing device. 9 is a process flow illustrating an example of a data profiling process. It is a figure showing an example of a data quality tool setting screen. It is a figure showing an example of a data quality tool screen. FIG. 7 is a diagram illustrating an example of a data profiling setting screen. FIG. 6 is a diagram illustrating an example of a data profiling screen.

  Hereinafter, a tabular data processing program, a tabular data processing device, and a tabular data processing method according to an embodiment will be described with reference to the drawings. The configuration of the embodiment is an exemplification, and the program, apparatus, and method according to the present invention are not limited to the configuration of the embodiment.

<<< Embodiment of Internal Processing and User Interface >>>
<System configuration>
FIG. 1 is a functional block diagram showing an example of a system for processing tabular data. The system according to the present embodiment includes a tabular data processing device 1 and a user terminal 2. The tabular data processing device 1 and the user terminal 2 are connected via a network 3.

  The tabular data processing device 1 is, for example, a server that performs processing described in the present embodiment on tabular data. The user terminal 2 is, for example, a computer having a function of connecting to the Internet. The user, for example, instructs the tabular data processing device 1 to execute the processing described in the present embodiment via the Internet browser of the user terminal 2 or checks the result of the processing received from the tabular data processing device 1 Or you can. The network 3 is, for example, a network system such as the Internet or an intranet. Note that the tabular data processing device 1 and the user terminal 2 may be connected by a dedicated line. Although FIG. 1 shows one user terminal 2, a plurality of user terminals may be connected. On the other hand, a plurality of tabular data processing apparatuses 1 may exist on a network and perform processing in a distributed manner.

<Functional configuration>
FIG. 2 is a functional block diagram illustrating an example of the tabular data processing device 1. The tabular data processing device 1 according to the present embodiment includes a data storage unit 101, a setting processing unit 102, a data division unit 103, a display control unit 104, a vertical combination unit 105, a control group generation unit 106, It has a horizontal combining unit 107, a totaling unit 108, and an output data generating unit 109.

  The data storage unit 101 stores, for example, tabular data to be processed in the present embodiment, setting data indicating a request for processing, tabular data generated as a processing result, and other data generated intermediately in processing. Hold. In the tabular data processing device 1, for example, fields (also called “columns”, “items”, “elements” or “attributes”) are separated by delimiters (also called “delimiters”) such as commas, tabs, and spaces. Text data in which records (also referred to as “lines”) are separated by line feeds are to be processed. In the present embodiment, it is assumed that CSV data transferred by the user is stored in the data storage unit 101 of the tabular data processing device 1 in advance.

  The setting processing unit 102 receives, for example, a specification of a plurality of tabular data to be combined, a condition for aggregating records, and a request for a data operation such as a method for aggregating from the user terminal 2 via the network 3. 101 is stored. The data dividing unit 103 divides the tabular data by record (by row) or by item (by column) and stores the data in the data storage unit 101. The display control unit 104 generates a GUI (Graphical User Interface) for the user to input a data operation request, and generates a screen for displaying the result.

  The vertical combining unit 105 performs, for example, a process (also referred to as “vertical combining process”) of integrating records included in a plurality of tabular data into one tabular data. The control group generation unit 106 performs a process of generating a control group indicating records to be aggregated as a set of row numbers (also referred to as “record numbers”). The line number is, for example, a serial number added from the beginning for each line of CSV data, and a record can be uniquely specified in tabular data by the line number. The horizontal combining unit 107 performs a process of combining a plurality of records of tabular data into one record in which the value of a key item satisfies a predetermined condition (also referred to as “horizontal combining process”). The tabulation unit 108 performs a process of tabulating a value as a numerical value or integrating the value as a character string into one field based on the settings stored in the data storage unit 101. The output data generation unit 109 generates line-by-line data from the combined item-by-item data and outputs the data as CSV data.

<Apparatus configuration>
FIG. 3 is an apparatus configuration diagram illustrating an example of a computer. The tabular data processing device 1 and the user terminal 2 are computers as shown in FIG. The computer 1000 shown in FIG. 3 includes a CPU (Central Processing Unit) 1001, a main storage device 1002, an auxiliary storage device 1003, a communication IF (Interface) 1004, an input / output IF (Interface) 1005, a drive device 1006, and a communication bus 1007. ing. The CPU 1001 performs processing and the like according to this embodiment by executing a program (also referred to as “software” or “application”). The main storage device 1002 caches programs and data read by the CPU 1001, and expands a work area of the CPU. The main storage device is, specifically, a random access memory (RAM) or a read only memory (ROM). The auxiliary storage device 1003 stores a program executed by the CPU 1001, setting information used in the present embodiment, and the like. The auxiliary storage device 1003 is, specifically, a hard disk drive (HDD), a solid state drive (SSD), a flash memory, or the like. The main storage device 1002 and the auxiliary storage device 1003 function as the data storage unit 101 of the tabular data processing device 1. The communication IF 1004 transmits and receives data to and from another computer. The tabular data processing device 1 and the user terminal 2 are connected to the network 3 via the communication IF 1004. The communication IF 1004 is, specifically, a wired or wireless network card or the like. The input / output IF 1005 is connected to the input / output device, and accepts input from a user and outputs information to the user. The input / output device is, specifically, a keyboard, a mouse, a display, a touch panel, or the like. The drive device 1006 reads data recorded on a storage medium such as a magnetic disk, a magneto-optical disk, and an optical disk, and writes data on the storage medium. The above components are connected by a communication bus 1007. A plurality of these components may be provided, or some of the components (for example, the drive device 1006) may not be provided. Further, the input / output device may be configured integrally with the computer. Also, a program executed in the present embodiment is provided via a portable storage medium readable by the drive device 1006, a portable auxiliary storage device 1003 such as a flash memory, a communication IF 1004, and the like. It may be. When the CPU 1001 executes the program, the above-described computer operates as the tabular data processing device 1 shown in FIG. Further, the user terminal 2 requests processing to the tabular data processing device 1 via an Internet browser executed by the CPU 1001 or displays a received processing result, for example.

<File reading process>
Next, processing executed by the tabular data processing device 1 will be described using a processing flow. FIG. 4 is a processing flow showing an example of the file reading processing. First, the setting processing unit 102 of the tabular data processing device 1 receives an input of a CSV file (FIG. 4: step S1). Specifically, the setting processing unit 102 receives the CSV file from the user terminal 2 and stores the CSV file in the data storage unit 101. For example, a CSV file as shown in FIG. 5 is input.

  FIG. 5A is a diagram for describing processing of tabular data according to the present embodiment. The CSV file in FIG. 5A has a plurality of item names separated by commas on the first line, and a plurality of records including a plurality of fields separated by commas on the second and subsequent lines. The item names and fields separated by commas correspond in the order separated by commas, and one CSV file has the same number of item names and fields of each record.

  Further, the setting processing unit 102 receives an input of an item name and a start line of a record (S2). Specifically, the user specifies, via the user terminal 2, a line in which an item name is registered for each CSV file and a start position of a line in which a record is registered. On the other hand, the setting processing unit 102 causes the data storage unit 101 to store the line where the item name is registered and the start position of the line where the record is registered in association with the CSV file. In the example of FIG. 5A, the first line is the start position of the item name, and the second line is the start position of the record.

  After that, the data division unit 103 generates a list including each line of the CSV file as an element (S3). Specifically, the data division unit 103 reads, for example, one line at a time from the head of the CSV file, and adds the new elements to the list in order from the first line. Here, for example, data as shown in the row list of the tabular data shown in FIG. 5B is generated. In the row list of FIG. 5B, each element of the list is separated by a solid line, and each record of CSV data is registered in each element.

  Further, the data division unit 103 stores the generated row list of the tabular data in the data storage unit 101 in association with the file name of the tabular data (S4). For example, a key (key) may be used as a file name, and a row list may be registered in a value (value), and the data may be stored in a key-value format data structure (so-called map).

  Then, for example, the setting processing unit 102 determines whether there is an unprocessed CSV file (S5). If there is an unprocessed CSV file (S5: YES), the process returns to S1. On the other hand, if there is no unprocessed CSV file (S5: NO), the file reading process ends. In the present embodiment, tabular data is combined or tabulated using one or a plurality of CSV files read in the file reading process.

<Vertical combination setting process>
Next, a vertical combination process for generating a union of records included in a plurality of tabular data will be described. FIG. 6 is a processing flow illustrating an example of the vertical combination setting processing.

  First, the setting processing unit 102 receives an input of a corresponding item from among the items included in the plurality of tabular data from the user terminal 2 (FIG. 6: S11). The user designates a plurality of tabular data to be combined, and designates a correspondence between items included in the combined tabular data and displayed in the same column (item) in the combined tabular data. Here, items indicating the same entity are associated with each other. For example, the correspondence between the item “name” in one CSV file and the item “name” in another CSV file is input.

  In this step, a plurality of items included in one tabular data may be combined and treated as one item. The setting processing unit 102 receives an input of association between a combination of a plurality of fields included in certain tabular data and a combination of one field or a plurality of fields included in another tabular data. For example, a combination of an item of “last name” and an item of “name” in a certain CSV file and an association with an item of “name” in another CSV file are input.

  FIG. 7A is a diagram for explaining the association input in this step. The first line of the table shown in FIG. 7A indicates the serial number of the item. The second line of the table shows the output item names of the tabular data after the vertical combination. It is assumed that the item names of the tabulated data after the combination are input by the user. The third and subsequent lines indicate item names in different CSV files. The item names registered in the same column indicate the item names associated in this step. For example, the combination of the items "Last name" and "Name" in the CSV file "AAA" shown on the fourth line is associated with the item "Name" in the CSV file "BBB" shown on the fifth line. The line with the CSV file name “common” is a setting that is commonly applied to unregistered CSV files (in the example of FIG. 7A, CSV files other than “AAA” and “BBB”).

  Thus, when there is a corresponding item, it is associated in this step by the operation of the user. Note that a plurality of tabular data items do not always have corresponding items. In this step, the corresponding items are described as being input by the user. However, for example, the same item names included in different tabular data may be presented in advance in association with each other so that the user can appropriately correct the items. .

  Next, the setting processing unit 102 causes the data storage unit 101 to store the input vertical combination setting data (S12). The setting processing unit 102, for example, associates a plurality of CSV file names and item names corresponding to the output item names with the output item names after the vertical combination based on the item association described with reference to FIG. 7A. And register it. For example, a list of output item names after vertical combination as shown in FIG. 7B is generated and stored in the data storage unit 101.

  In the example of FIG. 7B, CSV file names and item names are registered in a nested manner in elements representing output items separated by solid lines. Note that an item name with an empty CSV file name (nothing is registered in the CSV file name) indicates that the item name is not limited to a specific CSV file but is associated with an output item. Thus, the vertical combination setting process ends. The vertical combination setting process is executed in advance for a combination of CSV files for performing vertical combination.

<Item-specific list generation processing>
Next, an item-specific list generation process executed as a pre-process such as the vertical combination process will be described. FIG. 8 is a processing flow illustrating an example of the item-specific list generation processing.

  First, the data division unit 103 acquires one row list of tabular data to be processed (FIG. 8: S21). Here, the data division unit 103 reads, for example, one row list of the CSV file to be combined from the data storage unit 101.

  Next, the data division unit 103 counts the number of item names registered in the line of the item name (the first line in the example of FIG. 5B) from the line list, separated by commas (S22). As the item name and the start line of the record, the information specified in S2 of FIG. In this step, the data division unit 103 calculates the number of columns (item names) included in the tabular data to be processed.

  After that, the data division unit 103 generates and initializes item-specific lists by the number of counted item names (S23). For example, a class for registering a value in the item-specific list may be defined, and objects as shown in FIG. 9 may be generated by the number of item names. In the object of FIG. 9, a method (function) for adding a value or adding an item name is defined. In this step, an item name is added to the item-specific list. As a result, the itemized list can be identified by the item name. The itemized list may be identified by a serial number indicating the order in the tabular data, for example. The object in FIG. 9 is an example, and the present embodiment is not limited to a specific programming language or the like as long as a list holding elements for each item of tabular data can be generated.

  The data division unit 103 reads one registered element of the record from the row list of the tabular data (S24). For example, the element of the second row is read from the row list shown in FIG. 5B. Here, data (that is, a record) corresponding to one line of the CSV file is read.

  Next, the data division unit 103 decomposes a plurality of values separated by commas from the read data and adds them to a plurality of item-specific lists (S25). Here, the data division unit 103 registers the values corresponding to the plurality of items included in one line of the CSV file as new elements in each of the item-specific lists generated by the number of items.

  Then, the data division unit 103 determines whether an unprocessed element exists in the row list (S26). If an unprocessed element exists (S26: YES), the process returns to S24. On the other hand, when there is no unprocessed element (S26: NO), the data division unit 103 determines whether an unprocessed element exists in the row list of the tabular data (S27). If there is an unprocessed row list (S27: YES), the data division unit 103 returns to the processing of S21. On the other hand, when there is no unprocessed row list (S27: NO), the data division unit 103 ends the item-specific list generation processing.

  In the item-specific list generation processing, a list is generated for each item (column) included in the tabular data, in which the values of the records (rows) are sequentially held as elements from the first row. In the case of the row list shown in FIG. 5B, for example, an itemized list as shown in FIG. 10A is generated. FIG. 10A shows six item-specific lists corresponding to the item names “No.”, “ID”, “L_Name”, “F_Name”, “Sex”, “Station”, and “Expense”. . In each itemized list, values are held as elements in the order corresponding to the order of records in tabular data.

<Vertical combination processing>
Next, a vertical combination process for generating a union of records included in a plurality of tabular data will be described. In this embodiment, it is assumed that the itemized list shown in FIG. 10A and the itemized list shown in FIG. 10B generated from other tabular data are stored in the data storage unit 101 in advance. FIG. 11 is a processing flow illustrating an example of the vertical combination processing.

  First, the vertical combination unit 105 specifies one tabular data to be combined based on the vertical combination setting data (FIG. 11: S31). Further, the vertical combination unit 105 generates and initializes an output-specific list for the number of items to be output, based on the vertical combination setting data (S32). For example, the vertical combination unit 105 reads the setting data shown in FIG. 7B and generates an item-specific list for output by the number of elements. The generation and initialization of the list for each item are the same processing as S23 of FIG.

  In addition, the vertical combination unit 105 acquires one output item from the setting data and specifies the item name (S33). In addition, the vertical combination unit 105 determines whether an item corresponding to the output item acquired in S33 exists in the itemized list of the tabular data specified in S31 (S34).

  If a corresponding item exists (S34: YES), the vertical combination unit 105 registers the itemized list acquired in S31 in the corresponding itemized list for output (S35). Here, when a plurality of item names are combined with “+” in the vertical combination setting data (for example, FIG. 7B), the elements of the plurality of item list are combined into one item list for output. To register. On the other hand, when the corresponding item does not exist (S34: NO), an item-by-output list in which "empty" is registered as an element by the number of records is registered (S36). Since the item corresponding to the output item does not exist in the file to be processed, an empty item-specific list is added here. At this time, the values in each element are connected by a predetermined delimiter (for example, comma or space).

  Thereafter, the vertical combination unit 105 determines whether there is an unprocessed itemized list in the tabular data to be processed (S37). If there is an unprocessed list by item (S37: YES), the process returns to S33. On the other hand, when there is no unprocessed itemized list (S37: NO), the vertical combination unit 105 determines whether there is any unprocessed tabular data in the combined tabular data (S38). If there is unprocessed tabular data (S38: YES), the process returns to S31. On the other hand, when there is no unprocessed tabular data (S38: NO), the process proceeds to S39. If there is no item-specific data corresponding to the combined items, an output item-specific list may be generated in which “empty” is registered as an element by the number of records.

  By the processing up to S37, for example, an itemized list for output as shown in FIG. 12A is generated from the itemized list shown in FIG. 10A. In the example of FIG. 12A, based on the setting data of the vertical combination, the elements “L_Name” and “F_Name” of the itemized list to be combined are combined with “Name” of the itemized list for output in a space-separated manner. It is registered. In addition, “Office”, which does not exist in the combined itemized list, is generated as the itemized list for output. In the itemized list of “Office”, “empty” is registered for the number of records. Further, through the processing up to S38, an output itemized list as shown in FIG. 12A is generated for the combined tabular data. For example, an itemized list for output as shown in FIG. 12B is generated from the itemized list shown in FIG. 10B. In the example of FIG. 12B, of the two tabular data items to be combined, the combination of the items “L_Name” and “F_Name” shown in FIG. 10A and the item “Name” shown in FIG. Are grouped into two items "Name".

  Thereafter, the vertical combining unit 105 combines the generated output-specific lists for each item (S39). The output itemized lists (FIGS. 12A and 12B) generated from the plurality of tabulated data to be combined are combined as shown in FIG. 12C, for example. In FIG. 12C, a union of the elements of one itemized list and the elements of the other itemized list is generated. At this time, the corresponding item is registered in one item-specific list (“Name” in FIG. 12C) based on the setting data of the vertical combination. Thus, the vertical combining process ends.

<Output processing>
Next, a process of outputting the combined itemized data as tabular data will be described. FIG. 13 is a processing flow illustrating an example of the output processing.

  The output data generation unit 109 reads the combined output-specific list and generates an output-specific list (FIG. 13: S41). For example, first, a line-by-line list in which the value of each record is registered as an element is generated and initialized by the number of records. Next, the itemized list for output is read, and each element is registered as a new element in the line-by-line list corresponding to the order of records (that is, the line number). Such processing is performed for all of the output-specific lists, and a line-by-line list is generated. For example, a line-by-line list as shown in FIG. 14A is generated from the item-by-item list shown in FIG. 12C. In the example of FIG. 14A, each element is surrounded by a solid line, and elements adjacent in the horizontal direction represent a line-by-line list.

  The output data generation unit 109 generates a line list of tabular data to be output using the generated line-by-line list for output (S42). The elements of the generated line list are values obtained by adding a separator between elements included in the output line-by-line list and connecting them to one line. When outputting a CSV file, the output data generation unit 109 adds a comma as a delimiter between elements. For example, a line list as shown in FIG. 14B is generated from a line-by-line list as shown in FIG. 14A.

  Next, the output data generation unit 109 generates tabular data using the generated row list (S43). The tabular data generated in this step is text data in which fields are separated by delimiters such as commas and records are separated by line feeds. Here, the output data generation unit 109 combines each element of the line list generated in S42 as one line and combines them into one file. For example, a CSV file as shown in FIG. 14C is generated from the row list shown in FIG. 14B. In addition to the CSV file, a file that can be read by existing spreadsheet software may be generated.

  As described above, according to the file reading processing, the vertical combination setting processing, the itemized list generation processing, and the vertical combination processing, it is possible to generate a union of tabular data having different data items. Further, for example, when the user designates the correspondence of the items in advance, the related items can be arranged in the same column in the combined tabular data (that is, they can be aggregated). Such processing can be efficiently performed by using the item-by-item data generated intermediately in the present embodiment.

<Horizontal connection setting process>
Next, the horizontal joining process and the tallying process will be described. The horizontal combination process according to the present embodiment is a process of integrating a plurality of records into a single record in which the value of a key item satisfies a predetermined condition. The tallying process according to the present embodiment is a process of tallying a plurality of records in which the value of a key item satisfies a predetermined condition. First, a horizontal combination setting process for setting key items used in the horizontal combination process will be described.

  FIG. 15 is a processing flow illustrating an example of the horizontal coupling setting processing. In the present embodiment, a process of integrating a plurality of records into one record is performed using the tabular data after the vertical combination described above. Therefore, for example, it is assumed that the setting data of the vertical combination as shown in FIG. 7A is stored in the data storage unit 101 in advance.

  First, the display control unit 104 generates a list of output items, transmits the list to the user terminal 2, and causes the display device of the user terminal 2 to display the list (FIG. 15: S51). In this step, the display control unit 104 reads output items from the setting data of the vertical combination, and generates a list for the user to specify key items. For example, each item is displayed in the form of a panel so that the user can select a key item by clicking or dragging and dropping it to a predetermined area.

  FIG. 16 shows an example of the screen. The screen of FIG. 16 includes an item list display area 2001 for displaying a list of items, a key item display area 2002 for displaying a list of designated key items, and an overlap for selecting a process of a duplicate value. A value processing selection area 2003 and a delimiter selection area 2004 for selecting a delimiter when combining and displaying values in the same field are included. In the item list display area 2001, a list of items of the tabular data after the vertical combination is displayed in a panel shape for each item.

  Further, the setting processing unit 102 receives designation of a key item from the user terminal 2 (S52). In this step, when the user performs a predetermined operation on the panel of the item on the screen, the setting processing unit 102 receives the designation of the key item. The predetermined operation is, for example, dragging the panel to the key item display area or clicking the panel. For example, on the screen shown in FIG. 16, each panel in the item list display area 2001 can be dragged and dropped, and when the panel is dropped in the key item display area 2002, the setting processing unit 102 displays the item corresponding to the panel. Accept as a key item. In addition, the display control unit 104 moves the display of the panel of the dragged item on the screen to the key item display area.

  A plurality of key items may be specified. At this time, in the key item display area 2002, panels are added in the order specified from top to bottom. Note that the direction in which the panel is added is not limited to the direction from top to bottom, and may be another predetermined direction. Further, the order of panels in the key item display area may be changed by dragging by the user. Here, the order of the key items indicates the priority when the combined records are put together. That is, the combined records can be sorted by a plurality of keys based on the order of the key items, for example, such that records having the same upper key item value are sorted by lower key item values.

  In the selected key item, a condition for collecting records is set. That is, for example, conditions for performing name identification such as “perfect match”, “front match”, “backward match”, and “intermediate match” are set. Furthermore, whether to perform data cleansing (standardization) on the key item may be designated.

  FIG. 17 is an example of a screen for setting key items. The screen of FIG. 17 includes an item name of a key item, an option 3001 of “judgment determination”, an option 3002 of whether to perform “data cleansing process”, and a “register” button 3003 for registering a setting. Is displayed. For example, by clicking on a panel displayed in the key item list in FIG. 16, a setting screen for key items indicated by the panel is displayed as shown in FIG.

  In the option 3001 of “judgment determination”, conditions for performing name identification are displayed as options. In the present embodiment, “name identification” refers to a process of collecting records related to the same entity included in tabular data. In the present embodiment, records in which at least some of the values of the key items match are aggregated. By performing name identification, for example, records related to the same person can be specified, and the counting result becomes more accurate.

  The “data cleansing process” is a process for standardizing the description format of the value of the key item. For example, a value indicating a telephone number may be registered in a different description format such as “01-2345-6789”, “01 (2345) 6789”, “0123456789”, and the like. By standardizing such values, it is possible to perform more accurate name identification. Absorb the differences in the description format by performing data cleansing on the postal code, date, corporate name, numerical value (whether there is a digit separator, whether there is a unit, etc.), name variant, address, etc. it can. Existing technology can be applied to the data cleansing process.

  By name matching using data cleansing, for example, (1) one of “Saito”, “Saito”, “Saito”, and “Saito” described in variant characters, and (2) “Chiyoda, Tokyo” having a different description format Iidabashi 99-99-99 XX Heights Room 101, Ward, 99-99-99-101, Iidabashi, Chiyoda Ward, Tokyo 99-99-99, Iidabashi 99-999, XX Heights Room 101, Iidabashi 99, Chiyoda Ward, Tokyo -99-99-101 ", and (3) any one of code numbers" 10000523 "," 1,000,523 "," 1 million five hundred twenty-three "having different description formats, etc. Records that include various combinations of (1) to (3) that satisfy the condition can be handled as records relating to the same person.

  When the setting of the key item is completed, the setting processing unit 102 updates the display of the key item display area 2002 (S53). Here, the panel of the key item is changed to a predetermined display mode indicating that the specification of the condition has been completed. Examples of the display mode include a background color, a character color, a font, a size, and a change pattern thereof (blinking of the background color). For example, when the specification of the condition is completed for the key item shown in FIG. 16, the color of the panel of the key item is changed as shown in FIG. In the screen of FIG. 18, the color of the panel for which the conditions have been specified has been changed to the same color as the background color of the title of “key item”. In FIGS. 16 and 18, colors are represented by hatching patterns for convenience. It is assumed that areas painted with the same pattern are painted with the same color.

  Thereafter, the setting processing unit 102 determines whether the input of the key item has been completed (S54). For example, when the “data output” button in FIG. 18 is pressed by a user operation, the setting processing unit 102 determines that the input of the key item has been completed. If the input has not been completed (S54: NO), the process returns to, for example, S52, and the setting processing unit 102 further receives designation of a key item. On the other hand, when the input is completed (S54: YES), the setting processing unit 102 stores the setting in the data storage unit 101, and ends the horizontal connection setting process (S55). In this step, “key item” and “name identification condition” are set as the setting data of the horizontal combination. Furthermore, “necessity of data cleansing processing”, “processing of duplicate values”, “type of delimiter”, and the like may be set.

<Total setting process>
Next, a tally setting process for setting key items and tally items used in the tally process will be described. FIG. 19 is a process flow illustrating an example of the tally setting process. In the present embodiment, the values of the records are totaled using the tabular data after the vertical combination. Therefore, it is assumed that the vertical combination setting data as shown in FIG. 7A is stored in the data storage unit 101 in advance.

  First, the display control unit 104 generates a list of output items, transmits the list to the user terminal 2, and causes the display device of the user terminal 2 to display the list of output items (FIG. 19: S61). The process of this step is the same as S51 of FIG.

  FIG. 20 shows an example of the screen. The screen of FIG. 20 includes an item list display area 4001 for displaying a list of items, a new item panel 4002 for adding a new item to the tabulated data to be output, and a list of designated key items. A key item display area 4003 for display and a tally item display area 4004 for displaying a list of tally items are included. In the item list display area, a list of items of the tabular data after the vertical combination is displayed in a panel shape for each item.

  Further, the setting processing unit 102 receives designation of a key item from the user terminal 2 (S62). This step is substantially the same as S52 in FIG. 15, and when the user performs a predetermined operation on the panel of items on the screen, the setting processing unit 102 receives designation of a key item. In addition, in the tabulation setting process, a key item tabulation method is selected as a condition of a record to be tabulated. The tallying method is specified by the user from options such as “perfect match”, “by year”, “by month”, “by day”, and “by day of week”.

  FIG. 21 is an example of a screen for setting key items. The screen of FIG. 20 displays an item name 5001 of a key item and an option 5002 of “key item aggregation method”. For example, by clicking a panel displayed in the key item list in FIG. 20, a setting screen for key items indicated by the panel is displayed as shown in FIG.

  When “exact match” is selected, records in which the values of the key items completely match are aggregated, and the value of “total item” described later is obtained. Further, options such as “by year”, “by month”, “by day”, and “by day of the week” are effective when the key item indicates a date. Aggregation processing is performed for each predetermined period.

  Further, when the setting of the key item is completed, the setting processing unit 102 updates the display of the key item display area (S63). This step is the same as S53 in FIG. 15, and the key item panel is changed to a predetermined display mode indicating that the designation of the condition has been completed. For example, when the specification of the condition is completed for the key item shown in FIG. 20, the color of the key item panel is changed as shown in FIG. 20 and 22, the colors are represented by hatching patterns for convenience. Note that a plurality of key items may be specified. The process in the case of receiving a plurality of key items is the same as that in S54 (FIG. 15).

  Further, the setting processing unit 102 receives designation of a tally item (S64). Here, when the user performs a predetermined operation on the panel of the item on the screen, the setting processing unit 102 accepts the designation of the total item. For example, in the screen shown in FIG. 22, each panel in the item list display area 4001 can be dragged and dropped, and when the panel is dropped in the tally item display area 4004, the setting processing unit 102 receives the panel as a tally item. To do. Further, for example, as illustrated in FIG. 23, the display control unit 104 moves the panel of the dragged item on the screen to the total item display area 4004. Further, an item that does not exist in the tabular data after the vertical combination can be newly generated as a total item. In this case, the user drags, for example, the “new” panel 4002 existing at the lower left of FIG. The new items will be described later in detail.

  Also, a plurality of total items may be designated. At this time, panels are added from the top to the bottom in the specified order in the total item display area 4004. Note that the direction in which the panel is added is not limited to the direction from top to bottom, and may be another predetermined direction. Further, the order of panels in the tally item display area 4004 may be changed by dragging by the user. Here, in the present embodiment, the counting process is performed based on the order of the counting items. Therefore, in the lower-order tally item, the tallying process can be performed using the result of the higher-order tally item.

  In addition, a totaling method of the item is set in the totaling item. For example, "combination" to generate a character string in which values are combined with a delimiter, "character aggregation" such as "frequency" to count the number of occurrences of a value, or "total", "average", "median", " “Numerical aggregation (summary statistics)” such as “mode”, “maximum”, “minimum”, “variance”, and “standard deviation” is selected by the user.

  FIG. 24 is an example of a screen for setting tally items. The screen of FIG. 24 displays an item name 6001 of a total item and an option 6002 of “item totaling method”. For example, by clicking a panel displayed in the tally item display area in FIG. 23, a setting screen for key items indicated by the panel is displayed as shown in FIG.

  Further, when the setting of the tally item is completed, the setting processing unit 102 updates the display of the tally item display area (S65). This step is the same as that in S63, and the panel of the tally item is changed to a predetermined display mode indicating that the designation of the condition has been completed. For example, when the specification of the condition is completed for the key item shown in FIG. 23, the color of the panel of the total item is changed as shown in FIG. 23 and 25, the colors are represented by hatching patterns for convenience. The specification of the total item as described above may be plurally received.

  Thereafter, the setting processing unit 102 determines whether the input of the key item and the total item has been completed (S66). For example, when the “data output” button in FIG. 25 is pressed by a user operation, the setting processing unit 102 determines that the input of the key item has been completed. If the input has not been completed (S66: NO), the process returns to, for example, the process of S62, and the setting processing unit 102 further receives designation of a key item. Returning to the process of S64, the setting processing unit 102 may receive designation of a tally item. In the processing flow of FIG. 19, the specification of key items and the display update of the key item display area (S62 and S63) and the specification of total items and the display update of the total item display area (S64 and S65) are conveniently continued. Although described as processing, they are independent processing. Therefore, the execution is performed an arbitrary number of times in an arbitrary order based on a user operation. On the other hand, if the input has been completed (S66: YES), the setting processing unit 102 stores the settings in the data storage unit 101 (S67), and ends the tally setting process. In S67, for example, “key item” and “key item aggregation method”, and “total item” and “item aggregation method” are set as the setting data of the aggregation process.

<Control group generation processing>
When performing the horizontal combining process or the totalizing process, first, as described above, the vertical combining process is performed. That is, the union of a plurality of tabular data as shown in FIG. Also, in the horizontal joining process or the tabulation process, the item-specific list generation process shown in FIG. 8 is performed on the tabular data to be processed to generate the item-specific list. The item-specific list generation processing is as shown in FIG. For example, an itemized list as shown in FIG. 12C is generated from the tabular data shown in FIG. 14C.

  In the process of the horizontal joining process and the aggregation process, a “control group” which is data for specifying records to be aggregated using the itemized list is generated in the middle. The control group according to the present embodiment is a record (row) in which the value of a key item satisfies a predetermined condition is represented by a set of row numbers. In the control group generation processing, for example, a set of line numbers of records in which the values of key items completely match or a set of line numbers of records in which the values of the key items described in the date format belong within a predetermined period are generated. You. Then, the tabular data processing device 1 aggregates the records using the control group in a horizontal joining process or a totaling process described later.

  FIGS. 26 and 27 are processing flows illustrating an example of the control group generation processing. The control group generation unit 106 reads the setting data of the horizontal connection or the setting data of the tallying process from the data storage unit 101, and acquires one itemized list of key items (FIG. 26: S71). For example, when “Name” is specified as a key item in the tabular data shown in FIG. 14C, the third item from the left (item name: Name) in the item list shown in FIG. The list is obtained.

  In addition, the control group generation unit 106 generates a key-value format map that holds a value and a list of line numbers in association with each other (S72). In the key, the value of the key item is registered, and in the value, a list holding the row number as an element is registered.

  Next, the control group generation processing unit 106 performs a cleansing process (S73). This step is performed, for example, when the setting data of the horizontal coupling or the setting data of the aggregation processing specifies that the cleansing processing is to be performed. In this step, the description format of the values is changed and unified for the telephone number, postal code, date, corporation name, numerical value (whether there is a separator, whether there is a unit, etc.), the variant of the name, and the address. As a result, the accuracy of the process of collecting records, such as merging, is improved.

  Further, the control group generation unit 106 acquires one element from the itemized list of key items (S74). In this step, the values of the key items are read one by one in the record registration order (row number order). Then, the control group generation processing unit 106 determines whether the read element value exists in the control group (S75). Here, the control group generation processing unit 106 determines whether a set to which the values of the read elements belong has already been generated. In the example of FIG. 12C, for example, the value of “Name” is read, and it is determined whether a set having the same value already exists. If the read element value does not exist in the control group (S75: NO), the control group generation unit 106 registers the row number of the element in the list in association with the read element value (S76). ). On the other hand, when the value of the read element exists in the control group (S75: YES), the control group generation unit 106 adds the element number to the list (that is, the set) of the row numbers associated with the matching value. A line number is added (S77).

  Thereafter, the control group generation unit 106 determines whether an unprocessed element exists in the itemized list of key items (S78). If there is an unprocessed element (S78: YES), the process returns to S74. When the condition for aggregating key items is “perfect match” and processing is performed for the item-specific list of “Name” shown third from the left in FIG. 12C, the same value is applied to the first to third lines. Since it is not in the control group, a new set of row numbers is generated. "Fujita Tomoko" in the fourth line is the same as the first line. Therefore, when the fourth row is acquired in S74, it is determined that a matching value exists in the control group in S75. Similarly, the fifth row is added to the same set as the second row. A control group as shown in FIG. 28A is generated from the third item-by-item data from the left in FIG. 12C.

  Further, the control group generation unit 106 determines whether there is an unprocessed key item (S79). If there is an unprocessed key item (S79: YES), the process returns to S71 in FIG. On the other hand, when there is no unprocessed key item (S79: NO), the control group generation unit 106 determines whether there is one type of control group (S80). By the processing up to this point, the control groups are generated by the number of key items. Further, in the generation of the control group, the combination of the value and the set of the row numbers as shown in FIG. 28A was held. Here, the association with the value was deleted, and only the set of the row numbers was processed. set to target.

  When there are a plurality of control groups (S80: NO), the control group generation unit 106 acquires two control groups (S81). For example, two control groups are acquired in ascending order of the number of sets included in the control group. Further, the control group generation unit 106 generates an intersection of the two acquired control groups (S82). Then, the control group generation unit 106 replaces the obtained two control groups with the generated intersection set (S83). Then, the process returns to S80. On the other hand, if it is determined in S80 that there is only one control group (S80: YES), the control group generation unit 106 ends the control group generation processing.

  Through the above-described control group generation processing, the control group generation unit 106 causes the data storage unit 101 to store a set of row numbers that are not associated with values, as illustrated in FIG. 28B. Note that, in a later-described horizontal combining process and a totaling process, records can be aggregated if a set of row numbers is known.

  When records are sorted in tabular data to be output, a set of row numbers is sorted in the control group generation processing. It is assumed that a set of row numbers as shown in FIG. 29A is generated from tabular data having two key items of “alphabet” and “numeric”. Here, a set of line numbers in which the value of “alphabet” is “A”, “B”, and “C” is sorted in ascending order of “alphabet”. When the set of line numbers is sorted in ascending order of the value of “number”, it is assumed that a set of line numbers as shown in FIG. 29B is generated. In FIG. 29B, a set of row numbers in which the value of “number” is “0” and “1” is generated. When such data is sorted by a plurality of keys in the order of key letters “alphabet” and “numeric”, an intersection set as shown in FIG. 29C is generated. In the example of FIG. 29C, a set of line numbers in which the values of “alphabet” and “number” are “A” and “1”, “B” and “1”, “C” and “0” are generated. I have. When the key items are sorted by a plurality of keys in the order of “numeric” and “alphabet”, an intersection set as shown in FIG. 29D is generated. In the example of FIG. 29D, a set of row numbers in which the values of “numeric” and “alphabet” are “0” and “C”, “1” and “A”, “1” and “B” are generated. I have. By maintaining the sorted order in this way, even if the association between the set of row numbers and the value is deleted, records can be sorted and aggregated based on the set of row numbers and their order. .

  As described above, in the control group generation processing, one control group is finally generated and stored in the data storage unit 101. By obtaining the intersection set of the control group, even when a plurality of key items are specified, records can be aggregated for each set of records specified by the plurality of key items.

<Horizontal join processing / aggregation processing>
After the control group is generated, a horizontal joining process or a totaling process is performed. The horizontal combining process is performed by the horizontal combining unit 107, and the tallying process is performed by the tallying unit 108.

  FIG. 30 is a processing flow illustrating an example of the horizontal combination / aggregation processing. First, the horizontal combining unit 107 or the counting unit 108 generates and initializes an output-specific list (FIG. 30: S91). Here, item-by-item lists are generated by the number of output items based on the setting data of the horizontal connection or the setting data of the tallying process.

  Further, the horizontal combining unit 107 or the counting unit 108 acquires one itemized list to be processed (S92). In the case of the horizontal combination processing, one item list is acquired from the item list after the vertical combination as shown in FIG. 12C. In the case of the tallying process, one is obtained from the itemized list corresponding to the key item and the tallying item. Further, the horizontal coupling unit 107 or the counting unit 108 acquires one set from the control group (S93). Here, one set of row numbers is acquired from the control group as shown in FIG. 28A.

  Then, the horizontal combining unit 107 or the counting unit 108 determines whether to combine the values (S94). Here, it is determined that the values are to be combined when performing a horizontal combination or when “combination” is specified as the item aggregation method. On the other hand, as the method of summing items, “sum”, “average”, “median”, “mode”, “maximum”, “minimum”, “variance”, “standard deviation”, etc. are specified. If so, it is determined that the values will not be combined.

  When the values are combined (S94: YES), the horizontal combining unit 107 or the counting unit 108 acquires the element corresponding to the row number included in the set acquired in S93 from the itemized list acquired in S92, (S95). As shown in the duplication value processing selection area of FIG. 16, in the horizontal merging processing, when records whose key items satisfy predetermined conditions are combined into one record, values may be merged without duplication. Alternatively, the values may be combined while permitting duplication. In the horizontal combination process, records in which key items satisfy a predetermined condition may be sorted, for example, in order of appearance frequency, instead of being combined into one record.

  On the other hand, when it is determined that the values are not combined in S94 (S94: NO), the counting unit 108 calculates the values (S96). Here, the horizontal combining unit 107 or the counting unit 108 obtains the elements corresponding to the row numbers included in the set obtained in S93 from the itemized list obtained in S92, and totals them based on the item counting method. Or calculate the average.

  After S95 or S96, the horizontal combining unit 107 or the totaling unit 108 registers the combined value or the totalized value as a new element in the output itemized list (S97). Then, the horizontal coupling unit 107 or the counting unit 108 determines whether an unprocessed set exists in the control group (S98). If there is an unprocessed set (S98: YES), the process returns to S93. On the other hand, when there is no unprocessed set (S98: NO), the horizontal combining unit 107 or the counting unit 108 determines whether an unprocessed item exists (S99). In the case of the horizontal combination process, it is determined whether an unprocessed item list exists in the item list after the vertical combination as shown in FIG. 12C. In the case of the tallying process, it is determined whether an unprocessed item exists in the itemized list corresponding to the key item and the tallying item. If there is an unprocessed item (S99: YES), the process returns to S92. On the other hand, when there is no unprocessed item (S99: NO), the horizontal combining unit 107 or the counting unit 108 ends the horizontal combining / talling process.

  As described above, in the present embodiment, records to be aggregated are specified using the control group. As described above, the generation of the control group is performed by determining whether or not each of the values of the key item belongs to the already generated set (any of the sets of the control group). Since it is not necessary to perform a process for comparing each of the key item values with each of the other key item values, the control group can be generated relatively quickly. After the control group is generated, it is not necessary to maintain the association between each set and the value of the record belonging to the set. That is, each set of the control group holds only the row number. The amount of memory required to store such a control group is relatively small.

<New item generation process>
Next, a description will be given of a process when generation of a new item is instructed in the tallying process. For example, when a “new” panel is added to the total item display area on the screen shown in FIG. 23, in S64 of FIG. 19, the setting processing unit 102 inputs the coefficient of the value, the calculation formula, or the condition formula. Input is received from the user terminal 2. FIG. 31 is a diagram illustrating an example of a new item setting screen. For example, in FIG. 23, a “new” panel is added to the total item display area, and when the added panel is clicked, a screen as shown in FIG. 31 is displayed on the display unit of the user terminal 2. .

  In the example of FIG. 31, a new item name 7001 and buttons 7002 for “key count”, “calculation”, and “condition” are displayed. When "key count" is pressed, the number is counted for each of the same values included in the key item. When “calculation” is pressed, a screen for inputting a calculation formula is displayed on the user terminal 2. FIG. 32 is a diagram illustrating an example of a screen for inputting a calculation formula. On the screen of FIG. 32, an area 8001 for displaying a list of total items, a numeric keypad 8002 for inputting an arithmetic expression or a fixed value, and a calculation for displaying a calculation expression on an item panel and a numeric keypad panel It has a formula display area 8003 and a check calculation formula display area 8004 for checking the calculation formula with a character string. In the total item list, a panel added to the total item display area in FIG. 25 and the like is displayed. Further, in the present embodiment, a calculation formula can be created using an item registered at a higher rank than the new item. That is, a new item can be further defined by using the calculation result of another new item registered at a higher rank.

  When “condition” is pressed, a screen for inputting a conditional expression is displayed on the user terminal 2. FIG. 33 is a diagram illustrating an example of a screen for inputting a conditional expression. In the example of FIG. 33, a total item list 9001, an area 9002 for defining a condition, an area 9003 for setting an output value when the condition is met, and an area for setting an output value when the condition is not met. 9004 is displayed. For example, the user can set a value to be output in a case where the magnitude relationship between the total item and an arbitrary value satisfies a designated condition and in a case where the magnitude relationship does not satisfy the designated condition. Also, a plurality of conditional expressions can be defined by connecting them with “AND” (logical product) or “OR” (logical sum). Also in the case of a conditional expression, it is possible to further create a conditional expression by using a total item registered higher than the new item.

  When a new item is set, an item for calculating the frequency, a calculation formula, or a conditional expression is set in the setting data of the tally item as “item tallying method”.

  FIG. 34 is a process flow illustrating an example of a new item generation process. First, the counting unit 108 reads the setting data of the counting process, and acquires one new item (FIG. 34: S101). In addition, the counting unit 108 determines whether the counting process of the new item is a conditional expression or a calculation expression (S102). If it is a conditional expression or a calculation expression (S102: YES), the counting unit 108 acquires one set of the control group (S103).

  Then, the counting unit 108 determines a conditional expression or calculates a calculation expression (S104). In addition, the counting unit 108 registers the determination result of the conditional expression or the calculation result of the calculation expression as a new element in the itemized list for output (S105). Then, the tallying unit 108 determines whether an unprocessed set exists in the control group (S106). If an unprocessed set exists (S106: YES), the process returns to S103. On the other hand, when there is no unprocessed set (S106: NO), the process proceeds to S111. On the other hand, when it is determined in S102 that the expression is not a conditional expression or a calculation expression (S102: NO), the counting unit 108 acquires one set of the control group (S107). Then, the counting unit 108 counts the number of values included in the set of the control group (S108). Then, the counting unit 108 registers the new item in the output itemized list (S109).

  Thereafter, it is determined whether there is an unprocessed set (S110). If there is an unprocessed set (S110: YES), the process returns to S107. On the other hand, when there is no unprocessed set (S110: NO), or when it is determined in S106 that there is no unprocessed set (S106: NO), the counting unit 108 determines that an unprocessed new item exists. Is determined (S111). If there is an unprocessed new item (S111: YES), the process returns to S101. On the other hand, when there is no unprocessed new item (S111: NO), the counting unit 108 ends the new item generation processing.

  For example, in the case of horizontally connecting “Name” in the itemized list of FIG. 12C as a key item, a control group shown in FIG. 28B is generated. When the elements of the itemized list are aggregated based on the control group of FIG. 28B, the elements on the first and fourth rows, the elements on the second and fifth rows, and the element on the third row are classified into Become a list element. FIG. 35A shows an example of item-by-item data after horizontal merging. In the list for each item in FIG. 35A, the element on the first line is an aggregation of the elements on the first and fourth lines in FIG. 12C. Similarly, the elements on the second row in FIG. 35A are obtained by integrating the elements on the second and fifth rows in FIG. 12C. The element on the third row in FIG. 35A corresponds to the element on the third row in FIG. 12C.

  In the case where duplicate values are left in the field in the itemized list after the horizontal combination, an itemized list as shown in FIG. 35B is generated. In the example of FIG. 35B, two identical values are held in the elements on the first and second lines of the item-specific list of “Name”. Regarding items other than key items, a plurality of values may be combined and registered for one element in the combined itemized list, or registered without duplication. Further, a numerical value indicating the frequency of occurrence of the value after removing the overlap may be registered.

  In addition, although not shown, when performing the tallying process, an itemized list is generated for the key items and the tallying items. Then, for each set of the control groups, the total is calculated based on the set totaling method.

  As described above, the itemized list after the horizontal combination or the itemized list after the aggregation process is all generated. In order to output these itemized lists as tabular data, the output process described with reference to FIG. 13 is performed.

<Modification>
In the present embodiment, the tabular data is decomposed into an itemized list and handled by the itemized list generation processing. In the above description, the pattern for performing the output process from the vertical combination process and the pattern for performing the output process from the horizontal combination / aggregation process using the tabular data after the vertical combination have been described. However, the processing pattern according to the present embodiment is not limited to these. For example, by using the itemized list, vertical joining processing, horizontal joining processing, and totaling processing can be performed in this order, or further vertical joining processing can be performed using a plurality of tabular data generated in this manner. .

  In the present embodiment, the configuration has been described in which the tabular data processing device 1 provides a service to each user terminal 2 via the network 3; however, the present invention is not limited to such a mode. For example, the user terminal 2 installed with the tabular data processing program may execute the process according to the present embodiment without connecting to the network 3. In this case, for example, the tabular data processing apparatus receives a data operation instruction from the user via the input I / F. Further, the tabular data processing device outputs the result to an output device such as a monitor via the output I / F.

  Further, the processing flow described in the present embodiment is an example, and the processing order may be changed or the processing may be performed in parallel as long as the processing result does not change. The layout and the like of the example of the screen can be appropriately changed.

<< Batch process embodiment >>
The device according to the present embodiment processes so-called tabular data. In tabular data, for example, elements (also called “fields”, “columns”, “items”, “attributes”, or “values”) are separated by delimiters (that is, “delimiters”) such as commas, tabs, and spaces. This is text data that is separated and records (also called “lines”) are separated by line feeds. In the present embodiment, a predetermined number of lines from the top of the tabular data are treated as title lines. That is, elements included in a predetermined number of rows from the top are treated as field names (also called “column names”, “item names”, “attribute names”, and the like). At this time, if there is an empty line, it may be automatically deleted. The other lines are treated as data records. Then, the elements (also referred to as “values”) included in the data records are subjected to the joining process, the tallying process, and the like.

  Specifically, the program according to the present embodiment holds the elements included in the tabular data in an array (list) and performs various processes described later. Also, the tabular data according to the present embodiment may further hold a nested array as one element. In other words, the configuration is such that a plurality of values can be held in one field of the tabular data. In the present embodiment, elements are basically handled as character strings. However, in the tabulation process or the like, it may be determined whether or not the element is a numerical value, date and time, or the like, and an error message may be output when an element having a format other than a predetermined format is detected.

<Functional configuration>
FIG. 36 is a functional block diagram illustrating an example of a tabular data processing device that processes tabular data. The tabular data processing device 4 according to the present embodiment includes a data storage unit 401, an input / output unit 402, a control unit 403, a vertical connection unit 404, a horizontal connection unit 405, a tally unit 406, a grouping unit 407, a search unit 408, an external cooperation unit 409, and a batch processing unit 410.

  The data storage unit 401 stores, for example, tabular data to be processed in the present embodiment, script data indicating the details of the processing, tabular data generated as a processing result, and other data generated intermediately in the processing. Hold. The settings for executing the processing according to the present embodiment are also stored. In the present embodiment, it is assumed that CSV data is stored in the data storage unit 401 of the tabular data processing device 4 in advance.

  The input / output unit 402 reads the tabular data stored in the data storage unit 401 based on the user's specification, and writes the tabular data generated as a processing result to the data storage unit 401. The control unit 403 uses one or more processes (also referred to as processes) such as a vertical combination process, a horizontal combination process, a tally process, a grouping process, a search process, an external cooperation process, and the like, based on a user operation, and sequentially performs the processes. Define the job to be executed. Note that the vertical combining unit 404, the horizontal combining unit 405, the counting unit 406, the grouping unit 407, the search unit 408, the external cooperation unit 409, and the like execute processing based on the job definition. The job definition is stored as a script for executing a series of processes, for example, in an XML (Extensible Markup Language) format. Here, the setting data read by the program according to the present embodiment, not the source code of the program itself, is called a “script”.

  The vertical combination unit 404 performs a process (referred to as “vertical combination process”) of integrating records included in one or more tabular data into one tabular data. Also, a setting for executing the vertical combining process is received by a user operation. The horizontal combining unit 405 performs a process of combining records of a plurality of tabular data into one record in which the value of a key item satisfies a predetermined condition (referred to as “horizontal combining process”). Also, a setting for executing the horizontal combination process is received by a user operation.

  The tabulation unit 406 performs a process of tabulating the values as numerical values or integrating the values as a character string into one field based on the settings. The grouping unit 407 divides values included in the tabular data into predetermined groups and performs cross tabulation. In addition, a process (grouping release) of dividing tabular data in a cross tabulated state according to conditions is performed. Note that the horizontal connection unit 405, the aggregation unit 406, and the grouping unit 407 are collectively referred to as an aggregation unit in the present invention.

  The search unit 408 extracts records from tabular data based on predetermined search conditions. The external cooperation unit 409 activates an application program other than the program that performs the processing according to the present embodiment. The batch processing unit 410 uses one or more jobs defined by the control unit 403 to define a batch process that is a process of sequentially executing the jobs. In addition, based on the definition of the batch process, predetermined jobs are started in a predetermined order at a predetermined date and time. The details of the function will be described later.

<Apparatus configuration>
The tabular data processing device 4 is also a computer as shown in FIG. The computer 1000 shown in FIG. 3 includes a CPU (Central Processing Unit) 1001, a main storage device 1002, an auxiliary storage device 1003, a communication IF (Interface) 1004, an input / output IF (Interface) 1005, a drive device 1006, and a communication bus 1007. ing. The CPU 1001 performs a process according to the present embodiment by executing a program (also referred to as “software” or “application”). The main storage device 1002 caches programs and data read by the CPU 1001, and expands a work area of the CPU. The main storage device is, specifically, a RAM (Random Access Memory) or a ROM (Read Only Memory). The auxiliary storage device 1003 stores a program executed by the CPU 1001, setting information used in the present embodiment, and the like. The auxiliary storage device 1003 is, specifically, a hard disk drive (HDD), a solid state drive (SSD), a flash memory, or the like. The main storage device 1002 and the auxiliary storage device 1003 function as the data storage unit 401 of the tabular data processing device 4. The communication IF 1004 transmits and receives data to and from another computer. The communication IF 1004 is, specifically, a wired or wireless network card or the like. The tabular data processing device 4 may be connected to a network via the communication IF 1004. The input / output IF 1005 is connected to the input / output device, and receives an operation from a user or presents information to the user. The input / output device is, specifically, a keyboard, a mouse, a display, a touch panel, or the like. The drive device 1006 reads data recorded on a storage medium such as a magnetic disk, a magneto-optical disk, and an optical disk, and writes data on the storage medium. The above components are connected by a communication bus 1007. A plurality of these components may be provided, or some of the components (for example, the communication IF 1004, the drive device 1006, etc.) may not be provided. Further, the input / output device may be configured integrally with the computer. Also, the program executed in the present embodiment is provided via a portable storage medium readable by the drive device 1006, a portable auxiliary storage device 1003 such as a flash memory, a communication IF 1004, and the like. It may be. When the CPU 1001 executes the program, the computer shown in FIG. 3 operates as the tabular data processing device 4 shown in FIG.

<Details of function>
Next, the function of the tabular data processing device 4 will be described. FIG. 37 is a schematic diagram of a job according to the present embodiment. The tabular data processing device 4 extracts data of specified conditions from a plurality of tabular data having different formats (that is, a configuration of tabular data such as columns and column names), and can perform vertical and horizontal coupling. it can. In addition, when performing horizontal coupling, data cleansing can improve the accuracy of the result. In addition to the combining process, aggregation, search, and the like can be performed, and these processes can be executed in cooperation with each other on a set schedule. It is preferable that the tabular data to be created be output as a new file and the input tabular data is not updated, for example, in order for the user to repeat the process while performing trial and error. The settings of these functions can be saved, for example, in XML format.

<Vertical connection>
The vertical combination is, for example, a process of integrating records included in a plurality of tabular data (input data) having different data formats to generate new tabular data (output data). FIG. 38 and FIG. 39 are examples of input data of the vertical combination processing. FIG. 38 shows tabular data having a file name of “employee transportation expense settlement.csv”, which includes eight records. FIG. 39 shows tabular data of a file name “operating expenses.csv”, which includes eight records. Although the column names and the order in FIGS. 38 and 39 are different, the two tabular data partially include items corresponding in content. FIG. 40 shows an example of output data of the vertical combining process. The tabular data in FIG. 40 includes records corresponding to the eight cases in FIG. 38 and the eight cases in FIG. That is, the vertical combining unit 404 combines (stacks) the records included in the input data in the vertical direction. Further, in FIG. 40, predetermined columns are associated with each other. The column “name” in FIG. 38 is associated with the combination of the column “lastName” and the column “FirstName” in FIG. The input tabular data may be one instead of a plurality. In this case, the column names of the tabulated data to be output can be changed, but the elements of the data record are not different from the input data. Note that the vertical combining process is performed by the vertical combining unit 404.

  FIG. 41 is a diagram illustrating an example of a vertical combination setting screen (vertical combination column editing screen). The table of FIG. 41 includes a column of “CSV file name” and columns (“number”, “date”, “name”...) Indicating correspondence between columns included in each CSV file. The column names (“number”, “date”, “name”...) Displayed in the upper part of FIG. 41 represent the column names of the output file. That is, the columns of each CSV file displayed in the same column in FIG. 41 are associated with each other in the output file. Note that the column name of the output file can be changed by the user. The order of the columns shown in FIG. 41 indicates the order of the columns in the output file. Like the column of “name”, a combination of a plurality of columns in one CSV file can be associated with one column in the other CSV file. Further, there may be a column that is not associated with any column of the other CSV file, and at least a part of the column of the input file may not be output to the output file. It is also possible to associate three or more CSV files.

  When the “base file selection” button is pressed on the vertical combination column editing screen shown in FIG. 41, a vertical combination base file selection screen as shown in FIG. 42 is displayed. In the screen of FIG. 42, the name of the read CSV file is displayed in the column of “CSV file name”. In the column of “data size”, the file size of the CSV file is displayed. In the column of “date”, the update date of the CSV file is displayed. In the check box in the column of "selection", the user checks a CSV file as a reference of the vertical combination. In the present embodiment, the attribute name of the output data is set according to a predetermined priority using the attribute name of the reference CSV file.

  When the “edit” button is pressed on the screen in FIG. 41, an operation menu is displayed. The operation menu includes menus such as “edit”, “delete”, “move right”, “move left”, “add attribute to right”, “add attribute to left”, and “cancel”. When the user selects “edit”, a display attribute extraction setting screen as shown in FIG. 43 is displayed. The display attribute extraction setting screen will be described later. When "delete" is selected, the column is deleted. When "move right" is selected, the row is replaced with the row on the right. When "move to left" is selected, the column is replaced with the column on the left. When "add attribute to right" is selected, one new column is added to the right of the column. When "Cancel" is selected, menu selection is stopped.

  Further, on the vertical combination column editing screen, the color of the field may be changed according to the setting state. For example, light purple is the same as the new attribute name, and indicates a state in which no attribute is individually specified on the display attribute extraction setting screen. The cream color indicates a state in which the attribute is individually specified on the display attribute extraction setting screen. Further, the orange color indicates a state where the CSV file selection is released on the display attribute extraction setting screen. The white color indicates a state where the attribute name that matches the new attribute name on the display attribute extraction setting screen does not exist in the CSV file. When the process is restored, the panel that was light purple when the new process was created becomes cream-colored.

  When "edit" is selected from the operation menu in FIG. 41, a display attribute extraction setting screen as shown in FIG. 43 is displayed. The screen of FIG. 43 includes a text box of “new attribute name”, a column of “CSV file name”, a check box of “CSV file attribute name”, a line of “CSV file content”, and “setting” for saving the setting. Buttons are included. The attribute name is displayed in the text box of "new attribute name". The new attribute name can be modified by the user. The CSV file name is displayed in the column of “CSV file name”. The user checks the “CSV file attribute name” check box when associating attributes individually. For example, in a case where two attributes “prefecture” and “city” are to be combined into one attribute, “prefecture” and “city” are checked. The first line of the data record of the CSV file is displayed in the line of “contents of CSV file”. The above information is displayed on the display attribute extraction setting screen by the number of CSV files. Further, as the setting of the vertical combination, the contents set by the user using FIGS. 41 to 43, such as correspondence between columns, are stored in the data storage unit 401.

<Horizontal coupling>
The horizontal join is a process of integrating records (also referred to as aggregation) based on a set of elements included in a predetermined column and generating new tabular data. Specifically, for example, one table format data obtained by vertical combination or the like is processed, and records are combined in the horizontal direction based on the value of the key attribute specified by the user. By using a data cleansing function described later, when specifying a set of elements such as an address and a name, it is possible to perform processing while absorbing fluctuations in notation. It is also possible to check for missing data, duplicate data, and check data quality.

  FIG. 44 is an example of tabular data (input data) to be processed for horizontal joining. FIG. 44 shows the result of the vertical combining process using the tabular data as shown in FIGS. In FIG. 44, the items “company name” in FIG. 45 and “company” in FIG. 46 are arranged in one column. The other items are not associated, and some fields in FIG. 44 are empty. In the horizontal join, a plurality of records having the same key attribute value are combined into one record (also referred to as “name identification”), and for attributes other than the key, the values included in the multiple records are delimited by a single field. The list may be separated by a line feed. In this case, when a plurality of the same values exist in one field, the duplication may be removed or the duplication may not be removed. In the horizontal combination, the field of an item that does not exist in other tabular data may be complemented (also referred to as “automatic complement”) by using tabular data corresponding to so-called master data. When records having the same (perfect match) key attribute values are grouped into one line using the attribute “company” in FIG. 44 as a key attribute, the result is as shown in FIG. In the example of FIG. 47, the column of “number of employees” not shown in FIG. 46 is complemented by the element of FIG. 45, which is so-called master data, based on records having the same key attribute value. In the complementing process, when performing vertical combining, it is assumed that the attribute to be complemented is arranged on the left side, and the priority record (master data) on the tabular data is determined. Further, when the master data is uniquely specified by the key attribute, the complement can be executed from the contents of the priority record. Finally, the priority record is deleted. The key attributes of the horizontal combination may be standardized (data cleansing). For example, if the corporation name included in the value is “stock company”, “(stock)”, “f)”, etc., it can be treated as the same. In addition, in addition to “perfect match”, in the case of “front match”, “backward match”, “middle match”, etc., it may be determined that they are the same.

  FIG. 48 and FIG. 49 are diagrams illustrating an example of a screen for setting horizontal coupling. The screen (also referred to as a horizontal combination screen) in FIG. 48 includes areas for designating “attribute list”, “key attribute”, “processing of duplicate values”, and “connection factor between values”. Including. In the "attribute list" area, a list of attributes included in tabular data as an input file is displayed. By dragging and dropping the displayed attribute panel to the “key attribute” area, the user can specify the “key attribute”. When a plurality of key attributes are displayed in the key attribute area, the user can also change the order of the key attributes by dragging the panel.

  The details of the specification of the key attribute are performed through a screen as shown in FIG. 49 (also referred to as a horizontal combination key setting screen). The screen of FIG. 49 includes an area for designating “name determination” and “data cleansing processing”. The “name identification” is selected by the user clicking one of “perfect match”, “intermediate match”, “front match”, and “backward match”. Here, when a plurality of key attributes are specified, it is assumed that the operation is performed as “perfect match” regardless of the setting of the name identification judgment of each panel. When performing data cleansing, the user checks the check box of “Perform standardization (data cleansing)” and selects a type of data cleansing from a pull-down menu. In the example of FIG. 49, “corporate name” is selected.

  Further, as shown in FIG. 48, regarding the “processing in case of overlapping”, the user “collects in one line and removes duplicates”, “collects in one line and does not remove duplicates”, and Sort by "or" Complete automatically without grouping ". Further, in the case of grouping in one line, for the “connection factor between values”, a symbol that separates a plurality of elements grouped in one field for attributes other than the key attribute is selected. Options include “comma”, “slash”, “new line”, and “space”. The items described with reference to FIGS. 48 and 49 are stored in the data storage unit 401 as setting information of the horizontal combining process. In the setting screens of FIGS. 48 and 49, the color of the panel may indicate the setting state. For example, light blue indicates an unset state. Also, purple indicates a state that has been set on the horizontal combination key setting screen. In addition, the content specified by the user via the screens of FIGS. 48 and 49 is stored in the data storage unit 401 as the setting of the horizontal connection.

  Here, with reference to FIG. 50 to FIG. 59, output examples for “processing of duplicate values” and “connection factors between values” will be described. For example, it is assumed that the data of the employee's transportation expenses settlement as shown in FIG. 50 is used as input data, and "blood type" is laterally connected as a key attribute. FIG. 51 shows an output example in which the connection factor between values is removed and commas are excluded. Similarly, an output example in which duplicates are removed and the connection factor is a slash is as shown in FIG. FIG. 53 shows an output example in which duplicates are removed and the connection factor is a line feed. FIG. 54 shows an output example in which the overlap is removed and the connection factor is a space. FIG. 55 shows an output example in which the connection factor is a comma without removing the duplication. Similarly, an output example in which the slash is used as the connection factor without removing the duplication is as shown in FIG. FIG. 57 shows an output example in which the connection factor is a line feed without removing the duplication. Then, an output example in which the overlap is not removed and the connection factor is a space is as shown in FIG. Further, an output example in the case where the frequency sorting is performed without being combined into one line is as shown in FIG.

<Cleansing and user-defined dictionary>
Standardization (data cleansing) can be performed in horizontal joining, tabulation and grouping described later. Standardization performs name matching for shaky notations. For example, this is specified when “judgment judgment” selects “perfect match”. As a data cleansing method, any one of a telephone number, a postal code, a date, a company name, a numerical value, Japanese analysis, English analysis, a name, an address, and a user dictionary can be selected.

  Regarding telephone numbers, differences in notation such as “03-1234-5678”, “03 (1234) 5678”, and “0312345678” are treated as being the same. Regarding postal codes, differences in notation such as “$ 273-0039”, “273-0039”, and “2730039” are treated as being the same. Dates are GGGGyyMMdd (Heisei 250110, H250110), yyMMdd (130110), yyyyMMdd (20130110), GGGGyy MM / dd / dd (January 10, 2013, January 10, H25 / Jan 10, MM / dd / yyyy) (January 10, 2013), GGGGyy / MM / dd (2013/01/10, H25 / 01/10), yy / MM / dd (13/01/10), yyyy / MM / dd (2013 / 01 / 10,2013 / 1/10 9:06:39), yy-MM-dd (13-01-10), GGGGyy-MM-dd (2013-01-10, H25-01-10), yyyy -MM-dd (2013-01-10) and MM / dd / yy (yy / 13/13) are treated as the same. The corporate name is (stock), the mark with model-dependent characters and a corporation, (Yes), the mark with model-dependent characters and a limited liability company, (medical) and medical corporations, (corporation) and corporations, (goods) and foundations , (G) and joint-stock companies, (sect) and religious corporations, (study) and school corporations, (s) and mutual companies, (k) and joint-stock companies, (n) and joint-stock companies, (G) and independent administrative corporations , (Special) and specially licensed corporations, (Fuku) and welfare corporations, and general, public interest, the presence of councils and federations are treated as the same. Numerical values are treated as the same notation differences such as \ 12,300, 12,300, 12,300, 1,200,300, and 1,200,300. In the Japanese sentence analysis, sentences such as “I am Taro”, “Taro Taro”, and “Taro Aoi” are analyzed to extract names and the like. In the English sentence analysis, differences in titles in English sentences such as SankakuTaro, Sankaku Taro, and sankaku taro are treated as being the same. The name treats variants with the same feeling, such as Saito Taro, Saito Taro, Saito Taro, and Saito Taro. The addresses are `` No. B-567, Ingai, Ingai, Funabashi-shi, Chiba Prefecture, Room # Heights B-567 '', `` In-gai, Ingai, Funabashi-shi, Chiba Pref. -× -567 '', `` Ingai-Chome No. B-567, Funaibashi-shi, Chiba Prefecture, Room B-567 '', and `` Room No. 567, Ingai, Funaibashi-shi, Chiba Prefecture '' Treat as

  The user dictionary uses a dictionary file defined by the user in advance by setting the spelling and synonyms of the notation. By setting the notation fluctuation and synonyms in advance, ambiguous values can be normalized and handled. Also, a dictionary for collectively replacing characters (batch character replacement dictionary) can be selected. The user dictionary is a dictionary that performs name matching based on a relationship (parent-child relationship) and a synonym. The regular expression user dictionary is a dictionary for performing name matching based on a regular expression. The replacement user dictionary is a dictionary for performing character replacement collectively based on registered contents. The name-related dictionaries are applied with priorities such as, for example, rank 1: relationship and synonym, rank 2: regular expression.

  It is assumed that the contents of the user dictionary can be edited with a text editor or the like as shown in FIG. 60, for example. FIG. 60 is a description example of a user dictionary of synonyms with relations. In FIG. 60, words and phrases are schematically represented by alphabets. The relationship (parent-child relationship) is input, for example, by separating words and phrases with a half-width slash "/". Enter the parent phrase to the left of the slash and the child phrase to the right of the slash. The synonym is input, for example, by separating words and phrases with a single-byte comma “,”. Lines preceded by “#” are treated as comments and are not used for processing.

  FIG. 61 shows an example of the relationship between words and phrases formed by plotting the user dictionary shown in FIG. The alphabet enclosed by a circle in FIG. 61 represents one phrase. For example, “C”, “D” and “P” are synonyms, “C” is a parent “A”, and “P” is a relationship with a parent “O” (parent-child relationship). Note that the relationship and the synonym are automatically linked. Specifically, in FIG. 60, “K” directly defines the parent-child relationship with “I” only. When acquiring a top-level concept by automatically cooperating, if "K" is specified, it follows in the order of "K"-"I"-"H"-"G"-"Q"-"O", The top-level phrase "O" can be obtained. When a plurality of synonyms are associated with different parents, such as “C” and “P”, the synonyms “C”, “D”, and “P” are rearranged in a natural order, and Tied. The natural order is, for example, alphabetical order in the case of a character, and small value in the case of a numerical value. In the user dictionary as shown in FIG. 60, a synonym may be linked to the leftmost defined phrase. When the user dictionary as shown in FIG. 60 is set, the phrase “D” in FIG. 61 is associated with the synonym “C”. Further, the phrase “C” is associated with the parent “A”. When acquiring a subordinate concept of a phrase, for example, it is assumed that all children connected to the subordinate of the phrase and their synonyms are acquired. Specifically, when "H" is designated, "I", "K", "J", "M", and "L" can be acquired. In this way, each phrase is assumed to be associated with the highest parent, a synonym based on a predetermined priority, or all children connected below and their synonyms.

  In the present embodiment, for example, by defining the configuration of a department in an organization as a relationship, it is possible to perform name identification and counting described later for each department. Also, by defining the components that make up the product as a relationship hierarchically, it is also possible to perform name identification and aggregation for each component of the product. By comparing the results of the aggregation results for new and old products, it is also possible to check the compatibility of parts of the new and old products. Note that the synonym means a relationship of the same concept (same concept). In addition, the relationship means a relationship between a superordinate concept and a subordinate concept. In the present embodiment, it is possible to perform name identification with a high degree of freedom based on a dictionary set by the user.

  FIG. 62 shows an example of inputting a regular expression user dictionary. ".?" Indicates the presence of 0 or 1 character. Therefore, "parts" corresponds to "parts", "parts", "pats", and the like. “Parts” does not apply. “. *” Indicates the presence of zero or more characters. Therefore, ". * Maeda. *" Corresponds to "that Maeda", "Maeda", "Maeda?", And the like. It is assumed that a general regular expression can be used, and description of expressions other than the above is omitted.

  FIG. 63 shows an input example of the replacement user dictionary. In the example of FIG. 63, words are input by separating them with “/”. The phrase to the left of “/” represents the phrase after replacement, and the phrase to the right of “/” represents the phrase to be replaced. In addition, the term of the substituent can be input plurally by ",". When a user dictionary as shown in FIG. 63 is used, the phrase “JAPAN” is replaced with “JXPXP”.

<Tally>
The aggregation is a function of outputting a group of values as a summary statistic value based on a predetermined condition including a key attribute with one tabular data as a processing target. After the aggregation process, the aggregation attributes are aggregated (that is, aggregated) for each record in which the value of the key attribute satisfies the set condition. Further, four arithmetic operations can be performed on the obtained value or the original value group, and a calculation result based on a predetermined condition can be output. Further, data processing can be performed using a function. Further, in addition to counting the numerical values, data corresponding to the condition can be counted (counted), and data corresponding to the condition can be marked.

  For example, it is assumed that the output data of the vertical combination processing shown in FIG. 40 is the input data of the aggregation processing. FIG. 64 is an example of output data of the tallying process. With “station name” and “date” of FIG. 40 as key attributes, “value” and “salary” are respectively added, and the number of records to be counted (the corresponding number) depends on whether or not a predetermined conditional expression is satisfied. The flags (problems) are totaled for each year and month as shown in FIG.

  FIGS. 65 to 71 show examples of screens for setting the tallying process. The tabulation setting screen (tabulation screen) in FIG. 65 includes areas for setting “attribute list”, “new attribute”, “key attribute”, and “tabulation attribute”. In the “attribute list” area, a list of attributes included in the input data is displayed. In the area of “new attribute”, a “new” panel that the user drags when setting an item that is not included in the input data is displayed. The “key attribute” area is for the user to drag and drop a panel representing an attribute used as a key attribute, and the set key attribute is displayed. Note that the panels set to the key attributes may be reorderable. After the key attributes are set, the panel is displayed in two columns, the upper column displays the attribute names, and the lower column displays the contents of the tabulation method on the tabulation key setting screen described later. The “total attribute” area is an area for the user to drag and drop a panel representing an attribute to be totaled, and the set total attribute is displayed. The user can add a new attribute to the tally attribute by dragging and dropping the "new" panel into the "total attribute" area. The new attribute is added as a new attribute, such as the number of records, the result of an arithmetic operation, the result of a condition determination, the result of a predetermined function process, and the like. It should be noted that a new attribute that is set earlier (above the aggregation attribute area) can be used to define a later new attribute (below the aggregation attribute area).

  FIG. 66 is an example of a screen for setting key attributes (total key setting screen). The screen in FIG. 66 includes an area for selecting a tallying method for the key attribute selected by the user. As the counting method, the choices of "exact match", "user dictionary", "yearly", "monthly", "daily", "weekly / yearly", "weekly / monthly", and "by day of week" Are displayed, and the user can select one by clicking any of them. Note that “by year” to “by day of the week” is valid when the value is a date.

  FIG. 67 is an example of a screen for setting the tally attribute (tally attribute setting screen). The screen in FIG. 67 includes an area for selecting the type of character tabulation or the type of numerical tabulation for the tabulation attribute selected by the user. The types of character tabulation include “combination” and “frequency”. Joining is a function that outputs values separated by commas. Frequency is a function that outputs the number of occurrences of a value in parentheses. Types of numerical tabulation include “sum”, “average”, “maximum”, “minimum”, “median”, “mode”, “variance”, and “standard deviation”. The sum is a function for calculating a value obtained by adding all the numerical values. Average is a function that calculates the arithmetic mean of the sum. Maximum is a function that specifies the maximum value. The minimum is a function for specifying the minimum value. The median value is a function for specifying a value located at the center when a finite number of data are arranged in order of size. The mode specifies the value that appears most frequently in the sample group. The variance calculates the sample variance. The standard deviation calculates a sample standard deviation. The user can select the type of aggregation by clicking any of them. It should be noted that a “new” panel can be registered as the total attribute.

  FIG. 68 shows an example of a screen for setting a new attribute as a tally attribute (total tally attribute screen). That is, the screen in FIG. 68 is displayed when setting a new attribute added to the total attribute in the screen in FIG. The screen of FIG. 68 includes an area for inputting the name of the new attribute and an area for selecting the type of the new attribute. The user inputs a character string for the name of the new attribute. Then, the input character string is set as the attribute name of the output data. The types of the new attribute include options of “count”, “calculation”, “condition”, and “function processing”. The count is a function for outputting the number of attributes specified on the key attribute screen. That is, when “count” is selected, the number of records included in the record group that satisfies the condition set on the “total key setting” screen of FIG. 66 is counted and set as the value of the new item. The calculation is a function of outputting a calculation result, and details are set on a calculation screen described later. The condition is a function of outputting a judgment result of the set judgment condition, and details are set on a condition screen described later. The function processing is a function for outputting a result of the set function processing, and details are set on a function processing screen described later. In the setting screen of FIG. 68, the color of the panel may indicate the setting state. For example, light blue indicates an unset state. Also, purple indicates a state that has been set on the horizontal combination key setting screen. Yellow indicates that the new attribute has not been set.

  FIG. 69 is an example of a screen (calculation screen) for setting “calculation”. The screen of FIG. 69 includes an area for displaying a list of aggregation attributes, and a numeric keypad area for setting four arithmetic operations. The user can define a calculation formula for calculating the value of the new attribute by combining them. The aggregation attribute displays the aggregation screen aggregation attribute name. Here, the set attributes (purple panel) are displayed. Also, a new attribute before the own panel is displayed and can be used for calculation. For the value of the operation expression, select a numerical value or an operator for the expression. The input is performed by selecting from the panel of the tabulation attribute or by dragging and dropping from the numeric keypad area. Further, the value of the calculation formula is displayed as text in the confirmation calculation formula. If there is an error in the calculation formula, it may be highlighted in red letters or the like.

  FIG. 70 is an example of a screen (condition screen) for setting “condition”. The screen of FIG. 70 includes an area for displaying a list of total attributes, an area for setting a conditional expression, a value to be displayed for the new attribute when the condition is satisfied, and a value for the new attribute when the condition is not satisfied. And an area for inputting a value to be displayed. The user can set a conditional expression for determining whether or not an arbitrary aggregation attribute satisfies a predetermined condition. A plurality of conditional expressions can be connected by AND (logical product) or OR (logical sum). The aggregation attribute displays the aggregation attribute name on the aggregation screen. Here, the set attributes (purple panel) are displayed. As the new attribute, the attribute before the own panel is displayed. In the area for setting a conditional expression, a conditional expression for counting is input. As the attribute, a total attribute that is a condition is selected. For the value, enter the value of the condition. For the classification, one judgment condition is selected. As options, “match”, “include”, “greater than”, “less than”, “greater than”, “less than”, “does not match”, “do not include”, and “regular expression” can be selected. When the “AND” button is pressed, a new AND conditional expression row is added. When the “OR” button is pressed, a new OR conditional expression line is added. In the column of the condition matching, the value to be output to the row of the value matching the conditional expression is selected from the input or the total attribute. In the column that does not match the condition, a value to be output in a row of a value that does not match the conditional expression is input or selected from the total attribute.

  FIG. 71 is an example of a screen (function processing screen) for setting “function processing”. The screen in FIG. 71 includes an area for displaying a list of “total attributes”, an area for specifying “target attribute”, and an area for selecting “data processing function name”. The user can drag and drop the panel of the tally attribute to the area for setting the target attribute. When the user selects a data processing function name, an area for inputting an argument is displayed according to the selected data processing function name. The aggregation attribute displays the aggregation attribute name on the aggregation screen. Here, the set attributes (purple panel) are displayed. As the new attribute, the attribute before the own panel is displayed. For the target attribute, an attribute for performing the function processing is specified. For the data processing function name, select the classification of the function. The items described with reference to FIGS. 65 to 71 are stored in the data storage unit 401 as setting information of the tallying process.

  The options are as follows: “Alphabetic” is a function that extracts only letters of the alphabet (A-Z and a-z). “Left” is a function for acquiring the specified number of characters from the left end. “Lefta” is a function that searches for a search character from the left end and obtains characters up to that character. “Leftb” is a function that searches the left end or the search character and obtains the character up to that position. “Length” is a function for obtaining the length of a character. "Lower" is a function that changes uppercase letters to lowercase letters. “Middle” is a function for acquiring the position and the number of characters specified from the left. “Multibytechar” is a function that extracts only multibyte characters. “Number” is a function that determines one character at a time in a character string and extracts only a numerical value. “Repeat” is a function for repeatedly registering a specified character from a specified attribute value (numerical value). “Replace” is a function for replacing a specified character. “Replacedic” is a function that replaces a specified character based on the contents of a replacement dictionary. "Right" is a function for acquiring a specified character from the right end. “Righta” is a function that searches for a search character from the right end and obtains characters up to that character. “Rightb” is a function for performing a search character search from the right end and obtaining characters up to that position. “Substitute” is a function that replaces the position specified from the left with the number of characters. "Normalizeaddress" is a function that normalizes ambiguous addresses. “Normalizecompany” is a function for standardizing a company name. "Normalizedate" is a function that separates dates with a slash (/). “NormalizedateISO” is a function for normalizing a date. "Jnounphrase" is a function that extracts only general nouns from sentences. “Normalizename” is a function that normalizes the name. "Normalizenumber" is a function for standardizing numbers including kanji (including old kanji). "Normalizepostcode" is a function for standardizing postal codes. “Municipalities” is a function for acquiring the name of a city, town, or village from an address. “Postaddress” is a function for acquiring an address from a postal code. “Prefectures” is a function for acquiring a prefecture name from an address. In this embodiment, the attribute name at the time of output of the tally result is “attribute name” + “_ (under bar)” + ““ attribute name of tally result ”in the following table”.

<Grouping>
Grouping is a function of classifying and summarizing tabular data in an arbitrary form. Specifically, it is a function of classifying and totaling records (so-called “cross tabulation”) based on elements in a predetermined column. The grouping unit 407 performs totalization based on the key attribute and the grouping attribute set by the user. A data cleansing function, which will be described later, can be used as a key column element.

  FIG. 72 is an example of input data of the grouping process. FIG. 73 is an example of output data of the grouping process. In the tabular data as shown in FIG. 72, if “device name” and “serial number” are set as key attributes, “data acquisition date” is set as group attribute, and “output number” is set as a value, The attributes are grouped for each record and for each data acquisition date, and the values of the number of output sheets are totaled.

  FIG. 74 is an example of a screen (grouping screen) for setting the grouping processing. FIG. 74 shows an area for displaying an “attribute list”, an area for setting key attributes, an area for setting group attributes, an area for setting values, and a numerical tabulation. And an area for The attribute list displays the attribute names. In the key attribute, a key attribute for grouping is specified. Note that the panels can be rearranged. After the setting, the display becomes a two-level display, the upper part displays the attribute name, and the lower part displays the contents of the grouping key setting screen name determination and data cleansing processing. The items described with reference to FIG. 74 are stored in the data storage unit 401 as setting information of the grouping process.

<Ungroup>
Ungrouping is a function of dividing integrated data into records according to a specified key sequence. At this time, the name of the element can be used as a value. Further, the record is divided according to the key attribute and the group release attribute specified by the user.

  It is assumed that the data shown in FIG. 72 is input data for the grouping release processing. FIG. 75 shows an example of output data of the grouping release processing. When “data acquisition date” is set as a key attribute and “device name” and “serial number” are set as a degrouping attribute in the tabular data as shown in FIG. 72, the degrouping is performed as shown in FIG. It is divided into records whose values are attribute names of attributes.

  FIG. 76 is an example of a screen (grouping key setting screen) for setting grouping release processing. FIG. 76 includes an area for displaying an “attribute list”, an area for setting a “key attribute”, and an area for setting a “grouping release attribute”. The attribute list displays the attribute names. In the key attribute, a key attribute for canceling grouping is specified. Note that the panels can be rearranged. In the ungrouping attribute, an attribute for ungrouping is specified. If all move is specified, all the panels in the attribute list are moved to the ungrouping attribute. In the present embodiment, the attribute names at the time of outputting the grouping release result are the attribute names of the key attributes, “sub” and “value”. The items described with reference to FIG. 76 are stored in the data storage unit 401 as setting information of the grouping release processing.

<Numbering>
Numbering is a function of adding an attribute for assigning a serial number (also referred to as “serial number”) to a record. Even when a plurality of files are combined, it is possible to add a serial number that uniquely identifies a record. For example, a serial number can be added as “No” in FIG. FIG. 77 is an example of a screen for setting numbering. FIG. 77 includes an area for inputting an attribute name. The user can give an arbitrary name to the attribute for assigning a serial number.

<Search>
The search is a function of extracting a record from a plurality of tabular data having different data formats or a combined tabular data based on a specified retrieval condition. It is also possible to sort (sort) the extracted records.

  FIG. 78 is an example of a screen for setting a search condition. The screen shown in FIG. 78 includes an area for inputting a search condition expression, an area for setting a sort condition, an area for displaying an attribute list, and an area for a search condition expression generation option (a search keyword expression And a region for supporting input to the conditional expression of the search range). As a condition for keyword search, logical product (AND), logical sum (OR), and negation (NOT) can be used. The conditions for the range search include “exact match”, “middle match”, “before”, “after”, “greater than”, “less than”, “greater than”, “less than” , “Does not match”, and “does not include” can be selected. As a sort condition, “ascending order” or “descending order” can be designated for a predetermined attribute.

<External cooperation>
The external cooperation is a function of activating another program held by the tabular data processing device. For example, the processing can be taken over by another program that reads and uses data output by the processing according to the present embodiment. In the program file, enter the absolute path of the external program. A command line option may be input as a start option. Further, a standby time (second) after activation may be designated.

<File operation>
It is also possible to delete, output, and divide a CSV file. The file division is a function of dividing tabular data into a predetermined size. For example, by physically dividing the input tabular data, the size of the input data can be reduced, and the load on the computer when performing the processing according to the present embodiment can be reduced.

<Save / change settings>
Saving and restoring the setting contents are processing for saving the setting contents defining the execution order of one or more processes and the settings for executing each process in an XML file, and processing for opening the saved file. In the present embodiment, the above-described processing can be executed again on tabular data having the same item configuration based on the information stored in the XML file. Note that the XML file is also called re-execution information. Note that the saved file can be opened, the defined process can be executed, and the definition can be modified. In the present embodiment, a series of processes stored in the XML file is also called a job.

  At the time of restoration, it may be checked whether or not the output attribute and the input attribute of each process are inconsistent. For example, when the attribute of the CSV file is increased or decreased or the attribute name is changed, an inconsistency may be detected and a warning display may be displayed on the screen.

<Contents of XML file>
FIG. 79 shows an example of the content of the XML file. The <Set> tag is a data set item, and describes the input CSV file in the tag. The <Process> tag is a process set item, and describes the process to be executed in the tag. Specifically, search, vertical connection, horizontal connection, and totaling are set. In addition, a data declaration section may be included. Hereinafter, an example of element names and attribute names used in the XML file and their contents, including tags not used in FIG. 79, will be described.

  The <Search> tag is a search item, and describes a condition for searching in a file in the tag. The <OneColumnJoin> tag is a vertical join item, and describes the settings related to vertical join in the tag. The <FullColumnJoin> tag is a horizontal join item, and describes the settings related to horizontal join in the tag. The <Summarization> tag is a tabulation item, and describes settings related to tabulation in the tag. The <Runtime> tag is an external linkage item, and describes the activation of the external program in the tag. The <Numbering> tag is a numbering item, and describes settings related to numbering in the tag. The <Group> tag is a grouping item, and describes settings related to grouping in the tag. The <Ungroup> tag is an ungrouping item, and describes settings related to ungrouping in the tag. The <Out> tag is a data out item, and describes the output CSV file in the tag. The <Comment> tag is a comment item, and a comment (character string) is described in the tag.

  In the Search tag, a logical expression (Fml) indicating a search condition is described as an XML element (element). In the case of sorting, ascending order (asc) or descending order (desc) is described in the sort (Sort) element.

  In the OneColumnJoin tag, an element indicating the setting of vertical combination is described. The target (Target) is the file content specified in the data set. Field (Field) is described by the number of attributes to be output. The attribute name is described in the field element (Cell), and the file name of the full path is described in the file name (filename) attribute (attribute). The connection (Unite) is described by the number of attributes to which the attribute connection is connected. In the cell element (UCell) to be combined, the attribute name to be combined is described, and in the file name (filename) attribute, the full path file name is described. A new field (New) describes the attribute content to be output. Fields are described by the number of targets. The attribute name of the data out is described in the field element (Cell).

  Next, tags related to the horizontal connection will be described. Horizontal join (FullColumnJoin) is a tag indicating horizontal join. In the key (Key), a key attribute is described, and in the name identification (match) attribute, identification information indicating any of a perfect match, a forward match, a backward match, and an intermediate match is described. In the cleansing attribute, any of unused telephone numbers, postal codes, dates, corporation names, numerical values, Japanese analysis, English character analysis, names, addresses, and user dictionaries is described. In the join (Add) element, identification information indicating one of deduplication (one line), duplication (one line), frequency sorting, and auto-completion, and a connection factor between the values as a duplicate value processing (jointype) attribute As the (delimiter) attribute, identification information indicating any of a line feed, a slash, a comma, and a space is described.

  Next, each element at the time of counting will be described. Summarization is a tag indicating aggregation. In addition, Key indicates a key element and is described by the number of keys. In addition, the aggregate attribute indicates a name identification judgment, exact match, year, month, day, week (year unit), week (month) The identification information indicates any one of (unit), day of the week, and user dictionary. The Statistic element indicates attribute aggregation, and is described as many as the number of attributes to be output. Further, the flag attribute is a flag, and a non-overlapping value with a numerical value from 0 to 99 is input. In addition, the collect attribute indicates the content of the aggregation, and identification information indicating any of the combination, frequency, total, average, median, mode, maximum, minimum, variance, and standard deviation is set. In addition, a StatisticNew element indicates a new attribute aggregation. The attribute names are described as many as the number of attributes to be output. The collect attribute is the content of the aggregation, and identification information indicating any of count, calculation, condition, branch (unused), numbering (discontinued), and function processing is displayed. Is set. The fml attribute indicates a tally expression. In the parentheses in the tally expression, the numbers in square brackets indicate the numbers set by the flags, and the numbers in curly braces indicate the registration order of the new attribute tally starting from 0. In the description of the tally expression, when the tally content is “calculation”, the expression of the four arithmetic operations is described. If the total is "condition", describe the conditional expression. If the total is "function processing", describe the function.

  Runtime is a tag indicating external cooperation. The absolute path of the external program is described in the File element. Further, a command line option is described in the option attribute. In the Wait attribute, the waiting time after starting the external program is described in milliseconds. A set value for waiting until the external program ends may be provided.

  Numbering is a tag indicating numbering. In the XML element, Field describes a field, and Cell describes a field element (attribute name).

  Group is a tag indicating grouping. The Key element indicates a key. Further, in the match attribute, identification information indicating any of a perfect match, a forward match, a backward match, and an intermediate match is described as the name identification determination. In the cleansing attribute, identification information indicating any of the following types of cleansing: unused, phone number, postal code, date, corporate name, numerical value, Japanese analysis, alphabet analysis, name, address, user dictionary Is done. In the GroupLabel element, an attribute name indicating a grouping attribute is described. An attribute name indicating a grouping value is described in the GroupData element. In the StatisticNew element, attribute names indicating the grouping total are described by the number of grouping values. Further, in the collect attribute, identification information indicating any of the combination, frequency, sum, average, median, mode, maximum, minimum, variance, and standard deviation is described as the grouping content.

  Ungroup is a tag indicating ungrouping. Key attribute describes the key attribute. The number of attributes is described in the Field element. The Unite element indicates connection, and is not described when there is one cell element. In the UCell element, a grouping release attribute name, which is a cell element to be combined, is described.

  Out is a tag indicating data out. In Save, describe the file path to be output. In code, describe the character code. Delete indicates file deletion, and describes a folder or file path to be deleted.

  Comment is a tag that describes a comment in each element as a sentence, for example. The processing content, the person in charge, the date and time, etc. may be described in each of the above-mentioned tags.

  Information for re-executing the processing in the present embodiment can be stored in various formats. The XML file can be displayed by an Internet browser, a text editor, or the like, so that the user can easily check the XML file. Note that the tag standard according to the present embodiment is an example, and various changes can be made as long as the setting items can be held. Further, the types of tags may be increased according to the addition of the function.

<Batch processing>
As shown in FIG. 80, the batch process is a process for executing one or more jobs according to a set schedule. The example of FIG. 80 schematically illustrates a batch process in which three jobs defined by three XML files operate continuously. Note that two or more CSV files output by two or more jobs may be used as input files for one subsequent job.

  FIG. 81 shows an example of a setting screen for batch processing. In the batch processing, the created job (XML file) is started according to a set schedule (day of week, time, period). The screen in FIG. 81 includes a batch processing field and a batch execution field (schedule and option). Further, the settings of the batch processing are stored, and when the program according to the present embodiment is started, the set contents are read. When a plurality of jobs are specified, the jobs are executed in the order of the numbers (No.). The date and time specified in the batch execution field is determined using a clock function provided in the tabular data processing device. For the start time, enter the batch execution start time. When the "Batch start" button is pressed, the batch is executed according to the settings. When the “stop” button is pressed, the batch being executed is stopped. In the schedule, the day of the week (Sun-Sat) for executing the batch is specified. For the start date, enter the batch execution start date. For the end date, enter the batch execution end date. When the mail transmission is turned on, the set destination can be notified by e-mail when the execution result is an error. When the repetition is turned ON, the repetition is executed. The items described with reference to FIG. 81 are stored in the data storage unit 401 as setting information of the batch processing.

<New job execution process>
FIG. 82 is a process flowchart illustrating an example of the new job execution process. FIG. 83 is a process field screen that graphically displays the flow of a process (processing) included in one job with icons connected by arrows. For example, the tabular data processing device 4 receives a job setting and execution request from a user via a screen as shown in FIG. The screen in FIG. 83 includes a menu bar, a button for creating a job, and a process field for displaying a process included in the job. In the example of FIG. 83, the four files are vertically combined and integrated into one table, and a series of processes for performing the horizontal combination and outputting the CSV file are displayed in the process field. The icon of each process or CSV file operation displayed in the process field is also called an “element”.

  On the screen in FIG. 83, for example, a file saved in the XML format can be opened from the “File” menu and restored to the process field. Also, a batch processing screen for setting time activation can be opened from the menu. Further, the cache may be cleared from the menu.

  Also, the screen in FIG. 83 has buttons for setting each process. Each button adds an input file to the process field in order from the left on the screen, a “File setting” button to display the file setting screen, and a “Vertical” button to add a vertical combination element to the process field and display the setting screen. Add "join" button, horizontal join element to the process field, display the setting screen "horizontal join" button, add summary element to the process field, "set" button to display the setting screen, grouping element Is added to the process field, the "Grouping" button to display the setting screen, the element of ungrouping is added to the process field, and the "Ungrouping" button to display the setting screen, the numbering element is added to the process field Click the “Numbering” button to display the setting screen, Add a comment to the process field, add a "Search" button to display the setting screen, add an external linkage element to the process field, add an "External linkage" button to display the setting screen, and delete the specified CSV file when executing the process Element to add to the process field and display the setting screen "CSV file delete" button, add the element to output the specified CSV file to the process field and display the setting screen "CSV file output" button, the specified "CSV file split" button to add an element to split CSV file to process field and display setting screen, "Execute" button to execute created process, and "Process restore" button to display XML file selection screen to restore , XML file saving image To display the "process save" button, a "process Reset" button to reset "Image Data Viewer" button to display the result of the operation image in a different screen, the contents of the process field. By pressing these buttons, the user can add a process to the job (process field) or execute the process. When an element is right-clicked on the process field, an "edit" menu for setting processing of the element, a "delete" menu for deleting the element from the process field, and the like (context menu) are displayed. Here, it is assumed that the setting screen for each process described above is also displayed when “edit” is selected. Further, the order of the elements can be changed by drag and drop. If the input / output files between the elements do not match, the processing may be such that the order of the elements cannot be changed or the data inside the elements is automatically matched.

  In addition, on the process field screen of FIG. 83, the content of the processing executed by each element may be displayed. For example, a display may be added around each element, or a display may appear when the cursor is moved to each element. The content of the display may include at least a part of the content set in XML, for example. Further, tabular data before and after the processing element may be displayed. For example, in the case of an element of the vertical combination processing, at least a part of the association of attributes is displayed. In the case of an element of the horizontal combination processing, at least a part of the key attribute, the name identification determination, and the processing of the duplicate value is displayed. If the element is a tallying process, at least a part of the key attribute and the tallying attribute is displayed. Further, the user may be able to modify and save the content. In addition, the processing element may be configured to be able to be written and modified as a comment. Further, a function (also called an image data viewer) that can display the contents of the tabular data generated in the middle may be provided. The user can set the job while verifying the output data generated in each process. FIG. 84 shows an example of a screen that displays the processing contents and the image data viewer. In the example of FIG. 84, a balloon icon is provided for each element, and a comment stored in advance is displayed when the user clicks or moves the cursor. Further, by clicking a balloon icon or the like, a “comment edit” window as shown in the lower right may be displayed so that the user can input or correct a comment. An “image data viewer” window is displayed above FIG. Here, for example, the contents of the tabular data finally output by the job being edited are displayed. Note that, for each of the elements included in the job, the configuration of the items of the input file or the output file or the content of the tabular data may be displayed (not shown).

  In this way, the user can understand the specific contents of the processing such as “horizontal combination” and “totaling”. In the job according to the present embodiment, the setting can be easily changed, but with such a display, the user can easily understand the latest specification. Therefore, for example, even when a user who is in charge of the work is changed, the handover is smooth. Further, the content of each element included in the process field, the attribute name of the CSV file of the input / output data, and the like may be printed as a simple specification or design document of the job.

  The input / output unit 402 of the tabular data processing device 4 reads tabular data (FIG. 82: S201). In this step, the input / output unit 402 reads one or more tabular data from the data storage unit 401 as a processing target of the new job. On the display device of the tabular data processing device 4, for example, a window as shown in FIG. 85 is displayed. In the example of FIG. 85, tabular data “employee transportation expenses settlement.csv” and “operating expenses.csv” are read out, and two CSV file elements are displayed in the lower process field. The contents of the CSV file are displayed in the upper image data viewer.

  In addition, the control unit 403 stores the setting of the vertical combination in the data storage unit 401 in response to the operation of the user, and the vertical combination unit 404 performs the processing of the vertical combination (S202). The joining result can be represented by one tabular data. Here, it is temporarily stored in the memory. Further, the vertical combining process may be performed during execution of the entire job after the setting of the job is completed. Here, for example, a window as shown in FIG. 86 is displayed. In the example of FIG. 86, a vertically coupled element is added to the process field. In addition, the image data viewer displays tabular data generated as a result of the vertical combination.

  Next, the control unit 403 determines whether the setting of the job is completed (S203). If an instruction to save a setting, execute a job, or end a setting operation is issued through a user operation, it is determined that the job setting has been completed. When it is determined that the job setting is not completed (S203: NO), the control unit 403 adds another process to the end of the job in response to a user operation (S204). Here, an arbitrary process such as a horizontal join and a tally is added. In addition, each process performs each process using output data of the previous process as input data. FIG. 87 shows an example in which elements for horizontal connection and CSV file output are added. The content of the output CSV file is displayed in the image data viewer of FIG.

  On the other hand, if it is determined in S203 that the setting of the job has been completed, the control unit 403 generates a script (XML file) defining settings for re-executing a process included in the job, and stores the script in the data storage unit 401. It is stored (S205). Note that the entire job may be executed only when the setting of the job is completed, or the process up to an arbitrary process included in the job may be freely executed.

  In this way, the user can perform desired processing on the tabular data, convert the tabulated data into the set format, and output the data. Further, since a script for reproducing the executed processing can be stored, the same processing as the previous processing can be applied to, for example, business data generated after the previous processing. Complicated processing can be defined more easily than macro instructions and the like included in spreadsheet software, and processing can be created and modified more easily than developing a dedicated system. Therefore, for example, it is useful when the user wants to perform a statistical analysis while performing trial and error or wants to reduce the cost of data processing.

<Batch definition processing>
Further, it is also possible to define an execution schedule of one or more jobs and automate more complicated processing. FIG. 88 is a process flowchart showing an example of a batch definition process for defining settings for a so-called batch process. For example, the tabular data processing device 4 receives a batch definition request from a user via a screen as shown in FIG.

  First, the control unit 403 of the tabular data processing device 4 adds a job (XML file) according to a user operation (FIG. 88: S211). Here, one or more jobs to be continuously executed are registered in the order of execution.

  Next, the control unit 403 registers a schedule for activating the batch processing according to the user's operation (S212). As described above for the content of the “batch process”, it is assumed that conditions such as a day of the week, a time, a period, settings for notifying an error by e-mail, and the like are input and stored in the data storage unit 401. Thus, the batch definition processing ends.

  The batch processing is executed by the batch processing unit 410 at the set date and time. In order to perform batch processing, the tabular data processing device 4 and the program according to the present embodiment are activated in advance. According to the above-described batch processing, it is possible to execute processing more complicated than individual jobs. In addition, it is possible to schedule processing to be activated without receiving a user operation, for example, at night.

<Modification>
The processing described in the present embodiment may be provided by a so-called client-server type system. That is, the tabular data processing device 4 may receive a request from a user terminal connected via a network, execute the processing described in the present embodiment, and return a result to the user terminal. The network is, for example, a computer network such as the Internet or an intranet. Note that the tabular data processing device 4 and the user terminal may be connected by a dedicated line. In addition, a plurality of user terminals may be connected to one tabular data processing device, or a plurality of tabular data processing devices 4 may be present on a network to distribute requests from one or a plurality of user terminals. May be configured to perform the processing.

  For example, it may be provided as one of the functions of a service such as so-called SaaS (Software as a Service) operating on a server. In this embodiment, tabular data such as a CSV file is processed. Therefore, if records are exported from a database managed by an external RDBMS (Relational Database Management System), for example, in CSV format, the processing described in the present embodiment can be executed. Further, the tabular data output by the processing according to the present embodiment may be converted into a predetermined format and reflected in a database or the like of an external device.

  Further, the processing flow described in the present embodiment is an example, and the processing order may be changed or the processing may be performed in parallel as long as the processing result does not change. With respect to the example of the screen, the arrangement, design, and the like can be appropriately changed.

<< Modified example of continuous processing of vertical connection and horizontal connection >>
The above-described vertical connection and horizontal connection can also be executed as a series of processing. For example, the setting of the vertical connection and the setting of the horizontal connection are stored in the storage unit in advance. The setting of the vertical combination includes data representing the correspondence between the items included in the plurality of tabular data as input data. The setting of the horizontal connection includes designation of a key item and an aggregation item that define an aggregation method. Then, the generation of the union of the tabular data (vertical join) and the aggregation of the records whose key items satisfy the predetermined condition (horizontal join) are continuously executed. Further, a key item is further designated on the vertical join setting screen described with reference to FIGS. 7A and 41, and a so-called outer join (Left join, Right join, or Full outer join) or an inner join (Inner join) is executed. You may be able to.

  FIG. 89 shows an example of a setting screen when the vertical combination and the horizontal combination are executed as a series of processing. In the setting screen of FIG. 89, a list of item names included in the input data “list.csv” (hereinafter, referred to as “list”) is displayed in the second row of the table. The third line of the table displays a list of item names included in the input data “company information.csv” (hereinafter, referred to as “company information”). The first row of the table shows the serial numbers of the items for convenience. Then, the item “workplace” of the list and the item “company name” of the company information are associated with each other. At this time, when the "edit" button of item 4 is pressed, a menu indicated by, for example, an arrow is displayed. Then, by selecting “set as combined key” or “release combined key”, the item can be set or released as a key item. The items set in the keys may be identifiable by changing the display mode of the background color, for example, as shown in FIG. Also, by selecting “outer join” or “inner join” as the “join type” from the menu, for example, the values of the respective input data are externally or internally joined for a record in which the value of the key item completely matches. Setting may be made. The processing contents of the vertical connection and the horizontal connection are as described above. Description of other items in the menu is omitted.

<<< Embodiment related to data profiling >>>
The tabular data according to the above embodiment is a CSV file or the like, and a data type is not defined for each item. Therefore, there is a possibility that low-quality tabular data having errors, omissions, duplication, and the like may be input. In such a case, the aggregated result is not desirable. In the present embodiment, a value held in association with an arbitrary item is visualized by using a statistical method, and detection and correction of inappropriate data are supported.

<Functional configuration>
FIG. 90 is an example of a functional block diagram of the tabular data processing device according to the present embodiment. 90 includes a data storage unit 501, a data input unit 502, an instruction receiving unit 503, a statistical processing unit 504, a chart output unit 505, an input data display unit 506, and a data correction unit. Unit 507. The data storage unit 501 holds tabular data to be processed in the present embodiment and tabular data corrected in the present embodiment. The data input unit 502 receives designation of tabular data to be input data by the user, and reads the designated data from the data storage unit 501. The instruction receiving unit 503 receives designation of an item (attribute) to be verified and a verification method. Further, the statistical processing unit 504 performs a statistical process on the value (element) of each record held in association with the specified item based on the specified verification method. The statistical information output unit 505 outputs the result of the statistical processing to the user. The statistical information may be output in the form of a chart such as a pie chart, a bar graph, a histogram, a bubble chart, a line chart, a scatter chart, or the like according to the specified verification method. The input data display unit 506 displays the contents of tabular data subject to statistical processing. For example, when the user specifies a group of elements included in the output statistical information, the record corresponding to the specified group is highlighted (highlighted), and the contents of the tabular data are displayed. The data correction unit 507 receives a correction instruction from the user for the displayed tabular data, and corrects the value. Here, the correction may be performed on a case-by-case basis, or the correction may be performed collectively on a plurality of elements. The tabular data processing device 5 according to the present embodiment will be described as operating standalone, but forms a client-server type system in which the tabular data processing device and user terminals are connected via a network. You may do so.

<Apparatus configuration>
The tabular data processing device 5 is also a computer as shown in FIG. The computer 1000 shown in FIG. 3 includes a CPU 1001, a main storage device 1002, an auxiliary storage device 1003, a communication IF 1004, an input / output IF 1005, a drive device 1006, and a communication bus 1007. The CPU 1001 performs processing according to the present embodiment by executing a program. The main storage device 1002 caches programs and data read by the CPU 1001, and expands a work area of the CPU. The main storage device is, specifically, a RAM, a ROM, or the like. The auxiliary storage device 1003 stores a program executed by the CPU 1001, setting information used in the present embodiment, and the like. The auxiliary storage device 1003 is, specifically, an HDD, an SSD, a flash memory, or the like. The main storage device 1002 and the auxiliary storage device 1003 function as the data storage unit 501 of the tabular data processing device 5. The communication IF 1004 transmits and receives data to and from another computer. The communication IF 1004 is, specifically, a wired or wireless network card or the like. The tabular data processing device 4 may be connected to a network via the communication IF 1004. The input / output IF 1005 is connected to the input / output device, and receives an operation from a user or presents information to the user. The input / output device is, specifically, a keyboard, a mouse, a display, a touch panel, or the like. The drive device 1006 reads data recorded on a storage medium such as a magnetic disk, a magneto-optical disk, and an optical disk, and writes data on the storage medium. The above components are connected by a communication bus 1007. A plurality of these components may be provided, or some of the components (for example, the communication IF 1004, the drive device 1006, etc.) may not be provided. Further, the input / output device may be configured integrally with the computer. Also, the program executed in the present embodiment is provided via a portable storage medium readable by the drive device 1006, a portable auxiliary storage device 1003 such as a flash memory, a communication IF 1004, and the like. It may be. Then, by the CPU 1001 executing the program, the computer shown in FIG. 3 operates as the tabular data processing device 5 shown in FIG.

<Data profiling process>
FIG. 91 is a process flowchart illustrating an example of the data profiling process. First, the data input unit of the tabular data processing device 5 reads tabular data in response to a user instruction (FIG. 91: S301). In this step, designation of one or more tabular data described in the above embodiment is received. Here, for example, two or more tabular data in which the correspondence between items is defined on the setting screen shown in FIG. 7A or FIG. 41 may be input. In this case, the data profiling process is performed on the union of the tabular data after performing the vertical joining based on the setting.

  Next, the instruction receiving unit 503 of the tabular data processing device 5 receives designation of an item to be verified and a verification method based on a user operation (S302). In this step, the "data quality tool setting" screen is displayed on the display device connected to the tabular data processing device 5, and the specification from the user is accepted. FIG. 92 shows a “data quality tool setting screen” which is an example of a screen displayed in this step. First, the user selects a category of analysis to be performed from a pull-down menu of “extraction item”. Here, a category for determining whether or not the value is described in a predetermined expression format (format) is selected. Specifically, one of “quality analysis”, “search analysis”, “pattern analysis”, or “quality management graph” is selected as the category. In the example of FIG. 92, “pattern analysis” is selected.

  Here, in the case of the category “quality analysis”, the values are grouped according to whether or not the content is the specified content, the number of values included in each group is totaled, and a chart (a visual chart such as a graph) representing the result is obtained. Output). The quality analysis includes “extraction of date data”, “extraction of unique data”, “extraction of missing data”, and “extraction of numerical data” as specific analysis methods. When "extract date data" is selected, the specification of an item is further accepted as an argument. Then, it is determined whether or not the value of the designated item is date data, and the number of each item is represented, for example, in a pie chart. The determination as to whether or not the data is date data is made by determining the date in the above-described data cleansing. GGGGyyMMdd (Heisei 250110, H250110), yyMMdd (130110), yyyyMMdd (20130110), MMGGddyyMMDD Month 10, H25 January 10, 2013), MM month dd, yyyy (January 10, 2013), GGGGyy / MM / dd (2013/01/10, H25 / 01/10), yy / MM / dd (13/01/10), yyyy / MM / dd (2013/01/10, 2013/1/10 9:06:39), yy-MM-dd (13-01-10), GGGGyy-MM -dd (2013-01-10, H25-01-10), yyyy-MM-dd (2013-01-10), MM / dd / yy (yy / 01/13) It depends on whether or not. Also, when "extract unique data" is selected, the specification of an item is further accepted as an argument. Then, it is determined whether or not the values of the designated items are unique, and the number of each is output as a pie chart, for example. Also, when "extraction of missing data" is selected, the specification of an item is further accepted as an argument. Then, it is determined whether or not the value is empty (also referred to as “Null” or “missing”), and the number of each case is output as a pie chart, for example. Also, when "extract numerical data" is selected, the specification of an item is further accepted as an argument. Then, it is determined whether or not the value of each of the designated items is a numerical value, and the number of each is output as a pie chart, for example. When a currency symbol and other units are included, it may be determined whether or not to determine a numerical value, or it may be possible to select whether or not to determine a numerical value.

  Also, in the case of the category “search analysis”, the designated values are searched and grouped according to whether they are applicable, and the number of values included in each group is totaled and a chart showing the result is displayed. The search analysis includes “extraction of backward matching data”, “extraction of forward matching data”, and “extraction by regular expression” as specific analysis methods. If “extract backward matching data” is selected, an item specification and a character string to be searched are further accepted as arguments. Then, it is determined whether or not the value of the designated item matches backward with the designated character string, and the number of each item is represented in, for example, a pie chart. Even when “extract forward matching data” is selected, the specification of an item and the character string to be searched are further accepted as arguments. Then, it is determined whether or not the value of the designated item coincides with the designated character string in front, and the number of each is represented in a pie chart, for example. If “extract with regular expression” is selected, the regular expression indicating the item designation and the search target is further accepted as an argument. Then, it is determined whether the value of the designated item matches the value represented by the designated regular expression, and the number of each item is represented, for example, in a pie chart. As the regular expression, for example, a general regular expression used in the regular expression user dictionary shown in FIG. 62 can be used.

  In the case of the category “pattern analysis”, values associated with specified items are grouped by pattern, the number of values included in each group is totaled, and a chart showing the result is displayed. The pattern analysis includes “appearance frequency” and “data patterning” as specific analysis methods. When “appearance frequency” is selected, the specification of an item is further accepted as an argument. Then, grouping is performed for each value of the designated item, and the number of cases of each value is output as a bar graph sorted by, for example, frequency. When “data patterning” is selected, the specification of an item is further accepted as an argument. For example, one character is "W", one space is "S", one numeral is "D", one multi-byte character is "A", and other symbols are values of the pattern. Then, the number of values included in each group is output in the form of a bar graph sorted by the number of cases, for example. That is, the data pattern is obtained by classifying values into alphabetic characters, numerals, multibyte characters, and the like, and classifying the order of appearance and the number of appearances.

  In addition, the category “quality management graph” divides values associated with the specified items into groups in a predetermined range, totals the number of values included in each group, and displays a chart showing the result. The search analysis uses “histogram (date)”, “histogram (unit)”, “histogram (division)”, “bubble chart”, “line chart (time series)”, and “scatter chart” as specific analysis methods. Including. When "Histogram (date)" is selected, the specification of an item is further accepted as an argument. Then, a histogram indicating the period on the X axis and the appearance frequency on the Y axis is output. The number and size of the bins are automatically adjusted. When “Histogram (unit)” is selected, designation of items and bin widths (range per bin) is further accepted as arguments. Then, a histogram indicating the value of the designated item on the X axis and the appearance frequency on the Y axis is output. The number and size of the bins on the X axis are adjusted based on the designated bin width. When “histogram (division)” is selected, the specification of items and the number of bins in the X-axis direction are further accepted as arguments. Then, a histogram indicating the value of the designated item on the X axis and the appearance frequency on the Y axis is output. When “bubble chart” is selected, items of the X axis, Y axis, and Z axis are further accepted as arguments. Then, a bubble chart having the size of the Z axis is output at the position of the X coordinate and the Y coordinate according to the value of each item. When “line chart (time series)” is selected, the specification of an X-axis item (date format) and a Y-axis item is further accepted as arguments. Then, a line chart representing the transition of the values on the Y-axis shown in order of date and time shown on the X-axis is output. When “scatter chart” is selected, an X-axis item and a Y-axis item are further accepted as arguments. Then, a scatter diagram plotted at the positions of the X coordinate and the Y coordinate is output according to the value of each item. In each case, the missing value is output as, for example, “NaN (Not a Number)”.

  When a category is selected in S302, a "profile name" indicating a specific analysis method is displayed at the bottom of the pull-down menu, and the user selects any profile name. In the example of FIG. 92, the background of the selected profile name is highlighted by hatching. That is, in the example of FIG. 92, “appearance frequency” is selected. When one of the profile names is selected, an input field for an argument is displayed at the bottom of the data quality setting screen. In the example of FIG. 92, a pull-down menu for selecting an item name is displayed. The user can select any of the item names included in the input file.

  Thereafter, the statistical processing unit 504 of the tabular data processing device 5 performs statistical processing based on the received processing instruction (S303). In the example of FIG. 92, when an item holding a gender whose value is “male” or “female” is specified as an argument, the number of cases is totaled for each value (“male” and “female”). Then, the statistical processing unit 504 generates a bar graph indicating the number of each.

  Then, the statistical information output unit 505 of the tabular data processing device 5 outputs the aggregated statistical information (S304). Here, the statistical information includes at least one of the total value calculated in S303 and the bar graph (ie, chart) generated in S303. FIG. 93 is an example of a “data quality tool” screen that displays statistical information. On the left side of FIG. 93, a horizontal bar graph showing the results of totalizing the values associated with the items specified in the input file is displayed. Specifically, the frequency (number of cases) of each man and woman is visually displayed by the length of the horizontal bar. In the upper right part of FIG. 93, a table indicating the frequency of the counting result for each value is displayed. The number of cases corresponds to the bar graph on the left, and the number of cases for each gender is displayed numerically. Various statistics are calculated for the value of the designated item, and are displayed on the lower right side of the screen. More specifically, “sum”, “maximum value”, “minimum value”, “average value”, “variance”, “standard deviation”, and “NaN (number of non-numbers)” are calculated as statistics. You. In the example of FIG. 93, “NaN” is 19 cases, and all other statistics are NaN.

  Also, the input data display unit 506 of the tabular data processing device 5 receives an input indicating that a part of the statistical information output in S304 is specified, and displays the corresponding input data (S305). In this step, if any value or a numerical value indicating any frequency is specified in the statistical information at the upper right of the screen shown in FIG. 93, a list of record numbers corresponding to the input file is displayed at the middle right of the screen. Is done. In the example of FIG. 93, “female” is designated in the upper right table, and a list of record numbers of records in which “female” is registered in the gender item is displayed in the middle right. Note that the input of designating a part of the statistical information may be performed by designating one of the horizontal bars on the left side of the screen. FIG. 93 shows an example in which the appearance frequency is analyzed. Based on the verification method instructed in S302, in S304 and S305, each total value and various charts are output.

  After that, the data correction unit 507 of the tabular data processing device 5 receives an input data correction instruction from the user and corrects the input data (S306). In this step, for example, by selecting one of the record numbers of the input data displayed in the middle part on the right side of FIG. 93, the selected record in the input data as shown in FIG. 38 can be displayed and the value can be corrected. You may do so. Further, an instruction to reflect the correction may be received collectively. When the correction is performed collectively, the value can be corrected using, for example, the above-described function processing. As described above, according to the function processing, it is possible to extract a predetermined type of character and standardize the value expression format. If a value other than “male” and “female” is registered in the item indicating the gender of the input data in the example of FIG. 93, the value appears in the bar graph on the left side and the table on the upper right side of FIG. 93. If the value is inappropriate, the user can easily identify and correct the problematic record. The corrected input data may be overwritten and recorded, or may be saved as another file with a name. The saved file can be used for the above-described vertical combining process, horizontal combining process, totaling process, and the like.

  In addition, the batch correction of the input data can be performed by a function other than the illustrated function processing. For example, a predetermined character string or missing data may be replaced with a specified character string or the like. Further, a so-called regular expression may be used as the search condition. For example, for an element registered in association with a certain item, "construction" is searched for in a backward match, such as "* construction," and the found element is designated by a designated character such as "M construction." Can be replaced with a column.

  Thus, the data profiling process ends. Note that the flowchart illustrated in FIG. 91 is an example, and some processing such as display of input data and correction of input data may not be performed. According to the data profiling process, detection and correction can be supported by visualizing inappropriate data such as errors, omissions, and duplication by a statistical method. In particular, the tabular data according to the present embodiment is DSV (Delimiter Separated Values) data in which elements included in a record are separated into columns by a delimiter. For example, since a schema (metadata) is not defined unlike RDB, it is difficult to check the format at the time of data registration. That is, inappropriate data may be registered. Therefore, it can be said that the data profiling processing according to the present embodiment is particularly useful when handling input data in the DSV format.

<Modification>
In the examples of FIGS. 92 and 93, the process is performed by designating the target item and the analysis method one by one. However, a plurality of analyzes may be performed on a plurality of items. In this case, for example, in S302 of FIG. 91, a screen as shown in FIG. 94 is displayed instead of FIG. FIG. 94 is an example of a “data profiling setting” screen that can execute a plurality of analyzes on a plurality of items. In the example of FIG. 94, analysis methods are listed in the vertical direction, and a list of item names included in the tabular data is displayed in the horizontal direction. By checking a check box provided in a cell where a row and a column intersect, it is possible to specify execution of analysis by the analysis method on the value of the item.

  In the example of FIG. 94, the user checks the check boxes other than “extract date” to set the data, and executes data profiling. In S304 of FIG. 91, for example, the result (“data profiling” screen) shown in FIG. Is displayed. In FIG. 95, the tally value is displayed for the combination of the checked item and the analysis method. In the example of FIG. 95, the statistics such as the sum, the maximum value, the minimum value, the average value, the variance, the standard deviation, and the NaN (number of non-numbers) are also calculated for each item. On such a screen, by specifying the number of records, a list of record numbers as shown in the middle right part of FIG. 93 may be displayed. Further, by selecting a list of record numbers, for example, input data as shown in FIG. 38 may be displayed, and the values may be modified. The data profiling process according to the modified example can also assist detection and correction by visualizing inappropriate data such as errors, omissions, duplications, and the like by a statistical method.

1 Tabular data processing device, 101 data storage unit, 102 setting processing unit, 103 data division unit, 104 display control unit, 105 vertical connection unit, 106 control group generation unit, 107 horizontal connection unit, 108 totaling unit, 109 output data Generation unit 2 user terminal 3 network 4 tabular data processing unit, 401 data storage unit, 402 input / output unit, 403 control unit, 404 vertical connection unit, 405 horizontal connection unit, 406 counting unit, 407 grouping unit, 408 search unit , 409 External linking unit, 410 Batch processing unit 5 Tabular data processing unit, 501 data storage unit, 502 data input unit, 503 instruction receiving unit, 504 statistical processing unit, 505 statistical information output unit, 506 input data display unit, 507 Data correction section

Claims (5)

A tabular data processing apparatus that processes tabular data in which a plurality of records including a plurality of elements corresponding to a plurality of items are registered as input data,
Were classified into groups on the basis of incorrect data in order to integrate a plurality of said tabulated data to the aggregation rules for visualizing, the number of the elements corresponding to the predetermined item, and aggregated by the group, A tally unit that outputs tally results;
From the input data, an output unit that extracts and outputs a record including an element classified into any of the groups in the aggregation result,
A correction unit that receives a request for changing an element included in the output record and changes the element;
A tabular data processing device having: The classification based on the current meter rule, tabular data processing apparatus according to claim 1 is a classification based on the representation that describes the element. A vertical combination unit configured to generate a union of the plurality of tabular data based on data indicating the correspondence between items in the plurality of tabular data,
The tabular data processing device according to claim 1, wherein the totaling unit sets a union of the tabular data as the input data. A tabular data processing method for processing, as input data, tabular data in which a plurality of records including a plurality of elements respectively corresponding to a plurality of items are registered,
Were classified into groups on the basis of incorrect data in order to integrate a plurality of said tabulated data to the aggregation rules for visualizing, the number of the elements corresponding to the predetermined item, and aggregated by the group, An aggregation step for outputting an aggregation result;
From the input data, extracting and outputting a record including an element classified into any of the groups in the aggregation result,
Receiving a request to change an element included in the output record, and changing the element;
Is a computer-implemented tabular data processing method. A tabular data processing program that processes, as input data, tabular data in which a plurality of records including a plurality of elements respectively corresponding to a plurality of items are registered,
Were classified into groups on the basis of incorrect data in order to integrate a plurality of said tabulated data to the aggregation rules for visualizing, the number of the elements corresponding to the predetermined item, and aggregated by the group, An aggregation step for outputting an aggregation result;
From the input data, an output step of extracting and outputting a record including an element classified into any of the groups in the aggregation result,
Receiving a request for change of an element included in the output record, and modifying the element;
Tabular data processing program that causes a computer to execute

Images