CODEWITHSUNDEEP
pythonpandas
Nidhin_toms
Nidhin_toms

Reputation: 737

Using the read_excel function in panda to go through all the columns in an excel file

What the code does below is read a column (named "First") and look for the string "TOM".

I want to go through all the columns in the file ( not just the "First" column) - i was thinking of doing something like excelFile[i][j] where i and j are set in a loop but that does not work. Any ideas?

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import re

excelFile=pd.read_excel("test.xls")

for i in excelFile.index:
match=re.match(".*TOM.*",excelFile['First'][i])
if match:
    print(excelFile['First'][i])
    print("check")

Upvotes: 0

Views: 2670

Answers (2)

pistolpete
pistolpete

Reputation: 998

excelFile.any(axis=None) will return a boolean value telling you if the value was found anywhere in the dataframe.

Documentation for pd.DataFrame.any

To print if the value was found, get the columns from the dataframe and use iterrows:

# Create a list of columns in the dataframe
columns = excelFile.columns.tolist()

# Loop through indices and rows in the dataframe using iterrows
for index, row in excelFile.iterrows():
    # Loop through columns
    for col in columns:
        cell = row[col]
        # If we find it, print it out
        if re.match(".*TOM.*", cell):
            print(f'Found at Index: {index} Column: {col}')

Upvotes: 2

Jessica
Jessica

Reputation: 3173

something like this loops through all of the columns and looking for a string match 

for column in excelFile:
      if 'tom' in column.lower():
          print(column)

Upvotes: 1

Related Questions