Method to automatically join datasets with different geographic location naming conventions

A computer-implemented method for joining data sets with mismatched geographic location naming conventions is provided. The method includes identifying, by the computer, a first dataset and a second dataset as join candidates. The method also includes joining, by the computer, the first dataset and the second dataset when, each row of the first user dataset is associated with a single geographic identifier using a geographic knowledge dataset that includes a geographic name lookup table and each row of the second user dataset is associated with a single geographic identifier using the geographic knowledge dataset, wherein the geographic name lookup table includes a plurality of alias names for each of a plurality of unique geographic locations.

BACKGROUND

The disclosure relates generally to computer systems and, more particularly, to computer automated methods for organizing data, and even more particularly, to computer automated methods for joining datasets with different geo location aliases.

A geographic (Geo) location can have many different aliases and different datasets may use different Geo location aliases to refer to the same location. For example, the United States of America may be commonly referred to as “US”, “USA”, “U.S.”, “United States”, “United States of America”, “” in various kinds of datasets.

SUMMARY

According to one illustrative embodiment, a computer-implemented method for joining data sets with mismatched geographic location naming conventions is provided. The method includes identifying, by the computer, a first dataset and a second dataset as join candidates. The method also includes joining, by the computer, the first dataset and the second dataset when, each row of the first user dataset is associated with a single geographic identifier using a geographic knowledge dataset that includes a geographic name lookup table and each row of the second user dataset is associated with a single geographic identifier using the geographic knowledge dataset, wherein the geographic name lookup table includes a plurality of alias names for each of a plurality of unique geographic locations. According to other illustrative embodiments, a data processing system and computer program product for joining data sets with mismatched naming conventions for geographic locations are provided.

DETAILED DESCRIPTION

In business analytics, more and more companies and users join multiple datasets by Geo locations or connect external data using Geo location names in order to gain a deeper insight into the data. For example, joining air pollution statistics data with hospital enrollment data can deepen the analysis and allow for the discovery of the contribution of environment pollutants to the incidence and prevalence of some diseases. As another example, in order to improve supply chain management, it may be desirable to join store inventory weather data by location to discover how weather has impacted the sales of seasonal products.

The illustrative embodiments recognize and take into account one or more considerations. For example, the illustrative embodiments recognize and take into account that it is very common that different datasets use different aliases/names to refer to the same location. Thus, the illustrative embodiments recognize and take into account that even when two datasets contain data for the same location, they could not be joined together as the geo location names are not the same. Furthermore, the illustrative embodiments recognize and take into account that it is common that two datasets do not have the same level of geo information. For example, one dataset has country and state fields, but the other dataset only has county and city fields. The illustrative embodiments recognize and take into account that users must go through lengthy manual data cleansing steps to make sure that the datasets to be joined have exactly the same name for the same location and that fields are all a match. The illustrative embodiments recognize and take into account that these extra data cleansing steps have dramatically affected productivity, limited product usability, and resulted in user dissatisfaction of the product.

The illustrative embodiments recognize and take into account that prior methods for joining datasets based on geographic location required that the field names from the tales to be joined be exactly the same and that the values are exact matches (including being case sensitive).

The illustrative embodiments recognize and take into account that it would be desirable to have a method, system, and computer program product that automatically reconciles differences in geographic location name aliases and/or mismatched sets of geographic location attributes in different data sets and joins the datasets.

In an illustrative embodiment, systems and methods of automatically joining multiple datasets in business analytic systems that use different geographic location aliases and/or have mismatched sets of geographic location attributes without extra time-consuming data cleansing steps are provided. In an illustrative embodiment, systems and methods for using a combination of techniques that provide for automatically joining datasets or for connecting to external data in which different geographic location aliases are used for the same location in the fields to be joined and/or the geographic location fields to be joined are at different levels.

The illustrative embodiments recognize and take into account that reasons joining tables in the prior art may fail is that values in the specified fields for the join do not match. Prior methods are case sensitive such that, for example, “NEW YORK” will not match “New York.” The illustrative embodiment further recognize and take into account that in the prior art, each time a user tried to join two datasets with geographic location fields, the user had to go through lengthy manual data cleansing steps to ensure that the datasets satisfy the strict prerequisites, which significantly impacts the user's productivity and fostered user dissatisfaction.

