1

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.

enter image description here

nicolaskruchten
  • 26,384
  • 8
  • 83
  • 101
roshan
  • 51
  • 1
  • 9
  • 1
    Can you show the desired output directly, not just its format? – Aurèle Aug 17 '17 at 13:56
  • Adding exact RpivotTable output. https://i.stack.imgur.com/y1y1I.png – roshan Aug 18 '17 at 03:39
  • Can you confirm the code you used is `rpivotTable::rpivotTable(df, rows = c("Function", "SB", "Country Region"), cols = c("+1 Function", "+1 SB", "+1 Country Region"))` and if so, edit your question to include it? – Aurèle Aug 21 '17 at 08:33

1 Answers1

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")):

enter image description here

Aurèle
  • 12,545
  • 1
  • 31
  • 49
  • thanks Aurèle this worked for sample but when i am applying it on complete data i am missing out some combination. what I want all the combination of row and column and where their is no match found in data it should give me Zero (0)......can you help me in that – roshan Aug 21 '17 at 10:36
  • Is just `foo` what you want then? – Aurèle Aug 21 '17 at 12:15
  • It does indeed. – Aurèle Aug 22 '17 at 10:41
  • Nothing. `foo` should be in your workspace already if you've run it. Just type `foo` again then press Enter at the command line to print it – Aurèle Aug 22 '17 at 11:11
  • thanks for your valuable inputs i just seen foo output it gives me frequency and now i want matrix can you help me in it – roshan Aug 22 '17 at 12:20
  • `as.matrix(foo)`? – Aurèle Aug 22 '17 at 12:22
  • i have tried as.matrix on my data it should give me same no of row and column matrix but its not giving that......any other work around? – roshan Aug 23 '17 at 11:39