6

I have two panda data frames new_hpm and new_mr with datetime index, and I am trying to subset one based on datetime index of another using .loc.

The datetime indexes on the two data frames are:

new_hpm.index
DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04',
               '2013-01-05', '2013-01-06', '2013-01-07', '2013-01-08',
               '2013-01-09', '2013-01-10',
               ...
               '2017-12-15', '2017-12-20', '2017-12-21', '2017-12-22',
               '2017-12-23', '2017-12-24', '2017-12-28', '2017-12-29',
               '2017-12-30', '2017-12-31'],
              dtype='datetime64[ns]', name='datetime', length=1093, freq=None)
new_mr.index
DatetimeIndex(['2013-01-07', '2013-01-07', '2013-01-13', '2013-01-13',
               '2013-01-13', '2013-01-13', '2013-01-14', '2013-01-14',
               '2013-01-14', '2013-01-14',
               ...
               '2017-12-31', '2017-12-31', '2017-12-31', '2017-12-31',
               '2017-12-31', '2017-12-31', '2017-12-31', '2017-12-31',
               '2017-12-31', '2017-12-31'],
              dtype='datetime64[ns]', name='date_conv', length=13366, freq=None)

However, when I do 

subset_mr = new_mr.loc[new_hpm.index]

I get the error message:

ValueError: mixed datetimes and integers in passed array
halo09876
  • 2,725
  • 12
  • 51
  • 71

1 Answers1

1

This is most likely due to the duplicates present in new_mr, as seen in GH issue here.

You could remove the duplicates in the index of new_mr before slicing or, in general, you want to perform set operations, there are better ways that work well with indices, e.g.

subset_mr = new_mr.loc[new_mr.index.intersection(new_hpm.index).drop_duplicates()]
Pepino
  • 310
  • 2
  • 10