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

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!!

CodePudding user response：

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.