Filtering out a single day from pandas dataframe

user_7158458 0
Viewed 454

Trying to filter out one day from my data, when I try and run my code I get no results (which isn't accurate because there is data for that particular day).

This is the code I am using:

a['datetime'] = pd.to_datetime(a['datetime'])
start_date = pd.to_datetime('2019-09-01')
end_date = pd.to_datetime('2019-10-01')
sept = a[a['datetime'].between(start_date, end_date)]
day1 = pd.to_datetime('2019-09-11')
(sept['datetime'] == day1).sum()

Here is a sample of my data

3         2019-09-11 06:59:02.715641
13        2019-09-12 11:16:53.061871
24        2019-09-02 06:50:37.347313
27034     2019-09-15 11:57:34.582988
27163     2019-09-01 13:38:34.169917
31708     2019-09-17 07:45:50.693893
32883     2019-09-06 13:27:56.161920
33645     2019-09-17 10:02:11.010567
33657     2019-09-01 15:55:42.492608
57825     2019-09-17 11:25:19.405100
57836     2019-09-04 20:12:10.853341
57837     2019-09-04 20:12:00.959338
pandas python
2 Answers

you can use a .loc to filter your desired row and sum the relevant column.

as you haven't shown us your column name i'll assume they are col_1 and col_2

so, to access the row we can do : 

print(df.loc[df['col_2'].dt.normalize() == '2019-09-11'])


col_1 col_2
3     2019-09-11 06:59:02.715641

then, to access the relevant column to get a sum, we can pass 'col_1' to the end with .sum()

df.loc[df['col_2'].dt.normalize() == '2019-09-11']['col_1'].sum()
out : 3

dt.normalize() returns just the date value of your datetime column, the reason your code won't work because 01-01-2001 is not == to 01-01-2001 23:59

if we print the following :

print(df['col_2'].dt.normalize())
0    2019-09-11
1    2019-09-12
2    2019-09-02
3    2019-09-15
4    2019-09-01
5    2019-09-17
6    2019-09-06
7    2019-09-17
8    2019-09-01
9    2019-09-17
10   2019-09-04
11   2019-09-04
Name: 1, dtype: datetime64[ns]

note that dt.normalize() keeps the data type as datetime so you are able to use further datetime operations.

user_93751020

When you specify sept['datetime'] == day1, you are comparing Timestamps which include time. So you are not checking if you have data for sep 11, you are checking if you have data for sep 11 at exactly midnight.

You can use the .between function as you did above

user_18307250
Related