ben121 - 1 year ago 108
Python Question

# if negative then with weighted average

I have a DataFrame:

``````a = {'Price': [10, 15, 20, 25, 30], 'Total': [10000, 12000, 15000, 14000, 10000],
'Previous Quarter': [0, 10000, 12000, 15000, 14000]}
a = pd.DataFrame(a)
print (a)
``````

With this raw data, i have added a number of additional columns including a weighted average price (WAP)

``````a['Change'] = a['Total'] - a['Previous Quarter']
a['Amount'] = a['Price']*a['Change']
a['Cum Sum Amount'] = np.cumsum(a['Amount'])
a['WAP'] = a['Cum Sum Amount'] / a['Total']
``````

This is fine, however as the total starts to decrease this brings down the weighted average price.

my question is, if Total decreases how would i get WAP to reflect the row above? For instance in row 3, Total is 1000, which is lower than in row 2. This brings WAP down from 12.6 to 11.78, but i would like it to say 12.6 instead of 11.78.

I have tried looping through a['Total'] < 0 then a['WAP'] = 0 but this impacts the whole column.

Ultimately i am looking for a WAP column which reads:
10, 10.83, 12.6, 12.6, 12.6

You could use `cummax`:

``````a['WAP'] = (a['Cum Sum Amount'] / a['Total']).cummax()

print (a['WAP'])

0    10.000000
1    10.833333
2    12.666667
3    12.666667
4    12.666667
Name: WAP, dtype: float64
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download