如何基于多列及单个DataFrame的索引合并两个DataFrame，并保留左DataFrame的索引

Great questions—these are super common scenarios when working with Pandas merges, so let’s break them down with concrete examples to make it totally clear.

There are two straightforward ways to handle this, depending on whether you want to keep the index as-is or convert it to a column first:

• Option 1: Use left_index/right_index alongside left_on/right_on
  If, say, your right DataFrame (df2) has one of the join keys as its index (e.g., colA is df2's index, and you need to merge it with df1's col1, plus df1's col2/col3 with df2's colB/colC), you can use right_index=True to reference that index as a join key, while using right_on for the remaining columns:

  # Example: df2's index = colA (matches df1's col1)
  newdf = pd.merge(
      df1, df2,
      how='left',
      left_on=['col2', 'col3'],  # Remaining join columns from df1
      right_on=['colB', 'colC'],  # Matching columns from df2
      right_index=True  # Use df2's index as the third join key (matches df1's col1)
  )
  

• Option 2: Convert the index to a column first
  If you prefer a more explicit approach, reset the index of the DataFrame with the indexed join key and rename it to match the corresponding column in the other DataFrame:

  # Reset df2's index to make colA a regular column
  df2_reset = df2.reset_index().rename(columns={'index': 'colA'})
  # Now merge like you would with all regular columns
  newdf = pd.merge(
      df1, df2_reset,
      how='left',
      left_on=['col1', 'col2', 'col3'],
      right_on=['colA', 'colB', 'colC']
  )
  

If you need to use an index (either from df1 or df2) as a join key and keep df1's original index in the resulting newdf, here's how to do it with left_index=True as specified:

Scenario A: Using df1's index as a join key #

When you use left_index=True in pd.merge(), the resulting DataFrame will default to a new integer index. To keep df1's original index, you have two options:

1. Preserve the index by converting it to a column first

   # Convert df1's index to a named column so it stays in the merge result
   df1_with_idx = df1.reset_index().rename(columns={'index': 'original_idx'})
   # Merge using the new column as part of the join keys
   newdf = pd.merge(
       df1_with_idx, df2,
       how='left',
       left_on=['original_idx', 'col2', 'col3'],
       right_on=['colA', 'colB', 'colC']
   )
   # Optional: Set the original index back if needed
   newdf = newdf.set_index('original_idx')
   

2. Reassign the index after merging
   If you want to use left_index=True directly, just save df1's index before merging and apply it to the result:

   # Save df1's original index
   original_idx = df1.index
   # Merge using df1's index as a join key (left_index=True)
   newdf = pd.merge(
       df1, df2,
       how='left',
       left_index=True,  # Use df1's index to match df2's colA
       left_on=['col2', 'col3'],
       right_on=['colA', 'colB', 'colC']
   )
   # Restore df1's original index
   newdf.index = original_idx
   

Scenario B: Using df2's index as a join key while preserving df1's index #

If the join index is from df2, you can still keep df1's index by saving it before merging, then reassigning it post-merge:

original_idx = df1.index
newdf = pd.merge(
    df1, df2,
    how='left',
    left_on=['col1', 'col2', 'col3'],
    right_on=['colB', 'colC'],
    right_index=True  # Use df2's index to match df1's col1
)
newdf.index = original_idx

Alternatively, if you prefer a cleaner approach, you can use df.join() instead of merge()—it defaults to preserving the left DataFrame's index, though you'll need to align the columns first:

# Rename df2's columns to match df1's join columns (except the index)
df2_aligned = df2.rename(columns={'colB': 'col2', 'colC': 'col3'})
# Join uses df1's index and df2's index by default; adjust if needed
newdf = df1.join(df2_aligned, how='left', on=['col2', 'col3'])

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