1

Good afternoon,

I would like to round columns in a dataframe to x number of places using the round half up function to ensure that any .5 values are always rounded up as per conventional rounding rules and to avoid the "bankers rounding" issue.

The dataframe sample I have is:

import pandas as pd
import decimal

Data = {'Code' : ['x', 'x', 'x'],
        'Disaggregation' : ['a', 'b', 'Total'],
        'Numerator' : [19.3276542, 82.136492834, 101.192747123]}
       
Data = pd.DataFrame(Data, columns = ['Code', 'Disaggregation', 'Numerator'])

The code I have got, which does not work is as follow:

Data['Numerator'] = (Decimal(Data['Numerator']).quantize(Decimal('.1'), rounding=ROUND_HALF_UP))

The following error is produced: conversion from Series to Decimal is not supported.

Dtypes of the dataframe is:

Code               object
Disaggregation     object
Numerator         float64
dtype: object

Anyone have any clues how I can get this to work? (Of course the dataframe is much larger, thus I need to work on the column).

Thanks very much for this help on this in advance.

Ajk89
  • 13
  • 5

3 Answers3

1

try:

Data['Numerator'] = Data.Numerator.apply(lambda x : round(x, 1))

change the number to your desired rounding value

output:

Code    Disaggregation  Numerator
0   x   a               19.3
1   x   b               82.1
2   x   Total           101.2
JaySabir
  • 322
  • 1
  • 10
  • Thanks but this doesn't quite work. If I was to round 82.1365 to 3 d.p it gives me 82.136, not 82.137 as per general rules of rounding halfs up. – Ajk89 Jul 24 '20 at 10:37
  • python built-in round function has this behavior. This is not a bug: it’s a result of the fact that most decimal fractions can’t be represented exactly as a float. https://docs.python.org/3/tutorial/floatingpoint.html#tut-fp-issues – JaySabir Jul 28 '20 at 07:16
1

You are performing the rounding operation passing a series as an argument. Instead you need to fix this to perform the rounding up for each value in the series. I suggest you use map with a lambda in the function to do it:

Data['Numerator'] = Data['Numerator'].map(lambda x: Decimal(x).quantize(Decimal('.1'), rounding=ROUND_HALF_UP))

The output we get is as expected:

  Code Disaggregation Numerator
0    x              a      19.3
1    x              b      82.1
2    x          Total     101.2
Celius Stingher
  • 17,835
  • 6
  • 23
  • 53
  • Thanks but whenever I run this I get the following: 'module' object is not callable. I begin by importing pandas as pd and importing decimal. Is this correct or do I need something else? – Ajk89 Jul 24 '20 at 10:32
  • Ah yes, `from decimal import *` is what I used instead of `import decimal` – Celius Stingher Jul 24 '20 at 12:03
  • Still can't get it to work correctly (if indeed it is possible). If I try to round something like 82.13546, excel roundup function to 3 dp would result in 82.136, whereas in Python it goes to 82.135. (This is important as it is published data) – Ajk89 Jul 24 '20 at 15:58
  • So you want to **ALWAYS** round up? Because in your example, 82.13546 is not a .5 value, .46 gets correctly rounded down. Or do you mean to double round? .46 to .5 and then round up? – Celius Stingher Jul 24 '20 at 16:37
  • Apologies, i was mistaken. I've tried tweaking your code to test on different numbers etc just for robustness and found that when i tried to round the following: 19.3275, 82.1355, 101.1925 to 3dp, only the first one rounds correctly. Outputs I was getting were: 19.328, 82.135, 101.192. I need them to round up if 5 which is what I assumed the round _half_up did? – Ajk89 Jul 26 '20 at 01:50
1

Late for the party. Try to convert your float numbers to text before applying the Decimal(). You will get the result of ROUND_HALF_UP.

import pandas as pd
from decimal import Decimal, ROUND_HALF_UP

Data = {'Code' : ['x', 'x', 'x'],
        'Disaggregation' : ['a', 'b', 'Total'],
        'Numerator' : [19.3276, 82.1365, 101.1927]}

Data = pd.DataFrame(Data, columns = ['Code', 'Disaggregation', 'Numerator'])
Data['Numerator'].map(lambda x: Decimal(str(x)).quantize(Decimal('.100'), rounding=ROUND_HALF_UP))

This is what I ended with.

  • 19.328
  • 82.137
  • 101.193
Zeno
  • 331
  • 2
  • 5