R语言中按日期与区域合并多行及日期格式处理求助

Hey there! Let's work through your problem step by step — we'll first sort out the date format handling, then merge your rows exactly as you need.

Step 1: Fix Timestamp & Day Column Handling #

The most common issue with as.Date() on POSIXct timestamps is timezone mismatches, which can accidentally shift your day value. Here's how to do it reliably:

1. First, make sure your Timestamp is properly converted to POSIXct (if it isn't already):

   # Adjust the format string if your Timestamp uses a different pattern
   df$Timestamp <- as.POSIXct(df$Timestamp, format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
   

   Use a timezone that matches your data (e.g., "Asia/Shanghai" or "America/New_York" instead of UTC if needed).

2. Generate the day column with the same timezone to avoid date shifts:

   df$day <- as.Date(df$Timestamp, tz = "UTC")
   

   This guarantees the day value perfectly aligns with the date part of your Timestamp.

Step 2: Merge Rows by day & area #

Since each row has exactly one non-NA value for columns A/B/C, we can group by day and area, then extract the non-NA value for each column. Below are two common approaches:

Using dplyr (tidyverse-style) #

library(dplyr)

merged_df <- df %>%
  group_by(day, area) %>%
  summarize(
    Timestamp = first(Timestamp),  # Keep the earliest timestamp from each group (matches your example)
    A = first(na.omit(A)),         # Grab the only non-NA value for A
    B = first(na.omit(B)),         # Same logic for B
    C = first(na.omit(C)),         # Same logic for C
    .groups = "drop"               # Ungroup after summarizing
  )

Using data.table (faster for large datasets) #

library(data.table)
setDT(df)  # Convert data frame to data.table format

merged_df <- df[, .(
  Timestamp = first(Timestamp),
  A = first(na.omit(A)),
  B = first(na.omit(B)),
  C = first(na.omit(C))
), by = .(day, area)]

What This Does #

• group_by(day, area) clusters all rows that share the same date and area.
• first(na.omit(col)) removes NA values from the column and takes the first (and only, in your case) non-NA value.
• first(Timestamp) preserves the earliest timestamp from each group, which matches the structure you provided.

If you ever have multiple non-NA values for a column in a group, you can swap first() with sum(), mean(), or another aggregation function depending on your needs.

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