Filter over two columns on the pandas dataframe, where one represents the time of phone calls

Question

I have a pandas dataframe as following below:

import pandas as pd

df = pd.DataFrame({'DAY_OF_WEEK': ['Monday', 'Sunday', 'Saturday', 'Monday', 'Tuesday'],
                   'HOUR': ['21:02:02', '11:22:02', '23:02:02', '09:22:02', '09:02:02'],
              'id': [1,2,2,3,1]})

df

I need to applicate a filter for that a new dataframe have only register where HOUR between 19:00:00 and 07:00:00 or DAY_OF_WEEK == 'Sunday'. An expected output:

DAY_OF_WEEK |   HOUR  | ID
Monday      |21:02:02 | 1
Saturday    |23:02:02 | 2
Monday      |09:22:02 | 3

I trying the following:

df2 = df.loc[(pd.to_timedelta(df.HOUR).between('06:00:00','19:00:00') | df['DAY_OF_WEEK'] == 'Sunday')]
calls_night_or_sunday = df[~df2].copy()

but, I have an output where all the lines received NaN. Can anyone help?

Savostyanov Konstantin · Answer 1 · 2020-11-15T15:49:37.077

2

import pandas as pd

df = pd.DataFrame({'DAY_OF_WEEK': ['Monday', 'Sunday', 'Saturday', 'Monday', 'Tuesday'],
                   'HOUR': ['21:02:02', '11:22:02', '23:02:02', '09:22:02', '09:02:02'],
              'id': [1,2,2,3,1]})

df2 = df[(df.HOUR.between('19:00:00','23:59:59')) | (df.HOUR.between('00:00:00','06:00:00')) | (df.DAY_OF_WEEK == 'Sunday')]
df2

# Output
# DAY_OF_WEEK   HOUR    id
# 0 Monday  21:02:02    1
# 1 Sunday  11:22:02    2
# 2 Saturday    23:02:02    2

Pandas conditions

UPDATED:
timeranges changed

edited Nov 15 '20 at 15:49

answered Nov 15 '20 at 15:39

Savostyanov Konstantin

345
1
8

lines #3 and #4 bring time values out of range – Costa.Gustavo Nov 15 '20 at 15:47
I don't understand what you want to do. You wrote "where HOUR between 19:00:00 and 07:00:00 or DAY_OF_WEEK == 'Sunday'" and in example wrote "Monday |09:22:02 | 3". 09:22:02 not in between 19:00:00 and 07:00:00. – Savostyanov Konstantin Nov 15 '20 at 15:58

score 1 · Accepted Answer · answered Nov 15 '20 at 16:28

1

You can use:

df.loc[('19' < df['HOUR']) | (df['HOUR'] < '06') |  (df['DAY_OF_WEEK'] == 'Sunday')]

Output:

  DAY_OF_WEEK      HOUR  id
0      Monday  21:02:02   1
1      Sunday  11:22:02   2
2    Saturday  23:02:02   2

answered Nov 15 '20 at 16:28

Mykola Zotko

15,583
3
71
73

score -1 · Answer 3 · answered Nov 15 '20 at 15:28

-1

Try using "|" instead of "or"

answered Nov 15 '20 at 15:28

ahmed awada

115
5

Try using .query() --- df2=df.query('(HOUR >="06:00:00" and HOUR<="19:00:00") or Day_of_week == "Sunday") – ahmed awada Nov 15 '20 at 15:38

score -1 · Answer 4 · edited Nov 15 '20 at 15:36

-1

Try this instead

df2 = df.loc[(pd.to_timedelta(df.HOUR).between('06:00:00','19:00:00') | (df['DAY_OF_WEEK'] == 'Sunday'))]

edited Nov 15 '20 at 15:36

Dharman

30,962
25
85
135

answered Nov 15 '20 at 15:31

Paul Brennan

2,638
4
19
26

between 19:00:00 and 07:00:00 – Mykola Zotko Nov 15 '20 at 15:35
In this way all data receives NaN – Costa.Gustavo Nov 15 '20 at 15:37

Filter over two columns on the pandas dataframe, where one represents the time of phone calls

4 Answers4