如何基于时间戳获取两DataFrame的OBJECT列重叠内容并生成数据集？

1. 构造示例数据 #

先还原你提供的DF1和DF2（确保TIME为as.POSIXct类型）：

DF1 <- data.frame(
  TIME = as.POSIXct(c(
    "1899-12-31 00:00:00", "1899-12-31 00:00:00",
    "1899-12-31 00:00:01", "1899-12-31 00:00:01",
    "1899-12-31 00:00:02", "1899-12-31 00:00:02"
  )),
  OBJECT = c(
    "formes aimantées", "dinette thés",
    "dinette thés", "formes aimantées",
    "dinette thés", "boite"
  )
)

DF2 <- data.frame(
  TIME = as.POSIXct(c(
    "1899-12-31 00:00:00", "1899-12-31 00:00:01",
    "1899-12-31 00:00:01", "1899-12-31 00:00:02",
    "1899-12-31 00:00:02", "1899-12-31 00:00:03"
  )),
  OBJECT = c(
    "formes aimantées", "indéfini",
    "dinette thés", "boite",
    "dinette thés", "indéfini"
  )
)

2. 生成重叠数据集 #

通过内连接+去重，提取同一时间戳下两边都存在的OBJECT：

library(dplyr)

# 先去重每个数据集的TIME-OBJECT组合，再做内连接
overlap_df <- inner_join(
  distinct(DF1, TIME, OBJECT),
  distinct(DF2, TIME, OBJECT),
  by = c("TIME", "OBJECT")
)

# 查看结果
print(overlap_df)

输出结果：

TIME            OBJECT
1 1899-12-31 00:00:00 formes aimantées
2 1899-12-31 00:00:01     dinette thés
3 1899-12-31 00:00:02     dinette thés
4 1899-12-31 00:00:02             boite

3. 可视化重叠情况 #

用ggplot2生成对应图表，提供两种常见可视化方式：

方式一：统计每个时间点的重叠OBJECT数量 #

library(ggplot2)

# 按时间分组统计重叠数量
overlap_count <- overlap_df %>%
  group_by(TIME) %>%
  summarise(重叠数量 = n())

# 绘制柱状图
ggplot(overlap_count, aes(x = TIME, y = 重叠数量)) +
  geom_col(fill = "#27ae60", width = 0.0001) +
  labs(
    x = "时间戳",
    y = "重叠OBJECT数量",
    title = "DF1与DF2在各时间戳的OBJECT重叠统计"
  ) +
  theme_minimal()

方式二：展示时间戳与重叠OBJECT的对应关系（热力图） #

ggplot(overlap_df, aes(x = TIME, y = OBJECT)) +
  geom_tile(fill = "#3498db", color = "white") +
  labs(
    x = "时间戳",
    y = "OBJECT",
    title = "各时间戳下的重叠OBJECT分布"
  ) +
  theme_minimal()

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