将数值除以计数,除非存在零值,否则置零 [英] divide value by count except when zero values are present put zero
问题描述
我有这个数据框。
library(dplyr)
df<-tibble(grp = c (1,1,1,1,1,1,2,2,3,3,4,4,4,4,5,5,5,6,6,6,7),
count = c( NA,NA,NA,NA,NA,NA,NA,NA,6、6、6、3、3、3、3、3、3、3、3、3、3),
mdo = c(1500 ,1500、1500、1500,
1500、1500,NA,0,
0、0、1100、1100,
1100、200、200、200,
1100、1100 ,1100,0)
)
我要执行此计算。
df<-df%&%;%
变异(结果= mdo / count)
结果:
grp count mdo result
$现在,我想进行上述计算,但是当先前的mdo值(每组,grp)为零时,将其保留为零。因此,我希望结果为:
< dbl> < dbl> < dbl> < dbl>
1 1 NA 1500 NA
2 1 NA 1500 NA
3 1 NA 1500 NA
4 1 NA 1500 NA
5 1 NA 1500 NA
6 1不适用1500不适用
7 2不适用不适用不适用
8 3 6 0 0
9 3 6 0 0
10 3 6 0 0
11 4 3 1100 367。
12 4 3 1100 367.
13 4 3 1100 367.
14 5 3 200 66.7
15 5 3 200 66.7
16 5 3 200 66.7
17 6 3 1100 367.
18 6 3 1100 367.
19 6 3 1100 367.
20 7 3 0 0
NA
NA
NA
NA
NA
不适用
不适用
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
66.66667
66.66667
66.66667
366.66667
366.66667
366.66667
0.00000
编辑---
使用此数据
df<-tibble(grp = c(1, 1,1,1,1,1,2,2,3,3,4,4,4,4,5,5,5,6,6,6,7,7,7,8),
计数= c(NA,NA,NA,NA,NA,NA,NA,6、6、6,NA,NA,NA,NA,3、3、3、3、3、3、3、3、3 ,3),
mdo = c(1500,1500,1500,1500,1500,1500,
NA,0,0,0,NA,NA,NA,NA,
1100, 1100,1100,
200,200,200,
1100,1100,1100,0)
)
给出:
grp count mdo prev_mdo结果
< dbl> < dbl> < dbl> < dbl> < dbl>
1 1 NA 1500 NA NA
2 1 NA 1500 NA NA
3 1 NA 1500 NA NA
4 1 NA 1500 NA NA
5 1 NA 1500 NA NA
6 1 NA 1500 NA NA
7 2 NA NA 1500 NA
8 3 6 0 NA 0
9 3 6 0 NA 0
10 3 6 0 NA 0
11 4 NA NA 0 0
12 4 NA NA 0 0
13 4 NA NA 0 0
14 4 NA NA 0 0
15 5 3 1100 NA 367 。
16 5 3 1100 NA 367.
17 5 3 1100 NA 367.
18 6 3 200 1100 66.7
19 6 3 200 1100 66.7
20 6 3 200 1100 66.7
21 7 3 1100 200 367.
22 7 3 1100 200 367.
23 7 3 1100 200 367.
24 8 3 0 1100 0
,但我希望前367个值是零。因为在1100之前,我们有NA(必须忽略),在这些NA之前,我们有零。因此,结果在那里应该为零。取而代之的是,代码现在跳过了NA,移至前三个零(NA上方),并用它们除以1110。
解决方案
group_mdo<-df%>%
select(grp,mdo)%&%;%
unique()%&%;%
mutate(prev_mdo =滞后(mdo))%>%
select(-mdo)
df%>%
left_join(group_mdo,by = grp)%> %
mutate(结果= ifelse(prev_mdo!= 0 | is.na(prev_mdo),mdo / count,0))
给出:
grp count mdo prev_mdo结果
< dbl> < dbl> < dbl> < dbl> < dbl>
1 1 NA 1500 NA NA
2 1 NA 1500 NA NA
3 1 NA 1500 NA NA
4 1 NA 1500 NA NA
5 1 NA 1500 NA NA
6 1 NA 1500 NA NA
7 2 NA NA 1500 NA
8 3 6 0 NA 0
9 3 6 0 NA 0
10 3 6 0 NA 0
11 4 3 1100 0 0
12 4 3 1100 0 0
13 4 3 1100 0 0
14 5 3 200 1100 66.7
15 5 3 200 1100 66.7
16 5 3 200 1100 66.7
17 6 3 1100 200 367.
18 6 3 1100 200 367.
19 6 3 1100 200 367.
20 7 3 0 1100 0
编辑
这应该有效
group_mdo<-df%>%
select( grp,mdo)%>%
unique()%>%
mutate(prev_mdo = lag(mdo))%>%
select(-mdo)%>%
tidyr :: fill(prev_mdo,.direction = down)
df%&%;%
left_join(group_mdo,by = grp)%>%
mutate(结果= ifelse(prev_mdo!= 0,mdo /计数,0))
I have this data frame.
library(dplyr) df <- tibble(grp = c(1, 1, 1, 1, 1, 1, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7), count = c(NA, NA, NA, NA, NA, NA, NA, 6, 6, 6, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), mdo = c(1500, 1500, 1500, 1500, 1500, 1500, NA, 0, 0, 0, 1100, 1100, 1100, 200, 200, 200, 1100, 1100, 1100, 0) )
I want to do this computation.
df <- df %>% mutate(result = mdo/count)
the result:
grp count mdo result <dbl> <dbl> <dbl> <dbl> 1 1 NA 1500 NA 2 1 NA 1500 NA 3 1 NA 1500 NA 4 1 NA 1500 NA 5 1 NA 1500 NA 6 1 NA 1500 NA 7 2 NA NA NA 8 3 6 0 0 9 3 6 0 0 10 3 6 0 0 11 4 3 1100 367. 12 4 3 1100 367. 13 4 3 1100 367. 14 5 3 200 66.7 15 5 3 200 66.7 16 5 3 200 66.7 17 6 3 1100 367. 18 6 3 1100 367. 19 6 3 1100 367. 20 7 3 0 0
Now, I want to do the above computation but when the previous mdo value (per group , grp) is zero, leave it as zero. So, I want the result to be:
NA NA NA NA NA NA NA 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 66.66667 66.66667 66.66667 366.66667 366.66667 366.66667 0.00000
EDIT ---
Using this data
df <- tibble(grp = c(1, 1, 1, 1, 1, 1, 2, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8), count = c(NA, NA, NA, NA, NA, NA, NA, 6, 6, 6, NA, NA, NA, NA, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), mdo = c(1500, 1500, 1500, 1500, 1500, 1500, NA,0, 0, 0, NA, NA, NA, NA, 1100, 1100, 1100, 200, 200,200, 1100, 1100, 1100, 0)
)
gives:
grp count mdo prev_mdo result <dbl> <dbl> <dbl> <dbl> <dbl> 1 1 NA 1500 NA NA 2 1 NA 1500 NA NA 3 1 NA 1500 NA NA 4 1 NA 1500 NA NA 5 1 NA 1500 NA NA 6 1 NA 1500 NA NA 7 2 NA NA 1500 NA 8 3 6 0 NA 0 9 3 6 0 NA 0 10 3 6 0 NA 0 11 4 NA NA 0 0 12 4 NA NA 0 0 13 4 NA NA 0 0 14 4 NA NA 0 0 15 5 3 1100 NA 367. 16 5 3 1100 NA 367. 17 5 3 1100 NA 367. 18 6 3 200 1100 66.7 19 6 3 200 1100 66.7 20 6 3 200 1100 66.7 21 7 3 1100 200 367. 22 7 3 1100 200 367. 23 7 3 1100 200 367. 24 8 3 0 1100 0
but I would expect the first 367. values to be zero. Because before 1100 we have NA (which we must omit) and before these NA we have zero. So, result should be zero there. Instead, the code right now skips the NA, goes to previous 3 zeros (above NA) and divides 1110 with them.
解决方案group_mdo <- df %>% select(grp, mdo) %>% unique() %>% mutate(prev_mdo = lag(mdo)) %>% select(-mdo) df %>% left_join(group_mdo, by = "grp") %>% mutate(result = ifelse(prev_mdo != 0 | is.na(prev_mdo), mdo / count, 0))
gives:
grp count mdo prev_mdo result <dbl> <dbl> <dbl> <dbl> <dbl> 1 1 NA 1500 NA NA 2 1 NA 1500 NA NA 3 1 NA 1500 NA NA 4 1 NA 1500 NA NA 5 1 NA 1500 NA NA 6 1 NA 1500 NA NA 7 2 NA NA 1500 NA 8 3 6 0 NA 0 9 3 6 0 NA 0 10 3 6 0 NA 0 11 4 3 1100 0 0 12 4 3 1100 0 0 13 4 3 1100 0 0 14 5 3 200 1100 66.7 15 5 3 200 1100 66.7 16 5 3 200 1100 66.7 17 6 3 1100 200 367. 18 6 3 1100 200 367. 19 6 3 1100 200 367. 20 7 3 0 1100 0
EDIT
This should work for both cases now.
group_mdo <- df %>% select(grp, mdo) %>% unique() %>% mutate(prev_mdo = lag(mdo)) %>% select(-mdo) %>% tidyr::fill(prev_mdo, .direction = "down") df %>% left_join(group_mdo, by = "grp") %>% mutate(result = ifelse(prev_mdo != 0, mdo / count, 0))
这篇关于将数值除以计数,除非存在零值,否则置零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!