CODEWITHSUNDEEP
pythonexcelpandas
Walid Chiko
Walid Chiko

Reputation: 65

How to create variables and read several excel files in a loop with pandas?


L=[('X1',"A"),('X2',"B"),('X3',"C")]
for i in range (len(L)):
    path=os.path.join(L[i][1] + '.xlsx')
    book = load_workbook(path)
    xls = pd.ExcelFile(path)
    ''.join(L[i][0])=pd.read_excel(xls,'Sheet1')

File "<ipython-input-1-6220ffd8958b>", line 6
    ''.join(L[i][0])=pd.read_excel(xls,'Sheet1')
    ^
SyntaxError: can't assign to function call

I have a problem with pandas, I can not create several dataframes for several excel files but i don't know how to create variables

I'll need a result that looks like this :

X1 will have dataframe of A.xlsx X2 will have dataframe of B.xlsx . . .

Solved : 

d = {}

for i,value in L:
    path=os.path.join(value + '.xlsx')
    book = load_workbook(path)
    xls = pd.ExcelFile(path)
    df = pd.read_excel(xls,'Sheet1')
    key = 'df-'+str(i)
    d[key] = df

Upvotes: 0

Views: 1588

Answers (2)

BAC83
BAC83

Reputation: 891

Main pull:

I would approach this by reading everything into 1 dataframe (loop over files, and concat):

import os
import pandas as pd

files = [] #generate list for files to go into

path_of_directory = "path/to/folder/"

for dirname, dirnames, filenames in os.walk(path_of_directory):
    for filename in filenames:
        files.append(os.path.join(dirname, filename))

output_data = [] #blank list for building up dfs

for name in files:
    df = pd.read_excel(name)
    df['name'] = os.path.basename(name)
    output_data.append(df)

total = pd.concat(output_data, ignore_index=True, sort=True)

Then:

From then you can interrogate the df by using df.loc[df['name'] == 'choice']

Or (in keeping with your question):

You could then split into a dictionary of dataframes, based on this column. This is the best approach...

dictionary = {}
df[column] = df[column].astype(str)

col_values = df[column].unique()

for value in col_values:
    key_name = 'df'+str(value)
    dictionary[key_name] = copy.deepcopy(df)
    dictionary[key_name] = dictionary[key_name][df[column] == value]
    dictionary[key_name].reset_index(inplace=True, drop=True)

return dictionary

The reason for this approach is discussed here: Create new dataframe in pandas with dynamic names also add new column which basically says that dynamic naming of dataframes is bad, and this dict approach is best

Upvotes: 1

Rohit Chauhan
Rohit Chauhan

Reputation: 1159

This might help.

files_xls = ['all your excel filename goes here']
df = pd.DataFrame()
for f in files_xls:
  data = pd.read_excel(f, 'Sheet1')
  df = df.append(data)
print(df)

Upvotes: 0

Related Questions