I am trying to read a value from excel sheet against a string that I pass in python.

for example:

Sample.xlsx sheet has the following table:

I want to read the value from a given column of my choice when I pass a value in the "Name" column

For example I want my variable in python to be assigned value 29 when I pass Name "George" as string and column 2. For column 3 it should get "Farmer".

I only got as far as reading the table from my excel sheet.

import panda as pd

df = pd.read_excel("Sample.xlsx")

Basically this is vlookup operation, in excel:

value = vlookup("George", A2:C3,2,FALSE)
value is 29 here

value = vlookup("George", A2:C3,3,FALSE)
value is Farmer here

Trying to do this in python.

CodePudding user response：

df.loc[df['Name'] == 'George']['Age']

You can store the row separately:

row = df.loc[df['Name'] == 'George']
print(row['Age'])

CodePudding user response：

I think this will give you what you want, but I don't think it's a good practice. When designing a function, I like to return value with one specific data type, either it returns str or returns int.

def find_info(df: pd.DataFrame,
              name: str,
              col: int):
    index = df[df == name].iloc[:, 0].dropna().index[0]
    
    return df.iloc[index, col]

var = find_info(df, 'George', 1) # var = 29
var = find_info(df, 'George', 2) # var = 'Farmer'
var = find_info(df, 'Frank', 1) # var = 52
var = find_info(df, 'Frank', 2) # var = 'Engineer'

CodePudding user response：

This works for me so far:

import openpyxl

wb = openpyxl.load_workbook("Sample.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=1,min_col=1,max_row=3,max_col=3):
    for cell in row:
        if(cell.value == "George"):
            print(row[1].value) # prints 29
            print(row[2].value) # prints Farmer