Table of Contents

All content here is under a Creative Commons Attribution CC-BY 4.0 and all source code is released under a BSD-2 clause license.

Please reuse, remix, revise, and reshare this content in any way, keeping this notice.

Course overview¶

This is the fourth module of several (11, 12, 13, 14, 15 and 16), which refocuses the course material in the prior 10 modules in a slightly different way. It places more emphasis on

• dealing with data: importing, merging, filtering;
• calculations from the data;
• visualization of it.

In short: *how to extract value from your data*.

Module 14 Overview¶

In this module we will cover

• Setting date and time stamps
• More plots with Pandas
• Filtering and grouping data

Requirements before starting

• Have your Python installation working as you had for modules 11, 12 and 13, including the Pandas library installed.

Date and time processing¶

In the prior module you learned about box plots, histogram plot, time-series (or sequence) plots, and scatter plots. We will revise some of those, and build on that knowledge a bit further.

Start with the data from an actual plant, where we have 5 columns of measurements from a flotation cell. Read the link if you need a quick overview of what flotation is.

Some things to do with a new data set called df:

• df.head() and df.tail() to check you have the right data
• df.describe() to get some basic statistics
• df.info() to see the data types

In the space below, apply these to the data you just read in:

Next plot sequence plots of all data columns, using this command

ax = flot.plot()

Notice that the x-axis is not time-based, even though there is a column in data frame called "Date and time". So what went wrong?

When reading in a new data frame you might need to first:

• force a column to be of the type of date and time, so Pandas can use it in the plots
• set that column to be the index of your data frame.

and then you can proceed with your plotting and data analysis.

To set a column to the right type, you can use the pd.to_datetime(...) function. Many times Pandas will get it right, but if it doesn't you can give it some help.

So try this first below. If it works, you are lucky, and can continue.

flot["Timestamp"] = pd.to_datetime(flot["Date and time"])

Note that we created a new column. Check it with flot.info() again, to see if it is of the right type. You can of course simply overwrite your previous column.

If the conversion did not work, you could have given it some guidance.

For example:

pd.to_datetime("20/12/21", yearfirst=True)  # it is supposed to be 21 December 2020
pd.to_datetime("20/12/21", dayfirst=True)   # it is supposed to be 20 December 2021
pd.to_datetime("20/12/21", format="%d/%m", exact=False)

For the format specifier, you can see all the options available from this page: https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior

Once you have the column correctly as a date and time stamp, you probably want this to be your data frame index.

flot=flot.set_index("Timestamp")

# and drop the original "Date and time" column, since we don't need it anymore
flot.drop(columns="Date and time", inplace=True)

flot.plot()

Now you will see a short break in the data around 09:00 on 16 December 2004 which was not visible before.

Date and time processing: Check your understanding¶

From the provided Excel file, read in the data. Convert the date and time column to the desired format:

• assuming the date in the first row is in American format: June 01, 2018
• assuming the date in the first row is in the usual format: 06 January 2018

Recap: boxplot¶

A box plot can be shown per column in one simple line for a data frame df:

df.plot.box()

Does it make sense to plot box plots for all columns, especially when units and orders of magnitude are so different?

So now rather plot only the box plot for "Upstream pH":

Notice that there are so many outliers beyond the whiskers. What is going on? Look at the time-based plot of that column:

df["name of column"].plot.line()

Recap: histogram¶

Similar to df.plot.line() and df.plot.box() to get a line and box plot, you can also use df.plot.hist() to get a histogram.

But this tries to put all histograms in one plot, which is not so useful. But we will see a better way below.

Recap: line plot, or sequence plot (and learning about for-loops!)¶

If you use this code, you will get all the line plots in the same plot:

flot["Timestamp"] = pd.to_datetime(flot["Date and time"])
flot=flot.set_index("Timestamp")
flot.plot()

But if you want each plot in its own axis instead, you need to use a loop to create multiple plots:

