Python Pandas ValueError

Question

Thank you for the suggestions below, I have amended my question to make it clearer

I have a dataframe (bp) with a balance as well as the (annual) collections in columns 1 - 6.

import pandas as pd
bp = pd.DataFrame({'Balance': {0: 20000, 1: 2000, 2: 7000},
 '1': {0: 500, 1: 400, 2: 100},
 '2': {0: 1500, 1: 500, 2: 2000},
 '3': {0: 0, 1: 1000, 2: 3000},
 '4': {0: 0, 1: 500, 2: 20},
 '5': {0: 0, 1: 50, 2: 0},
 '6': {0: 0, 1: 0, 2: 0},
 },columns=['Balance','1','2','3','4','5','6'])

I'm trying to project the balance in the next year (so balance in column 1 should be starting balance less collection in year 1). However at the same time I want to write down the balance to zero if no more collections are expected.

gbv = bp.copy()

startcol =1
endcol = 7
for i in range(startcol,endcol):
        gbv.iloc[:,i] = gbv.iloc[:,i-1] - bp.iloc[:,i]
gbv[gbv < 0] = 0  

gbv

The above code works, but does not write down the balance to zero if no more collections are expected, I have tried the following, but this gives an error. I suppose it is because I am comparing the rows (checking if there are future collections in bp) and gbv.iloc[:,i] forces the result on the total columns. Not sure how I should do this though.

gbv = bp.copy()

startcol =2
endcol = 14
for i in range(startcol,endcol):
    if bp.iloc[:,i:endcol].sum(axis=0) == 0:
        gbv.iloc[:,i]= 0
    else:
        gbv.iloc[:,i] = gbv.iloc[:,i-1] - bp.iloc[:,i]

gbv[gbv < 0] = 0  

gbv



---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-22-1920f826f3ea> in <module>()
      4 endcol = 14
      5 for i in range(startcol,endcol):
----> 6     if bp.iloc[:,i:endcol].sum(axis=0) == 0:
      7         gbv.iloc[:,i]= 0
      8     else:

/Users/Jelmer/anaconda/lib/python3.5/site-packages/pandas/core/generic.py in __nonzero__(self)
    951         raise ValueError("The truth value of a {0} is ambiguous. "
    952                          "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
--> 953                          .format(self.__class__.__name__))
    954 
    955     __bool__ = __nonzero__

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I am trying to get the following output:

    Balance 1       2       3       4       5       6
0   20000   19500   18000   0       0       0       0
1   2000    1600    1100    100     0       0       0
2   7000    6900    4900    1900    1880    0       0

Any suggestions welcome!

Answer 1

Got it! for completeness I will post the answer here. The trick is to filter for the rows where the future collections are zero.

gbv = bp.copy()

startcol =2
endcol = 14
for i in range(startcol,endcol):
        gbv.iloc[:,i] = gbv.iloc[:,i-1] - bp.iloc[:,i]
        gbv.iloc[:,i][bp.iloc[:,i:endcol].sum(axis=1)==0] = 0
        gbv[gbv < 0] = 0  

gbv

Answer 2

bp.iloc[:,i:endcol] gives you Series and if you want to take the sum of that series the axis should be along the rows. Looks like you have a bug in your code. Change line 5 of your code to the following and see if it works.

bp.iloc[:,i:endcol].sum(axis=0) == 0

At least, the error you are getting should go away.