In the latest Seahorse release we introduced the scheduling of Spark jobs. We will show you how to use it to regularly collect data and send reports generated from that data via email.

Use case

Let’s say that we have a local meteo station and the data from this station is uploaded automatically to Google Sheets. That is, it contains only one row (i.e. row number 2) of data which always contains current weather characteristics.

We would like to generate some graphs from this data every now and then and do it without any repeated effort.

Solution

Collecting data

The first workflow that we create collects a snapshot of live data and appends it to a historical data set. It will be run at regular intervals.
Let’s start by creating two data sources in Seahorse. The first data source represents the Google Spreadsheet with live-updated weather data:

For the historical weather data, we create a data source representing a file stored locally within Seahorse. Initially, it only contains a header and one row – current data.

Now we can create our first Spark job which reads both data sources, concatenates them and writes back to the “Historical weather data” data source. In this way, there will be a new data row in “Historical weather data” every time the workflow is run.

There is one more step for us to do – this Spark job needs to be scheduled. It is run every half an hour and after every scheduled job execution an email is sent.

In the email there is a link to that workflow, where we are able to see node reports from the execution.

Sending a weather report

The next workflow is very simple – it consists of a Read DataFrame operation and a Python Notebook. It is done separately from collecting the data because we want to send reports at a much lower rate.

In a notebook, first we transform the data a little:

import pandas
import numpy
import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
data = dataframe().toPandas()
data['time'] = pandas.to_datetime(data['time'])
data = data.sort('time')

Next, we prepare a function that will be used to line-plot a time series of weather characteristics like temperature, air pressure and humidity:

def plot(col_to_plot):
   ax = plt.axes()
   ax.spines["top"].set_visible(False)
   ax.spines["right"].set_visible(False)
   ax.get_xaxis().tick_bottom()
   ax.get_yaxis().tick_left()
   y_formatter = matplotlib.ticker.ScalarFormatter(useOffset=False)
   ax.yaxis.set_major_formatter(y_formatter)
   plt.locator_params(nbins=5, axes='x')
   plt.locator_params(nbins=5, axes='y')
   plt.yticks(fontsize=14)
   plt.xticks(fontsize=14)
   plt.plot(data['time'], data[col_to_plot])
   plt.gcf().autofmt_xdate()
   plt.show()

After editing the notebook, we set up its parameters so that reports are sent to our email address after each run.

Finally, we can set up a schedule for our report. It is similar to the previous one, only sent less often– we send a report every day in the evening. After each execution two emails are sent – one, as previously, with a link to the executed workflow and the second one containing a notebook execution result.

Result

After some time, we have enough data to plot it. Here is a sample from the “Historical weather data” data source…

… and graphs that are in the executed notebook:

Summary

We created a data processing and email reporting pipeline using two simple workflows and some Python code. Seahorse works well not only for big data, but also for scenarios when you need to integrate data from different sources and periodically generate reports – Seahorse data sources and Spark job scheduling are the right tool for the job.

A few days ago we released Seahorse 1.1, an enhanced version of our machine learning, Big Data manipulation and data visualization product. Today, we will analyze statistical data about births in the USA over the last century and show you, how easy is data visualization with the new version of Seahorse.

The input dataset consists of 5,647,426 rows representing newborn children’s names in the U.S., aggregated by name, gender, year and state. Due to privacy reasons, names with less than 5 babies born in the same year and state were excluded from the dataset. Each row consists of 6 cells: Id (numeric), Name (string), Year (numeric), Gender (string), State (string), Count (numeric). Please note that we will be using only the demo version of Seahorse (in contrast to full-scale Spark cluster Seahorse deployment, this version works on a single machine with limited resource consumption) and the experiment execution may last more than 15 minutes.
We will create a workflow for data analysis and explore the data using Seahorse. We will show you how to create the experiment step-by-step, however, the final version can be downloaded here. To follow along with this post and fully understand it, download and install Seahorse 1.1 using these instructions.

Step-by-Step Workflow Creation

