Numpy, Matplotlib & Scipy Tutorial

DataFrame

The underlying idea of a DataFrame is based on spreadsheets. We can see the data structure of a DataFrame as tabular and spreadsheet-like. A DataFrame logically corresponds to a "sheet" of an Excel document. A DataFrame has both a row and a column index.

Like a spreadsheet or Excel sheet, a DataFrame object contains an ordered collection of columns. Each column consists of a unique data typye, but different columns can have different types, e.g. the first column may consist of integers, while the second one consists of boolean values and so on.

There is a close connection between the DataFrames and the Series of Pandas. A DataFrame can be seen as a concatenation of Series, each Series having the same index, i.e. the index of the DataFrame.

We will demonstrate this in the following example.

We define the following three Series:

What happens, if we concatenate these "shop" Series? Pandas provides a concat function for this purpose:

This result is not what we have intended or expected. The reason is that concat used 0 as the default for the axis parameter. Let's do it with "axis=1":

Let's do some fine sanding by giving names to the columns:

This was nice, but what kind of data type is our result?

This means, we can arrange or concat Series into DataFrames!

DataFrames from Dictionaries

A DataFrame has a row and column index; it's like a dict of Series with a common index.

Retrieving the Column Names

It's possible to get the names of the columns as a list:

Custom Index

We can see that an index (0,1,2, ...) has been automatically assigned to the DataFrame. We can also assign a custom index to the DataFrame object:

Rearranging the Order of Columns

We can also define and rearrange the order of the columns at the time of creation of the DataFrame. This makes also sure that we will have a defined ordering of our columns, if we create the DataFrame from a dictionary. Dictionaries are not ordered, as you have seen in our chapter on Dictionaries in our Python tutorial, so we cannot know in advance what the ordering of our columns will be:

We change both the column order and the ordering of the index with the function reindex with the following code:

Now, we want to rename our columns. For this purpose, we will use the DataFrame method 'rename'. This method supports two calling conventions

• (index=index_mapper, columns=columns_mapper, ...)
• (mapper, axis={'index', 'columns'}, ...)

We will rename the columns of our DataFrame into Romanian names in the following example. We set the parameter inplace to True so that our DataFrame will be changed instead of returning a new DataFrame, if inplace is set to False, which is the default!

Existing Column as the Index of a DataFrame

We want to create a more useful index in the following example. We will use the country name as the index, i.e. the list value associated to the key "country" of our cities dictionary:

Alternatively, we can change an existing DataFrame. We can us the method set_index to turn a column into an index. "set_index" does not work in-place, it returns a new data frame with the chosen column as the index:

We saw in the previous example that the set_index method returns a new DataFrame object and doesn't change the original DataFrame. If we set the optional parameter "inplace" to True, the DataFrame will be changed in place, i.e. no new object will be created:

Accessing Rows via Index Values

So far we have accessed DataFrames via the columns. It is often necessary to select certain rows via the index names. We will demonstrate now, how we can access rows from DataFrames via the locators 'loc' and 'iloc'. We will not cover 'ix' because it is deprecated and will be removed in the future.

We select all the German cities in the following example by using 'loc'. The result is a DataFrame:

It is also possible to simultaneously extracting rows by chosen more than on index labels. To do this we use a list of indices:

We will also need to select pandas DataFrame rows based on conditions, which are applied to column values. We can use the operators '>', '=', '=', '<=', '!=' for this purpose. We select all cities with a population of more than two million in the following example:

We can use this Boolean DataFrame condition with locto finally create the selection:

It is also possible to logically combine more than one condition with & and |:

We use a logical or | in the following example to see all cities of the Pandas DataFrame, where either the city name contains the letter 'm' or the population number is greater than three million:

Adding Rows to a DataFrame

Accessing Rows by Position

The iloc method of a Pandas DataFrame object can be used to select rows and columns by number, i.e. in the order that they appear in the data frame. iloc allows selections of the rows, as if they were numbered by integers 0, 1, 2, ....

We demonstrate this in the following example:

To get a DataFrame with selected rows by numbers, we use a list of integers. We can see that we can change the order of the rows and we are also able to select rows multiple times:

Sum and Cumulative Sum

The DataFrame object of Pandas provides a method to sum both columns and rows. Before we will explain the usage of the sum method, we will create a new DataFrame object on which we will apply our examples. We will start by creating an empty DataFrame without columns but an index. We populate this DataFrame by adding columns with random values:

Let's apply sum to the DataFrame shops:

We can see that it summed up all the columns of our DataFrame. What about calculating the sum of the rows? We can do this by using the axis parameter of sum.

You only want to the the sums for the first, third and the last column and for all the years:

Of course, you could have also have achieved it in the following way, if the column names are known:

We can use "cumsum" to calculate the cumulative sum over the years:

Using the keyword parameter axis with the value 1, we can build the cumulative sum over the rows:

Assigning New Values to Columns

x is a Pandas Series. We can reassign the previously calculated cumulative sums to the population column:

Instead of replacing the values of the population column with the cumulative sum, we want to add the cumulative population sum as a new culumn with the name "cum_population".

We can see that the column "cum_population" is set to Nan, as we haven't provided any data for it.

We will assign now the cumulative sums to this column:

We can also include a column name which is not contained in the dictionary, when we create the DataFrame from the dictionary. In this case, all the values of this column will be set to NaN:

Accessing the Columns of a DataFrame

There are two ways to access a column of a DataFrame. The result is in both cases a Series:

From the previous example, we can see that we have not copied the population column. "p" is a view on the data of city_frame.

Assigning New Values to a Column

The column area is still not defined. We can set all elements of the column to the same value:

In this case, it will be definitely better to assign the exact area to the cities. The list with the area values needs to have the same length as the number of rows in our DataFrame.

Sorting DataFrames

Let's sort our DataFrame according to the city area:

Let's assume, we have only the areas of London, Hamburg and Milan. The areas are in a series with the correct indices. We can assign this series as well:

Inserting new columns into existing DataFrames

In the previous example we have added the column area at creation time. Quite often it will be necessary to add or insert columns into existing DataFrames. For this purpose the DataFrame class provides a method "insert", which allows us to insert a column into a DataFrame at a specified location:

insert(self, loc, column, value, allow_duplicates=False)`

The parameters are specified as:

Creating a DataFrame by appending Rows

DataFrame from Nested Dictionaries

A nested dictionary of dicts can be passed to a DataFrame as well. The indices of the outer dictionary are taken as the the columns and the inner keys. i.e. the keys of the nested dictionaries, are used as the row indices:

You like to have the years in the columns and the countries in the rows? No problem, you can transpose the data:

Filling a DataFrame with random values: