{"action":"created","issue":{"url":"https://api.github.com/repos/pandas-dev/pandas/issues/16690","repository_url":"https://api.github.com/repos/pandas-dev/pandas","labels_url":"https://api.github.com/repos/pandas-dev/pandas/issues/16690/labels{/name}","comments_url":"https://api.github.com/repos/pandas-dev/pandas/issues/16690/comments","events_url":"https://api.github.com/repos/pandas-dev/pandas/issues/16690/events","html_url":"https://github.com/pandas-dev/pandas/issues/16690","id":235590155,"number":16690,"title":"KeyError from pandas DataFrame groupby for Windows based csv files","user":{"login":"suntong","id":422244,"avatar_url":"https://avatars2.githubusercontent.com/u/422244?v=3","gravatar_id":"","url":"https://api.github.com/users/suntong","html_url":"https://github.com/suntong","followers_url":"https://api.github.com/users/suntong/followers","following_url":"https://api.github.com/users/suntong/following{/other_user}","gists_url":"https://api.github.com/users/suntong/gists{/gist_id}","starred_url":"https://api.github.com/users/suntong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/suntong/subscriptions","organizations_url":"https://api.github.com/users/suntong/orgs","repos_url":"https://api.github.com/users/suntong/repos","events_url":"https://api.github.com/users/suntong/events{/privacy}","received_events_url":"https://api.github.com/users/suntong/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-06-13T15:15:01Z","updated_at":"2017-06-13T15:31:46Z","closed_at":null,"body":"#### Code Sample, a copy-pastable example if possible\r\n\r\n```python\r\n# Your code here\r\ndf = pd.read_csv('test.csv')\r\ndf.tail(5)\r\ndf.info()\r\ndf.columns\r\ndf.groupby(['Id'])\r\n```\r\n#### Problem description\r\n\r\nThe `df.groupby(['Id'])` threw an exception:\r\n\r\n```\r\nKeyErrorTraceback (most recent call last)\r\n<ipython-input-24-bba5c2dc5f75> in <module>()\r\n----> 1 df.groupby(['Id'])\r\n\r\n/usr/local/lib/python2.7/dist-packages/pandas/core/generic.pyc in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, **kwargs)\r\n   3776         return groupby(self, by=by, axis=axis, level=level, as_index=as_index,\r\n   3777                        sort=sort, group_keys=group_keys, squeeze=squeeze,\r\n-> 3778                        **kwargs)\r\n...\r\n/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in get(self, item, fastpath)\r\n   3288 \r\n   3289             if not isnull(item):\r\n-> 3290                 loc = self.items.get_loc(item)\r\n   3291             else:\r\n   3292                 indexer = np.arange(len(self.items))[isnull(self.items)]\r\n\r\n/usr/local/lib/python2.7/dist-packages/pandas/indexes/base.pyc in get_loc(self, key, method, tolerance)\r\n   1945                 return self._engine.get_loc(key)\r\n   1946             except KeyError:\r\n-> 1947                 return self._engine.get_loc(self._maybe_cast_indexer(key))\r\n   1948 \r\n   1949         indexer = self.get_indexer([key], method=method, tolerance=tolerance)\r\n\r\npandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)()\r\n\r\npandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4018)()\r\n\r\npandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12368)()\r\n\r\npandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12322)()\r\n\r\nKeyError: 'Id'\r\n```\r\n\r\n#### Expected Output\r\n\r\nNo exception. Returns `pandas.core.groupby.DataFrameGroupBy object`.\r\n\r\n#### Output of ``pd.show_versions()``\r\n\r\n<details>\r\n# Paste the output here pd.show_versions() here\r\n\r\nINSTALLED VERSIONS\r\n------------------\r\ncommit: None\r\npython: 2.7.12.final.0\r\npython-bits: 64\r\nOS: Linux\r\nOS-release: 4.4.0-57-generic\r\nmachine: x86_64\r\nprocessor: x86_64\r\nbyteorder: little\r\nLC_ALL: en_US.UTF-8\r\nLANG: en_US.UTF-8\r\n\r\npandas: 0.18.1\r\nnose: 1.3.4\r\npip: 8.1.2\r\nsetuptools: None\r\nCython: 0.19.2\r\nnumpy: 1.11.1\r\nscipy: 0.17.1\r\nstatsmodels: 0.8.0\r\nxarray: None\r\nIPython: 5.0.0\r\nsphinx: None\r\npatsy: 0.4.1\r\ndateutil: 2.5.3\r\npytz: 2016.4\r\nblosc: None\r\nbottleneck: None\r\ntables: 3.0.0\r\nnumexpr: 2.2.2\r\nmatplotlib: 2.0.2\r\nopenpyxl: None\r\nxlrd: None\r\nxlwt: None\r\nxlsxwriter: None\r\nlxml: None\r\nbs4: None\r\nhtml5lib: 0.999\r\nhttplib2: None\r\napiclient: None\r\nsqlalchemy: None\r\npymysql: None\r\npsycopg2: 2.4.5 (dt dec mx pq3 ext)\r\njinja2: 2.8\r\nboto: None\r\npandas_datareader: None\r\n</details>\r\n\r\n#### Further explanation\r\n\r\nThere are time-formatted string in the csv file, but that's not the cause:\r\n\r\n```\r\n$ head test.csv\r\nId,Time,Val\r\nT5115,00:00:00,20.819\r\nT5115,00:00:03,16.926\r\nT5115,00:00:08,11.343\r\nT5115,00:00:17,6.975\r\nT5115,00:00:29,13.553\r\nT5115,00:00:35,7.082\r\nT5115,00:00:49,8.147\r\nT5115,00:01:03,12.317\r\nT5115,00:01:07,6.171\r\n```\r\n\r\nI am certain that the real problem is the format of the file -- the \"test.csv\" is Windows based, and is output from SQL Server SSMS. \r\n\r\nUsing `file test.csv` under Linux shows:\r\n\r\n    test.csv: UTF-8 Unicode (with BOM) text, with CRLF line terminators\r\n\r\nHere are the top several bytes from the file:\r\n\r\n    0000000 ef bb bf 49 64 2c 54 69 - 6d 65 2c 56 61 6c 0d 0a  Id,Time,Val..\r\n    0000020 54 35 31 31 35 2c 30 30 - 3a 30 30 3a 30 30 2c 32  T5115,00:00:00,2\r\n    0000040 30 2e 38 31 39 0d 0a 54 - 35 31 31 35 2c 30 30 3a  0.819..T5115,00:\r\n    0000060 30 30 3a 30 33 2c 31 36 - 2e 39 32 36 0d 0a 54 35  00:03,16.926..T5\r\n    0000100 31 31 35 2c 30 30 3a 30 - 30 3a 30 38 2c 31 31 2e  115,00:00:08,11.\r\n    0000120 33 34 33 0d 0a 54 35 31 - 31 35 2c 30 30 3a 30 30  343..T5115,00:00\r\n    0000140 3a 31 37 2c 36 2e 39 37 - 35 0d 0a 54 35 31 31 35  :17,6.975..T5115\r\n    0000160 2c 30 30 3a 30 30 3a 32 - 39 2c 31 33 2e 35 35 33  ,00:00:29,13.553\r\n    0000200 0d 0a 54 35 31 31 35 2c - 30 30 3a 30 30 3a 33 35  ..T5115,00:00:35\r\n\r\nThis is very important and the root cause. Proofs:\r\n\r\n1. I opened, copied & saved the exactly content using Notepad++, and there won't be such problem with the newly saved file. \r\n2. If I convert it using `dos2unix` under Linux, then try the above same code, it would work. The `groupby` will not threw exception any more. \r\n\r\n```\r\n$ file test.csv \r\ntest.csv: ASCII text\r\n```\r\n\r\nHere are the top several bytes from the _**working**_ file:\r\n\r\n```\r\n0000000 49 64 2c 54 69 6d 65 2c - 56 61 6c 0a 54 35 31 31  Id,Time,Val.T511\r\n0000020 35 2c 30 30 3a 30 30 3a - 30 30 2c 32 30 2e 38 31  5,00:00:00,20.81\r\n0000040 39 0a 54 35 31 31 35 2c - 30 30 3a 30 30 3a 30 33  9.T5115,00:00:03\r\n0000060 2c 31 36 2e 39 32 36 0a - 54 35 31 31 35 2c 30 30  ,16.926.T5115,00\r\n0000100 3a 30 30 3a 30 38 2c 31 - 31 2e 33 34 33 0a 54 35  :00:08,11.343.T5\r\n\r\n```"},"comment":{"url":"https://api.github.com/repos/pandas-dev/pandas/issues/comments/308155876","html_url":"https://github.com/pandas-dev/pandas/issues/16690#issuecomment-308155876","issue_url":"https://api.github.com/repos/pandas-dev/pandas/issues/16690","id":308155876,"user":{"login":"TomAugspurger","id":1312546,"avatar_url":"https://avatars0.githubusercontent.com/u/1312546?v=3","gravatar_id":"","url":"https://api.github.com/users/TomAugspurger","html_url":"https://github.com/TomAugspurger","followers_url":"https://api.github.com/users/TomAugspurger/followers","following_url":"https://api.github.com/users/TomAugspurger/following{/other_user}","gists_url":"https://api.github.com/users/TomAugspurger/gists{/gist_id}","starred_url":"https://api.github.com/users/TomAugspurger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TomAugspurger/subscriptions","organizations_url":"https://api.github.com/users/TomAugspurger/orgs","repos_url":"https://api.github.com/users/TomAugspurger/repos","events_url":"https://api.github.com/users/TomAugspurger/events{/privacy}","received_events_url":"https://api.github.com/users/TomAugspurger/received_events","type":"User","site_admin":false},"created_at":"2017-06-13T15:31:46Z","updated_at":"2017-06-13T15:31:46Z","body":"Can you paste the output of `df.columns`? And try on a newer version of pandas, I suspect this was fixed in https://github.com/pandas-dev/pandas/issues/13885"}}
{"actor":{"display_login":"TomAugspurger"}}