How to collapse rows and add tables for a group

I'm trying to prepare a table that will be analyzed spatially with GIS. The table has the list of town and the different projects that are conducted in those towns. Some towns have multiple projects, others just have one. I want to create a data frame where there's one row for each town and columns for as many projects there are in that town.

Here's what I want to do:
Take this
Town Project
A 1
A 2
A 3
B 4
B 5
C 6

Town Project1, Project2, Project3
A 1 1 3
B 4 5
C 6

Please let me know if this makes sense. Thanks

You can do this with pivot_wider() from the tidyverse. We just need to add a new variable that creates a unique sequence number for each project within each Town. This new variable will become the new column names when we pivot to wide format.

library(tidyverse)

d = data.frame(Town=rep(LETTERS[1:3], 3:1), Project=1:6) 

d %>%
  group_by(Town) %>% 
  mutate(seq=paste0("Project", 1:n())) %>% 
  pivot_wider(names_from=seq, values_from=Project)
  Town  Project1 Project2 Project3
  <fct>    <int>    <int>    <int>
1 A            1        2        3
2 B            4        5       NA
3 C            6       NA       NA
1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.