import all csv files in directory as pandas dfs and name them as csv filenames

Question

I'm trying to write a script that will import all .csv files in a directory to my workspace as dataframes. Each dataframe should be named as the csv file (minus the extension: .csv).

This is what i have so far, but struggling to understand how to assign the correct name to the dataframe in the loop. I've seen posts that suggest using exec() but this does not seem like a great solution.

path = "../3_Data/Benefits"                     # dir path
all_files = glob.glob(os.path.join(path, "*.csv")) #make list of paths

for file in all_files:
    dfn = file.split('\\')[-1].split('.')[0] # create string for df name
    dfn = pd.read_csv(file,skiprows=5) # This line should assign to the value stored in dfn

Any help appreciated, thanks.

Answer 1

DataFrame have no name their index can have a name. This is how to set it.

import glob
import os

path = "./data/"
all_files = glob.glob(os.path.join(path, "*.csv")) #make list of paths

for file in all_files:
    # Getting the file name without extension
    file_name = os.path.splitext(os.path.basename(file))[0]
    # Reading the file content to create a DataFrame
    dfn = pd.read_csv(file)
    # Setting the file name (without extension) as the index name
    dfn.index.name = file_name

# Example showing the Name in the print output

#      FirstYear  LastYear
# Name                     
# 0         1990      2007
# 1         2001      2001
# 2         2001      2008