合并财务数据遇异常：内连接无结果，外连接列翻倍

问题场景 #

合并利润表df1、资产负债表df2、现金流量表df3时出现两类异常：

1. Inner Join合并结果为空 #

首次采用inner join合并，代码如下：

# Here's the first try:

# Create a custom function for merging the data together:
def getXDataMerged():    
    print('Income Statement CSV data is(rows, columns): ', df1.shape)
    print('Balance Sheet CSV data is: ', df2.shape)
    print('Cash Flow CSV data is: ' , df3.shape)

    # Merge the data together
    result = pd.merge(df1, df2, on=['Ticker', 'SimFinId', 'Currency',
                      'Fiscal Year', 'Fiscal Period', 'Report Date', 'Publish Date'], how='inner')    
    result = pd.merge(result, df3, on=['Ticker','SimFinId','Currency',
                    'Fiscal Year','Report Date','Publish Date'])        
    print('Merged X data matrix shape is: ', result.shape)    
    return result

# Use getXDataMerged() to retrieve some data, and then save it to a CSV file named "Annual_Stock_Price_Fundamentals.csv"
X = getXDataMerged()
X.to_csv("Annual_Stock_Price_Fundamentals.csv")

执行输出：

Income Statement CSV data is(rows, columns):  (17185, 28)
Balance Sheet CSV data is:  (17185, 30)
Cash Flow CSV data is:  (17185, 28)
Merged X data matrix shape is:  (0, 73)

合并结果无有效数据。

2. Outer Join合并行数翻倍 #

改用outer join（仅修改合并方法）：

# Second try (only changed the merging method to 'outer', everything else stays the same:
  
    # Merge the data together
    result = pd.merge(df1, df2, on=['Ticker', 'SimFinId', 'Currency',
                      'Fiscal Year', 'Fiscal Period', 'Report Date', 'Publish Date'], how='outer')    
    result = pd.merge(result, df3, on=['Ticker','SimFinId','Currency',
                    'Fiscal Year','Report Date','Publish Date'])        

执行输出：

Income Statement CSV data is(rows, columns):  (17185, 28)
Balance Sheet CSV data is:  (17185, 30)
Cash Flow CSV data is:  (17185, 28)
Merged X data matrix shape is:  (34370, 73)

合并行数为原单表的两倍，无法正确匹配公共键。

核心原因 #

1. Inner Join为空：

   • 合并键取值完全匹配的记录不存在：比如Fiscal Period字段取值不一致（如df1用FY、df3用Annual）、日期字段格式不统一（如YYYY-MM-DD与MM/DD/YYYY）、部分键存在空值；
   • 第二次合并时，df3的合并键少了Fiscal Period，导致前序合并结果与df3无法匹配。

2. Outer Join行数翻倍：
   单表中存在同一Ticker+Fiscal Year对应多条记录的重复项，outer join保留所有组合，形成笛卡尔积导致行数翻倍。

解决方案 #

步骤1：校验并统一合并键 #

先检查合并键的一致性，处理格式与空值：

# 检查合并键空值情况
print("df1 合并键空值统计：")
print(df1[['Ticker', 'SimFinId', 'Currency', 'Fiscal Year', 'Fiscal Period', 'Report Date', 'Publish Date']].isnull().sum())
print("\ndf2 合并键空值统计：")
print(df2[['Ticker', 'SimFinId', 'Currency', 'Fiscal Year', 'Fiscal Period', 'Report Date', 'Publish Date']].isnull().sum())
print("\ndf3 合并键空值统计：")
print(df3[['Ticker', 'SimFinId', 'Currency', 'Fiscal Year', 'Fiscal Period', 'Report Date', 'Publish Date']].isnull().sum())

# 检查Fiscal Period取值差异
print("\ndf1 Fiscal Period取值：", df1['Fiscal Period'].unique())
print("df2 Fiscal Period取值：", df2['Fiscal Period'].unique())
print("df3 Fiscal Period取值：", df3['Fiscal Period'].unique())

# 统一日期格式
date_cols = ['Report Date', 'Publish Date']
for col in date_cols:
    df1[col] = pd.to_datetime(df1[col], errors='coerce')
    df2[col] = pd.to_datetime(df2[col], errors='coerce')
    df3[col] = pd.to_datetime(df3[col], errors='coerce')

步骤2：调整合并逻辑，去重后合并 #

统一合并键，先清理单表重复记录再合并：

# 去重各表的重复合并键记录
merge_keys = ['Ticker', 'SimFinId', 'Currency', 'Fiscal Year', 'Report Date']
df1 = df1.drop_duplicates(subset=merge_keys)
df2 = df2.drop_duplicates(subset=merge_keys)
df3 = df3.drop_duplicates(subset=merge_keys)

# 重新实现合并函数
def getXDataMerged():    
    print('Income Statement CSV data is(rows, columns): ', df1.shape)
    print('Balance Sheet CSV data is: ', df2.shape)
    print('Cash Flow CSV data is: ' , df3.shape)

    # 合并df1与df2
    result = pd.merge(df1, df2, on=merge_keys, how='inner', suffixes=('_inc', '_bal'))
    # 合并df3
    result = pd.merge(result, df3, on=merge_keys, how='inner', suffixes=('', '_cf'))        
    print('Merged X data matrix shape is: ', result.shape)    
    return result

X = getXDataMerged()
X.to_csv("Annual_Stock_Price_Fundamentals.csv")

步骤3：如需保留全量记录，清理outer join结果 #

如果业务需要保留所有记录，用outer join后去重：

merge_keys = ['Ticker', 'SimFinId', 'Currency', 'Fiscal Year', 'Report Date']
result = pd.merge(df1, df2, on=merge_keys, how='outer', suffixes=('_inc', '_bal'))
result = pd.merge(result, df3, on=merge_keys, how='outer', suffixes=('', '_cf'))
# 清理重复行
result = result.drop_duplicates(subset=merge_keys)
print('Cleaned merged data shape: ', result.shape)

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