say I have data look's like this
vehicle <- c(1,2)
no_of_visits <- c(3,2,4,3,1)
duration <- c(20,30,18,15,20)
bind <- cbind(vehicle, no_of_visits, duration)
vehicle| no_of_visits | duration
----------------
1 | 3 | 20
------------
1 | 2 | 30
------------
1 | 4 | 18
-----------
2 | 3 | 15
-----------
2 | 1 | 20
-----------
And, here in one row i want to show the summarized values and below that i want to show those summed up values
Now, it should look like this
vehicle|no_of_visits|duration
----------------------------
1 | 9 | 68
--------
--------
1 | 3 | 20
--------
1 | 2 | 30
--------
1 | 4 | 18
-------
2 | 4 | 35
-------
-------
2 | 3 | 15
-------
2 | 1 | 2
-------
how to make it work?
Thanks
By using dplyr
library(dplyr)
bind=data.frame(bind)
target=rbind(bind,bind%>%group_by(vehicle)%>%dplyr::summarise_all(sum)
target=target[order(target$vehicle,-target$no_of_visits),]
target
vehicle no_of_visits duration
6 1 8 58
3 1 4 18
1 1 3 20
5 1 1 20
7 2 5 45
4 2 3 15
2 2 2 30
EDIT: for you additional requirement
vehicle <- c(1,2)
no_of_visits <- c(3,2,4,3,1)
duration <- c(20,30,18,15,20)
drivername <- c('a','b')
bind <- cbind(vehicle, no_of_visits, duration,drivername)
bind=data.frame(bind,stringsAsFactors = F)
bind$vehicle=as.numeric(as.character(bind$vehicle))
bind$no_of_visits=as.numeric(as.character(bind$no_of_visits))
bind$duration=as.numeric(as.character(bind$duration))
bind$drivername=(as.character(bind$drivername))
1st Option:
target=rbind(bind,data.frame(bind%>%group_by(vehicle,drivername)%>%dplyr::summarise_all(sum)))
target[order(target$vehicle,-target$no_of_visits),]
vehicle no_of_visits duration drivername
6 1 8 58 a
3 1 4 18 a
1 1 3 20 a
5 1 1 20 a
7 2 5 45 b
4 2 3 15 b
2 2 2 30 b
2nd Option
bind1=bind %>%
group_by(vehicle) %>%
summarise_each(funs(if(is.numeric(.)) sum(., na.rm = TRUE) else first(.)))
target=rbind(bind,bind1)
target[order(target$vehicle,-target$no_of_visits),]
vehicle no_of_visits duration drivername
6 1 8 58 a
3 1 4 18 a
1 1 3 20 a
5 1 1 20 a
7 2 5 45 b
4 2 3 15 b
2 2 2 30 b