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

Converting Excel Style Dates with Pandas

When working with XML files, one may encounter timestamps in the Excel style format, such as 42580.3333333333. Fortunately, Pandas offers a straightforward method to convert these numerical values into regular datetime objects.

To accomplish this conversion, we utilize two key components: a TimedeltaIndex and a scalar datetime. The TimedeltaIndex is created from the Excel-style float values, representing the number of days since a particular date. By adding this TimedeltaIndex to the scalar datetime for January 1, 1900, we effectively adjust the timestamp to its corresponding date and time.

Here's an example to demonstrate this process:

import pandas as pd
import datetime as dt

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

print(df)

Output:

           date                  real_date
0  42580.333333 2016-07-31 07:59:59.971200
1  10023.000000 1927-06-12 00:00:00.000000

Alternatively, if you are dealing with dates that originate from Excel's date system, which begins on December 30, 1899, you can adjust the conversion as follows:

df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d') + dt.datetime(1899, 12, 30)

This correction aligns the dates with Excel's internal date system, ensuring accurate date conversions.

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