I have a loop that read Excel sheets in a document. I want to store them all in a list:
DF_list= list()
for sheet in sheets:
df= pd.read_excel(...)
DF_list = DF_list.append(df)
If I type:
[df df df df]
it works.
Sorry I have a Matlab background and not very used to Python, but I like it. Thanks.
.append() modifies a list and returns None.
You override DF_list with None in your first loop and the append will fail in the second loop.
Therefore:
DF_list = list()
for sheet in sheets:
DF_list.append(pd.read_excel(...))
Or use a list comprehension:
DF_list = [pd.read_excel(...) for sheet in sheets]
Try this
DF_list= list()
for sheet in sheets:
df = pd.read_excel(...)
DF_list.append(df)
or for more compact python, something like this would probably do
DF_list=[pd.read_excel(...) for sheet in sheets]
If you will use parameter sheet_name=None:
dfs = pd.read_excel(..., sheet_name=None)
it will return a dictionary of Dataframes:
sheet_name : string, int, mixed list of strings/ints, or None, default 0
Strings are used for sheet names, Integers are used in zero-indexed
sheet positions.
Lists of strings/integers are used to request multiple sheets.
Specify None to get all sheets.
str|int -> DataFrame is returned.
list|None -> Dict of DataFrames is returned, with keys representing
sheets.
Available Cases
* Defaults to 0 -> 1st sheet as a DataFrame
* 1 -> 2nd sheet as a DataFrame
* "Sheet1" -> 1st sheet as a DataFrame
* [0,1,"Sheet5"] -> 1st, 2nd & 5th sheet as a dictionary of DataFrames
* None -> All sheets as a dictionary of DataFrames
Complete solution is as follows:
# (0) Variable containing location of excel file containing many sheets
excelfile_wt_many_sheets = 'C:\this\is\my\location\and\filename.xlsx'
# (1) Initiate empty list to hold all sheet specific dataframes
df_list= []
# (2) create unicode object 'sheets' to hold all sheet names in the excel file
df = pd.ExcelFile(excelfile_wt_many_sheets)
sheets = df.sheet_names
# (3) Iterate over the (2) to read in every sheet in the excel into a dataframe
# and append that dataframe into (1)
for sheet in sheets:
df = pd.read_excel(excelfile_wt_many_sheets, sheet)
df_list.append(df)
Actually there's no need to define new list to store bunch of dataframes. The pandas.ExcelFile function applied on excel file with multiple sheets returns ExcelFile object which is a collection that can catch hold bunch of dataframes together. Hope the below code helps.
import pandas as pd
df = pd.ExcelFile('C:\read_excel_file_with_multiple_sheets.xlsx')
sheet_names_list = df.sheet_names
for sheet in sheet_names_list:
df_to_print = df.parse(sheet_name=sheet)
print(df_to_print)
list. Or just runhelp([].append)from the interpreter.