select group before certain observations separated by grouping var in R with NA control

1
votes

My sample. 

 data=structure(list(add = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("x", 
"y"), class = "factor"), x1 = c(14L, 15L, 36L, 0L, 0L, 0L, 53L, 
10L, 39L, 27L, 67L, 25L, 19L, 49L, 53L, 64L, 61L, 12L, 75L, 34L, 
88L, 43L, 85L, 93L, 44L, 31L, 37L, 90L, 66L, 39L, 59L, 96L, 41L, 
23L, 20L, 26L, 69L, 28L, 35L, 96L, 87L, 82L, 70L, 68L, 26L, 12L, 
58L, 18L, 76L, 93L, 3L, 31L), group = structure(c(2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L), .Label = c("female", "male"), class = "factor")), .Names = c("add", 
"x1", "group"), class = "data.frame", row.names = c(NA, -52L))

In this data there is group variable (sex (male and female) I need get statistics mean and 25 percentile for ALL male which go before female. Male which after female, i don't touch. Also female i don't touch. this is analysis split by group x and y from add column. if for male that go after female value by x1 > than 25 percentile, which we calculated for male before female, then this value must be replaced by mean for male before female " Female category we dont' touch.

The solutuion of AntoniosK is very good

library(tidyverse)
library(data.table)

data %>%  
  group_by(add) %>%                                           # for each add do the below...
  mutate(group2 = rleid(group)) %>% 
  group_by(add, group, group2) %>%
  mutate(MEAN = mean(x1[group=="male" & group2==1]),               
         Q25 = quantile(x1[group=="male" & group2==1], 0.25)) %>%
  group_by(add) %>%                                            # for each add update x1 values....
  mutate(x1 = ifelse(group=="male" & group2==3 & x1 > unique(Q25[!is.na(Q25)]), unique(MEAN[!is.na(MEAN)]), x1)) %>%
  ungroup() %>%
  select(-group2) %>%
  data.frame()

but now i want replace 0 value by x1 to Na.

data$x1[data$x1 == 0] <- NA

after it, when i un script, i get the error

Error in mutate_impl(.data, dots) : Evaluation error: missing values and NaN's not allowed if 'na.rm' is FALSE.

How to do, that script passed NA and work only with int value?

edit

data=structure(list(add = c(11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 
                       11202L, 11202L, 11202L, 11202L, 11202L, 11202L, 11202L), x1 = c(NA, 
                                                                                       2L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, NA, NA, 
                                                                                       NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, 
                                                                                       NA, NA, NA, NA, NA, NA, NA, 3L, NA, NA, NA, NA, 1L, 1L, NA, NA, 
                                                                                       NA, NA, NA), group = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                                                                                                        2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                                                                                                        2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 2L, 2L, 
                                                                                                                        2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("female", 
                                                                                                                                                                                        "male"), class = "factor")), .Names = c("add", "x1", "group"), class = "data.frame", row.names = c(NA, 
                                                                                                                                                                                                                                                                                           -52L))

library(tidyverse)
library(data.table)

data %>%  
  group_by(add) %>%                                          
  mutate(group2 = rleid(group)) %>% 
  group_by(add, group, group2) %>%
  mutate(MEAN = mean(x1[group=="male" & group2==1]),               
         Q25 = quantile(x1[group=="male" & group2==1], 0.25)) %>%
  group_by(add) %>%                                           
  mutate(x1 = ifelse(group=="male" & group2==3 & x1 > unique(Q25[!is.na(Q25)]), unique(MEAN[!is.na(MEAN)]), x1),
         x1 = ifelse(x1==0, NA, x1)) %>%  # new code added
  ungroup() %>%
  select(-group2) %>%
  data.frame()

Edit2

result of code

