How to loop the days from frequency until the end of the month for Python?

Question

Let's imagine this is my table (better view here in google sheets):

index   frequency   start_execution_date    end_month
0       Weekly      2022-11-06 22:15:00 07-02-2023
1       Daily       2022-11-06 22:15:00 07-02-2023
2       Monthly     2022-11-06 22:15:00 07-02-2023
3       ??          2022-11-06 22:15:00 07-02-2023
4       Once        2022-11-06 21:00:00 07-02-2023
5       Every 1 months  2022-11-06 21:00:00 07-02-2023
6       Every 12 months 2022-11-06 21:00:00 07-02-2023
7       Every 3 months  2022-11-06 21:00:00 07-02-2023
8       SQL Startup 2021-07-29 12:38:01 07-02-2023
9       Every 2 weeks   2022-11-10 12:30:00 07-02-2023
10      Every 6 months  2022-11-10 12:30:00 07-02-2023

I want create a "next_schedule" column to reflect the "frequency" column. The starting date is "start_execution_date" and the ending date is "end_month" column. The issue is my code is looping endlessly 1 time only. So it predicts once and repeats the same thing.

from calendar import mdays, calendar
from datetime import datetime as dt, timedelta
from datetime import date
from dateutil.relativedelta import relativedelta
from dateutil.rrule import rrule, DAILY

predict = []

for frequency in df1['frequency']:
  if frequency == 'Daily':
    next= df1['start_execution_date'] + pd.Timedelta(days=1)
    predict.append(next)
  elif frequency == 'Weekly':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=1)
    predict.append(next)
  elif frequency == 'Every 2 Weeks':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=2)
    predict.append(next)
  elif frequency == 'Monthly':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=4)
    predict.append(next)
  elif frequency == 'Every 1 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=4)
    predict.append(next)
  elif frequency == 'Every 3 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=12)
    predict.append(next)
  elif frequency == 'Every 6 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=24)
    predict.append(next)
  else:
    next= df1['start_execution_date']
    predict.append(next)

df1.insert(4, "next_schedule", predict, True)
df1

This is what it looks like now:

Using the above example, it's supposed to loop to the next week, but I can't figure it out. I used the while-loop before the for-loop but I ran into an error. For instance, in the example, the next_schedule for weekly frequency is supposed to be 2022-11-20 22:15:00, not 2022-11-13 22:15:00 again, and continually through the following weeks until the end_date.

Secondly, how do make the next_schedule into separate lines instead of all in 1 cell?

Thank you so much!

alphaBetaGamma · Accepted Answer · 2023-01-09T08:45:55.133

I updated your code to suit the solution you seek.

The way you inserted the next schedule column is for adding a row not a column
You need to convert the column in a format when it can be added to timedelta output

I hope this solves your problem

from calendar import mdays, calendar
from datetime import datetime as dt, timedelta
from datetime import date
from dateutil.relativedelta import relativedelta
from dateutil.rrule import rrule, DAILY
from dateutil.parser import parse

df1 = pd.read_csv("Downloads/Frequency Loop - Sheet1.csv")
df1['next_schedule'] = ""
#convert start & end_excution_date format into the same date format
df1["start_execution_date"] = df1["start_execution_date"].apply(lambda x: dt.strptime(x, "%Y-%m-%d %H:%M:%S"))
df1["end_month"] = df1["end_month"].apply(lambda x: dt.strptime(parse(x).strftime('%Y-%m-%d %H:%M:%S'), "%Y-%m-%d %H:%M:%S"))
for frequency in df1['frequency']:
    predict = []
    start_time = df1.loc[df1.frequency==frequency,'start_execution_date'].values[0]
    end_date = df1.loc[df1.frequency==frequency,'end_month'].values[0]
    next_date = start_time
    if frequency == 'Daily':
        while(next_date<end_date):
            next_date = next_date + pd.Timedelta(days=1)
            predict.append(next_date)
    elif frequency == 'Weekly':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=1)
            predict.append(next_date)
    elif frequency == 'Every 2 Weeks':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=2)
            predict.append(next_date)
    elif frequency == 'Monthly':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=4)
            predict.append(next_date)
    elif frequency == 'Every 1 Months':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=4)
            predict.append(next_date)
    elif frequency == 'Every 3 Months':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=12)
            predict.append(next_date)
    elif frequency == 'Every 6 Months':
        while(next_date<=end_date):
            next_date = next_date + pd.Timedelta(weeks=24)
            predict.append(next_date)
    else:
        predict.append(df1.loc[df1.frequency==frequency,'start_execution_date'].values[0] )
    df1.loc[df1.frequency==frequency,'next_schedule'] = " ".join([str(pd.to_datetime(i)) for i in predict])

df1

output:

    index        frequency start_execution_date  end_month  \
0       0           Weekly  2022-11-06 22:15:00 2023-07-02   
1       1            Daily  2022-11-06 22:15:00 2023-07-02   
2       2          Monthly  2022-11-06 22:15:00 2023-07-02   
3       3               ??  2022-11-06 22:15:00 2023-07-02   
4       4             Once  2022-11-06 21:00:00 2023-07-02   
5       5   Every 1 months  2022-11-06 21:00:00 2023-07-02   
6       6  Every 12 months  2022-11-06 21:00:00 2023-07-02   
7       7   Every 3 months  2022-11-06 21:00:00 2023-07-02   
8       8      SQL Startup  2021-07-29 12:38:01 2023-07-02   
9       9    Every 2 weeks  2022-11-10 12:30:00 2023-07-02   
10     10   Every 6 months  2022-11-10 12:30:00 2023-07-02   

                                        next_schedule  
0   2022-11-13 22:15:00 2022-11-20 22:15:00 2022-1...  
1   2022-11-07 22:15:00 2022-11-08 22:15:00 2022-1...  
2   2022-12-04 22:15:00 2023-01-01 22:15:00 2023-0...  
3                                 2022-11-06 22:15:00  
4                                 2022-11-06 21:00:00  
5                                 2022-11-06 21:00:00  
6                                 2022-11-06 21:00:00  
7                                 2022-11-06 21:00:00  
8                                 2021-07-29 12:38:01  
9                                 2022-11-10 12:30:00  
10                                2022-11-10 12:30:00

Thank you so much for this. Is there a way to for the next_schedule to loop until the end_month? Right now, it's only looping once. I'm very stuck on that part. I'm not sure how to manipulate the dataframe so it can loop more than once per line. For instance, Daily 2022-11-06 22:15:00 should be looping in increments of 1 day until end_month (feb. 7th, 2023). — Kitty.Cattie, Jan 08 '23 at 07:28
Hello, you are amazing. Thank you so much! Now, I'm going to try to separate these onto different rows so Tableau can work with them. :) — Kitty.Cattie, Jan 09 '23 at 18:57
Great, if this does answer your question, mark it as solved and upvote the answer so others who seek a similar solution would benefit from it! — alphaBetaGamma, Jan 09 '23 at 20:38

How to loop the days from frequency until the end of the month for Python?

1 Answers1