Summarize and/or Transpose similar rows into columns

Question

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.

These might be what you're after: https://stackoverflow.com/questions/66643143/pivot-wider-with-pairs-of-rows and https://stackoverflow.com/questions/11322801/transpose-reshape-dataframe-without-timevar-from-long-to-wide-format — thehand0, Feb 04 '22 at 15:17

score 3 · Accepted Answer · answered Feb 04 '22 at 15:18

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

Summarize and/or Transpose similar rows into columns

1 Answers1