I want to rearrange the data so that each county is one observation and there is one column for each type of county. So the resulting dataframe would look like
county type_a type_b
1 10 5
2 3 6
I've been trying to think of different ways to do this with my limited skills and can't think of anything other than creating a subset for each type, summing them, and then manually merging together into a new dataset... but there must be a cleaner way to do this. Any ideas?
You can use tidyr::pivot_wider() function Note: Please be aware that since you are not providing sample data properly I have to make an extra effort to read it with non-conventional methods, other people might not have the patience to do so (or even I on the future) and also, providing sample data on a proper format would be a polite thing to do since you are asking others to invest their time helping you.
library(tidyverse)
# Sample data on a copy/paste friendly format
sample_df <- data.frame(
stringsAsFactors = FALSE,
county = c(1, 1, 2, 2, 2),
type = c("a", "b", "a", "b", "b"),
amount = c(10, 5, 3, 5, 1)
)
sample_df %>%
pivot_wider(id_cols = county,
names_from = type,
values_from = amount,
values_fn = list(amount = sum))
#> # A tibble: 2 x 3
#> county a b
#> <dbl> <dbl> <dbl>
#> 1 1 10 5
#> 2 2 3 6
Created on 2020-03-13 by the reprex package (v0.3.0.9001)