Create a Pivot Table of Two Categorical and Numerical Variables

user_1708393 0
Viewed 151

I have the following hypothetical dataframe

Region <- c("District A", "District B","District A","District A","District B")
Gender <- c("Male","Male","Female", "Male","Female")
Age <- c(20, 21, 23, 34, 22)
AmountSold <- c(50, 10, 20, 4, 12)
RegionSales <- data.frame(Region, Gender, Age, AmountSold)

I'd like to create a pivot table or a table that shows both mean of Amount sold per gender and Region as well as the mean of Age per gender and region. How do I do that in R?

dplyr pivot-table r
3 Answers

With dplyr, another option is to specify the variables in across

library(dplyr)
RegionSales %>%
    group_by(Region, Gender) %>%
    summarise(across(c(Age, AmountSold),
             ~ mean(., na.rm = TRUE), .names = "mean_{.col}"))
user_37322710

This would be my approach with the dplyr package:

library(dplyr)

RegionSales %>%
  group_by(Region, Gender) %>%
  summarize(mean_age = mean(Age), mean_amount = mean(AmountSold))

Output:

# A tibble: 4 x 4
# Groups:   Region [2]
  Region     Gender mean_age mean_amount
  <chr>      <chr>     <dbl>       <dbl>
1 District A Female       23          20
2 District A Male         27          27
3 District B Female       22          12
4 District B Male         21          10

An option that ignores NA values:

RegionSales %>%
  group_by(Region, Gender) %>%
  summarize(mean_age = mean(Age, na.rm = T),
            mean_amount = mean(AmountSold, na.rm = T))
user_70100560

A base option using aggregate could help

> aggregate(. ~ Region + Gender, RegionSales, mean)
      Region Gender Age AmountSold
1 District A Female  23         20
2 District B Female  22         12
3 District A   Male  27         27
4 District B   Male  21         10
user_121587570
Related