I have a large spreadsheet file [.xlsx] that I'm processing using python pandas. It happens that I need data from two tabs [sheets] in that large file. One of the tabs has a ton of data and the other is just a few square cells.
When I use
pd.read_excel[] on any worksheet, it looks to me like the whole file is loaded [not just the worksheet I'm interested in]. So when I use the method twice [once for each sheet], I effectively have to suffer the whole workbook being read in twice [even though we're only using the specified sheet].
How do I only load specific sheet[s] with pd.read_excel[]?
smci
30.5k18 gold badges110 silver badges145 bronze badges
asked Oct 23, 2014 at 4:21
1
Try pd.ExcelFile:
xls = pd.ExcelFile['path_to_file.xls']
df1 = pd.read_excel[xls, 'Sheet1']
df2 = pd.read_excel[xls, 'Sheet2']
As noted by @HaPsantran, the entire Excel file is read in during the ExcelFile[] call [there doesn't appear to be a way around this].
This merely saves you from having to read the same file in each time you want to access a new sheet.
Note that the sheet_name argument to pd.read_excel[] can be the name of the sheet [as above], an integer specifying the sheet number [eg 0, 1, etc], a list of sheet names or indices, or None. If a list is provided, it returns a dictionary where the keys are the sheet names/indices and the values are the data frames. The default is to simply return the first sheet [ie, sheet_name=0].
If
None is specified, all sheets are returned, as a {sheet_name:dataframe} dictionary.
answered Oct 23, 2014 at 5:16
NoahNoah
20.1k8 gold badges62 silver badges71 bronze badges
11
There are a few options:
Read all sheets directly into an ordered dictionary.
import pandas as pd
# for pandas version >= 0.21.0
sheet_to_df_map = pd.read_excel[file_name, sheet_name=None]
# for pandas version < 0.21.0
sheet_to_df_map = pd.read_excel[file_name, sheetname=None]
Read the first sheet directly into dataframe
df = pd.read_excel['excel_file_path.xls']
# this will read the first sheet into df
Read the excel file and get a list of sheets. Then chose and load the sheets.
xls = pd.ExcelFile['excel_file_path.xls']
# Now you can list all sheets in the file
xls.sheet_names
# ['house', 'house_extra', ...]
# to read just one sheet to dataframe:
df = pd.read_excel[file_name, sheet_name="house"]
Read all sheets and store it in a dictionary. Same as first but more explicit.
# to read all sheets to a map
sheet_to_df_map = {}
for sheet_name in xls.sheet_names:
sheet_to_df_map[sheet_name] = xls.parse[sheet_name]
# you can also use sheet_index [0,1,2..] instead of sheet name.
Thanks @ihightower for pointing it out way to read all sheets and @toto_tico,@red-headphone for pointing out the version issue.
sheetname : string, int, mixed list of strings/ints, or None, default 0 Deprecated since version 0.21.0: Use sheet_name instead Source Link
answered Sep 6, 2017 at 18:16
Vikash SinghVikash Singh
12.3k8 gold badges37 silver badges67 bronze badges
2
You can also use the index for the sheet:
xls = pd.ExcelFile['path_to_file.xls']
sheet1 = xls.parse[0]
will give the first worksheet. for the second worksheet:
sheet2 = xls.parse[1]
answered Feb 25, 2015 at 22:58
ElliottElliott
1,29112 silver badges12 bronze badges
1
You could also specify the sheet name as a parameter:
data_file = pd.read_excel['path_to_file.xls', sheet_name="sheet_name"]
will upload only the sheet
"sheet_name".
answered Feb 11, 2017 at 19:37
Mat0kanMat0kan
4214 silver badges4 bronze badges
Option 1
If one doesn't know the sheets names
# Read all sheets in your File
df = pd.read_excel['FILENAME.xlsm', sheet_name=None]
# Prints all the sheets name in an ordered dictionary
print[df.keys[]]
Then, depending on the sheet one wants to read, one can pass each of them to a specific dataframe, such as
sheet1_df = pd.read_excel['FILENAME.xlsm', sheet_name=SHEET1NAME]
sheet2_df = pd.read_excel['FILENAME.xlsm', sheet_name=SHEET2NAME]
Option 2
If the name is not relevant and all one cares about is the position of the sheet. Let's say one wants only the first sheet,
# Read all sheets in your File
df = pd.read_excel['FILENAME.xlsm', sheet_name=None]
sheet1 = list[df.keys[]][0]
Then, depending on the sheet name, one can pass each it to a specific dataframe, such as
sheet1_df = pd.read_excel['FILENAME.xlsm', sheet_name=SHEET1NAME]
answered Nov 24, 2020 at 12:15
Gonçalo PeresGonçalo Peres
7,3133 gold badges40 silver badges67 bronze badges
pd.read_excel['filename.xlsx']
by default read the first sheet of workbook.
pd.read_excel['filename.xlsx', sheet_name = 'sheetname']
read the specific sheet of workbook and
pd.read_excel['filename.xlsx', sheet_name = None]
read all the worksheets from excel to pandas dataframe as a type of OrderedDict means nested dataframes, all the worksheets as dataframes collected inside dataframe and it's type is OrderedDict.
answered Aug 1, 2019 at 17:01
Ashu007Ashu007
7271 gold badge9 silver badges13 bronze badges
If you are interested in reading all sheets and merging them together. The best and fastest way to do it
sheet_to_df_map = pd.read_excel['path_to_file.xls', sheet_name=None]
mdf = pd.concat[sheet_to_df_map, axis=0, ignore_index=True]
This will convert all the sheet into a single data frame m_df
answered Aug 11, 2020 at 3:02
anujsyalanujsyal
1581 silver badge6 bronze badges
You can read all the sheets using the following lines
import pandas as pd
file_instance = pd.ExcelFile['your_file.xlsx']
main_df = pd.concat[[pd.read_excel['your_file.xlsx', sheet_name=name] for name in file_instance.sheet_names] , axis=0]
answered Sep 1, 2021 at 13:09
1
If:
- you want multiple, but not all, worksheets, and
- you want a single df as an output
Then, you can pass a list of worksheet names. Which you could populate manually:
import pandas as pd
path = "C:\\Path\\To\\Your\\Data\\"
file = "data.xlsx"
sheet_lst_wanted = ["01_SomeName","05_SomeName","12_SomeName"] # tab names from Excel
### import and compile data ###
# read all sheets from list into an ordered dictionary
dict_temp = pd.read_excel[path+file, sheet_name= sheet_lst_wanted]
# concatenate the ordered dict items into a dataframe
df = pd.concat[dict_temp, axis=0, ignore_index=True]
OR
A bit of automation is possible if your desired worksheets have a common naming convention that also allows you to differentiate from unwanted sheets:
# substitute following block for the sheet_lst_wanted line in above block
import xlrd
# string common to only worksheets you want
str_like = "SomeName"
### create list of sheet names in Excel file ###
xls = xlrd.open_workbook[path+file, on_demand=True]
sheet_lst = xls.sheet_names[]
### create list of sheets meeting criteria ###
sheet_lst_wanted = []
for s in sheet_lst:
# note: following conditional statement based on my sheets ending with the string defined in sheet_like
if s[-len[str_like]:] == str_like:
sheet_lst_wanted.append[s]
else:
pass
answered Aug 17, 2020 at 21:32
CreekGeekCreekGeek
1,4491 gold badge12 silver badges19 bronze badges
1
df = pd.read_excel['FileName.xlsx', 'SheetName']
This
will read sheet SheetName from file FileName.xlsx
answered Jun 27, 2021 at 10:32
TajinderTajinder
2,1784 gold badges30 silver badges49 bronze badges
Yes unfortunately it will always load the full file. If you're doing this repeatedly probably best to extract the sheets to separate CSVs and then load separately. You can automate that process with d6tstack which also adds additional features like checking if all the columns are equal across all sheets or multiple Excel files.
import d6tstack
c = d6tstack.convert_xls.XLStoCSVMultiSheet['multisheet.xlsx']
c.convert_all[] # ['multisheet-Sheet1.csv','multisheet-Sheet2.csv']
See d6tstack Excel examples
answered Dec 17, 2018 at 3:10
citynormancitynorman
4,4202 gold badges34 silver badges38 bronze badges
If you have saved the excel file in the same folder as your python program [relative paths] then you just need to mention sheet number along with file name.
Example:
data = pd.read_excel["wt_vs_ht.xlsx", "Sheet2"]
print[data]
x = data.Height
y = data.Weight
plt.plot[x,y,'x']
plt.show[]
albert
7,3378 gold badges44 silver badges78 bronze badges
answered Jan 13, 2020 at 7:25