How can i store results from a loop in a dataframe?

Question

I have the following loop creating kpi values for each of my 5 subsets.

list = [sku_1,sku_2,sku_3,sku_4,sku_5]
for i in list:
    df = triple_exp_smooth_mul(i, slen=12, extra_periods=4, alpha=0.3,beta=0.2, phi=0.9, gamma=0.2)
    kpi(df)
    print(kpi)

Output: 
Bias: 3.19, 1.06%
MAPE: 14.93%
MAE: 43.94, 14.64%
RMSE: 56.84, 18.94%
<function kpi at 0x000002634941C8B0>
Bias: -2.57, -1.31%
MAPE: 15.27%
MAE: 28.04, 14.27%
RMSE: 40.29, 20.50%
<function kpi at 0x000002634941C8B0>
Bias: -7.93, -3.77%
MAPE: 16.98%
MAE: 33.76, 16.08%
RMSE: 48.83, 23.25%
<function kpi at 0x000002634941C8B0>
Bias: -15.05, -2.98%
MAPE: 37.16%
MAE: 170.31, 33.75%
RMSE: 224.58, 44.50%
<function kpi at 0x000002634941C8B0>
Bias: 5.37, 2.03%
MAPE: 21.45%
MAE: 53.89, 20.41%
RMSE: 78.66, 29.79%
<function kpi at 0x000002634941C8B0>

Is there a way to store this in a nice table, something like this:

KPI	SKU1 Value	SKU1 Scaled
BIAS	xx	%
MAPE	xx	%
MAE	xx	%
RMSE	xx	%

With the SKU Value and SKU scaled in the columns?

Edit: Code used to create KPI function:

#Defining the function for measuring the forecast accuracy for each Forecast "#Credits to Nicolas Candeput "Data Science For Supply Chain Forecasting""
def kpi(df):
    dem_ave = df.loc[df['Error'].notnull(),'Demand'].mean()
    bias_abs = df['Error'].mean()
    bias_rel = bias_abs / dem_ave
    print('Bias: {:0.2f}, {:.2%}'.format(bias_abs,bias_rel))
    MAPE = (df['Error'].abs()/df['Demand']).mean()
    print('MAPE: {:.2%}'.format(MAPE))
    MAE_abs = df['Error'].abs().mean()
    MAE_rel = MAE_abs / dem_ave
    print('MAE: {:0.2f}, {:.2%}'.format(MAE_abs,MAE_rel))
    RMSE_abs = np.sqrt((df['Error']**2).mean())
    RMSE_rel = RMSE_abs / dem_ave
    print('RMSE: {:0.2f}, {:.2%}'.format(RMSE_abs,RMSE_rel))

You could put the data into a CSV file. The CSV writer takes an array as an argument, so you could arrange your data into a set of arrays and write it into the CSV file https://docs.python.org/3/library/csv.html — Nick Mullen, Dec 22 '21 at 20:12
By `print(kpi)`, you print the _function_ `kpi`, not the result from calling that function (which is why you get the output ``). It seems like your function doesn't return anything, and prints its output instead. Since it doesn't return anything, and you can't variables internal to the function from the outside unless they are returned, you can't do what you want with what you have shown. Are you able to modify this function? Maybe show us the a [mre] containing the `kpi` function? — Pranav Hosangadi, Dec 22 '21 at 20:23
Wouldn't it make more sense to have the columns be "Bias", "Bias Scaled", "MAPE", "MAPE Scale" etc, then have a variable number of rows, one for each SKU? That's not hard. — Tim Roberts, Dec 22 '21 at 20:27
@TimRoberts - That would be perfect yes. How would i go about that? — Frederik Forum, Dec 23 '21 at 10:24
@PranavHosangadi - I have added the code for creating the KPI. Does that help you, to help me? — Frederik Forum, Dec 23 '21 at 10:27

score 1 · Accepted Answer · answered Dec 23 '21 at 15:04

The first thing to understand is that printing something inside a function isn't the same as returning it. See How is returning the output of a function different from printing it?

I also second Tim's suggestion that Bias, etc. should be columns, and you should have rows for each SKU.

You should return the values you want from your function, and then use those returned values to add a row to your dataframe. We will return the values as a dictionary, save all returned dicts to a list, and once we're done with all SKUs, create a dataframe from this list of dicts using the constructor (see Convert list of dictionaries to a pandas DataFrame).

So first, let's modify our kpi() function to return a dict whose keys are the column names, and values are the values we want for that row.

def kpi(df):
    ret = {}
    dem_ave = df.loc[df['Error'].notnull(),'Demand'].mean()
    bias_abs = df['Error'].mean()
    bias_rel = bias_abs / dem_ave
    ret['Bias'] = bias_abs
    ret['Bias %'] = bias_rel
    # print('Bias: {:0.2f}, {:.2%}'.format(bias_abs,bias_rel))
    MAPE = (df['Error'].abs()/df['Demand']).mean()
    ret['MAPE'] = MAPE
    # print('MAPE: {:.2%}'.format(MAPE))
    MAE_abs = df['Error'].abs().mean()
    MAE_rel = MAE_abs / dem_ave
    ret['MAE'] = MAE_abs
    ret['MAE %'] = MAE_rel
    # print('MAE: {:0.2f}, {:.2%}'.format(MAE_abs,MAE_rel))
    RMSE_abs = np.sqrt((df['Error']**2).mean())
    RMSE_rel = RMSE_abs / dem_ave
    ret['RMSE'] = RMSE_abs
    ret['RMSE %'] = RMSE_rel
    # print('RMSE: {:0.2f}, {:.2%}'.format(RMSE_abs,RMSE_rel))
    return ret

Now, let's create an empty list to hold our returned dicts and fill it up:

sku_list = [sku_1,sku_2,sku_3,sku_4,sku_5]
result_list = []
for sku in sku_list:
    df = triple_exp_smooth_mul(sku, slen=12, extra_periods=4, alpha=0.3,beta=0.2, phi=0.9, gamma=0.2)
    result_list.append(kpi(df))

You could write this as a list comprehension instead of a loop like so:

result_list = [kpi(
                   triple_exp_smooth_mul(sku, slen=12, extra_periods=4, alpha=0.3,beta=0.2, phi=0.9, gamma=0.2)
                   ) 
               for sku in sku_list]

Then, create a dataframe out of it:

result_df = pd.DataFrame(result_list)

Note that I renamed your list to sku_list because it's not a good idea to shadow builtins.

How can i store results from a loop in a dataframe?

1 Answers1