CODEWITHSUNDEEP
rexcelpivot
user35131
user35131

Reputation: 1134

How to replicate a pivot table procedure from excel in R?

I have a set of data with three columns. I would like to "group by" my second column "location" on the left side. Imagine New York, London, Berlin and all the cities lined up on the left column.

I would like to "group by" the third column which is "race" but as new columns

Location | White| Black |Asian|Grand Total
New York   700   465     323   1,488
London     1000  600     200   1,800

I have this code 

Attempt<-table %>%
group_by(`Location`) %>%
summarise(n())

but it gets me this result

Location|Grand Total
New York 1,488
London   1,800

To do an example like this in excel is very easy. I would like to do this in R. It is just a count of how many times the values appear in the table.

Upvotes: 0

Views: 96

Answers (1)

Ben
Ben

Reputation: 30504

Based on your description, maybe this might be what you're looking for.

First would group_by both Location and Race to get sub-total counts.

Then you can use pivot_wider to get final desired table in wide form.

A final rowSums will get a Grand_Total (where -1 removes the Location column from the calculation).

I made up some data for illustration.

library(tidyverse)

df %>%
  group_by(Location, Race) %>%
  summarise(Total = sum(n())) %>%
  ungroup() %>%
  pivot_wider(id_cols = Location, names_from = Race, values_from = Total, values_fn = list(Total = sum), values_fill = list(Total = 0)) %>%
  mutate(Grand_Total = rowSums(.[,-1]))

Output

# A tibble: 3 x 5
  Location Black Asian White Grand_Total
  <fct>    <int> <int> <int>       <dbl>
1 Berlin       1     0     0           1
2 London       0     1     2           3
3 New York     1     0     1           2

Data

df <- data.frame(
  ID = 1:6,
  Location = c("New York", "London", "Berlin", "London", "New York", "London"),
  Race = c("White", "White", "Black", "Asian", "Black", "White")
)

Upvotes: 1

Related Questions