Various illustrative embodiments use a combination of techniques that provide an approach to automatically join datasets or connect to external data in which different geographic location aliases are used for the same location in the fields to be joined and/or the geographic location fields to be joined are at different levels. As used herein, different levels refer to the different hierarchies with which a given geographic location corresponds. For example, New York City refers to the city, but also inherently specifies that the state is the state of New York in the country of the United States of America on the North American Continent in the Western hemisphere. Thus, in illustrative embodiments, one dataset may refer to locations at the city level while a different dataset may only refer to locations at the state or national level.

Various illustrative embodiments provide a unique way to organize geographic data including aliases, relationships and associate these data using abstract logical levels (concepts) which are stored in a datastore, such as a relational database. As used herein, an alias refers to the different names used to refer to the same unique geographic location. For example, “New York City” may also be referred to as “New York,” “NEW YORK”, “NY City,” “NY”, “the Big Apple”, etc. Each of these aliases uniquely identifies the same geographic location. In an illustrative embodiment, when a user dataset is processed, the columns are classified into concepts and columns are recommended as join candidates based on the concepts of the column. Illustrative embodiments automatically normalize the different geographic aliases in user datasets to a canonical form and pads mismatched fields using a series of techniques that leverage the data stored in a geographic knowledge database. After that, in illustrative embodiments, a join query is automatically generated to connect user datasets using the geographic knowledge table as a bridge.

With illustrative disclosed embodiments, a business analytics system can allow a user to easily broaden and deepen a user's analysis and discover more insights by combining two or more datasets by geographic location data that have mismatched aliases and/or mismatched levels without having to go through lengthy and time-consuming data cleansing steps, thereby dramatically improving user productivity and providing better user satisfaction.

With reference now to the figures and, in particular, with reference toFIG. 1, a pictorial representation of a network of data processing systems is depicted in which illustrative embodiments may be implemented. Network data processing system100is a network of computers in which the illustrative embodiments may be implemented. Network data processing system100contains network102, which is the medium used to provide communications links between various devices and computers connected together within network data processing system100. Network102may include connections, such as wire, wireless communication links, or fiber optic cables.

In the depicted example, server computer104and server computer106connect to network102along with storage unit108. In addition, client devices110connect to network102. As depicted, client devices110include client computer112, client computer114, and client computer116. Further, client devices110can also include other types of client devices such mobile phone118, tablet computer120, smart speaker122, and smart glasses124. Client devices110can be, for example, computers, workstations, or network computers. In the depicted example, server computer104provides information, such as boot files, operating system images, and applications to client devices110. In this illustrative example, server computer104, server computer106, storage unit108, and client devices110are network devices that connect to network102in which network102is the communications media for these network devices.

Client devices110are clients to server computer104in this example. Network data processing system100may include additional server computers, client computers, and other devices not shown. Client devices110connect to network102utilizing at least one of wired, optical fiber, or wireless connections.

Program code located in network data processing system100can be stored on a computer-recordable storage medium and downloaded to a data processing system or other device for use. For example, program code can be stored on a computer-recordable storage medium on server computer104and downloaded to client devices110over network102for use on client devices110.

As depicted, multiple structured datasets arranged into columns and rows and containing geographic location data are stored on storage unit108. An analyzer on, for example, server computer104or client computer112analyzes the datasets to determine whether the datasets can be joined.

Turning now toFIG. 2, a diagram of a computer for joining datasets having mismatched aliases and/or mismatched hierarchical geographic levels is depicted in accordance with an illustrative embodiment. Computer system202includes a geographic (Geo) knowledge database204and an analyzer212. Geo knowledge database204includes a geo knowledge table206, a geo name lookup table208and a geo logical hierarchy table210. The Analyzer212uses the geo knowledge database204to join first dataset214and second dataset216to produce a joined dataset218. The first dataset214and the second dataset216may have mismatched aliases and/or mismatched hierarchical geographic levels.

Turning now toFIG. 3, a diagram of a geo knowledge database is depicted in accordance with an illustrative embodiment. Geo knowledge database300may be implemented as geo knowledge database204depicted inFIG. 2. Geo knowledge database300includes geo name lookup table302, geo knowledge table304, geo logical hierarchy306and a geo knowledge table_1308.

