Randal Scott King

Your Data Science Tutor

Blog

Linear Regression in Python

Linear Regression in Python is easy to implement and is useful for predicting continous numbers (such as forecasting revenues or predicting a house price based on qualities like the number of rooms). Almost all machine learning algorithms use data as numbers, but sometimes those numbers can represent categories or other non-numeric values. Continuous numbers are those you would sum, multiply, or otherwise manipulate mathmatically.

Theory

You probably learned the formula for Simple Linear Regression in middle school math. It looks like this:

y = mx + b

…where y is the target we’re trying to solve for, x is a value we know (a “feature”), m is the slope of the line, and b is the value of y when x equals zero (the “intercept”). This formula is useful, but it’s limited to a single feature, x. If we want to solve for y with multiple features, the formula is a little different:

y = w[0]*x[0] + w[1]*x[0] ….. +b

For each feature we want to use, we just add a w[num]*x[num]. “w” represents the weight of that feature, or how important it is compared to the others. You probably figured out that w is also the slope of that particular feature’s line.

Coding basic Linear Regression in Python

Most tutorials use data that comes with Pandas or sckit-learn. That might be useful for learning the theory of the algorithm, but it’s not real life. In this example we’ll be using data from a CSV, which is something you’ll do almost every day as a data scientist.

#First import our libraries,
import numpy as np
import pandas as pd
from sklearn.linear_model import LinearRegression
from sklearn.cross_validation import train_test_split

# Load the CSV with Pandas
df = pd.read_csv(‘data.csv’)

#Define the Target and the Features
X = df.drop(‘columnname’, axis=1) #drop the target column
y = df[[‘columnname’]] #keep only the target column

#Create our cross-validation split
X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=42)

#Define the algorithm and train the model
lr = LinearRegression().fit(X_train,y_train)

#Check the coefficients (“w”)
print(lr.coef_)

#Now score the model
lr.score(X_test, y_test)

Cross-Validation in Scikit-learn

Cross-validation in Scikit-learn is important because it gives us not only the means to train our model, but also to score its effectiveness. Without cross-validation we would test the model on the very same data we used to train it, likely resulting in a condition called overfitting.

The process for cross-validation involves splitting the data into unequal parts, using the larger set to train the model and the smaller set to test and score. Thus, the algorithm isn’t testing results on the very same data it was trained with.

Random Splitting with train_test_split

The simplest way to split our data would be this:

X_train, X_test, y_train, y_test = train_test_split(df, y, test_size=0.3)

Where df is the dataset, y is the Target, and test_size indicates what percentage of the data will be the test set. This randomly divides our data into an 70/30 split of training data vs testing data, which is sufficient for most cases.

The underlying assumption of a random split like this one is that the test data set (and future data, for that matter) is exchangeable with the training data. Unfortunately that isn’t always the case.

One notable use where test_train_split would not be appropriate is time-series. For example, if you are using past sales data to predict future sales, a random split is the last thing you want. Rather, the best practice would be to take at least a year of past sales in chronological order to train the model. You would then test the resulting model on the following six months to gauge the model’s effectiveness. For time-series data, dividing the data based on a point of time is the only correct method.

Enter K-fold

For cases that benefit from random splits, a much more effective (and complicated) way to use cross-validation is the cross_val_score. This function divides the data into a specified number of ‘folds’ and holds out one fold as the test set, then iterates through them five times and tests each result. Visually represented, the process looks like this:

Cross-validation in Scikit-learn

This process is called K-fold, with ‘K’ being the number of folds.

Coding cross_val_score is a lot more involved than train_test_split because we have to involve the algorithm you’ll be using to analyze the data. Assuming we have loaded a dataset called “df” the code would look as follows:

from sklearn import linear_model
from sklearn.model_selection import cross_val_score

#Create variables X and y for our features and target
X = df.features
y = df.target

#Assume we’ll be using a form of regression called Lasso, and apply cross_val_score
lasso = linear_model.Lasso()
scores = cross_val_score(lasso, X, y, cv=5)

“cv” is the number of folds. If you want to see the scores, just print the variable ‘scores.’

Again, this would work in most situations but not time-series.

Data Warehousing with Hadoop

hadoop data warehouse

 

