我有以下两个表:
Table_1
ID Interval
1 10
1 11
2 11
和
Table_2
ID Interval Rating
1 10 0.5
1 10 0.3
1 11 0.1
2 11 0.1
2 11 0.2
输出表应该如下所示:
ID Interval Mean Ratings
1 10 0.4
1 11 0.1
2 11 0.15
我的目标是基于两个条件/列ID和interval连接两个表。假设我对相同的ID和间隔有多个评级,我想计算评级的平均值。虽然in是唯一的(~9500),但对于不同的in,间隔是重复的(如上表所示)。我目前的方法是使用带有2by参数的连接函数。如何创建一个根据条件ID和时间间隔连接Table_1和Table_2的终结表,并在result列中接收平均评级?
left_join(Table_1, Table_2, by = c("ID" = "ID", "Interval" = "Interval"))
发布于 2018-06-08 18:59:23
首先,您需要总结第二个表DT2
,然后与第一个表DT1
执行右连接。
library(data.table)
DT1[DT2[, .(Mean_Rating = mean(Rating)), .(ID, Interval)], on = c(ID = "ID", Interval = "Interval")]
这给了我们
ID Interval Mean_Rating
1: 1 10 0.40
2: 1 11 0.10
3: 2 11 0.15
示例数据:
DT1 <- structure(list(ID = c(1L, 1L, 2L), Interval = c(10L, 11L, 11L
)), .Names = c("ID", "Interval"), class = c("data.table", "data.frame"
), row.names = c(NA, -3L))
DT2 <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L), Interval = c(10L,
10L, 11L, 11L, 11L), Rating = c(0.5, 0.3, 0.1, 0.1, 0.2)), .Names = c("ID",
"Interval", "Rating"), class = c("data.table", "data.frame"), row.names = c(NA,
-5L))
发布于 2018-06-08 18:41:40
你可以用dplyr
的left_join
,group_by
,然后是summarise
来实现。
library(dplyr)
table1 %>%
left_join(table2, by = c("ID", "Interval")) %>%
group_by(ID, Interval) %>%
summarise("Mean Ratings" = mean(Rating))
## A tibble: 3 x 3
## Groups: ID [?]
# ID Interval `Mean Ratings`
# <int> <int> <dbl>
#1 1 10 0.4
#2 1 11 0.1
#3 2 11 0.15
data
table1 <- read.table(header = T, text="ID Interval
1 10
1 11
2 11")
table2 <- read.table(header = T, text = "ID Interval Rating
1 10 0.5
1 10 0.3
1 11 0.1
2 11 0.1
2 11 0.2")
发布于 2018-06-08 17:11:26
你不需要加入。相反,可以绑定您的表,并使用来自dplyr的group & summarize。下面的代码达到了您的要求:
library(dplyr)
table_1 <- data.frame("ID"= c(1,1,2),"Interval"=c (10,11,11),"Rating"= c(NA,NA,NA))
table_2 <- data.frame("ID"= c(1,1,1,2,2),"Interval"= c(10,10,11,11,11),"Rating"= c(0.5,0.3,0.1,0.1,0.2))
df1 <- bind_rows(table_1,table_2) %>% group_by(ID,Interval) %>% summarise("Mean Ratings" = mean(Rating,na.rm = TRUE))
https://stackoverflow.com/questions/50756878
复制相似问题