Home > front end >  Why table() function is giving me long data instead of wide?
Why table() function is giving me long data instead of wide?

Time:03-21

I'm having trouble getting my data set to work with the table() function. My data originally looks something like this:

Student_Ethnicity Pre_DAS Ethnicity
White Black
White White
Asian Black
Black White
White Black

But, I want it to tabulate like this:

Student_Ethnicity White Black Asian
White 1 2 0
Black 1 0 0
Asian 0 1 0

I previously ran this code:

PreDASEthnicityPredictor <- table(Predict_DAS$Student_Ethnicity, Predict_DAS$PreDAS_Ethnicity)

But, when the data tabulated it looked like this:

Student_Ethnicity Pre_DAS Ethnicity Frq
White White 1
White Black 2
White Asian 0
Black White 1
Black Black 0
Black Asian 0
Asian White 0
Asian Black 1
Asian Asian 0

Is there a specific reason this could be happening and how can I make this tabulate correctly? It is important to get this table format for a chi-squared analysis I would like to do on this later.

Here is the actual dataset I am working with:

structure(list(Predict_DAS.Student_Ethnicity = c("White/Caucasian", 
"White/Caucasian", "White/Caucasian", "White/Caucasian", "Other", 
"White/Caucasian", "White/Caucasian", "Multiple", "White/Caucasian", 
"White/Caucasian", "Black/African American", "White/Caucasian", 
"White/Caucasian", "Black/African American", "Hispanic/Latinx", 
"Black/African American", "White/Caucasian", "Other", "Other", 
"Hispanic/Latinx", "Hispanic/Latinx", "Other", "Native Hawaiian or Pacific Islander", 
"Black/African American", "Black/African American", "Hispanic/Latinx", 
"White/Caucasian", "White/Caucasian", "White/Caucasian", "Black/African American", 
"Black/African American", "White/Caucasian", "Black/African American", 
"Hispanic/Latinx", "Black/African American", "White/Caucasian", 
"Black/African American", "White/Caucasian", "Black/African American", 
"White/Caucasian", "White/Caucasian", "Other", "Native Hawaiian or Pacific Islander", 
"Black/African American", "White/Caucasian", "Other", "Hispanic/Latinx", 
"No Selection", "White/Caucasian", "American Indian or Alaskan Native", 
"Hispanic/Latinx", "White/Caucasian", "Hispanic/Latinx", "Black/African American", 
"American Indian or Alaskan Native", "White/Caucasian", "White/Caucasian", 
"Multiple", "White/Caucasian", "American Indian or Alaskan Native", 
"No Selection", "Asian", "White/Caucasian", "Black/African American", 
"Native Hawaiian or Pacific Islander", "Native Hawaiian or Pacific Islander", 
"Hispanic/Latinx", "American Indian or Alaskan Native", "No Selection", 
"Asian", "Black/African American", "Black/African American", 
"Black/African American", "White/Caucasian", "American Indian or Alaskan Native", 
"Black/African American", "Black/African American", "White/Caucasian", 
"Black/African American", "Black/African American", "Multiple", 
"White/Caucasian", "Hispanic/Latinx", "White/Caucasian", "Asian", 
"Multiple", "White/Caucasian", "White/Caucasian", "Black/African American", 
"White/Caucasian", "No Selection", "White/Caucasian", "White/Caucasian", 
"Multiple", "Black/African American", "White/Caucasian", "White/Caucasian", 
"Black/African American", "Other", "Black/African American", 
"Multiple", "Black/African American", "Hispanic/Latinx", "White/Caucasian", 
"White/Caucasian", "White/Caucasian", "Black/African American", 
"White/Caucasian", "White/Caucasian", "White/Caucasian", "Black/African American", 
"Black/African American", "Hispanic/Latinx", "Multiple", "Black/African American", 
"Black/African American", "Asian", "White/Caucasian", "White/Caucasian", 
"Black/African American", "White/Caucasian", "Black/African American", 
"White/Caucasian", "Other", "Multiple", "Multiple", "Multiple", 
"No Selection", "Asian", "No Selection", "White/Caucasian", "White/Caucasian", 
"No Selection", "Other", "White/Caucasian", "American Indian or Alaskan Native", 
"White/Caucasian", "Hispanic/Latinx", "Multiple", "Hispanic/Latinx"
), Predict_DAS.PreDAS_Ethnicity = c("White/Caucasian", "White/Caucasian", 
"Asian", "White/Caucasian", "Other", "No Selection", "White/Caucasian", 
"No Selection", "No Selection", "White/Caucasian", "Black/African American", 
"White/Caucasian", "No Selection", "No Selection", "Hispanic/Latinx", 
"Black/African American", "No Selection", "Asian", "Other", "No Selection", 
"No Selection", "No Selection", "White/Caucasian", "No Selection", 
"No Selection", "Hispanic/Latinx", "No Selection", "White/Caucasian", 
"No Selection", "Black/African American", "Black/African American", 
"White/Caucasian", "White/Caucasian", "Hispanic/Latinx", "No Selection", 
"Hispanic/Latinx", "No Selection", "White/Caucasian", "Black/African American", 
"White/Caucasian", "White/Caucasian", "No Selection", "Asian", 
"White/Caucasian", "White/Caucasian", "No Selection", "Hispanic/Latinx", 
"Other", "White/Caucasian", "Black/African American", "No Selection", 
"White/Caucasian", "No Selection", "Black/African American", 
"No Selection", "White/Caucasian", "Asian", "Multiple", "White/Caucasian", 
"White/Caucasian", "No Selection", "No Selection", "No Selection", 
"Black/African American", "No Selection", "No Selection", "No Selection", 
"White/Caucasian", "Black/African American", "Other", "Black/African American", 
"No Selection", "Black/African American", "White/Caucasian", 
"No Selection", "Black/African American", "No Selection", "No Selection", 
"No Selection", "Black/African American", "Multiple", "Black/African American", 
"Hispanic/Latinx", "Black/African American", "Asian", "Multiple", 
"White/Caucasian", "White/Caucasian", "Black/African American", 
"White/Caucasian", "No Selection", "No Selection", "White/Caucasian", 
"No Selection", "Asian", "No Selection", "White/Caucasian", "Black/African American", 
"No Selection", "Black/African American", "Black/African American", 
"Black/African American", "No Selection", "White/Caucasian", 
"No Selection", "White/Caucasian", "Black/African American", 
"White/Caucasian", "No Selection", "White/Caucasian", "No Selection", 
"Black/African American", "No Selection", "Other", "No Selection", 
"No Selection", "No Selection", "White/Caucasian", "White/Caucasian", 
"Black/African American", "White/Caucasian", "Black/African American", 
"White/Caucasian", "Other", "White/Caucasian", "White/Caucasian", 
"No Selection", "No Selection", "White/Caucasian", "No Selection", 
"White/Caucasian", "Black/African American", "Black/African American", 
"Black/African American", "Other", "No Selection", "White/Caucasian", 
"Black/African American", "No Selection", "No Selection")), class = "data.frame", row.names = c(NA, 
-140L))

