时间戳

首页 > 解决方案 > 如何在R中执行多列的countifs（）函数（计算文本值）

r - 如何在R中执行多列的countifs（）函数（计算文本值）

问题描述

我想按用户分组并计算order_hour_type为“白天”和“晚上”的数量，并分别以“白天”和“晚上”两列表示，按用户分组。

user_id  order_hour_type order_day_type
1         daytime            weekend
1         daytime            weekday
1         daytime            weekday
1         daytime            weekend
2         evening            weekday
2         evening            weekday
2         evening            weekend
2         daytime            weekday
3         daytime            weekday
3         evening            weekday
3         daytime            weekday

结果应该是这样的：

user_id daytime evening weekend weekday
1         4       0        2       2
2         1       3        1       3
3         2       1        0       3

我尝试使用dplyr带有以下代码的包：

（以添加“白天”栏为例）

agg1 <- df %>%
  group_by(user_id,order_hour_type) %>%
  summarise(
    daytime = sum(order_hour_type == "daytime"),
  )

结果很奇怪，只有一个用户：

> head(agg1)
  daytime
1  834149

我该怎么做才能产生我的预期结果？非常感谢！！

标签： rtextdplyrcountif

解决方案

一个选项是gather进入“长”格式，然后count在列上执行一个并spread返回“宽”

library(dplyr)
library(tidyr)
gather(df1, key, val, -user_id) %>% 
    count(user_id, val) %>%
    spread(val, n, fill = 0)
# A tibble: 3 x 5
#  user_id daytime evening weekday weekend
#    <int>   <dbl>   <dbl>   <dbl>   <dbl>
#1       1       4       0       2       2
#2       2       1       3       3       1
#3       3       2       1       3       0

或使用melt/dcastfromdata.table

library(data.table)
dcast(melt(setDT(df1), id.var = 'user_id'), user_id ~ value, length)

一个base R选项是通过其他列的数量复制第一列，同时unlisting 其他列并使用table

table(rep(df1[,1], 2), unlist(df1[-1]))

数据

df1 <- structure(list(user_id = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 
3L, 3L), order_hour_type = c("daytime", "daytime", "daytime", 
"daytime", "evening", "evening", "evening", "daytime", "daytime", 
"evening", "daytime"), order_day_type = c("weekend", "weekday", 
"weekday", "weekend", "weekday", "weekday", "weekend", "weekday", 
"weekday", "weekday", "weekday")), class = "data.frame", 
row.names = c(NA, 
-11L))

推荐阅读