Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

groupby level after stack not actually grouping #28301

Closed
christopherzimmerman opened this issue Sep 5, 2019 · 3 comments · Fixed by #28336
Closed

groupby level after stack not actually grouping #28301

christopherzimmerman opened this issue Sep 5, 2019 · 3 comments · Fixed by #28336
Labels
Bug Groupby MultiIndex Reshaping Concat, Merge/Join, Stack/Unstack, Explode
Milestone
1.0.0

Comments

@christopherzimmerman
Copy link
Contributor

christopherzimmerman commented Sep 5, 2019 •
edited
Loading

Code Sample, a copy-pastable example if possible

columns = pd.MultiIndex.from_product([['a', 'b'], [1, 2]])
index = [0, 0, 1, 1]
values = np.arange(16).reshape(-1, 4)

df = pd.DataFrame(values, columns=columns, index=index)

    a       b
    1   2   1   2
0   0   1   2   3
0   4   5   6   7
1   8   9  10  11
1  12  13  14  15

df.stack(1)

      a   b
0 1   0   2
  2   1   3
  1   4   6
  2   5   7
1 1   8  10
  2   9  11
  1  12  14
  2  13  15

# ACTUAL BEHAVIOR
df.stack(1).groupby(level=[0, 1]).sum()

      a   b
0 1   0   2
  2   1   3
  1   4   6
  2   5   7
1 1   8  10
  2   9  11
  1  12  14
  2  13  15

# EXPECTED

      a   b
0 1   4   8
  2   6  10
1 1  20  24
  2  22  26

# WORKAROUND

df.stack(1).reset_index().groupby(['level_0', 'level_1']).sum().rename_axis([None, None])

Problem description

When grouping by levels after stacking, no grouping seems to be taking place. However, after resetting the index and using those series to group, the groupby works as expected

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : None python : 3.6.7.final.0 python-bits : 64 OS : Windows OS-release : 10 machine : AMD64 processor : Intel64 Family 6 Model 58 Stepping 9, GenuineIntel byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : None.None

pandas : 0.25.0
numpy : 1.17.0
pytz : 2018.6
dateutil : 2.7.3
pip : 18.1
setuptools : 39.1.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.3.4
html5lib : 1.0.1
pymysql : None
psycopg2 : 2.7.5 (dt dec pq3 ext lo64)
jinja2 : 2.10.1
IPython : 7.2.0
pandas_datareader: None
bs4 : 4.7.1
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : 4.3.4
matplotlib : 3.0.0
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.2.1
sqlalchemy : 1.2.12
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
@christopherzimmerman
Copy link
Contributor Author

christopherzimmerman commented Sep 5, 2019 •
edited
Loading

Just found something strange, when I create the DataFrame created by the stack from scratch, the groupby seems to work:

index = pd.MultiIndex.from_product([[0, 0, 1, 1], [1, 2]])
columns = ['a', 'b']
values = np.arange(16).reshape(-1, 2)

scratch_df = pd.DataFrame(values, columns=columns, index=index)

      a   b
0 1   0   1
  2   2   3
  1   4   5
  2   6   7
1 1   8   9
  2  10  11
  1  12  13
  2  14  15

scratch_df.groupby(level=[0, 1]).sum()

      a   b
0 1   4   6
  2   8  10
1 1  20  22
  2  24  26

Yet as far as I can tell, the two indexes are equal:

>>> pd.testing.assert_index_equal(df.stack(1).index, scratch_df.index)

@WillAyd
Copy link
Member

WillAyd commented Sep 5, 2019

Hmm that is rather strange. Care to investigate? Relevant code is going to be in pandas.core.groupby

@WillAyd WillAyd added Bug Groupby MultiIndex Reshaping Concat, Merge/Join, Stack/Unstack, Explode labels Sep 5, 2019
@christopherzimmerman
Copy link
Contributor Author

christopherzimmerman commented Sep 5, 2019 •
edited
Loading

Looks like the issue is in stack as far as I can tell. For this DataFrame, this line of code is creating codes based on an arbitrary length of the DataFrame without taking into account duplicate index values, which are then used by the groupby.

>>> df.stack(1).index.codes
FrozenList([[0, 0, 1, 1, 2, 2, 3, 3], [0, 1, 0, 1, 0, 1, 0, 1]])

>>> scratch_df.index.codes
FrozenList([[0, 0, 0, 0, 1, 1, 1, 1], [0, 1, 0, 1, 0, 1, 0, 1]])

A naive solution to stack for a non-MultiIndex might be replacing this:

new_levels = [this.index]
new_codes = [np.arange(N).repeat(levsize)]
new_names = [this.index.name]  # something better?

with this:

ncodes, nlevels = _factorize_from_iterable(this.index)
new_levels = [nlevels]
new_codes = [ncodes.repeat(levsize)]
new_names = [this.index.name]  # something better?

But I'm not sure how that would transfer to the MultiIndex path.

It seems odd that pd.testing.assert_index_equal doesn't catch a difference in codes, maybe something else worth investigating?

@christopherzimmerman christopherzimmerman mentioned this issue Sep 7, 2019
Merged
5 tasks
@jreback jreback added this to the 1.0 milestone Sep 10, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug Groupby MultiIndex Reshaping Concat, Merge/Join, Stack/Unstack, Explode
Projects
None yet
Milestone
1.0.0
Development

Successfully merging a pull request may close this issue.

GH28301 check for non-unique index in stack_multi_columns
3 participants
@WillAyd @jreback @christopherzimmerman