Python多列层级映射问题：两DataFrame的层级聚类映射

Hey there! I see where you're stuck with this hierarchical mapping problem. Splitting the mapping_df into three separate sub-tables and merging each individually doesn't work because it ignores the parent-child hierarchy between the clusters—same cluster_2 values might exist under different cluster_1 groups, and merging only on cluster_2 would pull in incorrect labels without respecting that parent scope.

Let's fix this with a step-by-step approach that enforces the hierarchical constraints:

Solution: Hierarchical Merging with Parent Scope #

The key is to merge using combinations of parent and child cluster columns at each step, so we only pull labels that belong to the exact parent cluster group.

Assuming your df2 contains the columns cluster_1, cluster_2, and cluster_3, and mapping_df has all the cluster-label pairs tied to their full hierarchy, here's how to do it:

1. Map cluster_label1 first
   Since cluster_label1 is tied directly to cluster_1, we can merge on just cluster_1 (we'll drop duplicates first to avoid redundant rows):

   # Get unique cluster_1 -> cluster_label1 mappings
   df_l1 = df2.merge(
       mapping_df[['cluster_1', 'cluster_label1']].drop_duplicates(),
       on='cluster_1',
       how='left'
   )
   

2. Map cluster_label2 within the cluster_1 scope
   Now, we merge using both cluster_1 and cluster_2 to ensure we only get labels for cluster_2 values that belong to the correct cluster_1 group:

   # Get unique cluster_1 + cluster_2 -> cluster_label2 mappings
   df_l2 = df_l1.merge(
       mapping_df[['cluster_1', 'cluster_2', 'cluster_label2']].drop_duplicates(),
       on=['cluster_1', 'cluster_2'],
       how='left'
   )
   

3. Map cluster_label3 within the cluster_1 + cluster_2 scope
   Finally, use all three cluster columns to lock in the correct cluster_label3 for the full hierarchy:

   # Get unique cluster_1 + cluster_2 + cluster_3 -> cluster_label3 mappings
   final_df = df_l2.merge(
       mapping_df[['cluster_1', 'cluster_2', 'cluster_3', 'cluster_label3']].drop_duplicates(),
       on=['cluster_1', 'cluster_2', 'cluster_3'],
       how='left'
   )
   

Why Your Original Approach Failed #

When you split mapping_df into three separate sub-tables and merged each on a single cluster column (e.g., merging df2 with mapping_df_L2 only on cluster_2), you weren't accounting for the fact that a single cluster_2 value can exist under multiple cluster_1 groups. This leads to incorrect label matches that don't respect the hierarchical structure.

Shortcut: Single Merge with All Hierarchy Keys #

If every row in mapping_df represents a unique combination of cluster_1, cluster_2, and cluster_3, you can simplify this to a single merge using all three cluster columns at once:

final_df = df2.merge(
    mapping_df[['cluster_1', 'cluster_2', 'cluster_3', 'cluster_label1', 'cluster_label2', 'cluster_label3']],
    on=['cluster_1', 'cluster_2', 'cluster_3'],
    how='left'
)

This works because merging on all three cluster columns ensures we only match rows where the full hierarchy matches exactly.

内容的提问来源于stack exchange，提问作者Aditya sharma