The geo knowledge database preparation is typically performed once and then utilized multiple times each time two datasets are joined. In operation 1, a set of concepts is defined in an ontology to represent the abstract logical levels in Geo Hierarchy, which can be commonly used by all countries.

In operation 2, a Geo knowledge table304is created in which each row is identified by a unique location identifier (Geo ID), which can be treated as the standardized name of a location. The other fields are the additional features describing the location, such as administrative level, population, etc.

In operation 3, a Geo name lookup tables302,308are created in which each row is uniquely identified by a compound primary key, which contains three fields, Geo Name, Geo ID, and Concept. Each alias for a location tupled with the Geo ID of the location and its abstract logical level (concept) is inserted as a record in the table. Geo Name field stores all the variations of the name for a location, including different cases, with or without diacritics, name in different languages. It can easily add new names or remove obsoleted names. A location can have multiple abstract logical levels (concepts). For example, Singapore can be both City and Country, each of which will be a record in Geo Name Lookup table.

In operation 4, a Geo Logical Hierarchy table306is created in which each row is uniquely identified by a compound primary key, which contains three fields, Geo ID, Ancestor Geo ID, Ancestor level Concept; the ancestors can be parent, grand-parent, and further level, each of which will be a record in Geo Logical Hierarchy table306. The ancestors, as used herein, refer to higher levels that incorporate multiple lower levels within themselves. For example, a state or province is an ancestor to multiple cities and a state or province may be the child of a country.

The data is organized in such a way that the tables can be queried or joined at any abstract logical levels in the geo hierarchy.

Once the geo knowledge database300has been created, the geo knowledge database300can be used to join multiple datasets.

Turning now toFIG. 4, a flowchart of a join process is depicted in accordance with an illustrative embodiment. The method400begins with concept classification on columns of user dataset is performed (operation402). In user dataset, for each column with data type as text, use the distinct value to query Geo name lookup table and count the occurrence of each concept. Then use the frequencies of concepts as an important factor to classify the concept of the column.

Next, a join recommendation is performed (operation404). Even if two datasets have completely different column names for Geo data fields or mismatched Geo data fields, as long as the columns satisfy a set of rules for join based on concepts, the two datasets can be identified as join candidates.

Next, a canonical geo name resolution and mismatched level padding is performed (operation406). For ambiguity detection, in operation a), join a user dataset with Geo Name Lookup table to check if each row in user dataset can be associated with single Geo ID. If yes, go to operation c; otherwise, it indicates that an ambiguity has been detected, and goes to operation b) disambiguation. In operation b), where disambiguation is performed if ambiguity is detected, in an illustrative embodiment, a combination of methods to disambiguate the ambiguous rows are used. These methods may include automatic unique geo identifier (ID) resolution, such as clustering algorithms to find common ancestor, name preference rank bias, population size bias, administration level bias, etc. these methods may also include user clarification. In operation c), canonical geo name column padding is performed. After operation a) and/or operation b), geo location information in each row contains enough information to uniquely identify single Geo ID. Illustrative embodiments then create a derived view, which enhances user dataset with Geo ID column. The Geo ID column can be used as canonical Geo name to join other datasets. In operation d), geo fields padding is performed if geo fields to be joined have mismatched levels. As unique Geo ID has been resolved for each location, if Geo fields to be joined have mismatched levels, this missing level can be automatically resolved by joining the user data with Geo logical hierarchy table using existing Geo information in the derived view.

Next, join query generation is performed (operation408). As different Geo aliases have been normalized to Geo ID and mismatched fields have been padded, join query can be generated using Geo Knowledge table as the bridge table to connect two user datasets.

The following examples may aid in understanding the various illustrative embodiments. Consider the data in tables 1 and 2 below for the various examples. A snippet of dataset Sales contains sales information for each branch in the company is shown in Table 1 below:

A snippet of dataset Expenses that contains all the expense information for each site in the company is shown in Table 2 below:

In example 1, a user wants to automatically join the two datasets to calculate earnings. Without the disclosed methods and systems, these two tables cannot be joined directly since different names are used for the same city. Using disclosed methods and systems, from operation402, dataset sales column branch has been tagged with the concept “city.” Dataset expense columns “branch country,” branch state,” and “branch city” have been tagged with the concept “country,” “state,” and “city” respectively. From operation404, a join recommendation is generated to suggest “sales.branch” to join to “expense.branch city” at the “city” abstract logical level. From operation406, the system detects ambiguity on “London” in dataset “sales” shown in Table 1 since the city “London” is used in multiple different countries. After applying a combination of disambiguation algorithms, “London” is resolved as “London in the United Kingdom.” After canonical geo name column padding, the “derived sales view” is created. A section of a materialized view looks as shown in Table 3 below:

