Filter rows based on date in the column

Question

I want to filter the data based on date. I tried comparing the value directly by looping into the 'Transaction Time' column and comparing with date I want but returned an empty data on that column. I am trying the below code as an alternative but I am getting error.

First, I want to slice the date string to remove the time and generate just date only. This I believe will enable my comparison work better. Here is the code for the slicing

 for i in sheet2['G']:
  if i.value=='Transaction Time':
    sheet2['G1']='Transaction Time'
  else:
    sheet2['G{}'.format(i)]=i.value[0:10]

here is the error below:

ile "C:\Users\m2jto\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\utils\cell.py", line 135, in range_boundaries raise ValueError(msg) ValueError: G<Cell 'Balance'.G2> is not a valid coordinate or range.

how can I solve this or do you have a better method of achieving this.

Answer 1

You can use pandas for this. Here I am only making sure that the date is 18. You can use your own comparisons. Here are most of the commands in pandas, but you can find more documentation online as well: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf

import pandas as pd

dataFrame = pd.read_excel(your file path here)
filteredDataFrame = dataFrame.loc[dataFrame['Transaction Time'].str[8:10] == '18']

print(filteredDataFrame)

Edit: If you want to separate the time part of the Transaction Time you can do this:

dataFrame[['Date', 'Time']] = dataFrame['Transaction Time'].str.split(' ', expand=True)
dataFrame[['Year','Month','day']] = dataFrame['Date'].str.split('-', expand=True)

And now, if you want, you can delete the other columns with dataFrame.drop(columns=['Date', 'Time']). Here I am only dropping the date and time column. You decide what you want to remove. Now it should be easy to compare dates.

Edit 2: You will have to convert the column to a numeric one like this:

dataFrame['Day'] = pd.to_numeric(dataFrame['Day'])

then you can do comparisons like this:

filteredDataFrame = dataFrame.loc[dataFrame['Day'] < 18]

Answer 2

If you are using openpyxl:

The column 'Transaction Time' has values of type (datetime.datetime) For the date comparison to work, first import datetime from module date time and also import date

to convert: "2022-03-27 10:18:16" to "2022-03-27" we have to use datetime conversion and not str conversion

for _val in _file_obj.my_base_wb['Sheet2'].iter_rows(min_row=2, values_only=True):
    if _val[6].date() == datetime(2022, 3, 27).date():
        print("Matched Row: - ", _val)

##########Result##########
Matched Row: -  ('Withdrawal', None, None, None, 2500, 'Completed', datetime.datetime(2022, 3, 27, 10, 18, 16), 2)

Process finished with exit code 0