如何在R语言中利用给定的多个DataFrame创建矩阵？

Alright, let's walk through how to build your desired matrix from those DataFrames in R. Since your fourth DataFrame isn't fully detailed, I'll make practical assumptions about common use cases, but you can easily tweak this to fit your actual data structure.

First off, R needs date-time values to be in a structured format (not just text) to work with them properly. Let's convert all date columns to POSIXct (R's standard date-time type):

# Convert date columns to POSIXct (adjust the format if your dates differ)
DF_1$Date <- as.POSIXct(DF_1$Date, format = "%d/%m/%Y %H:%M")
DF_2$Date1 <- as.POSIXct(DF_2$Date1, format = "%d/%m/%Y %H:%M")
DF_2$Date2 <- as.POSIXct(DF_2$Date2, format = "%d/%m/%Y %H:%M")
DF_3$Date1 <- as.POSIXct(DF_3$Date1, format = "%d/%m/%Y %H:%M")
DF_3$Date2 <- as.POSIXct(DF_3$Date2, format = "%d/%m/%Y %H:%M")

Since DF_2 and DF_3 have overlapping data, let's merge them and remove duplicates (adjust the deduplication logic if you need to keep specific rows, like the latest entry):

# Combine DF_2 and DF_3, then drop exact duplicate rows
combined_time_data <- rbind(DF_2, DF_3)
combined_time_data <- combined_time_data[!duplicated(combined_time_data), ]

Next, merge this with DF_1 to link each ID's reference date from DF_1 with their time ranges from DF_2/3:

# Merge with DF_1 to keep all IDs from your base data
full_combined_data <- merge(DF_1, combined_time_data, by = "ID", all.x = TRUE)

Now let's cover two typical matrix use cases—pick the one that matches your goal, or adapt it:

Scenario 1: Matrix with IDs as Rows, Time Metrics as Columns #

If you want a matrix where each row is an ID, and columns are calculated time features (like reference date, earliest Date1, latest Date2, average time between Date1/Date2):

# Use dplyr to calculate features per ID (install it first if you haven't: install.packages("dplyr"))
library(dplyr)

# Calculate time-based features for each ID
id_time_features <- full_combined_data %>%
  group_by(ID) %>%
  summarize(
    reference_date = first(Date),          # The date from DF_1
    earliest_date1 = min(Date1, na.rm = TRUE),
    latest_date2 = max(Date2, na.rm = TRUE),
    avg_hours_between = mean(difftime(Date2, Date1, units = "hours"), na.rm = TRUE)
  )

# Convert to a matrix (set row names to IDs, drop the ID column from values)
id_feature_matrix <- as.matrix(id_time_features[, -1])
rownames(id_feature_matrix) <- id_time_features$ID

Scenario 2: Matrix with Time Points as Columns, IDs as Rows #

If you want a presence/absence matrix (e.g., 1 if an ID has a record at that time point, 0 otherwise):

# Extract all unique time points from all date columns
all_unique_times <- unique(c(full_combined_data$Date, full_combined_data$Date1, full_combined_data$Date2))
all_unique_times <- all_unique_times[!is.na(all_unique_times)]  # Remove missing values
all_unique_times <- sort(all_unique_times)  # Sort chronologically

# Create empty matrix with IDs as rows and time points as columns
time_presence_matrix <- matrix(0, 
                               nrow = length(unique(full_combined_data$ID)),
                               ncol = length(all_unique_times))
rownames(time_presence_matrix) <- unique(full_combined_data$ID)
colnames(time_presence_matrix) <- as.character(all_unique_times)

# Fill the matrix: mark 1 where an ID has a record at that time
for (current_id in rownames(time_presence_matrix)) {
  # Get all time points associated with this ID
  id_associated_times <- c(full_combined_data$Date[full_combined_data$ID == current_id],
                           full_combined_data$Date1[full_combined_data$ID == current_id],
                           full_combined_data$Date2[full_combined_data$ID == current_id])
  id_associated_times <- id_associated_times[!is.na(id_associated_times)]
  
  # Mark those time points in the matrix
  time_presence_matrix[current_id, as.character(id_associated_times)] <- 1
}

Since your fourth DataFrame isn't fully detailed, here's how to integrate it:

• If it has ID + additional metrics/time columns: Repeat the merging step above, add its features to the id_time_features summary (for Scenario 1) or include its time points in all_unique_times (for Scenario 2).
• If it's a cross-ID relationship table (e.g., interactions between IDs): Build a square matrix where rows and columns are IDs, and values are the metrics from this fourth DataFrame.

• Always check your date formats with str(your_dataframe) to make sure they're POSIXct (not character strings).
• Handle missing values intentionally: Use na.rm = TRUE carefully, or impute missing dates if needed (e.g., fill with the reference date from DF_1).
• Adjust feature calculations to match your actual goal—you might want counts of events instead of min/max dates, for example.

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