-1

Suppose I have a dataframe with a variable which has in it data indicating groupings. Let's say a 'level' variable which has in it 1,2, etc. indicating the level of some observations.

I would like to generate a crosstabs table of two other variables based on all groupings of level. I would like to end up with a SINGLE table such that for each level grouping we can see the crosstabs of the two other variables and also an 'overall' section of the table where the level grouping is not applied and so that section of the table shows the cross tabs without any level grouping.

How can I do this in r please?

Log On
  • 69
  • 9
  • 2
    Your description isn't very clear to me. People can provide more useful suggestion if you could provide a [minimal example dataset](https://stackoverflow.com/a/5963610/8699463) and the output dataset that you need. – Zaw Jun 29 '21 at 23:10
  • Tx for pointing that out. I did try to do a mock up of the table but the formatting gets lost when I post it. I couldn't see anyway to upload an example excel table. And because am somewhat new to using R I couldn't see how to make an eg table as I don't know how to do it. But the answer from dcarlson is what I wanted. – Log On Jun 30 '21 at 16:08

1 Answers1

2

Here is a data set that is included with R. It is a 4-dimensional array, so we need to convert it to a data frame:

data(Titanic)
Titanic.df <- as.data.frame(Titanic)
str(Titanic.df)
# 'data.frame': 32 obs. of  5 variables:
#  $ Class   : Factor w/ 4 levels "1st","2nd","3rd",..: 1 2 3 4 1 2 3 4 1 2 ...
#  $ Sex     : Factor w/ 2 levels "Male","Female": 1 1 1 1 2 2 2 2 1 1 ...
#  $ Age     : Factor w/ 2 levels "Child","Adult": 1 1 1 1 1 1 1 1 2 2 ...
#  $ Survived: Factor w/ 2 levels "No","Yes": 1 1 1 1 1 1 1 1 1 1 ...
#  $ Freq    : num  0 0 35 0 0 0 17 0 118 154 ...

Now create the table:

Survival <- xtabs(Freq~Age+Sex+Survived, Titanic)
Survival
# , , Survived = No
# 
#        Sex
# Age     Male Female
#   Child   35     17
#   Adult 1329    109
# 
# , , Survived = Yes
# 
#        Sex
# Age     Male Female
#   Child   29     28
#   Adult  338    316
# 
ftable(Survival, row.vars=c("Age", "Sex"), col.vars="Survived")
#              Survived   No  Yes
# Age   Sex                      
# Child Male              35   29
#       Female            17   28
# Adult Male            1329  338
#       Female           109  316
ftable(Survival, row.vars="Survived", col.vars=c("Age", "Sex"))
#          Age Child        Adult       
#          Sex  Male Female  Male Female
# Survived                              
# No              35     17  1329    109
# Yes             29     28   338    316
dcarlson
  • 10,936
  • 2
  • 15
  • 18
  • Thank you very much; this is the answer I was looking for. Is there a way to write this table to excel? I try to write it as .csv or using openxlsx but the row names and columns do not get written to the excel sheet. If I convert the ftable to a dataframe/table first then the row/column names are kept but the structure of the ftable is lost in excel. – Log On Jun 30 '21 at 16:05
  • 1
    If you want to export the table, your best option is to use `xtable` or one of the other table packages that can export the table as an html file. You you can read into Excel or Word and apply different styles for publication. – dcarlson Jun 30 '21 at 16:57