I am trying to drop the value of Y with one important variable.
Y starts with value of 2.4 and endpoint is 1.7. The problem is I do not know the data between 2.4 and 1.7. (Only clue is it always depends on the value of X and drops til hit 1.7)
If X value is big, Y should drop more. (X value is between 0 ~ 8).
If it is small, it should drop less.
Anyone can give an advice? or a way to perform?
Below is the sample data.
Y X
2.4 1
? 7
? 3
? 5
? 8
1.7 3
One way would be :
start = 2.4
end = 1.7
df$Y <- c(start, start - cumsum(df$X[-1] * (start - end)/sum(df$X[-1])))
# X Y
#1 1 2.400000
#2 7 2.211538
#3 3 2.130769
#4 5 1.996154
#5 8 1.780769
#6 3 1.700000
Let's break this down. df$X is the numbers which determines the magnitude of drop
df$X
#[1] 1 7 3 5 8 3
When we do
(start - end)/sum(df$X[-1])
#[1] 0.02692308
It gives us how much 1 unit of drop is. We are ignoring the first value since we already have that number (2.4) and we don't want to take that into calculation.
Now we multiply it by X so that when the number is 7 we will get higher drop, when it is 1 the drop will be lower
df$X[-1] * (start - end)/sum(df$X[-1])
#[1] 0.18846154 0.08076923 0.13461538 0.21538462 0.08076923
When you compare this with df$X you will notice that
0.02692308 * 7 = 0.1884615
0.02692308 * 3 = 0.08076923
0.02692308 * 5 = 0.1346154
and so on.
Finally we take cumulative sum (cumsum) by adding all these numbers which we subtract it from start so that we now have relative drops according to corresponding X.