CodePudding user response:

Assuming your data set is called df, you could do the following with the tidyverse packages:

library(tidyverse)

result <- df %>% 
  count(Predict_DAS.Student_Ethnicity, Predict_DAS.PreDAS_Ethnicity) %>% 
  pivot_wider(names_from = Predict_DAS.PreDAS_Ethnicity, values_from = n, values_fill = 0)

  Predict_DAS.Student_Ethnicity   `Black/African…` `No Selection` `White/Caucasi…` Asian Other
  <chr>                                      <int>          <int>            <int> <int> <int>
1 American Indian or Alaskan Nat…                1              3                2     0     0
2 Asian                                          0              2                1     1     1
3 Black/African American                        19             11                2     1     0
4 Hispanic/Latinx                                1              8                0     0     0
5 Multiple                                       1              4                2     0     1
6 Native Hawaiian or Pacific Isl…                0              2                1     1     0
7 No Selection                                   2              4                0     0     1
8 Other                                          1              4                0     1     3
9 White/Caucasian                                3             12               32     2     1
# … with 2 more variables: `Hispanic/Latinx` <int>, Multiple <int>

CodePudding user response:

I suspect the problem is that the 'levels' of your factor (PreDAS_Ethnicity) don't match your expected outcome. Here is a potential solution specifying the levels before 'table()ing' the data:

Predict_DAS <- read.table(text = "Student_Ethnicity PreDAS_Ethnicity
White   Black
White   White
Asian   Black
Black   White
White   Black", header = TRUE)

Predict_DAS$Student_Ethnicity <- factor(Predict_DAS$Student_Ethnicity, levels = unique(Predict_DAS$Student_Ethnicity))
Predict_DAS$PreDAS_Ethnicity <- factor(Predict_DAS$PreDAS_Ethnicity, levels = unique(Predict_DAS$Student_Ethnicity))

PreDASEthnicityPredictor <- table(Predict_DAS$Student_Ethnicity, Predict_DAS$PreDAS_Ethnicity)
PreDASEthnicityPredictor
#>        
#>         White Asian Black
#>   White     1     0     2
#>   Asian     0     0     1
#>   Black     1     0     0


# With your example data:
df <- structure(list(Predict_DAS.Student_Ethnicity = c("White/Caucasian", 
                                                       "White/Caucasian", "White/Caucasian", "White/Caucasian", "Other", 
                                                       "White/Caucasian", "White/Caucasian", "Multiple", "White/Caucasian", 
                                                       "White/Caucasian", "Black/African American", "White/Caucasian", 
                                                       "White/Caucasian", "Black/African American", "Hispanic/Latinx", 
                                                       "Black/African American", "White/Caucasian", "Other", "Other", 
                                                       "Hispanic/Latinx", "Hispanic/Latinx", "Other", "Native Hawaiian or Pacific Islander", 
                                                       "Black/African American", "Black/African American", "Hispanic/Latinx", 
                                                       "White/Caucasian", "White/Caucasian", "White/Caucasian", "Black/African American", 
                                                       "Black/African American", "White/Caucasian", "Black/African American", 
                                                       "Hispanic/Latinx", "Black/African American", "White/Caucasian", 
                                                       "Black/African American", "White/Caucasian", "Black/African American", 
                                                       "White/Caucasian", "White/Caucasian", "Other", "Native Hawaiian or Pacific Islander", 
                                                       "Black/African American", "White/Caucasian", "Other", "Hispanic/Latinx", 
                                                       "No Selection", "White/Caucasian", "American Indian or Alaskan Native", 
                                                       "Hispanic/Latinx", "White/Caucasian", "Hispanic/Latinx", "Black/African American", 
                                                       "American Indian or Alaskan Native", "White/Caucasian", "White/Caucasian", 
                                                       "Multiple", "White/Caucasian", "American Indian or Alaskan Native", 
                                                       "No Selection", "Asian", "White/Caucasian", "Black/African American", 
                                                       "Native Hawaiian or Pacific Islander", "Native Hawaiian or Pacific Islander", 
                                                       "Hispanic/Latinx", "American Indian or Alaskan Native", "No Selection", 
                                                       "Asian", "Black/African American", "Black/African American", 
                                                       "Black/African American", "White/Caucasian", "American Indian or Alaskan Native", 
                                                       "Black/African American", "Black/African American", "White/Caucasian", 
                                                       "Black/African American", "Black/African American", "Multiple", 
                                                       "White/Caucasian", "Hispanic/Latinx", "White/Caucasian", "Asian", 
                                                       "Multiple", "White/Caucasian", "White/Caucasian", "Black/African American", 
                                                       "White/Caucasian", "No Selection", "White/Caucasian", "White/Caucasian", 
                                                       "Multiple", "Black/African American", "White/Caucasian", "White/Caucasian", 
                                                       "Black/African American", "Other", "Black/African American", 
                                                       "Multiple", "Black/African American", "Hispanic/Latinx", "White/Caucasian", 
                                                       "White/Caucasian", "White/Caucasian", "Black/African American", 
                                                       "White/Caucasian", "White/Caucasian", "White/Caucasian", "Black/African American", 
                                                       "Black/African American", "Hispanic/Latinx", "Multiple", "Black/African American", 
                                                       "Black/African American", "Asian", "White/Caucasian", "White/Caucasian", 
                                                       "Black/African American", "White/Caucasian", "Black/African American", 
                                                       "White/Caucasian", "Other", "Multiple", "Multiple", "Multiple", 
                                                       "No Selection", "Asian", "No Selection", "White/Caucasian", "White/Caucasian", 
                                                       "No Selection", "Other", "White/Caucasian", "American Indian or Alaskan Native", 
                                                       "White/Caucasian", "Hispanic/Latinx", "Multiple", "Hispanic/Latinx"
), Predict_DAS.PreDAS_Ethnicity = c("White/Caucasian", "White/Caucasian", 
                                    "Asian", "White/Caucasian", "Other", "No Selection", "White/Caucasian", 
                                    "No Selection", "No Selection", "White/Caucasian", "Black/African American", 
                                    "White/Caucasian", "No Selection", "No Selection", "Hispanic/Latinx", 
                                    "Black/African American", "No Selection", "Asian", "Other", "No Selection", 
                                    "No Selection", "No Selection", "White/Caucasian", "No Selection", 
                                    "No Selection", "Hispanic/Latinx", "No Selection", "White/Caucasian", 
                                    "No Selection", "Black/African American", "Black/African American", 
                                    "White/Caucasian", "White/Caucasian", "Hispanic/Latinx", "No Selection", 
                                    "Hispanic/Latinx", "No Selection", "White/Caucasian", "Black/African American", 
                                    "White/Caucasian", "White/Caucasian", "No Selection", "Asian", 
                                    "White/Caucasian", "White/Caucasian", "No Selection", "Hispanic/Latinx", 
                                    "Other", "White/Caucasian", "Black/African American", "No Selection", 
                                    "White/Caucasian", "No Selection", "Black/African American", 
                                    "No Selection", "White/Caucasian", "Asian", "Multiple", "White/Caucasian", 
                                    "White/Caucasian", "No Selection", "No Selection", "No Selection", 
                                    "Black/African American", "No Selection", "No Selection", "No Selection", 
                                    "White/Caucasian", "Black/African American", "Other", "Black/African American", 
                                    "No Selection", "Black/African American", "White/Caucasian", 
                                    "No Selection", "Black/African American", "No Selection", "No Selection", 
                                    "No Selection", "Black/African American", "Multiple", "Black/African American", 
                                    "Hispanic/Latinx", "Black/African American", "Asian", "Multiple", 
                                    "White/Caucasian", "White/Caucasian", "Black/African American", 
                                    "White/Caucasian", "No Selection", "No Selection", "White/Caucasian", 
                                    "No Selection", "Asian", "No Selection", "White/Caucasian", "Black/African American", 
                                    "No Selection", "Black/African American", "Black/African American", 
                                    "Black/African American", "No Selection", "White/Caucasian", 
                                    "No Selection", "White/Caucasian", "Black/African American", 
                                    "White/Caucasian", "No Selection", "White/Caucasian", "No Selection", 
                                    "Black/African American", "No Selection", "Other", "No Selection", 
                                    "No Selection", "No Selection", "White/Caucasian", "White/Caucasian", 
                                    "Black/African American", "White/Caucasian", "Black/African American", 
                                    "White/Caucasian", "Other", "White/Caucasian", "White/Caucasian", 
                                    "No Selection", "No Selection", "White/Caucasian", "No Selection", 
                                    "White/Caucasian", "Black/African American", "Black/African American", 
                                    "Black/African American", "Other", "No Selection", "White/Caucasian", 
                                    "Black/African American", "No Selection", "No Selection")), class = "data.frame", row.names = c(NA, 
                                                                                                                                    -140L))

df$Predict_DAS.Student_Ethnicity <- factor(df$Predict_DAS.Student_Ethnicity, levels = unique(df$Predict_DAS.Student_Ethnicity))
df$Predict_DAS.PreDAS_Ethnicity <- factor(df$Predict_DAS.PreDAS_Ethnicity, levels = unique(df$Predict_DAS.Student_Ethnicity))

PreDASEthnicityPredictor <- table(df$Predict_DAS.Student_Ethnicity,
                                  df$Predict_DAS.PreDAS_Ethnicity)
PreDASEthnicityPredictor
#>                                      
#>                                       White/Caucasian Other Multiple
#>   White/Caucasian                                  32     1        0
#>   Other                                             0     3        0
#>   Multiple                                          2     1        3
#>   Black/African American                            2     0        0
#>   Hispanic/Latinx                                   0     0        0
#>   Native Hawaiian or Pacific Islander               1     0        0
#>   No Selection                                      0     1        0
#>   American Indian or Alaskan Native                 2     0        0
#>   Asian                                             1     1        0
#>                                      
#>                                       Black/African American Hispanic/Latinx
#>   White/Caucasian                                          3               1
#>   Other                                                    1               0
#>   Multiple                                                 1               0
#>   Black/African American                                  19               0
#>   Hispanic/Latinx                                          1               5
#>   Native Hawaiian or Pacific Islander                      0               0
#>   No Selection                                             2               0
#>   American Indian or Alaskan Native                        1               0
#>   Asian                                                    0               0
#>                                      
#>                                       Native Hawaiian or Pacific Islander
#>   White/Caucasian                                                       0
#>   Other                                                                 0
#>   Multiple                                                              0
#>   Black/African American                                                0
#>   Hispanic/Latinx                                                       0
#>   Native Hawaiian or Pacific Islander                                   0
#>   No Selection                                                          0
#>   American Indian or Alaskan Native                                     0
#>   Asian                                                                 0
#>                                      
#>                                       No Selection
#>   White/Caucasian                               12
#>   Other                                          4
#>   Multiple                                       4
#>   Black/African American                        11
#>   Hispanic/Latinx                                8
#>   Native Hawaiian or Pacific Islander            2
#>   No Selection                                   4
#>   American Indian or Alaskan Native              3
#>   Asian                                          2
#>                                      
#>                                       American Indian or Alaskan Native Asian
#>   White/Caucasian                                                     0     2
#>   Other                                                               0     1
#>   Multiple                                                            0     0
#>   Black/African American                                              0     1
#>   Hispanic/Latinx                                                     0     0
#>   Native Hawaiian or Pacific Islander                                 0     1
#>   No Selection                                                        0     0
#>   American Indian or Alaskan Native                                   0     0
#>   Asian                                                               0     1

Created on 2022-03-21 by the reprex package (v2.0.1)

  • Related