How Can Pandas Convert Excel-Style Dates to Python Datetime Objects?

Convert Excel-Style Dates Using Pandas

In the context of data parsing, one may encounter XML files containing datetimes in the Excel-style format, represented as decimal numbers. Pandas, a versatile data manipulation library for Python, offers a straightforward solution for converting these numerical values into standard datetime objects.

Conversion Process:

To transform the Excel-style date to a datetime object using Pandas, the following steps can be followed:

1. Create a TimedeltaIndex from the Excel-Style Date: Using pd.TimedeltaIndex(df['date'], unit='d'), create a TimedeltaIndex from the numerical representation of the date.
2. Add the TimedeltaIndex to a Scalar Datetime: Add the TimedeltaIndex to a scalar datetime representing the reference point. For Excel-style dates, this reference point is 1900-01-01.

Code Example:

import datetime as dt
import pandas as pd

df = pd.DataFrame({'date': [42580.333333, 10023]})
df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d') + dt.datetime(1900, 1, 1)

This process will convert the numerical dates into datetime objects, preserving the timezone information (if any).

Additional Note:

Depending on the version of Excel, the reference point for the numerical dates may differ. For Excel versions released after 1900-01-01, the reference point is 1899-12-30 (as evident in the example provided). It's important to consider the appropriate reference point based on the Excel version used to generate the dates.

The above is the detailed content of How Can Pandas Convert Excel-Style Dates to Python Datetime Objects?. For more information, please follow other related articles on the PHP Chinese website!