Group by columns and sum the column

Hi,
Can someone help me figure this out.
Dataset: Data
State County Col3 Col4 Col5 Col6....Col(last)
AL........As............1........2......1........5 ....... 10
AL........Bo...........10.....12....13........4 ....... 15
NY.......Ad............2....... 5......6........ 7 ..........12
NY.......Cd............3........4......5.........7 ..........11
CA.......Ya............ 4........2......1........3 ..........9

Need Output as:
State Col3 Col4 Col5 Col6....Col(last)
AL........11.....14......14.....9 ....... 25
NY........5........9.........11....14.......23
CA.......4.......2......... 1......3 .......9

I tried using
Dt_states <- Data %>%
group_by(State, County) %>%
summarise(Data[2:10]) # summarise_each(funs(sum))

I also tried:
dt_states<-as.data.table(unique(data$State))

f <- function(x){
b<-tapply(x,Data$State, sum)
return(as.data.frame(b)) }

for (a in c(3:(length(Data[1,])))) {
z=Data[,a,drop=T]
dt_states[,colnames(Data[a])]<- f(z) }

If you want to sum all of the numeric columns, I would use summarize_if().

library(dplyr)
DF <- data.frame(State  = c("AL", "AL", "NY", "NY", "CA"),
                 County = c("As", "Bo", "Ad", "Cd", "Ya"),
                 Col3 = c(1, 10, 2,3,4),
                 Col4 = c(2, 12, 5,4,2),
                 Col5 = c(1, 13, 6,5,1))
DF %>% group_by(State) %>% 
  summarize_if(is.numeric, sum)
#> # A tibble: 3 x 4
#>   State  Col3  Col4  Col5
#>   <fct> <dbl> <dbl> <dbl>
#> 1 AL       11    14    14
#> 2 CA        4     2     1
#> 3 NY        5     9    11

Created on 2020-06-13 by the reprex package (v0.3.0)

1 Like

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

Thanks for your help