Steve Lorimer
Steve Lorimer

Reputation: 28719

pandas: reindex panel with dataframe index

I have a panel with the Items index being days.

<class 'pandas.core.panel.Panel'>
Dimensions: 1260 (items) x 6 (major_axis) x 6 (minor_axis)
Items axis: 2011-06-27 00:00:00 to 2016-06-28 00:00:00
Major_axis axis: BP to XOM
Minor_axis axis: BP to XOM

Index:

DatetimeIndex(['2011-06-27', '2011-06-28', '2011-06-29', '2011-06-30',
               '2011-07-01', '2011-07-05', '2011-07-06', '2011-07-07',
               '2011-07-08', '2011-07-11', 
               ...
               '2016-06-15', '2016-06-16', '2016-06-17', '2016-06-20',
               '2016-06-21', '2016-06-22', '2016-06-23', '2016-06-24',
               '2016-06-27', '2016-06-28'],
              dtype='datetime64[ns]', name=u'Date', length=1260, freq=None, tz=None)

I have a dataframe with an index of times in millisecond resolution:

DatetimeIndex(['2016-05-18 09:30:00.200000', '2016-05-18 09:30:00.400000',
               '2016-05-18 09:30:00.600000', '2016-05-18 09:30:00.800000',
               '2016-05-18 09:30:01',        '2016-05-18 09:30:01.200000',
               '2016-05-18 09:30:01.400000', '2016-05-18 09:30:01.600000',
               '2016-05-18 09:30:01.800000', '2016-05-18 09:30:02', 
               ...
               '2016-05-20 15:59:58.200000', '2016-05-20 15:59:58.400000',
               '2016-05-20 15:59:58.600000', '2016-05-20 15:59:58.800000',
               '2016-05-20 15:59:59',        '2016-05-20 15:59:59.200000',
               '2016-05-20 15:59:59.400000', '2016-05-20 15:59:59.600000',
               '2016-05-20 15:59:59.800000', '2016-05-20 16:00:00'],
              dtype='datetime64[ns]', name=u'time', length=351000, freq='200L', tz=None)

When I try to reindex my panel, I get just NAN values

pnl2 = pnl.reindex(df.index)

pnl2[0,:,:]

    BP  COP CVX MPC VLO XOM
BP  NaN NaN NaN NaN NaN NaN
COP NaN NaN NaN NaN NaN NaN
CVX NaN NaN NaN NaN NaN NaN
MPC NaN NaN NaN NaN NaN NaN
VLO NaN NaN NaN NaN NaN NaN
XOM NaN NaN NaN NaN NaN NaN

I know, however, that the panel has data for the date's matching my dataframe:

pnl[datetime.datetime(2016, 5, 18),:,:]


     BP         COP         CVX         MPC         VLO         XOM
BP   0.085169   -0.020719   -0.027176   -0.001270   -0.004472   -0.025772
COP -0.020719    0.064905   -0.043904   -0.012531    0.010451   -0.005583
CVX -0.027176   -0.043904    0.185100   -0.006396   -0.001374   -0.108513
MPC -0.001270   -0.012531   -0.006396    0.053583   -0.042791    0.012225
VLO -0.004472    0.010451   -0.001374   -0.042791    0.059244   -0.015029
XOM -0.025772   -0.005583   -0.108513    0.012225   -0.015029    0.199892

Question:

What do I need to do in order to reindex my panel with my dataframe's index correctly?

Upvotes: 3

Views: 150

Answers (1)

EdChum
EdChum

Reputation: 394469

The reindexing fails due to the time component so you can access just the date component of your datetimeIndex

pnl2 = pnl.reindex(df.index.date)

Upvotes: 1

Related Questions