BUG: prod and groupby.prod behave differently

[tf401]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = pd.DataFrame(
    data=[
        [5,	2, 17, 11, 7, 2, 3],
        [5,	2, 17, 41, 7, 2, 3],
        [5,	2, 17, 17, 7, 2, 3],
        [5,	2, 17, 37, 7, 2, 3],
        [5,	2, 17, 7, 7, 2, 3],
        [5,	2, 17, 29, 7, 2, 3],
        [5,	2, 17, 31, 7, 2, 3],
        [5,	2, 17, 2, 7, 2, 3],
        [5,	2, 17, 3, 7, 2, 3],
        [5,	2, 17, 43, 7, 2, 3],
        [5,	2, 17, 5, 7, 2, 3],
        [5,	2, 17, 47, 7, 2, 3],
        [5,	2, 17, 19, 7, 2, 3],
        [5,	2, 17, 23, 7, 2, 3],

    ],
    columns=['A','B','C','D','E','F','G']
)

df.prod()
#A            6103515625
#B                 16384
#C    168377826559400929
#D     47299214045268570 <-- correct value
#E          678223072849
#F                 16384
#G               4782969
#dtype: int64

df.groupby(['A','B','C','E','F','G']).prod()

#						D
#A	B	C	E	F	G	
#5	2	17	7	2	3	47299214045268568 <-- wrong value

Issue Description

During a project that uses pandas groupby prod method I noticed possible bug.

The reproducible example should be clear but the core issue is that is seems that pandas prod() method behaves different
than pandas groupby prod method. The result is inconsistent. In the example, please inspect column "D"

Expected Behavior

Correct output of column D during the groupby operation

Installed Versions

'1.4.1'

[mctessi]

@tf401
I have simplified your example a bit and discovered even weirder behavior:

import pandas as pd
print("pd version:", pd.__version__)

def factory(i):
    return pd.DataFrame(
        data=[
            [1, 11],
            [1, 41],
            [1, 17],
            [1, 37],
            [1, 7],
            [1, 29],
            [1, 31],
            [1, 2],
            [1, 3],
            [1, 43],
            [1, 5],
            [1, 47],
            [1, 19],  # up to this line everything is fine
            [1, i],  # this line triggers the bug
        ],
        columns=["a", "b"],
        dtype="int64",
    )


for i in range(90):
    df = factory(i)
    df_prod = df.prod()["b"]
    df_groupby_prod = df.groupby(["a"]).prod().iloc[0, 0]
    diff = df_prod - df_groupby_prod
    if diff != 0:
        print(
            f"i={i}, diff={diff}, df_prod={df_prod}, df_groupby_prod={df_groupby_prod} "
        )
#sample output:
#pd version: 1.4.1
#i=9, diff=-2, df_prod=18508388104670310, df_groupby_prod=18508388104670312 
#i=11, diff=2, df_prod=22621363239041490, df_groupby_prod=22621363239041488 
#i=13, diff=-2, df_prod=26734338373412670, df_groupby_prod=26734338373412672 
#i=15, diff=2, df_prod=30847313507783850, df_groupby_prod=30847313507783848 
#i=17, diff=-2, df_prod=34960288642155030, df_groupby_prod=34960288642155032 
#...
#i=23, diff=2, df_prod=47299214045268570, df_groupby_prod=47299214045268568 
#...
#i=84, diff=-8, df_prod=172744955643589560, df_groupby_prod=172744955643589568 
#i=85, diff=14, df_prod=174801443210775150, df_groupby_prod=174801443210775136 
#i=86, diff=4, df_prod=176857930777960740, df_groupby_prod=176857930777960736 
#i=87, diff=-6, df_prod=178914418345146330, df_groupby_prod=178914418345146336 
#i=88, diff=16, df_prod=180970905912331920, df_groupby_prod=180970905912331904 
#i=89, diff=6, df_prod=183027393479517510, df_groupby_prod=183027393479517504 

Remarkably, the difference is always a multiple of 2.
The calculated values are also well below max np.int64 of 9_223_372_036_854_775_807, so probably no simple overflow error!

[mctessi]

Found the bugging part! The groupby operation prod calculates correctly with dtype float64, but before returning lands in pandas/core/dtypes/cast.py in function maybe_downcast_numeric where the call to astype lands in pandas/core/arrays/base.py. Here the resulting numpy cast is failing:

df=factory(23) #to replicate the OP example
df.groupby(["a"]).prod() #calling code 

Minimal reproducing example is:

import numpy as np
np.array(4.729921404526857e16, dtype="float64").astype("int64")
#array(47299214045268568, dtype=int64)

numpy version: '1.22.3'

Edit:
seems to be a weird numpy thing! Floating point deviations from the real world are expected somewhere beyond >1e15, but why is the debugger able to display the true value...

np.array(4.729921404526857e16, dtype="float64").astype("int64") - np.array(47299214045268570, dtype="int64")
# -2
np.array(4.729921404526857e16, dtype="float64") - np.array(47299214045268570, dtype="int64")
# 0
np.array(4.729921404526857e16, dtype="float64") - np.array(47299214045268570, dtype="int64").astype('float')
# 0

Edit 2: the trans call in image 1 is not the problem!

[mctessi]

take

[mctessi]

@tf401
Although not resolving the issue, hopefully it helped a bit! The explanations is probably found somewhere in numpy's exact representation of doubles and casting rules! Will do a quick check tomorrow and maybe it can be circumvented somehow in pandas astype cast, which is essentially a numpy cast to the target format! Somewhere in the groupby prod call the datatype changes to float64 and that was unexpected! Will investigate further...

[tf401]

@mctessi
Great, thanks for providing additional examples and a drill down of the problematic part!
I guess that the pandas casting implementation of the standard prod() method and groupby.prod() differs under the hood?

[mctessi]

The standard prod call delegates directly (except for a gazilion sanity checks:) to the numpy prod method without anything in between and returns the result (and again with additional checks) :


In the groupby prod this is done in the Grouper class with a pandas build in cython method and somewhere the dtype is changing to float64 and the final numpy cast to the target dtype fails as shown above! By the way, the pandas astype does essentially nothing more than returning np.array(values,dtype=dtype)

Edit: To mitigate this problem in the short term, you could use something like a c-style macro

def LARGE_EQUALS(x, y):
    return abs(x - y) < YOUR_THRESHOLD

for huge values. Obviously it all depends on your exact usecase and is a bit dirty...

[mctessi]

Just wanted to add: my suspicion is that the underlying c function prod in groupby has a signature like double(double[]) and the final 'downcast' cannot be avoided at the moment. If so I will try to find a way to intercept this call for int64 groups and instead use the numpy prod implementation, which can deal with these large integers. Might take a while though...

[tf401]

any update on this?