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

Time:03-20

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

enter image description here

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

enter image description here

CodePudding user response:

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

CodePudding user response:

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.

  • Related