How to get excel cell/row number of a value using python?

Question

I have dataframe like as shown below

val
5
7
17
95
23
45

df = pd.read_clipboard()

I would like to get the excel cell/index/row number for each of the value

I was trying something like below but not sure whether I am overcomplicating it

for row in range(1,99999):
    for col in range(1,2999999):
        if wb.sheets[1].range((row,col)).value == 5:
            print("The Row is: "+str(row)+" and the column is "+str(col))
        else
            row = row + 1

I also tried something like below but doesn't work either

wb.sheets[1].range("A9:B500").options(pd.DataFrame).value

Basically, I have a list of values and would like to find out in which column and row do they appear in excel. Can someone help me please?

I expect my output to be like below. Excel row numbers are extracted/found from excel

See this: stackoverflow.com/q/71537265/4961700

Solar Mike
– Solar Mike

2022-03-19 12:02:51 +00:00
Commented Mar 19, 2022 at 12:02 — Solar Mike
– Solar Mike, Commented Mar 19, 2022 at 12:02
This is the same as your other question.

Charlie Clark
– Charlie Clark

2022-03-19 14:04:00 +00:00
Commented Mar 19, 2022 at 14:04 — Charlie Clark
– Charlie Clark, Commented Mar 19, 2022 at 14:04

mouwsy · Accepted Answer · 2023-01-15 12:41:12Z

1

Keep in mind that the index of pandas dataframes is zero-based and the row numbers of excel worksheets are one-based, therefore, if you would like to solve this issue with pandas

df["index"] = df.index.astype(int) + 1

is correct. However, note that the picture in your question shows a part of an excel worksheet, which has an active filter applied. The solution presented here works only, if the data in excel is not filtered.

Update

You can also specify values in .isin and get their location in terms of row index and column index. The following code prints – as an example – the row index and column index in pandas notation of all 5 and 2 occurrences in the dataframe (because of isin([5, 2])):

import pandas as pd

df = pd.DataFrame((
    (2, 5, 8),
    (20, 5, 772),
    (0, 20, 5),
    (2, 0, 0),
    (76, 35, 66),
    (23, 21, 29),
    (5, 55, 88),
    (44, 23, 5),
    (1, 2, 3),
    ), columns=["a", "b", "c"])

cols = df.columns.to_list()
for col in cols:
    filt = df[col].isin([5, 2])
    df_x = df.loc[filt, col]
    for row, val in df_x.items():
        print(f"row index: {row}     column index: {df.columns.get_indexer([col])[0]}")

edited Jan 15, 2023 at 12:41

answered Mar 19, 2022 at 23:32

mouwsy

2,12718 silver badges28 bronze badges

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

2 Comments

mouwsy Over a year ago

Yes, I've updated the answer.

The Great Over a year ago

I am encountering some challenge with similar xlwings problem to find and replace data in template excel file. Is this something that you would be able to help me with? stackoverflow.com/questions/72812206/…

Ítalo De Pontes Oliveira · Accepted Answer · 2022-03-19 20:19:58Z

1

I think that the most simple solution to get the index row is using this command:

df["index"] = df.index.to_list()

A new column will be created with the index values. So, you can use this column to format as you want.

index
1
2
6
7
10
12

answered Mar 19, 2022 at 20:19

Ítalo De Pontes Oliveira

3641 silver badge9 bronze badges

1 Comment

The Great Over a year ago

Do you know how can we pass in a list of values and get their corresponding location? Instead of getting location for all values?

Collectives™ on Stack Overflow

How to get excel cell/row number of a value using python?

2 Answers 2

Update

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related