Python program to split Excel file Into Multiple Files By Column Values Error

Viewed 51

I'm trying to develop a Python program to split excel spreadsheet in the "Query_Tab" folder into separate excel files based on a value in column name "Site" and output these separate files in "output" folder within "Query Tab" folder. All folders are on a shared drive. The program that I have is giving me the following error:

raise AttributeError("Can only use .str accessor with string " "values!") AttributeError: Can only use .str accessor with string values!

The values in "Site" column are numeric, up to 4 digits long.

Here is my code:

import pandas as pd #pip install pandas import os

df = pd.read_excel(r'\\folder\\folder\\folder\\py_prgms\\Query_Tab\\TEST_Excel_File.xlsx').dropna()
column_name = 'Site'
replace_symbols = ['>', '<', ':', '"', '/', '\\\\', '\|', '\?', '\*']
df[column_name] = (
    df[column_name].replace(replace_symbols, '', regex=True).str.strip().str.title()
)
unique_values = df[column_name].unique()

for unique_value in unique_values:
    df_output = df[df[column_name].str.contains(unique_value)]
    output_path = os.path.join('output', str(unique_value) + '.xlsx')
    df_output.to_excel(output_path, sheet_name=unique_value, index=False)
0 Answers
Related