how to add some value of columns with respect of 3 groups?

[sherek]

I have 3 column : SAMPN,PERNO, loop. and utilities corresponding to 3 modes. carobs and walk
I want to add utility of rows whose have the same SAMPN,PERNO, loop. for car.car, bus.bus, walk.walk, walk.bus and bus.walk

example

   SAMPN PERNO  PLANO loop      walk               car               bus            MODE1
  <chr>   <fct> <fct> <fct> <chr>              <chr>             <chr>              <fct>
1 "    4" 1     " 2"  2     -0.990765697239748 2.09989661853416  -0.92177603128108  2    
2 "    4" 1     " 7"  2     0.11385013993979   1.09436996098927  -0.534987482042767 2    
3 "    4" 2     " 2"  2     0.500507525721786  0.924888419124695 -0.376370439308976 2    
4 "    4" 2     " 7"  2     -0.299078042202768 1.54226436622111  -0.289562610169849 2    
5 "    6" 1     " 2"  2     -0.991897610390741 1.58114646818508  -0.973443199067661 2    
6 "    6" 1     " 3"  2     -1.05376527366975  1.61719511863015  -0.832468269682489 2 

in SAMPN 4, 2 first row have same SAMPN , PERNO and loop so I will add the crossponding utility of these 2 rows.

car.car 2.09989661853416+1.09436996098927   = 3.194267
bus.bus -0.92177603128108+-0.534987482042767 =-1.456764
walk.walk -0.990765697239748+0.11385013993979=-0.8769156
walk.bus -0.92177603128108+-0.534987482042767=-1.456764
bus.walk -0.92177603128108+ 0.11385013993979 =-0.8079259

same for others.

I know if I wanted to add the value in each row I could use this: but how to add utility in different rows?

kl<-r %>%
+   group_by(SAMPN, PERNO,loop) %>%
+   mutate(car.car = car+car, walk.walk=walk+walk, bus.bus=bus+bus, walk.bus=walk+bus, bus.walk=bus+walk)

data:

structure(list(SAMPN = c("    4", "    4", "    4", "    4", 
"    6", "    6"), PERNO = structure(c(1L, 1L, 2L, 2L, 1L, 1L
), .Label = c("1", "2", "3", "4", "5", "6", "7"), class = "factor"), 
    PLANO = structure(c(1L, 6L, 1L, 6L, 1L, 2L), .Label = c(" 2", 
    " 3", " 4", " 5", " 6", " 7", " 8", " 9", "10", "11", "12", 
    "13", "14", "15", "16", "17", "18", "19", "20", "21", "23", 
    "24"), class = "factor"), loop = structure(c(2L, 2L, 2L, 
    2L, 2L, 2L), .Label = c("1", "2", "3", "4", "5", "6", "7", 
    "8"), class = "factor"), walk = c("-0.990765697239748", "0.11385013993979", 
    "0.500507525721786", "-0.299078042202768", "-0.991897610390741", 
    "-1.05376527366975"), car = c("2.09989661853416", "1.09436996098927", 
    "0.924888419124695", "1.54226436622111", "1.58114646818508", 
    "1.61719511863015"), bus = c("-0.92177603128108", "-0.534987482042767", 
    "-0.376370439308976", "-0.289562610169849", "-0.973443199067661", 
    "-0.832468269682489"), MODE1 = structure(c(2L, 2L, 2L, 2L, 
    2L, 2L), .Label = c("1", "2", "3", "4"), class = "factor")), row.names = c(NA, 
-6L), groups = structure(list(SAMPN = c("    4", "    4", "    6"
), PERNO = structure(c(1L, 2L, 1L), .Label = c("1", "2", "3", 
"4", "5", "6", "7"), class = "factor"), loop = structure(c(2L, 
2L, 2L), .Label = c("1", "2", "3", "4", "5", "6", "7", "8"), class = "factor"), 
    .rows = list(1:2, 3:4, 5:6)), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

output for just 2 first rows:

 SAMPN PERNO  PLANO loop      car.car      bus.bus  walk.walk    walk.bus  bus.walk  MODE1
 
1 "    4" 1     " 2"  2    3.194267    -1.456764    -0.8769156  -1.456764  -0.8079259   2
2 "    4" 1     " 7"  2    3.194267    -1.456764    -0.8769156  -1.456764  -0.8079259   2

I know one solution is to bring rows with same SAMPN, PERNO, loop next to each other then use mutate . but I am looking for simpler solution –

[pieterjanvc]

Hi,

I'm not sure if I understand your question correctly, and my answer does use mutate, but I'll share it anyway in case it might help:

#I saved you dataset above in the variable myData

library(dplyr)

myData = myData %>% 
mutate(car = as.numeric(car), bus = as.numeric(bus), walk = as.numeric(walk)) %>% 
  group_by(SAMPN, PERNO, loop) %>% 
  mutate(car.car = sum(car), bus.bus = sum(bus),  
              walk.walk = sum(walk), walk.bus = walk + bus,
              bus.walk = bus + walk)

myData
# A tibble: 6 x 13
# Groups:   SAMPN, PERNO, loop [3]
  SAMPN   PERNO PLANO loop    walk   car    bus MODE1 car.car bus.bus walk.walk walk.bus bus.walk
  <chr>   <fct> <fct> <fct>  <dbl> <dbl>  <dbl> <fct>   <dbl>   <dbl>     <dbl>    <dbl>    <dbl>
1 "    4" 1     " 2"  2     -0.991 2.10  -0.922 2        3.19  -1.46     -0.877   -1.91    -1.91 
2 "    4" 1     " 7"  2      0.114 1.09  -0.535 2        3.19  -1.46     -0.877   -0.421   -0.421
3 "    4" 2     " 2"  2      0.501 0.925 -0.376 2        2.47  -0.666     0.201    0.124    0.124
4 "    4" 2     " 7"  2     -0.299 1.54  -0.290 2        2.47  -0.666     0.201   -0.589   -0.589
5 "    6" 1     " 2"  2     -0.992 1.58  -0.973 2        3.20  -1.81     -2.05    -1.97    -1.97 
6 "    6" 1     " 3"  2     -1.05  1.62  -0.832 2        3.20  -1.81     -2.05    -1.89    -1.89

I had to convert the variables bus, car and walk first to numeric before could group them and add them up as numbers

Hope this helps,
PJ