How to do an event study for multiple companies with different event dates?

Question

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 1

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