Index/Slicing Pandas DataFrame with Numpy datetime64 objects

Question

I'd like to be able to figure out if I can get the following to work (Pandas 0.23.4). Any help would be most appreciated.

import numpy as np
import pandas as pd

rows = 12
rng = pd.date_range('2011-01', periods=rows, freq='M')

df = pd.DataFrame(np.arange(rows), index=rng)

print(df.loc['2011-01'])
print(df.loc[np.datetime64('2011-01')])

The first print does what I would expect: shows all the rows that are in Jan of 2011. However, the second one throws an KeyError because the value is not in the index. I was hoping that it would provide the same output, but after some testing I realize that it is looking for an exact match 2011-01-01, which is not in the DataFrame. I'd like for the second one to work, so that I can use numpy.arange or pandas.date_range to easily generate arrays of dates that I can loop through. Anyone got this to work? (Seems like this works, but only if you have an exact match for the dates.)

Thanks for the help cryptonome and jpp. Unfortunately, it seems like the answer for this particular version of Pandas is "No. You can't do this exactly." I marked jpp answer as correct, because it doesn't require another loop. — Ryan
– Ryan, Commented Oct 21, 2018 at 12:52

deadvoid · Accepted Answer · 2018-10-20 01:14:04Z

2

use DatetimeIndex.to_period() & Period.month

import numpy as np
import pandas as pd

rows = 12
rng = pd.date_range('2011-01', periods=rows, freq='M')

df = pd.DataFrame(np.arange(rows), index=rng)

# print(df.loc['2011-01'])
for idx, di in enumerate(df.index.to_period()):
    if di.month == np.datetime64('2011-01').item().month:
        print(f'loc: [{idx}] == {df.index[idx]}')

output:

# loc: [0] == 2011-01-31 00:00:00

Since your df indexes consist of the end of the month dates, you can use this trick to use df.loc to get the row:

>>>> df.loc[df.index == np.datetime64('2011-03', 'D') -1]
            0
2011-02-28  1

>>>> df.loc[df.index == np.datetime64('2011-04', 'D') -1]
            0
2011-03-31  2

>>>> df[df.index == np.datetime64('2011-12', 'D') -1]
             0
2011-11-30  10

# use 2012 January 1st minus one day to get 2011 Dec 31st
>>>> df[df.index == np.datetime64('2012-01', 'D') -1]
             0
2011-12-31  11

edited Oct 20, 2018 at 1:14

answered Oct 19, 2018 at 0:09

deadvoid

1,3101 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ryan Over a year ago

Thanks @cryptonome. The to_period method is interesting; I'll have to consider that. However, I was hoping for a way to do this without adding another explicit loop, if possible. The implicit looping in Numpy/Pandas is much more efficient...

deadvoid Over a year ago

since your index is always the end of the month & your np.datetime64 is in year-month format, there's a trick that you can use for that. let me edit my answer.

Ryan Over a year ago

Thanks again, @cryptonome. Unfortunately, your new code only works for exact matches. I was hoping to do a search for the entire month. I appreciate your help, though.

deadvoid Over a year ago

that's alright @Ryan, maybe i misunderstood your question

jpp · Accepted Answer · 2018-10-20 01:59:55Z

1

You can write a function to convert np.datetime64 to Pandas-compatible strings:

def stringify(x):
    year = x.astype('datetime64[Y]').astype(int) + 1970
    month = x.astype('datetime64[M]').astype(int) % 12 + 1
    return f'{year}-{month:02}'

a = df.loc['2011-01']
b = df.loc[stringify(np.datetime64('2011-01'))]

assert a.equals(b)

answered Oct 20, 2018 at 1:59

jpp

166k37 gold badges301 silver badges363 bronze badges

Collectives™ on Stack Overflow

Index/Slicing Pandas DataFrame with Numpy datetime64 objects

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related