Reading the Data

The first thing we need to do is load the data into Seahorse. This is done by placing a Read DataFrame operation on the canvas and modifying its parameters:
SOURCE: https://s3.amazonaws.com/workflowexecutor/examples/data/StateNames.csv
Run the workflow by clicking the RUN button in the top panel. Depending on your internet connection speed, it will take up to few minutes to download the data. When the execution completes, we can see what the data looks like. Display DataFrame’s report by clicking on the output port of the Read DataFrame operation.

The read data frame report. It contains a data sample of only 20 out of the 5,647,426 rows.

By clicking on the icons in report header, you can see more information about the whole data set (not just the data sample). Click on the Year column icon to see its values’ distribution:

As we can see in the histogram, we have data for the years 1910 to 2014. Another interesting information is that we have a lot more data from recent years.
Now, let’s choose the Gender column in the report:

The pie chart for the Gender column illustrates that there is more data for females than males. Since this is official government data, we can assume that there are more female newborns than male, or that unusual names are more popular for males thus are not included in the dataset.

Filtering the Data

Let’s take a closer look at the female names (an analysis of male names would be analogous; if you feel confident, you can explore male names while following this tutorial). We can use a Filter Rows operation to select only female names.
The Filter Rows operation is parameterized by an expression which will be used to select the desired rows. To select female names, we can use the expression below:
CONDITION: Gender = ‘F’

Finding the Most Popular Names

The first question we will explore is:  what names were the most popular. We need to sum the Count column for every name, disregarding the year and the state in which the  child was born. To achieve this, we will use a SQL Transformation operation. Let’s leave dataframe id set to `df` and just modify the expression parameter:
EXPRESSION:select Name, sum(Count) as Count from df group by Name order by Count desc

After running the operation we can explore its report and see that the three most popular names are Mary, Patricia and Elizabeth.
We have found the most popular names in the U.S., but we can also narrow our results down to find the most popular names in particular states.

Most Popular Names in States using Custom Transformer

To find the most popular names in a certain state we need to execute a sequence of operations. It is a good practice to enclose such a sequence within a “procedure” which can be reused later.
To do that we need to use a Create Custom Transformer operation. Please drag and drop Create Custom Transformer to the canvas and click the Edit workflow button on the operation parameters panel. This takes us to a new canvas where we can create a custom Transformer. A Transformer is a function which takes a DataFrame as an input and returns another DataFrame as the output. We want to create a Transformer that calculates the most popular names in a given state.
To do that, we need to:
1. Calculate how many times each name was given in every state.

We can achieve this by executing a SQL Transformation operation:
EXPRESSION: select df.Name, df.State, sum(df.Count) as AllTimeCount from df group by df.State, df.Name
2. For every state, calculate number of occurrences for the most popular name in the state.

In order to do so we need to execute a SQL Transformation operation:
EXPRESSION: select State, max(AllTimeCount) as MaxCount from df group by State
3. Now, we will use a Join operation to filter out only the most popular names for each state. We will join name occurrences (from step 1) with counts of the most popular names in every state (step 2).

We need to add a Join operation to the canvas and set two equality constraints as the joining conditions: Left DataFrame’s column MaxCount should be equal to right DataFrame’s column AllTimeCount. Left DataFrame’s column State should be equal to right DataFrame’s column State. This can be done by setting two appropriate groups in the join columns parameter (see image below).

Close the inner workflow by clicking the CLOSE INNER WORKFLOW button at top of the screen.
Add a Transform operation to the canvas and connect it with nodes, as shown below:

After we execute the current workflow and open the report for the Transform operation, we can see which names were the most popular in which state. By clicking on the Name column, we can see its distribution:

It turns out that Mary was the most popular name in Washington D.C. and 48 states with Jennifer topping the list in just two states.

Traditional vs Modern Names

Some parents like traditional names while others prefer modern ones. We will now explore which names were popular in the past, and which names are popular now. To do that we split our data into two DataFrames:

