CODEWITHSUNDEEP
pythondatetimepandas
drum
drum

Reputation: 5651

How to search by Date given Datetime index

Given a DataFrame, where the index is Datetime, how can I retrieve the row(s) by matching only on the Date portion?

For example:

df1 =

                     A    B    C    D
2011-01-13 16:00:00  344  144  616  73
2011-01-14 16:00:00  346  145  624  74
2011-01-18 16:00:00  339  146  639  77
...

And given:

df2['Date'] = 

0    2011-01-13
1    2011-01-13
2    2011-01-26
3    2011-02-02
4    2011-02-10
5    2011-03-03
6    2011-03-03
7    2011-06-03
8    2011-05-03
9    2011-06-10
10   2011-08-01
11   2011-08-01
12   2011-12-20

I want something like this:

for indx, row in df2.iterrows():
    print df1.loc[df1.index.date() == row['Date'].date()]

Upvotes: 1

Views: 1578

Answers (2)

jezrael
jezrael

Reputation: 862581

You can use to_series, date and isin:

print df1.index.to_series().dt.date
2011-01-13 16:00:00    2011-01-13
2011-01-14 16:00:00    2011-01-14
2011-01-18 16:00:00    2011-01-18
dtype: object

print df1.index.to_series().dt.date.isin(df2['Date'].dt.date)
Name: Date, dtype: object
2011-01-13 16:00:00     True
2011-01-14 16:00:00    False
2011-01-18 16:00:00    False
dtype: bool

print df1[df1.index.to_series().dt.date.isin(df2['Date'].dt.date)]
                       A    B    C   D
2011-01-13 16:00:00  344  144  616  73

Or maybe you need:

print df1.index.date
[datetime.date(2011, 1, 13) datetime.date(2011, 1, 14)
 datetime.date(2011, 1, 18)]

print df2['Date'].dt.date.isin(df1.index.date)
0      True
1      True
2     False
3     False
4     False
5     False
6     False
7     False
8     False
9     False
10    False
11    False
12    False
Name: Date, dtype: bool

print df2[df2['Date'].dt.date.isin(df1.index.date)]
        Date
0 2011-01-13
1 2011-01-13

Upvotes: 3

Alexander
Alexander

Reputation: 109526

You can create a date column in df1 and then merge that table to df2 (assumes dates in df2 are of type datetime.date):

df1['Date'] = [d.date() for d in df1.index]
>>> df2.merge(df1, how='left', on='Date']
          Date    A    B    C   D
0   2011-01-13  344  144  616  73
1   2011-01-13  344  144  616  73
2   2011-01-26  NaN  NaN  NaN NaN
3   2011-02-02  NaN  NaN  NaN NaN
4   2011-02-10  NaN  NaN  NaN NaN
5   2011-03-03  NaN  NaN  NaN NaN
6   2011-03-03  NaN  NaN  NaN NaN
7   2011-06-03  NaN  NaN  NaN NaN
8   2011-05-03  NaN  NaN  NaN NaN
9   2011-06-10  NaN  NaN  NaN NaN
10  2011-08-01  NaN  NaN  NaN NaN
11  2011-08-01  NaN  NaN  NaN NaN
12  2011-12-20  NaN  NaN  NaN NaN

Upvotes: 0

Related Questions