Pandas version checks
-
[X] I have checked that this issue has not already been reported.
-
[X] 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
d1 = pd.DataFrame({
'ref': ['ref', 'ref'],
'date': pd.to_datetime(['2019-01-31', '2019-02-01']),
'value': [100.0, 100.0],
}).set_index(['ref', 'date'])
d2 = pd.DataFrame({
'ref': ['ref', 'ref'],
'date': pd.to_datetime(['2019-02-01', '2019-02-01']),
'code': ['A', 'B'],
'value': [0.75, 0.25],
}).set_index(['ref', 'date', 'code'])
d12 = d1 * d2
d21 = d2 * d1
print(d12)
print(d21)
print(d12.equals(d21))
code = ['A']
r12 = d12.query('code in @code').reset_index('ref', drop=True).get('value').loc[:'2019-01-31']
r21 = d21.query('code in @code').reset_index('ref', drop=True).get('value').loc[:'2019-01-31']
print(r12)
print(r21) #wrong result
Issue Description
As d12 and d21 are equal to each other so the outputs from r12 and r21 should be the same.
The result for r12 is correct
Series([], Name: value, dtype: float64)
But the result for r21 is wrong
date code
2019-02-01 A 75.0
Name: value, dtype: float64
Expected Behavior
r21 should also be an empty Series same as r12.
In pandas 1.5.3 both r12 and r21 are an empty Series.
Installed Versions
Comment From: rhshadrach
Thanks for the report @seanslma. Can you narrow down the operation the first produces what you see as a wrong result? Currently you are chaining together many operations. You should be able to narrow this down to a single input and a single operation that you believe produces an incorrect result.
Comment From: seanslma
Thanks @rhshadrach.
I tried to simplify the example but not able to - good news is that I got some extra info that might be helpful for the investigation.
Input DataFrame 1 & 2
d1 = pd.DataFrame(
[100.0, 100.0],
columns=['value'],
index=pd.MultiIndex.from_arrays([pd.to_datetime(['2019-01-31', '2019-02-01']),['ref', 'ref']], names=[ 'date', 'ref']),
)
d2 = pd.DataFrame(
[0.75, 0.25],
columns=['value'],
index=pd.MultiIndex.from_arrays([pd.to_datetime(['2019-02-01', '2019-02-01']), ['ref', 'ref'], ['A', 'B']], names=['date', 'ref', 'code']),
)
Results DataFrame. Note that d1 * d2 will produce correct results
d21 = d2 * d1
r21 = d21.query('code == "A"').loc[:'2019-01-31']
Also you can try slicing without chaining
idx = pd.IndexSlice
d21.loc[idx[:'2019-01-31',:,:],:]
UnsortedIndexError: 'MultiIndex slicing requires the index to be lexsorted: slicing on levels [0], lexsort depth 0'
Comment From: attack68
The assertion here is that d1 * d2 and d2 * d1 should produce the same result. But d1 and d2 have different indexes and are different objects. Is the operation of DataFrame multiplication defined to be communtative? If not (and perhaps there is a reason that it is not), then your argument is invalid, otherwise this should be inspected further.
Comment From: phofl
No it's not commutative. We do reindexing ops under the hood which probably cause the differences
Comment From: seanslma
Even the multiplication is not commutative, the results for d2 * d1 is obviously wrong.
Also the function equals is questionable as d1 * d2 != d2 * d1 but d12.equals(d21) returns True.
Perhaps we should never use the multiplication - can lead to unexpected wrong results.
Comment From: phofl
The index is constructed incorrectly during alignment, everything else works as expected