how to get a weighted moving avg if we are having 2 df in pandas
AnsweredHi Team,
Hope you are doing well.
I am having 2 df in pandas my goal is to get a weighted moving avg in my problem but when i am trying to code this in python so i get an error. i have attached the link of my project
https://drive.google.com/drive/folders/1MiZpAv1pf6KyZTA2TgdTnQ2Y34pzvNAF
First df is;-
Week | Actual (A) |
1 | 122 |
2 | 105 |
3 | 93 |
4 | 113 |
5 | 107 |
6 | 115 |
7 | 111 |
8 | 87 |
9 | 126 |
10 | 104 |
11 | 125 |
12 | 126 |
13 | 151 |
14 | 76 |
15 | 71 |
16 | 70 |
17 | 127 |
18 | 98 |
19 | 105 |
20 | 103 |
21 | 62 |
22 | 120 |
23 | 118 |
24 | 98 |
Second df is in which i have defined the weight decission variable.
serial number |
|
|
---|---|---|
1 | <gurobi.Var *Awaiting Model Update*> | |
2 | <gurobi.Var *Awaiting Model Update*> | |
3 | <gurobi.Var *Awaiting Model Update*> | |
4 | <gurobi.Var *Awaiting Model Update*> | |
5 | <gurobi.Var *Awaiting Model Update*> |
My code is :-
8 frames
/usr/local/lib/python3.10/dist-packages/pandas/_libs/window/aggregations.pyx in pandas._libs.window.aggregations.roll_apply()
TypeError: must be real number, not gurobipy.LinExpr
I am looking forward to resolved this issue
Thanks
Munasib
-
Hi Mohd,
The error message indicates that the pandas Rolling.apply() method can only produce single numeric values. Maybe you can use the fact that the pandas rolling object is iterable.
It is not clear to me the constraints you are trying to define. The script below finds the moving weighted sum of the forecast values over 5 weeks and ensures each weighted sum is >= the constant value 20, for example.
weights = pd.DataFrame({"serial_number": range(1, 6)}).set_index(["serial_number"])
model = gp.Model()
x = gppd.add_vars(model, weights, ub=1, name="weights")
window_size = 5
moving_sum = (
(x * df.values).sum()
for df in forecast["Actual (A)"].rolling(window=window_size)
if not len(df.values) < window_size
)
gppd.add_constrs(model, moving_sum, GRB.GREATER_EQUAL, 20, name="c")
Best regards,Maliheh
0 -
Hi Team,
Hope you are doing well.
I am having 2 df in pandas my goal is to get a weighted moving avg in my problem but when i am trying to code this in python so i get an error. i have attached the link of my project
https://drive.google.com/drive/folders/1MiZpAv1pf6KyZTA2TgdTnQ2Y34pzvNAF
First df is;-
Week Actual (A) 1 122 2 105 3 93 4 113 5 107 6 115 7 111 8 87 9 126 10 104 11 125 12 126 13 151 14 76 15 71 16 70 17 127 18 98 19 105 20 103 21 62 22 120 23 118 24 98 Second df is in which i have defined the weight decission variable.
serial number weights w1 <gurobi.Var *Awaiting Model Update*> w2 <gurobi.Var *Awaiting Model Update*> w3 <gurobi.Var *Awaiting Model Update*> w4 <gurobi.Var *Awaiting Model Update*> w5 <gurobi.Var *Awaiting Model Update*> My code is :-
window_size = len(weights_v_df)
wma_values = actual_forecast['Actual (A)'].rolling(window=window_size).apply(lambda x: (x * weights_v_df['weights']).sum(), raw=True)
# Add the WMA column to the original DataFrameactual_forecast['WMA'] = wma_values
# Display the resultprint(actual_forecast)I am getting this error : -
8 frames
/usr/local/lib/python3.10/dist-packages/pandas/_libs/window/aggregations.pyx in pandas._libs.window.aggregations.roll_apply()
TypeError: must be real number, not gurobipy.LinExpr
my expected output is:-
week Actual (A) Forecast (F) 1 122 NaN 2 105 NaN 3 93 NaN 4 113 NaN 5 107 NaN 6 115 122*<gurobi.Var *Awaiting Model Update*> + 105*<gurobi.Var *Awaiting Model Update*> + 93*<gurobi.Var *Awaiting Model Update*> +113*<gurobi.Var *Awaiting Model Update*> + 107*<gurobi.Var *Awaiting Model Update*> 7 111 105*<gurobi.Var *Awaiting Model Update*> + 93*<gurobi.Var *Awaiting Model Update*> +113*<gurobi.Var *Awaiting Model Update*> + 107*<gurobi.Var *Awaiting Model Update*> + 115 * <gurobi.Var *Awaiting Model Update*> 8 87 I will do this tilee the last row 9 126 10 104 11 125 12 126 13 151 14 76 15 71 16 70 17 127 18 98 19 105 20 103 21 62 22 120 23 118 24 98 till this row will repet the same step Thanks
Munasib
0 -
The script shared before was very close to what you need. You just need to define a new column. See the script below:
model = gp.Model()
x = gppd.add_vars(model, weights, ub=1, name="weights")
window_size = 5
moving_sum= [
(x * df.values[:-1]).sum()
for df in forecast["Actual (A)"].rolling(window=window_size+1)
if not len(df.values) < window_size + 1
]
forecast["Forecast (F)"] = [np.NaN for_ in range(window_size)] + moving_sum
If you then print the \(\texttt{forecast}\) dataframe, you will see:Actual (A) Forecast (F)
Week
1 122 NaN
2 105 NaN
3 93 NaN
4 113 NaN
5 107 NaN
6 115 122.0 weights[1] + 105.0 weights[2] + 93.0 wei...
7 111 105.0 weights[1] + 93.0 weights[2] + 113.0 wei...
8 87 93.0 weights[1] + 113.0 weights[2] + 107.0 wei...
9 126 113.0 weights[1] + 107.0 weights[2] + 115.0 we...
10 104 107.0 weights[1] + 115.0 weights[2] + 111.0 we...
11 125 115.0 weights[1] + 111.0 weights[2] + 87.0 wei...
12 126 111.0 weights[1] + 87.0 weights[2] + 126.0 wei...
13 151 87.0 weights[1] + 126.0 weights[2] + 104.0 wei...
14 76 126.0 weights[1] + 104.0 weights[2] + 125.0 we...
15 71 104.0 weights[1] + 125.0 weights[2] + 126.0 we...
16 70 125.0 weights[1] + 126.0 weights[2] + 151.0 we...
17 127 126.0 weights[1] + 151.0 weights[2] + 76.0 wei...
18 98 151.0 weights[1] + 76.0 weights[2] + 71.0 weig...
19 105 76.0 weights[1] + 71.0 weights[2] + 70.0 weigh...
20 103 71.0 weights[1] + 70.0 weights[2] + 127.0 weig...
21 62 70.0 weights[1] + 127.0 weights[2] + 98.0 weig...
22 120 127.0 weights[1] + 98.0 weights[2] + 105.0 wei...
23 118 98.0 weights[1] + 105.0 weights[2] + 103.0 wei...
24 98 105.0 weights[1] + 103.0 weights[2] + 62.0 wei...
Could you please clarify what the issue with the previous script was that you posted your question again?
Thanks,
Maliheh0 -
Hi Team,
hope you are doing good.
I am not getting this output. please explain this again.
0 -
Could you please run the script below exactly as it and let us know the output you see?
import gurobipy as gp
from gurobipy import GRB
import gurobipy_pandas as gppd
import numpy as np
import pandas as pd
if __name__ == "__main__":
data = [
[1, 122],
[2, 105],
[3, 93],
[4, 113],
[5, 107],
[6, 115],
[7, 111],
[8, 87],
[9, 126],
[10, 104],
[11, 125],
[12, 126],
[13, 151],
[14, 76],
[15, 71],
[16, 70],
[17, 127],
[18, 98],
[19, 105],
[20, 103],
[21, 62],
[22, 120],
[23, 118],
[24, 98],
]
forecast = pd.DataFrame(data, columns=["Week", "Actual (A)"]).set_index(["Week"])
weights = pd.DataFrame({"serial_number": range(1, 6)}).set_index(["serial_number"])
model = gp.Model()
x = gppd.add_vars(model, weights, ub=1, name="weights")
window_size = 5
moving_sum = [
(x * df.values[:-1]).sum()
for df in forecast["Actual (A)"].rolling(window=window_size+1)
if not len(df.values) < window_size + 1
]
forecast["Forecast (F)"] = [np.NaN for _ in range(window_size)] + moving_sum
model.update()
print(forecast)0 -
Hi Team,
Hope you are doing good.
this is a actual_forecast data frame and i am trying to get the absolute value of (actual_forecast[A-f]).
So please let me know how to define the abs function in gurobipy
Week Actual (A) Forecast (F) A-f 1 122 NaN NaN 2 105 NaN NaN 3 93 NaN NaN 4 113 NaN NaN 5 107 NaN NaN 6 115 122.0 weights[0] + 105.0 weights[1] + 93.0 weights[2] + 113.0 weights[3] + 107.0 weights[4] 115.0 + -122.0 weights[0] + -105.0 weights[1] + -93.0 weights[2] + -113.0 weights[3] + -107.0 weights[4] 7 111 105.0 weights[0] + 93.0 weights[1] + 113.0 weights[2] + 107.0 weights[3] + 115.0 weights[4] 111.0 + -105.0 weights[0] + -93.0 weights[1] + -113.0 weights[2] + -107.0 weights[3] + -115.0 weights[4] 8 87 93.0 weights[0] + 113.0 weights[1] + 107.0 weights[2] + 115.0 weights[3] + 111.0 weights[4] 87.0 + -93.0 weights[0] + -113.0 weights[1] + -107.0 weights[2] + -115.0 weights[3] + -111.0 weights[4] 9 126 113.0 weights[0] + 107.0 weights[1] + 115.0 weights[2] + 111.0 weights[3] + 87.0 weights[4] 126.0 + -113.0 weights[0] + -107.0 weights[1] + -115.0 weights[2] + -111.0 weights[3] + -87.0 weights[4] 10 104 107.0 weights[0] + 115.0 weights[1] + 111.0 weights[2] + 87.0 weights[3] + 126.0 weights[4] 104.0 + -107.0 weights[0] + -115.0 weights[1] + -111.0 weights[2] + -87.0 weights[3] + -126.0 weights[4] 11 125 115.0 weights[0] + 111.0 weights[1] + 87.0 weights[2] + 126.0 weights[3] + 104.0 weights[4] 125.0 + -115.0 weights[0] + -111.0 weights[1] + -87.0 weights[2] + -126.0 weights[3] + -104.0 weights[4] 12 126 111.0 weights[0] + 87.0 weights[1] + 126.0 weights[2] + 104.0 weights[3] + 125.0 weights[4] 126.0 + -111.0 weights[0] + -87.0 weights[1] + -126.0 weights[2] + -104.0 weights[3] + -125.0 weights[4] 13 151 87.0 weights[0] + 126.0 weights[1] + 104.0 weights[2] + 125.0 weights[3] + 126.0 weights[4] 151.0 + -87.0 weights[0] + -126.0 weights[1] + -104.0 weights[2] + -125.0 weights[3] + -126.0 weights[4] 14 76 126.0 weights[0] + 104.0 weights[1] + 125.0 weights[2] + 126.0 weights[3] + 151.0 weights[4] 76.0 + -126.0 weights[0] + -104.0 weights[1] + -125.0 weights[2] + -126.0 weights[3] + -151.0 and the second task is to add the constrained of (∑(weights_v_df["weights"]) = 1)
here weights_v_df is a data frame
serial number weights 1 <gurobi.Var *Awaiting Model Update*> 2 <gurobi.Var *Awaiting Model Update*> 3 <gurobi.Var *Awaiting Model Update*> 4 <gurobi.Var *Awaiting Model Update*> 5 <gurobi.Var *Awaiting Model Update*> I am looking forward to your reply
0 -
To implement the constraint on the weights variables, you need to use the Model.addConstr() method.
x = gppd.add_vars(model, weights, ub=1, name="weights")
# Add constr \sum weights = 1
model.addConstr(x.sum() ==1, name="sum_of_weights");mfci am trying to get the absolute value of (actual_forecast[A-f]).
See the script below:
# Auxiliary variables y and absy to represent
# absy = |y = forecast["Actual (A)""] - forecast["Forecast (F)"]|
forecast = forecast.gppd.add_vars(
model, lb=-GRB.INFINITY, ub=GRB.INFINITY, name="y"
)
forecast = forecast.gppd.add_vars(model, name="absy")
# Define constraints y = forecast["Actual (A)""] - forecast["Forecast (F)"]
forecast["y == A - F"] = gppd.add_constrs(
model,
forecast["y"].iloc[window_size:],
GRB.EQUAL,
(forecast["Actual (A)"] - forecast["Forecast (F)"]).dropna(),
name="A_minus_F",
)
# Define constraints absy = |y|. There is no built-in gurobipy-pandas
# method to implement general constraints at once. The constraints are
# therefore added one by one.
forecast["absy == |y|"] = pd.Series(
[
model.addConstr(
row.absy == gp.abs_(row.y), name=f"abs_A_minus_F[{row.Index}]"
)
for row in forecast.itertuples()
],
index=forecast.index,
)If you have additional questions on other types of constraints and their implementations, it would be more efficient to include all in your next post.
Best regards,
Maliheh
0
Please sign in to leave a comment.
Comments
7 comments