单个Dataframe内基于日期关联列并整理数据的方法求助

Hey there! I see you're trying to restructure a single DataFrame by aligning D2price values with their corresponding D1date entries, instead of merging two separate DataFrames. Let's walk through how to solve this.

Understanding Your Goal #

You have a DataFrame with paired date-price columns (D1date/D1price and D2date/D2price), and you want to:

• Match each D2price to the row where D1date equals D2date
• Drop the D2date column entirely
• Fill in NaN for any D1date that doesn't have a corresponding D2date entry

Step-by-Step Solution #

We can use Pandas' indexing and mapping capabilities to achieve this cleanly. Here's how:

1. (Optional but Recommended) Convert Date Columns to Datetime Type

First, make sure your date columns are properly recognized as datetime objects—this avoids issues with string formatting mismatches:

import pandas as pd

# Sample DataFrame matching your input
data = {
    'D1date': ['1/2/2017', '1/3/2017', '1/4/2017', '1/5/2017'],
    'D1price': [11.4, 12.4, 14.4, 15.5],
    'D2date': ['1/3/2017', '1/4/2017', '1/5/2017', '1/6/2017'],
    'D2price': [11.3, 12.3, 12.4, 12.5]
}
df = pd.DataFrame(data)

# Convert dates to datetime
df['D1date'] = pd.to_datetime(df['D1date'])
df['D2date'] = pd.to_datetime(df['D2date'])

2. Map D2price to Matching D1date

We'll create a lookup series using D2date as the index, then map this to the D1date column to get the aligned D2price values:

# Create a lookup series: D2date -> D2price
d2_price_lookup = df.set_index('D2date')['D2price']

# Map this lookup to D1date to get the new D2price column
df['D2price'] = df['D1date'].map(d2_price_lookup)

# Drop the now-unneeded D2date column
result_df = df.drop(columns=['D2date'])

3. (Optional) Convert Dates Back to Original String Format

If you want to retain the original date string format (e.g., 1/2/2017 instead of datetime objects), add this line:

result_df['D1date'] = result_df['D1date'].dt.strftime('%m/%d/%Y')

Final Result #

Running this code will give you exactly the output you're looking for:

D1date  D1price  D2price
0  1/2/2017     11.4      NaN
1  1/3/2017     12.4     11.3
2  1/4/2017     14.4     12.3
3  1/5/2017     15.5     12.4

How It Works #

• The lookup series lets us directly match dates between D1date and D2date
• Pandas automatically fills NaN for any D1date that doesn't have a corresponding entry in D2date
• This approach is efficient and avoids unnecessary DataFrame merges, which is perfect for your single-DataFrame use case

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