如何在Pandas中合并仅按Date对齐、不含Symbol的股票DataFrame？

Hey there! I totally get the frustration with those pesky NaNs popping up when merging your Morningstar stock data—let's break down why this is happening and fix it step by step.

Why You're Seeing NaNs #

The root issue here is the multi-index (Symbol + Date) on your BAC and C DataFrames. When you use pd.concat(axis=1) with these multi-indexed frames, pandas only aligns rows where both the Symbol and Date values match exactly. Since BAC and C have different Symbol values, there are zero matching rows, hence all those NaNs.

Solution: Align Only by Date #

We need to strip out the Symbol from the index first, then merge/concat using just the Date as the alignment key. Here are two straightforward methods:

Method 1: Clean Indexes and Concatenate #

This approach removes the Symbol from the multi-index, adds stock-specific prefixes to columns, then concatenates on the Date index:

import pandas_datareader.data as web
import pandas as pd

# Fetch your data (as you did before)
bac = web.DataReader('BAC', 'morningstar', start='2006-01-01', end='2016-12-31')
c = web.DataReader('C', 'morningstar', start='2006-01-01', end='2016-12-31')

# Remove Symbol from the multi-index, keep Date as the index, and add stock prefixes to columns
bac_clean = bac.reset_index(level='Symbol', drop=True).add_prefix('BAC_')
c_clean = c.reset_index(level='Symbol', drop=True).add_prefix('C_')

# Now concat by columns—pandas will align on the Date index
merged_df = pd.concat([bac_clean, c_clean], axis=1)

Method 2: Use pd.merge with Date as the Key #

If you prefer to work with a flat structure (no index), reset both indexes and merge on Date:

# Reset indexes to turn Symbol and Date into columns
bac_reset = bac.reset_index()
c_reset = c.reset_index()

# Merge on Date, add suffixes to distinguish BAC vs C columns
merged_df = pd.merge(bac_reset, c_reset, on='Date', suffixes=('_BAC', '_C'))

Verify No NaNs #

After merging, check for remaining NaNs (if any) with:

print(merged_df.isna().sum())

If you see a small number of NaNs, it’s likely due to one stock having no data on specific dates. You can drop those rows with merged_df = merged_df.dropna() or forward-fill missing values with merged_df = merged_df.fillna(method='ffill') depending on your needs.

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