{"action":"created","issue":{"url":"https://api.github.com/repos/pydata/pandas/issues/10769","labels_url":"https://api.github.com/repos/pydata/pandas/issues/10769/labels{/name}","comments_url":"https://api.github.com/repos/pydata/pandas/issues/10769/comments","events_url":"https://api.github.com/repos/pydata/pandas/issues/10769/events","html_url":"https://github.com/pydata/pandas/issues/10769","id":99805194,"number":10769,"title":"Performance: Implicit boxing to TimeStamp of datetime64[ns] inside DateTimeIndex","user":{"login":"drglitch","id":910285,"avatar_url":"https://avatars.githubusercontent.com/u/910285?v=3","gravatar_id":"","url":"https://api.github.com/users/drglitch","html_url":"https://github.com/drglitch","followers_url":"https://api.github.com/users/drglitch/followers","following_url":"https://api.github.com/users/drglitch/following{/other_user}","gists_url":"https://api.github.com/users/drglitch/gists{/gist_id}","starred_url":"https://api.github.com/users/drglitch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drglitch/subscriptions","organizations_url":"https://api.github.com/users/drglitch/orgs","repos_url":"https://api.github.com/users/drglitch/repos","events_url":"https://api.github.com/users/drglitch/events{/privacy}","received_events_url":"https://api.github.com/users/drglitch/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-08-08T14:14:29Z","updated_at":"2015-08-08T16:11:19Z","closed_at":"2015-08-08T16:11:19Z","body":"Hello,\r\n\r\nI've stumbled onto a behavior that causes performance problems on large datasets: When creating a multiindex with second level being a DateTime64 col, and then doing a `.loc[('val1', TimeStamp('val2'),)]` to get at particular value, pandas coerces (via _box_func, called from __iter__) all values in that particular column to TimeStamp dtype. This is done only on first access, but affect performance quite heavily since the op is O(n).\r\n\r\nHowever, when chaining the same op (or calling it via multi-level `.xs`, this behavior is not exhibited. It is not a viable workaround however because doing so introduces an extra call to getitem per each level of multiindex.\r\n\r\nIs there a workaround for this or what am i doing wrong?\r\nIn essence, given a df with unique multiindex, i want to get to a particular row given values for each level of the index.\r\n\r\nRepro:\r\n```\r\ndf = <dataframe with: [colA:string, colB:datetime64, colC:numeric with 1,000,000 rows>\r\ngr = df.groupby(('colA', 'colB',))\r\nag = gr.aggregate({'colC':'sum'})\r\n\r\npath = ('one', pd.TimeStamp('2015-01-01 01:02:03'),)\r\nag.loc[path] # << first call iterates over entire dataset doing the conversion, and is slow\r\nag.loc[path] # << second call is very quick\r\nag.xs(path, levels=('colA','colB',)) # << this takes a different code path but does an extra call to getitem, making it slower.\r\n```\r\n\r\nOther things:\r\n- padding numpy.datetime64 into the path tuple instead of timestamp doesnt help\r\n- specifiying axis for loc doesn't help"},"comment":{"url":"https://api.github.com/repos/pydata/pandas/issues/comments/128993805","html_url":"https://github.com/pydata/pandas/issues/10769#issuecomment-128993805","issue_url":"https://api.github.com/repos/pydata/pandas/issues/10769","id":128993805,"user":{"login":"jreback","id":953992,"avatar_url":"https://avatars.githubusercontent.com/u/953992?v=3","gravatar_id":"","url":"https://api.github.com/users/jreback","html_url":"https://github.com/jreback","followers_url":"https://api.github.com/users/jreback/followers","following_url":"https://api.github.com/users/jreback/following{/other_user}","gists_url":"https://api.github.com/users/jreback/gists{/gist_id}","starred_url":"https://api.github.com/users/jreback/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jreback/subscriptions","organizations_url":"https://api.github.com/users/jreback/orgs","repos_url":"https://api.github.com/users/jreback/repos","events_url":"https://api.github.com/users/jreback/events{/privacy}","received_events_url":"https://api.github.com/users/jreback/received_events","type":"User","site_admin":false},"created_at":"2015-08-08T16:11:19Z","updated_at":"2015-08-08T16:11:19Z","body":"You are doing some really non-performant construction. The way you showed things are all ``object`` dtype. You want to avoid object whenever possible. Certainly for datetimes. \r\n\r\n```\r\nIn [69]: np.random.seed(1234)\r\n\r\nIn [70]: pd.set_option('max_rows',12)\r\n\r\nIn [71]: dates = pd.date_range('1/1/2001', periods=500000, freq='S')\r\n\r\nIn [72]: cats = list('ABCDEFGHIJ'*50000)\r\n\r\nIn [73]: df = pd.DataFrame({ 'A' : cats, 'B' : dates, 'C' : np.arange(500000) })\r\n\r\nIn [74]: df['C'] = df['C'].shift(-1)\r\n\r\nIn [75]: df.info()\r\n<class 'pandas.core.frame.DataFrame'>\r\nInt64Index: 500000 entries, 0 to 499999\r\nData columns (total 3 columns):\r\nA    500000 non-null object\r\nB    500000 non-null datetime64[ns]\r\nC    499999 non-null float64\r\ndtypes: datetime64[ns](1), float64(1), object(1)\r\nmemory usage: 15.3+ MB\r\n\r\n# if you have lots of duplicates then this will make some types of selections much faster (and reduces used memory)\r\nIn [77]: df['A'] = df['A'].astype('category')\r\n\r\nIn [78]: df.info()\r\n<class 'pandas.core.frame.DataFrame'>\r\nInt64Index: 500000 entries, 0 to 499999\r\nData columns (total 3 columns):\r\nA    500000 non-null category\r\nB    500000 non-null datetime64[ns]\r\nC    499999 non-null float64\r\ndtypes: category(1), datetime64[ns](1), float64(1)\r\nmemory usage: 11.9 MB\r\n\r\nIn [84]: agged = df.groupby(['A','B']).aggregate({'C' : 'sum' })\r\n\r\nIn [85]: agged.info()\r\n<class 'pandas.core.frame.DataFrame'>\r\nMultiIndex: 500000 entries, (A, 2001-01-01 00:00:00) to (J, 2001-01-06 18:53:19)\r\nData columns (total 1 columns):\r\nC    499999 non-null float64\r\ndtypes: float64(1)\r\nmemory usage: 10.0+ MB\r\n```\r\n\r\nTo do this type of indexing it constructs a hash table. So it needs to do some work on the first indexing. \r\nA multi-index by-definition is an ``object`` dtype itself, so this type of individual indexing is not efficient.\r\nIf you need to do this repeatedly and the initial indexing is a problem, then you are going about things the wrong way.\r\n```\r\nIn [86]: path = ('A', pd.Timestamp('2001-01-01 00:00:10'),)\r\n\r\nIn [87]: %timeit -n 1 -r 1 agged.loc[path]\r\n1 loops, best of 1: 1.85 s per loop\r\n\r\nIn [88]: %timeit -n 1 -r 1 agged.loc[path]\r\n1 loops, best of 1: 423 us per loop\r\n```\r\n\r\nThis is almost always better to slice out things first.\r\n```\r\nIn [92]: idx = pd.IndexSlice\r\nn [94]: agged.loc[idx[['A']],:]\r\nOut[94]: \r\n                            C\r\nA B                          \r\nA 2001-01-01 00:00:00       1\r\n  2001-01-01 00:00:10      11\r\n  2001-01-01 00:00:20      21\r\n  2001-01-01 00:00:30      31\r\n  2001-01-01 00:00:40      41\r\n  2001-01-01 00:00:50      51\r\n...                       ...\r\n  2001-01-06 18:52:20  499941\r\n  2001-01-06 18:52:30  499951\r\n  2001-01-06 18:52:40  499961\r\n  2001-01-06 18:52:50  499971\r\n  2001-01-06 18:53:00  499981\r\n  2001-01-06 18:53:10  499991\r\n\r\n[50000 rows x 1 columns]\r\n```\r\n\r\n```\r\nIn [95]: agged = df.groupby(['A','B']).aggregate({'C' : 'sum' })\r\n\r\nIn [96]: %timeit -n 1 -r 1 agged.loc[idx[['A']],:]\r\n1 loops, best of 1: 40.9 ms per loop\r\n```\r\n\r\nThen subslice on this, which has a quite efficient index scheme."}}