"cannot reindex from a duplicate axis" when groupby().apply() on MultiIndex columns (original) (raw)

Code Sample, a copy-pastable example if possible

import pandas as pd import numpy as np

df = pd.DataFrame( np.ones([6, 4], dtype=int), columns=pd.MultiIndex.from_product([['A', 'B'], [1, 2]]) )

( df .groupby(level=0, axis=1) .apply( lambda df: pd.concat( [df.xs(df.name, axis=1), df.sum(axis=1).to_frame('Total')], axis=1 ) ) )

Problem description

The code above produces the error:

cannot reindex from a duplicate axis

I believe this is a bug because, as described below, essentially the same operations can be successfully run along the other axis.

Expected Output

It should, as far as I can tell, produce the following output:

The desired output can be obtained when working on the transposed DataFrame along the index rather than the columns:

( df .T .groupby(level=0) .apply( lambda df: pd.concat( [df.xs(df.name), df.sum().to_frame('Total').T] ) ) .T )

Output of `pd.show_versions()`

Details

pd.show_versions()
pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 2.7.9.final.0
python-bits: 64
OS: Windows
OS-release: 8
machine: AMD64
processor: Intel64 Family 6 Model 69 Stepping 1, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None