How do I import an excel file and search for specific record using python and pandas?

user_14067056 0
Viewed 148

I’m trying to import an excel file and search for a specific record

Here’s what I have come up with so far, which keeps throwing error.

The excel spread sheet has two columns Keyword and Description, each keyword is around 10 characters max, and description is around 150 characters max.

I can print the whole sheet in the excel file without any errors using print(df1) but as soon as I try to search for a specific value it errors out.

Error

ValueError: ('Lengths must match to compare', (33,), (1,))

Code

import pandas as pd

file = 'Methods.xlsx'
df = pd.ExcelFile(file)
df1 = df.parse('Keywords')
lookup = df1['Description'].where(df1['Keyword']==["as"])
print (lookup)
pandas dataframe excel python
2 Answers

the filter syntax is like this

df_filtered = df[df[COLUMN]==KEYWORD]

so in your case it'd be

lookup = df1[df1['Keyword'] == "as"]['Description']

or the whole code

import pandas as pd
file = 'Methods.xlsx'
df = pd.ExcelFile(file)
df1 = df.parse('Keywords')
lookup = df1[df1['Keyword'] == "as"]['Description']
print (lookup)

breaking it down:

is_keyword = df1['Keyword'] == "as"

this would return a series containing True or False depending on if the keyword was present.
then we can filter the dataframe to get those rows that have True with.

df_filtered = df1[is_keyword]

this will result in all the columns, so to get only the Description column we get it by

lookup = df_filtered['description']

or in one line

lookup = df1[df1['Keyword'] == "as"]['Description']
user_5466340

adding to the elaborate answer given by @Jimmar above:

Just for syntactical convenience, you could write the code like this:

lookup = df1[df1.keyword == "as"].Description

Pandas provides column name lookup like it is a member of DataFrame class( use of dot notation). Please note that the for using this way the column names should not have any spaces in them

user_123378370
Related