How to get the rows in Pandas dataframe with datetime index using another datetime index?

Question

I have a Pandas dataframe with the following datetime index:

DatetimeIndex(['2020-01-02', '2020-01-03', '2020-01-06', '2020-01-07',
               '2020-01-08', '2020-01-09', '2020-01-10', '2020-01-13',
               '2020-01-14', '2020-01-15',
               ...
               '2020-01-17', '2020-01-21', '2020-01-22', '2020-01-23',
               '2020-01-24', '2020-01-27', '2020-01-28', '2020-01-29',
               '2020-01-30', '2020-01-31'],
              dtype='datetime64[ns]', name='Date', length=49098, freq=None)

I want to get the rows which intersect with the following datetime index:

DatetimeIndex(['2020-01-02', '2020-01-03', '2020-01-06', '2020-01-07',
               '2020-01-08', '2020-01-09', '2020-01-10'],
              dtype='datetime64[ns]', name='Date', freq=None)

What is the most natural (aka "Pythonic") way to do it?

Celius Stingher · Accepted Answer · 2020-02-20 12:00:42Z

5

Use Index.intersection:

idx = idx1.intersection(idx2)

Or, if the indexes have not been previously defined:

idx = df1.index.intersection(df2.index)

edited Feb 20, 2020 at 12:00

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

answered Feb 20, 2020 at 11:58

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

Celius Stingher Over a year ago

At your service.

Matti Over a year ago

This is how you get the intersection of two DateTimeIndex as a DateTimeIndex. But how do you get the rows from the original dataframe (which was the original question)? df.loc[idx] gives me a dataframe that has more rows than the original df. My original df has multiple occurances of the same dates.

Matti Over a year ago

@jezrael I knew you are fast, but I did not know you were this fast! Yes, it works.

jerrylogansquare Over a year ago

thanks @jezrael ! i was searching almost hour looking for clean answer

Collectives™ on Stack Overflow

How to get the rows in Pandas dataframe with datetime index using another datetime index?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related