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:
I want for each element of the column “FRUIT”, to be on a different excel file like this :
excel-BANANA.xlsx :
excel-CHERRY.xlsx :
excel-APPLE.xlsx :
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