CODEWITHSUNDEEP
rcastingpivot-table
Cami Hurtado
Cami Hurtado

Reputation: 27

Making a pivot table with multiple columns in R

I have a table called "Trends" that looks something like this

Project   Area      Animal     Trend
A        Canada     Bird       1.5
A        USA        Mammal     -0.6
A        Mexico     Bird       -2.5
B        Mexico     Bird       0.7
B        Canada     Reptile    2.5
B        Canada     Bird       -0.3

What I want is to make a summary table that will look something like this:

Animal     Area       ATrend      BTrend
  Bird     Canada      1.5          -0.3
  Bird     Mexico      -2.5         0.7
  Mammal    USA        -0.6
  Reptile   Canada                  2.5

I have 4 different projects, over 20 different areas and over 200 different animals, so need to find code that can adapt to all of it without specifying each grouping.

I was trying to use cast function to make a pivot table but I'm struggling to add more than one column (i.e. Animal and Area). What I tried was: 

cast(Trends, Animal~Project)

But this only gave me the count of how many animals were recorded in each project, and obviously did not take into account the different areas. I am very new to R and don't know much, so I would greatly appreciate a detailed answer for a newbie :)

Upvotes: 1

Views: 792

Answers (1)

akrun
akrun

Reputation: 887851

An option would be pivot_wider

library(tidyr)
library(dplyr)
library(stringr)
Trends %>% 
    mutate(Project = str_c("Trend", Project)) %>%
    pivot_wider(names_from = Project, values_from = Trend)
# A tibble: 4 x 4
#  Area   Animal  TrendA TrendB
#  <chr>  <chr>    <dbl>  <dbl>
#1 Canada Bird       1.5   -0.3
#2 USA    Mammal    -0.6   NA  
#3 Mexico Bird      -2.5    0.7
#4 Canada Reptile   NA      2.5

data

Trends <- structure(list(Project = c("A", "A", "A", "B", "B", "B"), Area = c("Canada", 
"USA", "Mexico", "Mexico", "Canada", "Canada"), Animal = c("Bird", 
"Mammal", "Bird", "Bird", "Reptile", "Bird"), Trend = c(1.5, 
-0.6, -2.5, 0.7, 2.5, -0.3)), class = "data.frame", row.names = c(NA, 
-6L))

Upvotes: 2

Related Questions