0

My Excel file Contains Following Columns

enter image description here

I need to filter out the row by present Date and Time. Means, it should display those rows where Today is between "From Date" and "To Date"

AND

Present time is Between "From Time" and "To Time"

This is my code :

import pandas as pd
from datetime import datetime

today = (datetime.date(datetime.now())) ##For Date
print (today)
now = datetime.now()
now = now.strftime("%H:%M:%S")
print(now)

df = pd.read_excel (r'ymca.xlsx')

df = df[(df['From Date']<= str(today)) & (df['To Date']>= str(today))]
print (df)
df = df[(df['From Time']<= str(now)) & (df['To Time']>= str(now))]
print (df)

The first filter is working perfectly, It is able to filter Date

2020-09-01
22:04:37
   From Date    To Date From Time   To Time
1 2020-08-31 2020-09-01  00:00:00  13:00:00
2 2020-09-01 2020-09-02  12:00:00  13:00:00
5 2020-08-31 2020-09-05  12:00:00  13:00:00

But second Filter for Time is not working. It is erroring out , where as the syntax is same for both.

Please help.

  File ".\tts2.py", line 17, in <module>
    df = df[(df['From Time']<= str(now)) & (df['To Time']>= str(now))]
  File "C:\Python38\lib\site-packages\pandas\core\ops\common.py", line 64, in new_method
    return method(self, other)
  File "C:\Python38\lib\site-packages\pandas\core\ops\__init__.py", line 529, in wrapper
    res_values = comparison_op(lvalues, rvalues, op)
  File "C:\Python38\lib\site-packages\pandas\core\ops\array_ops.py", line 247, in comparison_op
    res_values = comp_method_OBJECT_ARRAY(op, lvalues, rvalues)
  File "C:\Python38\lib\site-packages\pandas\core\ops\array_ops.py", line 57, in comp_method_OBJECT_ARRAY
    result = libops.scalar_compare(x.ravel(), y, op)
  File "pandas\_libs\ops.pyx", line 96, in pandas._libs.ops.scalar_compare
TypeError: '<=' not supported between instances of 'datetime.time' and 'str'
Ashish
  • 479
  • 3
  • 7
  • 18
  • Why don't you convert the pandas columns to `datetime` format and perform the comparison? This [link](https://stackoverflow.com/questions/1831410/how-to-compare-times-in-python) provides a good post on comparing datetimes. – Anurag Reddy Sep 01 '20 at 16:50

1 Answers1

2

Just convert separate date and time columns to from and to columns containing datetime, and then filter on those columns:

df['from'] = pd.to_datetime(df['From Date'].astype(str) + ' ' + df['From Time'])
df['to'] = pd.to_datetime(df['To Date'].astype(str) + ' ' + df['To Time'])

now = pd.to_datetime('now')
df.loc[(now >= df.pop('from')) & (now <= df.pop('to'))]

Output:

    From Date     To Date From Time   To Time
1  2020-09-01  2020-09-02  12:00:00  13:00:00
2  2020-08-31  2020-09-05  12:00:00  13:00:00

P.S. Now, to explain the error that you're getting, it's due to the fact that your time columns appear to be of datetime.time type, which does not support comparison with strings.

perl
  • 9,826
  • 1
  • 10
  • 22