如何将Pandas DataFrame与透视表（Pivot Table）进行合并？

Got it, let's break down how to merge your two datasets effectively. The critical detail here is that the year field has different meanings across df1 (contract execution year) and df2 (audit year), so we'll cover the two most common matching scenarios depending on your analysis needs.

First, Let's Set Up Example Data #

To make this concrete, let's create sample versions of your DataFrames so you can follow along:

import pandas as pd

# df1: Contract list with execution year and institution ID
df1 = pd.DataFrame({
    'contract_id': ['C001', 'C002', 'C003', 'C004'],
    'year': [2021, 2022, 2021, 2023],
    'o_id': ['O01', 'O02', 'O01', 'O03'],
    'contract_value': [10000, 25000, 15000, 30000]
})

# df2: Pivot table of institution audit issues by audit year
df2 = pd.DataFrame({
    'year': [2021, 2021, 2022, 2022],
    'o_id': ['O01', 'O02', 'O01', 'O03'],
    'P_1': [3, 1, 2, 0],
    'P_2': [1, 0, 4, 2]
})

Scenario 1: Match Contract Execution Year to Exact Audit Year #

If you want to link each contract to the same year's audit results for its institution (i.e., a contract executed in 2021 gets the 2021 audit data for its o_id), use Pandas' merge() function with both o_id and year as matching keys:

# Inner join: Only keep contracts that have matching audit data for the same year
merged_inner = pd.merge(df1, df2, on=['o_id', 'year'], how='inner')

# Left join: Keep all contracts, fill missing audit data with NaN (great if you don't want to lose contract records)
merged_left = pd.merge(df1, df2, on=['o_id', 'year'], how='left')

• Inner join will filter out contracts where there's no audit data for the exact year (like contract C004 in our example, since O03 only has 2022 audit data).
• Left join preserves all your contract records, and fills P_1/P_2 with NaN where no matching audit data exists.

Scenario 2: Match to the Most Recent Audit Before/In the Contract Year #

If your analysis needs the latest available audit data up to the contract execution year (e.g., a 2023 contract uses the 2022 audit data if there's no 2023 audit yet), use merge_asof()—this is perfect for time-based "closest match" scenarios:

# First, sort both DataFrames by year (required for merge_asof)
df1_sorted = df1.sort_values('year')
df2_sorted = df2.sort_values(['o_id', 'year'])

# Merge to get the latest audit data <= contract year for each institution
merged_recent = pd.merge_asof(
    df1_sorted,
    df2_sorted,
    on='year',
    by='o_id',
    direction='backward'  # Grabs the largest audit year <= contract year
)

In our example, contract C004 (2023, O03) will now match the 2022 audit data for O03, since that's the latest available before 2023.

Quick Tips to Avoid Issues #

• Check data types: Ensure o_id and year are the same type in both DataFrames (e.g., both strings or integers). Fix mismatches with df['o_id'] = df['o_id'].astype(str).
• Aggregate duplicate audit records: If df2 has multiple entries for the same o_id and year, aggregate first (e.g., sum or average the issue counts):

  df2_aggregated = df2.groupby(['o_id', 'year']).agg({'P_1': 'sum', 'P_2': 'sum'}).reset_index()
  

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