I am new to the MultiIndex in pandas but I have a situation where it would be helpful. I have a df with a MultiIndex (ON_SCENE and LAST) structured as so:
ID
ON_SCENE LAST
2016-05-05 03:58:54 last1 1000
2016-05-05 17:23:39 last1 1001
2016-05-05 18:20:50 last1 1002
2016-05-05 21:30:29 last2 1003
2016-05-05 22:33:19 last2 1004
2016-05-05 23:30:23 last3 1005
2016-05-06 00:08:34 last3 1006
2016-05-06 01:33:54 last3 1007
I want to subset this data using a date and last name as so:
df.loc[j.strftime('%Y-%m-%d'),Last_Name]
where j is type datetime.date and Last_Name is a str with a last name. Unfortunately, I keep getting a KeyError. I have also tried:
df[j.strftime('%Y-%m-%d')]
df[Last_Name]
But these also give me a KeyErrors. Not sure what I am doing wrong?
In [103]: x.loc[('2016-05-05', 'last2'), :]
Out[103]:
ID
ON_SCENE LAST
2016-05-05 21:30:29 last2 1003
2016-05-05 22:33:19 last2 1004
or using pd.IndexSlice:
In [104]: idx = pd.IndexSlice
In [105]: x.loc[idx['2016-05-05':'2016-05-06', 'last3'], :]
Out[105]:
ID
ON_SCENE LAST
2016-05-05 23:30:23 last3 1005
2016-05-06 00:08:34 last3 1006
2016-05-06 01:33:54 last3 1007