summing rows based on group and converting to columns


I have a data frame that's something like this

county type amount
1       a     10
1       b     5
2       a     3
2       b     5
2       b     1

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?

Hi @Erica, could you post your data frame like this?

<--- paste output of dput(head(your_data_frame, 50)) here

That might help make it easier for folks to help you.

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.


# 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)

1 Like

thanks for the reply! next time will attach data

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