[Python] Error in Pandas and Openpyxl - "Value must be either numerical or a string containing...

I am facing an issue while reading an Excel file using Pandas and Openpyxl. The error message I receive is:

ValueError: Value must be either numerical or a string containing a wildcard


This error is raised from the filters.py file in the openpyxl library during the execution of pd.read_excel().

I am trying to read an Excel file, process the data, and filter it based on certain conditions. I have attempted various approaches to handle non-numeric values in the "Phone country code" column, but the error persists.

import pandas as pd
import openai
import os

# Set your OpenAI GPT-3 API key
openai.api_key = 'key'

# Load the Excel file
file_path = r'C:\Users\ivatu\Downloads\iglistaff.xlsx'
df = pd.read_excel(file_path)

# Function to identify names using GPT-3
def identify_names(full_name):
response = openai.Completion.create(
engine="text-davinci-002",
prompt=f"Identify names in {full_name}.",
max_tokens=50
)
return response.choices[0].text.strip()

# Handle non-numeric values in the "Phone country code" column
df['Phone country code'] = df['Phone country code'].apply(lambda x: str(x) if str(x).isnumeric() else '0')

# Retry reading the Excel file with the updated dtype
df = pd.read_excel(file_path)

# Apply GPT-3 to identify names and replace non-obvious names with "Coach"
df['Full name'] = df['Full name'].apply(lambda name: identify_names(name) if not name.replace(" ", "").isalpha() else name)
df['Full name'] = df['Full name'].apply(lambda name: "Coach" if "Coach" in name else name)

# Filter the data based on the condition
filtered_data = df[(df['Followers count'] > 1000) &
(df['Phone country code'].apply(lambda x: str(x) in ['1', '44', '0'])) &
(df['Is private'] == 'NO')]

# Extract the required columns
extracted_data = filtered_data[['Username', 'Full name', 'Followers count']]

output_folder = os.path.join(os.path.expanduser("~"), "Downloads", "output_path_folder")
os.makedirs(output_folder, exist_ok=True)

# Save the extracted data to a new Excel file in the output folder
output_path = os.path.join(output_folder, 'output_file.xlsx')
extracted_data.to_excel(output_path, index=False)

print("Extraction and filtering completed. Results saved to:", output_path)


I am using Pandas version X.X.X and Openpyxl version Y.Y.Y. The error is related to the filters.py file in the openpyxl library. I have non-numeric values in the "Phone country code" column, and I am attempting to handle them by converting them to '0'.

Expected Outcome:

I expect to read the Excel file, handle non-numeric values in the specified column, and continue with further processing without encountering the mentioned error.

I am looking for guidance on how to resolve this issue and successfully read the Excel file with the mentioned conditions.

Continue reading...