在R语言中使用as.POSIXct处理日期时间列时出现NA值问题

Hey there! I see exactly where you're hitting a snag—this is a super common issue when working with date-time formats in R, especially when you're just getting started. Let's break this down and fix it step by step.

The Root Cause #

Your date-time strings look like this: "26-04-2020 17:45" (day-month-year hour:minute), but you're using the format string "%Y-%m-%d %H:%M" which expects year-month-day hour:minute. R can't match the pattern you provided to your actual data, so it returns NA for every value.

Step-by-Step Solutions #

1. Fix the format Parameter in as.POSIXct

You just need to adjust the format string to match your data's structure. Use "%d-%m-%Y %H:%M" instead (where %d = day, %m = month, %Y = 4-digit year):

# Using base R
file_name$start_date_time <- as.POSIXct(file_name$start_date_time, format = "%d-%m-%Y %H:%M", tz = "UTC")
file_name$end_date_time <- as.POSIXct(file_name$end_date_time, format = "%d-%m-%Y %H:%M", tz = "UTC")

2. Use lubridate (Easier for New Users)

If you want a more intuitive approach, install and use the lubridate package—it handles date parsing without needing to write explicit format strings for common patterns:

# Install and load the package first
install.packages("lubridate")
library(lubridate)

# Convert using dmy_hm (day-month-year hour-minute)
file_name <- mutate(file_name,
                    start_date_time = dmy_hm(start_date_time, tz = "UTC"),
                    end_date_time = dmy_hm(end_date_time, tz = "UTC"))

Extra Notes to Clean Up Your Workflow #

• You don't need the step where you convert col_character() to chr—read_csv already loads col_character() as character vectors in your data frame. That step is redundant and can be skipped.
• When you see NA values after conversion, always double-check that your format string exactly matches the structure of your date-time strings (including separators like - vs /, and order of day/month/year).
• POSIXct is perfect for your subtraction needs—it stores dates as numeric values (seconds since 1970-01-01), so subtracting two POSIXct columns will give you a difftime object with the time difference between them.

Verify the Fix #

After running either solution, check your data structure again with str(file_name)—you should see POSIXct, format: "2020-04-26 17:45:00" for your date-time columns, no more NAs!

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