Python Tutorial: Read data from Excel worksheets

Key Takeaways

let us now look at how to import data from Excel worksheets as example you will be using an Excel workbook with three worksheets containing listing information for three exchanges the AMEX Exchange and the Nasdaq that you're already familiar with and also the NYC each sheet contains the same information as the AMEX csv file you have seen before we just omitted the last update color you can use the parameter sheet name to tell read Excel which worksheet to import you have several options to import either a single sheet or multiple sheets simultaneously first you can provide an integer that refers to the position of the worksheet the number 0 means you want to import the first sheet you can also refer to a sheet by its name second you can import several sheets at the same time just provide a list with the names of positions of the sheets you would like read Excel to import the result will be a dictionary where the keys are the sheet names and the values are data frames with the sheet content let's look at an example to read the worksheet for the AMEX exchange simply provide the label to the read Excel parameter sheet name note that read Excel also uses the NA values parameter to parse missing values if you call the dot info method on the result you will notice the same output you obtained earlier from the read CSV method there's no import data from two worksheets just supply a list with the labels MX and a stack to the sheet name parameter the result contained in the variable listings is a dictionary that contains two key value pairs the keys contain the names of the worksheets and the values are the corresponding data frames since listings is a dictionary you can access the data frame within a stock data by providing the matching key once you apply the right info method to the result you can view the structure of the data about the listings on the stock exchange pandas also allows you to retrieve the sheet names from an Excel workbook to obtain this information create an excel file object using the path to an excel workbook as illustrated here for the listings xlsx file once you have created this object you can access its sheet names attribute this attribute contains a list with the names of the worksheets for this workbook here we retrieve the list of all the exchange names and assign it to a variable exchanges in the next step you can pause the excel file object to read Excel to import its content instead of the path to the file we can then select the name of the target worksheet from the list stored in the exchanges variable assign the resulting data frame to the variable NYC and call the method or info on this data frame to show the expected output let's now practice your new skills