Date and Time Cleaning

import pandas as pd

df = pd.DataFrame({
    'date_str': [
        '2024-01-15',      # ISO standard (YYYY-MM-DD)
        '01/25/2024',      # US format (MM/DD/YYYY)
        'Jan 5, 2024',     # Text format
        '20240130'         # YYYYMMDD continuous
    ]
})

print("=== RAW DTYPES ===")
print(df.dtypes)

# Pandas is remarkably good at guessing the format automatically
df['clean_date'] = pd.to_datetime(df['date_str'])

print("\n=== CONVERTED DATETIME ===")
print(df)
print("\nNew Dtypes:")
print(df.dtypes) # Note it is now datetime64[ns]

ambiguous = pd.Series(['01/05/2024', '15/05/2024']) # 15 must be day, so it's May 15th

# If data is European (Day first), TELL pandas explicitly:
euro_dates = pd.to_datetime(ambiguous, dayfirst=True)
print("\n=== DAY FIRST PARSING ===")
print(euro_dates)

# Explicitly defining the format (FASTEST and SAFEST method)
# %Y = 4 digit year, %m = 2 digit month, %d = 2 digit day
df['exact_date'] = pd.to_datetime(df['date_str'], format='%Y-%m-%d', errors='ignore')

bad_dates = pd.DataFrame({'dob': ['1990-05-12', '2020-02-30', 'Unknown', '1985-11-22']})

# default: errors='raise' (will crash the script)
# errors='coerce': forces bad data to become NaT (Not a Time)

bad_dates['clean_dob'] = pd.to_datetime(bad_dates['dob'], errors='coerce')

print("\n=== HANDLING BAD DATES ===")
print(bad_dates)
# 2020-02-30 (Feb 30th doesn't exist) becomes NaT
# 'Unknown' becomes NaT

df = pd.DataFrame({'timestamp': pd.to_datetime(['2024-01-15 14:30:00', '2024-07-22 09:15:00'])})

# Extract components using .dt
df['year'] = df['timestamp'].dt.year
df['month'] = df['timestamp'].dt.month
df['day_name'] = df['timestamp'].dt.day_name()
df['hour'] = df['timestamp'].dt.hour
df['is_weekend'] = df['timestamp'].dt.dayofweek >= 5 # 5 is Saturday, 6 is Sunday

print("\n=== EXTRACTED COMPONENTS ===")
print(df)

events = pd.DataFrame({
    'event': ['A', 'B', 'C'],
    'start': ['2024-01-01', '2024-05-10', '2024-10-01'],
    'end':   ['2024-01-05', '2024-05-01', '2024-10-15'] # Event B ends before it starts!
})

# 1. Convert to datetime
events['start_dt'] = pd.to_datetime(events['start'])
events['end_dt'] = pd.to_datetime(events['end'])

# 2. Calculate duration
events['duration_days'] = (events['end_dt'] - events['start_dt']).dt.days

# 3. Data Validation: Find logically impossible rows
invalid_events = events[events[&#039;duration_days'] < 0]

print("\n=== EVENT VALIDATION ===")
print("Invalid events found:")
print(invalid_events)

# Clean it by forcing invalid durations to NaN
events.loc[events[&#039;duration_days'] < 0, 'end_dt'] = pd.NaT