聚合为空因子但保留行 [英] aggregate with empty factor but keep row
本文介绍了聚合为空因子但保留行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对by()有类似的问题,我接受了必须手动替换生成的NA的事实。现在,我想聚合我的data.frame并保留结构。例如我的较大数据集包含100个国家/地区* 10年* 5个细分的因素,因此应减少到5000行。但是有时某些细分市场因素是空的,我只能得到<5000行。我没办法解决...
I had a similar questions with by() where I accepted the fact that I had to manually replace the resulting NAs. Now I would like to aggregate my data.frame and keep the structure. e.g. My larger data set has factors for 100 countries * 10 years * 5 segments, so it should reduce to 5000 rows. But sometimes some of the segment factors are empty and i only get <5000 rows. I cannot get my head around it...
我的MWE仍然适用:
#All 3 categories are used
df1<-data.frame( val=rep(seq(1:4),3), factor=cut(rep(seq(1:4),3),breaks=c(1,2,3,4), include.lowest = TRUE, ordered_results=True , labels=LETTERS[1:3]))
# Thirds category is not used
df2<-data.frame( val=rep(seq(1:3),4), factor=cut(rep(seq(1:3),4),breaks=c(1,2,3,4), include.lowest = TRUE, ordered_results=True , labels=LETTERS[1:3]))
#df1 reduces to 3 rows as each category is used
aggregate(df1$val,list(df1$factor),sum)
#df2 reduces to 2 rows because C is empty
aggregate(df2$val,list(df2$factor),sum)
#I would like
data.frame(Group.1=LETTERS[1:3], x=c(12,12,0))
Group.1 x
1 A 12
2 B 12
3 C 0
推荐答案
# create dataset
df2 <- data.frame( val=rep(seq(1:3),4), factor=cut(rep(seq(1:3),4),breaks=c(1,2,3,4), include.lowest = TRUE, ordered_results=True , labels=LETTERS[1:3]))
library(dplyr)
levels(df2$factor) %>% # get distinct levels of the factor variable
data.frame(factor = .) %>% # create a data frame
left_join(df2 %>% # join with
group_by(factor) %>% # for each value that exists
summarise(x = sum(val)), by = "factor") %>% # sum column val
mutate(x = coalesce(x, 0L)) # replace NAs with 0s
# factor x
# 1 A 12
# 2 B 12
# 3 C 0
或者不带任何包装
dd = merge(data.frame(Group.1 = levels(df2$factor)),
aggregate(df2$val,list(df2$factor),sum), all.x = T)
dd$x = ifelse(is.na(dd$x), 0, dd$x)
dd
# Group.1 x
# 1 A 12
# 2 B 12
# 3 C 0
或使用 data.table
包检查其速度是否更快
Or using data.table
package to check if it's faster
library(data.table)
# assuming you start with a data frame
df2 <- data.frame( val=rep(seq(1:3),4), factor=cut(rep(seq(1:3),4),breaks=c(1,2,3,4), include.lowest = TRUE, ordered_results=True , labels=LETTERS[1:3]))
# create a data table with all unique values of the variable "factor" and an index (key) on that variable
dt_levels = data.table(factor = levels(df2$factor), key = "factor")
# make df2 a data table with an index on column "factor" and aggregate
dt_sum = setDT(df2, key = "factor")[, list(Sum = sum(val)), by = "factor"]
# left join the two data tables and replace NA values with 0s
dt_result = dt_sum[dt_levels][, Sum := ifelse(is.na(Sum), 0, Sum)]
dt_result[]
# factor Sum
# 1: A 12
# 2: B 12
# 3: C 0
这篇关于聚合为空因子但保留行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文