1. Data from before January 1, 1980
2. Data since January 1, 1980

We can do such splits by executing two SQL Transformations.
SQL Transformation (select traditional names):
EXPRESSION: select * from df where Year < 1980
SQL Transformation (select modern names):
EXPRESSION: select * from df where Year >= 1980

Now let’s find out what were the most popular names before and after 1980. We do not need to write another SQL Expression, as we already created a Transformer that does it for us. All we need to do is apply the Transformer to different DataFrames. The SQL Transformation operation, besides returning a DataFrame, also returns a SqlTransformer which can be executed using a Transform operation.
We can also use the previously defined Create Custom Transformer to calculate popular names in each state and just apply it to another DataFrame.

The execution of the current workflow might take a few minutes. By viewing reports from the lowest operations, we can see that the most popular names before 1980 were Mary, Patricia and Linda. Mary was the most popular name in every state and in Washington D.C. The most popular names since 1980 are Jessica, Ashley and Jennifer.
Now, let’s take a closer look at the report for the most popular modern names:
When we click on the Name column in the report for the most popular modern names in states we see:

We can see that Jessica was the most popular name in 31 states, Ashley in Washington D.C. and 16 states, with Emily leading the way in two states and Sarah in one (New Hampshire).

Name Popularity Plot – Data Visualization

As we discovered, the most popular names were:

• all time: Mary, Patricia, Elizabeth;
• before 1980: Mary, Patricia, Linda;
• after 1980: Jessica, Ashley, Jennifer.

Let’s filter only the data regarding these names and then take a closer look.
As a first step, we have to filter the data using a SQL Transformation operation with an appropriate expression:
EXPRESSION:

select Name, Year, sum(Count) as Count
   from df
   where
       Name = 'Mary'
       or Name = 'Patricia'
       or Name = 'Elizabeth'
       or Name = 'Linda'
       or Name = 'Jessica'
       or Name = 'Ashley'
       or Name = 'Jennifer'
   group by Name, Year
   order by Year

Now, let’s connect a Notebook operation to the newly created and filtered DataFrame:

Notebook gives us the ability to interactively explore the data. We will use it to draw a plot of the names’ popularity. To do so, we need to execute the following Python code in the Notebook:

import matplotlib
%matplotlib inline
df = dataframe().toPandas()
# Reshape data (produce a “pivot” table) based on column values
names_over_year = df.pivot("Year", "Name", "Count")
names_over_year.plot()          # Make plot of DataFrame using matplotlib
fig = matplotlib.pyplot.gcf()   # Get a reference to the current figure
fig.set_size_inches(18.5, 10.5) # Set the figure size

The code above prepares the data and draws a plot of the names’ popularity. After executing it, a plot will be displayed:

The graph shows that Mary was a very popular name until around 1960, when its popularity dropped. Meanwhile the popularity of Elizabeth has been the most stable throughout the time-frame.
The popularity of the name Linda is an interesting example as it  became hugely popular during the 1950s and then rapidly dropped back to its previous low levels. Linda may owe its brief popularity to Buddy Clark’s 1946 hit song “Linda”.

Conclusion

We have briefly explored U.S. Government’s statistical dataset. It transpires that names’ popularity may be influenced by many factors. For example, we have found that the most popular name in the last century was Mary, which may owe its popularity to Christian culture, but it has lost in popularity since the 1960s when counterculture revolution took place. Another interesting observation is peaks in names popularity influenced by popular culture (i.e. Linda). The next fact worth mentioning is that the faster a name becomes popular, the faster its popularity drops after reaching the peak (both slopes are similar). On the other hand, there are names with stable popularity, like “Elizabeth”, and they have their part in every generation.

A few days ago we have released Seahorse 1.0, a visual platform for machine learning and Big Data manipulation available for all, for free! Today, we show you how to use Seahorse to solve a simple classification problem.

