如何处理CSV文件中时间序列的NA值，统一观测数量？

Hey there! Let's get your time series aligned so they all have the same number of observations. Since you've already done preprocessing in the ts environment and saved to a CSV, we can work directly with that data without switching to zoo. Here's a step-by-step approach:

Step 1: Read in your data (as you already do) #

First, load your data with the code you provided:

ger.data <- read.table("inputData/rstar.data.ger.csv", sep = ',', na.strings = ".", header=TRUE, stringsAsFactors=FALSE)

Step 2: Convert columns back to ts objects (if needed) #

Since you originally worked with ts objects, we'll convert each column back to preserve the time structure. You'll need to know the frequency (e.g., 4 for quarterly, 12 for monthly) and start date of your original time series. Adjust these values to match your data:

# Example: Quarterly data starting in 1980 Q1
freq <- 4
start_date <- c(1980, 1)

# Convert each column to a ts object
ts_series <- lapply(ger.data, function(col) ts(col, start = start_date, frequency = freq))

Step 3: Find the common valid time range #

We need to identify the earliest start date where all series have non-NA data, and the latest end date where all series still have non-NA data. This ensures we only keep the overlapping, valid portion of all series:

# Helper function to get the first and last non-NA time points for a ts object
get_valid_time_range <- function(ts_obj) {
  non_na_positions <- which(!is.na(ts_obj))
  if (length(non_na_positions) == 0) stop("One of your series has no valid data!")
  
  first_valid_time <- time(ts_obj)[min(non_na_positions)]
  last_valid_time <- time(ts_obj)[max(non_na_positions)]
  
  return(c(start = first_valid_time, end = last_valid_time))
}

# Get ranges for all series
all_ranges <- lapply(ts_series, get_valid_time_range)

# Find the common start (latest of all individual starts) and common end (earliest of all individual ends)
common_start <- max(sapply(all_ranges, function(r) r["start"]))
common_end <- min(sapply(all_ranges, function(r) r["end"]))

Step 4: Trim all series to the common range #

Use window() to slice each ts object to the shared time interval, then combine them back into a data frame if needed:

# Trim each series to the common range
aligned_ts <- lapply(ts_series, function(series) window(series, start = common_start, end = common_end))

# Convert back to a data frame (optional, if you need tabular format)
aligned_data <- do.call(cbind, aligned_ts)
colnames(aligned_data) <- colnames(ger.data)

Alternative: If your CSV includes a date column #

If you saved a separate date column in your CSV (instead of relying on ts time attributes), you can use that to filter directly:

# Convert the date column to a proper date format (adjust based on your date structure)
ger.data$date <- as.Date(ger.data$date) # For daily dates
# Or for quarterly: ger.data$date <- zoo::as.yearqtr(ger.data$date)

# Get valid date ranges for each variable
get_var_date_range <- function(var_col, date_col) {
  valid_dates <- date_col[!is.na(var_col)]
  return(c(min(valid_dates), max(valid_dates)))
}

var_ranges <- lapply(ger.data[, !colnames(ger.data) %in% "date"], function(col) get_var_date_range(col, ger.data$date))

# Find common date range
common_start_date <- max(sapply(var_ranges, function(r) r[1]))
common_end_date <- min(sapply(var_ranges, function(r) r[2]))

# Filter the data frame to this range
aligned_data <- ger.data[ger.data$date >= common_start_date & ger.data$date <= common_end_date, ]

This will give you a dataset where all variables have the exact same number of observations, with no missing data at the start/end (though you might still have NAs in the middle if they existed in your original data).

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