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
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.