rexpss

Function is not working as required with mrset_p variables

I am trying to create a function to get a output like below but getting different result.

I want table only for Col1,col2,col3 with regular expression as input parameter.

the output i am getting like below which is little strange and also showing data from text column which is not required.

enter image description here

Required output should look like below.

enter image description here

library(expss)

data<-data.frame(
  gender = c(1,2,1,2,1,2,1,2,2,2,2,1,1,2,2,2,2,1,1,1,1,1,2,1,2,1,2,2,2,1,2,1,2,1,2,1,2,2,2),
  sector = c(3,3,1,2,5,4,4,4,4,3,3,4,3,4,2,1,4,2,3,4,4,4,3,1,2,1,5,5,4,3,1,4,5,2,3,4,5,1,4),
  col_1=c(1,1,2,0,2,0,0,2,1,0,0,2,0,3,0,3,0,1,0,3,0,1,1,2,0,1,1,3,0,3,0,1,2,0,3,0,1,0,1),
  col_2=c(1,1,1,1,1,0,3,3,2,1,1,1,2,1,0,2,0,1,2,1,0,1,2,1,1,1,0,2,0,1,1,2,1,1,1,1,2,0,0),
  col_3=c(1,1,0,0,0,0,2,1,3,2,0,3,0,2,0,2,1,0,2,0,2,0,1,3,1,0,0,0,1,0,3,1,1,1,1,1,3,0,1),
  col_Text=c(NA,NA,NA,"we",NA,NA,NA,NA,"we",NA,NA,NA,NA,"we",NA,NA,NA,NA,"we",NA,NA,NA,NA,"we",NA,NA,NA,NA,"we",NA,"se",NA,NA,"we",NA,"te","we","te",NA),
  coll.4=c(1,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
  coll.5=c(1,2,1,1,1,2,1,2,2,1,2,1,1,1,2,2,2,1,1,1,2,1,2,1,1,1,2,2,2,1,1,2,2,1,1,1,2,2,2)
)

data$col_1<-factor(data$col_1, levels=c(1,2,3,0), labels=c("sale","Ops","MGMT","Mark"))
data$col_2<-factor(data$col_2, levels=c(1,2,3,0), labels=c("sale","Ops","MGMT","Mark"))
data$col_3<-factor(data$col_3, levels=c(1,2,3,0), labels=c("sale","Ops","MGMT","Mark"))
data$coll.4<-factor(data$coll.4, levels=c(1,0), labels=c("USA","CA"))
data$coll.5<-factor(data$coll.5, levels=c(1,0), labels=c("Local","Regional"))

data$gender<-factor(data$gender, levels=c(1,2), labels=c("Male","female"))
data$sector<-factor(data$sector, levels=c(1,2,3,4,5), labels=c("TX","CA","NY","LA","WA"))


data$gender1 <- ifelse(data$gender == "Male",1, NA)
data$total <- ifelse(data$coll.5 == "Local",1, NA)

val_lab(data$gender1)<-c("GENDER"=1)
val_lab(data$total)<-c("All Market"=1)

lkl <- with(data,list(total,gender1))


fun1<- function(dataset,pattern,banner,label){
  print(pattern)
  npatt<-paste0(pattern, ".*(?<!_TEXT)$")
  T1 = dataset %>% 
    tab_rows(banner)
  lab<-paste0(pattern, "[fun1]:", label)
  for(each_var in npatt){
    T1 = T1 %>% 
      tab_cells("|" = mrset_p(each_var)) %>% 
      tab_stat_cpct(label = lab)
  }
  T1 %>% 
    tab_pivot(stat_position = "inside_columns") %>% 
    tab_transpose()}


t1 <- fun1(dataset=data, pattern="col_",banner=lkl,label="Table 1")
Solution 
  • fun1<- function(dataset,pattern,banner,label){
    print(pattern)
    vars = grep(pattern, colnames(dataset), value = TRUE)
    T1 = dataset %>% 
        tab_rows(banner)
    lab<-paste0("[fun1]:", label)
    for(each_var in vars){
        T1 = T1 %>% 
            tab_cells("|" = ..p(each_var)) %>% 
            tab_cols(total(label = "|")) %>% 
            tab_stat_cpct(label = paste0(lab, "|",  each_var), total_row_position = "none")
    }
    T1 %>% 
        tab_pivot(stat_position = "inside_columns") %>% 
        tab_transpose()
    }


t1 <- fun1(dataset=data, pattern="^col_[0-9]",banner=lkl,label="Table 1")
t1

# |                |       | All Market |      |      |      | GENDER |      |      |      |
# |                |       |       sale |  Ops | MGMT | Mark |   sale |  Ops | MGMT | Mark |
# | -------------- | ----- | ---------- | ---- | ---- | ---- | ------ | ---- | ---- | ---- |
# | [fun1]:Table 1 | col_1 |       19.0 | 19.0 | 19.0 | 42.9 |   29.4 | 23.5 | 11.8 | 35.3 |
# |                | col_2 |       85.7 |  9.5 |  4.8 |      |   70.6 | 17.6 |  5.9 |  5.9 |
# |                | col_3 |       23.8 | 19.0 | 14.3 | 42.9 |   23.5 | 17.6 | 11.8 | 47.1 |