Incorrect sum in python pivot table

Question

I am trying to do a pivot in python using pandas pivot_table. When I am aggregating a column with huge(more than 10 digits with 3-4 numbers beyond decimals point) floating-point numbers using both "np.sum" and just "sum", getting wrong results. Below sharing an example.

data = pd.DataFrame({"store":["A","B","C","D"],
                      "sales":[11046021.3675,9222589.4978,3851017.2855,8284985.4983],
                      "place":["P","P","Q","Q"]})

code1 = pd.pivot_table(data,index="place",aggfunc={"sales":"sum"})

code2 = pd.pivot_table(data,index="place",aggfunc={"sales":np.sum})

Ideally, we should get sum of "P" as 20268610.8653 but with the above codes, it comes out 20268600. Does anyone know what is wrong here and can anyone please guide me on how to resolve the above discrepancy?

your code works fine, and returns floats when I ran it on my PC. my pandas version is 1.2.4 — sammywemmy, May 01 '21 at 10:18
can't reproduce on my end either (pd 1.2.3): `code1.loc['P','sales']` => 20268610.8653 — tdy, May 01 '21 at 10:18
try `pd.set_option('display.float_format', lambda x: '%.4' % x)` and then run. Maybe it's getting truncated — Shubham Periwal, May 01 '21 at 10:28
Try styling/formatting the output: pd.pivot_table(data,index="place",aggfunc={"sales":"sum"}).style.format("{:,.4f}") — Utsav, May 01 '21 at 10:32
can you post your pandas version? You may run `pip list` in any terminal to get it — Shubham Periwal, May 01 '21 at 10:45
@Utsav. I am using spyder.Not sure if this works as I am not able to see the variable in the variable explorer after running that styling command. — Prasad Patil, May 01 '21 at 10:51

score 0 · Answer 1 · answered May 01 '21 at 11:10

0

I think you can just try pip3 install --upgrade pandas to upgrade the version and run the code. Your code is fine. It might be a version issue

answered May 01 '21 at 11:10

Shubham Periwal

2,198
2
8
26

1

I have upgraded to pandas "1.2.4" but the issue still persists. – Prasad Patil May 01 '21 at 11:24
https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency this must answer it for you. Create a big decimal data type and increase your precision levels – Shreyas Moolya May 01 '21 at 19:07

score 0 · Accepted Answer · answered May 03 '21 at 14:07

0

So there is a loophole here, I updated to the latest python & spyder version I get the correct answer when "I am printing the variable" not when I am seeing the answer in variable explorer. In variable explorer, the value is in exponentials and when I do copy-paste it in excel I find the discrepancy.

answered May 03 '21 at 14:07

Prasad Patil

45
1
8

1

I highly suggest saving and loading the data via files (e.g., `to_csv`). Copy-pasting relies on display formatting which is never totally precise. Even without the sci notation issue, you would lose raw float precision by copy-pasting. – tdy May 06 '21 at 03:33
1

Point taken, I was just checking at each step whether I was getting the correct number or not on variable explorer and there I came across this. – Prasad Patil May 06 '21 at 07:12

Incorrect sum in python pivot table

2 Answers2