Calculating a sum in one table based on values in another

Question

I have a table that has different % values for days in a cycle:

I have another table that has different specific days and corresponding dates:

6/17/19       1    
6/23/19       7    
6/27/19       4    
7/01/19       7

For every pair in the second table (i.e [1,7] & [4,7]) I would like the sum of percentages from the 1st to 7th day and 4th to 7th day, respectively in the first table.

I considered joining the tables but that doesn't help with the issue of summing a column based on conditions from another column. what functions could be useful for this?

This isn't very clear. Please give a [mcve]. See [How to make a great R reproducible example?](https://stackoverflow.com/q/5963269/4996248) for what this would mean in R. — John Coleman, Jun 17 '19 at 20:47
Ah that's on me, can't share the actual data so made up on the spot. Its supposed to be a cycle of 7 days though. — boomboom p, Jun 17 '19 at 21:58

score 2 · Accepted Answer · answered Jun 17 '19 at 22:25

df1 <- read.table(text="Day Value
1    5%    
2   10%    
3   30%    
4   10%    
5   15%    
6   15%  
7   15%", header=T)

df2 <- read.table(text = "Date StartEnd
6/17/19       1    
6/23/19       7    
6/27/19       3    
7/01/19       7", header=T)


library(tidyverse)

df2 %>% 
 mutate(id = rep(1:(n()/2), each=2)) %>% 
  group_by(id) %>% 
  expand(SE=min(StartEnd):max(StartEnd)) %>% 
  left_join(df1, by=c("SE"="Day")) %>% 
  mutate(Value = as.numeric(sub("%", "", Value))) %>%
  summarise(Value = sum(Value)) %>% 
  ungroup() %>% 
  left_join({df2 %>% mutate(id = rep(1:(n()/2), each=2))},., by='id') %>% 
  select(-id)

#>      Date StartEnd Value
#> 1 6/17/19        1   100
#> 2 6/23/19        7   100
#> 3 6/27/19        3    85
#> 4 7/01/19        7    85

^{Created on 2019-06-17 by the reprex package (v0.3.0)}

Calculating a sum in one table based on values in another

1 Answers1