Repository: pandas-dev/pandas
Pull request: read_excel() modifies provided types dict when accessing file with duplicate column
- [x] closes #42462
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry


Problem statement:
read_excel() modifies provided types dict when accessing file with duplicate column
- [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.
- [ ] (optional) I have confirmed this bug exists on the master branch of pandas.


_test.xlsx :_
| a | a | b | c |
|---|---|---|---|
| 1 | 1 | b1| c1|
| 2 | 2 | b2| c2|
| 3 | 3 | b3| c3|

```
import pandas as pd


types_dict = {'a': str,
             'b': str,
             'c': str,
             }


if __name__ == "__main__":
    df = pd.read_excel('./test.xlsx', dtype=type_dict)
    print(list(type_dict.keys()))
```

```
>> ['a', 'b', 'c', 'a.1']
```

**Bug/Issue description:**
When using dtype loading a .xlsx-file with a duplicate column into a dataframe modifies the provided types_dict / adds entries for duplicate columns.

It seems to me like the modification of the types_dict is an unwanted side effect.

Hints from issue discussion:
Thanks for reporting this @cdol! Confirmed on master (seems limited to `read_excel`, doesn't show up for similar example with `read_csv`), agree it is an unwelcome side effect. Fixes very welcome!

Commit message: BUG: added xlsb

Patch to review:
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index 4d6a766ad6cfa..07cbde001a881 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -358,6 +358,10 @@ def read_excel(
     mangle_dupe_cols=True,
     storage_options: StorageOptions = None,
 ):
+    kwargs = locals().copy()
+    for each in kwargs:
+        if isinstance(locals()[each], dict):
+            kwargs[each] = locals()[each].copy()
 
     should_close = False
     if not isinstance(io, ExcelFile):
@@ -369,32 +373,9 @@ def read_excel(
             "an ExcelFile - ExcelFile already has the engine set"
         )
 
+    del kwargs["io"], kwargs["engine"], kwargs["storage_options"]
     try:
-        data = io.parse(
-            sheet_name=sheet_name,
-            header=header,
-            names=names,
-            index_col=index_col,
-            usecols=usecols,
-            squeeze=squeeze,
-            dtype=dtype,
-            converters=converters,
-            true_values=true_values,
-            false_values=false_values,
-            skiprows=skiprows,
-            nrows=nrows,
-            na_values=na_values,
-            keep_default_na=keep_default_na,
-            na_filter=na_filter,
-            verbose=verbose,
-            parse_dates=parse_dates,
-            date_parser=date_parser,
-            thousands=thousands,
-            comment=comment,
-            skipfooter=skipfooter,
-            convert_float=convert_float,
-            mangle_dupe_cols=mangle_dupe_cols,
-        )
+        data = io.parse(**kwargs)
     finally:
         # make sure to close opened file handles
         if should_close:
diff --git a/pandas/tests/io/data/excel/test_common_headers.ods b/pandas/tests/io/data/excel/test_common_headers.ods
new file mode 100644
index 0000000000000..03595c18e8aea
Binary files /dev/null and b/pandas/tests/io/data/excel/test_common_headers.ods differ
diff --git a/pandas/tests/io/data/excel/test_common_headers.xls b/pandas/tests/io/data/excel/test_common_headers.xls
new file mode 100644
index 0000000000000..c02dc69aac93b
Binary files /dev/null and b/pandas/tests/io/data/excel/test_common_headers.xls differ
diff --git a/pandas/tests/io/data/excel/test_common_headers.xlsb b/pandas/tests/io/data/excel/test_common_headers.xlsb
new file mode 100644
index 0000000000000..98a38ab17f5c2
Binary files /dev/null and b/pandas/tests/io/data/excel/test_common_headers.xlsb differ
diff --git a/pandas/tests/io/data/excel/test_common_headers.xlsm b/pandas/tests/io/data/excel/test_common_headers.xlsm
new file mode 100644
index 0000000000000..9e5187bda8dc1
Binary files /dev/null and b/pandas/tests/io/data/excel/test_common_headers.xlsm differ
diff --git a/pandas/tests/io/data/excel/test_common_headers.xlsx b/pandas/tests/io/data/excel/test_common_headers.xlsx
new file mode 100644
index 0000000000000..6db5d1bd089f9
Binary files /dev/null and b/pandas/tests/io/data/excel/test_common_headers.xlsx differ
diff --git a/pandas/tests/io/excel/test_readers.py b/pandas/tests/io/excel/test_readers.py
index cbd241ceda0b1..1ce447828f5b9 100644
--- a/pandas/tests/io/excel/test_readers.py
+++ b/pandas/tests/io/excel/test_readers.py
@@ -1278,6 +1278,13 @@ def test_ignore_chartsheets_by_int(self, request, read_ext):
         ):
             pd.read_excel("chartsheet" + read_ext, sheet_name=1)
 
+    def test_dtype_dict(self, read_ext):
+        filename = "test_common_headers" + read_ext
+        dtype_dict = {"a": str, "b": str, "c": str}
+        dtype_dict_copy = dtype_dict.copy()
+        pd.read_excel(filename, dtype=dtype_dict)
+        assert dtype_dict == dtype_dict_copy, "dtype dict changed"
+
 
 class TestExcelFileRead:
     @pytest.fixture(autouse=True)


Code review comments:
diff_hunk: @@ -358,6 +358,10 @@ def read_excel(
     mangle_dupe_cols=True,
     storage_options: StorageOptions = None,
 ):
+    kwargs = locals().copy()
+    for each in kwargs:
+        if isinstance(locals()[each], dict):
+            kwargs[each] = locals()[each].copy()
path: pandas/io/excel/_base.py
text: @user1:
This is essentially trying to do a deepcopy?

@author:
Hi @user1 . Yes it is. I tried to use deepcopy first, but it was failing multiple tests.

I got the idea of creating a copy of kwargs from read_csv (which as you mentioned doesn't produce this unnecessary side effect). So I thought it would be a good idea to keep it consistent.

https://github.com/pandas-dev/pandas/blob/d60e6870f9c69d3407a5edb2298cb2181bc0bb83/pandas/io/parsers/readers.py#L566-L586

@user1:
> I got the idea of creating a copy of kwargs from read_csv (which as you mentioned doesn't produce this unnecessary side effect). So I thought it would be a good idea to keep it consistent.

Makes sense.  That case is a bit different though because `kwargs` itself was being modified. Here the issue is limited to the `dtypes` dict.  If there's a clean way to copy (or not modify in place) the dtypes dict, I _think_ that would be cleaner. 



@author:
Sure. I will work along those lines. Thanks!

@user1:
Let me know if you run into any issues!

diff_hunk: @@ -1278,6 +1278,13 @@ def test_ignore_chartsheets_by_int(self, request, read_ext):
         ):
             pd.read_excel("chartsheet" + read_ext, sheet_name=1)
 
+    def test_dtype_dict(self, read_ext):
path: pandas/tests/io/excel/test_readers.py
text: @user1:
Can you please leave a link to the relevant github issue here? And maybe also make the name of this test more specific, eg in this care we about about `dtype` argument not being modified in the case of duplicate columns being present