Aligning Event Windows for Companies with Different Event Dates
Summary
The document demonstrates how to extract equal-length return windows for several companies when each company has a different event date. The example begins with a date-indexed table of returns and a separate mapping of companies to their individual start and end dates. The goal is to form a new table in which each company contributes only the observations in its own interval, aligned by position within the window.
The included Python example uses a pandas DataFrame, selects a slice for each column, shifts that slice so the event windows line up, and drops rows without observations. Its example output matches the requested four-row table. The answer describes the approach as understandable rather than especially idiomatic and notes that actual data construction is outside its scope. The example assumes equal-length windows and regular row positions; applying the method to real event studies requires care with missing trading dates, date indexing, and window lengths.
Key ideas
- Each company can have its own event-window start date while contributing the same number of observations.
- The example extracts a separate slice from each company's return column.
- Shifting the selected slices aligns their observations into common relative positions.
- Dropping incomplete rows produces a rectangular table of aligned event-window returns.
- The demonstrated approach assumes equal-length windows and does not address missing or irregular dates.
Tags
Full text
# How to do an event study for multiple companies with different event dates?
# How to do an event study for multiple companies with different event dates?
Hi everyone I would like to use Python (or any other program that works well such as R, Excel, etc.) to analyze the impact of an event. Suppose that I have the following dataset (company in the columns, date in the rows):
```
A B C D
Date1 0.11 0.21 0.31 0.41
Date2 0.12 0.22 0.32 0.42
Date3 0.13 0.23 0.33 0.43
Date4 0.14 0.24 0.34 0.44
Date5 0.15 0.25 0.35 0.45
Date6 0.16 0.26 0.36 0.46
Date7 0.17 0.27 0.37 0.47
Date8 0.18 0.28 0.38 0.48
```
Suppose that I want to select an interval of elements depending on list values, such for example from this matrix:
```
A Date2 Date5
B Date3 Date6
C Date4 Date7
D Date5 Date8
```
That is, from company `A` I want to select the values from `Date2` up to `Date5` (which are: `0.12`, `0.13`, `0.14` and `0.15`); from company `B` I want to select the values from `Date3` up to `Date6` (which are: `0.23`, `0.24`, `0.25` and `0.26`) and so on. Notice that date ranges will always have the same length.
Therefore I would like to obtain this new dataset:
```
A B C D
0.12 0.23 0.34 0.45
0.13 0.24 0.35 0.46
0.14 0.25 0.36 0.47
0.15 0.26 0.37 0.48
```
How can I achieve this? I apologize if I don't provide any part of the Python code but I have no idea how to do it, I gave it a try with VBS but I combined the use of macros with corrections by hand...
I only have an idea: I was thinking to create a vector for each column that assumes the value of `1` in correspondence with the date range and `0` otherwise, that is:
```
A B C D
Date1 0 0 0 0
Date2 1 0 0 0
Date3 1 1 0 0
Date4 1 1 1 0
Date5 1 1 1 1
Date6 0 1 1 1
Date7 0 0 1 1
Date8 0 0 0 1
```
and then by multiplying this new dataset with the previous one we would obtain:
```
A B C D
Date1 0 0 0 0
Date2 0.12 0 0 0
Date3 0.13 0.23 0 0
Date4 0.14 0.24 0.34 0
Date5 0.15 0.25 0.35 0.45
Date6 0 0.26 0.36 0.46
Date7 0 0 0.37 0.47
Date8 0 0 0 0.48
```
As a last step we should eliminate all the "cells" that have the value of zero. I don't know if this is the easiest way to do it and I am not able to do the whole process.
Thanks for your insight :)
## Answer by amdopt (score 1)
https://quant.stackexchange.com/a/60347
This Python code will do what you ask. It is not the most pythonic way of accomplishing this but I left it this way because I thought it would make sense if you aren't all that familiar with Python. I use Pandas to accomplish this because the `.shift()` method makes it easy, but you could do this in Numpy or pure Python just as well.
I constructed the DataFrame by hand from your question and I used dates for the index that I turned into pandas datetime objects using `.to_datetime()` because I assume you will have similar datatypes when actually working this out. You will probably want to construct the DataFrame programmatically but that is beyond the scope of your question.
```
import pandas as pd
dates = ['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04',
'2020-01-05', '2020-01-06', '2020-01-07', '2020-01-08']
df = pd.DataFrame({'A': [.11, .12, .13, .14, .15, .16, .17, .18],
'B': [.21, .22, .23, .24, .25, .26, .27, .28],
'C': [.31, .32, .33, .34, .35, .36, .37, .38],
'D': [.41, .42, .43, .44, .45, .46, .47, .48]},
index=pd.to_datetime(dates))
result = pd.DataFrame(index=df.index)
i = 1
for col in df.columns:
result[col] = df[col].iloc[i:i+4]
result[col] = result[col].shift(-i)
i += 1
result.reset_index(level=None, inplace=True)
result.drop('index', axis=1, inplace=True)
result.dropna(inplace=True)
print(result)
```
The above script returns this:
```
A B C D
0 0.12 0.23 0.34 0.45
1 0.13 0.24 0.35 0.46
2 0.14 0.25 0.36 0.47
3 0.15 0.26 0.37 0.48
```Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.