I have a large data.frame in this format:
Location Crop Acres
Plot 1 Wheat 6
Plot 1 Canola 10
Plot 1 Barley 50
Plot 2 Canola 100
Plot 2 Wheat 25
Where each location may have many crops and some might only have 1. Somehow I would like to summarize and transpose the crop and acres into one location so that the new data.frame would look like
Location Crop1 Acres1 Crop2 Acres2 Crop3 Acres3
Plot 1 Wheat 6 Canola 10 Barley 50
Plot 2 Canola 100 Wheat 25 NA NA
Obviously the Crop and Acres column couldn't be the same so that's why there would be a Crop1, Acres1, Crop2, Acres2 and so on.
I've tried pivot tables but that doesn't give me the result I need or maybe I'm not using the right code.
What about something like this with tidyverse:
library(dplyr)
library(tidyr)
data %>%
# first ensure you do not have some dupes
group_by(Location, Crop) %>%
summarise(Acres = sum(Acres)) %>%
# here you add a column that give the "position" by group
group_by(Location) %>%
mutate(n_ = 1:n()) %>%
# lastly you pivot to have data from long to wide format
pivot_wider( names_from = c(n_),
values_from = c(Crop,Acres))
# A tibble: 2 x 7
# Groups: Location [2]
Location Crop_1 Crop_2 Crop_3 Acres_1 Acres_2 Acres_3
<chr> <chr> <chr> <chr> <dbl> <dbl> <dbl>
1 Plot 1 Barley Canola Wheat 50 10 6
2 Plot 2 Canola Wheat NA 100 25 NA