How to find specifics keywords in a xlsx file using python?

Question

I have a xlsx file with game names, their prices and the links to their sites where we can find them. I'm trying to make a program where someone can type one word and the program will search for every game with this word in its name and show its price and link.

      nome                  preco    link
0   Fifa 20              R$ 164,90  
1   FIFA 19              R$ 84,90    https://www.americanas.com.br/produto/13379718...
2   EFootball PES 2020   R$ 93,88    https://www.americanas.com.br/produto/13456974...
3   Forza Horizon 4      R$ 199,90   https://www.americanas.com.br/produto/13379732...
4   Mortal Kombat 11     R$ 129,90   https://www.americanas.com.br/produto/13416378...

Since I'm a beginner I don't know how to start, even though I already tried regex. Could someone help me? Thank you

Take a look at `openpyxl` - it's a module for reading/writing XLSX files — Collin Heist, Nov 02 '20 at 20:50
My xlsx file has about 90 items, and some games are the same but with different prices and from different stores. I would like to create a program using regex that can take a word like 'Fifa", and find every game with this word, showing their price and link as well. — Nícolas Costa, Nov 02 '20 at 21:32

Tom · Answer 1 · 2020-11-02T21:57:52.577

1

Start with loading the whole thing into a pandas dataframe.

import pandas as pd
df = pd.read_excel('my_file.xlsx')

Then assuming you don't need a fancy UI:

nm = input('Enter a partial name of a game: ')
print(df[df['name'].str.contains(nm)])

Now that just prints the segment of the dataframe. If you wanted to grab a particular cell:

link = df.loc[df['name'].str.contains(nm), 'link']
print(link)

If you want to see all the games which include nm but only certain columns, you can either do this... (include to_string() to make sure whole dataframe displays)

disp_cols = ['nome', 'preco', 'link']
print(df.loc[df['name'].str.contains(nm), disp_cols].to_string())

Or you can loop through them similar to @b-bogart solution. I also second that using .lower() is usually a good idea to make life easier on both the user and to avoid troublesome data cleanup.

edited Nov 02 '20 at 21:57

answered Nov 02 '20 at 20:59

Tom

1,003
2
13
25

The problem is that my xlsx file has many items, about 90, and some of them have the same name or similar name. I'm trying to implement regex but i dontknow how to do it – Nícolas Costa Nov 02 '20 at 21:10
Meaning there are several "FIFA" games? – Tom Nov 02 '20 at 21:31
Yess, and other games as well that repeat – Nícolas Costa Nov 02 '20 at 21:35
So if someone types in "FIFA" what output do you want? – Tom Nov 02 '20 at 21:40
Every game with fifa in its name, their prices and their links – Nícolas Costa Nov 02 '20 at 21:41
I added some comments. I don't think you need to bother with regex for your purpose FWIW. – Tom Nov 02 '20 at 21:58
Thank you for your help! – Nícolas Costa Nov 02 '20 at 22:18

score 0 · Accepted Answer · answered Nov 02 '20 at 21:11

I would use pandas to import and search, then loop through the results and print your output.

import pandas as pd
df = pd.read_excel('64653127.xlsx')

search = 'fifa'

#use str.lower() for case insensitive results
results = df[df['nome'].str.lower().str.contains(search)]
#print(results)
for index, row in results.iterrows():
    print(str(row['nome']) + ' link: ' + str(row['link']))

The output is:

Fifa 20 link: nan
FIFA 19 link: https://www.americanas.com.br/produto/13379718...

Thank you, you really helped me! :) – Nícolas Costa Nov 02 '20 at 21:56 — Nícolas Costa, Nov 02 '20 at 21:56

How to find specifics keywords in a xlsx file using python?

2 Answers2