我想根據 df2 中列的出現次數在 df1 中創建新列
df1：
df2:
[![在此處輸入影像描述][2]][2]
對于資料幀 1 中的 ID 100，在資料幀 2 中沒有分配 RM，對于 ID 103，資料幀 2 中有 2 個 RM，對于 108，有 3 個 RM
所以我的最終資料框是
我嘗試了使用左連接的合并功能，但不確定如何計算非空單元格的數量
這是示例資料集
df1 <- data.frame(Id = c(100,101,103,105,108,109),
channel = c("A","C","C","C","D","D"),
duration = c(12,23,56,89,73,76))
df2 <- data.frame(ID=c(100,103,109,105,101,108),
RM1= c("","john","","Miller","","Maddy"),
RM2 = c("","Ryan","","","","sean"),
RM3 = c("","","","","","Arvind"))
uj5u.com熱心網友回復：
在 df2 中添加聚合并合并
df2$Total=rowSums(df2[,-1]!="")
merge(
df1,
df2[c("ID","Total")],
by="ID",
all.x=T
)
ID channel duration Total
1 100 A 12 0
2 101 C 23 0
3 103 C 56 2
4 105 C 89 1
5 108 D 73 3
6 109 D 76 0
uj5u.com熱心網友回復：
這是一個tidyverse解決方案。加入ID，重塑為長格式并計算未留空的值。
df1 <- data.frame(ID = c(100,101,103,105,108,109),
channel = c("A","C","C","C","D","D"),
duration = c(12,23,56,89,73,76))
df2 <- data.frame(ID=c(100,103,109,105,101,108),
RM1= c("","john","","Miller","","Maddy"),
RM2 = c("","Ryan","","","","sean"),
RM3 = c("","","","","","Arvind"))
suppressPackageStartupMessages(library(dplyr))
library(tidyr)
inner_join(df1, df2) %>%
pivot_longer(cols = starts_with("RM")) %>%
group_by(ID, channel, duration) %>%
summarise(Total = sum(value != ""), .groups = "drop")
#> Joining, by = "ID"
#> # A tibble: 6 x 4
#> ID channel duration Total
#> <dbl> <chr> <dbl> <int>
#> 1 100 A 12 0
#> 2 101 C 23 0
#> 3 103 C 56 2
#> 4 105 C 89 1
#> 5 108 D 73 3
#> 6 109 D 76 0
由reprex 包于 2022-03-23 創建(v2.0.1)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/447564.html
標籤：r