Using Pandas to pd read excel for multiple worksheets of the same workbook

Question

I have a large spreadsheet file   xlsx  that I m processing using python pandas  It happens that I need data from two tabs 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    Am I using it wrong or is it just limited in this way   Thank you

User · Accepted Answer

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

User · Answer

There are a few options  Read all sheets directly into an ordered dictionary  import pandas as pd    for pandas version  gt   0 21 0 sheet to df map   pd read excel file name  sheet name None     for pandas version  lt  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  sheetname  quot house quot    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 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

User · Answer

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

User · Answer

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

User · Answer

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    quot C   Path  To  Your  Data   quot  file    quot data xlsx quot  sheet lst wanted     quot 01 SomeName quot   quot 05 SomeName quot   quot 12 SomeName quot     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    quot SomeName quot            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

User · Answer

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

User · Answer

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  quot wt vs ht xlsx quot    quot Sheet2 quot    print data   x   data Height  y   data Weight  plt plot x y  x    plt show

User · Answer

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

User · Answer

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

User · Answer

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

[python] Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

Examples related to python

Examples related to excel

Examples related to pandas

Examples related to dataframe

Examples related to xlsx