monsoon85 monsoon85 -4 years ago 77
Python Question

Calculate various differences in days using DateOffSet using loops

given a fame with the next five Saturdays:

start_date =
0 04.03.2017
1 11.03.2017
2 18.03.2017
3 25.03.2017
4 01.04.2017


and a list which I use for calculating end_dates

duration = [4, 8, 15, 22].


I would like to create a new frame which based on start_date plus duration, e.g.

start_date[0] = duration[0] = 07.03.2017
start_date[1] = duration[0] = 11.03.2017
start_date[2] = duration[0] = 18.03.2017
...


I tried solving this using pd.DateOffset:

for j in range(len(duration)):
end_date = []
for i in start_dates:
# get offset date, convert it and append it to end_date list
z = pd.to_datetime(i) + pd.DateOffset(days=duration[(j)])
end_date.append(z.strftime("%d.%m.%Y"))


However, I get the following output:

['07.04.2017', '07.11.2017', '22.03.2017', '29.03.2017', '08.01.2017']


Where is the issue? Is it because of the DateOffset function? Unfortunately, I canĀ“t see a pattern regarding the export.

Any hints?

Thanks, Mark

Answer Source

The problem is not with DateOffset(). The problem is you do not specify the date format when you pass your date string to pandas.

So pandas is guessing the date format. What you should do is passing the format parameter when using to_datetime().

df
   start_date
0  04.03.2017
1  11.03.2017
2  18.03.2017
3  25.03.2017
4  01.04.2017

df['py_start_date'] = pd.to_datetime(df['start_date'], format='%d.%m.%Y')

   start_date py_start_date
0  04.03.2017      2017-03-04
1  11.03.2017      2017-03-11
2  18.03.2017      2017-03-18
3  25.03.2017      2017-03-25
4  01.04.2017      2017-04-01

for d in duration:
    df['offset {}'.format(d)] = df['py_start_date'] + pd.DateOffset(days=d)

    start_date py_start_date   offset 4   offset 8  offset 15  offset 22
0  04.03.2017      2017-03-04 2017-03-08 2017-03-12 2017-03-19 2017-03-26
1  11.03.2017      2017-03-11 2017-03-15 2017-03-19 2017-03-26 2017-04-02
2  18.03.2017      2017-03-18 2017-03-22 2017-03-26 2017-04-02 2017-04-09
3  25.03.2017      2017-03-25 2017-03-29 2017-04-02 2017-04-09 2017-04-16
4  01.04.2017      2017-04-01 2017-04-05 2017-04-09 2017-04-16 2017-04-23
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download