Fusionner les gauche avec un nombre mixte d'identifiants

Question

J'ai un maptable et un df où je veux appliquer une fusion à gauche pour mapper une colonne supplémentaire sur un ensemble d'une ou plusieurs colonnes. Cependant, dans mon cas, les identifiants disponibles diffèrent par ligne.

Voici un exemple :

maptable =

  asset_class currency target
0      Equity      EUR     t1
1          FX      EUR     t2
2       Rates      USD     t3
3       Rates              t3o
4       Bonds              t4o
5       Bonds      AAA     t4

Supposons que nous ayons le df suivant :

df =

  asset_class currency
0      Equity      EUR
1      Equity      USD
2      Equity      GBP
3       Rates      EUR
4       Rates      USD
5       Rates      GBP
6       Bonds      AAA
7       Bonds      BBB
8       Bonds      CCC

Dans ce cas, le résultat souhaité devrait être :

  asset_class currency target
0      Equity      EUR     t1   (we have Equity+EUR)
1      Equity      USD    NaN   (we don't have Equity+USD and also not Equity)
2      Equity      GBP    NaN   (we don't have Equity+GBP and also not Equity)
3       Rates      EUR    t3o   (we don't have Rates+EUR, but we do have Rates)
4       Rates      USD     t3   (we have Rates+USD)
5       Rates      GBP    t30   (we don't have Rates+GBP, but we do have Rates)
6       Bonds      AAA     t4   (we have Bonds+AA)
7       Bonds      BBB    t4o   (we don't have Bonds+BBB, but we do have Bonds)
8       Bonds      CCC    t4o   (we don't have Bonds+CCC, but we do have Bonds)

Appliquer simplement une fusion à gauche sur asset_class et currency ne fonctionnera pas car les cas où une colonne d'identifiant sur deux a des valeurs, elle sera ignorée :

df_m = df.merge(maptable, how='left', on=['asset_class','currency'])

Il est également très important d'écraser les données dans le cas où une colonne cible est déjà mappée si nous utilisons plusieurs colonnes d'identification. Par exemple, l'utilisation de 'asset_class' et 'currency' est plus prioritaire que le mappage sur 'asset_class'. Pour cette raison fillna ne fonctionnera pas car nous avons besoin d'un update .

Comment y parvenir de manière efficace ?

---

Exemple de données

Vous pouvez recréer l'exemple ci-dessus comme suit :

import pandas as pd

maptable = pd.DataFrame({
    'asset_class': ['Equity', 'FX',   'Rates', 'Rates', 'Bonds', 'Bonds'],
    'currency':    ['EUR',    'EUR',  'USD',   '',      '',      'AAA'],
    'target':      ['t1',     't2',   't3',    't3o',    't4o',    't4']
})

df = pd.DataFrame({
    'asset_class': ['Equity', 'Equity', 'Equity', 'Rates', 'Rates', 'Rates', 'Bonds', 'Bonds', 'Bonds'],
    'currency':    ['EUR', 'USD', 'GBP', 'EUR', 'USD', 'GBP', 'AAA', 'BBB', 'CCC'],
})

---

Ce que j'ai essayé jusqu'à présent

Voici ce que j'ai essayé jusqu'à présent (mais c'est vraiment rudimentaire) :

def merge_mix(dl, dr, target_cols, id_cols):
    """Apply a merge left with a mixed number of identifiers

    :param dl:  target DataFrame on which we want to map the target_cols, contains id_cols but might also
    contain target_cols. If non-NA matching target values are found in dr, it will overwrite the values for the
    index/col combinations
    :param dr:  mapping DataFrame that contains both target_cols and id_cols
    :param target_cols: list of column names that we want to map from the dr
    :param id_cols: list of columns that we want to use as identifier, can be empty
    """
    def is_empty(x):
        """Check if empty"""
        if x is not None:
            if isinstance(x, str) and x != '':
                return False
            else:
                if not pd.np.isnan(value):
                    return False
        return True

    # Append target col
    for target_col in target_cols:
        if target_col not in dl:
            dl.insert(loc=len(dl.columns), column=target_col, value=None)

    # Clean dr
    dr = dr[id_cols + target_cols]
    dr = dr.drop_duplicates(keep='last')

    # Loop over all the indices and check which combinations exists
    for index in dr.index:
        combo_cols = []
        for col in id_cols:
            value = dr.loc[index, col]

            # Add combination if value is not empty
            if not is_empty(value):
                combo_cols.append(col)

        # The combination for this index
        dr.loc[index, 'combo_cols'] = "+".join(combo_cols)
        dr.loc[index, 'combo_count'] = len(combo_cols)

    # Get the unique combo cols combinations. Take first the least granular and then work towards more granular
    # as we are working with .update and not with .merge
    combos_count = list(dr['combo_count'].unique())  # Unique list
    combos_count = [x for x in combos_count if x > 0]  # Take out zero count combo cols
    combos_count.sort(reverse=False)  # Sort to move the least granular first

    for count in combos_count:

        # For a given count, check all combo combinations with this count
        dr_cc = dr[dr['combo_count'] == count]
        unique_combo_cols_cc = list(dr_cc['combo_cols'].unique())

        for combo_col in unique_combo_cols_cc:

            # Maptable for given combo col
            dr_uc_cc = dr_cc[dr_cc['combo_cols'] == combo_col]
            dr_uc_cc = dr_uc_cc.drop_duplicates(keep='last')

            # Set index on the id cols for this combo combination
            id_cols_uc_cc = combo_col.split('+')
            dl = dl.set_index(id_cols_uc_cc)
            dr_uc_cc = dr_uc_cc.set_index(id_cols_uc_cc)

            # Update matching row, cols
            dl.update(dr_uc_cc[target_cols])
            dl = dl.reset_index()

    return dl

Answer 1

Créez une fonction personnalisée qui vérifie par défaut uniquement le premier composant d'un tuple si le tuple composé des deux parties n'existe pas.

mapdict = {
    tuple(filter(pd.notna, (a, c))): t
    for a, c, t in maptable.itertuples(index=False)
}

def get(x):
    return mapdict.get(x, mapdict.get((x[0], ''), mapdict.get(x[:1])))

list_of_cols = ['asset_class', 'currency']
df.assign(target=[*map(get, zip(*map(df.get, list_of_cols)))])

  asset_class currency target
0      Equity      EUR     t1
1      Equity      USD   None
2      Equity      GBP   None
3       Rates      EUR    t3o
4       Rates      USD     t3
5       Rates      GBP    t3o
6       Bonds      AAA     t4
7       Bonds      BBB    t4o
8       Bonds      CCC    t4o

Answer 2

Utilisation de pd.merge fusionner les deux cadres de données sur "asset_class", "currency" l'obtention de colonnes df_m .

df_m = pd.merge(df, maptable, on=["asset_class", "currency"], how="left")
# df_m

 asset_class currency target
0      Equity      EUR     t1
1      Equity      USD    NaN
2      Equity      GBP    NaN
3       Rates      EUR    NaN
4       Rates      USD     t3
5       Rates      GBP    NaN
6       Bonds      AAA     t4
7       Bonds      BBB    NaN
8       Bonds      CCC    NaN

On obtient alors un mappings du dictionnaire de l df dataframe correspondant aux lignes où la valeur de la devise est '' et les clés de ce dictionnaire sont de asset_class et les valeurs proviennent de target colonne.

mappings = maptable[maptable["currency"].eq('')].set_index("asset_class")["target"].to_dict()
# mappings

{'Rates': 't3o', 'Bonds': 't4o'}

Maintenant, filtrez le asset_class col de la df_m où les valeurs de target col sont nan et cartographier ce col en utilisant le mappings dictionnaire obtenu à l'étape précédente pour créer une nouvelle série s .

s = df_m.loc[df_m["target"].isna(), "asset_class"].map(mappings)
# s
1    NaN
2    NaN
3    t3o
5    t3o
7    t4o
8    t4o

Ensuite, en utilisant .fillna remplit la fonction nan valeurs de target colonne dans df_m en utilisant les séries s .

---

Utilisez :

df_m = pd.merge(df, maptable, on=["asset_class", "currency"], how="left")

# {'Bonds': 't4o', 'Rates': 't3o'}
mappings = maptable[maptable["currency"].eq('')].set_index("asset_class")["target"].to_dict()

s = df_m.loc[df_m["target"].isna(), "asset_class"].map(mappings)
df_m["target"] = df_m["target"].fillna(s)
print(df_m)

Cette empreinte :

  asset_class currency target
0      Equity      EUR     t1
1      Equity      USD    NaN
2      Equity      GBP    NaN
3       Rates      EUR    t3o
4       Rates      USD     t3
5       Rates      GBP    t3o
6       Bonds      AAA     t4
7       Bonds      BBB    t4o
8       Bonds      CCC    t4o

Answer 3

Vous pouvez commencer par vos données fusionnées :

merged = df.merge(maptable, how='left', on=['asset_class','currency'])

Ça te donnera la première couche :

  asset_class currency target
0      Equity      EUR     t1
1      Equity      USD    NaN
2      Equity      GBP    NaN
3       Rates      EUR    NaN
4       Rates      USD     t3
5       Rates      GBP    NaN
6       Bonds      AAA     t4
7       Bonds      BBB    NaN
8       Bonds      CCC    NaN

Ensuite, faites un fillna d'une autre fusion, correspondant à la valeur par défaut '' pour currency seulement :

merged['target'].fillna(df.assign(currency='').merge(maptable, on=['asset_class','currency'], how='left')['target'], inplace=True)

Ce qui vous donnera le résultat :

>>> merged
  asset_class currency target
0      Equity      EUR     t1
1      Equity      USD    NaN
2      Equity      GBP    NaN
3       Rates      EUR    t3o
4       Rates      USD     t3
5       Rates      GBP    t3o
6       Bonds      AAA     t4
7       Bonds      BBB    t4o
8       Bonds      CCC    t4o

Inutile de dire qu'en fonction de votre valeur de repli, vous devrez mettre à jour '' en conséquence. Si c'est NaN utiliser maptable['currency'].isna() .

Une doublure serait :

df_m = df.assign(target=\
    df.merge(maptable, on=['asset_class','currency'], how='left')['target'].fillna( \
    df.assign(currency='').merge(maptable, on=['asset_class','currency'], how='left')['target']))

Answer 4

Une autre approche consiste à vérifier d'abord si la paire particulière de asset_class y currency existent dans maptable d'abord, remplir le manquant avec la valeur par défaut ( '' ), et ensuite fusionner :

keys = ['asset_class', 'currency']
df_m = df.assign(currency= \
    np.where(df.set_index(keys).index.isin(maptable.set_index(keys).index), df['currency'], '') \
    ).merge(maptable, on=keys, how='left').assign(currency=df['currency'])

Résultat :

  asset_class currency target
0      Equity      EUR     t1
1      Equity      USD    NaN
2      Equity      GBP    NaN
3       Rates      EUR    t3o
4       Rates      USD     t3
5       Rates      GBP    t3o
6       Bonds      AAA     t4
7       Bonds      BBB    t4o
8       Bonds      CCC    t4o