2

enter image description here

I have this excel file, I need to Search Hour and name filter and get row number. How can I do that ?

For example if the Name == "John" and Hour == 12:00:00 return row number. How can I do this ?

I read data with this:

writer = pd.ExcelWriter(file_name, engine='openpyxl', mode='a', if_sheet_exists='overlay')
df = pd.read_excel(file_name, sheet_name="Today")

This values in 'Today' sheet. But I couldnt get row number two filter value. How can I do that ?

Edit:

According to comments I try with this:

writer = pd.ExcelWriter(file_name, engine='openpyxl', mode='a', if_sheet_exists='overlay')
df = pd.read_excel(file_name, sheet_name="Today")

df['Hour'] = pd.to_datetime(df['Hour'])

print(df)

a = df[(df["Name"] == "John") & (df['Hour'] == '12:00:00')].index + 1
print(a)

But print df returns:

   Unnamed: 0      Name Surname        Hour
0         NaN      John     Lake 1970-01-01
1         NaN  Jennifer    Brown 1970-01-01
2         NaN      Dean     Body 1970-01-01
3         NaN      John     Lake 1970-01-01

but my excel format like this:

enter image description here

Bireysel Finans
  • 57
  • 4
  • Do you mean the row number in the dataframe (in this case `0`) or the row number in the spreadsheet (in this case `1`) with the given condition ? – Timeless Oct 05 '22 at 08:29

1 Answers1

0

Considering the dataframe that OP shared in the image

import pandas as pd

df = pd.DataFrame({'Name': {0: 'John', 1: 'Jennifer', 2: 'Dean'}, 'Surname': {0: 'Lake', 1: 'Brown', 2: 'Body'}, 'Hour': {0: '12:00:00', 1: '5:10:00', 2: '3:35:00'}})

[Out]:

       Name Surname      Hour
0      John    Lake  12:00:00
1  Jennifer   Brown   5:10:00
2      Dean    Body   3:35:00

Let us first convert the hour column to a datetime object

df['Hour'] = pd.to_datetime(df['Hour'])

[Out]:

       Name Surname                Hour
0      John    Lake 2022-10-05 12:00:00
1  Jennifer   Brown 2022-10-05 05:10:00
2      Dean    Body 2022-10-05 03:35:00

Now, with that dataframe, if one wants to get the row number after filtering for the Name John and the Hour 12:00:00, one can use filter the original dataframe and then .index[0]

rownumber = df[(df['Name'] == 'John') & (df['Hour'] == '12:00:00')].index[0]

[Out]: 0

which is the row 0.

If one wants to filter for the Name Dean

rownumber = df[(df['Name'] == 'Dean')].index[0]

[Out]: 2

which is the row 2.

Gonçalo Peres
  • 11,752
  • 3
  • 54
  • 83
  • Thank you for your reply but when I use read_excel, I get this time format: John Lake 1970-01-01. I add details in topic – Bireysel Finans Oct 05 '22 at 10:54
  • @BireyselFinans that is a different question. My answer solves the problem of printing a row number that satisfies specific conditions. If now you are having problems reading an excel file, then either search throughout the community to see if something helps, such as [this Q&A](https://stackoverflow.com/q/17685191/7109869). If your doubts are still persisting, consider asking another question with your new problem. Most likely you will get the needed help from me or another SO user. – Gonçalo Peres Oct 05 '22 at 11:14
  • @BireyselFinans There is a [good practice on StackOverflow](https://meta.stackoverflow.com/q/332820/7109869) related with editing questions - avoid chameleon questions. Also, [see what should I do when someone answers my question](https://stackoverflow.com/help/someone-answers). – Gonçalo Peres Oct 05 '22 at 11:19