I want to take multiple .csv files and convert them to Excel worksheets in one workbook, specifically using Pandas.

I finally got this to work, but I know the code itself is of poorly written.

Any suggestions on how to clean this up? "Beautify is better than Ugly"

Here is the code:

import pandas as pd
import os
import openpyxl as xl

directory = os.path.join(os.curdir, "data/")
new_xl_file_path = "csv_merge.xlsx"
new_xl_file = xl.Workbook()  # Create a new Excel workbook
new_xl_file.save(new_xl_file_path)
name_list = os.listdir(directory)  # file1.csv, file2.csv, file3.csv, etc...

full_path_list = []  # For reading with pd.read_csv()
data_frame_list = []  # List to save .csv dataframes

for filename in os.listdir(directory):
    f = os.path.join(directory, filename)  # Get full path name
    df = pd.read_csv(f)
    data_frame_list.append(df)

counter = 0
with pd.ExcelWriter(new_xl_file_path) as writer:
    for dataframe in data_frame_list:
        dataframe.to_excel(writer, index=False, sheet_name=name_list[counter])
        counter += 1