Dealing wonky dates from Excel is such a common headache—extra digits, string years, BCE entries, you name it. Let’s walk through a step-by-step approach to clean and parse these into usable date objects in R.

Step 1: Load Data & Diagnose the Mess #

First, get your data into R and take a close look at the problematic column. We’ll use readxl for Excel files, but the cleaning steps work regardless of your data source:

library(readxl)
library(lubridate)
library(stringr)
library(readr)

# Load your Excel file
df <- read_excel("your_file.xlsx")

# Inspect the messy date column (replace "messy_date_col" with your actual column name)
str(df$messy_date_col)
unique(df$messy_date_col)

This will show you exactly what anomalies you’re up against—like numeric strings with extra leading digits, BCE labels, or mixed date formats.

Step 2: Clean Common Anomalies #

Let’s build custom functions to tackle the most frequent issues one by one:

a. Fix Extra Digits (e.g., "1111983" → "1983") #

For entries with random extra leading digits, use regex to extract the last 4 valid year digits:

clean_extra_digits <- function(x) {
  if (str_detect(x, "^\\d{5,}$")) { # Check for 5+ digit numbers
    str_extract(x, "\\d{4}$") # Grab last 4 digits
  } else {
    x
  }
}

b. Handle BCE/BC Years (e.g., "-450 BCE" → "-450", "BC 300" → "-300") #

Convert BCE/BC labels to negative year values (since most date tools recognize negative years as BCE):

clean_bce <- function(x) {
  # Remove BCE/BC labels first
  x_clean <- str_remove_all(x, "\\s*(BCE|BC)")
  # Add negative sign if BCE/BC was present and not already negative
  if (str_detect(x, "(BCE|BC)") && !str_detect(x_clean, "^-")) {
    paste0("-", x_clean)
  } else {
    x_clean
  }
}

c. Combine All Cleaning Steps #

Wrap these into a single function to apply to your date column:

clean_date_string <- function(x) {
  x %>%
    as.character() %>%  # Ensure all entries are strings
    str_trim() %>%      # Strip leading/trailing whitespace
    clean_extra_digits() %>%
    clean_bce()
}

# Apply cleaning to your column
df$cleaned_date_str <- clean_date_string(df$messy_date_col)

Step 3: Parse Cleaned Strings into Dates #

Now use flexible parsing functions to turn cleaned strings into date objects. lubridate::parse_date_time is perfect for mixed formats, and it handles year-only entries gracefully:

# Define all possible date formats your data might use
date_formats <- c("Y", "Ymd", "dmy", "ymd", "mdy")

# Parse the cleaned strings
df$parsed_date <- parse_date_time(df$cleaned_date_str, orders = date_formats, truncated = 2)

The truncated = 2 argument lets it parse year-only entries by default (setting month and day to 1-1, which you can adjust if needed).

For BCE Dates (Optional) #

If you need to work with BCE years specifically (instead of full dates), convert them to numeric values:

df$year_value <- case_when(
  str_detect(df$cleaned_date_str, "^-") ~ as.numeric(df$cleaned_date_str),
  !is.na(parse_date_time(df$cleaned_date_str, "Y")) ~ as.numeric(df$cleaned_date_str),
  TRUE ~ NA_real_
)

Step 4: Validate & Fix Remaining Issues #

After parsing, check for NA values to catch any unhandled edge cases:

# View rows where parsing failed
df[is.na(df$parsed_date), ]

For these stragglers, you can add custom rules to your clean_date_string function—like handling typos, non-standard labels, or other weird formatting.

Example Workflow with Sample Data #

Let’s test this with a messy sample vector to see how it works:

messy_dates <- c("1983", "1111983", "2000-05-12", "1999/10/25", "-450 BCE", "BC 300", "2021", "abc123")

# Clean and parse
cleaned <- clean_date_string(messy_dates)
parsed <- parse_date_time(cleaned, orders = date_formats, truncated = 2)

# View results
data.frame(original = messy_dates, cleaned = cleaned, parsed_date = parsed)

This will output a table showing how each entry is cleaned and parsed—with "abc123" becoming NA (which you can investigate further if needed).

Key Takeaways #

• Inspect first: Always check unique values to understand the specific mess you’re dealing with.
• Regex is your friend: String manipulation fixes most typos and format inconsistencies.
• Flexible parsing: Use lubridate or readr functions instead of writing custom parsers for every case.
• Validate: Don’t skip checking NA values—they’ll point you to edge cases you missed.

内容的提问来源于stack exchange，提问作者Boro Dega