Pandas - Introduction

This notebook explans how to use the pandas library for analysis of tabular data.

Visit the official website for a nicely written documentation: https://pandas.pydata.org

Basic objects¶

The pandas library has a vast API with many useful functions. However, most of this revolves around two important classes:

• Series

• DataFrame

In this introduction, we will focus on them - what each of them does and how they relate to each other and numpy objects.

Series¶

Series is a one-dimensional data structure, central to pandas.

For a complete API, visit https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html

This looks a bit like a Numpy array, does it not?

Actually, in most cases the Series wraps a Numpy array...

But there is something more. Alongside the values, we see that each item (or “row”) has a certain label. The collection of labels is called index.

This index (see below) can be used, as its name suggests, to index items of the series.

Exercise: Create a series with 5 elements.

DataFrame¶

A DataFrame is pandas’ answer to Excel sheets - it is a collection of named columns (or, in our case, a collection of Series). Quite often, we directly read data frames from an external source, but it is possible to create them from:

• a dict of Series, numpy arrays or other array-like objects

• from an iterable of rows (where rows are Series, lists, dictionaries, ...)

Exercise: Create DataFrame whose x-column is $0, \frac{1}{4}\pi, \frac{1}{2}\pi, .. 2\pi$, y column is cos(x) and index are fractions 0, 1/4, 1/2 ... 2

D(ata) types¶

Pandas builds upon the numpy data types (mentioned earlier) and adds a couple of more.

We will see some of the types practically used in further analysis.

Indices & indexing¶

A slice with a ["list", "of", "columns"] yields a DataFrame with those columns.

For example:

[["column_name"]] returs a DataFrame as well, not Series:

There are two ways how to properly index rows & cells in the DataFrame:

• loc for label-based indexing

• iloc for order-based indexing (it does not use the index at all)

Note the square brackets. The mentioned attributes actually are not methods but special “indexer” objects. They accept one or two arguments specifying the position along one or both axes.

loc¶

iloc¶

Modifying DataFrames¶

Adding a new column is like adding a key/value pair to a dict. Note that this operation, unlike most others, does modify the DataFrame.

Non-destructive version that returns a new DataFrame, uses the assign method:

Deleting a column is very easy too.

The drop method works with both rows and columns (creating a new data frame), returning a new object.

Exercise: Use a combination of reset_index, drop and set_index to transform table_df into pd.DataFrame({'index': table_df.index}, index=table_df["alpha"])

Let’s get some real data!

I/O in pandas¶

Pandas can read (and write to) a huge variety of file formats. More details can be found in the official documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/io.html

Most of the functions for reading data are named pandas.read_XXX, where XXX is the format used. We will look at three commonly used ones.

Read CSV¶

Nowadays, a lot of data comes in the textual Comma-separated values format (CSV). Although not properly standardized, it is the de-facto standard for files that are not huge and are meant to be read by human eyes too.

Let’s read the population of U.S. states that we will need later:

The automatic data type parsing converts columns to appropriate types:

Sometimes the CSV input does not work out of the box. Although pandas automatically understands and reads zipped files, it usually does not automatically infer the file format and its variations - for details, see the read_csv documentation here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html

...in this case, the CSV file does not use commas to separate values. Therefore, we need to specify an extra argument:

See the difference?

Read Excel¶

Let’s read the list of U.S. incidents when lasers interfered with airplanes.

Note: This reads just the first sheet from the file. If you want to extract more sheets, you will need to use the pandas.'ExcelFile class. See the relevant part of the documentation.

Read HTML (Optional)¶

Pandas is able to scrape data from tables embedded in web pages using the read_html function. This might or might not bring you good results and probably you will have to tweak your data frame manually. But it is a good starting point - much better than being forced to parse the HTML ourselves!

Write CSV¶

Pandas is able to write to many various formats but the usage is similar.

Data analysis (very basics)¶

Let’s extend the data of laser incidents to a broader time range and read the data from a summary CSV file:

Let’s see what we have here...

For an unknown, potentially unevenly distributed dataset, looking at the beginning / end is typically not the best idea. We’d rather sample randomly:

The topic of data cleaning and pre-processing is very broad. We will limit ourselves to dropping unused columns and converting one to a proper type.

Categorical dtype (Optional)¶

To analyze Laser Color, we can look at its typical values.

Not too many different values.

This column is a very good candidate to turn into a pandas-special, Categorical data type. (See https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html)

Exercise: Are there any other columns in the dataset that you would suggest for conversion to categorical?

Integer vs. float¶

Pandas is generally quite good at guessing (inferring) number types. You may wonder why Altitude is float and not int though. This is a consequence of not having an integer nan in numpy. There’s been many discussions about this.

Quite recently, Pandas introduced nullable types for working with missing data, for example nullable integer.

Filtering¶

Indexing in pandas Series / DataFrames ([]) support also boolean (masked) arrays. These arrays can be obtained by applying boolean operations on them.

You can also use standard comparison operators like <, <=, ==, >=, >, !=.

It is possible to perform logical operations with boolean series too. You need to use |, &, ^ operators though, not and, or, not keywords.

As an example, find all California incidents:

Now we can directly apply the boolean mask. (Note: This is no magic. You can construct the mask yourself)

Or maybe we should include the whole West coast?

Or low-altitude incidents?

Visualization intermezzo¶

Without much further ado, let’s create our first plot.

Sorting¶

Exercise: Find the last 3 incidents with blue laser.

Arithmetics and string manipulation¶

Standard arithmetic operators work on numerical columns too. And so do mathematical functions. Note all such operations are performed in a vector-like fashion.

You may mix columns and scalars, the string arithmetics also works as expected.

Summary statistics¶

The describe method shows summary statistics for all the columns:

Basic string operations (Optional)¶

These are typically accessed using the .str “accessor” of the Series like this:

• series.str.lower

• series.str.split

• series.str.startswith

• series.str.contains

• ...

See more in the documentation.

Merging data¶

It is a common situation where we have two or more datasets with different columns that we need to bring together. This operation is called merging and the Pandas apparatus is to a great detail described in the documentation.

In our case, we would like to attach the state populations to the dataset.

We will of course use the state name as the merge key. Before actually doing the merge, we can explore a bit whether all state names from the laser incidents dataset are present in our population table.

We could certainly clean the data by correcting some of the typos. Since the number of the rows with unknown states is not large (compared to the length of the whole dataset), we will deliberetly not fix the state names. Instead, we will remove those rows from the merged dataset by using the inner type of merge. All the merge types: left, inner, outer and right are well explained by the schema below:

We can use the merge function to add the "Population" values.

Grouping & aggregation¶

A common pattern in data analysis is grouping (or binning) data based on some property and getting some aggredate statistics.

Example: Group this workshop participants by nationality a get the cardinality (the size) of each group.

Possibly the simplest group and aggregation is the value_counts method, which groups by the respective column value and yields the number (or normalized frequency) of each unique value in the data.

This is just a primitive grouping and aggregation operation, we will look into more advanced patterns. Let us say we would like to get some numbers (statistics) for individual states. We can groupby the dataset by the "State" column:

What did we get?

What is this DataFrameGroupBy object? Its use case is:

• Splitting the data into groups based on some criteria.

• Applying a function to each group independently.

• Combining the results into a data structure.

Let’s try a simple aggregate: the mean of altitude for each state:

What if we were to group by year? We don’t have a year column but we can just extract the year from the date and use it for groupby.

You may have noticed how we extracted the year using the .dt accessor. We will use .dt even more below.

Let’s calculate the mean altitude of laser incidents per year. Are the lasers getting more powerful? 🤔

We can also quickly plot the results, more on plotting in the next lessons.

Exercise: Calculate the sum of injuries per year. Use the fact that True + True = 2 ;)

We can also create a new Series if the corresponding column does not exist in the dataframe and group it by another Series (which in this case is a column from the dataframe). Important is that the grouped and the by series have the same index.

Time series operations (Optional)¶

We will briefly look at some more specific operation for time series data (data with a natural time axis). Typical operations for time series are resampling or rolling window transformations such as filtering. Note that Pandas is not a general digital signal processing library - there are other (more capable) tools for this purpose.

First, we set the index to "timestamp" to make our dataframe inherently time indexed. This will make doing further time operations easier.

First, turn the data into a time series of incidents per hour. This can be done by resampling to 1 hour and using count (basically on any column or on any column that has any non-NA value) to count the number of incidents.

Looking at those data gives us a bit too detailed information.

A daily mean, the result of resampling to 1 day periods and calculating the mean, is already something more digestible. Though still a bit noisy.

We can look at filtered data by rolling mean with, e.g., 28 days window size.