View or edit on GitHub

This page is synchronized from doc/Traders-Developer-Guide.md. Last modified on 2025-12-09 00:30 CET by Trase Admin. Please view or edit the original file there; changes should be reflected here after a midnight build (CET time), or manually triggering it with a GitHub action (link).

Traders - Developer guide

[!NOTE] There is a recorded presentation about the way that traders are stored in Trase under Google Drive > 2. Operations > Internal Webinars and Training > Internal Webinar Series > Trase speakers > Traders in Trase - Clement.mp4

• 1. Trader nodes
• 1.1 Types of traders
• 1.2 Alternate trader names
• 1.3 Unique identifiers
• 1.4 Trader countries
• 1.5 Time-dependent group membership
• 1.6 Ambiguous trader labels
• 1.7 Constraints
• 1.8 Node codes
• 2. Finding existing trader nodes in the database
• 3. Creating new traders
• 4. Improving the traders hierarchy
• 4.1 Moving a label to a new trader
• 4.2 Moving a trader to a new group
• 4.3 Full example
• 5. Solving duplicated trader labels/names/groups

1. Trader nodes

1.1 Types of traders

Traders, groups, and labels all exist as nodes in the main.nodes table, of type TRADER. These three types are distinguished based on the level and sub-type:

Type	Node Sub-Type	Level
Trader Group	TRADER GROUP	0
Trader	TRADER	1
Trader Label	TRADER LABEL	2

The relationship between traders and groups is stored in database table main.trader_groups. The relationship between traders and labels is stored in database table main.trader_labels.

1.2 Alternate trader names

Traders often have multiple names, due to alternate spellings, or even typos in the datasets. These alternate spellings, or "labels", are added as a third type of trader node in the database, with level 2, and attached to the traders they are referring to.

So for instance, a complete trader hierarchy in the database will be comprised of the following 3 nodes:

level 0:  [11111111] QWERTYUIOP INC [TRADER GROUP]
            |  
level 1:    |-- [11111112] QWERTYUIOP INC [TRADER]
            |     |
level 2:    |     |--     [11111113] QWERTYUIOP INC

When different spellings for trader QWERTYUIOP INC are discovered, they can be added as labels:

[11111111] QWERTYUIOP INC [TRADER GROUP]
  |  
  |-- [11111112] QWERTYUIOP INC [TRADER]
  |     |
  |     |--     [11111113] QWERTYUIOP INC
  |     |--     [11111114] QWERTYUIOP INCORPORATED
  |     |--     [11111115] QWERTYUIOP ICN
  |     |--     [11111116] QWERTYUIOP
  |     |--     [11111116] QWERTYUI0P INC
  |     |--     [11111116] QWERTYUIP INC
  |     |--     [11111116] QWERTYUUIPO INC

The relationship between traders and labels is stored in database table main.trader_labels.

1.3 Unique identifiers

Unique identifiers such as country-specific tax numbers can be used to uniquely identify legal entities. When such data is available, we can generate a unique trase id following the pattern {country_iso_2}-TRADER-{tax_id}. For instance in Brazil, the first 8 digits of the 14-digit CNPJ number uniquely identify companies, so the trase id for Brazilian companies is BR-TRADER-{cnpj_8}.

The Trase ID is attached to level-1 traders.

1.4 Trader countries

We consider a trader to be "operating" in a country in two different ways:

1. Inferred from the first two digits of the Trase ID, which is interpreted as the two-letter country code. For example a trader with the Trase ID BR-12345678 is assumed to operate in Brazil. If a trader does not have a Trase ID then it is not associated with any specific country, in this sense.
2. It is part of a supply chain (flows) dataset. That is to say, it appears in the flow paths of a supply chain dataset. You can use the function get_trader_activities to query this.

These are two independent characteristics: is it possible for the Trase ID of a trader to start with BR- (indicating Brazil) yet for the trader to also appear in a supply chains dataset for Paraguay.

1.5 Time-dependent group membership

In order to account for acquisitions of companies, the membership of a trader in a group can be variable in time. The time range at which a trader is a member of a group is stored in table columns main.trader_groups.time_start and main.trader_groups.time_end.

1.6 Ambiguous trader labels

Some trader names can refer to multiple companies; for instance different companies with the same name in different countries, or companies named after their owner in the same country.

In this case, disambiguation can be performed by using the country of the trader, or unique identifiers based on country-specific tax codes.

