I have a dataset that looks like this:

      id      a        b      c      sum
      1      state     yes    1       0
      1      state     yes    1       234
      1      state     yes    1       45
      2      county     no    4       456
      2      county     no    4       55

For each id, the values in a, b and c are the same. I want to reshape to long such that sum in the sum of all values in that id number:

       id      a        b      c      sum
       1      state     yes    1       279
       2      county    no     4       511

Not sure how to go about this

CodePudding user response：

I thinks this should work:

# loading tidyverse packages
library(tidyverse)

# making reprex dataframe
df = data.frame(id = c(1,1,1,2,2),
           a = c("state","state","state","county","county"),
           b = c("yes","yes","yes","no","no"),
           c = c(1,1,1,4,4),
           sum = c(0, 234, 45, 456, 55))

# grouping dataframe by variables of interest and calculating sum
df %>%
  group_by(id, a, b, c) %>%
  summarise(total_sum = sum(sum))

#> # A tibble: 2 x 5
#> # Groups:   id, a, b [2]
#>      id a      b         c total_sum
#>   <dbl> <chr>  <chr> <dbl>     <dbl>
#> 1     1 state  yes       1       279
#> 2     2 county no        4       511


Created on 2022-08-07 by the reprex package (v2.0.1)