Rounding calculated column to 1dp

Question

I am conscious this is a popular query but I haven't found anything on here that quite matches what I need. I have a column in a table that is rounding 36.25 to 36.2 rather than 36.3. Having read about this extensively on here I appreciate the details, however this doesn't help me solve my problem. How can I create some code that will round my 'Outcome' column as I wish please? This code produces a version of the df I am using:

import pandas as pd
import numpy as np

raw_data = {'AreaCode' : ['101', '102', '103', '104'],
            'Numerator' : [300, 500, 600, 667],
            'Denominator' : [1000, 780, 1100, 1840]}
Data = pd.DataFrame(raw_data, columns = ['AreaCode', 'Numerator', 'Denominator'])

And then i am trying to add an 'Outcome' column:

Data['Outcome'] = Data['Numerator'] / Data['Denominator'] * 100

Produces table below:

AreaCode|Numerator|Denominator|Outcome
101|300|1000|30.000000 
102|500|780|64.102564 
103|600|1100|54.545455
104|667|1840|36.25000

Which is fine except when I apply Data = Data.round(1) to this, it rounds 36.250000 to 36.2. I need this column to 1dp showing as 36.3 but how can I code this in Python. Examples on here of how to do this use strings of one-off, user-entered numbers, rather than an entire df.column. It won't let me pass my df.column into these. One example I tried which didn't work:

import math
def my_round(n, ndigits):
    part = n * 10 ** ndigits
    delta = part - int(part)
    # always round "away from 0"
    if delta >= 0.5 or -0.5 < delta <= 0:
        part = math.ceil(part)
    else:
        part = math.floor(part)
    return part / (10 ** ndigits

How about `round_up` function used here: https://www.knowledgehut.com/blog/programming/python-rounding-numbers — SKPS, Jan 28 '20 at 18:59
Does this answer your question? [floor and ceil with number of decimals](https://stackoverflow.com/questions/58065055/floor-and-ceil-with-number-of-decimals) — SKPS, Jan 28 '20 at 19:03

Celius Stingher · Accepted Answer · 2020-01-29T11:22:07.607

1

I made a little tweak to your function and it seems to work now :)

def my_round(n, ndigits=1):
    try:
       part = n * 10 ** ndigits
       delta = part - int(part)
       # always round "away from 0"
       if delta >= 0.5 or -0.5 < delta <= 0:
           part = math.ceil(part)
       else:
           part = math.floor(part)
       val =  part/(10 ** ndigits)
    except ValueError:
       val = np.nan
    return val

Data['Outcome'] = (Data['Numerator'].divide(Data['Denominator'])*100).apply(my_round)
print(Data)

Output:

  AreaCode  Numerator  Denominator  Outcome
0      101        300         1000     30.0
1      102        500          780     64.1
2      103        600         1100     54.5
3      104        667         1840     36.3

edited Jan 29 '20 at 11:22

answered Jan 28 '20 at 19:08

Celius Stingher

17,835
6
23
53

Thanks for this! I had hoped it was the solution but when I execute on the data I've imported it is returning the error 'ValueError: cannot convert float NaN to integer', despite me having used .fillna(0) to turn all these to zero. Is there something else I can add to the code above to eradicate this? – nnn1234 Jan 29 '20 at 07:29
Sure, the way I'd do it is using `try` and `except` check the edit and let me know if it works please! – Celius Stingher Jan 29 '20 at 11:20
1

This is superb thanks Celius, working perfectly as we need now. A massive help for us cheers – nnn1234 Jan 29 '20 at 12:54
Hi Celius. Is there any modification I can make to your ceil/floor code used above in order to use it to round to nearest 5 whole numbers? i.e. I have figures such as 702.5 rounding to 700 rather than 705 when I'm trying to round to 5. – nnn1234 Jan 31 '20 at 12:31

score 0 · Answer 2 · answered Jan 28 '20 at 19:27

0

After importing pandas to your notebook, you can add this line so that your float values will not exceed one decimal place:

import pandas as pd
pd.options.display.float_format = '{:.1f}'.format

answered Jan 28 '20 at 19:27

PugFanatic

31
2
9

Thank you for help with this. I need the actual calculation to round up though, not just display to 1dp. I am calculating NHS performance indicators and the 36.25 being shown as 36.2 won't be acceptable – nnn1234 Jan 29 '20 at 07:33

Rounding calculated column to 1dp

2 Answers2