CODEWITHSUNDEEP
pythonexcelpandasxlwings
The Great
The Great

Reputation: 7713

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

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

Upvotes: 0

Views: 6305

Answers (2)

mouwsy
mouwsy

Reputation: 1933

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]}")

Upvotes: 1

Ítalo De Pontes Oliveira
Ítalo De Pontes Oliveira

Reputation: 364

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

Upvotes: 1

Related Questions