print(flot.columns)
for column in flot.columns:
    print(column)
    display(flot[column].plot())

Pandas can only plot columns of numeric data. If the column is non-numeric, it will create an error. So to ensure the loop only goes through numeric columns, you can filter on that. Change the first lines to

flot["artificial column"] = "abc"
flot.head()
for column in flot.select_dtypes("number"):
    # add the loop content here, indented appropriately

Recap: correlation, and introducing the scatter plot matrix¶

We saw the correlation matrix can be calculated with this handy one-liner:

df.corr()

Do this below for the flotation data. Any interesting leads to investigate?

The scatter plot matrix is a visual tool to help create a scatter plot of each combination. The plot on the diagonal would not be an interesting scatter plot, so this is often replaced with a histogram or a kernel density estimate (kde) plot.

Use the code below to try creating both types of plots on the diagonal:

from pandas.plotting import scatter_matrix

scatter_matrix(df, alpha = 0.2, figsize=(10, 8), diagonal = 'kde');
scatter_matrix(df, alpha = 0.2, figsize=(10, 8), diagonal = 'hist');

Learn about filtering and grouping (Blender Efficiency dataset)¶

Filtering and grouping data is part of the daily work of anyone working with data. The reason is because once you have filtered the data or grouped it, then you want to calculate some statistics or create a visualization on the result. So your workflow becomes:

1. Import all your data;
2. Filter or group to get a subset of the data;
3. Do calculations and create visualizations on the subset of the data.

Some typical examples of filtering and grouping:

• Filter out and keep only the data after 1 January 2018. Throw the rest away.
• Extract only the rows in the data frame where vessel V145 was used, and ignore the rest.
• Group the data by type of product (we have product name A, B, C and D). Do the same set of calculations/visualizations for each product.

The "Blender Efficiency" data set is related to a set of designed experiments. There are 4 factors being changed to affect the blending efficiency:

• particle size
• mixer diameter
• mixer rotational speed, and
• blending time.

Last time we mentioned 6 steps in a data workflow:

1. Define the objective: understanding which factors can be changed to improve blending efficiency.
2. Get your data: in this example it is given to you at the web site address above.
3. Explore your data: use tables and plots.
4. Clean your data, if needed. In this case the data are pre-cleaned.
5. Calculations and models: later we will see how to build a regression model from these data. For now we will calculate correlations and show the results for groups.
6. Communicate your result: show the correlations.

Step 2 and 3: get your data and explore it

import pandas as pd
blender = pd.read_csv('http://openmv.net/file/blender-efficiency.csv')

Tips to explore your data.

• Sort the table by the outcome value (the BlendingEfficiency column). Values from low to high. Visually, in the table, which columns appear to be related to it?

• Is a box plot useful?

Now move on to calculations and other visualizations inspired by those calculations:

• Create and display the numeric correlation matrix. Why columns are most correlated with the outcome variable?
• Instead of plotting a scatter plot for each and every interesting correlation, rather plot a scatter plot matrix. What interesting features do you observe?

Some more models/calculations: the particle size (discrete values at 2, 5 and 8) seem to have an interesting relationship to the outcome variable.

Let's look at this a bit more. Start with the scatter plot of just these 2 variables:

blender.plot.scatter(x="ParticleSize", y="BlendingEfficiency")

Next, we will create a subset of the data set showing just the results when the particle size is "2":

blender["ParticleSize"] == 2

will create an 'indicator' variable with True values where the condition is met. We only want the rows where the condition is true.

In module 12, in the sub-section on "Accessing entries", you saw how you can do this.

blender[blender["ParticleSize"] == 2]

returns just the 4 rows where this condition is true.

• Try it below.
• Also, add code to return only the rows when ParticleSize $\leq 5$.
• Change the filter to return rows when ParticleSize $> 5$. How many rows are that?

Now you can do interesting things on this subset. The subset is just a regular data frame, so you can plot them or do further calculations with them.

blender[blender["ParticleSize"] == 2].mean()

will calculate the average of only these rows.

Next, calculate the average of only the "BlendingEfficiency" column when particle size is 2, 5 and 8. In other words, calculate 3 averages.

You probably end up with something like this:

print(blender[blender["ParticleSize"] == 2]["BlendingEfficiency"].mean())
print(blender[blender["ParticleSize"] == 5]["BlendingEfficiency"].mean())
print(blender[blender["ParticleSize"] == 8]["BlendingEfficiency"].mean())

Can it be done more cleanly? Perhaps you could do it in a loop?

The df.groupby() function in Panadas is a way to do that in a single line.

blender.groupby(by="ParticleSize").mean()  # simplify it: leave out the "by="

Now go wild. Try it with different types of functions:

• blender.groupby("ParticleSize").std()
• blender.groupby("ParticleSize").max()
• blender.groupby("ParticleSize").plot() # what do you think this does? Guess before testing it!
• blender.groupby("ParticleSize").plot.scatter(x="BlendingTime", y="BlendingEfficiency")

You will find, that if you use the plotly backend for plotting, you will notice you don't get plots displayed if you use the code above, while with the matplotlib backend it will show plots.

So, to use Plotly, you will need to call use groupby in a loop instead:

import time
for psize, subset in blender.groupby(by="ParticleSize"):
    print(psize)
    display(subset)
    subset.plot()

    # Then add code here to do something with the "subset" plot.
    # For example, such as changing the axis titles, or figure size

    time.sleep(0.2) # pause for 200 milliseconds

Keeping Conda up to date and installing new packages¶

Newer versions of packages are released frequently. You can update your packages (libraries), with this command::

conda update -n base conda
    conda update --all

Installing a new package in your virtual environment¶

You will come across people recommending different packages in Python for all sorts of interesting applications. For example, the library seaborn is often recommended for visualization. But you might not have it installed yet.

This is how you can install the package called seaborn in your virtual environment called myenv:

conda activate myenv    <--- change the last word in the command to the name of your actual environment
    conda install seaborn

Or in one line:

conda install -n myenv seaborn

Updating an existing package¶

Similar to the above, you can update a package to the latest version. Just change install to update instead. Or in one line:

conda update -n myenv seaborn

Homework: Try the above steps again on a different data set¶

There is another data set, about the taste of Cheddar cheese: https://openmv.net/info/cheddar-cheese

Read the data set in:

cheese = pd.read_csv("https://openmv.net/file/cheddar-cheese.csv")

1. Drop the column called "Case"
2. Calculate the correlation matrix of values and display that
3. Plot a scatter plot matrix of these values:

• with the "kde" on the diagonal
• squares for the markers
• alpha value of 0.8 for the points.

Hint: look at the documentation for scatter_matrix to see how to do this. You can look at the documentation inside Jupyter in several ways:

• help(scatter_matrix)
• scatter_matrix? and then hit Ctrl-Enter.

Homework: Testing your knowledge on another data set¶

The pulp digester is an industrial unit operating in the pulp and paper industry. You can find the data on this page: https://openmv.net/info/kamyr-digester

Some things to try when exploring the data:

• Using a set of histograms, one per variable, find 2 variables which have a bimodal distribution. Which 2 have a normal distribution?
• For the 2 variables with a bimodal distribution: make a time-series (sequence) plot, to visualize what they look like when their data are plotted in sequence order. Do you now see why they have a bimodal histogram?
• Plot time sequence plots of some of the other variables, including the output variable called 'Y-Kappa'.
• Which interesting correlations are there with this variable? Write Python code to find the 3 strongest positively correlated columns, and 3 strongest negatively correlated columns.
• Create a new data frame with only these 6 columns of strongly correlating variables and add the Y-Kappa as the 7th column.
• Create a scatter plot matrix for only this group of data. Use a kde for the diagonal plots.
• If you needed to increase the Kappa number for this process, which variables would you change and in which direction?