如何在Pandas DataFrame中获取非数据集日期对应的当选政党？

To find the ruling party for a date that doesn't appear in your election dataset, you need to forward-fill the last known election result to cover the period between elections. Here are two straightforward methods using pandas:

Method 1: Use merge_asof for Arbitrary Dates #

This method is perfect if you need to check specific, non-sequential dates. It matches each target date with the most recent election date that occurred on or before it.

Step-by-Step Code: #

1. First, ensure your Date column is in datetime format:

import pandas as pd

# Your original data
data = {
    'Date': ['1910-04-13', '1913-05-31', '1914-09-05', '1917-05-05', '1919-12-13'],
    'Winner': ['ALP', 'L+NP', 'ALP', 'L+NP', 'L+NP']
}
df = pd.DataFrame(data)
df['Date'] = pd.to_datetime(df['Date'])

2. Create a DataFrame with your target dates (e.g., 1916 and 1918):

target_dates = pd.DataFrame({'Date': pd.to_datetime(['1916-01-01', '1918-06-01'])})

3. Use merge_asof to match target dates with the latest election result:

# Both DataFrames must be sorted by Date (required for merge_asof)
df_sorted = df.sort_values('Date')
target_sorted = target_dates.sort_values('Date')

# Perform the merge
result = pd.merge_asof(target_sorted, df_sorted, on='Date')
print(result)

Output: #

Date Winner
0 1916-01-01    ALP
1 1918-06-01  L+NP

Method 2: Reindex and Forward-Fill for a Date Range #

If you want to fill results for a continuous range of dates (e.g., every year from 1910 to 1920), use this approach:

Step-by-Step Code: #

1. Set Date as the index and sort it:

df_indexed = df.set_index('Date').sort_index()

2. Create your desired date range (e.g., yearly dates):

date_range = pd.date_range(start='1910-01-01', end='1920-12-31', freq='Y')

3. Reindex and forward-fill the missing values:

filled_df = df_indexed.reindex(date_range).ffill()

# Get the result for 1916
print(filled_df.loc['1916'])

Output: #

Winner    ALP
Name: 1916-12-31 00:00:00, dtype: object

Why Your Original Approach Failed #

When you run df['Winner'][df['Date'].dt.year == 1916], it returns an empty Series because there’s no election recorded in 1916. The methods above solve this by carrying forward the last valid election result until the next one occurs.

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