Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calculating Growth Percentage in R Dataframe

Tags:

dataframe

r

dplyr

I have below mentioned dataframe:

Month         Val1          Val2      Part1      Part2
Feb-17        250           10000     25         100
A             25            2000      5          20
B             25            2000      5          20
C             100           2000      5          20
D             10            2000      5          20
E             40            2000      5          20
Jan-17        200           8000     50         100
A             20            1000      10         20
B             40            1600      10         20
C             100           1600      10         20
D             20            1600      10         20
E             20            2200      10         20

I want to add two columns Per_1 after Val1 and Per_2 after Val2. Which compare Feb-17 Data with Jan-17 Data and give growth percentage (increase or decrease).

Note: For A,B,C,D and E the sequence would be differ in Feb-17 and Jan-17, here i want to calculate growth of Feb-17 A with Jan-17 A and respectively.

In case of Jan-17, since there are no Data in last month to compare with in dataframe, the Per_1 and Per_2 should be 0.00% in that case.

Required Output:

Month   Val1    Per_1   Val2    Per_2   Part1   Part2
Feb-17  250     25.00%  10000   25.00%  25  100
A       25      25.00%  2000    100.00% 5   20
B       25     -37.50%  2000    25.00%  5   20
C       100     0.00%   2000    25.00%  5   20
D       10     -50.00%  2000    25.00%  5   20
E       40      100.00% 2000    -9.09%  5   20
Jan-17  200     0.00%   8000    0.00%   50  100
C       100     0.00%   1600    0.00%   10  20
A       20      0.00%   1000    0.00%   10  20
B       40      0.00%   1600    0.00%   10  20
E       20      0.00%   2200    0.00%   10  20
D       20      0.00%   1600    0.00%   10  20
like image 923
Roy1245 Avatar asked May 06 '18 13:05

Roy1245


People also ask

How do you calculate growth rate in R?

Net reproductive rate (r) is calculated as: r = (births-deaths)/population size or to get in percentage terms, just multiply by 100. the population is so much bigger, many more individuals are added. If a population grows by a constant percentage per year, this eventually adds up to what we call exponential growth.

How do you calculate CAGR in R?

example_data <- tribble( ~Values_2009, ~Values_2014, ~Years, 10000, 19500, 5, 10500, 15000, 5, 25000, 35500, 5 ) CAGR_formula <- function(PV, FV, yrs) { values <- ((FV/PV)^(1/yrs)-1) return(values) } example_data %>% mutate(CAGR = CAGR_formula(Values_2009, Values_2014, Years), check = Values_2009 * (1 + CAGR) ^ Years ) ...


1 Answers

This can be done with dplyr and lead

I made a seperate column ID that replaced each month by a constant value so that we could then group_by and have a consistent category from month to month. Then I created the Per_1 and Per_2 columns by using lead. Last I select the columns that you want returned not including any of the intermediate columns.

library(dplyr)
df %>% 
  mutate(ID = ifelse(grepl("\\d",Month),"First",Month)) %>% 
  group_by(ID) %>% 
  mutate(Val1 = as.numeric(Val1),
         Val2 = as.numeric(Val2), 
         next.month1 = lead(Val1, order_by=ID) ,
         next.month2 = lead(Val2, order_by=ID), 
         Per_1 = 100*(Val1-next.month1)/next.month1,
         Per_2 = 100*(Val2-next.month2)/next.month2) %>%
  ungroup() %>% 
  select(Month,Val1,Per_1,Val2,Per_2,Part1,Part2)


# A tibble: 12 x 7
#   Month   Val1  Per_1  Val2   Per_2 Part1 Part2
#   <fct>  <dbl>  <dbl> <dbl>   <dbl> <int> <int>
# 1 Feb-17 250     25.0 10000   25.0     25   100
# 2 A       25.0   25.0  2000  100        5    20
# 3 B       25.0 - 37.5  2000   25.0      5    20
# 4 C      100      0    2000   25.0      5    20
# 5 D       10.0 - 50.0  2000   25.0      5    20
# 6 E       40.0  100    2000 -  9.09     5    20
# 7 Jan-17 200     NA    8000   NA       50   100
# 8 C      100     NA    1600   NA       10    20
# 9 A       20.0   NA    1000   NA       10    20
#10 B       40.0   NA    1600   NA       10    20
#11 E       20.0   NA    2200   NA       10    20
#12 D       20.0   NA    1600   NA       10    20
like image 198
jasbner Avatar answered Oct 16 '22 12:10

jasbner