Skip to content
Advertisement

Generate Excel files for each of element in a column using Python

I want to generate Excel files for each of element in a column : I will describe what I want with pictures :

I have an excel file like this:

enter image description here

I want for each element of the column “FRUIT”, to be on a different excel file like this :

excel-BANANA.xlsx :

enter image description here

excel-CHERRY.xlsx :

enter image description here

excel-APPLE.xlsx :

enter image description here

Is it possible ?

Advertisement

Answer

As BoarGules mentioned, you can use pandas.

Try this:

import pandas as pd

# Removes the default header style so it looks like your screenshots
pd.io.formats.excel.header_style = None

df = pd.read_excel(r"fruits.xlsx")

for fruit in df['FRUIT'].unique():
    df.loc[df['FRUIT'] == fruit].to_excel(f"excel-{fruit}.xlsx", index=False)

It reads the fruits.xlsx, iterates over the unique values in the FRUIT column (in this case: BANANA, CHERRY, and APPLE), and filters the dataframe to include only that one fruit each iteration.

to_excel(f"excel-{fruit}.xlsx", index=False) saves the dataframe as an Excel file without including an index (row numbers).

See https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html

See also https://docs.python.org/3/tutorial/inputoutput.html#formatted-string-literals

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement