How To Sum Numbers Of Next 7 Days

[Developer_2018]

I would like to sum up the seconds that each employee spoke on the phone over the next 7 days.

ID; Person_nr; Name; Datum; Datum_plus7; Call_As_Sekunde
3469; 1111; Musterman, Muster; 19.01.2020; 25.01.2020; 0
3470; 1111; Musterman, Muster; 20.01.2020; 26.01.2020; 30600
3471; 1111; Musterman, Muster; 21.01.2020; 27.01.2020; 30600
3472; 1111; Musterman, Muster; 22.01.2020; 28.01.2020; 30600
3473; 1111; Musterman, Muster; 23.01.2020; 29.01.2020; 30600
3474; 1111; Musterman, Muster; 24.01.2020; 30.01.2020; 18000
3475; 1111; Musterman, Muster; 25.01.2020; 31.01.2020; 0
3476; 1111; Musterman, Muster; 26.01.2020; 01.02.2020; 0
3477; 1111; Musterman, Muster; 27.01.2020; 02.02.2020; 30600
3478; 1111; Musterman, Muster; 28.01.2020; 03.02.2020; 30600
3479; 1111; Musterman, Muster; 29.01.2020; 04.02.2020; 30600
3480; 1111; Musterman, Muster; 30.01.2020; 05.02.2020; 30600
3481; 1111; Musterman, Muster; 31.01.2020; 06.02.2020; 18000
16698;1111; Musterman, Muster; 01.02.2020; 07.02.2020; 0
16699;1111; Musterman, Muster; 02.02.2020; 08.02.2020; 0
16700;1111; Musterman, Muster; 03.02.2020; 09.02.2020; 30600
16701;1111; Musterman, Muster; 04.02.2020; 10.02.2020; 30600
16702;1111; Musterman, Muster; 05.02.2020; 11.02.2020; 30600
16703;1111; Musterman, Muster; 06.02.2020; 12.02.2020; 30600
16704;1111; Musterman, Muster; 07.02.2020; 13.02.2020; 18000
16705;1111; Musterman, Muster; 08.02.2020; 14.02.2020; 0
16706;1111; Musterman, Muster; 09.02.2020; 15.02.2020; 0
I wrote this code:

i=0     
j=0     
Summe=0       
total_7days=[]
#Current Day in row                                                            
for i in range(0,24): 
      # the next 7 days                                                                                                                                                                              
      for j in range(0,7):
       if (df['Datum'][i+j] <= df['Datum_plus7'][i] and df['Personal_nr'][i+j] == df['Personal_nr'][i]  ):
           Summe +=df['Call_AS_Sekunde'][j]
           print(str(df['Personal_nr'][i])+ '---'+ str(df['Personal_nr'][i+j])+ '---' + df['Datum'][i+j] +'---' +df['Datum_plus7'][i]+'---'+ str(df['call_As_Sekunde'][i+j]))
   total_7days.append(Summe)
   Summe=0
   print(total_7days[i])

Output:
Person_nr      Datum        Datum_7_Days  Call_As_Sekunde 
...                                 
1111---1111---25.01.2020---31.01.2020---  0                                                                           
1111---1111---26.01.2020---31.01.2020---  0                                                                                                                                   
1111---1111---27.01.2020---31.01.2020--- 30600                                                                   
1111---1111---28.01.2020---31.01.2020--- 30600                                                                        
1111---1111---29.01.2020---31.01.2020--- 30600                                                                            
1111---1111---30.01.2020---31.01.2020--- 30600                                                                               
1111---1111---31.01.2020---31.01.2020--- 18000                                                                                  
                  SUM of last seven days=140400  
                                                                                               
1111---1111---01.02.2020---01.02.2020---   0
                  SUM of last seven days=30600 

Error:
The code works correctly until 25.01.2020. From 26.01.2020 on it doesn't work properly because the 7th day is in the next month. I mean from 26.01.2020 to 31.01.2020 there is no calculation, because the next 7 days are in the next month! 
[b]26.01.2020 to 01.02.2020 ??? 
   27.01.2020 to 02.02.2020 ???
.. 31.01.2020 to 06.02.2020 ???[/b] 

How can I solve this problem?

Thanks a lot for your help

Best regards

[perfringo]

It is usually good idea to take advantage of stuff which is built-in into Python for specific reason.

>>> from datetime import datetime, timedelta
>>> my_date = '25.01.2020'
>>> date = datetime.strptime(my_date, '%d.%m.%Y')
>>> date
datetime.datetime(2020, 1, 25, 0, 1)
>>> date + timedelta(7)
datetime.datetime(2020, 2, 1, 0, 1)

If data in tabular form you may look into Pandas.

[Developer_2018]

Thanks a lot for your suggestion. But I have tried with this method. I became Error by datetime.strptime!

It is usually good idea to take advantage of stuff which is built-in into Python for specific reason.

>>> from datetime import datetime, timedelta
>>> my_date = '25.01.2020'
>>> date = datetime.strptime(my_date, '%d.%M.%Y')
>>> date
datetime.datetime(2020, 1, 25, 0, 1)
>>> date + timedelta(7)
datetime.datetime(2020, 2, 1, 0, 1)

If data in tabular form you may look into Pandas.

[Developer_2018]

by executing date = datetime.strptime(df['datum'], '%d.%M.%Y')become this Error:

Error:
TypeError: strptime() argument 1 must be str, not Series

[perfringo]

My initial code consisted a typo which I fixed: instead of %M (which is minutes) should be %m (which is month). You can look up formatting codes from documentation: strftime and strptime format codes

This example was for string to datetime, not for dataframe column.

[Developer_2018]

ok. thanks for your answer. do you know what is the problem with my code? How can I solive it?

[perfringo]

Assuming that data you provided is in csv file named 'pandas_data_range.csv' then this should work:

- read data to DataFrame using following settings: sep - regex which uses ; as separator and clears all spaces before and after, engine - use Python instead of c (due to regex syntax), header - first row in file, parse_dates - parse values in col 'Datum_plus7' into dates; date_parser - use pd.to_datetime for parsing

- create report which filters 'Datum_plus7' column for specific date range, groups it by 'Name' column and uses 'Call_As_Sekunde' to sum values.

import pandas as pd

df = pd.read_csv('pandas_date_range.csv', sep="\s*[;]\s*", engine='python', header=[0],
                 parse_dates=['Datum_plus7'],
                 date_parser=pd.to_datetime)

report = df[df['Datum_plus7'].isin(pd.date_range('2020-01-25',
                                                 '2020-02-02'))].groupby(by=['Name'])['Call_As_Sekunde'].sum()

print(report)

Output of print should be somathing like:

Output:
Name
Musterman, Muster    171000
Name: Call_As_Sekunde, dtype: int64

I have no idea on which dates etc report should be generated so I just hardcoded start and end in date_range.

[Developer_2018]

I should calculate from every day the next 7 days. therfore you donot need to write it as hardcore!

for Example: my idae is:
...
3476; 1111; Musterman, Muster; 26.01.2020; 01.02.2020; 0
3477; 1111; Musterman, Muster; 27.01.2020; 02.02.2020; 30600
3478; 1111; Musterman, Muster; 28.01.2020; 03.02.2020; 30600
3479; 1111; Musterman, Muster; 29.01.2020; 04.02.2020; 30600
3480; 1111; Musterman, Muster; 30.01.2020; 05.02.2020; 30600
3481; 1111; Musterman, Muster; 31.01.2020; 06.02.2020; 18000
16698;1111; Musterman, Muster; 01.02.2020; 07.02.2020; 0
SUM= 140400
I mean, you take the current value from column date and for the next 7 days you sum up the call_as_second together.
For the next row:
3477; 1111; Musterman, Muster; 27.01.2020; 02.02.2020; 30600
3478; 1111; Musterman, Muster; 28.01.2020; 03.02.2020; 30600
3479; 1111; Musterman, Muster; 29.01.2020; 04.02.2020; 30600
3480; 1111; Musterman, Muster; 30.01.2020; 05.02.2020; 30600
3481; 1111; Musterman, Muster; 31.01.2020; 06.02.2020; 18000
16698;1111; Musterman, Muster; 01.02.2020; 07.02.2020; 0
16699;1111; Musterman, Muster; 02.02.2020; 08.02.2020; 0
Sum= 140400
and so on...