如何将事件数据转换为含事件虚拟变量的时间序列横截面面板数据？

I'll walk you through two common approaches to solve this problem—using Python (Pandas) (great for general data manipulation) and Stata (popular in social sciences for panel analysis). Both methods will generate a daily panel for each country in 2012, with dummy variables marking the occurrence of your two events.

Approach 1: Python with Pandas #

This method leverages Pandas' datetime handling and cross-join capabilities to build the panel efficiently.

Step-by-Step Code & Explanation #

import pandas as pd

# 1. Load your sample data
raw_data = {
    'country': [1, 2, 3, 4],
    'date1': ['03/01/2012', '05/04/2012', '07/12/2012', '04/02/2012'],
    'date2': ['05/01/2012', '12/10/2012', '20/03/2012', '24/12/2012']
}
df = pd.DataFrame(raw_data)

# 2. Parse date strings to datetime objects (critical for comparisons)
# Note: Your dates are in DD/MM/YYYY format, so we specify the format explicitly
df['date1'] = pd.to_datetime(df['date1'], format='%d/%m/%Y')
df['date2'] = pd.to_datetime(df['date2'], format='%d/%m/%Y')

# 3. Create a full daily date range for 2012 (2012 is a leap year, so 366 days)
daily_dates = pd.date_range(start='2012-01-01', end='2012-12-31', freq='D')

# 4. Build the base panel: cross-join countries with all daily dates
countries = df[['country']].drop_duplicates()
# Use a temporary 'key' column to perform the cross join
base_panel = countries.assign(key=1).merge(
    pd.DataFrame({'date': daily_dates, 'key': 1}),
    on='key'
).drop('key', axis=1)

# 5. Merge back the original event dates to the panel
panel_with_events = base_panel.merge(df, on='country', how='left')

# 6. Create dummy variables for each event
# 1 if the day matches the event date, 0 otherwise
panel_with_events['event1'] = (panel_with_events['date'] == panel_with_events['date1']).astype(int)
panel_with_events['event2'] = (panel_with_events['date'] == panel_with_events['date2']).astype(int)

# 7. Extract year, month, day as separate columns (formatted with leading zeros)
panel_with_events['year'] = panel_with_events['date'].dt.year
panel_with_events['month'] = panel_with_events['date'].dt.month.astype(str).str.zfill(2)
panel_with_events['day'] = panel_with_events['date'].dt.day.astype(str).str.zfill(2)

# 8. Finalize the panel: keep only desired columns and sort
final_panel = panel_with_events[['country', 'year', 'month', 'day', 'event1', 'event2']]
final_panel = final_panel.sort_values(['country', 'year', 'month', 'day']).reset_index(drop=True)

# Preview the first few rows
print(final_panel.head())

Output Preview #

The first 5 rows will match your example plus the dummy columns:

country  year month day  event1  event2
0        1  2012    01  01       0       0
1        1  2012    01  02       0       0
2        1  2012    01  03       1       0
3        1  2012    01  04       0       0
4        1  2012    01  05       0       1

Approach 2: Stata #

Stata has built-in tools for panel data, making this straightforward for users familiar with the software.

Step-by-Step Code & Explanation #

// 1. Load your raw data
clear
input country str10 date1 str10 date2
1 "03/01/2012" "05/01/2012"
2 "05/04/2012" "12/10/2012"
3 "07/12/2012" "20/03/2012"
4 "04/02/2012" "24/12/2012"
end

// 2. Convert date strings to Stata's internal date format (DD/MM/YYYY)
gen date1_d = date(date1, "DMY")
gen date2_d = date(date2, "DMY")
format date1_d date2_d %td // Apply human-readable date format

// 3. Save original data to a temporary file for later merging
tempfile original_data
save `original_data'

// 4. Create a full daily date range for 2012 (366 days for leap year)
clear
set obs 366
gen date = mdy(1,1,2012) + _n - 1 // Start at Jan 1, 2012, increment by 1 day
format date %td

// 5. Get unique country list and cross-join with dates
use `original_data', clear
levelsof country, local(countries) // Store country IDs in a macro

clear
set obs 366
gen date = mdy(1,1,2012) + _n -1
format date %td

// Expand to match number of countries and assign country IDs
expand `=wordcount("`countries'")'
gen country = word("`countries'", _n)
destring country, replace
sort country date

// 6. Merge back the original event dates
merge m:1 country using `original_data', keepusing(date1_d date2_d)
drop _merge // Remove merge indicator column

// 7. Create dummy variables for events
gen event1 = (date == date1_d)
gen event2 = (date == date2_d)
// Replace missing values (days without events) with 0
replace event1 = 0 if missing(event1)
replace event2 = 0 if missing(event2)

// 8. Extract year, month, day as separate columns (with leading zeros)
gen year = year(date)
gen month = string(month(date), "%02.0f")
gen day = string(day(date), "%02.0f")

// 9. Finalize the panel
keep country year month day event1 event2
sort country year month day

// Preview first 10 rows
list in 1/10

Key Notes for Both Methods #

• Date Format: Ensure you correctly specify the input date format (DD/MM/YYYY in your case) to avoid parsing errors.
• Leap Year: 2012 is a leap year, so we account for 366 days. For non-leap years, adjust the number of observations/dates accordingly.
• Dummy Variables: The dummies will be 1 only on the exact event date for each country, 0 otherwise.

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