Concatenate CSVs into XLSX files based on filename (pandas)

Question

I have a bunch of CSVs with names '<3-letter-string> YYYY.csv'. There are four different versions of <3-letter-string>, and I want to sort the csvs into four xlsxs, each identified by that three letter string.

My code:

import pandas as pd
import os
full_df = pd.DataFrame()
for filename in os.listdir('C:/Users/XXXXXX/ZZZZZZ'):
    if filename.endswith(".csv"): 
        print(filename)
        df = pd.read_csv(filename, skiprows=1, names=['ID','Units Sold','Retail Dollars'])
        df['Year'] = filename[-8:-4]
        full_df = pd.concat([full_df, df])
        full_df.to_excel(filename[0:3] + '.xlsx', index=False) 

This makes four different xlsxs, which is what I want, but they're all a mixture of the different csvs.

How do I tell pandas to group them into four separate xlsxs according to the filename? My initial thought is to include filename slicing in the penultimate line and create four different concatenated full_df dataframes to write separately, but I'm not sure how.

Answer 1

import pandas as pd
import os

def Get_Yo_Fantasy_Hennnnnyyyyy():
  full_df = pd.DataFrame()
  for filename in os.listdir("path"):
    if filename.endswith(".csv"):
        print(filename)
        df = pd.read_csv(
            filename,
            skiprows=1,
            names=["ID", "Units Sold", "Retail Dollars"])
        df["Year"] = filename[-8:-4]
        df["Type"] = filename[0:3]
        full_df = pd.concat([full_df, df])
        for i in list(full_df.Type.unique()):
            full_df[full_df.Type.str.contains(i)].to_excel(
                "{}".format(i) + ".xlsx", index=False)

 Get_Yo_Fantasy_Hennnnnyyyyy()