Data deduplication: using entity resolution on the Transparence Santé database

The challenge of identifying beneficiaries

#

The healthcare ecosystem brings together several types of actors: pharmaceutical laboratories, healthcare professionals and patient associations. These actors maintain financial and influence relationships with each other that can create conflicts of interest.

To limit these risks, the Transparence Santé database requires healthcare companies to publicly declare the benefits and remuneration they provide to healthcare professionals and other sector actors. This database is used by the EurosForDocs project to analyze these financial flows.

But to precisely analyze these interest ties, you must be able to unambiguously identify each beneficiary of benefits or remuneration. However, the government platform gives companies great freedom in drafting their declarations. This flexibility leads to a practical problem: the same beneficiary can be described in many different ways depending on the companies. It can then be complicated to group all declarations concerning them.

The ideal situation for grouping the different declarations relating to the same beneficiary would be the presence of a unique national identifier. Healthcare professionals have exactly such a number: the RPPS number. An older reference system (the ADELI number) is also available and can be converted to RPPS for most professions. Scientific societies and patient associations can be identified by their national association number (RNA) or SIREN number.

In practice, declarations are far from meeting expected quality standards. The filing rate of a SIREN or RNA number for legal entities is only 50%. The situation was similar for healthcare professionals, but a platform update made it possible to increase the filing rate to 90%.

One of EurosForDocs’s reprocessing objectives is to merge all beneficiary accounts relating to the same physical entity, even in the absence of a national identifier. To do this, we have implemented a four-step system:

1. Correct incoherent identifiers. We correct declarations that have RPPS numbers but for which the names, first names and profession declared are incoherent compared to information in the official RPPS directory. This step is the subject of a dedicated article.

2. Assign identifiers. We attempt to find the missing identifier of a beneficiary by searching for it in an official directory (RPPS for healthcare professionals, SIREN/RNA for legal entities).

3. Search for direct links. When identification in a directory fails, we search for direct matches between accounts by comparing their information (name, first name, profession, etc.).

4. Create entities. An algorithm automatically groups all accounts that are linked to each other, directly or through other accounts to recreate the physical entities to which they correspond.

Account matching methods

#

As mentioned in the previous section, the ideal situation consists of having a national identifier for each beneficiary account. When none is present, we seek to find one through different sources of truth.

Identifier re-assignment

#

Healthcare professionals are the simplest cases to handle. The health directory (or RPPS) lists all healthcare professionals practicing in France, sometimes with different variants for their name, first name or postal code. It also includes for each professional their RPPS and/or ADELI number. We can thus attempt to directly assign an RPPS number to healthcare professionals declared without any identifier.

For each of these professionals, we search the directory for a person with the same name, first name and profession. If there is only one RPPS number in the directory with this information (thus no homonyms that could create ambiguity), we assign it to this account.

Several variants of this logic are tested to adjust tolerance thresholds on the information considered and try to overcome homonym problems. For example, we can try to use the postal code if it is declared to resolve ambiguities, or work on subparts of a compound name. Unlike the techniques applied in this article, we do not assign an RPPS number to an account if we do not obtain an exact match for a given approach. We thus choose not to be able to handle simple typographical errors, which would open the door to too many false positives. Among the 2 million accounts of professionals declared without RPPS, 60% are assigned an identifier at the end of this procedure.

In theory, we should be able to apply the same logic to legal entities through association and SIREN directories. Unfortunately, the only information that is truly usable is the entity name. While you would expect to find the exact name for a natural person (sometimes with maiden name/married name issues), an association name allows for greater variations without questioning the intelligibility of the declaration. For example, in this case study, the Clinical and Therapeutic Association for Children of the Val de Marne was identified under different mentions, ranging from the full name to the acronym ACTIV through condensed versions such as “Ass Cliniq Therap Infantil”. Such diversity does not allow applying an assignment logic similar to that for natural persons.

During data exploration, we also identified special cases. Some companies declare identification numbers that do not appear to correspond to national repositories, but seem to be uniquely linked to the same beneficiary. We deduced that these numbers must be internal identification numbers. We then use these numbers (by company) as national identifiers to link different accounts of the same entity together.

