transformations: CSV timestamps are sorted as strings, so events in a trace are in the wrong order #1

Open
opened 2026-10-06 13:43:55 +00:00 by ffrauenberger · 2 comments
Owner

Severity: critical (every CSV log with non-ISO dates silently gets wrong traces, including the bundled example)

Where

  • app/io_operations/import_operations.py:27
  • app/transformations/dataframe_transformations.py:62-69

Problem

When a CSV event log is imported, the timestamp column is never converted to datetimes. ImportOperations.read_csv calls pd.read_csv(...) without parse_dates, so the column stays a string (object) column unless it is purely numeric. DataframeTransformations.dataframe_to_cases_list then sorts by it:

sorted_dataframe = self.dataframe.sort_values(by=[time_label])

The timestamps are therefore sorted lexicographically, and every miner (Alpha, Heuristic, Inductive, IMf, Fuzzy, Genetic) receives traces in the wrong order. The user gets no warning. The XES import path is not affected, because it converts timestamps in xes_to_csv_converter.py:148.

Two typical failure patterns:

  • Dates without zero padding: "11.3.10" sorts before "9.3.10".
  • Times without zero padding: "9.3.10 10:15" sorts before "9.3.10 8:05".

In addition, sort_values uses quicksort by default, which is not stable. Events with the same timestamp (common with day-level dates) lose their original file order, and the result can change between runs or pandas versions.

Steps to reproduce

  1. Open the application
  2. Import tests/testcsv/CallcenterExample.csv
  3. Select Start Date as the timestamp column
  4. Mine a model with any algorithm
  5. Inspect the order of events within a case

Expected behavior

Events within each case are ordered chronologically. Events with identical timestamps keep their order from the file.

Actual behavior

Events are ordered as strings. In Case 1 the app gives ['Handle Case', 'Call Outbound', 'Inbound Call']; the chronological order is ['Inbound Call', 'Handle Case', 'Call Outbound']. The mined model is wrong.

Corrections from the review

  • In the fixture, the wrong order comes from unpadded days ("11.3.10" sorts before "9.3.10"). Unpadded hours also break the order.
  • On CallcenterExample.csv, 341 of 3,885 cases get the wrong event order, and the log grows from 354 to 416 variants.
  • Ties are reordered for string and numeric timestamps alike.
  • ISO-8601 timestamps sort correctly. They are a workaround until this is fixed.
  • Missing timestamps are silently moved to the end of the trace (na_position="last").

