how to pass the value for fun2 which is in fun1

Question

I tried to show the two columns into separate like in the image but i am getting an error In rbind(df, subrow) : number of columns of result is not a multiple of vector length (arg 2)

x = structure(list(Incubatee = c("SpotSense", "Devidayal Solar Solutions "
    ), Sector = c("Health", "Energy & CleanTech"), date = structure(c(18809, 
    18840), class = "Date"), year = c(2021L, 2021L), month = structure(4:5, .Label = c("Apr", 
    "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", 
    "Feb", "Mar"), class = "factor"), qtr = structure(c(2L, 2L), .Label = c("Q1", 
    "Q2", "Q3", "Q4"), class = "factor"), year_range = c("2021-2022", 
    "2021-2022"), count = c(1, 1), type = c("Actual", "Actual"), 
        yrmo = structure(9:10, .Label = c("May-2019", "Sep-2019", 
        "Oct-2019", "Mar-2020", "May-2020", "Jul-2020", "Dec-2020", 
        "Feb-2021", "Jul-2021", "Aug-2021"), class = "factor"), qtr_yr = structure(c(9L, 
        9L), .Label = c("Q1-2019", "Q2-2019", "Q3-2019", "Q4-2020", 
        "Q1-2020", "Q2-2020", "Q3-2020", "Q4-2021", "Q2-2021"), class = "factor"), 
        m_date = c("Jul-2021-07-01", "Aug-2021-08-01")), row.names = c(NA, 
    -2L), class = "data.frame")

expand_collapse <- function(right_table){
  
  sector_list <- unique(right_table$Sector)
  df = data.frame("Sector1"=c(""),"Sector"=c(""),"Incubatee"=c(""),"Actual"=c(""))
  
  my_fun <- function(s){
    df1 = right_table[right_table$Sector==s,]
    sector1 = df1$Sector[1]
    Sector = df1$Sector[1]
    Incubatee = ""
    actual = as.character(nrow(df1))
    mainrow = c(sector1,Sector,Incubatee,actual) 
    df = rbind(df,mainrow)
    incubatee_list <<- unique(df1$Incubatee)
  }
  df = do.call(rbind,lapply(sector_list, my_fun))
  
  my_fun2 <- function(i){
    df2 = right_table[right_table$Incubatee==i,]
    sector1 = df1$Sector[1]
    Sector = ""
    Incubatee = df2$Incubatee[1]
    actual = nrow(df2)
    subrow = c(sector1,Sector,Incubatee,actual)
    df = rbind(df,subrow)
  }
  df = do.call(cbind,lapply(incubatee_list, my_fun2))
  df = df[2:nrow(df),]
  df$Actual = as.numeric(df$Actual)
  df_total = nrow(right_table)
  df = rbind(df,c("","Total","",df_total))
  return(df)
}

DT::datatable(expand_collapse(x), rownames = F,escape = FALSE,selection=list(mode="single",target="row"),options = list(pageLength = 50,scrollX = TRUE,dom = 'tp',ordering=F,columnDefs = list(list(visible=FALSE, targets=0),list(className = 'dt-left', targets = '_all'))),class='hover cell-border stripe')

kybazzi kybazzi · Accepted Answer · 2021-12-23T07:03:47

I tried going through your function but it was difficult to understand what exactly you were trying to do. Here are some inconsistencies and problems I found with the code:

You define sector_list at the beginning, but define incubatee_list using <<- in my_fun() and it's unclear why.
my_fun() and my_fun2() don't seem to actually return anything, and I wasn't sure if you meant to use <<- on the df variable in each of these functions.
my_fun2() refers to df1 which causes an error.

You likely don't need to adopt such a complicated method for producing the table in the screenshot. Here is an example of what you could do instead.

# First I'm going to add one more row to the data so that I could demonstrate 
# more clearly what will happen if there is more than one Incubatee per sector. 
# I create "Test" under the sector "Energy and CleanTech" with a count of 5.
library(tidyverse)
x = x %>% 
  bind_rows(tibble(
    Incubatee = "Test", 
    Sector = "Energy & CleanTech",
    count = 5,
    type = "Actual",
    qtr = "Q2",
    year = 2021
  ))

# A tibble: 3 x 12
  Incubatee     Sector   date        year month qtr   year_range count type  yrmo 
  <chr>         <chr>    <date>     <dbl> <fct> <chr> <chr>      <dbl> <chr> <fct>
1 "SpotSense"   Health   2021-07-01  2021 Jul   Q2    2021-2022      1 Actu~ Jul-~
2 "Devidayal S~ Energy ~ 2021-08-01  2021 Aug   Q2    2021-2022      1 Actu~ Aug-~
3 "Test"        Energy ~ NA          2021 NA    Q2    NA             5 Actu~ NA   
# ... with 2 more variables: qtr_yr <fct>, m_date <chr>

Here is a suggested pipeline using the tidyverse.

expand_collapse = function(data) {
  data %>% 
    split(.$Sector) %>% 
    map_df(function(sector_data) {
      main_row = tibble(
        Sector = sector_data$Sector[1],
        Incubatee = "",
        Actual = sum(sector_data$count)
      ) 
    
      incubatee_rows = sector_data %>% 
        group_by(Incubatee) %>% 
        summarise(
          Sector = "",
          Actual = sum(count)
        )
      
      bind_rows(main_row, incubatee_rows)
    }) %>% 
    # Create a total row
    bind_rows(tibble(
      Sector = "Total",
      Incubatee = "",
      Actual = sum(data$count)
    ))
}

To demonstrate the output:

> expand_collapse(x)
# A tibble: 6 x 3
  Sector               Incubatee                    Actual
  <chr>                <chr>                         <dbl>
1 "Energy & CleanTech" ""                                6
2 ""                   "Devidayal Solar Solutions "      1
3 ""                   "Test"                            5
4 "Health"             ""                                1
5 ""                   "SpotSense"                       1
6 "Total"              ""                                7

how to pass the value for fun2 which is in fun1

1 Answers