Finally, some account duplicates resist all our methods (including ones not yet presented). These duplicates are generally identified by users during their exploration of certain specific people or associations. We then implemented a manual modification system that allows assigning identifiers to beneficiary accounts.

Direct link between accounts

#

A second strategy for merging accounts consists of proving that two beneficiary accounts refer to the same entity, even in the absence of an identifier. In the ideal case, the first account has an identifier that will be propagated to the second. When no account has an identifier, this link nonetheless allows merging the two accounts, hoping that another direct link with a third account will allow subsequent identification.

Healthcare professionals

#

The first strategy implemented applies to healthcare professionals. We seek to group beneficiary accounts that have the same name, first name, profession and postal code, but may be declared by different companies.

However, this approach can produce false positives, even with the requirement of an entered postal code. Imagine for example a case where an account without RPPS coexists with two other accounts sharing the same name, first name, profession and postal code, but possessing distinct RPPS numbers. In this situation, assigning the correct RPPS becomes impossible. After completing RPPS as much as possible in the previous steps, we apply an additional constraint: each group of homonymous accounts can contain only one RPPS number. This limitation significantly reduces the risks of assignment error.

Legal entities

#

Regarding legal entities, the strategy is similar but can only be based on the entity name. Here, the risk of merging homonymous accounts is much higher: it is not uncommon for different entities to have the same acronym and for companies to declare only this one. We also identified a substantial number of declarations where the beneficiary name is a non-indicative keyword: “Hospital” for example.

These different cases make it important to have a mechanism for limiting false positives. As for healthcare professionals, we require a group of homonymous beneficiaries to have no more than one identifier among them to allow accounts without identifiers to receive one. Furthermore, we prohibit links between accounts whose names are non-indicative words.

Manual links

#

Finally, certain specific studies conducted by our users have reported links between accounts for which no identifier is entered. Most of the time, we try to manually assign them identifiers as mentioned in the previous section.

Unfortunately, it can be difficult to obtain an identifier for certain entities. For example, some inter-entity working groups do not technically have legal existence but receive funding for their activities. Even if these funds are surely attributed accountingly to legal entities that participate in these groups, the declaration mentions only the group, which will then have no identifier. In these particular cases, we then create a new database of links between accounts that allows linking accounts two by two.

Creating entities

#

The previous section presented us with several cases of links between accounts. Depending on the quality of declarations, these links can be numerous and diverse. We thus need a strategy to reconstitute unique and quality entities from this set of declared accounts and the links between them.

Grouping by identifier

#

Consider the simplest case where multiple accounts are linked through the same identifier. We can then group all accounts with the same identifier. For example, imagine that the following accounts are all linked to the same RPPS number:

We seek to create an entity for this pharmacist that is the best possible combination of information contained in all accounts. Here, the name poses no problem as it is identical everywhere. The final name of our entity will therefore be Dupont.

Our deduplication strategy allows us to enrich our data with the profession of this person. Indeed, the declarations corresponding to beneficiary accounts 2 and 3 have not documented a profession. Thanks to linking these 3 accounts through the RPPS number, we can assign a profession to all accounts, which will improve the quality of declarations without this information.

This system also allows us to unify certain variable information. For example, account number 3 has a typo in the first name: the h is missing. It could just as well provide a variant of the first name: we have cases of Marie-Christine simply declared as Marie or Christine. There is thus naturally variability in the declared data. We can choose the most relevant variant to create our final entity. Thus, all declarations with incorrect and/or variant information can be modified to harmonize all declarations linked to this person.

To link a beneficiary account to an entity, it is preferable to create an ID for this entity. It is important to have an identifier that has a certain stability. Indeed, we receive new declarations or modifications daily. We want an identifier that changes little when new declarations are created, so that users can easily find the beneficiaries they are interested in from one day to the next.

The solution we chose is to use the smallest ID present among duplicate accounts. The addition of new accounts to the list of duplicates will not change the reference ID for the group. Updating declarations or improving merge logic can change the ID, but the proportion of affected accounts will be relatively small.

Impact of varied relationships

#

The logic of grouping by unique identifier becomes more complex when multiple types of links coexist between accounts.

Consider the following example: accounts 0 and 1 share the same RPPS number, account 2 is linked to account 1 via a company’s internal identifier, and account 3 is directly linked to account 1 by a data match.