In the data set “Expenses,” shown in Table 2, no ambiguity is detected. Thus, after canonical geo name column padding, the “derived expenses view” is created. A section of this view is shown in Table 4 below:

From operation408, an SQL query can be generated to join “Derived Sales” and “Derived Expenses” on Geo ID (city).

In example 2, a user wants to automatically join the two datasets to calculate earnings at the state level. Without the disclosed systems and methods, these two tables cannot be joined directly since sales dataset does not have state information. However, using the illustrative embodiments of the disclosed systems and methods, many operations are the same as in Example 1 above except that from operation406, after the geo fields padding, if geo fields are to be joined having mismatched levels, the derived sales view a portion of which is shown in Table 3 above, is padded with the “state” level resulting in a view a portion of which is shown in Table 5 below:

From operation408, a SQL query can be generated to join “derived sales” and “derived expenses” on “geo ID (state).”

Turning now toFIG. 5, a flowchart of a method for joining two datasets is depicted in accordance with an illustrative embodiment. The method500begins by identifying, by the computer, a first dataset and a second dataset as join candidates (operation502). Identifying the datasets as join candidates may be performed as described above in method400shown inFIG. 4. Next, the method500proceeds by joining, by the computer, the first dataset and the second dataset when, each row of the first user dataset is associated with a single geographic identifier using a geographic knowledge dataset including at least a geographic name lookup table and each row of the second user dataset is associated with a single geographic identifier using the geographic knowledge dataset, wherein the geographic name lookup table includes a plurality of alias names for each of a plurality of unique geographic locations (operation504).

In illustrative embodiments, the method includes, when an ambiguous geolocation is determined in a first row in one of the first and second datasets, disambiguating using a clustering algorithm to find a common ancestor, name preference rank bias, population size bias, or administration level bias and associating the first row with a single geographic identifier. In illustrative embodiments, the method also includes adding a geographic identifier column to the joined datasets to create a derived view, wherein an entry for the geographic identifier column for a row in the joined datasets includes a unique geographic identifier for the row derived from the geographic name lookup table wherein the unique geographic identifier corresponds to multiple geographic aliases describing a same geographic location. In illustrative embodiments, the method also includes, when a first geographic field in the first dataset is mismatched with a second geographic field in the second dataset, automatically resolving a missing level by joining the first and second datasets with a geographic logical hierarchy table using existing geographic information in the derived view. In illustrative embodiments, the joining includes joining the first and second datasets using a geographic knowledge table as a bridge table to connect the first and second datasets. In illustrative embodiments, the geographic knowledge dataset further includes a geographic knowledge table, a geographic logical hierarchy table. In illustrative embodiments, the geographic knowledge table includes a plurality of rows in which each row is identified by a unique location identifier which is treated as a standardized name of a geographic location and wherein other fields in each row include identifying features of the geographic location. In illustrative embodiments, the identifying features include at least one of administrative level, population, nation, city province, state, county, and longitude and latitude. In illustrative embodiments, the geographic name lookup table includes a plurality of rows wherein each row is identified by a compound primary key which contains three fields, wherein the three fields include a geographic name, a geographic identifier, and a concept, wherein the concept includes an abstract logical level, wherein each alias for a location is tupled with the geographic identifier of the location and the concept, and wherein the geographic name field stores a plurality of variations of the name for a location. In illustrative embodiments, the geographic logical hierarchy table includes a plurality of rows, wherein each row is uniquely identified by a compound primary key, wherein the compound primary key includes at least three fields, wherein the at least three fields include a geographic identifier, an ancestor geographic identifier, and an ancestor level concept.

