代码之家 › 专栏 › 技术社区 › Joe

按年份平均df列/行

Joe · 技术社区 · 3 年前

我有下面的df,我希望对每年的值取平均值,使我在df中的每一年都有一个值(这里1个值表示整体,1个值代表东部),这将使我在这个例子中有3列3行的df。我试着用tapply做这个,但还没有快乐

df 
    date       overall  eastern
1   1997-12-15  17.548  18.751
2   1998-01-15  16.189  17.155
3   1998-02-15  15.667  16.504
4   1998-03-15  15.509  16.208
5   1998-04-15  16.709  17.702
6   1998-05-15  18.822  19.660
7   1998-06-15  22.722  23.512
8   1998-07-15  25.372  25.912
9   1998-08-15  26.597  27.226
10  1998-09-15  25.256  26.151
11  1998-10-15  22.857  24.440
12  1998-11-15  20.242  21.867
13  1998-12-15  17.179  18.797
14  1999-01-15  16.003  17.206
15  1999-02-15  15.140  16.345
16  1999-03-15  15.522  16.689
17  1999-04-15  16.537  17.521
18  1999-05-15  19.658  20.740
19  1999-06-15  23.245  24.161
20  1999-07-15  25.313  26.053
21  1999-08-15  26.753  27.112
22  1999-09-15  26.040  26.597
23  1999-10-15  23.843  24.940
24  1999-11-15  20.940  22.375
25  1999-12-15  17.842  19.439

1 回复 | 直到 3 年前

akrun 3 年前

我们将“日期”转换为 Date 类(具有 lubridate::ymd 或 as.Date 从…起 base R ),提取 year 作为分组变量和 summarise across 要获取的列 mean 价值

library(dplyr)
library(lubridate)
df1 %>%
    group_by(year = year(ymd(date))) %>%
    summarise(across(overall:eastern, mean, na.rm = TRUE))

输出

# A tibble: 3 Ã 3
   year overall eastern
  <dbl>   <dbl>   <dbl>
1  1997    17.5    18.8
2  1998    20.3    21.3
3  1999    20.6    21.6

如果我们还需要按季节划分,请使用创建一个键值数据集 month 和 seasons 值,加入并执行一个组 意思是

keydat <- tibble(seasons = rep(c("Winter", "Spring", "Summer", "Fall"),
    each = 3), month = c("Dec", month.abb[-length(month.abb)]))
df1 %>%
   mutate(date = as.Date(date), month = format(date, '%b'),
   year = format(date, '%Y')) %>% 
  left_join(keydat) %>% 
  group_by(year, seasons) %>% 
  summarise(across(c(overall, eastern), mean, na.rm = TRUE), 
    .groups = 'drop')

输出

# A tibble: 9 Ã 4
  year  seasons overall eastern
  <chr> <chr>     <dbl>   <dbl>
1 1997  Winter     17.5    18.8
2 1998  Fall       22.8    24.2
3 1998  Spring     17.0    17.9
4 1998  Summer     24.9    25.6
5 1998  Winter     16.3    17.5
6 1999  Fall       23.6    24.6
7 1999  Spring     17.2    18.3
8 1999  Summer     25.1    25.8
9 1999  Winter     16.3    17.7

或者在 基本R

aggregate(.~ year, transform(df1, year = format(as.Date(date), 
   '%Y'))[-1], FUN = mean)
  year  overall  eastern
1 1997 17.54800 18.75100
2 1998 20.26008 21.26117
3 1999 20.56967 21.59817

数据

df1 <- structure(list(date = c("1997-12-15", "1998-01-15", "1998-02-15", 
"1998-03-15", "1998-04-15", "1998-05-15", "1998-06-15", "1998-07-15", 
"1998-08-15", "1998-09-15", "1998-10-15", "1998-11-15", "1998-12-15", 
"1999-01-15", "1999-02-15", "1999-03-15", "1999-04-15", "1999-05-15", 
"1999-06-15", "1999-07-15", "1999-08-15", "1999-09-15", "1999-10-15", 
"1999-11-15", "1999-12-15"), overall = c(17.548, 16.189, 15.667, 
15.509, 16.709, 18.822, 22.722, 25.372, 26.597, 25.256, 22.857, 
20.242, 17.179, 16.003, 15.14, 15.522, 16.537, 19.658, 23.245, 
25.313, 26.753, 26.04, 23.843, 20.94, 17.842), eastern = c(18.751, 
17.155, 16.504, 16.208, 17.702, 19.66, 23.512, 25.912, 27.226, 
26.151, 24.44, 21.867, 18.797, 17.206, 16.345, 16.689, 17.521, 
20.74, 24.161, 26.053, 27.112, 26.597, 24.94, 22.375, 19.439)), 
class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", 
"14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", 
"25"))