Almost from the moment Hadoop was first introduced, organizations have sought to replace their expensive data warehousing systems with it. Hadoop’s distributed nature and the fact that it uses commodity hardware make it cheap, massively scalable, and highly available. However, data warehousing with Hadoop is often ill-advised and the projects have ended badly. HDFS, the Hadoop filesystem, was not designed for the speed and updateability requirements of data warehouses.

The Problem of Immutability

The main problem behind replacing an existing data warehouse with Hadoop is a seemingly innocent concept called immutability. Data in HDFS cannot be changed, they can only be overwritten or appended. This makes Change Data Capture (CDC) and other data warehousing concepts difficult to implement. When Hadoop is used for archival or analytical purposes this is not usually an issue. However, data warehouses alter existing data tables anywhere from daily to multiple times a second.

Workarounds using existing Hadoop tools were invented to address the issue, but they are cumbersome, slow, and unreliable. Many who have attempted these workarounds have concluded that the efforts required are simply not worth the investment.

Previous Attempts

To make a Hadoop cluster based on HDFS and Hive function as a data warehouse, two main approaches evolved: partitioning based on time and/or the “Four-Step Method.”

Time-based partitioning separates the data and stores each month, week, or other time unit of data as a separate file. If changes are detected in a record for the month of July in the originating table, for example, the entire month is overwritten in HDFS. While somewhat effective at keeping data current, this method is needlessly cumbersome. Why overwrite an entire month or week just to update a single record? This is a waste of resources.

The Four-Step Method takes a different, much more complex approach. New data from the originating system are used to create a temporary table, which is then joined to the existing table in a view, which is then used to overwrite the existing data in HDFS at the end of the current time period.

Confused? You’re not alone.

Overcoming Immutability

MapR realized early in the game that immutability would be an issue and created a filesystem that was true read-write. Despite the proprietary nature of MapR-FS it uses the same API as HDFS, which all but negates compatibility issues.

Cloudera recognized the issues that the immutability of data in HDFS caused for their customers, and set to work to develop an alternative that would store data in such a way that would allow updates and changes in an expedient fashion. The result is Kudu, an efficient, updateable file store that acts much like a relational database in everyday use.

Commands like INSERT, UPDATE, and DELETE that are unavailable in HDFS and Hive (or didn’t work like their counterparts in relational databases), now function as expected and scale to previously unimagined sizes of data. Kudu even ups the ease of use by including support for an UPSERT command which chooses whether a record should be updated or inserted based on the primary key. Which brings us to the next point…

Keys to the Kingdom

Another notable difference between Hadoop and relational databases has been the lack of keys in Hive. Keys are essential to the success of Change Data Capture (CDC), the process by which data in the data warehouse are continually updated. Kudu’s support of and requirement for a primary key in each table mean that there is now a mechanism in Hadoop for preventing duplicate data during updates. This was a glaring omission before.

Caveats

It can’t be all sunshine and roses, can it? Since Kudu is a relatively new technology there are limitations that must be considered when using it for data warehousing, some of which include the following:

  • Kudu requires primary keys but doesn’t support foreign keys, so linking between tables in the way a relational database would is not yet possible.
  • As of version 1.3, Kudu doesn’t support creating tables from files or the LOAD DATA INFILE command. The fastest way to bulk load data is to bring them into HDFS, create a Hive table over them, and then create the Kudu table through a CREATE AS SELECT statement. Another alternative is to interface directly with the API, bypassing ODBC or JDBC drivers. The API works with C++, Java, and now even includes bindings for Python.
  • The number of supported data types for columns in Kudu is relatively small and does not include either DATE or TIMESTAMP.
  • Supporting a Hadoop data warehouse can be more challenging than with traditional solutions. Hadoop is an ecosystem of components all working together.

Now, Why Would I Do This?

Now that immutability isn’t the limiting issue it was, the question remains: why use a Hadoop data warehouse? This usually boils down to one of two things: price and/or scale. Some switchers cite excessive licensing costs for traditional data warehouses and limitations of the hardware used in those solutions. Hadoop also easily scales past petabytes of data and does so inexpensively.

Infer Schema when Importing to PostgreSQL or MySQL

infer schema
Postgres and MySQL are great Open Source databases in use by organizations of all sizes. Both of them are flexible and somewhat scalable. Both of them have nice GUI front-ends available to make them even easier to use.

