Masking Values for column in function

shoaibali · August 5, 2020, 7:06am

the function i have is below

library(dplyr)
library(expss)

df <- data.frame(
  aa = c("q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c","q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c"),
  col1=c(1,2,3,2,1,2,3,4,4,4,5,3,4,2,1,2,5,3,2,1,2,4,2,1,3,2,1,2,3,1,2,3,4,4,4,1,2,5,3,5),
  col2=c(2,1,1,7,4,1,2,7,5,7,2,6,2,2,6,3,4,3,2,5,7,5,6,4,4,6,5,6,4,1,7,7,2,7,7,2,3,7,2,4)
)

crosstab_sum <- function(dat1,var1,var2){
  
  var1 <- rlang::parse_expr(var1)
  var2 <- rlang::parse_expr(var2)
  
  dat1 = dat1 %>% select(!!var1,!!var2)
  var_lab(dat1[[1]]) <- ""
  var_lab(dat1[[2]]) <- ""
  tab1 <- cro_cpct(dat1[[1]],dat1[[2]])
  tab1 <- as.data.frame(tab1)
  tab1[which(tab1[,1]=="#Total cases"),1] <- "N"
  tab1[is.na(tab1)] <- 0
  tab1 <- tab1 %>% mutate_if(is.numeric,round,digits=1) 
  tab1 <- tab1 %>% format(is.numeric(tab1),nsmall = 1)
    tab1[nrow(tab1), ] <- ifelse(tab1[nrow(tab1), ] %in% c(3, 4, 5), "---", tab1[nrow(tab1), ])
  tab1[-nrow(tab1), -1] <- sapply(tab1[-nrow(tab1), -1], function(x) paste(x, "%"))
  tab1 <- flextable::flextable(tab1)
  tab1
}

crosstab_sum(dat1 = df, var1 = "col1", var2 = "col2")

Do we have solution if total falls in (3,4,5) then whole column values will replace with "--" like below

if total falls in (3,4,5) then all column will not show any value (replace by "--")

i have tried but not able to do

output should be like

MyKo101 · August 5, 2020, 10:02am

Replacing 3,4 & 5 with "---" would require the entire column to be converted from numeric to character.

Try the following:

tab1 %>%
  mutate(across(where(is.numeric),.~if_else(last(.) %in% c(3,4,5),"---", as.character(.))))

Also, mutate_if() is deprecated as of dplyr 1.0.0, you're recommended to use the above syntax of mutate(across(where(<predicate>),<function>))

shoaibali · August 5, 2020, 10:39am

i have used below

``
tab1 <- tab1 %>%
mutate_if(where(is.numeric,.~if_else(last(.) %in% c(3,4,5))),"---", as.character(.))

``
getting errror

Error in calc_cond(cond, envir = e) : 'cond' must be logical or numeric

also giving error on across

