how to combine year and month column and add a date

Question

I have a DataFrame with a column of months and a column of years:

A = [2, 3, 4]
B = [2013, 2014, 2015]
df = pd.DataFrame({
    'A': A,
    'B': B,
})

I want to add the end date of each month and generate a new column like this:

A = [2, 3, 4]
B = [2013, 2014, 2015]
C = [2013-2-28, 2014-3-31, 2015-4-30]
df = pd.DataFrame({
    'A': A, 
    'B': B, 
    'assessDate': C,
})

Does this answer your question? [Find the end of the month of a Pandas DataFrame Series](https://stackoverflow.com/questions/37354105/find-the-end-of-the-month-of-a-pandas-dataframe-series) — Emma, Mar 06 '23 at 17:05

score 1 · Answer 1 · answered Mar 06 '23 at 17:05

You can make use of calendar.monthrange(year, month), which returns a tuple where the first element is the first day of the month and the second is the number of days in the month in the given year.

Code:

import calendar

A = [2, 3, 4, 6, 8, 9, 10, 11]
B = [2013, 2014, 2015, 2019, 2020, 2021, 2022, 2023]
C = [f"{year}-{month}-{calendar.monthrange(year, month)[1]}" for year, month in zip(B, A)]
df = pd.DataFrame({'A': A, 'B': B, 'assessDate': C})

Output:

    A     B  assessDate
0   2  2013   2013-2-28
1   3  2014   2014-3-31
2   4  2015   2015-4-30
3   6  2019   2019-6-30
4   8  2020   2020-8-31
5   9  2021   2021-9-30
6  10  2022  2022-10-31
7  11  2023  2023-11-30

score 0 · Answer 2 · answered Mar 06 '23 at 17:08

My suggestion would be to use pd.to_datetime() to turn those into a datetime for the first of the month.

Pandas then allows you to add an offset to that datetime representing the difference between the start and end of the month.

df['assessDate'] = pd.to_datetime(dict(year=df['B'], month=df['A'], day=1)) + pd.offsets.MonthEnd(0)

how to combine year and month column and add a date

2 Answers2