TXT文件日期格式混合致as.Date返回NA，求R中日期转换方案

Hey there! Let's tackle this mixed date format problem—dealing with 300k rows of dates in different styles can be frustrating, especially when as.Date() or POSIX functions spit out NA values. Here are two reliable methods to get all your dates converted properly:

Method 1: Use lubridate (Easiest for Mixed Formats) #

The lubridate package is built for messy date data, and its parse_date_time() function handles multiple formats seamlessly. It’s perfect for your large dataset.

First, install and load the package if you haven't already:

install.packages("lubridate")
library(lubridate)

From your example, we can see two core date formats in your X_NOMIN_DT column:

• dd-mm-yyyy (like 02-02-2011)
• mm/dd/yyyy H:M (like 12/23/2010 0:00)

Run this code to convert the column:

# Convert to datetime first (handles the time component)
df$X_NOMIN_DT_datetime <- parse_date_time(df$X_NOMIN_DT, orders = c("%d-%m-%Y", "%m/%d/%Y %H:%M"))

# If you only need a Date type (without time), convert it:
df$X_NOMIN_DT_date <- as.Date(df$X_NOMIN_DT_datetime)

parse_date_time() will automatically try each format on every value, so you won’t get NA values as long as you’ve listed all formats present in your data.

Method 2: Base R Approach (No Additional Packages) #

If you prefer sticking to base R, you can first convert most values with one format, then fill in the remaining NAs with the second format.

# First try the mm/dd/yyyy with time format
df$X_NOMIN_DT_date <- as.Date(df$X_NOMIN_DT, format = "%m/%d/%Y %H:%M")

# Find rows where the conversion failed (NA)
na_rows <- is.na(df$X_NOMIN_DT_date)

# Convert those rows with the dd-mm-yyyy format
df$X_NOMIN_DT_date[na_rows] <- as.Date(df$X_NOMIN_DT[na_rows], format = "%d-%m-%Y")

This method works well if you only have a couple of distinct formats, and it’s lightweight for large datasets.

Quick Tip for Edge Cases #

If you have ambiguous values (like 02-02-2011 could be either dd-mm or mm-dd), confirm which format those dash-separated dates follow. You can test subsets of your data with lubridate's helper functions:

# Test a subset of dash-separated dates
test_dates <- df$X_NOMIN_DT[grepl("-", df$X_NOMIN_DT)]
head(dmy(test_dates)) # Use this if dates are day-month-year
head(mdy(test_dates)) # Use this if dates are month-day-year

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