I have a dataframe (df). It contains predicted daily data from a model, up until the end of 2020. As each day passes in the year, actual and id data is added to the row. There are multiple names for each day
JavaScript
x
42
42
1
+------+-----+-----------+--------+------------+2
| NAME | ID | PREDICTED | ACTUAL | YYYY_MM_DD |3
+------+-----+-----------+--------+------------+4
| Nir | 215 | 100 | 400 | 2020-01-01 |5
| Nir | 215 | 200 | 400 | 2020-01-02 |6
| Nir | 215 | 100 | 400 | 2020-01-03 |7
| Nir | 215 | 200 | 400 | 2020-01-04 |8
| Nir | 215 | 100 | 400 | 2020-01-05 |9
| Nir | 215 | 200 | 400 | 2020-01-06 |10
| Nir | 215 | 100 | 400 | 2020-01-07 |11
| Nir | 215 | 200 | 400 | 2020-01-08 |12
| Nir | 215 | 100 | 400 | 2020-01-09 |13
| Nir | 215 | 200 | 400 | 2020-01-10 |14
| Nir | 215 | 100 | 400 | 2020-01-11 |15
| Nir | 215 | 200 | 400 | 2020-01-12 |16
| Nir | 215 | 100 | 400 | 2020-01-13 |17
| Nir | 215 | 200 | 400 | 2020-01-14 |18
| Nir | 215 | 100 | 400 | 2020-01-15 |19
| Nir | 215 | 200 | 400 | 2020-01-16 |20
| Nir | 215 | 100 | 400 | 2020-01-17 |21
| Nir | 215 | 200 | 400 | 2020-01-18 |22
| Nir | 215 | 100 | 400 | 2020-01-19 |23
| Nir | 215 | 200 | 400 | 2020-01-20 |24
| Nir | 215 | 100 | 400 | 2020-01-21 |25
| Nir | 215 | 200 | 400 | 2020-01-22 |26
| Nir | 215 | 100 | 400 | 2020-01-23 |27
| Nir | Nan | 100 | Nan | 2020-01-24 |28
| Nir | Nan | 100 | Nan | 2020-01-25 |29
| Nir | Nan | 100 | Nan | 2020-01-26 |30
| Nir | Nan | 100 | Nan | 2020-01-27 |31
| Nir | Nan | 100 | Nan | 2020-01-28 |32
| Nir | Nan | 100 | Nan | 2020-01-29 |33
| Nir | Nan | 100 | Nan | 2020-01-30 |34
| Nir | Nan | 100 | Nan | 2020-01-31 |35
| Xyc | 40 | 800 | 500 | 2020-01-01 |36
| Xyc | 40 | 100 | 500 | 2020-01-02 |37
| Xyc | 40 | 100 | 500 | 2020-01-03 |38
| Xyc | 40 | 100 | 500 | 2020-01-04 |39
| | | | | |40
| | | | | |41
+------+-----+-----------+--------+------------+42
I want to add an additional column named payout. The payout should be 0 unless the sum of actual, month to date has passed the sum of predicted.
I.e., for Nir, we can see the sum of predicted is 4200. So the payout should be 0 until the sum of actual passes 4200. Once that threshold is passed, then the payout should be 1% of actual-predicted. With the above data, the output would look like this:
JavaScript
1
42
42
1
+------+-----+-----------+--------+---------------+--------+------------+2
| NAME | ID | PREDICTED | ACTUAL | MONTH_TO_DATE | PAYOUT | YYYY_MM_DD |3
+------+-----+-----------+--------+---------------+--------+------------+4
| Nir | 215 | 100 | 400 | 400 | 0 | 2020-01-01 |5
| Nir | 215 | 200 | 400 | 800 | 0 | 2020-01-02 |6
| Nir | 215 | 100 | 400 | 1200 | 0 | 2020-01-03 |7
| Nir | 215 | 200 | 400 | 1600 | 0 | 2020-01-04 |8
| Nir | 215 | 100 | 400 | 2000 | 0 | 2020-01-05 |9
| Nir | 215 | 200 | 400 | 2400 | 0 | 2020-01-06 |10
| Nir | 215 | 100 | 400 | 2800 | 0 | 2020-01-07 |11
| Nir | 215 | 200 | 400 | 3200 | 0 | 2020-01-08 |12
| Nir | 215 | 100 | 400 | 3600 | 0 | 2020-01-09 |13
| Nir | 215 | 200 | 400 | 4000 | 0 | 2020-01-10 |14
| Nir | 215 | 100 | 400 | 4400 | 3 | 2020-01-11 |15
| Nir | 215 | 200 | 400 | | 2 | 2020-01-12 |16
| Nir | 215 | 100 | 400 | | 3 | 2020-01-13 |17
| Nir | 215 | 200 | 400 | | 2 | 2020-01-14 |18
| Nir | 215 | 100 | 400 | | 3 | 2020-01-15 |19
| Nir | 215 | 200 | 400 | | 2 | 2020-01-16 |20
| Nir | 215 | 100 | 400 | | 3 | 2020-01-17 |21
| Nir | 215 | 200 | 400 | | 2 | 2020-01-18 |22
| Nir | 215 | 100 | 400 | | 3 | 2020-01-19 |23
| Nir | 215 | 200 | 400 | | 2 | 2020-01-20 |24
| Nir | 215 | 100 | 400 | | 3 | 2020-01-21 |25
| Nir | 215 | 200 | 400 | | 2 | 2020-01-22 |26
| Nir | 215 | 100 | 400 | | 3 | 2020-01-23 |27
| Nir | Nan | 100 | Nan | | | 2020-01-24 |28
| Nir | Nan | 100 | Nan | | | 2020-01-25 |29
| Nir | Nan | 100 | Nan | | | 2020-01-26 |30
| Nir | Nan | 100 | Nan | | | 2020-01-27 |31
| Nir | Nan | 100 | Nan | | | 2020-01-28 |32
| Nir | Nan | 100 | Nan | | | 2020-01-29 |33
| Nir | Nan | 100 | Nan | | | 2020-01-30 |34
| Nir | Nan | 100 | Nan | | | 2020-01-31 |35
| Xyc | 40 | 800 | 500 | 500 | 0 | 2020-01-01 |36
| Xyc | 40 | 100 | 500 | 1000 | 0 | 2020-01-02 |37
| Xyc | 40 | 100 | 500 | 1500 | 4 | 2020-01-03 |38
| Xyc | 40 | 100 | 500 | 2000 | 4 | 2020-01-04 |39
| | | | | | | |40
| | | | | | | |41
+------+-----+-----------+--------+---------------+--------+------------+42
In the above output, Xyc has a total predicted 2000, so payout should be 0 until the sum of actual passes 2000 also. In the real dataframe, there is daily data for ~70 names, so I feel like a grouping may be needed.
I’ve tried:
JavaScript
1
4
1
new_sum = [df.actual.values[0]] for i in range(1, len(df.index)): 2
new_sum.append(new_sum[i-1]+df.actual.values[i]) 3
df['actual_sum'] = new_sum 4
However, that simply gave me a running total of actual. I also tried this:
JavaScript
1
2
1
df['inc'] = df['actual'] - df['predicted'] df['payout'] = np.where(df['inc']>=1, (df['inc'] / 100) * 1, 0) 2
But the above doesn’t make sure the month to date >= total for the month before attributing the 1%.
Advertisement
Answer
First You need to remove NaN rows from data.
Here You go:
JavaScript
1
45
45
1
import pandas as pd2
import numpy as np3
4
5
df = pd.DataFrame({'Name':['Nir','Nir','Nir','Nir','Xyc','Xyc','Xyc'],'PREDICTED':[100,200,100,200,100,200,300],6
'ACTUAL':[400,400,400,400,500,500,500],7
'YYYY_MM_DD':['2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-01','2020-01-02','2020-01-03']})8
9
10
def calculate(item):11
# select name12
data = df[df['Name'] == item]13
# calculate sum14
sum = data['PREDICTED'].sum()15
16
# remove NaN rows17
data = data.dropna()18
19
# calculate and insert month to date column values20
month_to_date = []21
value = 022
for index, row in data.iterrows():23
value += row['ACTUAL']24
month_to_date.append(value)25
26
data.insert(3, "MONTH_TO_DATE", month_to_date, True)27
28
# calculate and instert payout values29
conditions = [30
(data['MONTH_TO_DATE'] < sum),31
(data['MONTH_TO_DATE'] >= sum)32
]33
choices = [0, ((data['ACTUAL'] - data['PREDICTED'])/100).astype(int)]34
data.insert(5, "PAYOUT", np.select(conditions, choices), True)35
36
return data37
38
39
# collect results40
results = pd.DataFrame(columns=['Name','PREDICTED','ACTUAL','MONTH_TO_DATE','YYYY_MM_DD','PAYOUT'])41
42
for item in df['Name'].unique():43
df2 = calculate(item)44
results = results.append(df2)45
Result:
JavaScript
1
9
1
Name PREDICTED ACTUAL MONTH_TO_DATE YYYY_MM_DD PAYOUT2
0 Nir 100 400 400 2020-01-01 03
1 Nir 200 400 800 2020-01-02 24
2 Nir 100 400 1200 2020-01-03 35
3 Nir 200 400 1600 2020-01-04 26
4 Xyc 100 500 500 2020-01-01 07
5 Xyc 200 500 1000 2020-01-02 38
6 Xyc 300 500 1500 2020-01-03 29