Skip to main content

how to get a weighted moving avg if we are having 2 df in pandas

Answered

Comments

7 comments

  • Maliheh Aramon
    Gurobi Staff Gurobi Staff

    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
  • Mohd Munasib
    Gurobi-versary
    Curious
    Conversationalist

    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 DataFrame
    actual_forecast['WMA'] = wma_values

    # Display the result
    print(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
  • Maliheh Aramon
    Gurobi Staff Gurobi Staff

    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,
    Maliheh

    0
  • Mohd Munasib
    Gurobi-versary
    Curious
    Conversationalist

    Hi Team, 

    hope you are doing good.

    I am not getting this output. please explain this again.

    0
  • Maliheh Aramon
    Gurobi Staff Gurobi Staff

    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
  • Mohd Munasib
    Gurobi-versary
    Curious
    Conversationalist

    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
  • Maliheh Aramon
    Gurobi Staff Gurobi Staff

    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");mfc

    i 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.