Python Tutorial: Read data from Excel worksheets

Veröffentlicht am: 18 April 2020
auf dem Kanal: DataCamp

15,336

Want to learn more? Take the full course at https://learn.datacamp.com/courses/im... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
Let us now look at how to import data from Excel worksheets.

As example, you will be using an Excel workbook with 3 worksheets containing listing information for 3 exchanges: the AmEx Exchange and NASDAQ that you are already familiar with, and also the NYSE. Each sheet contains the same information as the AMEX csv file you have seen before, we just omitted the ‘Last Update’ column.

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:

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.
You can import several sheets at the same time. Just provide a list with the names or 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 DataFrames 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 .info() method on the result, you will notice the same output you obtained earlier from the read_csv() method.

Let's now import data from two worksheets. Just supply a list with the labels ‘amex’ and ‘nasdaq’ 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 DataFrames.

Since listings is a dictionary, you can access the DataFrame with the NASDAQ data by providing the matching key. Once you apply the .info() method to the result, you can view the structure of the data about the listings on this stock exchange.

Pandas also allows you to retrieve the sheet names from an Excel workbook. To obtain this information, create an ExcelFile object using the path to an Excel workbook, as illustrated here for the ‘listings.xlsx’ file.

One to have created this object, you can access its sheetnames 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 the variable exchanges.

In the next step, you can pass the ExcelFile object to read_excel() to import its content, instead of the path to the file. You can then select the name of the target worksheet from the list stored the exchanges variable. Assigning the resulting DataFrame to the variable NYSE, and calling the method .info() on this DataFrame shows the expected output.

Let’s practice your new skills.

#DataCamp #PythonTutorial #Importing #Managing #Financial #Data #Python

Auf dieser Seite können Sie das Online-Video Python Tutorial: Read data from Excel worksheets mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer DataCamp 18 April 2020 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 15,336 Mal angesehen und es wurde von 71 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!

228