如何将moon_data数据行关联至对应night_of并计算夜晚平均光照度

我有两个DataFrame：

1. moon_data 包含逐小时的月球高度和光照度：

date     altitude     illum
9   2020-08-31 05:30:00  -0.29518673 0.9676100
10  2020-08-31 06:30:00  -0.26451042 0.9690620
11  2020-08-31 07:30:00  -0.19938593 0.9704781
12  2020-08-31 08:30:00  -0.10514524 0.9718584
13  2020-08-31 09:30:00   0.01137494 0.9732028

2. night_info 包含各夜晚的标识、开始和结束时间：

night_of         night_start           night_end
1   2020-08-30 2020-08-30 19:43:00 2020-08-31 06:25:06
2   2020-08-31 2020-08-31 19:41:03 2020-09-01 06:26:24
3   2020-09-01 2020-09-01 19:39:06 2020-09-02 06:27:43

需求：将moon_data中的每行数据关联到对应的night_of（判断date是否落在某个夜晚的night_start和night_end区间内），并计算每个夜晚的平均光照度。

R语言实现 #

1. 统一时间格式 #

先把所有时间列转换成POSIXct类型，确保时间计算准确：

moon_data$date <- as.POSIXct(moon_data$date)
night_info$night_start <- as.POSIXct(night_info$night_start)
night_info$night_end <- as.POSIXct(night_info$night_end)

2. 匹配时间区间关联night_of #

用fuzzyjoin包的区间匹配功能，把moon_data的每条记录对应到所属夜晚：

library(fuzzyjoin)

moon_with_night <- fuzzy_left_join(
  moon_data,
  night_info,
  by = c("date" = "night_start", "date" = "night_end"),
  match_fun = list(`>=`, `<`)  # 匹配date >= night_start且date < night_end
)

3. 计算夜晚平均光照度 #

用dplyr分组求均值，过滤掉不在夜晚区间的无效数据：

library(dplyr)

night_avg_illum <- moon_with_night %>%
  filter(!is.na(night_of)) %>%
  group_by(night_of) %>%
  summarise(avg_illum = mean(illum, na.rm = TRUE))

Python语言实现 #

1. 统一时间格式 #

将时间列转换为pandas的datetime类型：

import pandas as pd

moon_data['date'] = pd.to_datetime(moon_data['date'])
night_info['night_start'] = pd.to_datetime(night_info['night_start'])
night_info['night_end'] = pd.to_datetime(night_info['night_end'])

2. 匹配时间区间关联night_of #

创建区间索引，遍历每条date找到对应的night_of：

# 生成夜晚时间区间
night_info['interval'] = pd.IntervalIndex.from_arrays(
  night_info['night_start'],
  night_info['night_end'],
  closed='left'  # 左闭右开规则，匹配date >= night_start且date < night_end
)

# 为每条moon数据匹配对应的night_of
def match_night(date):
    mask = night_info['interval'].contains(date)
    if mask.any():
        return night_info.loc[mask, 'night_of'].values[0]
    return None

moon_data['night_of'] = moon_data['date'].apply(match_night)

3. 计算夜晚平均光照度 #

分组聚合求均值，过滤掉无匹配夜晚的数据：

night_avg_illum = moon_data.dropna(subset=['night_of']).groupby('night_of')['illum'].mean().reset_index()

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