I would like to calculate differences in values between weeks that are specified with a value in another column.
I've got a dataframe as below:
Week Unique value Stock WkDiff Expected output
0 15 a 3 2 2
1 15 b 1 2 -
2 15 c 3 2 -
3 15 e 10 1 3
4 15 d 1 2 -4
5 15 e 7 1 5
6 14 a 7 2 -
7 14 b 3 2 -
8 14 c 1 2 -
9 14 e 2 1 -4
10 13 e 6 1 -
11 13 a 1 2 -
12 13 d 5 2 -
In this case I would like to compare the most recent week stock to the stock identified by the WkDiff (So for the first row I would like to compare the stock of unique value a (wk15) to the stock of unique value a in wk15 - 2 (so wk 13). The result of this subtraction should be the input for the new column.
I've already created a function that provides a unique list of values in column "Unique value" since it is a flexible data file. I would like to run a loop for this list and refresh the column each month, whenever there is no match or calculation possible i want it to return empty.
def uniquelist(x,y):
temp = []
for item in x[y]:
if item not in temp:
temp.append(item)
However, my knowledge doesn't reach far enough to make a match in column "Unique value" and make the week comparison.