# Pivot Table

#### David Gerbing

    library("lessR")

`pivot()` uses base R `aggregate()` to generate a pivot table (Excel terminology). Express the categorical variables over which to pivot as a vector with the `c())` function.

`pivot()` provides two additional features than `aggregate()` provides. First is the inclusion of the sample size for each cell. Second is that the data parameter is listed first in the parameter list, which facilitates the use of the pipe operator from the magrittr package.

Presently, there is only a single variable for the aggregation, the parameter value. Variable ranges in the specification of by as not needed in general. Only a small number of grouping variables generally define the cells for the aggregation.

To illustrate, use the Employee data set included in lessR, here read into the d data frame.

    d <- Read("Employee", quiet=TRUE)

Two categorical variables are Dept and Salary. A continuous variable is Salary. Create the pivot table that expresses the mean of Salary for all combinations of Dept and Salary.

    pivot(data=d, compute=mean, value=Salary, by=c(Dept, Gender))

    ##    Dept Gender  n   Salary
    ## 1  ACCT      F  3 63237.16
    ## 2  ADMN      F  4 81434.00
    ## 3  FINC      F  1 57139.90
    ## 4  MKTG      F  5 64496.02
    ## 5  SALE      F  5 64188.25
    ## 6  ACCT      M  2 59626.20
    ## 7  ADMN      M  2 80963.35
    ## 8  FINC      M  3 72967.60
    ## 9  MKTG      M  1 99062.66
    ## 10 SALE      M 10 86150.97

The output of `pivot()` is a data frame of the aggregated variables. This can be saved for further analysis. Here, list the parameter values in order without the parameter names.

    a <- pivot(d, mean, Salary, c(Dept, Gender))

Because the output of `pivot()` is a standard R data frame, typical operations such as sorting can be applied, here using the lessR function `Sort()`. Here change the sort order.

    Sort(a, by=Dept)

    ##
    ## Sort Specification
    ##   1 -->  ascending

    ##    Dept Gender  n   Salary
    ## 1  ACCT      F  3 63237.16
    ## 6  ACCT      M  2 59626.20
    ## 2  ADMN      F  4 81434.00
    ## 7  ADMN      M  2 80963.35
    ## 3  FINC      F  1 57139.90
    ## 8  FINC      M  3 72967.60
    ## 4  MKTG      F  5 64496.02
    ## 9  MKTG      M  1 99062.66
    ## 5  SALE      F  5 64188.25
    ## 10 SALE      M 10 86150.97

Here, filter the rows so that the analysis applies only to the men in the full data set.

    pivot(d, mean, Salary, c(Dept, Gender), filter=(Gender=="M"))

    ##   Dept Gender  n   Salary
    ## 1 ACCT      M  2 59626.20
    ## 2 ADMN      M  2 80963.35
    ## 3 FINC      M  3 72967.60
    ## 4 MKTG      M  1 99062.66
    ## 5 SALE      M 10 86150.97

For two `by` categorical variables, have the option of displaying results as a two-dimensional table.

    pivot(d, mean, Salary, c(Dept, Gender), table_2d=TRUE)

    ##             F        M
    ## ACCT 63237.16 59626.20
    ## ADMN 81434.00 80963.35
    ## FINC 57139.90 72967.60
    ## MKTG 64496.02 99062.66
    ## SALE 64188.25 86150.97