Data and reporting

Convert a pandas Column From String to Datetime

Parse a known format with pandas.to_datetime, count invalid dates separately from missing values, and handle UTC and local timestamps.

Convert a pandas column with pd.to_datetime(column, format="%Y-%m-%d") when the input looks like 2026-03-12. By default a bad date raises an error. Use errors="coerce" to turn unparseable dates into NaT, then compare the parsed column with the input to separate failed conversions from values that were already missing.

Convert known date strings and count failures

import pandas as pd

raw = pd.Series(["2026-03-12", "2026-02-30", None, "2026-03-25"])
parsed = pd.to_datetime(raw, format="%Y-%m-%d", errors="coerce")

missing = raw.isna()
invalid = raw.notna() & parsed.isna()
assert int(missing.sum()) == 1
assert int(invalid.sum()) == 1
assert parsed.iloc[0].day == 12

NaT is a missing marker for datetime-like values. It can result from genuinely missing input or a failed parse. The two masks retain the difference in this sample. If blank strings count as missing in the source contract, normalize them before counting failures while keeping the raw values elsewhere.

A date string such as 2026-03-12 has no timezone. Its parsed value is a calendar reading represented in a datetime column. If the business question is about a date alone, avoid converting it to UTC and displaying it in a local zone as though it had started as an event. That could change the displayed date.

Decide when to use utc=True

utc=True converts timezone-aware inputs to UTC and localizes naive inputs as UTC. That second behavior is appropriate only when your source defines naive values as UTC. If the source reports naive facility times, first establish the facility’s named timezone and use an explicit localization policy for ambiguous or nonexistent clock readings.

A mixed-offset column can still represent valid instants. Normalizing it to UTC gives a consistent timeline, while keeping the original strings preserves which offset the source reported. For local daily summaries, convert the normalized instants to the reporting zone before grouping by calendar date. Local dates and UTC dates can classify the same event in different days.

Avoid guessing when the export format is ambiguous

If both 01/03/2026 and 10/03/2026 are valid in more than one regional format, neither string tells you which convention the source intended. Use confirmed export metadata or an explicit format such as %d/%m/%Y. A successful parse can still be logically wrong. Check known dates whose day exceeds 12 and verify a record with a known date against the original system.

When producers use different formats, group the rows by a known source field and parse each group according to that producer’s format. Keep rejected rows with their original text. If an import that normally rejects three dates suddenly rejects hundreds, check whether the source export changed before modifying your parser.

Filter by calendar date or timestamp

For a monthly report, choose a start timestamp and the start of the next month. Include the first and exclude the second. Make sure those boundaries and the column use compatible timezone semantics. Validate how missing dates contribute to coverage before filtering. Rows with missing timestamps often drop out of comparisons. Report them separately so the filtered count can be reconciled.

If the output is a presentation table, format a display column after sorting or grouping by parsed values. Formatting back to strings early can make chronological operations depend on text conventions. Test an invalid date, an ambiguous day and month, an event at midnight and a truly missing entry. These tests catch the errors that persist even when the library returns a valid datetime dtype.

Sources and further reading

Related reading

Browse Data and reporting · Explore project tutorials

Search PythonProjects.org