如何用R（或Excel）生成x值不匹配的数据集均值曲线？

背景与需求 #

我有两个结构相同的DataFrame（fire2022_12和fire2021_12），都包含variable、x、y三列，已经用以下代码在同一张图中画出两条曲线：

ggplot() + geom_line(data=fire2022_12, aes(x, y)) + geom_line(data=fire2021_12, aes(x, y))

我需要生成一条代表这两个DataFrame均值的曲线，以此分析火灾后时长(x)对物种栖息地适宜度(y，取值0-1)的平均影响，但遇到了问题：两个DataFrame的x值不完全重叠，部分x值仅存在于其中一个数据集，计算均值时直接采用该单值，导致绘制结果不符合预期。

数据片段 #

fire2022_12数据片段 #

timesincefire202212v2,-115.1,0.8304737597703934
timesincefire202212v2,-113.71879999999999,0.8304737597703934
timesincefire202212v2,-112.3376,0.8304737597703934
timesincefire202212v2,-110.9564,0.8304737597703934
timesincefire202212v2,-109.5752,0.8304737597703934
timesincefire202212v2,-108.19399999999999,0.8304737597703934
timesincefire202212v2,-106.8128,0.8304737597703934
timesincefire202212v2,-105.4316,0.8304737597703934
timesincefire202212v2,-104.0504,0.8304737597703934
timesincefire202212v2,-102.66919999999999,0.8304737597703934
timesincefire202212v2,-101.288,0.8304737597703934
timesincefire202212v2,-99.9068,0.8304737597703934
timesincefire202212v2,-98.5256,0.8304737597703934
timesincefire202212v2,-97.14439999999999,0.8304737597703934
timesincefire202212v2,-95.7632,0.8304737597703934
timesincefire202212v2,-94.382,0.8304737597703934
timesincefire202212v2,-93.0008,0.8304737597703934
timesincefire202212v2,-91.61959999999999,0.8304737597703934
timesincefire202212v2,-90.2384,0.8304737597703934

fire2021_12数据片段 #

timesincefire2021_12,-113.9,0.9661756336688996
timesincefire2021_12,-112.53320000000001,0.9661756336688996
timesincefire2021_12,-111.16640000000001,0.9661756336688996
timesincefire2021_12,-109.7996,0.9661756336688996
timesincefire2021_12,-108.4328,0.9661756336688996
timesincefire2021_12,-107.066,0.9661756336688996
timesincefire2021_12,-105.6992,0.9661756336688996
timesincefire2021_12,-104.3324,0.9661756336688996
timesincefire2021_12,-102.96560000000001,0.9661756336688996
timesincefire2021_12,-101.59880000000001,0.9661756336688996
timesincefire2021_12,-100.232,0.9661756336688996
timesincefire2021_12,-98.8652,0.9661756336688996
timesincefire2021_12,-97.4984,0.9661756336688996
timesincefire2021_12,-96.1316,0.9661756336688996
timesincefire2021_12,-94.76480000000001,0.9661756336688996
timesincefire2021_12,-93.398,0.9661756336688996
timesincefire2021_12,-92.0312,0.9661756336688996
timesincefire2021_12,-90.6644,0.9661756336688996

尝试过的代码及问题 #

我试过以下代码，但绘制结果出现异常（比如多条线）：

library(ggplot2)
library(tidyverse)
library(dplyr)

#merging two dataframes into one
data2 <- rbind(fire2022_12, fire2021_12)

#rounding the x-axis values to a whole number
data2$x <- round(data2$x, 0)

#obtaining the mean y-axis value for each x-axis value
grouped <- data2 %>% group_by(x) %>% summarise(y = mean(y))

#plotting the data 
ggplot(data=grouped, aes(x=x, y=y))+geom_point()

我认为问题源于仅单数据集存在的x值，希望能剔除这些值，或对齐到双方都有的x值。我也尝试过ggplot2的stat_summary()，但遇到类似问题。

补充说明 #

• 当前结果：绘制的点/线混乱，不符合均值曲线的预期
• 原始两条曲线：两条连续平滑的曲线，分别对应两个数据集
• 使用geom_line()的结果：出现多条交叉混乱的线，不是预期的单条平滑均值曲线

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