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
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.
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 ) ...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With