Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Datetime as string in indexing a MultiIndex not always working #4758

Closed
jorisvandenbossche opened this issue Sep 5, 2013 · 4 comments · Fixed by #4761

Comments

@jorisvandenbossche
Copy link
Member

commented Sep 5, 2013

See http://stackoverflow.com/questions/18643030/groupby-and-multi-indexing/18644920#18644920

Basically, indexing a DateTimeIndex with a string does work in a single index, but not always in a MultiIndex:

In [7]: df= pd.DataFrame({'ACCOUNT':["ACCT1", "ACCT1", "ACCT1", "ACCT2"],
   ...:                   'TICKER':["ABC", "MNP", "XYZ", "XYZ"],
   ...:                   'val':[1,2,3,4]},
   ...:                  index=pd.date_range("2013-06-19 09:30:00", periods=4, freq='5T'))
In [8]: df_multi = df.set_index(['ACCOUNT', 'TICKER'], append=True)
In [9]: df
Out[9]:
                    ACCOUNT TICKER  val
2013-06-19 09:30:00   ACCT1    ABC    1
2013-06-19 09:35:00   ACCT1    MNP    2
2013-06-19 09:40:00   ACCT1    XYZ    3
2013-06-19 09:45:00   ACCT2    XYZ    4

In [10]: df_multi
Out[10]:
                                    val
                    ACCOUNT TICKER
2013-06-19 09:30:00 ACCT1   ABC       1
2013-06-19 09:35:00 ACCT1   MNP       2
2013-06-19 09:40:00 ACCT1   XYZ       3
2013-06-19 09:45:00 ACCT2   XYZ       4

Accessing a row with string in single index works:

In [11]: df.loc['2013-06-19 09:30:00']
Out[11]:
ACCOUNT    ACCT1
TICKER       ABC
val            1
Name: 2013-06-19 09:30:00, dtype: object

In a MultiIndex it doesn't work:

In [12]: df_multi.loc[('2013-06-19 09:30:00', 'ACCT1', 'ABC')]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last) <ipython-input-12-cbb2ba302ffa> in <module>()
----> 1 df_multi.loc[('2013-06-19 09:30:00', 'ACCT1', 'ABC')]

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\indexing.pyc in __getitem__(self, key)
    722     def __getitem__(self, key):
    723         if type(key) is tuple:
--> 724             return self._getitem_tuple(key)
    725         else:
    726             return self._getitem_axis(key, axis=0)

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\indexing.pyc in _getitem_tuple(self, tup)
    285
    286         # no multi-index, so validate all of the indexers
--> 287         self._has_valid_tuple(tup)
    288
    289         # ugly hack for GH #836

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\indexing.pyc in _has_valid_tuple(self, key)
    717             if i >= self.obj.ndim:
    718                 raise ValueError('Too many indexers')
--> 719             if not self._has_valid_type(k,i):
    720                 raise ValueError("Location based indexing can only have [%s] types" % self._valid_types)
    721

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\indexing.pyc in _has_valid_type(self, key, axis)
    792
    793             if not key in ax:
--> 794                 raise KeyError("the label [%s] is not in the [%s]" % (key,self.obj._get_axis_name(axis)))
    795
    796         return True

KeyError: 'the label [ACCT1] is not in the [columns]'

But when only accessing the first two levels of the three levels of the MultiIndex, it does work:

In [13]: df_multi.loc[('2013-06-19 09:30:00', 'ACCT1')]
Out[13]:
        val
TICKER
ABC       1

And when using a Timestamp it also works (as it should be):

In [14]: df_multi.loc[(pd.Timestamp('2013-06-19 09:30:00', tz=None), 'ACCT1', 'ABC')]
Out[14]:
val    1
Name: (2013-06-19 09:30:00, ACCT1, ABC), dtype: int64
@hayd

This comment has been minimized.

Copy link
Contributor

commented Sep 5, 2013

@jreback

This comment has been minimized.

Copy link
Contributor

commented Sep 5, 2013

I think pretty simple, but have to debug the path

@jreback

This comment has been minimized.

Copy link
Contributor

commented Sep 6, 2013

@jorisvandenbossche see #4761

turned out to be where I thought it was, fix was straightforward (though non-trivial)

@hayd hayd closed this in #4761 Sep 6, 2013

@hayd

This comment has been minimized.

Copy link
Contributor

commented Sep 6, 2013

awesome!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.