Error in across(where(is.numeric), . ~ if_else(last(.) %in% c(3, 4, 5), :
could not find function "across"

MyKo101 · August 6, 2020, 10:01am

You need to update dplyr. It needs to be at least version 1.0.0

shoaibali · August 6, 2020, 10:53am

i have updated , still facing

Error in across(where(is.numeric), . ~ if_else(last(.) %in% c(3, 4, 5), :
could not find function "across"

gueyenono · August 6, 2020, 12:20pm

If you get this error, then it means that you:

did not update the dplyr package to 1.0.0 (but you said you did) or...
You did not load the package with library(dplyr) before executing your code.

shoaibali · August 6, 2020, 12:44pm

I have updated package but now getting new error, not able to understand this error

tab1 <- cro_cpct(dat1[[1]],dat1[[2]])
  tab1 <- as.data.frame(tab1)
  tab1[which(tab1[,1]=="#Total cases"),1] <- "N"
  tab1[is.na(tab1)] <- 0
  tab1 <- tab1 %>% mutate_if(is.numeric,round,digits=1)
  tab1 <- tab1 %>% format(is.numeric(tab1),nsmall = 1)
  tab1 %>%
    mutate(across(where(is.numeric),.~if_else(last(.) %in% c(3,4,5),"---", as.character(.))))
  tab1[nrow(tab1), ] <- ifelse(tab1[nrow(tab1), ] %in% c(3, 4, 5), "--", tab1[nrow(tab1), ])
  tab1[-nrow(tab1), -1] <- sapply(tab1[-nrow(tab1), -1], function(x) paste(x, "%"))
  tab1 <- flextable::flextable(tab1)

Error: Problem with mutate() input ..1.
x Can't convert a two-sided formula to a function
i Input ..1 is across(...).

gueyenono · August 6, 2020, 12:59pm

The problem comes from this line of code:

mutate(across(where(is.numeric),.~if_else(last(.) %in% c(3,4,5),"---", as.character(.))))

You put a dot "." before ~ if_else() and that is not supposed to be. Just remove it and the code will work.

shoaibali · August 6, 2020, 1:04pm

now its not giving any error but its not giving any solution i guess its because of double (value)

dataa<-data.frame(
  aa = c("q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c","q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c"),
col1=c(1,2,3,2,1,2,3,4,4,4,5,3,4,2,1,2,5,3,2,1,2,4,2,1,3,2,1,2,3,1,2,3,4,4,4,1,2,5,3,5),
col2=c(2,1,1,7,4,1,2,7,5,7,2,6,2,2,6,3,4,3,2,5,7,5,6,4,4,6,5,6,4,1,7,7,2,7,7,2,3,7,2,4)
)


dat1 = dataa
var1 = "col1"
var2 = "col2"


#crosstab_sum <- function(dat1,var1,var2){
  
  var1 <- rlang::parse_expr(var1)
  var2 <- rlang::parse_expr(var2)
  
  dat1 = dat1 %>% select(!!var1,!!var2)
  var_lab(dat1[[1]]) <- ""
  var_lab(dat1[[2]]) <- ""
  tab1 <- cro_cpct(dat1[[1]],dat1[[2]])
  tab1 <- as.data.frame(tab1)
  tab1[which(tab1[,1]=="#Total cases"),1] <- "N"
  tab1[is.na(tab1)] <- 0
  tab1 <- tab1 %>% mutate_if(is.numeric,round,digits=1)
  tab1 <- tab1 %>% format(is.numeric(tab1),nsmall = 1)
  tab1 <-tab1 %>%
    mutate(across(where(is.numeric),~if_else(last(.) %in% c(3,4,5),"---", as.character(.))))
  tab1[nrow(tab1), ] <- ifelse(tab1[nrow(tab1), ] %in% c(3, 4, 5), "--", tab1[nrow(tab1), ])
  tab1[-nrow(tab1), -1] <- sapply(tab1[-nrow(tab1), -1], function(x) paste(x, "%"))
  tab1 <- flextable::flextable(tab1)
  tab1

column are still showing values....am i doing something wrong...??
might be working for you

gueyenono · August 6, 2020, 1:10pm

The code I gave you in your last question solved that problem. How is this question different?

shoaibali · August 6, 2020, 1:18pm

yes, you gave me code that worked perfectly fine . the previous code was to convert the total row to "--" if fall in in (3,4,5) but now the requirement is to change the whole column if fall in (3,4,5) like i described in attached.please refer above.

please also help me to understand how "~" works ...

gueyenono · August 6, 2020, 1:36pm

Point of clarification. You want to change the whole column to "---" if the total row is either 3, 4 or 5?

shoaibali · August 6, 2020, 1:37pm

yes, ofcourse the same i need changes in function

nirgrahamuk · August 6, 2020, 1:47pm

dataa<-data.frame(
  aa = c("q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c","q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c"),
  col1=c(1,2,3,2,1,2,3,4,4,4,5,3,4,2,1,2,5,3,2,1,2,4,2,1,3,2,1,2,3,1,2,3,4,4,4,1,2,5,3,5),
  col2=c(2,1,1,7,4,1,2,7,5,7,2,6,2,2,6,3,4,3,2,5,7,5,6,4,4,6,5,6,4,1,7,7,2,7,7,2,3,7,2,4)
)

library(expss)
library(tidyverse)
dat1 = dataa
var1 = "col1"
var2 = "col2"


#crosstab_sum <- function(dat1,var1,var2){

var1 <- rlang::parse_expr(var1)
var2 <- rlang::parse_expr(var2)

dat1 = dat1 %>% select(!!var1,!!var2)
var_lab(dat1[[1]]) <- ""
var_lab(dat1[[2]]) <- ""
tab1 <- cro_cpct(dat1[[1]],dat1[[2]])
tab1 <- as.data.frame(tab1)

tcase_row <- which(tab1[,1]=="#Total cases")
tab1[tcase_row,1] <- "N"
tab1[is.na(tab1)] <- 0
tab1 <- tab1 %>% mutate_if(is.numeric,round,digits=1)
tab1 <- tab1 %>% format(is.numeric(tab1),nsmall = 1) # this step makes it so that you cant 
                                                     #look for 3,4,5 in this row will 
                                                     # be ' 3.0' etc..

cols_to_blank_index <- which(tab1[tcase_row,] %in% c(" 3.0"," 4.0", " 5.0")) 
cols_to_blank <- names(tab1)[cols_to_blank_index]
tab1[-nrow(tab1), -1] <- sapply(tab1[-nrow(tab1), -1], function(x) paste(x, "%"))
tab2 <- mutate(tab1,
               across(all_of(cols_to_blank),
                      function(x){'--'}))


tab2 <- flextable::flextable(tab2)
tab2

gueyenono · August 6, 2020, 2:02pm

@nirgrahamuk provided a solution. I also made this:

library(dplyr)
library(expss)

df <- data.frame(
  aa = c("q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c","q","r","y","v","g","y","d","s","n","k","y","d","s","t","n","u","l","h","x","c"),
  col1=c(1,2,3,2,1,2,3,4,4,4,5,3,4,2,1,2,5,3,2,1,2,4,2,1,3,2,1,2,3,1,2,3,4,4,4,1,2,5,3,5),
  col2=c(2,1,1,7,4,1,2,7,5,7,2,6,2,2,6,3,4,3,2,5,7,5,6,4,4,6,5,6,4,1,7,7,2,7,7,2,3,7,2,4)
)

crosstab_sum <- function(dat1,var1,var2){
  
  var1 <- rlang::parse_expr(var1)
  var2 <- rlang::parse_expr(var2)
  
  dat1 <- dat1 %>% select(!!var1,!!var2)
  var_lab(dat1[[1]]) <- ""
  var_lab(dat1[[2]]) <- ""
  tab1 <- cro_cpct(dat1[[1]],dat1[[2]])
  tab1 <- as.data.frame(tab1)
  tab1[which(tab1[,1]=="#Total cases"),1] <- "N"
  tab1[is.na(tab1)] <- 0
  tab1 <- tab1 %>%
    mutate(
      across(
        .cols = where(is.numeric),
        .fns = ~ round(.x, digits = 1)
      )
    )
  mask_indices <- sapply(tab1, function(x) x[length(x)] %in% c(3, 4, 5)) %>%
    which()
  tab1[, mask_indices] <- "---"
  tab1[-nrow(tab1), -mask_indices] <- sapply(tab1[-nrow(tab1), -mask_indices], function(x) paste(x, "%"))
  tab1 <- flextable::flextable(tab1)
}

crosstab_sum(dat1 = df, var1 = "col1", var2 = "col2")

shoaibali · August 6, 2020, 2:02pm

actually these two lines also required because i need all values in decimal points except Total values but by adding these two lines doesn't work

tab1 <- tab1 %>% mutate_if(is.numeric,round,digits=1)
tab1 <- tab1 %>% format(is.numeric(tab1),nsmall = 1)

Just one more thing for my knowledge.

after this line

tab2 <- mutate(tab1,
                 across(all_of(cols_to_blank),
                        function(x){'--'}))

if i want to change highlighted "0.0%" with "--" from all cells then what should i do ...??

i mean with that function "cols_to_blank" for (3,4,5) , also want to change if any cell have "0.0%" then what will be the approach...??

shoaibali · August 7, 2020, 12:09pm

i have tried this way also

tab1 <- tab1 %>%
    mutate(
      across(
        .cols = where(is.numeric),
        .fns = ~ format(round(.x, digits = 1),nsmall = 1)
      )
    )

still doesn't work

system · August 28, 2020, 12:09pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.