How to time different numbers to different columns?

[llyvslly]

I wonder how to time 3 different numbers to columns period1, period2, and period 3. Those 3 different numbers are the inflation rates for different years. Since I've used the "spread" to make the year horizontal, I'm not pretty sure how to time the different inflation to different year's data. Specifically, I want to time inflation to column 3,4 and 5.

Hope someone can help me on that. Many thanks!

periods <- data.frame(p = c(1:3), start = c(as.Date('2020-01-01', '%Y-%m-%d'), as.Date('2021-01-01', '%Y-%m-%d'),as.Date('2022-01-01', '%Y-%m-%d')),
                      end = c(as.Date('2020-12-31', '%Y-%m-%d'), as.Date('2021-12-31', '%Y-%m-%d'),as.Date('2022-12-31', '%Y-%m-%d')))

example<-data.frame("Provider" = c("a", "b", "c", "c", "b", "a"), "Network" = c("50k", "45k", "40k", "40k", "45k", "50k"), 
                    "AWP" = c(500, 1000, 1500, 2000, 2500, 3000), "Claim" = c(100, 150, 200, 250, 300, 350), stringsAsFactors = FALSE)

f<-example %>% 
  group_by(Provider, Network) %>% summarize(total_AWP = sum(AWP)) %>% as.data.frame()
#> Error in example %>% group_by(Provider, Network) %>% summarize(total_AWP = sum(AWP)) %>% : could not find function "%>%"

g<-merge(periods, f) %>% 
  mutate(new_AWP=total_AWP*10) %>% 
  group_by(p, start,end,Provider, Network) %>% 
  summarize(new_awp=sum(new_AWP)) %>% 
  as.data.frame() 

PLAN_AWP<- g %>% 
  rowwise() %>%
  mutate(dt_range = paste0('Period ' , as.character(p), '. ', format(start, '%m/%d/%Y'), '-', format(end, '%m/%d/%Y'))) %>%
  as.data.frame() %>%
  select(Provider, Network,dt_range,new_awp) %>%
  spread(key = dt_range,value=new_awp,fill = FALSE) 
View(PLAN_AWP)

^{Created on 2020-07-21 by the reprex package (v0.3.0)}

[mrmallironmaker]

I'm not sure I've understood the question, but I'll give it a try.

Do you want to multiply the values of, say, g$new_awp by some factor that accounts for inflation? (I don't know what AWP is, so I'm guessing that's the column.)

The way I would do this would be to add another value to the 'periods' table - as I would assume inflation is some factor that is specified by each period - and then divide by it later on down the line.

Did I identify the right question? And did this solution work for you?

periods <- data.frame(p = c(1:3), start = c(as.Date('2020-01-01', '%Y-%m-%d'), as.Date('2021-01-01', '%Y-%m-%d'),as.Date('2022-01-01', '%Y-%m-%d')),
                      end = c(as.Date('2020-12-31', '%Y-%m-%d'), as.Date('2021-12-31', '%Y-%m-%d'),as.Date('2022-12-31', '%Y-%m-%d')), 
                      # added an inflation_factor column to this table
                      inflation_factor = c(1.00, 1.02, 1.05))

example<-data.frame("Provider" = c("a", "b", "c", "c", "b", "a"), "Network" = c("50k", "45k", "40k", "40k", "45k", "50k"), 
                    "AWP" = c(500, 1000, 1500, 2000, 2500, 3000), "Claim" = c(100, 150, 200, 250, 300, 350), stringsAsFactors = FALSE)

f<-example %>% 
  group_by(Provider, Network) %>% summarize(total_AWP = sum(AWP)) %>% as.data.frame()

g<-merge(periods, f) %>% 
  mutate(new_AWP=total_AWP*10) %>% 
  group_by(p, start,end,Provider, Network) %>% 
  # updated the computation for new_awp here, accounting for inflation
  summarize(new_awp=sum(new_AWP) / inflation_factor) %>% 
  as.data.frame() 

PLAN_AWP<- g %>% 
  rowwise() %>%
  mutate(dt_range = paste0('Period ' , as.character(p), '. ', format(start, '%m/%d/%Y'), '-', format(end, '%m/%d/%Y'))) %>%
  as.data.frame() %>%
  select(Provider, Network,dt_range,new_awp) %>%
  spread(key = dt_range,value=new_awp,fill = FALSE)

[llyvslly]

Hi mrmallironmaker,

thank you so much for your help. That's exactly what I need! You really made my day!!!