How to Convert Float to Datetime in Pandas DataFrame?

Pandas is a powerful data manipulation library widely used in Python for data analysis and preprocessing tasks. When working with data, it is common to encounter situations where dates and times are represented as floating?point numbers instead of the expected datetime format. In such cases, it becomes essential to convert the float values to datetime objects to perform accurate time?based analysis.

This article aims to provide a comprehensive guide on how to convert float values to datetime objects in a Pandas DataFrame.

Understanding the Importance of Converting Float to Datetime

Datetime objects offer several advantages over float representations of dates and times. By converting float values to datetime objects, we can leverage the rich set of datetime functionalities provided by Pandas and Python, including date arithmetic, time zone handling, resampling, and plotting.

Additionally, converting float to datetime allows for precise time?based comparisons and calculations, enabling accurate analysis and visualisation of time series data.

Method 1: Using Pandas' Built-in Functions

The most straightforward approach is using pd.to_datetime() with the unit parameter to specify the time unit ?

import pandas as pd

# Sample DataFrame with float column representing Unix timestamps
data = {'timestamp': [1620619200.0, 1620705600.0, 1620792000.0]}
df = pd.DataFrame(data)

print("Original DataFrame:")
print(df)

# Convert float to datetime using Pandas' to_datetime() function
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='s')

print("\nAfter conversion:")
print(df)

Original DataFrame:
    timestamp
0  1.620619e+09
1  1.620706e+09
2  1.620792e+09

After conversion:
   timestamp
0 2021-05-10 04:00:00
1 2021-05-11 04:00:00
2 2021-05-12 04:00:00

Key Parameters

• unit='s' ? Specifies timestamps are in seconds since Unix epoch

• unit='ms' ? Use for timestamps in milliseconds

• unit='D' ? Use for timestamps representing days

Method 2: Using Custom Function for Fractional Years

For specialized formats like fractional years, a custom function provides more control over the conversion logic ?

import pandas as pd
from datetime import datetime, timedelta

# Sample DataFrame with float column representing fractional years
data = {'year': [2021.5, 2022.25, 2023.75]}
df = pd.DataFrame(data)

print("Original DataFrame:")
print(df)

# Custom function to convert fractional years to datetime
def convert_fractional_year(year_float):
    year = int(year_float)
    fraction = year_float - year
    
    # Calculate days from fraction (assuming 365.25 days per year)
    days = int(fraction * 365.25)
    
    # Create base date (January 1st of the year)
    base_date = datetime(year, 1, 1)
    
    # Add the fractional days
    return base_date + timedelta(days=days)

# Apply the custom function to the 'year' column
df['datetime'] = df['year'].apply(convert_fractional_year)

print("\nAfter conversion:")
print(df[['year', 'datetime']])

Original DataFrame:
    year
0  21.50
1  22.25
2  23.75

After conversion:
    year    datetime
0  21.50  2021-07-02
1  22.25  2022-04-01
2  23.75  2023-10-01

Handling Different Float Formats

Here's how to handle various float timestamp formats ?

import pandas as pd

# Different float timestamp formats
data = {
    'unix_seconds': [1620619200.0, 1620705600.0],
    'unix_milliseconds': [1620619200000.0, 1620705600000.0],
    'excel_serial': [44331.0, 44332.0]  # Excel date serial numbers
}

df = pd.DataFrame(data)

# Convert different formats
df['datetime_from_seconds'] = pd.to_datetime(df['unix_seconds'], unit='s')
df['datetime_from_ms'] = pd.to_datetime(df['unix_milliseconds'], unit='ms')

# For Excel serial dates (days since 1900-01-01)
df['datetime_from_excel'] = pd.to_datetime(df['excel_serial'], unit='D', origin='1899-12-30')

print(df[['datetime_from_seconds', 'datetime_from_ms', 'datetime_from_excel']])

  datetime_from_seconds datetime_from_ms datetime_from_excel
0   2021-05-10 04:00:00  2021-05-10 04:00:00        2021-05-10
1   2021-05-11 04:00:00  2021-05-11 04:00:00        2021-05-11

Comparison

Conclusion

Converting float values to datetime in Pandas DataFrames is essential for time?series analysis. Use pd.to_datetime() with appropriate unit parameters for standard formats, and create custom functions for specialized conversion requirements.