Programming/Kdb/Labs/Exploratory data analysis - Revision history

Admin at 22:13, 21 December 2021

2021-12-21T22:13:59Z

← Older revision		Revision as of 22:13, 21 December 2021
Line 1:		Line 1:
	=Getting hold of data=		=Getting hold of data=

	In this lab we'll make sense of the following data set from the ~~<span class="plainlinks">~~[https://archive.ics.uci.edu/ml/index.php UCI Machine Learning Repository]~~</span>~~:		In this lab we'll make sense of the following data set from the [https://archive.ics.uci.edu/ml/index.php UCI Machine Learning Repository]:

	* '''Name:''' Real estate valuation data set		* '''Name:''' Real estate valuation data set
Line 17:		Line 17:
	** Yeh, I.C., and Hsu, T.K. (2018). Building real estate valuation models with comparative approach through case-based reasoning. Applied Soft Computing, 65, 260-271.		** Yeh, I.C., and Hsu, T.K. (2018). Building real estate valuation models with comparative approach through case-based reasoning. Applied Soft Computing, 65, 260-271.

	* '''URL:''' ~~<span class="plainlinks">~~https://archive.ics.uci.edu/ml/datasets/Real+estate+valuation+data+set~~</span>~~		* '''URL:''' https://archive.ics.uci.edu/ml/datasets/Real+estate+valuation+data+set

	There are many data sets on UCI that are worth exploring. We picked this one because it is relatively straightforward and clean.		There are many data sets on UCI that are worth exploring. We picked this one because it is relatively straightforward and clean.
Line 48:		Line 48:
	=Downloading the data set and converting it to CSV=		=Downloading the data set and converting it to CSV=

	The data set can be downloaded from the data folder ~~<span class="plainlinks">~~https://archive.ics.uci.edu/ml/machine-learning-databases/00477/~~</span>~~. The data is supplied in the form of an excel file, <tt>Real estate valuation data set.xlsx</tt>. In order to export this data to kdb+/q, we convert it to the '''comma-separated values (CSV)''' format:		The data set can be downloaded from the data folder https://archive.ics.uci.edu/ml/machine-learning-databases/00477/. The data is supplied in the form of an excel file, <tt>Real estate valuation data set.xlsx</tt>. In order to export this data to kdb+/q, we convert it to the '''comma-separated values (CSV)''' format:
	* start Excel;		* start Excel;
	* File > Open the file <tt>Real estate valuation data set.xlsx</tt>;		* File > Open the file <tt>Real estate valuation data set.xlsx</tt>;
Line 201:		Line 201:
	</pre>		</pre>

	More advanced rules can be found in the library quantQ: ~~<span class="plainlinks">~~https://github.com/hanssmail/quantQ~~</span>~~		More advanced rules can be found in the library quantQ: https://github.com/hanssmail/quantQ

	Equipped with this code, we can plot the histograms:		Equipped with this code, we can plot the histograms:
Line 506:		Line 506:
	</blockquote>		</blockquote>

	~~<span class="plainlinks">~~[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]~~</span>~~ tells us that '''gradient boosting'''		[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia] tells us that '''gradient boosting'''
	<blockquote>		<blockquote>
	is a machine learning technique for regression, classification and other tasks, which produces a prediction model in the form of an ensemble of weak prediction models, typically decision trees. When a decision tree is the weak learning, the resulting algorithm is called gradient boosted trees, which usually outperforms random forest. It builds the model in a stage-wise fashion like other boosting methods do, and it generalizes them by allowing optimization of an arbitrary differentiable loss function.		is a machine learning technique for regression, classification and other tasks, which produces a prediction model in the form of an ensemble of weak prediction models, typically decision trees. When a decision tree is the weak learning, the resulting algorithm is called gradient boosted trees, which usually outperforms random forest. It builds the model in a stage-wise fashion like other boosting methods do, and it generalizes them by allowing optimization of an arbitrary differentiable loss function.

Admin: /* Combining q and Python: improving on OLS */

2021-07-01T15:49:53Z

Combining q and Python: improving on OLS

← Older revision		Revision as of 15:49, 1 July 2021
Line 507:		Line 507:

	<span class="plainlinks">[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]</span> tells us that '''gradient boosting'''		<span class="plainlinks">[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]</span> tells us that '''gradient boosting'''
	<~~longquote~~>		<blockquote>
	is a machine learning technique for regression, classification and other tasks, which produces a prediction model in the form of an ensemble of weak prediction models, typically decision trees. When a decision tree is the weak learning, the resulting algorithm is called gradient boosted trees, which usually outperforms random forest. It builds the model in a stage-wise fashion like other boosting methods do, and it generalizes them by allowing optimization of an arbitrary differentiable loss function.		is a machine learning technique for regression, classification and other tasks, which produces a prediction model in the form of an ensemble of weak prediction models, typically decision trees. When a decision tree is the weak learning, the resulting algorithm is called gradient boosted trees, which usually outperforms random forest. It builds the model in a stage-wise fashion like other boosting methods do, and it generalizes them by allowing optimization of an arbitrary differentiable loss function.
	</~~longquote~~>		</blockquote>

	Let's install XGBoost:		Let's install XGBoost:

Admin: /* Combining q and Python: improving on OLS */

2021-07-01T15:49:08Z

Combining q and Python: improving on OLS

← Older revision		Revision as of 15:49, 1 July 2021
Line 502:		Line 502:

	XGBoost is advertised as		XGBoost is advertised as
	<~~longquote~~>		<blockquote>
	an optimized distributed gradient boosting library designed to be highly '''efficient''', '''flexible''' and '''portable'''. It implements machine learning algorithms under the Gradient Boosting framework. XGBoost provides a parallel tree boosting (also known as GBDT, GBM) that solve many data science problems in a fast and accurate way. The same code runs on major distributed environment (Hadoop, SGE, MPI) and can solve problems beyond billions of examples.		an optimized distributed gradient boosting library designed to be highly '''efficient''', '''flexible''' and '''portable'''. It implements machine learning algorithms under the Gradient Boosting framework. XGBoost provides a parallel tree boosting (also known as GBDT, GBM) that solve many data science problems in a fast and accurate way. The same code runs on major distributed environment (Hadoop, SGE, MPI) and can solve problems beyond billions of examples.
	</~~longquote~~>		</blockquote>

	<span class="plainlinks">[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]</span> tells us that '''gradient boosting'''		<span class="plainlinks">[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]</span> tells us that '''gradient boosting'''

Admin at 17:47, 19 June 2021

2021-06-19T17:47:39Z

← Older revision		Revision as of 17:47, 19 June 2021
Line 489:		Line 489:
	</pre>		</pre>
	0.5941468 (approx. 59%). Still (somewhat surprisingly) greater than in-sample, but much closer.		0.5941468 (approx. 59%). Still (somewhat surprisingly) greater than in-sample, but much closer.

			=Combining q and Python: improving on OLS=

			Can we do better than that?

			For some things q is perfect (e.g. working with big data), whereas others are best reserved for Python (and especially its excellent machine learning libraries). We'll now use Python to improve on the results of the OLS regression.

			We'll use the <tt>qpython</tt> module to interact with q from Python. We install it using
			<pre>
			pip install qpython
			</pre>

			XGBoost is advertised as
			<longquote>
			an optimized distributed gradient boosting library designed to be highly '''efficient''', '''flexible''' and '''portable'''. It implements machine learning algorithms under the Gradient Boosting framework. XGBoost provides a parallel tree boosting (also known as GBDT, GBM) that solve many data science problems in a fast and accurate way. The same code runs on major distributed environment (Hadoop, SGE, MPI) and can solve problems beyond billions of examples.
			</longquote>

			<span class="plainlinks">[https://en.wikipedia.org/wiki/Gradient_boosting Wikipedia]</span> tells us that '''gradient boosting'''
			<longquote>
			is a machine learning technique for regression, classification and other tasks, which produces a prediction model in the form of an ensemble of weak prediction models, typically decision trees. When a decision tree is the weak learning, the resulting algorithm is called gradient boosted trees, which usually outperforms random forest. It builds the model in a stage-wise fashion like other boosting methods do, and it generalizes them by allowing optimization of an arbitrary differentiable loss function.
			</longquote>

			Let's install XGBoost:
			<pre>
			pip install xgboost
			</pre>

			We start a Jupyter notebook and import the following Python modules:
			<pre>
			import numpy as np
			import qpython.qconnection
			import sklearn.metrics
			import xgboost
			</pre>

			We want Python to be able to talk to kdb+/q. To this end, in q, we open a port:
			<pre>
			\p 41822
			</pre>

			We connect to this port from the Python side:
			<pre>
			q = qpython.qconnection.QConnection('localhost', 41822, pandas=True)
			q.open()
			</pre>

			We fetch the data set from q to Python:
			<pre>
			x_train = np.array(q('x_train'))
			y_train = np.array(q('y_train'))
			x_test = np.array(q('x_test'))
			y_test = np.array(q('y_test'))
			</pre>

			We then create an XGBoost model with default parameters (let's see how well XGBoost works out of the box):
			<pre>
			model = xgboost.XGBRegressor()
			</pre>
			and fit this model to the data:
			<pre>
			model.fit(x_train, y_train)
			</pre>
			The model is then used to obtain the predictions <tt>y_train_pred</tt>:
			<pre>
			y_train_pred = model.predict(x_train)
			</pre>

			How good is the fit? What is our in-sample <math>R^2</math>?
			<pre>
			sklearn.metrics.r2_score(y_train, y_train_pred)
			</pre>
			The result is
			<pre>
			0.9955447127025048
			</pre>

			Of course, we are interested in the out-of-sample, rather than in-sample, performance of the model. Let's apply the model to the test set:
			<pre>
			y_test_pred = model.predict(x_test)
			</pre>
			What's our out out-of-sample <math>R^2</math>?
			<pre>
			sklearn.metrics.r2_score(y_test, y_test_pred)
			</pre>
			<pre>
			0.7187695555297899
			</pre>
			The out-of-sample <math>R^2</math> of about 72% is significantly better than that produced using the OLS regression.

Admin at 13:57, 19 June 2021

2021-06-19T13:57:24Z

← Older revision		Revision as of 13:57, 19 June 2021
Line 483:		Line 483:
	r_squared[y_train;y_train_pred]		r_squared[y_train;y_train_pred]
	</pre>		</pre>
	Now we obtain the in-sample <math>R^2</math> of 0.5706795 (approx. 57\%). What about out-of-sample?		Now we obtain the in-sample <math>R^2</math> of 0.5706795 (approx. 57%). What about out-of-sample?
	<pre>		<pre>
	y_test_pred:ols_predict[model;x_test];		y_test_pred:ols_predict[model;x_test];
	r_squared[y_test;y_test_pred]		r_squared[y_test;y_test_pred]
	</pre>		</pre>
	0.5941468 (approx. 59\%). Still (somewhat surprisingly) greater than in-sample, but much closer.		0.5941468 (approx. 59%). Still (somewhat surprisingly) greater than in-sample, but much closer.

Admin at 13:56, 19 June 2021

2021-06-19T13:56:23Z

← Older revision		Revision as of 13:56, 19 June 2021
Line 457:		Line 457:

	[[File:scatter_plot_out_of_sample.png]]		[[File:scatter_plot_out_of_sample.png]]

			On the in-sample scatter plot we see the outlier (the rightmost point on the plot). This outlier is likely affecting the in-sample <math>R^2</math>. Let's find this outlier:
			<pre>
			select from t_train where (max abs y_train_pred-y_train)=abs y_train_pred-y_train
			</pre>
			<pre>
			no transaction_date house_age distance_to_nearest_mrt number_of_convenience_stores latitude longitude house_price_per_unit_area
			--------------------------------------------------------------------------------------------------------------------------------
			271 2013.333 10.8 252.5822 1 24.9746 121.5305 117.5
			</pre>
			This outlier corresponds to a property with an unusually high <tt>house_price_per_unit_area</tt>. We can even find this property on Google Maps (by entering the latitude and longitude).

			Let's remove this outlier:
			<pre>
			delete from `t_train where (max abs y_train_pred-y_train)=abs y_train_pred-y_train;
			x_train:flip`float$t_train[-1_2_cols t_train];
			y_train:raze t_train[-1#cols t_train];
			</pre>

			Now let's repeat the fitting procedure:
			<pre>
			model:ols_fit[x_train;y_train];
			y_train_pred:ols_predict[model;x_train];

			r_squared[y_train;y_train_pred]
			</pre>
			Now we obtain the in-sample <math>R^2</math> of 0.5706795 (approx. 57\%). What about out-of-sample?
			<pre>
			y_test_pred:ols_predict[model;x_test];
			r_squared[y_test;y_test_pred]
			</pre>
			0.5941468 (approx. 59\%). Still (somewhat surprisingly) greater than in-sample, but much closer.

Admin at 13:38, 19 June 2021

2021-06-19T13:38:10Z

← Older revision		Revision as of 13:38, 19 June 2021
Line 448:		Line 448:
	flip `y_train`y_train_pred!(y_train;y_train_pred)		flip `y_train`y_train_pred!(y_train;y_train_pred)
	</pre>		</pre>

			[[File:scatter_plot_in_sample.png]]

	Out-of-sample:		Out-of-sample:
Line 453:		Line 455:
	flip `y_test`y_test_pred!(y_test;y_test_pred)		flip `y_test`y_test_pred!(y_test;y_test_pred)
	</pre>		</pre>

			[[File:scatter_plot_out_of_sample.png]]

Admin at 13:34, 19 June 2021

2021-06-19T13:34:00Z

← Older revision		Revision as of 13:34, 19 June 2021
Line 439:		Line 439:
	</pre>		</pre>
	The resulting <math>R^2</math> is 0.5986606 (approx. 60%).		The resulting <math>R^2</math> is 0.5986606 (approx. 60%).

			It is unusual for the out-of-sample <math>R^2</math> to be better than the in-sample <math>R^2</math>, but it may occasionally happen. Why did this happen in our case?

			The answer is revealed by the scatter-plots.

			In-sample:
			<pre>
			flip `y_train`y_train_pred!(y_train;y_train_pred)
			</pre>

			Out-of-sample:
			<pre>
			flip `y_test`y_test_pred!(y_test;y_test_pred)
			</pre>

Admin at 13:26, 19 June 2021

2021-06-19T13:26:19Z

← Older revision		Revision as of 13:26, 19 June 2021
Line 433:		Line 433:
	</pre>		</pre>

	~~Perhaps~~ we ~~would do~~ better ~~if we allowed~~ <math>y</math>~~-intercept?~~		How well are we doing out-of-sample? Surprisingly, better than in-sample:
			<pre>
			y_test_pred:ols_predict[model;x_test];
			r_squared[y_test;y_test_pred]
			</pre>
			The resulting <math>R^2</math> is 0.5986606 (approx. 60%).

Admin at 13:18, 19 June 2021

2021-06-19T13:18:35Z

← Older revision		Revision as of 13:18, 19 June 2021
Line 428:		Line 428:
	</pre>		</pre>

	We obtain the in-sample <math>R^2</math> of		We obtain the in-sample <math>R^2</math> of 0.4859935 (approx. 49%):
	<pre>		<pre>
	r_squared[y_train;y_train_pred]		r_squared[y_train;y_train_pred]