Repository: pandas-dev/pandas
Pull request: BUG: pd.crosstab not working when margin and normalize are set together
- [ ] closes #27500 
- [ ] tests added / passed
- [ ] passes `black pandas`
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry


Problem statement:
crosstabs doesn't work with margin and normalize together
#### Code Sample, a copy-pastable example if possible
```python
import pandas as pd
df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
          "bar", "bar", "bar", "bar"],
                    "B": ["one", "one", "one", "two", "two",
                          "one", "one", "two", "two"],
                    "C": ["small", "large", "large", "small",
                          "small", "large", "small", "small",
                          "large"],
                    "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                    "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

pd.crosstab([df.A,df.B],df.C,margins=True,margins_name='Sub-Total',normalize=0)
```
#### Problem description

pandas.Crosstab:
As per the Documentation, For parameter normalize If margins is True, will also normalize margin values. However when I give normalize as True and margins as True with margin_names as a string then it throws following exception:

KeyError: "['Sub-Total'] not found in axis"

where Sub-Total is the margins_name String.

#### Expected Output
Normalize value of the Margin rows and columns

#### Output of ``pd.show_versions()``


INSTALLED VERSIONS
------------------
commit: None
python: 3.6.3.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 142 Stepping 9, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.24.2
pytest: 3.2.1
pip: 18.0
setuptools: 39.1.0
Cython: 0.26.1
numpy: 1.15.4
scipy: 1.0.0
pyarrow: 0.8.0
xarray: 0.10.7
IPython: 6.1.0
sphinx: 1.6.3
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.2
feather: None
matplotlib: 2.1.0
openpyxl: 2.4.8
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml.etree: 4.1.0
bs4: 4.6.0
html5lib: 0.9999999
sqlalchemy: 1.1.13
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None
None

Hints from issue discussion:
I think the issue here is the combination of normalize across the rows with a MultiIndex. Note if you did the following this works:

```python
pd.crosstab([df.A],df.C, margins=True, normalize=0)
```

But the MultiIndex you are creating doesn't

```python
pd.crosstab([df.A, df.B],df.C, margins=True, normalize=0)
```

The source for that is here - if you'd like to investigate and submit a PR to patch would certainly be welcome:

https://github.com/pandas-dev/pandas/blob/88ccb25e74c5e88bc393fa15790d341574f2bff8/pandas/core/reshape/pivot.py#L579
I think i found the bug, i will submit PR tonight.

Commit message: Correct codes

Patch to review:
diff --git a/doc/source/whatsnew/v0.25.1.rst b/doc/source/whatsnew/v0.25.1.rst
index fb67decb46b64..a628f79ea440d 100644
--- a/doc/source/whatsnew/v0.25.1.rst
+++ b/doc/source/whatsnew/v0.25.1.rst
@@ -129,7 +129,7 @@ Reshaping
 ^^^^^^^^^
 
 - A ``KeyError`` is now raised if ``.unstack()`` is called on a :class:`Series` or :class:`DataFrame` with a flat :class:`Index` passing a name which is not the correct one (:issue:`18303`)
--
+-  Bug in :meth:`DataFrame.crosstab` when margins set to True and normalize is not False, an error is raised. (:issue:`27500`)
 -
 
 Sparse
diff --git a/pandas/core/reshape/pivot.py b/pandas/core/reshape/pivot.py
index 79716520f6654..2a224c93c7314 100644
--- a/pandas/core/reshape/pivot.py
+++ b/pandas/core/reshape/pivot.py
@@ -612,12 +612,17 @@ def _normalize(table, normalize, margins, margins_name="All"):
 
     elif margins is True:
 
+        # keep index and column of pivoted table
+        table_index = table.index
+        table_columns = table.columns
+        # drop margins created in pivot_table and only keep the core
         column_margin = table.loc[:, margins_name].drop(margins_name)
-        index_margin = table.loc[margins_name, :].drop(margins_name)
+        # separate cases between multiindex and index
+        if isinstance(table_index, MultiIndex):
+            index_margin = table.loc[margins_name, :].drop(margins_name, axis=1)
+        else:
+            index_margin = table.loc[margins_name, :].drop(margins_name)
         table = table.drop(margins_name, axis=1).drop(margins_name)
-        # to keep index and columns names
-        table_index_names = table.index.names
-        table_columns_names = table.columns.names
 
         # Normalize core
         table = _normalize(table, normalize=normalize, margins=False)
@@ -627,11 +632,19 @@ def _normalize(table, normalize, margins, margins_name="All"):
             column_margin = column_margin / column_margin.sum()
             table = concat([table, column_margin], axis=1)
             table = table.fillna(0)
+            table.columns = table_columns
 
         elif normalize == "index":
