{"action":"created","issue":{"url":"https://api.github.com/repos/pandas-dev/pandas/issues/14704","repository_url":"https://api.github.com/repos/pandas-dev/pandas","labels_url":"https://api.github.com/repos/pandas-dev/pandas/issues/14704/labels{/name}","comments_url":"https://api.github.com/repos/pandas-dev/pandas/issues/14704/comments","events_url":"https://api.github.com/repos/pandas-dev/pandas/issues/14704/events","html_url":"https://github.com/pandas-dev/pandas/issues/14704","id":190839827,"number":14704,"title":"read_csv(...,mangle_dupe_cols=True) causes silent data loss for certain column names. Request introduction of mangle_dupe_cols_str","user":{"login":"wilsoc5","id":11683051,"avatar_url":"https://avatars.githubusercontent.com/u/11683051?v=3","gravatar_id":"","url":"https://api.github.com/users/wilsoc5","html_url":"https://github.com/wilsoc5","followers_url":"https://api.github.com/users/wilsoc5/followers","following_url":"https://api.github.com/users/wilsoc5/following{/other_user}","gists_url":"https://api.github.com/users/wilsoc5/gists{/gist_id}","starred_url":"https://api.github.com/users/wilsoc5/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wilsoc5/subscriptions","organizations_url":"https://api.github.com/users/wilsoc5/orgs","repos_url":"https://api.github.com/users/wilsoc5/repos","events_url":"https://api.github.com/users/wilsoc5/events{/privacy}","received_events_url":"https://api.github.com/users/wilsoc5/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-11-21T21:25:11Z","updated_at":"2016-11-22T00:55:38Z","closed_at":null,"body":"Lets say we have data with both column and row headers. Lets say this dataset also simply outputs a ```0``` in that csv file for the cell at the intersection of the column and row headers (A1 in excel notation, or cell (0,0) in the csv file). Additionally, both ```\"0\"``` and ```\"0.1\"``` are valid column names:\r\n\r\nRH\\\\CH|-0.1|0|0.1\r\n---|---|---|---\r\n10.0|123|456|789\r\n20.0|012|345|678\r\n\r\nThus, \"RH\\\\CH\" is replaced by ```\"0\"``` on export (of which I have no control). \r\n\r\n```python\r\nimport pandas as pd\r\nfrom cStringIO import StringIO\r\n\r\ndata = '0,-0.1,0,0.1\\n10.0,123,456,789\\n20.0,012,345,678'\r\ndf = pd.read_csv(StringIO(data),index_col=0,header=0)\r\nprint(df)\r\n\r\n      -0.1  0.1  0.1\r\n0                   \r\n10.0   123  789  789\r\n20.0    12  678  678\r\n\r\n```\r\n\r\nThe name mangling will change the duplicate ```\"0\"``` column containing data to ```\"0.1\"```, thus if a real data column has the name ```\"0.1\"```, this data will be copied (?) back to the mangled duplicate ```\"0\"``` column. Additionally, if the true ```\"0.1\"``` data name is missing, then this name mangling is frustrating as there is no obvious way to determine if the now present ```\"0.1\"``` column is a duplicate ```\"0\"``` that has been mangled or is a real ```\"0.1\"``` data series.\r\n\r\nI propose the addition of a ```mangle_dupe_cols_str``` keyword option that defaults to ```'.'``` to preserve the current behavior. However, it can be passed as a kwarg to ```read_csv``` in cases where the period name mangling could result in further duplicate columns.\r\n\r\nIn https://github.com/pandas-dev/pandas/blob/v0.19.1/pandas/io/parsers.py#L2109-L2111:\r\n```python\r\n                        if cur_count > 0:\r\n                            this_columns[i] = '%s.%d' % (col, cur_count)\r\n                        counts[col] = cur_count + 1\r\n```\r\nshould be adapted to \r\n```python\r\n                        if cur_count > 0:\r\n                            this_columns[i] = '%s%s%d' % (col, self.mangle_dupe_cols_str, cur_count)\r\n                        counts[col] = cur_count + 1\r\n```\r\nand lines 1042-1043 in https://github.com/pandas-dev/pandas/blob/v0.19.1/pandas/io/parsers.py#L1042-L1043 changed to:\r\n```python\r\n        self.mangle_dupe_cols = kwds.get('mangle_dupe_cols', True)\r\n        self.mangle_dupe_cols_str = kwds.get('mangle_dupe_cols_str', '.')        \r\n        self.infer_datetime_format = kwds.pop('infer_datetime_format', False)\r\n```\r\n\r\n#### Expected Output\r\nThen the corrected output would be:\r\n```python\r\n>>> data = '0,-0.1,0,0.1\\n10.0,123,456,789\\n20.0,012,345,678'\r\n>>> df = pd.read_csv(StringIO(data),index_col=0,header=0,mangle_dupe_cols_str='__')\r\n>>> print(df)\r\n\r\n      -0.1  0__1  0.1\r\n0                   \r\n10.0   123  456  789\r\n20.0    12  345  678\r\n\r\n```\r\nwhere subsequent operations could identify any columns with the mangle_dupe_cols_str, if needed. The current behavior silently causes data loss for certain column names.\r\n\r\n<details>\r\n>>> pd.show_versions()\r\n\r\nINSTALLED VERSIONS\r\n------------------\r\ncommit: None\r\npython: 2.7.12.final.0\r\npython-bits: 64\r\nOS: Windows\r\nOS-release: 7\r\nmachine: AMD64\r\nprocessor: Intel64 Family 6 Model 60 Stepping 3, GenuineIntel\r\nbyteorder: little\r\nLC_ALL: None\r\nLANG: en_US\r\n\r\npandas: 0.18.1\r\nnose: 1.3.7\r\npip: 8.1.2\r\nsetuptools: None\r\nCython: 0.24.1\r\nnumpy: 1.11.1\r\nscipy: 0.18.1\r\nstatsmodels: None\r\nxarray: None\r\nIPython: 5.1.0\r\nsphinx: 1.4.6\r\npatsy: 0.4.1\r\ndateutil: 2.5.3\r\npytz: 2016.6.1\r\nblosc: None\r\nbottleneck: 1.1.0\r\ntables: 3.2.2\r\nnumexpr: 2.6.1\r\nmatplotlib: 1.5.3\r\nopenpyxl: 2.3.2\r\nxlrd: 1.0.0\r\nxlwt: 1.1.2\r\nxlsxwriter: 0.9.3\r\nlxml: 3.6.4\r\nbs4: 4.5.1\r\nhtml5lib: None\r\nhttplib2: None\r\napiclient: None\r\nsqlalchemy: 1.1.3\r\npymysql: None\r\npsycopg2: 2.6.2 (dt dec pq3 ext lo64)\r\njinja2: 2.8\r\nboto: 2.42.0\r\npandas_datareader: None\r\n\r\n</details>\r\n"},"comment":{"url":"https://api.github.com/repos/pandas-dev/pandas/issues/comments/262115663","html_url":"https://github.com/pandas-dev/pandas/issues/14704#issuecomment-262115663","issue_url":"https://api.github.com/repos/pandas-dev/pandas/issues/14704","id":262115663,"user":{"login":"sinhrks","id":1696302,"avatar_url":"https://avatars.githubusercontent.com/u/1696302?v=3","gravatar_id":"","url":"https://api.github.com/users/sinhrks","html_url":"https://github.com/sinhrks","followers_url":"https://api.github.com/users/sinhrks/followers","following_url":"https://api.github.com/users/sinhrks/following{/other_user}","gists_url":"https://api.github.com/users/sinhrks/gists{/gist_id}","starred_url":"https://api.github.com/users/sinhrks/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sinhrks/subscriptions","organizations_url":"https://api.github.com/users/sinhrks/orgs","repos_url":"https://api.github.com/users/sinhrks/repos","events_url":"https://api.github.com/users/sinhrks/events{/privacy}","received_events_url":"https://api.github.com/users/sinhrks/received_events","type":"User","site_admin":false},"created_at":"2016-11-22T00:55:38Z","updated_at":"2016-11-22T00:55:38Z","body":"I think using ``names`` is simple in such complex cases.\r\n\r\nI'm slightly -1 to add further less-frequently used options to ``read_csv``. One idea is to additionally allow ``mangle_dupe_cols`` to accept ``str`` (mangled using specified ``str``).\r\n"}}
{"actor":{"display_login":"sinhrks"}}