We will try to distinguish edible mushrooms from poisonous ones basing on their appearance. Mushroom picking requires a lot of experience and knowledge on mushroom species recognition. Wrong classification of a mushroom (mistaking poisonous for edible one) might result in very serious health problems. We will show you that with Seahorse it is possible to aid mushroom pickers in this responsible task of mushroom classification.
We will use a publicly available dataset with mushrooms^[1], where specimens descriptions are classified in two classes: edible and poisonous. Using this dataset we create and verify a prediction model that will be usable for classification of gilled mushrooms in Lepiota and Agaricus Family (the most common representative is Agaricus bisporus, also known as table mushroom).
The dataset consists of 8124 instances (4208 edible and 3916 poisonous). Each instance is described by 22 features (phenotypic traits codes, e.g. cap color, odor, veil type, habitat) and 1 label column (edible or poisonous).
We will create a Seahorse workflow: a graphical machine learning experiment representation.

Workflow Overview

Mushroom classification – Seahorse workflow

This is a complete experiment that generates a mushroom classification model. It can be downloaded from here, but we will also show you how to create that experiment step-by-step. To follow up with this article and fully understand it, you should download and install Seahorse 1.0 using these instructions.

Step-by-Step Workflow Creation

Reading the Data

The data is provided in a form of a 23-column, comma-separated CSV-like file with column names in the first line. To work with the dataset, it has to be loaded into Seahorse. This can be done by a Read DataFrame operation. Let’s place it on the canvas using drag-and-drop from the operations palette. To load the data, we need to provide the correct path to the file.

Read DataFrame operation parameters panel

Just click at the Read DataFrame operation on the canvas. Now, in panel on the right you will see its parameters. The Read DataFrame needs to have its parameters modified:
SOURCE: https://s3.amazonaws.com/workflowexecutor/examples/data/mushrooms.csv
After setting the Read DataFrame’s parameters to the correct values, the operation is ready to be executed – just simply click the RUN button in the top Seahorse toolbar. If you have much more operations on the canvas and you are interested in the results of only one operation, you can use partial execution of the workflow. Simply select that operation before clicking RUN.

RUN button

When the execution ends, a report of the operation will be available. Let’s click on the operation output port to see its result.

Operation output port

At the bottom of the screen you will see a simple DataFrame report. It contains information about data loaded by the Read DataFrame operation.

The DataFrame’s report

The DataFrame is too wide (more than 20 columns) to allow viewing the data sample in the report, so we are able to explore here only the column types and names. If you want to explore the data a bit more, you can use a Notebook operation (it allows interactive data exploration): Just place it on the canvas (use drag-and-drop technique) and connect its input port with the Read DataFrame output port (click on the output port and drag it to an input port of the other operation). Now, you can open the Notebook by selecting it on the canvas and clicking at the “Open notebook” button on the right panel.

”Open notebook” button

In the newly opened window, enter: dataframe().take(10) and click on the “run cell” button (you can use a shortcut for it: Ctrl+Enter). Now you can closely investigate the data sample of the first 10 rows returned by the Read DataFrame operation.

Data Transformation

In the first column we have labels stating to which class a specific sample belongs (possible values: edible or poisonous). We have discovered that all columns have string values. To perform classification, Seahorse needs a numeric label column and a vector of numerics as features column. We need to map string values to numbers and assembly features into a single vector column. This can be done by combining multiple operations – String Indexer with One Hot Encoder and Assemble Vector – as shown in the workflow overview image.
The String Indexer operation translates string values to numeric ordinal values. It needs to have its parameters modified, as follows:

String Indexer operation parameters panel

OPERATE ON: multiple columns
INPUT COLUMNS: Including index range 0-22 (all columns)
OUTPUT: append new columns
COLUMN NAME PREFIX: indexed

One Hot Encoder operation parameters panel

The One Hot Encoder operation translates ordinal values to vector having “1” only at position given by input numeric value. It needs to have its parameters modified, as follows:

OPERATE ON: multiple columns
INPUT COLUMNS: Including index range 24-38 and 40-45

