CODEWITHSUNDEEP
pythonexcelpandas
Fathi
Fathi

Reputation: 15

Convert Month&Year column into datetime format python

I have some excel that has the columns Month and Year and I want to have a newly created column that converts the month and year into a datetime column of the format %d/%m/%Y with the day being the 1st of that month.

Example:

Month Year
3 2021
5 2021

The new column should look like: 01-03-2021.

Datum
01/03/2021
01/05/2021

I have tried this:

import datetime
df = pd.read_excel(r"C:\Users\f0cdnu\Downloads\Test.xlsx")
df['Datum'] = datetime.datetime(df.Year, df.Month,1)
df

Gives cant convert series to int error and this:

df = pd.read_excel(r"C:\Users\f0cdnu\Downloads\Test.xlsx")
df['Datum'] = pd.to_datetime(df.Year*10000+df.Month*100)

gives Mm

Upvotes: 0

Views: 977

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

A datetime in both Python and Excel has no format. In both cases it's a binary value. In Excel dates are stored as OLE Automation DATE values, also known as OADAte - a 64-bit float where the integral part is an offset from Decemberr 30, 1899 and the fractional part the time of day.

Formats apply only when parsing text into dates or formating dates to strings for export or display. In Excel, the display of a date depends on the cell's style.

Since you use Pandas, the problem becomes:

  1. how to create a new datetime column from parts and
  2. how to control the cell style when exporting to Excel

Create the column

The answers to How to convert columns into one datetime column in pandas? show several ways to add the new column. The most elegant would be :

df['Datum'] = pd.to_datetime(dict(year=df.Year, month=df.Month, day=1))

or

df['Datum'] =pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d')

In this case the number is treated as string parsed using the format parameter.

Specify a date format in Excel

The answers to Python Pandas custom time format in Excel output show how to control the display style of datetime columns through the datetime_format property of the ExcelWriter object :

writer = pd.ExcelWriter("time.xlsx",  datetime_format='dd/mm/yyyy')
df.to_excel(writer, "Sheet1")

Pandas uses XlsxWriter to write to Excel. Working with Dates and Time how XlsxWriter works with dates in general and Working with Python Pandas and XlsxWriter how to work with Pandas and how to control formatting.

For example, you can set the default date and time styles for all cells:

writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

Or you can specify formats for specific ranges :

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})

# Set the column width and format.
worksheet.set_column('B:B', 18, format1)

# Set the format but not the column width.
worksheet.set_column('C:C', None, format2)

Upvotes: 2

Related Questions