Consider a data frame that has 3 columns: A - a name; B - the yearly food intake (one name can eat different foods); C - the year in which the person stops eating that food.

Such as:
A B C
Peter 400 2035
Peter 500 2050
Peter 350 2024
John 700 2050

I need to create a time series that sums all the food intake for each person, from today (2022) to 2050. In the case of John is easy: 700 * (2050-2022). But for Peter, I need to add some restrictions: sum the 3 lines until 2024, then one of them goes to zero, but the time series keeps summing the other two lines, until eventually there is only one line to sum.

So year 2022 would be (400 500 350), the same for years 2023 to 2024. Then would be (400 500), until 2035, etc.

This allows me to have a time-serie, per person, which contains the yearly intake of food, taking into consideration that the yearly food intake will decrease throughout the years.

Thank you very much and have a great day

CodePudding user response：

Are you after the total intake over the period? Then this will calculate it:

library(tidyverse)

data <- tribble(~"A", ~"B", ~"C",
                "Peter", 400, 2035,
                "Peter", 500, 2050,
                "Peter", 350, 2024,
                "John", 700, 2050)    

data %>% 
  mutate(line_total = B*(C - 2022)) %>% # 2022 being the start year
  group_by(A) %>% 
  summarise(person_total = sum(line_total))

If you actually want a time-series, with a column for each row and the total for the row at the end, then try this:

years <- 2022:max(data$C)

mat <- matrix(nrow = nrow(data), ncol = length(years)) 
colnames(mat) <- c(years)

timeseries <- cbind(data, mat) %>% 
  as_tibble() %>% 
  pivot_longer(-c(A, B, C)) %>% 
  mutate(value = ifelse(name <= C, B, 0)) %>% 
  pivot_wider() %>% 
  select(-c(B, C)) %>% 
  mutate(rowsum = rowSums(across(where(is.numeric))))