Sometimes, it’s just easier to query data in SQLite via SQL than write formulas in Excel. The below script assumes that you have python and pandas installed.
Use Python to convert an Excel file (.xlsx) to a SQLite database:
import sqlite3
import pandas as pd
filename="NameOfYourExcelFile"
con=sqlite3.connect(filename+".db")
wb=pd.read_excel(filename+'.xlsx',sheet_name=None)
for sheet in wb:
wb[sheet].to_sql(sheet,con, index=False)
con.commit()
con.close()
I was interested in this experiment that involved the querying of 9 million unique records distributed across three HDFS files (total 1.4GB) using Spark RDD’s, Spark DataFrames and SparkSQL to determine performance differences.
The results for the two different types of queries from the experiment are as shown below:
Based on my own experience I’ve found that while RDD’s are the most performant, they are tedious when it comes to data manipulation. SparkSQL is definitely the most versatile and maintainable with a slight performance edge over DataFrames when grouping and ordering large datasets.
The below is an attempt to create a reusable template that can easily modified to repurpose SparkSQL queries:
from pyspark.sql import SparkSession
# Create an empty pandas DataFrame
df = pd.DataFrame()
# Get an existing Spark Session or Create a New One
sparkSession = SparkSession.builder.appName("reading csv").getOrCreate()
# Read a CSV file and populate a Spark DataFrame
df = sparkSession.read.csv("YOURFILE.csv", header=True, sep=",").cache()
# Print out the schema of the Spark Dataframe
df.dtypes
# Display the schema of the Spark DataFrame
df.schema
# Create a Table from the Spark DataFrame before running SparkSQL
df.createOrReplaceTempView("SOMETABLE")
# Run SparkSQL
sqlDF = sparkSession.sql('''
SELECT Company, Rating, count(*) as Number_of_Ratings
FROM SOMETABLE
GROUP BY Company, Rating
ORDER BY Company
''')
# Display the results
sqlDF.show(200, False)
The below Natural Language workflow can be used to generate Topic Models from a monolingual corpus, along with their associated Word Clouds.
Latent Semantic Indexing, Latent Dirichlet Allocation and Hierarchical Dirichlet Process are the three techniques available for Topic Modelling in the Orange 3 toolkit.
From my experience, the most useful and relevant Topics were produced by the Latent Semantic Indexing (LSI) because of its ability to correlate semantically related terms that are latent in a collection of text. LSI employs a mathematical technique called singular value decomposition (SVD) to identify patterns in the relationships between the terms and concepts contained in an unstructured collection of text.
The following code displays the binary contents of a parquet file as a table in a Jupyter notebook:
import pyarrow.parquet as pq
import pandas as pd
table = pq.read_table('SOME_PARQUET_TEST_FILE.parquet')
table_dict = dict(table.to_pydict())
items = table_dict.items()
keys = [item[0] for item in items]
values = [item[1] for item in items]
pivoted_values = zip(*values)
table_dictionary_array = []
for record in pivoted_values:
table_dictionary_array.append(dict(zip(keys, record)))
df = pd.DataFrame.from_dict(table_dictionary_array)
df
The following code displays the binary contents of an AVRO file as a table in a Jupyter notebook:
import avro.schema, pandas as pd
from avro.datafile import DataFileReader, DataFileWriter
from avro.io import DatumReader, DatumWriter
from tabulate import tabulate
from urllib.request import urlopen
from pandas.io.json import json_normalize
reader = DataFileReader(open("SOME_AVRO_FILE.avro", "rb"),
DatumReader())
df = pd.DataFrame(reader)
print(tabulate(df, headers="firstrow", tablefmt='grid'))
The steps below leverage information from this and this site, along with some modifications and sequencing before I could get my VM configured 100%.
STEP 1: Install VMware tools after creating Ubuntu VM
While Anaconda is a quick and easy way of getting Python libraries installed, I chose not to go with this option following some lessons learnt in package management and versioning during a recent Deep Learning project. The following steps therefore install all required packages manually and in a sequence that I found works best for me.
STEP 2: Install PRE-REQUISITE PYTHON LIBRARIES ON THE VM
$ sudo apt-get -y install git curl vim tmux htop ranger $ sudo apt-get -y install python-dev python-pip $ sudo apt-get -y install python-serial python-setuptools python-smbus
STEP 3: SET UP A CONTAINER FOR DATA SCIENCE ON THE VM
$ sudo pip install virtualenv $ cd ~/ $ mkdir venv $ pushd venv $ virtualenv data-science $ popd $ source ~/venv/data-science/bin/activate $ pip install --upgrade setuptools $ pip install virtualenvwrapper $ pip install cython $ pip install nose
STEP 4: INSTALL PYTHON DATA SCIENCE LIBRARIES ON THE VM
$ sudo apt-get -y install python-numpy python-scipy python-matplotlib $ sudo apt-get -y install ipython ipython-notebook $ sudo apt-get -y installpython-pandas python-sympy python-nose $ pip install jupyter $ sudo apt-get -y install libfreetype6-dev libpng12-dev libjs-mathjax $ sudo apt-get -y install fonts-mathjax libgcrypt11-dev libxft-dev $ pip install matplotlib $ sudo apt-get install libatlas-base-dev gfortran $ pip install Seaborn && pip install statsmodels $ pip install scikit-learn && pip install numexpr $ pip install bottleneck && pip install pandas $ pip install SQLAlchemy && pip install pyzmq $ pip install jinja2 && pip install tornado $ pip install nltk && pip install gensim $ pip install tensorflow && pip install keras
STEP 5: CONFIGURE NETWORKING, SSH, FIREWALL & JUPYTER ON THE VM
$ sudo apt-get install openssh-server $ sudo ufw allow from host_ip to any port 22 $ nice jupyter notebook
Once the Jupyter notebook server runs in Terminal following the last command, make note of the token querystring parameter at the end of the notebook URL http://localhost:8888/?token=62gwj3k28djdkelsnab7293kkl0172hdu3ks9al7sj3kb1j
STEP 6: SSH TUNNEL FROM HOST WINDOWS O/S TO GUEST UBUNTU VM
Set up the following in Putty on Windows to enable SSH Tunnelling. The below screenshot has the settings for the Windows host
The below screenshot has the settings for the port forwarding, i.e.; the Jupyter server URL http://localhost:8000 on the Windows host O/S forwards to http://localhost:8888 on the Ubuntu VM
STEP 7: BROWSE TO JUPYTER NOTEBOOK ON HOST O/S
Browse to http://localhost:8000 on your guest O/S and verify that you can access the Jupyter notebook. When run for the first time, the web page will request a token. Enter the token you saved from Step 5.
If you have any problems with connectivity it is likely due to the Ubuntu Guest O/S firewall ufw, see Step 5 above for ufw configuration.
STEP 1 – Install Theano
After installing Anaconda run the following command in Terminal
$ conda install theano pygpu
STEP 2 – Install the correct CUDA driver based on your model of Mac
Browse to this link to download the correct version of the CUDA driver for your Mac. Make sure to click on the driver link and choose the Supported Products tab to determine the Mac hardware that the particular driver supports.
For some older Macs, the version 6.5.45 driver is the best choice.
STEP 3 – Install the CUDA toolkit, but don’t upgrade the driver
Download the right version of the CUDA toolkit for your Mac from the archive here.
For some older Macs, version 6.5 toolkit is the best choice
STEP 4 – Install XCode
Download the XCode app from the Apple site and install it on your Mac
STEP 5 – Install XCode Command Line tools
Open a Terminal and run the following command
$ xcode-select --install
Choose to install the command line tools
STEP 6 – Check the cc compiler
Open a Terminal and run the following command
$ /usr/bin/cc --version
STEP 7 – Update your .bash_profile file
Add the following to .bash_profile
export LD_LIBRARY_PATH=/Developer/NVIDIA/CUDA-6.5/lib/
export CUDA_ROOT=/Developer/NVIDIA/CUDA-6.5/
export THEANO_FLAGS='mode=FAST_RUN,device=gpu,floatX=float32'
export PATH=/Developer/NVIDIA/CUDA-6.5/bin${PATH:+:${PATH}}
export DYLD_LIBRARY_PATH=/Developer/NVIDIA/CUDA-6.5/lib\
${DYLD_LIBRARY_PATH:+:${DYLD_LIBRARY_PATH}}
STEP 8 – Test the NVCC compiler
Run the following in Terminal
$ /Developer/NVIDIA/CUDA-6.0/bin/nvcc -V
STEP 9 – Switch to the Samples Directory
Switch to Samples directory that were installed as part of the toolkit
cd /Developer/NVIDIA/CUDA-6.0/samples/
STEP 10 – Make the Samples
Run the below, one line at a time and make sure you don’t get any errors
make -C 0_Simple/vectorAdd make -C 0_Simple/vectorAddDrv make -C 1_Utilities/deviceQuery make -C 1_Utilities/bandwidthTest
STEP 11 – Run the Samples
Switch to the relevant directory to run the compiled files
cd /Developer/NVIDIA/CUDA-6.5/samples/bin/x86_64/darwin/release
Make sure you get the relevant output when running the below line by line
./deviceQuery ./bandwidthTest
STEP 12 – Configure Theano to use the GPU
Create a file called .theanorc in your HOME directory and add the following to it
[blas] ldflags = [global] floatX = float32 device = gpu [nvcc] fastmath = True [gcc] cxxflags = -ID:\MinGW\include [cuda] # Set to where the cuda drivers are installed. root=/usr/local/cuda/
STEP 13 – Run the following to confirm that Theano now uses the GPU
from theano import function, config, shared, tensor
import numpy
import time
vlen = 10 * 30 * 768 # 10 x #cores x # threads per core
iters = 1000
rng = numpy.random.RandomState(22)
x = shared(numpy.asarray(rng.rand(vlen), config.floatX))
f = function([], tensor.exp(x))
print(f.maker.fgraph.toposort())
t0 = time.time()
for i in range(iters):
r = f()
t1 = time.time()
print("Looping %d times took %f seconds" % (iters, t1 - t0))
print("Result is %s" % (r,))
if numpy.any([isinstance(x.op, tensor.Elemwise) and
('Gpu' not in type(x.op).__name__)
for x in f.maker.fgraph.toposort()]):
print('Used the cpu')
else:
print('Used the gpu')
I use the below script to set up my R environment with required Data Science libraries.
Through trial and error, I’ve found that MacOS/X is my preferred O/S environment for Data Science. Some of the below libraries (like ‘bigrf’ used for Random Forests for larger datasets) are not available for Windows. Also for packages that I needed to compile, MacOS/X just worked every time.
It is important to ensure that Java is installed on your system prior to running the below.
STEP 1: INSTALL JAVA & POINT R TO IT
On a Mac add the following to your bash_profile
$ export LD_LIBRARY_PATH=/Library/Java/JavaVirtualMachines/jdk1.8.0_102.jdk/Contents/Home/jre/lib:/Library/Java/JavaVirtualMachines/jdk1.8.0_102.jdk/Contents/Home/jre/lib/server
Run the below to create the relevant links
$ sudo ln -s $(/usr/libexec/java_home)/jre/lib/server/libjvm.dylib /usr/local/lib
Point R to Java
$ sudo R CMD javareconf
Install the pre-requisite rJava package in R
install.packages('rJava', type='source')
STEP 2: PRE-MODELING STAGE PACKAGES
# Data Visualisation
install.packages("ggvis")
install.packages("ggplot2")
install.packages("googleVis")
# Data Transformation
install.packages("plyr")
install.packages("data.table")
# Missing Value Imputations
install.packages("missForest")
install.packages("missMDA")
# Outlier Detection
install.packages("outliers")
install.packages("evir")
# Feature Selection
install.packages("features")
install.packages("RRF")
# Dimension Reduction
install.packages("FactoMineR")
install.packages("CCP")
STEP 3: MODELING STAGE PACKAGES
# Continuous regression
install.packages("car")
install.packages("randomForest")
# Ordinal regression
install.packages("rminer")
install.packages("CORElearn")
# Classification
install.packages("caret")
install.packages("devtools")
library(devtools)
install_github("bigrf", repo='aloysius-lim/bigrf')
#install.packages("~/Downloads/bigrf_0.1-11.tar.gz", repos = NULL, type = "source") #LINUX
# Clustering
install.packages("cba")
install.packages("Rankcluster")
# Time Series
install.packages("forecast")
install.packages("ltsa")
# Survival
install.packages("survival")
install.packages("BaSTA")
STEP 4: POST MODELING STAGE PACKAGES
# General Model Validation
install.packages("lsmeans")
install.packages("comparison")
# Regression Validation
install.packages("regtest")
install.packages("ACD")
# Classification Validation
install.packages("binomTools")
install.packages("Daim")
# Clustering Validation
install.packages("clusteval")
install.packages("sigclust")
# ROC Analysis
install.packages("pROC")
install.packages("timeROC")
STEP 4: OTHER USEFUL PACKAGES
# Improve Performance
install.packages("Rcpp")
install.packages("parallel")
# Work with Web
install.packages("XML")
install.packages("jsonlite")
install.packages("httr")
# Report Results
install.packages("shiny")
install.packages("rmarkdown")
# Text Mining
install.packages("tm")
install.packages("twitteR")
# Database
install.packages("sqldf")
# Install unixodbc first before RODBC
#On a Mac run: brew install unixodbc
install.packages("RODBC")
install.packages("RMongo")
# Miscellaneous
install.packages("swirl")
install.packages("reshape2")
install.packages("qcc")
install.packages("qdap")
STEP 5: RUN INVENTORY OF INSTALLED PACKAGES
# Get a full list of installed packages write.csv(installed.packages(), file = "InstalledPackages.csv")
Nice visual summary of Machine Learning Algorithms http://machinelearningmastery.com/a-tour-of-machine-learning-algorithms/
