When using pd.to_datetime() in Pandas, you might encounter the error:

ValueError: Cannot mix tz-aware with tz-naive values

This happens when:

  • timezone-aware (tz-aware) and
  • timezone-naive (tz-naive) datetime values

exist in the same column. Pandas does not allow this combination for operations like comparison or merging.

Understanding the Problem

  • Timezone-naive timestamps do not have timezone information.
  • Timezone-aware timestamps include a timezone (UTC, America/New_York, etc.).

Example of mixed values causing the error:

import pandas as pd  

df = pd.DataFrame({"timestamp": ["2024-02-10 12:00:00", "2024-02-10 14:00:00+00:00"]})  
df["timestamp"] = pd.to_datetime(df["timestamp"])

which will result into:

ValueError: unconverted data remains when parsing with format "%Y-%m-%d %H:%M:%S": "+00:00", at position 1. You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.

How to Fix the Error

1. Make All Datetimes Timezone-Naive

If you don’t need timezones, remove them using .tz_localize(None):

df['timestamp'].apply(lambda x: pd.to_datetime(x).tz_localize(None))

2. Make All Datetimes Timezone-Aware

If you need timezones, localize all values explicitly:

pd.to_datetime(df['timestamp'], format='mixed', utc=True)

or

pd.to_datetime(df['timestamp'], format='ISO8601', utc=True)

Or specify a different timezone:

df["timestamp"] = pd.to_datetime(df["timestamp"]).dt.tz_localize("America/New_York")

Tips

  • Use .dt.tz_localize(None) to remove timezones and make timestamps naive.
  • Use pd.to_datetime(df["col"], utc=True) to ensure consistency in timezone-aware timestamps.
  • Convert timezones before merging or comparing datetime values.

Resources