Filter a dataframe with datetime index using another dataframe with date index

Question

I have two dataframes, one dataframe has index with date and time, the other dataframe's index only has date. Now I'd like to filter rows of first dataframe if its date is within 2nd dataframe's index.

I can do it but very complicated to use two for loop to check each item. Is there a simple way?

Here is what I did:

First dataframe

import pandas as pd
df=pd.DataFrame({'value':[1,2,3,4,5,6]})
df.index=['2022-01-01 10:00','2022-01-02 13:00','2022-01-07 10:00','2022-01-08 10:00','2022-01-11 10:00','2022-01-12 10:00']
df.index=pd.to_datetime(df.index)
df

    value
2022-01-01 10:00:00 1
2022-01-02 13:00:00 2
2022-01-07 10:00:00 3
2022-01-08 10:00:00 4
2022-01-11 10:00:00 5
2022-01-12 10:00:00 6

2nd dataframe

f_1=pd.DataFrame({'value':[1,2,3]})
df_1.index=['2022-01-02','2022-01-05','2022-01-07']
df_1.index=pd.to_datetime(df_1.index)
df_1

    value
2022-01-02  1
2022-01-05  2
2022-01-07  3

Now I am checking each element of first dataframe's index is equal to any element of 2nd dataframe's index. If so, then save value of True

date_same=[False]*len(df.index)
for ix,date1 in enumerate(df.index.date):
    for date2 in df_1.index:
        if date1==date2:
            date_same[ix]=True
            break
date_same   

[False, True, True, False, False, False]

Now using saved list to filter the first dataframe

df_filter=df.loc[date_same]
df_filter

    value
2022-01-02 13:00:00 2
2022-01-07 10:00:00 3

Try looking at this stackoverflow.com/questions/40468069/… — Galo do Leste
– Galo do Leste, Commented Jan 12, 2023 at 4:15

cottontail · Accepted Answer · 2023-01-12 04:16:12Z

1

You can directly use np.isin to create a boolean array and index it:

df.loc[np.isin(df.index.date, df_1.index.date)]

answered Jan 12, 2023 at 4:16

cottontail

25.7k25 gold badges187 silver badges178 bronze badges

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

3 Comments

roudan Over a year ago

Hi cottontail, how to get row index of this filtered dataframe within the original dataframe? Thanks

roudan Over a year ago

I meant row index like which row in the original dataframe, for example, in the above example, the filtered dataframe is from the 2nd row and 3rd row of the original dataframe, so I am expecting [1,2]. I think I found it now by doing [df.index.get_loc(a) for a in df_filtered.index]. Thanks

cottontail Over a year ago

@roudan does assigning a new column before filtering work? df.assign(idx=np.arange(len(df))).loc[np.isin(df.index.date, df_1.index.date)]

Collectives™ on Stack Overflow

Filter a dataframe with datetime index using another dataframe with date index

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related