How to combine str and int to return the date at end of the month?

user_8704944 0
Viewed 51

I have a CSV file that has a Year column (int) and a Month column (str).

Year MonthLong
1984 January
1984 January

I'd like to have the last day of each month in a separate column (dd/mm/yy).

I've tried lots of dumb things (just learning and still trying too many of my excel work arounds) but am at a loss now.

months = {'January':1, 'February':2, 'March':3, 'April':4, 'May':5, 'June':6, 'July':7, 'August':8, 'September':9, 'October':10, 'November':11, 'December':12}

#Creates a column with month as a number
tax_cols['Month'] = tax_cols['MonthLong'].map(months)

#Adds a day column to get to_datetime to work - day is wrong
tax_cols['Day'] = tax_cols['MonthLong'].map(months)

#Creates a full date column - day is wrong
tax_cols['FullDate'] = pd.to_datetime(tax_cols[['Year', 'Month', 'Day']])

From here I have tried datetime/relativedelta and monthrange with no success. Any ideas how I can move this forward?

pandas csv python
2 Answers

Use to_datetime with %Y%B for specified year in YYYY and month in month name format and for lass day of month add offsets.MonthEnd:

date = pd.to_datetime(tax_cols['Year'].astype(str) + tax_cols['MonthLong'], format='%Y%B')
tax_cols['FullDate'] = date + pd.offsets.MonthEnd()
print (tax_cols)
   Year MonthLong   FullDate
0  1984   January 1984-01-31
1  1984   January 1984-01-31

If possible some different format you can use errors='coerce' for NaT for this wrong datetimes:

date = pd.to_datetime(tax_cols['Year'].astype(str) + tax_cols['MonthLong'], format='%Y%B', errors='coerce')
tax_cols['FullDate'] = date + pd.offsets.MonthEnd()
print (tax_cols)
   Year MonthLong   FullDate
0  1984   January 1984-01-31
1  1984   January 1984-01-31
user_29010020

First of all, you need to know last day of month. For example

def last_day(month: int, year: int) -> int:
    last_days = {
        1: 31,
        2: 28,
        3: 31,
        4: 30,
        5: 31,
        6: 30,
        7: 31,
        8: 31,
        9: 30,
        10: 31,
        11: 30,
    }
    if month != 2 or (year % 4 != 0 or (year % 100 == 0 and year % 400 != 0)):
        # month is not Feb and year is common
        return last_days[month]
    return 29

Then you can use datetime module for formatting:

from datetime import date


def formatted_date(year, month) -> str:
    return date(year, month, last_day(month, year)).strftime("%d/%m/%y")


print(fotmatted_date(1985, 1))
# 31/01/85
print(formatted_date(1984, 2))
# 29/02/84
user_151913010
Related