1.7 Constraints

The system assumes the below are always true. Most of these are enforced as database constraints.

• Trader label nodes are unique. We cannot have two trader labels with the same name.
• Trader nodes are not unique. Multiple trader nodes can have the same name; they correspond to different entities, such as companies with the same name in different countries, or individual traders with the same name in the same country.
• Trader groups are unique. We cannot have two trader groups with the same name.
• A trader label is associated with one or more traders. We cannot have a trader label not attached to a trader. A trader label attached to multiple traders corresponds to multiple companies with the same name, either within the same country (in which case they have different trase ids, which are used to disambiguate them), or in different countries (in which case they can be disambiguated with trase id, or with activities if the trase id is not defined).
• A trader connected to at least one label must belong to a trader group. This means that all labels in the database are connected to a trader and a group.
• The name of a trader with labels is always the name of one of its labels.
• The name of a group is always the name of one of the traders that belong to the group.

1.8 Node codes

Like any other node, a trader can have one or more node codes. These act as attributes of the trader. For example, a trader node could have a node code value for the tax identifier in Brazil (CNPJ).

2. Finding existing trader nodes in the database

The following functions can be used to find the node id of traders of different levels: - find_label(label) is used to retrieve the id of a level-2 trader node; - find_trader_by_name(trader_name) is used to retrieve the id of a level-1 trader node; - find_group_by_name(group_name) is used to retrieve the id of a level-0 trader node;

3. Creating new traders

When a trader name is not found in the database, a new trader tree can be inserted using insert_trader_tree_if_missing. This function creates 3 trader nodes with the same name, of levels 0, 1, and 2, connected in a tree:

[21111111] AZERTYUIOP INC [TRADER GROUP]
  |  
  |-- [21111112] AZERTYUIOP INC [TRADER]
  |     |
  |     |--     [21111113] AZERTYUIOP INC

4. Improving the traders hierarchy

4.1 Moving a label to a new trader

When a label is identified as a synonym for another trader, change_label_trader can be used to move the label to the other trader.

4.2 Moving a trader to a new group

When a trader is identified as a subsidiary of another group, change_trader_group can be used to move the trader to the other group. When the membership of the trader into the group is limited in time, time arguments can be provided: year_start, month_start, day_start, year_end, month_end, day_end. So for instance the acquisition of a trader in August 2010 can be captured with:

change_trader_group(trader_id, new_group_id, year_start=2010, month_start=8)

4.3 Full example

Here is an example of typical operations related with improving the traders hierarchy in the database (note that we have removed some labels for readability).

We realize a lot of labels corresponding to the same group are part of separate trader trees.

>>> show_trader_tree('ITOCHU CORPORATION')
[191446] ITOCHU BRASIL S/A [TRADER GROUP]
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | AUSTRALIA, BELGIUM, BRAZIL, TAIWAN, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD
  |     |--     [78010] ITOCHU & CO
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |     |--     [78013] ITOCHU CO LTD
  |     |--     [78015] ITOCHU CORP
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--     [13416] ITOCHU CORPORATION
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |     |--     [78027] ITOCHU RIO GRANDE
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,

>>> show_trader_tree('ITOCHU EUROPE PLC')
[15235999] ITOCHU EUROPE PLC [TRADER GROUP]
  |  
  |-- [15236000] ITOCHU EUROPE PLC [TRADER]
  |     |
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--  [15236001] ITOCHU EUROPE PLC

We create a new trader with the simplified name we want all these traders to be grouped under:

>>> insert_trader_tree_if_missing('ITOCHU')
15237411

We attach all labels starting by "ITOCHU" to trader "ITOCHU". First run with dryrun=True to make sure all merges are correct, then run with dryrun=False to perform the merges.