We have to exclude the column at index 39 (indexed_veil-type) because all mushroom specimens had partial veil. One Hot Encoder does not allow operating on columns with only one value (unless user wants to drop the last category using DROP LAST parameter).

Assemble Vector operation parameters panel

Assemble Vector merges columns with numerics and vectors of numerics into a single vector of numerics. It needs to have its parameters modified, as follows:
INPUT COLUMNS: Including index range 24-45 (columns generated by the String Indexer, excluding generated column containing edibility label)
OUTPUT COLUMN: features

Remove Unnecessary Columns

We will use the Execute SQL Expression operation to remove unnecessary columns from the dataset and give more meaningful names to columns that are essential for our experiment. It will make the dataset reports smaller and facilitate exploring data.
Execute SQL Expression needs to have its parameters modified, as follows:

DATAFRAME ID: df
EXPRESSION:
SELECT
    edible AS edibility_string,
    indexed_edible AS edibility_label,
    features
FROM df

Splitting Into Training and Test Set

Split operation parameters panel

To perform a fair evaluation of our model, we need to split our data into two parts: a testing dataset and a training dataset. That task could be accomplished by using a Split operation. To divide the dataset in ratio 1 to 3, we do need to modify its default parameters:
SPLIT RATIO: 0.25 (percentage of rows that should end up in the first output DataFrame – the test set)

Model Training

Logistic Regression operation LABEL COLUMN parameter

To train a model, we need to use the Fit operation, which can be used to fit an Estimator. We want to use logistic regression classification, so we will put the Logistic Regression operation on the canvas and connect it to the Fit operation.
We will leave almost all default values of Logistic Regression parameters unchanged, we need only to change the label column to

edibility_label.
LABEL COLUMN: edibility_label

Verifying Model Effectiveness On Test Data

By using Split operation, we had generated a training dataset and a test dataset from the input data. We have trained our model on the training dataset. Now it is time to use the test dataset to verify the effectiveness of our classification model. To generate predictions using the trained model, we need to use a Transform operation. To assess effectiveness of our model we will count “Falsely Poisonous” (waste of edible mushrooms) and “Falsely Edible“ (very dangerous!) entries in the test dataset. To perform the calculations, we will use the Execute SQL Expression operation.
After executing the Transform operation, we can investigate the resulting report:

Thanks to projecting only the necessary columns, the resulting dataset fits in the column number limit and we are able to view the data sample. We can notice that the test dataset has 2043 entries. Also we can see that the String Indexer operation assigned 0 to “e” label (edible class) and 1 to “p” label (poisonous class). We can also notice that the prediction column has “almost the same” values as “edibility” column, so we can suspect that our model performs well. Let’s measure its performance:
The Execute SQL Expression (Falsely Edible) needs to have its parameters modified, as follows:

DATAFRAME ID: df
EXPRESSION: SELECT * FROM df WHERE prediction=0 AND edibility_label=1
The Execute SQL Expression (Falsely Poisonous) needs to have its parameters modified, as follows:
DATAFRAME ID: df
EXPRESSION: SELECT * FROM df WHERE prediction=1 AND edibility_label=0
Now we can explore reports in these two operations and compare the number of rows in each of them:
Falsely Poisonous: 0
Falsely Edible: 0

Conclusion

It means that we have trained a surprisingly accurate prediction model for classifying mushrooms. We have to remember that some dangers remain such as:

1. Mushroom picker examining the specimen can make a mistake during assessment of traits or entering data to the computer.
2. Data sample might be too small to create a comprehensive model for predicting edibility of all mushrooms that users will want to classify.
3. Two different mushroom species can have identical traits, while one of the species is edible and the second one is poisonous.

Due to those problems, we can use this model only to aid expertise on mushroom edibility. Experts should always have the last word over issues that are potentially dangerous for other people, like classifying poisonous mushrooms. This must be followed not only due to lack of confidence for the newly created prediction model (experts can make mistakes even more often than this model), but most of all – due to legal responsibility of those decisions.

^[1] Lichman, M. (2013). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science.