My excel file has following data:
I would like to populate a new row with dates for that particular month starting from first Monday of that month.
Example:
For May 2020 it should be as follows:
The dates are in d/m/yyyy format.
Is there any way to do this via code?
I have tried manually inserting the values but it is hectic to do it manually and also it diminishes all the purpose of coding.
Let's start from month and year number. For your example, May 2020, the are:
monthNo = 5
yearNo = 2020
So the start of month date can be computed as:
myDate = pd.to_datetime(f'{yearNo}-{monthNo:02}-01')
(Timestamp('2020-05-01 00:00:00'))
Then let's define offsets to Monday and to the end of month:
mondayOffs = pd.offsets.Week(0, weekday=0)
eoMonthOffset = pd.offsets.MonthEnd(0)
To compute the date of first Monday in the selected month and the end of this month, run:
startDate = monOffs.rollforward(myDate)
endDate = eoMonthOffset.rollforward(myDate)
getting:
Timestamp('2020-05-04 00:00:00')
Timestamp('2020-05-31 00:00:00')
To generate a sequence of dates between the above dates, run:
seq = pd.date_range(start=startDate, end=endDate)
And the final step, conversion to your desired format you can achieve running:
dates = seq.strftime('%#d/%#m/%Y').tolist()
The result, compressed a bit (several dates in a single line), is:
['4/5/2020', '5/5/2020', '6/5/2020', '7/5/2020', '8/5/2020',
'9/5/2020', '10/5/2020', '11/5/2020', '12/5/2020', '13/5/2020',
'14/5/2020', '15/5/2020', '16/5/2020', '17/5/2020', '18/5/2020',
'19/5/2020', '20/5/2020', '21/5/2020', '22/5/2020', '23/5/2020',
'24/5/2020', '25/5/2020', '26/5/2020', '27/5/2020', '28/5/2020',
'29/5/2020', '30/5/2020', '31/5/2020']
Now it is up to you how you save it in a DataFrame and then save this DataFrame in an Excel file.
Unfortunately, your pictures are absolutely unreadable, so I don't know how to proceed any further.
