Allowing last line in "amount" column of dataframe to be a plug value to solve rounding error

user_14580806 0
Viewed 48

I have the following dataframe:

       BILL_NO    DUE_DATE  AMOUNT
0   1000005848  01/06/2021   39.73
1   1000005848  01/06/2021   39.73
2   1000005848  01/06/2021   39.73
3   1000005848  01/06/2021   39.73
4   1000005848  01/06/2021   39.73
5   1000005848  01/06/2021   39.73
6   1000005848  01/06/2021   39.73
7   1000005848  01/06/2021   39.73
8   1000005848  01/06/2021   39.73
9   1000005848  01/06/2021   39.73
10  1000005848  01/06/2021   39.73
11  1000005848  01/06/2021   39.73
12  1000005848  01/06/2021   39.73
13  1000005848  01/06/2021   39.73
14  1000005848  01/06/2021   39.73
15  1000005848  01/06/2021   39.73
16  1000005848  01/06/2021   39.73
17  1000005848  01/06/2021   39.73

The amount column is the total of invoice #1000005848 ($715.17) divided by 18. But as you can see, if you add up the amount column it equals 715.14.

This is the code I used to get the amount column:

df['AMOUNT'] = round(invoice_amount/len(df['BILL_NO']), 2)

This is causing a rounding issue.

How can I get the last row of the amount column to be a "plug-in" to cover the rounding?

pandas dataframe csv python
2 Answers

How about manually adjust the last item:

# assuming `AMOUNT` is the last column
df.iloc[-1,-1] += invoice_amount - df['AMOUNT'].sum()

Output:

       BILL_NO    DUE_DATE  AMOUNT
0   1000005848  01/06/2021   39.73
1   1000005848  01/06/2021   39.73
2   1000005848  01/06/2021   39.73
3   1000005848  01/06/2021   39.73
4   1000005848  01/06/2021   39.73
5   1000005848  01/06/2021   39.73
6   1000005848  01/06/2021   39.73
7   1000005848  01/06/2021   39.73
8   1000005848  01/06/2021   39.73
9   1000005848  01/06/2021   39.73
10  1000005848  01/06/2021   39.73
11  1000005848  01/06/2021   39.73
12  1000005848  01/06/2021   39.73
13  1000005848  01/06/2021   39.73
14  1000005848  01/06/2021   39.73
15  1000005848  01/06/2021   39.73
16  1000005848  01/06/2021   39.73
17  1000005848  01/06/2021   39.76
user_42384080

Try with saferound, even it is not only trim the last row

from iteround import saferound
df['AMOUNT'] = saferound(df['AMOUNT'],places=2)
df
Out[261]: 
       BILL_NO    DUE_DATE  AMOUNT
0   1000005848  01/06/2021   39.74
1   1000005848  01/06/2021   39.74
2   1000005848  01/06/2021   39.74
3   1000005848  01/06/2021   39.73
4   1000005848  01/06/2021   39.73
5   1000005848  01/06/2021   39.73
6   1000005848  01/06/2021   39.73
7   1000005848  01/06/2021   39.73
8   1000005848  01/06/2021   39.73
9   1000005848  01/06/2021   39.73
10  1000005848  01/06/2021   39.73
11  1000005848  01/06/2021   39.73
12  1000005848  01/06/2021   39.73
13  1000005848  01/06/2021   39.73
14  1000005848  01/06/2021   39.73
15  1000005848  01/06/2021   39.73
16  1000005848  01/06/2021   39.73
17  1000005848  01/06/2021   39.73
user_79645270
Related