create OLAP cube in R programming language

问题:

Hi I have following data

  Function    SB `Country Region` `+1 Function` `+1 SB` `+1 Country Region`
     <chr> <chr>            <chr>         <chr>   <chr>               <chr>
1      ENG  SB10             AMER           ENG    SB10                AMER
2       IT  SB07             EMEA            IT    SB07                EMEA
3      QLT  SB05             EMEA           QLT    SB05                EMEA
4      MFG  SB07             EMEA           MFG    SB07                EMEA
5      MFG  SB04             EMEA           MFG    SB05                EMEA
6      SCM  SB08             EMEA           SCM    SB08                EMEA

i want to create 3 dimensional OLAP cube in which column Function SB Country Region should be in row and +1 Function,+1 SB, +1 Country Region should be in column .

output should be of following format

                   `+1 Function` `+1 SB` `+1 Country Region`  
Function
SB 
Country Region

thank you

Adding exact RpivotTable output.

回答1:

You could work with ftable() for instance:

df <- read.table(text =
"  Function    SB 'Country Region' '+1 Function' '+1 SB' '+1 Country Region'
       ENG  SB10             AMER           ENG    SB10                AMER
        IT  SB07             EMEA            IT    SB07                EMEA
       QLT  SB05             EMEA           QLT    SB05                EMEA
       MFG  SB07             EMEA           MFG    SB07                EMEA
       MFG  SB04             EMEA           MFG    SB05                EMEA
       SCM  SB08             EMEA           SCM    SB08                EMEA",
  stringsAsFactors = FALSE, header = TRUE, check.names = FALSE)

foo <- ftable(df, row.vars = c(3, 1, 2), col.vars = c(6, 4, 5))

as.matrix(foo)[apply(foo, 1, function(x) any(x != 0)), apply(foo, 2, function(x) any(x != 0))]
# Or simply:
as.matrix(foo)[apply(foo, 1, any), apply(foo, 2, any)]

#                           +1 Country Region_+1 Function_+1 SB
# Country Region_Function_SB AMER_ENG_SB10 EMEA_IT_SB07 EMEA_MFG_SB05 EMEA_MFG_SB07 EMEA_QLT_SB05 EMEA_SCM_SB08
#              AMER_ENG_SB10             1            0             0             0             0             0
#              EMEA_IT_SB07              0            1             0             0             0             0
#              EMEA_MFG_SB04             0            0             1             0             0             0
#              EMEA_MFG_SB07             0            0             0             1             0             0
#              EMEA_QLT_SB05             0            0             0             0             1             0
#              EMEA_SCM_SB08             0            0             0             0             0             1

Comparison with rpivotTable::rpivotTable(df, rows = c("Country Region", "Function", "SB"), cols = c("+1 Country Region", "+1 Function", "+1 SB")):