CODEWITHSUNDEEP
pythonpandasdataframe
mHelpMe
mHelpMe

Reputation: 6668

finding dates in a dataframe and getting the index of those dates

I have two dataframes say dfOne and dfTwo.

dfOne has a column called Dates. It has every day from 1st Jan 2016 until 1st Jan 2018.

dfTwo also has a column called Dates but say only has 5 dates,

  4th Feb 2016
  23rd March 2016
  3rd May 2016
  22nd November 2016
  18th July 2017

What is the best way to find the index of each of the dates above in the dataframe dfOne?

Upvotes: 0

Views: 37

Answers (2)

rafaelc
rafaelc

Reputation: 59274

pd.isin is the way to go if you just need to know the indexes of the intersecting values. If you need the actual index values for each entry in your df2, you may use index matching 

df2.set_index('dates').assign(index=df.set_index('dates')['index']).reset_index()

For example, MCVE:

df          = pd.DataFrame({'dates': ['10/10/2010', '10/11/2010', '10/12/2010', '10/13/2010']}) 
df['index'] = df.index
df2         = pd.DataFrame({'dates': ['10/10/2010', '10/13/2010']})

Then, df2 goes from

    dates
0   2010-10-10
1   2010-10-13

to

    dates       index
0   2010-10-10  0
1   2010-10-13  3

where 0 and 3 are the respective indexes in df.

Upvotes: 1

BENY
BENY

Reputation: 323276

You may need convert them into datetime format 

df2.Date=pd.to_datetime(df2.Date)
df1.Date=pd.to_datetime(df1.Date)

Then we using isin 

df1.index[df1.Date.isin(df2.Date)]

Upvotes: 2

Related Questions