Summarising among the selected rows

user_13941288 0
Viewed 139

Hi I have a data file like this sample:

a<-tribble(
  ~s.no,~a,~b,~volume,
  1,51,52,200,
  2,52,51,500,
  3,51,58,44,
  4,58,51,22
)

What is this table ?

The rows tell the volume in each road in both the directions. Eg: s.no 1 shows volume on road 51 to 52, similarly s.no, 2, shows volume on the smae link but in reverse direction: 52 to 51.

What i want ?

I want volume on both directions to be summed up into one, eg: road 51 to 52 should have 200+500 as volume. is it possible in R ? The following is the sample illustration of what i expect as a output.

Expected output

dplyr tidyverse filter r
2 Answers

you can try

library(tidyverse)
a %>% 
  group_by(gr= a + b) %>% 
  summarise(s.no = min(s.no),
            a = first(a),
            b = first(b),
            vol = sum(volume)) 
# A tibble: 2 x 5
     gr  s.no     a     b   vol
  <dbl> <dbl> <dbl> <dbl> <dbl>
1   103     1    51    52   700
2   109     3    51    58    66

A more generalized way taking into account similar sums of a+b e.g. 51 + 52 vs. 50 + 53

a %>% 
  pivot_longer(c(a, b)) %>% 
  group_by(s.no) %>% 
  mutate(gr = toString(sort(value))) %>% 
  pivot_wider() %>% 
  group_by(gr) %>% 
  summarise(s.no = min(s.no),
            volume = sum(volume)) %>% 
  separate(gr, into = letters[1:2])
# A tibble: 2 x 4
  a     b      s.no volume
  <chr> <chr> <dbl>  <dbl>
1 51    52        1    700
2 51    58        3     66
user_48706950

Here is an idea where we create groups for 2 rows at a time, i.e.

library(dplyr)

a %>% 
 group_by(grp = rep(seq(n() / 2), each = 2)) %>% 
 mutate(volume = sum(volume)) %>% 
 slice(1L) %>% 
 ungroup() %>% 
 select(-grp)

# A tibble: 2 x 4
#   s.no     a     b volume
#  <dbl> <dbl> <dbl>  <dbl>
#1     1    51    52    700
#2     3    51    58     66
user_56355800
Related