>>> combine_traders_by_pattern('ITOCHU%','ITOCHU',dryrun=True)
ITOCHU CORPORATION : -----------------------------> TO ITOCHU
ITOCHU SINGAPORE PTE.,LTD. : -----------------------------> TO ITOCHU
ITOCHU & CO : -----------------------------> TO ITOCHU
ITOCHU BRASIL SA : -----------------------------> TO ITOCHU
ITOCHU CO LTD : -----------------------------> TO ITOCHU
ITOCHU CORP : -----------------------------> TO ITOCHU
ITOCHU CORP LTD : -----------------------------> TO ITOCHU
ITOCHU CORP. : -----------------------------> TO ITOCHU
ITOCHU FOODSALES AND MARKETING CO.LTD. : -----------------------------> TO ITOCHU
ITOCHU INTERNATIONAL INC : -----------------------------> TO ITOCHU
ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122 : -----------------------------> TO ITOCHU
ITOCHU INTL INC : -----------------------------> TO ITOCHU
ITOCHU RIO GRANDE : -----------------------------> TO ITOCHU
ITOCHU SINGAPORE PTE LTD : -----------------------------> TO ITOCHU
ITOCHU SINGAPORE PTE LTD, : -----------------------------> TO ITOCHU
ITOCHU BRASIL S/A : -----------------------------> TO ITOCHU
ITOCHU FOOD SALES AND MARKETING CO. : -----------------------------> TO ITOCHU
ITOCHU EUROPE PLC : -----------------------------> TO ITOCHU
ITOCHU  EUROPE  PLC : -----------------------------> TO ITOCHU
ITOCHU FOOD SALES & MARKETING CO. L : -----------------------------> TO ITOCHU
ITOCHU FOODSALES AND MARKETING CO. : -----------------------------> TO ITOCHU
ITOCHU CORPORATION COFFEE DEPT / : -----------------------------> TO ITOCHU
ITOCHU CORPORATION COFFE DEPT : -----------------------------> TO ITOCHU
ITOCHU CORP COFFEE DEPT : -----------------------------> TO ITOCHU
ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122 : -----------------------------> TO ITOCHU
ITOCHU CORPORATION TOKFJ : -----------------------------> TO ITOCHU
ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122 : -----------------------------> TO ITOCHU
ITOCHU SINGAPORE : -----------------------------> TO ITOCHU
ITOCHU CORPORATION (TOKYF) : -----------------------------> TO ITOCHU
ITOCHU CORPORTATION : -----------------------------> TO ITOCHU
ITOCHU CORPORATION (TOKEH SECTION) : -----------------------------> TO ITOCHU
ITOCHU CORPORATION (TOKEN SECTION) : -----------------------------> TO ITOCHU

The resulting trader tree is:

[15237409] ITOCHU [TRADER GROUP]
  |  
  |-- [15237410] ITOCHU [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15237411] ITOCHU
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--     [78010] ITOCHU & CO
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |     |--     [78013] ITOCHU CO LTD
  |     |--     [78015] ITOCHU CORP
  |     |--   [2246309] ITOCHU CORP COFFEE DEPT
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--     [13416] ITOCHU CORPORATION
  |     |--  [15255687] ITOCHU CORPORATION (TOKEH SECTION)
  |     |--  [15267816] ITOCHU CORPORATION (TOKEN SECTION)
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--   [2245808] ITOCHU CORPORATION COFFE DEPT
  |     |--   [2242478] ITOCHU CORPORATION COFFEE DEPT /
  |     |--   [2221874] ITOCHU CORPORATION TOKFJ
  |     |--  [15255534] ITOCHU CORPORTATION
  |     |--  [15236001] ITOCHU EUROPE PLC
  |     |--  [15236202] ITOCHU FOOD SALES & MARKETING CO. L
  |     |--  [15236388] ITOCHU FOOD SALES AND MARKETING CO.
  |     |--  [15237750] ITOCHU FOODSALES AND MARKETING CO.
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |     |--   [1821946] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122
  |     |--   [2514447] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |     |--     [78027] ITOCHU RIO GRANDE
  |     |--   [2514630] ITOCHU SINGAPORE
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,
  |     |--     [15833] ITOCHU SINGAPORE PTE.,LTD.

In the process, we have combined all subsidiaries from different countries under the same generic trader "ITOCHU", but the Brazilian subsidiary with trase_id "[144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155]" is still detached from the group "ITOCHU". Note that 2 labels are still under "ITOCHU BRASIL S/A" as they have typos, so were not picked up by the combine function.

[191446] ITOCHU BRASIL S/A [TRADER GROUP]
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | AUSTRALIA, BELGIUM, BRAZIL, TAIWAN, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD

So we need to bring that trader under the "ITOCHU" group as well:

change_trader_group(144280,15237409)

The resulting trader tree is:

>>> show_trader_tree('ITOCHU')
[15237409] ITOCHU [TRADER GROUP]
  |  
  |-- [15237410] ITOCHU [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15237411] ITOCHU
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--     [78010] ITOCHU & CO
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |     |--     [78013] ITOCHU CO LTD
  |     |--     [78015] ITOCHU CORP
  |     |--   [2246309] ITOCHU CORP COFFEE DEPT
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--     [13416] ITOCHU CORPORATION
  |     |--  [15255687] ITOCHU CORPORATION (TOKEH SECTION)
  |     |--  [15267816] ITOCHU CORPORATION (TOKEN SECTION)
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--   [2245808] ITOCHU CORPORATION COFFE DEPT
  |     |--   [2242478] ITOCHU CORPORATION COFFEE DEPT /
  |     |--   [2221874] ITOCHU CORPORATION TOKFJ
  |     |--  [15255534] ITOCHU CORPORTATION
  |     |--  [15236001] ITOCHU EUROPE PLC
  |     |--  [15236202] ITOCHU FOOD SALES & MARKETING CO. L
  |     |--  [15236388] ITOCHU FOOD SALES AND MARKETING CO.
  |     |--  [15237750] ITOCHU FOODSALES AND MARKETING CO.
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |     |--   [1821946] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122
  |     |--   [2514447] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |     |--     [78027] ITOCHU RIO GRANDE
  |     |--   [2514630] ITOCHU SINGAPORE
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,
  |     |--     [15833] ITOCHU SINGAPORE PTE.,LTD.
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | AUSTRALIA, BELGIUM, BRAZIL, TAIWAN, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD

We can now refine the hierarchy by separating the subsidiaries and sorting out the labels. The first step is to detach the subsidiaries:

# ITOCHU CORPORATION
detach_trader_label(13416)

# ITOCHU SINGAPORE
detach_trader_label(2514630)

# ITOCHU INTERNATIONAL INC
detach_trader_label(78022)

# ITOCHU EUROPE PLC
detach_trader_label(15236001)

# ITOCHU & CO
detach_trader_label(78010)

# ITOCHU RIO GRANDE
detach_trader_label(78027)

# ITOCHU FOOD SALES AND MARKETING CO.
detach_trader_label(15236388)

The resulting trader tree is:

