Read multiple csv files into separate dataframes using pandas

Question

I like to read two csv files from a particular folder into two separate dataframes.

The two file names are: 23314621_MACI_NAV.CSV and 23314623_MACI_Holding.CSV

The file second part of the file names are fixed MACI_NAV.CSV and MACI_Holding.CSV, however the first part of the file name which are numbers change everyday.

I like to read them into two different dataframe by trying this:

import pandas as pd
import glob

msci_folder = 'N:/Operation/Daily CDS E_Report/CDS/MACI/'
mscifile = glob.glob(msci_folder + "\*.csv")

for file in mscifile:
    df_nav=pd.read_csv(file)
    df_holding=pd.read_csv(file)

It seems like both lines are reading the same file, how do I make them read different files (second file)?

jezrael · Accepted Answer · 2018-08-29 10:17:08Z

8

If want create list of DataFrames:

dfs = []
for file in mscifile:
    df = pd.read_csv(file)
    dfs.append(df)

Or use list comprehension:

dfs = [pd.read_csv(file) for file in mscifile]

print (dfs[0])
print (dfs[1])

Another solution is create dictionary of DataFrames with keys by last substring after _ in filename:

from os.path import splitext, basename

dfs = {splitext(basename(fp))[0].split('_')[-1] : pd.read_csv(fp) for fp in mscifile}
print (dfs)

print (dfs['NAV'])
print (dfs['Holding'])

edited Aug 29, 2018 at 10:17

answered Aug 29, 2018 at 9:43

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sujit patel · Accepted Answer · 2024-12-26 10:21:50Z

you can do something like this.

import pandas as pd
import glob

msci_folder = 'N:/Operation/Daily CDS E_Report/CDS/MACI/'

mscifiles = glob.glob(msci_folder + "*.csv")

# Initialize variables to hold the dataframes
df_nav = None
df_holding = None

# Iterate over the list of files to find the specific ones
for file in mscifiles:
    # Check if the file name ends with 'MACI_NAV.CSV'
    if 'MACI_NAV.CSV' in file:
        df_nav = pd.read_csv(file)
        print(f"Loaded NAV file: {file}")
    # Check if the file name ends with 'MACI_Holding.CSV'
    elif 'MACI_Holding.CSV' in file:
        df_holding = pd.read_csv(file)
        print(f"Loaded Holding file: {file}")

# Now df_nav and df_holding will contain the data from the respective CSVs

Collectives™ on Stack Overflow

Read multiple csv files into separate dataframes using pandas

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related