How to create a list of date and year with special condition in Python?

Question

I'm very new to Python. I'm trying to create a list of date and a list of its year, which I will pass to my python loop script.

The date will be the first day of each month in a given time period. The tricky part is the year will be last year for each January. e.g. for '01Jan2022', the Year will be '2021'.

Currently I listed them out in my python code as below:

Date = ['01Feb2022', ..., '01Dec2022']
Year = '2022'

Then manually change and run for Jan in each year, for example in 2022:

Date = '01Jan2022'
Year = '2021'

The python script that I will pass to:

Query = """select distinct A.name, count(A.number)
           from A, B
           where Date between A.Opendate and A.Enddate
           and B.Status='Y'
           Group by A.name"""

def summary(Date, Year, Query):
    try:
        cur = conn()
        cur.execute(Query, {'Date': Date, 'Year':Year})
        df = pd.DataFrame(cur.fetchall())
        df.columns = [x[0] for x in cur.description]
        Return df


df = summary(Dates[0], Query)
for i in range(1, len(Dates)):
    df = pd.concat([df, plan(Dates[i], Query)], axis=0)

How to create a python script to automatically generate it? so that I don't need to list all date and also don't need to run Jan in each year separately.

I tried to define the date as below:

start = '01Feb2022'
end = '01Jul2022'

Date = pd.date_range(start, end, freq='MS')
Date.strftime('%d%b%Y')

However, how to link it with the year? especially for Jan? Any Advice?

Appreciate any recommendations!!

@ScottHunter, thank you!! I'm very new here and also new to python. Just updated my code. Any advice will be so appreciated. — user20159866, Oct 06 '22 at 17:30
it would be nice to see the loop script for which you need these lists. what you are asking seems simple but cannot figure out what is your input format is and what is your output format that you want @user20159866 — sos, Oct 06 '22 at 20:45

shawn caza · Accepted Answer · 2022-10-08T02:47:35.267

I took some ideas from here to apply to your use case.

import calendar
from datetime import *
from dateutil.relativedelta import relativedelta

def summary(date_dict):

    print(date_dict)


def generate_months(start_day, end_day):
    # convert start and end dates to datetime objects
    date1 = datetime.strptime(start_day, "%d%b%Y")
    date2 = datetime.strptime(end_day, "%d%b%Y")
    
    # Loop through all months from start to end.
    while date1 <= date2:

        # use date1 year. Unless it's January, in which case subtract a year
        Year = str(date1.year) if date1.month != 1 else str((date1 - relativedelta(years=1)).year)
        
        date_dict = {'Date': date1.strftime("%d%b%Y"), 'Year':Year}
        # send back a dictionary with current date and year combination 
        yield date_dict
        
        # set date1 to the next month so we can continue the sequence
        date1 = date1 + relativedelta(months=1)

# Pass start and end dates into generator function
months = generate_months(start_day ='01Dec2021', end_day='01Dec2022')

for month in months:
    summary(month)

Result:

{'Date': '01Dec2021', 'Year': '2021'}
{'Date': '01Jan2022', 'Year': '2021'}
{'Date': '01Feb2022', 'Year': '2022'}
{'Date': '01Mar2022', 'Year': '2022'}
{'Date': '01Apr2022', 'Year': '2022'}
{'Date': '01May2022', 'Year': '2022'}
{'Date': '01Jun2022', 'Year': '2022'}
{'Date': '01Jul2022', 'Year': '2022'}
{'Date': '01Aug2022', 'Year': '2022'}
{'Date': '01Sep2022', 'Year': '2022'}
{'Date': '01Oct2022', 'Year': '2022'}
{'Date': '01Nov2022', 'Year': '2022'}
{'Date': '01Dec2022', 'Year': '2022'}

If you're new to python, maybe you're new to generators. Here's more about what the yield statement does.

Thank you so much for your recommendation and sharing! They are very helpful. I'm sorry that I should list more clearly that, if use Jan 2022 as example, the month will still be 01Jan2022, however, the year will be 2021. Any advice on it? I also update my post with the loop script that I use. Thank you again! — user20159866, Oct 07 '22 at 17:17
Do you just need a dictionary of date and year pairs to use in your summary function? I updated my answer to incorporate the summary function. However, I simplified it to just focus on the date dictionary. In your actual summary function I think you can just replace {'Date': Date, 'Year':Year} with the variable Date_dict — shawn caza, Oct 08 '22 at 02:45
Thank you so much! I modified a little bit to get date and year separately and it all works out :) — user20159866, Oct 10 '22 at 16:35
Glad you were able to make it work. Please mark the answer as correct if it helped solve your problem. — shawn caza, Oct 10 '22 at 23:37

How to create a list of date and year with special condition in Python?

1 Answers1