>>> show_trader_tree('ITOCHU')
[15237409] ITOCHU [TRADER GROUP]
  |  
  |-- [15237410] ITOCHU [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15237411] ITOCHU
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |     |--     [78013] ITOCHU CO LTD
  |     |--     [78015] ITOCHU CORP
  |     |--   [2246309] ITOCHU CORP COFFEE DEPT
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--  [15255687] ITOCHU CORPORATION (TOKEH SECTION)
  |     |--  [15267816] ITOCHU CORPORATION (TOKEN SECTION)
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--   [2245808] ITOCHU CORPORATION COFFE DEPT
  |     |--   [2242478] ITOCHU CORPORATION COFFEE DEPT /
  |     |--   [2221874] ITOCHU CORPORATION TOKFJ
  |     |--  [15236202] ITOCHU FOOD SALES & MARKETING CO. L
  |     |--  [15237750] ITOCHU FOODSALES AND MARKETING CO.
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |     |--   [1821946] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122
  |     |--   [2514447] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,
  |     |--     [15833] ITOCHU SINGAPORE PTE.,LTD.
  |  
  |-- [2533020] ITOCHU & CO [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78010] ITOCHU & CO
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | AUSTRALIA, BELGIUM, BRAZIL, TAIWAN, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD
  |  
  |-- [78005] ITOCHU CORPORATION [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [13416] ITOCHU CORPORATION
  |  
  |-- [15236000] ITOCHU EUROPE PLC [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15236001] ITOCHU EUROPE PLC
  |  
  |-- [15236387] ITOCHU FOOD SALES AND MARKETING CO. [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15236388] ITOCHU FOOD SALES AND MARKETING CO.
  |  
  |-- [2537004] ITOCHU INTERNATIONAL INC [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |  
  |-- [2534639] ITOCHU RIO GRANDE [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78027] ITOCHU RIO GRANDE
  |  
  |-- [2514629] ITOCHU SINGAPORE [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--   [2514630] ITOCHU SINGAPORE

We then need to move the labels to their subsidiaries:

>>> for label_id in get_trader_label_ids(find_trader_by_name('ITOCHU')):
...     label = get_node(label_id)
...     if 'CORP' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU CORPORATION'))
...     if 'EUROPE' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU EUROPE PLC'))
...     if 'SINGAPORE' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU SINGAPORE'))
...     if 'MARKETING' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU FOOD SALES AND MARKETING CO.'))
...     if 'BRASIL' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU BRASIL S/A'))
...     if 'INTERNATIONAL' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU INTERNATIONAL INC'))
...     if 'CO' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU & CO'))

We also need to move the labels that stayed under the Brazilian subsidiary due to typos:

>>> for label_id in get_trader_label_ids(find_trader_by_name('ITOCHU BRASIL S/A')):
...     label = get_node(label_id)
...     if 'SINGAPORE' in label.name:
...         change_label_trader(label_id, find_trader_by_name('ITOCHU SINGAPORE'))

The resulting trader tree is:

>>> show_trader_tree('ITOCHU')
[15237409] ITOCHU [TRADER GROUP]
  |  
  |-- [15237410] ITOCHU [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15237411] ITOCHU
  |  
  |-- [2533020] ITOCHU & CO [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78010] ITOCHU & CO
  |     |--     [78013] ITOCHU CO LTD
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |  
  |-- [78005] ITOCHU CORPORATION [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78015] ITOCHU CORP
  |     |--   [2246309] ITOCHU CORP COFFEE DEPT
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--     [13416] ITOCHU CORPORATION
  |     |--  [15255687] ITOCHU CORPORATION (TOKEH SECTION)
  |     |--  [15267816] ITOCHU CORPORATION (TOKEN SECTION)
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--   [2245808] ITOCHU CORPORATION COFFE DEPT
  |     |--   [2242478] ITOCHU CORPORATION COFFEE DEPT /
  |     |--   [2221874] ITOCHU CORPORATION TOKFJ
  |  
  |-- [15236000] ITOCHU EUROPE PLC [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--  [15236001] ITOCHU EUROPE PLC
  |  
  |-- [15236387] ITOCHU FOOD SALES AND MARKETING CO. [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--  [15236202] ITOCHU FOOD SALES & MARKETING CO. L
  |     |--  [15236388] ITOCHU FOOD SALES AND MARKETING CO.
  |     |--  [15237750] ITOCHU FOODSALES AND MARKETING CO.
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |  
  |-- [2537004] ITOCHU INTERNATIONAL INC [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |     |--   [1821946] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122
  |     |--   [2514447] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |  
  |-- [2534639] ITOCHU RIO GRANDE [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78027] ITOCHU RIO GRANDE
  |  
  |-- [2514629] ITOCHU SINGAPORE [TRADER | ANGOLA, AUSTRALIA, BELGIUM, BRAZIL, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD
  |     |--   [2514630] ITOCHU SINGAPORE
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,
  |     |--     [15833] ITOCHU SINGAPORE PTE.,LTD.

The last step is to update the countries attached to the traders:

>>> for trader_id in get_group_trader_ids(find_group_by_name('ITOCHU')):
...     update_trader_countries(trader_id)

The resulting trader tree is:

>>> show_trader_tree('ITOCHU')
[15237409] ITOCHU [TRADER GROUP]
  |  
  |-- [15237410] ITOCHU [TRADER | BELGIUM, JAPAN, SOUTH KOREA]
  |     |
  |     |--  [15237411] ITOCHU
  |  
  |-- [2533020] ITOCHU & CO [TRADER]
  |     |
  |     |--     [78010] ITOCHU & CO
  |     |--     [78013] ITOCHU CO LTD
  |  
  |-- [144280] ITOCHU BRASIL S/A [TRADER | BR-TRADER-61274155 | BRAZIL]
  |     |
  |     |--    [100099] ITOCHU BRASIL S/A
  |     |--     [78012] ITOCHU BRASIL SA
  |  
  |-- [78005] ITOCHU CORPORATION [TRADER | AUSTRALIA, BELGIUM, CANADA, TAIWAN, ...]
  |     |
  |     |--     [78015] ITOCHU CORP
  |     |--   [2246309] ITOCHU CORP COFFEE DEPT
  |     |--     [78017] ITOCHU CORP LTD
  |     |--     [78018] ITOCHU CORP.
  |     |--     [13416] ITOCHU CORPORATION
  |     |--  [15255687] ITOCHU CORPORATION (TOKEH SECTION)
  |     |--  [15267816] ITOCHU CORPORATION (TOKEN SECTION)
  |     |--   [2516421] ITOCHU CORPORATION (TOKYF)
  |     |--   [2245808] ITOCHU CORPORATION COFFE DEPT
  |     |--   [2242478] ITOCHU CORPORATION COFFEE DEPT /
  |     |--   [2221874] ITOCHU CORPORATION TOKFJ
  |  
  |-- [15236000] ITOCHU EUROPE PLC [TRADER | BELGIUM, CHINA (MAINLAND), INDONESIA, JAPAN, ...]
  |     |
  |     |--  [15236109] ITOCHU  EUROPE  PLC
  |     |--  [15236001] ITOCHU EUROPE PLC
  |  
  |-- [15236387] ITOCHU FOOD SALES AND MARKETING CO. [TRADER | JAPAN]
  |     |
  |     |--  [15236202] ITOCHU FOOD SALES & MARKETING CO. L
  |     |--  [15236388] ITOCHU FOOD SALES AND MARKETING CO.
  |     |--  [15237750] ITOCHU FOODSALES AND MARKETING CO.
  |     |--     [78020] ITOCHU FOODSALES AND MARKETING CO.LTD.
  |  
  |-- [2537004] ITOCHU INTERNATIONAL INC [TRADER | AUSTRALIA, CHINA (MAINLAND), TAIWAN, GERMANY, ...]
  |     |
  |     |--     [78022] ITOCHU INTERNATIONAL INC
  |     |--   [1821946] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV N05/122
  |     |--   [2514447] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV NO05/122
  |     |--     [78025] ITOCHU INTERNATIONAL INC:SUBSTITUI PARTE DO RV Nº05/122
  |     |--     [78026] ITOCHU INTL INC
  |  
  |-- [2534639] ITOCHU RIO GRANDE [TRADER]
  |     |
  |     |--     [78027] ITOCHU RIO GRANDE
  |  
  |-- [2514629] ITOCHU SINGAPORE [TRADER | ANGOLA, MYANMAR, CAMBODIA, TAIWAN, ...]
  |     |
  |     |--     [78006] I TOCHU SINGAPORE PTE LTD
  |     |--     [78008] ITHOCHU SINGAPORE PTE LTD
  |     |--   [2514630] ITOCHU SINGAPORE
  |     |--     [78029] ITOCHU SINGAPORE PTE LTD
  |     |--     [78030] ITOCHU SINGAPORE PTE LTD,
  |     |--     [15833] ITOCHU SINGAPORE PTE.,LTD.

4.4 Fixing Trase IDs

In Brazil we have some CPFs which have Trase IDs like BR-TRADER-00038853. This is incorrect - we shouldn't be left-padding with 0s, and the Trase ID should be changed to BR-TRADER-38853329.

5. Solving duplicated trader labels/names/groups

Our database has constraints about duplicated trader labels. This means ideally each trader label should be unique and belong to only one trader name/group. However, there could still be some duplicated trader labels, which exist before we set the constraint. Here is the solution if you face duplications:

>>> show_trader_tree(15524311)
[15524309] AGRO SAN GERARDO E I R L [TRADER GROUP]
  |  
  |-- [15524310] AGRO SAN GERARDO E I R L [TRADER | PE-TRADER-20600699131]
  |     |
  |     |--  [15524311] AGRO SAN GERARDO E I R L


>>> show_trader_tree(15524340)
[15524336] AGRO SAN GERARDO E I R L [TRADER GROUP]
  |  
  |-- [15524337] AGRO SAN GERARDO E I R L [TRADER | PE-TRADER-20600699131]
  |     |
  |     |--  [15524340] AGRO SAN GERARDO E I R L

Firstly, we should check whether those traders had activities in any dataset, with get_trader_activities(trader_id). If those traders have activities in certain datasets. Use SQL to replace the duplicated trader id with the other trader id:

UPDATE flow_paths SET node_id = trader_id_to_keep WHERE node_id = trader_id_to_delete;
UPDATE link_paths SET node_id = trader_id_to_keep WHERE node_id = trader_id_to_delete;
UPDATE reference_trader_labels SET trader_id = trader_id_to_keep WHERE trader_id = trader_id_to_delete;
UPDATE reference_group_traders SET trader_id = trader_id_to_keep WHERE trader_id = trader_id_to_delete;

Then, remove the duplicated trader label, name, and group, with delete_trader(group_id).