Dates that arrive in five formats

The problem

_submission_time holds five different formats. Two of them are indistinguishable from each other, and the fix everyone reaches for first produces a clean-looking column that is wrong in a way nothing will flag.

This is the most dangerous lesson in the module, because the failure is silent.

What you will have at the end

A parsed date column, a count of the rows that cannot be resolved from the data alone, and the habit of checking a date column against something you know.

The steps

See the formats

import pandas as pd

df = pd.read_csv("household-survey-messy.csv", dtype=str, keep_default_na=False)
print(df["_submission_time"].head(10).to_string())

You will see things like 2026-03-14, 14/03/2026, 03/14/2026, 14-Mar-2026 and 2026/03/14 09:21.

Watch the naive version fail loudly

dates = pd.to_datetime(df["_submission_time"], errors="coerce")
print(dates.isna().sum())

451 of 618 unparsed. That is annoying, but it is honest — you can see the problem.

Watch the obvious fix fail silently

dates = pd.to_datetime(df["_submission_time"], format="mixed",
                       dayfirst=True, errors="coerce")
print(dates.isna().sum())

Zero failures. Every row parsed. This looks like success and it is not.

Why zero failures is not zero errors

232 rows are in the DD/MM/YYYY or MM/DD/YYYY shape. Of those:

slashed = df["_submission_time"][df["_submission_time"].str.match(r"^\d{2}/\d{2}/\d{4}$")]

day_first   = pd.to_datetime(slashed, format="%d/%m/%Y", errors="coerce")
month_first = pd.to_datetime(slashed, format="%m/%d/%Y", errors="coerce")

both = day_first.notna() & month_first.notna()
print("parse both ways:", both.sum())
print("and disagree:   ", (both & (day_first != month_first)).sum())

parse both ways: 100
and disagree:    84

84 rows get a different date depending on a flag you guessed at. 06/03/2026 is either the 6th of March or the 3rd of June. The data cannot tell you which, and dayfirst=True does not find out — it just picks.

Use something you know to check

The survey ran in March 2026. Any submission outside that window is a misparse:

def outside_march(series):
    return ((series < "2026-03-01") | (series > "2026-03-31")).sum()

df_dates = df["_submission_time"]
print("dayfirst=True: ", outside_march(pd.to_datetime(df_dates, format="mixed", dayfirst=True,  errors="coerce")))
print("dayfirst=False:", outside_march(pd.to_datetime(df_dates, format="mixed", dayfirst=False, errors="coerce")))

dayfirst=True:  117
dayfirst=False: 53

Neither is zero, because the file genuinely contains both conventions. No single flag is correct. Anyone who sets dayfirst and moves on has 53 or 117 wrong dates and no idea.

Parse what you can prove, flag the rest

The honest approach: resolve the rows where only one reading is possible, and mark the rest as unresolved rather than guessing.

import numpy as np

s = df["_submission_time"].str.strip()
is_slashed = s.str.match(r"^\d{2}/\d{2}/\d{4}$")

# Rows where the first number is over 12 can only be a day.
first = pd.to_numeric(s.where(is_slashed).str.slice(0, 2), errors="coerce")
second = pd.to_numeric(s.where(is_slashed).str.slice(3, 5), errors="coerce")

unambiguous_day_first   = is_slashed & (first > 12)
unambiguous_month_first = is_slashed & (second > 12)
ambiguous               = is_slashed & (first <= 12) & (second <= 12)

print(f"only day-first possible:   {unambiguous_day_first.sum()}")
print(f"only month-first possible: {unambiguous_month_first.sum()}")
print(f"genuinely ambiguous:       {ambiguous.sum()}")

parsed = pd.to_datetime(s.where(~is_slashed), format="mixed", errors="coerce")
parsed[unambiguous_day_first]   = pd.to_datetime(s[unambiguous_day_first],   format="%d/%m/%Y")
parsed[unambiguous_month_first] = pd.to_datetime(s[unambiguous_month_first], format="%m/%d/%Y")

df["submitted"] = parsed
df["date_ambiguous"] = ambiguous
print(f"\nunresolved: {df['date_ambiguous'].sum()} rows flagged for follow-up")

only day-first possible:   65
only month-first possible: 67
genuinely ambiguous:       100

unresolved: 100 rows flagged for follow-up

132 of the 232 slashed dates resolve themselves: a first number over 12 can only be a day, a second number over 12 can only be a month. Check the resolved rows against the fieldwork window and none fall outside March — against 53 or 117 for the two guesses above. The method is right where it commits, and honest where it cannot.

Now you have a column you can defend, and a flag naming the rows you could not resolve. That flag is the deliverable. "I do not know, and here are exactly which ones" is a far better answer than a complete column that is quietly wrong.

Where it breaks

Trusting a column because it parsed

Parsing success says the string had a date shape. It says nothing about whether the date is right. Always sanity-check against something external: the fieldwork window, the contract dates, the day the form was published.

Two-digit years

03/04/26 adds a third unknown. If you meet these, find out the convention from whoever exported the file. Do not infer it.

Timezones that shift a day

Kobo stores submission times in UTC. An interview at 9pm in Kampala (UTC+3) is stored as 18:00 UTC the same day — fine. But one at 1am local becomes 22:00 UTC the previous day, so a naive date count moves interviews between days.

df["submitted_local"] = (
    df["submitted"].dt.tz_localize("UTC").dt.tz_convert("Africa/Kampala")
)

The real fix

Export ISO dates from the server, where the format is unambiguous by construction. Everything above is repair work for a problem that should not reach you — worth doing well, and worth preventing.

Your turn

Using date_ambiguous, answer: do the ambiguous rows cluster with one enumerator, or are they spread evenly?

If they cluster, you have found which tablet or export setting produced them — and that is a question you can take to a person, which beats any amount of guessing in code.

Export a table people can use — putting it together and handing it on.