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

Issue

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

Solution

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 to respond to the first comment

You can also specify values in .isin and get their location in terms of index and column with the following code (not pretty):

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]
    for row, vals in df_x.iteritems():
        for val in vals.index:
            print(f"index:{val:4}     column:  {col}")

Answered By - mouwsy