Can read Python Excel files

Read in Excel data with Python

Update: There is now another article on this subject, the module openpyxl treated.

The previous Python tutorials were about importing data from a CSV file or via the URL (if the data set was available online). Now the data from a Excel-File to be read. This time it's about the unemployment figures in the city of Kiel. This data is made available via the open data portal of the city of Kiel.

How do you proceed now? On the one hand, the library is in Python xlrd available that over

or

Can be installed.

On the other hand, it offers pandas also the possibility to read Excel files. This only requires one line of code:

Without additional information, the first worksheet is always read. If you want to change this, you can use the following code:

In this tutorial I would like to introduce both libraries, so I will first show how they can be used. The code for reading the Excel file then follows. Since I usually read the data anyway in order to then display it graphically, the use of here is basically superfluous. Nonetheless, I am introducing here for the sake of completeness.

Incidentally, I generated the Excel file from the CSV file provided by the City of Kiel.

Read data with xlrd

Let's start by importing the required libraries:

The following is the reading in of the Excel file (with):

The constant is first assigned the file path.

The function then does the real work. About the line

the Excel file to be used is specified.

Excel files consist of several worksheets. Since the data is in the first worksheet (index 0), this can be assigned to the variable as follows:

In this worksheet we need the data from the first and second columns (year and unemployed) as a numpy array:

The function is finally called with

Now this data should also be included matplotlib can be visualized using a pandas data frame. Since I have already explained this code in the previous Python tutorials, I will spare myself further explanations here.

Read data with pandas

As already said, the way via is superfluous in this example, since it is used here anyway. Hence the code without the detour via:

You can find this code - together with the code for other Python / Jupyter projects - in my Github repository Kiel_Open_Data.

Related Links

Categories ProgrammingTags Excel, Matplotlib, Pandas, Python