Home / Questions / 120061
Accepted
Patricio Moracho
Asked: 2020-11-25 14:54:35 +0800 CST 2020-11-25 14:54:35 +0800 CST

How to rearrange a data.frame?

  • 772

I have a data.framewith a certain structure:

ucba <- data.frame(UCBAdmissions)
ucba

      Admit Gender Dept Freq
1  Admitted   Male    A  512
2  Rejected   Male    A  313
3  Admitted Female    A   89
4  Rejected Female    A   19
5  Admitted   Male    B  353
6  Rejected   Male    B  207
7  Admitted Female    B   17
8  Rejected Female    B    8
9  Admitted   Male    C  120
10 Rejected   Male    C  205
11 Admitted Female    C  202
12 Rejected Female    C  391
13 Admitted   Male    D  138
14 Rejected   Male    D  279
15 Admitted Female    D  131
16 Rejected Female    D  244
17 Admitted   Male    E   53
18 Rejected   Male    E  138
19 Admitted Female    E   94
20 Rejected Female    E  299
21 Admitted   Male    F   22
22 Rejected   Male    F  351
23 Admitted Female    F   24
24 Rejected Female    F  317

And I would like to reformulate it to the following form:

  Dept Male/Admitted Male/Rejected Female/Admitted Female/Rejected
1    A           512           313              89              19
2    B           353           207              17               8
3    C           120           205             202             391
4    D           138           279             131             244
5    E            53           138              94             299
6    F            22           351              24             317

Basically:

  • We group by department
  • We summarize in columns the values ​​of acceptance/rejection ( Admit) and gender Gender.
  • Final output should be other data.frameand column names should be self explanatory

I've researched various options ( aggregateand xtabs) which so far are not entirely convincing to me.

r

2 Answers

  1. Best Answer

    An alternative is to use functions that come in the package tidyr:

    First we join the columns Genderand Admitusing the function unite(opposite function is separate): 

    library(tidyr)
ucbau <- unite(ucba, "gender_admit", Gender, Admit, sep = "/")
ucbau
#> # A tibble: 24 x 3
#>       gender_admit   Dept  Freq
#>  *           <chr> <fctr> <dbl>
#>  1   Male/Admitted      A   512
#>  2   Male/Rejected      A   313
#>  3 Female/Admitted      A    89
#>  4 Female/Rejected      A    19
#>  5   Male/Admitted      B   353
#>  6   Male/Rejected      B   207
#>  7 Female/Admitted      B    17
#>  8 Female/Rejected      B     8
#>  9   Male/Admitted      C   120
#> 10   Male/Rejected      C   205
#> # ... with 14 more rows

    Then we spread the data frame (transform it to wide format) using the function spread(the opposite is gather):

    spread(ucbau, gender_admit, Freq)
#> # A tibble: 6 x 5
#>     Dept `Female/Admitted` `Female/Rejected` `Male/Admitted`
#> * <fctr>             <dbl>             <dbl>           <dbl>
#> 1      A                89                19             512
#> 2      B                17                 8             353
#> 3      C               202               391             120
#> 4      D               131               244             138
#> 5      E                94               299              53
#> 6      F                24               317              22
#> # ... with 1 more variables: `Male/Rejected` <dbl>

    One of the nice things about the package's functions tidyr(including those in dplyr) is that both input and output are data.frames so it's easy to chain them together. They also make the code more readable because each function is a verb.

    • 3

  2. agregate()

    A somewhat complex way to read but feasible, is to use aggregate()as follows:

    setNames(
     as.data.frame(
          sapply(
               aggregate(Freq ~ Dept, ucba, cbind)
              ,unlist
          )
     ), 
     c("Dept", c(unique(cbind(paste0(ucba$Gender,"/",ucba$Admit)))))
)

    The exit:

      Dept Male/Admitted Male/Rejected Female/Admitted Female/Rejected
1    A           512           313              89              19
2    B           353           207              17               8
3    C           120           205             202             391
4    D           138           279             131             244
5    E            53           138              94             299
6    F            22           351              24             317

    Explanation:

    • With the aggregateinitial one, we manage to group by Deptand create a column that will be a list with the values ​​of Freqof each subgroup ( Genderand Admit)
    • With sapply()we apply the function unlist()and "open" the list in columns
    • Finally we convert everything to a data.frameand set the column names to something clearer using setNames().

    xtabs()

    Much easier is to use the contingency tables through xtabs(), to achieve the output we are looking for, we can simply do:

    xtabs(Freq ~ Dept+paste0(Gender,"/",Admit), data = ucba)

    This directly generates the expected output, the problem is that it is an object of the class xtabs tableand not a data.frame, so we must convert it, but to maintain the structure of the table we must use as.data.frame.matrix():

    as.data.frame.matrix((xtabs(Freq ~ Dept+paste0(Gender,"/",Admit), data = ucba)))
    • 1