Clean inconsistent date formats and text categories

Turn chaotic timestamp formats and varied categorical spellings into unified standards. Use it when mismatched export values break dashboards.

You getCategorical Standardization, timestamp Fixes and SQL or Python Cleanup Snippet

Prompt

Prompt
Act as a data cleaning specialist. Standardize the inconsistent date strings, timestamps, and categorical labels provided below.
- Convert all timestamps to standard ISO 8601 (YYYY-MM-DD HH:MM:SS) format, noting any ambiguous dates (such as DD/MM vs MM/DD).
- Group messy categorical values into clean, canonical categories using snake_case or proper title case.
- Provide a clean mapping dictionary or conversion rules for automated cleaning.
Output format:
1. Categorical Standardization: Table showing Original Value, Canonical Value, Rule
2. Timestamp Fixes: Table showing Original String, Inferred Format, Standardized ISO 8601 Output, Ambiguity Flag
3. SQL or Python Cleanup Snippet: Ready-to-run transformation code

The raw values are below:

Example output

Example for: mixed user sign-up dates and regional source channels

1. Categorical Standardization
Original ValueCanonical ValueRule
paid-searchPaid SearchStandardize hyphen to space, title case
Paid Google AdPaid SearchMap channel vendor to broad parent
organicOrganic SearchMap unbranded traffic to standard channel
org-searchOrganic SearchResolve abbreviation
Direct_TrafficDirectStrip suffix, remove underscore
2. Timestamp Fixes
Original StringInferred FormatStandardized ISO 8601 OutputAmbiguity Flag
04/12/2024 14:30DD/MM/YYYY HH:MM2024-12-04 14:30:00Yes (Verify if Dec 4 or Apr 12)
2024-05-18T09:15:00ZISO UTC2024-05-18 09:15:00No
Oct 14, 2024 4:00 PMMon DD, YYYY hh:mm a2024-10-14 16:00:00No
2024.08.01YYYY.MM.DD2024-08-01 00:00:00No
3. SQL Cleanup Snippet
SELECT
  CASE 
    WHEN LOWER(channel) LIKE '%paid%' THEN 'Paid Search'
    WHEN LOWER(channel) LIKE '%org%' THEN 'Organic Search'
    WHEN LOWER(channel) LIKE '%direct%' THEN 'Direct'
    ELSE 'Other'
  END AS clean_channel,
  COALESCE(
    TRY_TO_TIMESTAMP(signup_time, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
    TRY_TO_TIMESTAMP(signup_time, 'MON DD, YYYY HH12:MI AM'),
    TRY_TO_TIMESTAMP(signup_time, 'DD/MM/YYYY HH24:MI')
  ) AS clean_signup_timestamp
FROM raw_signups;
Written by AI from this prompt. Your results will vary.

Paste your raw text lists, sample CSV columns, or category dumps into Claude, ChatGPT or Gemini. Follow up by asking for an equivalent Pandas or dbt SQL replacement snippet if you use a specific stack.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis