Pandas reading multiple files from different folders

Question

I have the same file with quarterly data saved in different folders corresponding to the quarter. In other words, a quarter 1 folder, quarter 2 , quarter 3, quarter 4. This is the only difference in the file path. I am looking to read all four files in and concatenate them into one database. I can do this manually using a version of the simplified code below and changing the period each time.

period = ‘Q1’

filepath = ‘filepath/’ + period

file = filepath + ‘/file.xls’

df = pd.read_excel(file)

I would like to automate it with some form of for loop (I assume). That loops through the 4 periods, reads the file into a database and then concatenates. I have read other answers as to how this can be done with files in the same folder. But am struggling to do it where the file path changes. Manually putting the files into the same folder is not a desirable solution.

I tried making period a tuple and a list containing all 4 periods then a simple for loop but this didn’t work. I got the following error message.

TypeError: Can't convert 'list' object to str implicitly

Greatly appreciate any advice.

The code I have given is what works. I get the error when I convert period to be a tuple or list such as ('Q1', 'Q2', 'Q3', 'Q4') which I want to loop through. — jyh5
– jyh5, Commented Sep 26, 2019 at 14:43
To fix the problem, I need the code that causes the problem, not the code that works. — Thomas Weller
– Thomas Weller, Commented Sep 26, 2019 at 14:44

Julia K · Accepted Answer · 2019-09-26 14:44:23Z

1

How about you first use list comprehension to get a list of all files:

periods= ["Q1", "Q2", "Q3", "Q4"]
files = ["filepath/"+ p + "/file.xls" for p in periods]

and then load them all into a list of data frames with

dfs = []
for f in files:
    df = pd.read_excel(f)
    dfs.append(df)

answered Sep 26, 2019 at 14:44

Julia K

4073 silver badges10 bronze badges

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

Comments

Porada Kev · Accepted Answer · 2019-09-26 14:44:25Z

1

You can use these loops to create full file paths and to iterate over them to create one DataFrame containing all the files.

filepath = 'path/'
file = 'file.xlsx'
periods=['Q1','Q2','Q3','Q4']

files = []
for p in periods:
    files.append(filepath+p+'/'+file)
files

data = []
for f in files:
    data.append(pd.read_excel(f))

df = pd.concat(data)

answered Sep 26, 2019 at 14:44

Porada Kev

5131 gold badge12 silver badges24 bronze badges

Comments

Rob Bricheno · Accepted Answer · 2019-09-26 14:42:58Z

0

You probably want something like this:

periods = ['Q1', 'Q2', 'Q3', 'Q4']

df = None
for period in periods:
    filepath = 'filepath/' + period
    file = filepath + '/file.xls'
    if df is None:
        df = pd.read_excel(file)
    else:
        df.append(pd.read_excel(file))

answered Sep 26, 2019 at 14:42

Rob Bricheno

4,66317 silver badges30 bronze badges

Comments

Paridhi Choudhary · Accepted Answer · 2019-09-26 14:48:22Z

0

You could try something like this:

complete_df = pd.DataFrame()
for i in range(1,5):
  quarter = 'Q'+str(i)
  filepath = 'filepath/' + quarter
  file = filepath + '/file.xls'
  df = pd.read_excel(file)
  complete_df = complete_df.append(df)

answered Sep 26, 2019 at 14:48

Paridhi Choudhary

13 bronze badges

Collectives™ on Stack Overflow

Pandas reading multiple files from different folders

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related