在R中使用dplyr转换日期为月份及解决as.Date生成NA的问题

Hey there! Let's work through this together—since you're new to R, we'll start by fixing those frustrating NA values when converting your dates, then move on to using dplyr to extract the months you need.

You used as.Date(sample1$Date, "%d-%m-%Y") but got lots of NAs, which means R can't parse some of your original date strings using the %d-%m-%Y (day-month-year) format you specified. Here's how to troubleshoot and fix this:

• First, check what those problematic dates look like with this code:

  # Pull out the original dates that turned into NA
  sample1$Date[is.na(list1)]
  

  You might find some dates have a different format—like mm/dd/yyyy, yyyy-mm-dd, or even ones with spelled-out months (e.g., "24-Jun-2015").

• If there are multiple formats in your data, the lubridate package is a lifesaver. Its parse_date_time() function can auto-detect common formats. Install it first if you haven't:

  install.packages("lubridate")
  library(lubridate)
  
  # List all possible formats your dates might use
  list1 <- parse_date_time(sample1$Date, orders = c("d-m-Y", "m/d/Y", "Y-m-d", "d-b-Y"))
  

  After this, check if list1 has fewer (or no) NAs with sum(is.na(list1)).

Once you have all dates successfully converted to a Date/POSIXct type, we can use dplyr to extract months in a few different ways, depending on what you need:

Option 1: Numeric Month (1-12) #

If you want a number representing the month (e.g., 6 for June), you can use either base R's format() or lubridate's month() function:

library(dplyr)

# Using base R
sample1 <- sample1 %>%
  mutate(
    date_formatted = parse_date_time(Date, orders = c("d-m-Y", "m/d/Y")), # Replace with your working date conversion
    month_number = as.integer(format(date_formatted, "%m"))
  )

# Or using lubridate (cleaner!)
sample1 <- sample1 %>%
  mutate(
    date_formatted = parse_date_time(Date, orders = c("d-m-Y", "m/d/Y")),
    month_number = month(date_formatted)
  )

Option 2: Month Name (Full or Abbreviated) #

If you want the actual month name (like "June" or "Jun"):

# Using base R for full names (e.g., "June")
sample1 <- sample1 %>%
  mutate(
    date_formatted = parse_date_time(Date, orders = c("d-m-Y", "m/d/Y")),
    month_full = format(date_formatted, "%B"),
    month_abbr = format(date_formatted, "%b") # Abbreviated, e.g., "Jun"
  )

# Using lubridate for labeled months (works with your locale too!)
sample1 <- sample1 %>%
  mutate(
    date_formatted = parse_date_time(Date, orders = c("d-m-Y", "m/d/Y")),
    month_abbr = month(date_formatted, label = TRUE), # Returns "Jun"
    month_full = month(date_formatted, label = TRUE, abbr = FALSE) # Returns "June"
  )

Option 3: Year-Month Combination (e.g., 2015-06) #

If you need the year and month together (great for grouping):

sample1 <- sample1 %>%
  mutate(
    date_formatted = parse_date_time(Date, orders = c("d-m-Y", "m/d/Y")),
    year_month = format(date_formatted, "%Y-%m")
  )

Make sure you resolve all NAs in your date conversion first—any NA dates will stay NA when you extract months. Taking a minute to check your raw date formats will save you headaches later!

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