add x1  group   MEAN    Q25
x   14.00000    male    23.72727    5.0
x   15.00000    male    23.72727    5.0
x   36.00000    male    23.72727    5.0
x   0.00000 male    23.72727    5.0
x   0.00000 male    23.72727    5.0
x   0.00000 male    23.72727    5.0
x   53.00000    male    23.72727    5.0
x   10.00000    male    23.72727    5.0
x   39.00000    male    23.72727    5.0
x   27.00000    male    23.72727    5.0
x   67.00000    male    23.72727    5.0
x   25.00000    female  NaN NA
x   19.00000    female  NaN NA
x   49.00000    female  NaN NA
x   53.00000    female  NaN NA
x   64.00000    female  NaN NA
x   61.00000    female  NaN NA
x   12.00000    female  NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA
x   23.72727    male    NaN NA

After

add x1     group
x   94.90   male

sum of first 4 male after female=94.90

1
rdplyrdata.table
One of your column names is "how this analysis split by group?group". I can't see any column ReturnCount in data. And I can't see any zero values anywhere when I run that code. – AntoniosK
ReturnCount is x1. – varimax
I think it will be easier if you post an example where x1 has zeros so we can check why you get that error. – AntoniosK
@AntoniosK, i edited post with zero. please check – varimax
@AntoniosK, i provided it. – varimax

1 Answers

1
votes

I've added a piece of code that would solve your issue and a brief explanation of the error.

Updated code

data=structure(list(add = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("x", 
"y"), class = "factor"), x1 = c(14L, 15L, 36L, 0L, 0L, 0L, 53L, 
10L, 39L, 27L, 67L, 25L, 19L, 49L, 53L, 64L, 61L, 12L, 75L, 34L, 
88L, 43L, 85L, 93L, 44L, 31L, 37L, 90L, 66L, 39L, 59L, 96L, 41L, 
23L, 20L, 26L, 69L, 28L, 35L, 96L, 87L, 82L, 70L, 68L, 26L, 12L, 
58L, 18L, 76L, 93L, 3L, 31L), group = structure(c(2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L), .Label = c("female", "male"), class = "factor")), .Names = c("add", 
"x1", "group"), class = "data.frame", row.names = c(NA, -52L))

library(tidyverse)
library(data.table)

data %>%  
  group_by(add) %>%                                          
  mutate(group2 = rleid(group)) %>% 
  group_by(add, group, group2) %>%
  mutate(MEAN = mean(x1[group=="male" & group2==1]),               
         Q25 = quantile(x1[group=="male" & group2==1], 0.25)) %>%
  group_by(add) %>%                                           
  mutate(x1 = ifelse(group=="male" & group2==3 & x1 > unique(Q25[!is.na(Q25)]), unique(MEAN[!is.na(MEAN)]), x1),
         x1 = ifelse(x1==0, NA, x1)) %>%  # new code added
  ungroup() %>%
  select(-group2) %>%
  data.frame()

Error explanation

You have to run the previous part of the code and in the end you just update the x1 column. You get that error because NA values break the mean and quantile calculations you need to do.

An alternative way would be to update x1 in the beginning and then use na.rm=T for your calculations.

For the new case you mentioned, where you start with NA values for x1 try this:

data %>%  
  group_by(add) %>%                                          
  mutate(group2 = rleid(group)) %>% 
  group_by(add, group, group2) %>%
  mutate(MEAN = mean(x1[group=="male" & group2==1], na.rm = T),      ## extra code here ##    
         Q25 = quantile(x1[group=="male" & group2==1], 0.25, na.rm = T)) %>%  ## extra code here ##
  group_by(add) %>%                                           
  mutate(x1 = ifelse(group=="male" & group2==3 & x1 > unique(Q25[!is.na(Q25)]), unique(MEAN[!is.na(MEAN)]), x1))%>%
  ungroup() %>%
  select(-group2) %>%
  data.frame()

For the new case (edit 2) you mentioned, first save the output of the previous code as data2:

data2 = data %>% ...

And then run this:

data2 %>%
  group_by(add) %>%                           # for each add value                      
  mutate(group2 = rleid(group)) %>%           # created group2
  filter(group=="male" & group2==3) %>%       # keep only male after female
  summarise(SUM = sum(x1[row_number() <= 4])) # get sum of x1 for first 4 rows

# # A tibble: 2 x 2
#   add     SUM
#   <fct> <dbl>
# 1 x      94.9
# 2 y     107.