How to get filter cell row number in excel with python?

Question

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

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
– Timeless, Commented Oct 5, 2022 at 8:29

Gonçalo Peres · Accepted Answer · 2022-10-05 08:40:25Z

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.

answered Oct 5, 2022 at 8:40

Gonçalo Peres

13.8k5 gold badges73 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bireysel Finans Over a year ago

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

Gonçalo Peres Over a year ago

@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. 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 Over a year ago

@BireyselFinans There is a good practice on StackOverflow related with editing questions - avoid chameleon questions. Also, see what should I do when someone answers my question.

Collectives™ on Stack Overflow

How to get filter cell row number in excel with python?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related