0

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
Walid Chiko
  • 65
  • 8
  • Does any of that work? What errors are you getting? – BAC83 Nov 20 '19 at 09:56
  • i get : File "", line 6 ''.join(L[i][0])=pd.read_excel(xls,'Sheet1') ^ SyntaxError: can't assign to function call – Walid Chiko Nov 20 '19 at 09:58
  • I'm not sure actually on what you're trying to do - are you trying to loop over a bunch of excel files, and put them all into separate dataframes, named as you wish? – BAC83 Nov 20 '19 at 10:04
  • Yes ! i need to create diffirents dataframe from diffirents excel files – Walid Chiko Nov 20 '19 at 10:06

2 Answers2

1

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

BAC83
  • 811
  • 1
  • 12
  • 27
0

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)
Rohit Chauhan
  • 1,119
  • 1
  • 12
  • 30
  • That's not working , because i need different df ( like df per excel file ) so i need to get df1 , df2 , df3.... without append , Thank's – Walid Chiko Nov 20 '19 at 10:09
  • Maybe you can append one column with one static excel filename and then filter the data for the particular need. – Rohit Chauhan Nov 20 '19 at 10:12