Extract values from excel sheet against a string using python

Question

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:


George	29	Farmer
Frank	52	Engineer

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.

Tim Roberts · Accepted Answer · 2022-02-17 05:20:16Z

1

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

You can store the row separately:

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

answered Feb 17, 2022 at 5:20

Tim Roberts

55.3k4 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CJR · Accepted Answer · 2022-02-17 06:17:15Z

0

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'

answered Feb 17, 2022 at 6:17

CJR

764 bronze badges

Comments

Rookie_Coder2318 · Accepted Answer · 2022-02-17 06:18:40Z

0

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

answered Feb 17, 2022 at 6:18

Rookie_Coder2318

911 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Extract values from excel sheet against a string using python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related