Fix

  1. Parse in the model layer, in DataframeTransformations.dataframe_to_cases_list or a helper it calls, so all six miners are fixed at once.
    • Leave numeric and datetime columns (XES) as they are.
    • Parse object columns with an explicit format, or with dayfirst chosen by the user.
    • Do not rely on pandas inference or format="mixed". Without dayfirst, pd.to_datetime silently misreads 2,901 of 7,548 fixture dates. It never raises and never gives NaT, so a count of unparseable rows would say 0.
  2. Detect ambiguity instead of guessing. Try day-first and month-first. If both parse every value but give different results, ask the user in the column-selection controller and view (app/ui/column_selection_ui/), not in the model. If only one parses everything, use it.
  3. Use errors="coerce" only after the format is fixed. Count NaT and missing values, show the count, and stop or drop those rows explicitly.
  4. Sort stably: sort_values(by=[time_label], kind="stable").
  5. Do not reuse CsvToXesConverter._parse_timestamps: it has the same day-first bug (#27).

Check

New tests/transformations/dataframe_transformations_test.py (the existing file is never run, see #56):

  • day-first unpadded dates, unpadded hours, US dates, ISO dates, ties kept in file order
  • CallcenterExample.csv Case 1 is ['Inbound Call', 'Handle Case', 'Call Outbound']

from issues/BUG-001-csv-timestamps-sorted-as-strings.md (code analysis of commit 6bcc646, 2026-10-03), corrected by the review in verdicts-claude/BUG-001-csv-timestamps-sorted-as-strings.md

**Severity:** critical (every CSV log with non-ISO dates silently gets wrong traces, including the bundled example) ## Where - `app/io_operations/import_operations.py:27` - `app/transformations/dataframe_transformations.py:62-69` ## Problem When a CSV event log is imported, the timestamp column is never converted to datetimes. `ImportOperations.read_csv` calls `pd.read_csv(...)` without `parse_dates`, so the column stays a string (`object`) column unless it is purely numeric. `DataframeTransformations.dataframe_to_cases_list` then sorts by it: ```python sorted_dataframe = self.dataframe.sort_values(by=[time_label]) ``` The timestamps are therefore sorted lexicographically, and every miner (Alpha, Heuristic, Inductive, IMf, Fuzzy, Genetic) receives traces in the wrong order. The user gets no warning. The XES import path is not affected, because it converts timestamps in `xes_to_csv_converter.py:148`. Two typical failure patterns: - Dates without zero padding: `"11.3.10"` sorts before `"9.3.10"`. - Times without zero padding: `"9.3.10 10:15"` sorts before `"9.3.10 8:05"`. In addition, `sort_values` uses `quicksort` by default, which is not stable. Events with the same timestamp (common with day-level dates) lose their original file order, and the result can change between runs or pandas versions. ### Steps to reproduce 1. Open the application 2. Import `tests/testcsv/CallcenterExample.csv` 3. Select *Start Date* as the timestamp column 4. Mine a model with any algorithm 5. Inspect the order of events within a case ### Expected behavior Events within each case are ordered chronologically. Events with identical timestamps keep their order from the file. ### Actual behavior Events are ordered as strings. In Case 1 the app gives `['Handle Case', 'Call Outbound', 'Inbound Call']`; the chronological order is `['Inbound Call', 'Handle Case', 'Call Outbound']`. The mined model is wrong. ### Corrections from the review - In the fixture, the wrong order comes from unpadded **days** (`"11.3.10"` sorts before `"9.3.10"`). Unpadded hours also break the order. - On `CallcenterExample.csv`, 341 of 3,885 cases get the wrong event order, and the log grows from 354 to 416 variants. - Ties are reordered for string and numeric timestamps alike. - ISO-8601 timestamps sort correctly. They are a workaround until this is fixed. - Missing timestamps are silently moved to the end of the trace (`na_position="last"`). ## Fix 1. Parse in the model layer, in `DataframeTransformations.dataframe_to_cases_list` or a helper it calls, so all six miners are fixed at once. - Leave numeric and datetime columns (XES) as they are. - Parse `object` columns with an explicit `format`, or with `dayfirst` chosen by the user. - Do not rely on pandas inference or `format="mixed"`. Without `dayfirst`, `pd.to_datetime` silently misreads 2,901 of 7,548 fixture dates. It never raises and never gives NaT, so a count of unparseable rows would say 0. 2. Detect ambiguity instead of guessing. Try day-first and month-first. If both parse every value but give different results, ask the user in the column-selection controller and view (`app/ui/column_selection_ui/`), not in the model. If only one parses everything, use it. 3. Use `errors="coerce"` only after the format is fixed. Count NaT and missing values, show the count, and stop or drop those rows explicitly. 4. Sort stably: `sort_values(by=[time_label], kind="stable")`. 5. Do not reuse `CsvToXesConverter._parse_timestamps`: it has the same day-first bug (#27). ## Check New `tests/transformations/dataframe_transformations_test.py` (the existing file is never run, see #56): - day-first unpadded dates, unpadded hours, US dates, ISO dates, ties kept in file order - `CallcenterExample.csv` Case 1 is `['Inbound Call', 'Handle Case', 'Call Outbound']` --- from `issues/BUG-001-csv-timestamps-sorted-as-strings.md` (code analysis of commit 6bcc646, 2026-10-03), corrected by the review in `verdicts-claude/BUG-001-csv-timestamps-sorted-as-strings.md`
Author
Owner

Reviewed by Codex (GPT-6) against origin/main 6bcc646eb464.

Review: partially correct.

The main bug and fixture numbers are confirmed: 341/3,885 cases differ from stable chronological ordering; variants change from 354 to 416; 2,901/7,548 timestamps are misread by the converter's mixed parser. However, “every CSV log with non-ISO dates” is too broad: string sorting only changes a trace when its lexical and chronological order differ. Likewise, ISO-8601 is a safe lexical workaround only with a consistent representation, precision and timezone/offset. Mixed ISO offsets need datetime parsing too. Keep the stable-sort/parsing fix, but qualify the severity and workaround.

Code references: app/transformations/dataframe_transformations.py:62, app/io_operations/import_operations.py:27.

Reviewed by **Codex (GPT-6)** against origin/main [`6bcc646eb464`](https://code.swisdata.eu/ffrauenberger/ProcessIntel/commit/6bcc646eb464e0dd897228b5e49867f65d7255c4). **Review: partially correct.** The main bug and fixture numbers are confirmed: 341/3,885 cases differ from stable chronological ordering; variants change from 354 to 416; 2,901/7,548 timestamps are misread by the converter's mixed parser. However, “every CSV log with non-ISO dates” is too broad: string sorting only changes a trace when its lexical and chronological order differ. Likewise, ISO-8601 is a safe lexical workaround only with a consistent representation, precision and timezone/offset. Mixed ISO offsets need datetime parsing too. Keep the stable-sort/parsing fix, but qualify the severity and workaround. Code references: [app/transformations/dataframe_transformations.py:62](https://code.swisdata.eu/ffrauenberger/ProcessIntel/src/commit/6bcc646eb464e0dd897228b5e49867f65d7255c4/app/transformations/dataframe_transformations.py#L62), [app/io_operations/import_operations.py:27](https://code.swisdata.eu/ffrauenberger/ProcessIntel/src/commit/6bcc646eb464e0dd897228b5e49867f65d7255c4/app/io_operations/import_operations.py#L27).
Author
Owner

🤖 work-issue

Fixed on branch fix/csv-timestamp-parsing (uncommitted in the working tree).

Change

  • app/transformations/timestamp_parsing.py (new): parse_timestamps. Numeric and datetime columns (XES) stay as they are. Text columns are parsed with the user's strftime format (or ISO8601). With no format, ISO 8601 and common day-first, month-first and year-first formats (separators ., /, -; with or without time) are tried. A format is chosen only if it is unambiguous. If several formats fit with different results, or none fits, or a value is missing, it raises TimestampParseException.
  • app/transformations/dataframe_transformations.py:69: parses before sorting and sorts with kind="stable". New optional timestamp_format / timestampFormat argument.
  • app/exceptions/io_exceptions.py: TimestampParseException.
  • app/ui/column_selection_ui/standard_column_selection_view.py: new "Timestamp format (optional)" input. column_selection_controller.py on_mine_click stores it as selected_timestamp_format.
  • app/ui/base_algorithm_ui/base_algorithm_controller.py: passes the format on. On TimestampParseException the conversion stops, no model is created, and the user returns to the column selection page with the error message and their selections restored.
  • tests/mining_algorithms/genetic_mining_test.py: genetic_test.csv uses 09-3-2004:15.01, which is not a known format, so the test now passes %d-%m-%Y:%H.%M.

Verification

  • New tests/transformations/dataframe_transformations_test.py: unpadded days and hours, US dates, ISO with mixed offsets, ties in file order, numeric and datetime columns, ambiguous dates, user format, non-matching values, missing values, and CallcenterExample.csv Case 1 = ['Inbound Call', 'Handle Case', 'Call Outbound'].
  • python -m unittest discover -s tests -p '*_test.py': 266 tests OK. black applied.
  • Not verified: the Streamlit error flow (redirect back to column selection with restored widgets) was not run in a browser.
🤖 **work-issue** Fixed on branch `fix/csv-timestamp-parsing` (uncommitted in the working tree). **Change** - `app/transformations/timestamp_parsing.py` (new): `parse_timestamps`. Numeric and datetime columns (XES) stay as they are. Text columns are parsed with the user's `strftime` format (or `ISO8601`). With no format, ISO 8601 and common day-first, month-first and year-first formats (separators `.`, `/`, `-`; with or without time) are tried. A format is chosen only if it is unambiguous. If several formats fit with different results, or none fits, or a value is missing, it raises `TimestampParseException`. - `app/transformations/dataframe_transformations.py:69`: parses before sorting and sorts with `kind="stable"`. New optional `timestamp_format` / `timestampFormat` argument. - `app/exceptions/io_exceptions.py`: `TimestampParseException`. - `app/ui/column_selection_ui/standard_column_selection_view.py`: new "Timestamp format (optional)" input. `column_selection_controller.py` `on_mine_click` stores it as `selected_timestamp_format`. - `app/ui/base_algorithm_ui/base_algorithm_controller.py`: passes the format on. On `TimestampParseException` the conversion stops, no model is created, and the user returns to the column selection page with the error message and their selections restored. - `tests/mining_algorithms/genetic_mining_test.py`: `genetic_test.csv` uses `09-3-2004:15.01`, which is not a known format, so the test now passes `%d-%m-%Y:%H.%M`. **Verification** - New `tests/transformations/dataframe_transformations_test.py`: unpadded days and hours, US dates, ISO with mixed offsets, ties in file order, numeric and datetime columns, ambiguous dates, user format, non-matching values, missing values, and `CallcenterExample.csv` Case 1 = `['Inbound Call', 'Handle Case', 'Call Outbound']`. - `python -m unittest discover -s tests -p '*_test.py'`: 266 tests OK. `black` applied. - Not verified: the Streamlit error flow (redirect back to column selection with restored widgets) was not run in a browser.
Sign in to join this conversation.
No milestone
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
ffrauenberger/ProcessIntel#1
No description provided.