pythondatetimemonthcalendardayofmonth

Check if value in Date column value is month end

I am new to Python so I'm sorry if this sounds silly. I have a date column in a DataFrame. I need to check if the values in the date column is the end of the month, if yes then add one day and display the result in the new date column and if not we will just replace the day of with the first of that month.

For example. If the date 2000/3/31 then the output date column will be 2000/4/01 and if the date is 2000/3/30 then the output value in the date column would be 2000/3/1

Now I can do a row wise iteration of the column but I was wondering if there is a pythonic way to do it.

Let's say my Date column is called "Date" and new column which I want to create is "Date_new" and my dataframe is df, I am trying to code it like this but it is giving me an error:

if(df['Date'].dt.is_month_end == 'True'):
    df['Date_new'] = df['Date'] + timedelta(days = 1)
else:
    df['Date_new'] =df['Date'].replace(day=1)
Solution

  • I made your if statement into a function and modified it a bit so it works for columns. I used dataframe .apply method with axis=1 so it operates on columns instead of rows

    import pandas as pd
import datetime

df = pd.DataFrame({'Date': [datetime.datetime(2022, 1, 31), datetime.datetime(2022, 1, 20)]})
print(df)

def my_func(column):
    if column['Date'].is_month_end:
        return column['Date'] + datetime.timedelta(days = 1)
    else:
        return column['Date'].replace(day=1)

df['Date_new'] = df.apply(my_func, axis=1)
print(df)