Split xlsx file into other xlsx files based on first column with Python

user_12005383 0
Viewed 71

I have a large .xlsx file that I want to split into several files based on the first column. The data structure is kinda iffy which complicates it a lot and I am new to excel in python.

Basically I need to create a separate file for every Row starting with "Brand1" (see below) and another for rows starting with "Brand2" and so on. Whats the best way to do this?

Brand_name               Sales                 Year
  BRAND1                1000000                2018 
  BRAND1                1100000                2019
  BRAND2                 900000                2018
  BRAND2                 500000                2019
  BRAND3                 200000                2019
  BRAND4                1200000                2019
pandas excel python
1 Answers

You might want to do this.

# list all the brands (unique values)
brands = list(df['Brand_name'].unique())

# for each brand, we want to filter the dataframe based on the brand name
# and then export it to excel
for brand in brands:
  filtered_df = df[df['Brand_name'] == brand]
  filtered_df.to_excel(brand + '.xlsx', index=False)
user_73979530
Related