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!

To have the balance written down to zero if no further collections are expected, and floor the balance at zero (when collections > balance) — Jelmerd
– Jelmerd, Commented Aug 27, 2017 at 12:24
Sorry, I should have been more specific: Please include how gbv should look like at the end. Just like you did with the input. :) It's much easier to understand text if you have the result to verify that it's understood correctly. — MSeifert
– MSeifert, Commented Aug 27, 2017 at 12:27
It would be easier to understand which output you expect if you show what you want the dataframe to look like after its gone through your function. Also if you give your input data in such a form that it is possible to copy paste your code (i.e. gbv = DataFrame({'Borrower' : [4946,...], ...}]) etc. it is easier for others to replicate your code and try to figure it out — mortysporty
– mortysporty, Commented Aug 27, 2017 at 12:31

Jelmerd · Accepted Answer · 2017-08-28 18:00:19Z

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

answered Aug 28, 2017 at 18:00

Jelmerd

1634 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Heapify · Accepted Answer · 2017-08-27 13:52:51Z

0

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.

answered Aug 27, 2017 at 13:52

Heapify

2,97623 silver badges21 bronze badges

Collectives™ on Stack Overflow

Python Pandas ValueError

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related