Sequel Pro for MySQL is a handy GUI frontend that runs on Macs, and one of the things I like most is that it will infer the schema of a CSV on import. This saves a lot of time since it’s not necessary to create the table and define the columns ahead of time. Unfortunately Sequel Pro can also be a bit buggy.

pgAdmin is a similar tool designed for Postgres, and is more stable than Sequel Pro in daily use. But like a great many other database tools, pgAdmin won’t infer schema (and if I’m wrong about that, please comment below). So if you need to import a CSV with a lot of columns and you’d rather not define them all yourself, what can you do?

Back to the Command Line

One of the most useful means to deal with the problem is to use Pandas. Pandas dataframes will infer schema when you read in a CSV, and the “to_sql” function will load dataframes into a few different databases. Here’s an example of Pandas reading a CSV located at /Users/me/Desktop/mydata.csv and then loading it into a Postgres table named yourdata in a database called hisdb.

First we have to import Pandas, and “create_engine” from sqlalchemy:

from sqlalchemy import create_engine
import pandas as pd

Now we use create_engine to create a connection to the Postgres database:

engine = create_engine(‘postgresql://user:password@localhost/hisdb’)

Then we can load our CSV into a Pandas datframe, and use the “to_sql” to load that dataframe into a table called “yourdata” over the engine connection:

df = pd.read_csv(‘/Users/me/Desktop/mydata.csv’)
df.to_sql(‘yourdata’, engine)

Done and done. On occasion it will be necessary to change the data type of a column because Pandas guessed wrong, but that’s a minor inconvenience when compared to manually defining dozens of columns ahead of time.

Using SQL Commands in Spark with SparkSQL

SparkSQL

Spark has become a standard for performing analysis on huge amounts of data due to its distributed nature. SparkSQL evolved as a necessary component of Spark due to the need for working with structured data.

There are many times when there is a need to query data in Spark with SQL commands. Doing so isn’t complicated at all, and in fact you can use the same SQL syntax you would use in a relational database as long as you execute a few Spark-specific commands first. Here’s what it looks like:







First we’ll need to create our Spark Session and application:

In [16]:
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("example").getOrCreate()

Next we can load a csv. In the code below we’ll create a dataframe named “df”, use the Databricks CSV loader, specify that the data has a header and uses a comma for a delimiter, and give the path to the file. I like to use this version of the read command because it allows the user to specify the delimiter being used (not all delimited files use commas).

In [17]:
df = spark.read.format("com.databricks.spark.csv").option("header", "true").option("delimiter",",").load("/Users/randalking/Desktop/Mac/Superstore.csv")
In [18]:
df.show(10)

+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+
|Row ID|Order ID|Order Date|Order Priority|Order Quantity|    Sales|Discount|     Ship Mode| Profit|Unit Price|Shipping Cost|     Customer Name|Province| Region|Customer Segment|Product Category|Product Sub-Category|        Product Name|Product Container|Product Base Margin|Ship Date|
+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+
|     1|       3|  10/13/10|           Low|             6|   261.54|    0.04|   Regular Air|-213.25|     38.94|           35|Muhammed MacIntyre| Nunavut|Nunavut|  Small Business| Office Supplies|Storage & Organiz...|Eldon Base for st...|        Large Box|                0.8| 10/20/10|
|    49|     293|   10/1/12|          High|            49| 10123.02|    0.07|Delivery Truck| 457.81|    208.16|        68.02|      Barry French| Nunavut|Nunavut|        Consumer| Office Supplies|          Appliances|"1.7 Cubic Foot C...|       Jumbo Drum|               0.58|  10/2/12|
|    50|     293|   10/1/12|          High|            27|   244.57|    0.01|   Regular Air|  46.71|      8.69|         2.99|      Barry French| Nunavut|Nunavut|        Consumer| Office Supplies|Binders and Binde...|Cardinal Slant-D®...|        Small Box|               0.39|  10/3/12|
|    80|     483|   7/10/11|          High|            30|4965.7595|    0.08|   Regular Air|1198.97|    195.99|         3.99|     Clay Rozendal| Nunavut|Nunavut|       Corporate|      Technology|Telephones and Co...|                R380|        Small Box|               0.58|  7/12/11|
|    85|     515|   8/28/10| Not Specified|            19|   394.27|    0.08|   Regular Air|  30.94|     21.78|         5.94|    Carlos Soltero| Nunavut|Nunavut|        Consumer| Office Supplies|          Appliances|Holmes HEPA Air P...|       Medium Box|                0.5|  8/30/10|
|    86|     515|   8/28/10| Not Specified|            21|   146.69|    0.05|   Regular Air|   4.43|      6.64|         4.95|    Carlos Soltero| Nunavut|Nunavut|        Consumer|       Furniture|  Office Furnishings|G.E. Longer-Life ...|       Small Pack|               0.37|  8/30/10|
|    97|     613|   6/17/11|          High|            12|    93.54|    0.03|   Regular Air| -54.04|       7.3|         7.72|      Carl Jackson| Nunavut|Nunavut|       Corporate| Office Supplies|Binders and Binde...|Angle-D Binders w...|        Small Box|               0.38|  6/17/11|
|    98|     613|   6/17/11|          High|            22|   905.08|    0.09|   Regular Air|  127.7|     42.76|         6.22|      Carl Jackson| Nunavut|Nunavut|       Corporate| Office Supplies|Storage & Organiz...|SAFCO Mobile Desk...|        Small Box|               null|  6/18/11|
|   103|     643|   3/24/11|          High|            21|  2781.82|    0.07|   Express Air|-695.26|    138.14|           35|    Monica Federle| Nunavut|Nunavut|       Corporate| Office Supplies|Storage & Organiz...|SAFCO Commercial ...|        Large Box|               null|  3/25/11|
|   107|     678|   2/26/10|           Low|            44|   228.41|    0.07|   Regular Air|-226.36|      4.98|         8.33|   Dorothy Badders| Nunavut|Nunavut|     Home Office| Office Supplies|               Paper|           Xerox 198|        Small Box|               0.38|  2/26/10|
+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+
only showing top 10 rows

In order to use SQL commands in SparkSQL we’ll need to register the data as a table:

In [19]:
df.registerTempTable("Superstore")

Finally, we can use SQL commands by invoking the spark.sql function and our command in quotes as an argument:

In [20]:
result = spark.sql("SELECT * FROM Superstore LIMIT 5").show()

+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+
|Row ID|Order ID|Order Date|Order Priority|Order Quantity|    Sales|Discount|     Ship Mode| Profit|Unit Price|Shipping Cost|     Customer Name|Province| Region|Customer Segment|Product Category|Product Sub-Category|        Product Name|Product Container|Product Base Margin|Ship Date|
+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+
|     1|       3|  10/13/10|           Low|             6|   261.54|    0.04|   Regular Air|-213.25|     38.94|           35|Muhammed MacIntyre| Nunavut|Nunavut|  Small Business| Office Supplies|Storage & Organiz...|Eldon Base for st...|        Large Box|                0.8| 10/20/10|
|    49|     293|   10/1/12|          High|            49| 10123.02|    0.07|Delivery Truck| 457.81|    208.16|        68.02|      Barry French| Nunavut|Nunavut|        Consumer| Office Supplies|          Appliances|"1.7 Cubic Foot C...|       Jumbo Drum|               0.58|  10/2/12|
|    50|     293|   10/1/12|          High|            27|   244.57|    0.01|   Regular Air|  46.71|      8.69|         2.99|      Barry French| Nunavut|Nunavut|        Consumer| Office Supplies|Binders and Binde...|Cardinal Slant-D®...|        Small Box|               0.39|  10/3/12|
|    80|     483|   7/10/11|          High|            30|4965.7595|    0.08|   Regular Air|1198.97|    195.99|         3.99|     Clay Rozendal| Nunavut|Nunavut|       Corporate|      Technology|Telephones and Co...|                R380|        Small Box|               0.58|  7/12/11|
|    85|     515|   8/28/10| Not Specified|            19|   394.27|    0.08|   Regular Air|  30.94|     21.78|         5.94|    Carlos Soltero| Nunavut|Nunavut|        Consumer| Office Supplies|          Appliances|Holmes HEPA Air P...|       Medium Box|                0.5|  8/30/10|
+------+--------+----------+--------------+--------------+---------+--------+--------------+-------+----------+-------------+------------------+--------+-------+----------------+----------------+--------------------+--------------------+-----------------+-------------------+---------+