-            index_margin = index_margin / index_margin.sum()
+            # index_margin is a dataframe, and use a hacky way: sum(axis=1)[0]
+            # to get the normalized result, and use sum() instead for series
+            if isinstance(index_margin, ABCDataFrame):
+                sum_index_margin = index_margin.sum(axis=1)[0]
+            else:
+                sum_index_margin = index_margin.sum()
+            index_margin = index_margin / sum_index_margin
             table = table.append(index_margin)
             table = table.fillna(0)
+            table.index = table_index
 
         elif normalize == "all" or normalize is True:
             column_margin = column_margin / column_margin.sum()
@@ -641,13 +654,12 @@ def _normalize(table, normalize, margins, margins_name="All"):
             table = table.append(index_margin)
 
             table = table.fillna(0)
+            table.index = table_index
+            table.columns = table_columns
 
         else:
             raise ValueError("Not a valid normalize argument")
 
-        table.index.names = table_index_names
-        table.columns.names = table_columns_names
-
     else:
         raise ValueError("Not a valid margins argument")
 
diff --git a/pandas/tests/reshape/test_pivot.py b/pandas/tests/reshape/test_pivot.py
index be82e7f595f8c..74dee9f68f8ea 100644
--- a/pandas/tests/reshape/test_pivot.py
+++ b/pandas/tests/reshape/test_pivot.py
@@ -2447,3 +2447,38 @@ def test_crosstab_unsorted_order(self):
             [[1, 0, 0], [0, 1, 0], [0, 0, 1]], index=e_idx, columns=e_columns
         )
         tm.assert_frame_equal(result, expected)
+
+    def test_margin_normalize(self):
+        df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
+                                 "bar", "bar", "bar", "bar"],
+                           "B": ["one", "one", "one", "two", "two",
+                                 "one", "one", "two", "two"],
+                           "C": ["small", "large", "large", "small",
+                                 "small", "large", "small", "small",
+                                 "large"],
+                           "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
+                           "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})
+        # normalize on index
+        result = pd.crosstab([df.A, df.B], df.C, margins=True, margins_name='Sub-Total',
+                             normalize=0)
+        expected = pd.DataFrame([[0.5, 0.5], [0.5, 0.5],
+                                 [0.666667, 0.333333], [0, 1],
+                                 [0.444444, 0.555556]])
+        expected.index = MultiIndex(levels=[['Sub-Total', 'bar', 'foo'],
+                                            ['', 'one', 'two']],
+                                    codes=[[1, 1, 2, 2, 0], [1, 2, 1, 2, 0]],
+                                    names=['A', 'B'])
+        expected.columns = Index(['large', 'small'], dtype='object', name='C')
+        tm.assert_frame_equal(result, expected)
+
+        # normalize on columns
+        result = pd.crosstab([df.A, df.B], df.C, margins=True, margins_name='Sub-Total',
+                             normalize=1)
+        expected = pd.DataFrame([[0.25, 0.2, 0.222222], [0.25, 0.2, 0.222222],
+                                 [0.5, 0.2, 0.333333], [0, 0.4, 0.222222]])
+        expected.columns = Index(['large', 'small', 'Sub-Total'], dtype='object',
+                                 name='C')
+        expected.index = MultiIndex(levels=[['bar', 'foo'], ['one', 'two']],
+                                    codes=[[0, 0, 1, 1], [0, 1, 0, 1]],
+                                    names=['A', 'B'])
+        tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -627,11 +632,19 @@ def _normalize(table, normalize, margins, margins_name="All"):
             column_margin = column_margin / column_margin.sum()
             table = concat([table, column_margin], axis=1)
             table = table.fillna(0)
+            table.columns = table_columns
 
         elif normalize == "index":
-            index_margin = index_margin / index_margin.sum()
+            # index_margin is a dataframe, and use a hacky way: sum(axis=1)[0]
+            # to get the normalized result, and use sum() instead for series
+            if isinstance(index_margin, ABCDataFrame):
path: pandas/core/reshape/pivot.py
text: @user1:
why do you need to distinguish MI here?

@author:
yes, after i changed the code above, this issue can be solve as well. And i removed this if/then. thank you for pointing it out @user1

diff_hunk: @@ -612,12 +612,17 @@ def _normalize(table, normalize, margins, margins_name="All"):
 
     elif margins is True:
 
+        # keep index and column of pivoted table
+        table_index = table.index
+        table_columns = table.columns
+        # drop margins created in pivot_table and only keep the core
         column_margin = table.loc[:, margins_name].drop(margins_name)
-        index_margin = table.loc[margins_name, :].drop(margins_name)
+        # separate cases between multiindex and index
+        if isinstance(table_index, MultiIndex):
+            index_margin = table.loc[margins_name, :].drop(margins_name, axis=1)
path: pandas/core/reshape/pivot.py
text: @user1:
why are we not always passing axis= here? I don't like the need for this if/then

@author:
Yeah, i figured out a way to walk it around, and also avoid MI problem, seems even test speed is faster ^^ @user1