Pandas中筛选两DataFrame共有Portfolio Value并保留日期索引的方法

Hey there! I get it, when you're new to Pandas, keeping track of indexes during operations can be tricky. Let's break down how to solve your problem step by step.

问题分析 #

You tried an inner join but lost the Date index—this happens because Pandas' merge() function doesn't automatically preserve the original index unless you explicitly handle it. Instead, let's look at two straightforward ways to get the result you want:

方法1：使用isin()直接筛选（推荐新手） #

This is the simplest approach for your use case. We'll check which values in df1['Portfolio Value'] exist in df2['Portfolio Value'], then filter df1 to keep only those rows—your Date index will stay intact automatically.

示例代码：

import pandas as pd

# 先构造示例数据（你可以替换成自己的DataFrame）
dates = pd.date_range('2023-01-01', periods=5)
df1 = pd.DataFrame({'Portfolio Value': [100, 200, 300, 400, 500]}, index=dates)
df2 = pd.DataFrame({'Portfolio Value': [200, 400, 600]})

# 核心筛选代码
filtered_df = df1[df1['Portfolio Value'].isin(df2['Portfolio Value'])]

print(filtered_df)

输出结果：

Portfolio Value
2023-01-02              200
2023-01-04              400

As you can see, the Date index is preserved perfectly.

方法2：修正内连接（merge()）保留索引 #

If you prefer using an inner join (maybe for more complex operations later), you just need to temporarily convert the index to a column, perform the merge, then set it back as the index.

示例代码：

# 把df1的索引转为普通列（Date），执行内连接后再恢复索引
filtered_df_merge = df1.reset_index().merge(df2, on='Portfolio Value', how='inner').set_index('Date')

print(filtered_df_merge)

This will give you the exact same result as the first method, with your Date index intact.

为什么之前的内连接丢失了索引？ #

By default, merge() ignores the original index of your DataFrames and uses a default integer index for the result. By adding reset_index() and set_index('Date'), you're explicitly telling Pandas to keep your Date column as the index throughout the process.

内容的提问来源于stack exchange，提问作者lee.edward01