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 :)