Select DataFrame rows by date range

Question

This thread didn't solved my problem.

This is my data:

Date        Server
2019-02-13  A
2019-02-13  B
2019-02-13  B
2019-02-17  A
2019-02-17  B
2019-02-17  C
2019-02-19  C
2019-02-19  D

I need to get a list of the servers for a respective date range. I tried this code:

df['Date'] = pd.to_datetime(df['Date'], format='%Y%m%d').apply(lambda x: x.strftime(format='%Y-%m-%d'))

df = df.set_index(df['Date'])

### This formatting changes the cell content from a format like 20190217 to the 
one represented above. Maybe there is already an error right here.### 

start_date = pd.to_datetime('20190212', format='%Y%m%d').strftime(format='%Y-%m-%d')
end_date   = pd.to_datetime('20190217', format='%Y%m%d').strftime(format='%Y-%m-%d')

The print statements however deliver the correct result, if I write the dates explicitly. However in my program I need to pipe in the dates by start_date and end_date.

print(df[df.Date.between('2019-02-12','2019-02-17')].Server.unique())
print(df.loc['2019-02-12':'2019-02-17'].Server.unique())
print(df.loc[start_date : end_date].Server.unique())

Output:

['A' 'B' 'C']     - correct
['A' 'B' 'C']     - correct
['A' 'B' 'C' 'D'] - incorrect

Which changes to my code do I need to apply?

Akhilesh_IN · Accepted Answer · 2019-03-27 18:20:25Z

1

you need not to make strftime and change format to format='%Y-%m-%d'

import pandas as pd

df = pd.DataFrame({'Date': ['2019-02-13', '2019-02-13', '2019-02-13', '2019-02-17', '2019-02-17', '2019-02-17', '2019-02-19', '2019-02-19'],
                   'Server':['A','B','B','A','B','C','C','D']})


df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')
df = df.set_index(df['Date'])
start_date = pd.to_datetime('20190212', format='%Y%m%d').strftime(format='%Y-%m-%d')
end_date   = pd.to_datetime('20190217', format='%Y%m%d').strftime(format='%Y-%m-%d')
print(df[df.Date.between('2019-02-12','2019-02-17')].Server.unique())
print(df.loc['2019-02-12':'2019-02-17'].Server.unique())
print(df.loc[start_date : end_date].Server.unique())

output is

['A' 'B' 'C']
['A' 'B' 'C']
['A' 'B' 'C']

edited Mar 27, 2019 at 18:20

answered Mar 27, 2019 at 18:11

Akhilesh_IN

1,3271 gold badge13 silver badges21 bronze badges

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

Comments

warwick12 · Accepted Answer · 2019-03-28 10:48:25Z

1

This should do the trick.

import pandas as pd
start_date = '2019-02-12'
end_date = '2019-02-17'
df['Date'] = pd.to_datetime(df['Date'])
print(df.loc[(df['Date'] > start_date) & (df['Date'] <= end_date)].Server.unique())

edited Mar 28, 2019 at 10:48

answered Mar 27, 2019 at 18:17

warwick12

3263 silver badges13 bronze badges

1 Comment

Mike_H Over a year ago

Thank you, this might have solved my mysterious errors as well. However my main problem was originating from transforming the format. That's why @Akhileshs answer helped me a bit better. However, thank you very much! :)

Collectives™ on Stack Overflow

Select DataFrame rows by date range

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related