Turning now toFIG. 6, a block diagram of a data processing system is depicted in accordance with an illustrative embodiment. Data processing system600can be used to implement server computer104, server computer106, and/or one or more of client devices110, inFIG. 1. Data processing system600can also be used to implement computer system202inFIG. 2. Additionally, data processing system600can be used to implement the method400depicted inFIG. 4and/or method500depicted inFIG. 5. In this illustrative example, data processing system600includes communications framework602, which provides communications between processor unit604, memory606, persistent storage608, communications unit610, input/output (I/O) unit612, and display614. In this example, communications framework602takes the form of a bus system.

Processor unit604serves to execute instructions for software that can be loaded into memory606. Processor unit604includes one or more processors. For example, processor unit604can be selected from at least one of a multicore processor, a central processing unit (CPU), a graphics processing unit (GPU), a physics processing unit (PPU), a digital signal processor (DSP), a network processor, or some other suitable type of processor. For example, further, processor unit604can may be implemented using one or more heterogeneous processor systems in which a main processor is present with secondary processors on a single chip. As another illustrative example, processor unit604can be a symmetric multi-processor system containing multiple processors of the same type on a single chip.

Memory606and persistent storage608are examples of storage devices616. A storage device is any piece of hardware that is capable of storing information, such as, for example, without limitation, at least one of data, program code in functional form, or other suitable information either on a temporary basis, a permanent basis, or both on a temporary basis and a permanent basis. Storage devices616may also be referred to as computer-readable storage devices in these illustrative examples. Memory606, in these examples, can be, for example, a random-access memory or any other suitable volatile or non-volatile storage device. Persistent storage608may take various forms, depending on the particular implementation.

For example, persistent storage608may contain one or more components or devices. For example, persistent storage608can be a hard drive, a solid-state drive (SSD), a flash memory, a rewritable optical disk, a rewritable magnetic tape, or some combination of the above. The media used by persistent storage608also can be removable. For example, a removable hard drive can be used for persistent storage608.

Communications unit610, in these illustrative examples, provides for communications with other data processing systems or devices. In these illustrative examples, communications unit610is a network interface card.

Input/output unit612allows for input and output of data with other devices that can be connected to data processing system600. For example, input/output unit612may provide a connection for user input through at least one of a keyboard, a mouse, or some other suitable input device. Further, input/output unit612may send output to a printer. Display614provides a mechanism to display information to a user.

Instructions for at least one of the operating system, applications, or programs can be located in storage devices616, which are in communication with processor unit604through communications framework602. The processes of the different embodiments can be performed by processor unit604using computer-implemented instructions, which may be located in a memory, such as memory606.

These instructions are referred to as program code, computer usable program code, or computer-readable program code that can be read and executed by a processor in processor unit604. The program code in the different embodiments can be embodied on different physical or computer-readable storage media, such as memory606or persistent storage608.

Program code618is located in a functional form on computer-readable media620that is selectively removable and can be loaded onto or transferred to data processing system600for execution by processor unit604. Program code618and computer-readable media620form computer program product622in these illustrative examples. In the illustrative example, computer-readable media620is computer-readable storage media624.

In these illustrative examples, computer-readable storage media624is a physical or tangible storage device used to store program code618rather than a medium that propagates or transmits program code618.

Alternatively, program code618can be transferred to data processing system600using a computer-readable signal media. The computer-readable signal media can be, for example, a propagated data signal containing program code618. For example, the computer-readable signal media can be at least one of an electromagnetic signal, an optical signal, or any other suitable type of signal. These signals can be transmitted over connections, such as wireless connections, optical fiber cable, coaxial cable, a wire, or any other suitable type of connection.

The different components illustrated for data processing system600are not meant to provide architectural limitations to the manner in which different embodiments can be implemented. In some illustrative examples, one or more of the components may be incorporated in or otherwise form a portion of, another component. For example, memory606, or portions thereof, may be incorporated in processor unit604in some illustrative examples. The different illustrative embodiments can be implemented in a data processing system including components in addition to or in place of those illustrated for data processing system600. Other components shown inFIG. 6can be varied from the illustrative examples shown. The different embodiments can be implemented using any hardware device or system capable of running program code618.

Thus, illustrative embodiments of the present disclosure provide a computer implemented method, computer system, and computer program product for generating lyrics for poetic compositions. The method determines a theme randomly or from input and, from the theme, the method determines words that are associated with the theme and words that rhyme with the associated words according to a star schema approach. The method provides a filter and other mechanisms to tailor the output to fit a specified sentiment, topic, or other feature.