1
$\begingroup$

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

$\endgroup$
1
$\begingroup$

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
$\endgroup$
3
  • $\begingroup$ Thanks a lot for your reply. How can I apply this if the dates are not consecutive? For example if Date 1 = 05/04/2018 and Date 2 = 08/04/2018? $\endgroup$ – zcbcpaoa Jan 7 at 18:22
  • $\begingroup$ You don't need to use the index at all. The loop doesn't need it and your desired output doesn't need it. I only included because I assumed you would have dates to work with. Just don't include index=pd.to_datetime() when constructing the dataframe and the index will just be integers so it won't matter if they consecutive or not. $\endgroup$ – amdopt Jan 8 at 17:06
  • $\begingroup$ Thanks for your answer but I'm afraid I have expressed myself badly. I have the dates to work on, I have a time series (dates in ascending order) but sometimes the following datum refers to a date NOT immediately following. An example, to explain me better: if I have the datum of January 8, 2021, it could happen that the next datum available is on January 11, 2021 (due to non-working days). So, what you wrote in the previous comment still holds true? $\endgroup$ – zcbcpaoa Jan 8 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.