If we limit ourselves to RPPS grouping (red box), we would exclude accounts 2 and 3 from the final entity, while they correspond to the same beneficiary. The solution is to extend the grouping recursively: all accounts linked to a member of the initial group must be included in the final entity. This logic applies iteratively until no new accounts can be added.

Once all linked accounts have been identified, we apply the same deduplication strategy as before to determine the name, first name and other final characteristics of the entity.

Grouping sub-components

#

This iterative logic of needing to search for new accounts linked to a group member through different types of links is fairly complex to implement when placing ourselves in the context of data in the form of an Excel table; which we implicitly did in this article and previous ones about EurosForDocs. However, a paradigm shift will allow us to implement this logic extremely simply. This paradigm is data representation in network form.

A network consists of a list of entities (nodes) that can be linked together through different mechanisms. The concept of networks is fairly well-known today through social networks, where people are connected by “friendship” links on Facebook or users connected to products and companies in an e-commerce database for example.

To create our network of duplicates, we must ask ourselves two main questions: what will the nodes of the network consist of and what will the links between them be. First, our network will contain all beneficiary accounts as nodes. However, we will also add as nodes all identifiers present in the database. We will thus have a set of nodes corresponding to different SIRENs, another set for RPPS numbers, etc… The companies’ internal identifiers that we mentioned earlier will also generate nodes.

Next we will seek to link these nodes together. All the business rules detailed earlier will allow us to link nodes together. The presence of a declared (or found) RPPS number allows linking the node corresponding to this beneficiary with the node corresponding to their RPPS number. Direct relationships between accounts can also be represented by links between their respective nodes. This is the representation that was used in the previous example. Once this network is created, we get something that will resemble the following image, but with approximately 8 million nodes and 8 million links between these nodes.

If you look carefully at the network, you realize that nodes are naturally organized into 4 groups. In each of these groups, beneficiaries are linked to each other either directly or through a common link with an identification number. The group at the bottom left shows clearly that most nodes organize around a SIREN number and an RNA number, but with nodes on the left grouped around node 37, itself linked to the rest of the group via direct relationships with other beneficiary accounts. Each of these groups corresponds to all nodes linked to each other more or less closely; that is, to a final entity.

Networks as mathematical objects possess a vast range of algorithms that make it possible to explore them and deduce their properties. In particular, the connected components algorithm will explore a network and distribute nodes into groups so that it is always possible to reach one node in the group from another, possibly passing through other intermediate nodes, by following the links. Conversely, two nodes not belonging to the same group will not be accessible to each other.

This new formalism is then used to extract groups of beneficiary accounts that will make it possible to create entities. Since the connected components problem is relatively old, we have access to optimized implementations for graph-type data. This makes the procedure much more efficient than manual code; potentially in a formalism and technology not intended for this type of problem. Being able to call a well-known algorithm also reduces the amount of code (since we can call a dedicated tool) and facilitates documentation. Ultimately, we had 7.1 million beneficiary accounts that we were able to deduplicate into 2.1 million entities.

Managing inconsistencies

#

The connected components algorithm reveals an important problem: some groups contain multiple identifiers of the same type (multiple RPPS or multiple SIREN). This situation indicates an error in the data, as each identifier should be unique for an entity.

In the example illustrated, the bottom group contains two distinct RPPS numbers (77 and 75), suggesting that two different entities have been incorrectly linked. Beneficiary account 2 likely presents an incorrect link. More complex situations exist, such as the group on the left where SIREN nodes are found on the periphery of the structure.

Entity creation through connected components has two advantages. It allows efficient grouping of linked accounts while automatically identifying groups with inconsistencies. To limit processing errors, we choose not to merge accounts belonging to an inconsistent group, but to mark them for later processing, automatic or manual.

Conclusion

#

The deduplication of beneficiary accounts from the Transparence Santé database requires a progressive approach combining identifier assignment, direct matches and algorithmic grouping. Graph modeling transforms this complex problem into a standard connected components algorithm, making it possible to efficiently process 7.1 million accounts to obtain 2.1 million distinct entities.

This methodology provides a structured framework for entity resolution: start with reliable identifiers, expand with direct matches, then use graph theory to manage multiple relationships. Automatic detection of inconsistencies maintains data quality while identifying cases requiring manual intervention.