多源数据同步：将多时间戳数据聚合为每日单行数据

Got it, let's work through this data aggregation problem together! You've got timestamped rows where each day has scattered non-NA values across columns x, y, z, and you want to collapse them into one row per day with all the valid values. Here are solutions using common tools that should get you exactly the output you need:

Python (Pandas) #

Pandas makes this straightforward with grouping and custom aggregation. We'll extract the date from the timestamp, group by that date, and pick the non-NA value for each column (since your sample shows one valid value per column per day):

import pandas as pd

# Load your data into a DataFrame (adjust this based on your actual data source)
data = pd.DataFrame({
    'timestamp': ['1990-01-01 13:00:00', '1990-01-01 14:00:00', '1990-01-01 16:30:00',
                  '1990-01-02 12:30:00', '1990-01-02 13:30:00', '1990-01-02 14:30:00',
                  '1990-01-03 09:30:00', '1990-01-03 12:30:00', '1990-01-03 13:30:00'],
    'x': [1, None, None, None, None, 2, None, None, 5],
    'y': [None, 4, None, 2, None, None, 3, None, None],
    'z': [None, None, 3, None, 6, None, None, 4, None]
})

# Extract just the date from the timestamp
data['date'] = pd.to_datetime(data['timestamp']).dt.date

# Group by date and pull the non-NA value for each column
daily_summary = data.groupby('date').agg(lambda col: col.dropna().iloc[0])

# Clean up to match your target format
result = daily_summary[['x', 'y', 'z']].reset_index()
print(result)

This will output exactly the daily rows you're looking for.

R (with dplyr) #

Using the tidyverse, we can do similar date extraction and grouping to aggregate the valid values:

library(dplyr)
library(lubridate)

# Create your data frame (adjust for your actual input)
data <- tibble(
  timestamp = c('1990-01-01 13:00:00', '1990-01-01 14:00:00', '1990-01-01 16:30:00',
                '1990-01-02 12:30:00', '1990-01-02 13:30:00', '1990-01-02 14:30:00',
                '1990-01-03 09:30:00', '1990-01-03 12:30:00', '1990-01-03 13:30:00'),
  x = c(1, NA, NA, NA, NA, 2, NA, NA, 5),
  y = c(NA, 4, NA, 2, NA, NA, 3, NA, NA),
  z = c(NA, NA, 3, NA, 6, NA, NA, 4, NA)
)

# Process dates and aggregate
result <- data %>%
  mutate(date = as_date(timestamp)) %>%
  group_by(date) %>%
  summarize(
    x = first(na.omit(x)),
    y = first(na.omit(y)),
    z = first(na.omit(z))
  ) %>%
  ungroup()

print(result)

SQL #

If your data is stored in a database, you can use SQL's aggregation functions to ignore NULLs (which correspond to your NA values). Since each date has exactly one valid value per column, MAX() (or MIN(), SUM()) will pick that value:

SELECT
  DATE(timestamp) AS date,
  MAX(x) AS x,
  MAX(y) AS y,
  MAX(z) AS z
FROM your_table
GROUP BY DATE(timestamp)
ORDER BY date;

Key Note #

All these solutions assume that for each date and each column (x, y, z), there's exactly one non-NA/non-NULL value. If you ever have multiple valid values per day per column, you'll need to adjust the logic (e.g., take the latest value, average them, etc.)—but based on your sample data, this setup works perfectly.

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