Summarizing data and also showing the raw data below

52 views Asked by At

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

2

There are 2 answers

3
BENY On BEST ANSWER

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
1
Psidom On

You can summarize, then bind_rows with original data frame:

bind_df <- as.data.frame(bind)

bind_df %>% 
    group_by(vehicle) %>% 
    summarise_all(sum) %>% 
    bind_rows(bind_df) %>% 
    arrange(vehicle)

# A tibble: 7 x 3
#  vehicle no_of_visits duration
#    <dbl>        <dbl>    <dbl>
#1       1            9       68
#2       1            3       20
#3       1            2       30
#4       1            4       18
#5       2            4       35
#6       2            3       15
#7       2            1       20