Google Spreadsheet Add Ons for Data Analysis

Google spreadsheets are rapidly replacing excel for some types of data analysis. Here are some useful Google spreadsheet add ons for data analysis.

BLOCKSPRING

Blockspring helps people scrape websites, get product prices from Amazon, search Bing, save files to Dropbox, automate Twitter and outbound emails, find sales leads, run advanced text analysis, and much, much more.  It turns Google Sheets into a playground for APIs. Irrespective of the technical background, add-on enables one to automate work and build advanced tools.

Text Analysis

The Text Analysis add-on provides an easy way to analyze any text  as in links, tweets or documents in Google Spreadsheets. It provides various Natural Language Processing and Machine Learning tools.

The addon can be used to perform various tasks like

Sentiment analysis on Social Media streams

Extract mentions of entities and concepts
Summarize long chunks of text and articles

Detect the language of a document or tweet
Classify your documents or links into more than 500 categories
Extract the full text of an article, as well as its author name, embedded media etc

Text Mining

With this add-on one can analyze any kind of text and perform different types of Text Analysis and perform sentiment analysis on content in different languages.
It supports several languages like Italian, English, French, German and Portuguese.
The add-on helps enhance columns of text by automatically extracting keywords and named entities and linking them to Wikipedia.

Translate My Sheet

Translate my sheet add-on  helps translate your spreadsheet cell by cell or fully in one click in to  more than 100 languages.

Remove Duplicates

The Add-on helps in finding duplicate or unique values between two tables or in one sheet in 5 simple steps.

Power Tools

This Add-on provides one-click solutions for daily tasks like splitting cells, removing duplicates, changing case, finding and cleaning up data, working with formulae & more. Power Tools add-on cuts the clicks on repeated  tasks and brings features for  organizing and unifying data in Google Sheets.

Smart Autofill

Smart Autofill add-on uses Machine Learning via the Google Prediction API to fill missing values in a column of data, based on other data in the column and the data in adjacent columns.
It will look for patterns in the rows of data where there are values present in the column, and apply them to fill in the missing values of the column.

Mapping Sheets

The Add-on helps in plotting your own data onto a Google Map directly from Google Sheets.

Google Analytics

The Google Analytics spreadsheet add-on brings you the power of the Google Analytics API to Google Spreadsheets. With this tool, you can:
- Query data from multiple views (profiles).
- Create custom calculations from your report data.
- Create dashboards with embedded data visualizations.
- Schedule reports to run automatically so your data is always current.
- Easily control who can see these data and visualizations by leveraging Google Spreadsheet’s existing sharing and privacy features.

Find Fuzzy Matches

This add-on is a great assistant for correcting all fuzzy matches and removing partial duplicates from your sheet.

5 major differences between Naïve Bayes and Logistic Regression.

1.       Purpose or what class of machine leaning does it solve? 
Both the algorithms can be used for classification of the data. Using these algorithms, you could predict whether a banker can offer a loan to a customer or not or identify given mail is a Spam or ham

2.       Algorithm’s Learning mechanism
Naïve Bayes: For the given features (x) and the label y, it estimates a joint probability from the training data. Hence this is a Generative model
Logistic regression: Estimates the probability(y/x) directly from the training data by minimizing error. Hence this is a Discriminative model

3.       Model assumptions
Naïve Bayes: Model assumes all the features are conditionally independent .so, if some of the features are dependent on each other (in case of a large feature space), the prediction might be poor.
Logistic regression: It the splits feature space linearly, it works OK even if some of the variables are correlated

4.       Model limitations
Naïve Bayes: Works well even with less training data, as the estimates are based on the joint density function
Logistic regression: With the small training data, model estimates may over fit the data

5.       Approach to be followed to improve the results
Naïve Bayes: When the training data size is less relative to the features, the information/data on prior probabilities help in improving the results 
Logistic regression: When the training data size is less relative to the features, Lasso and Ridge regression will help in improving the results.

How to Learn R

December 10, 2015

By Tal Galili

293
inShare

There are tons of resources to help you learn the different aspects of R, and as a beginner this can be overwhelming. It’s also a dynamic language and rapidly changing, so it’s important to keep up with the latest tools and technologies.

That’s why R-bloggers and DataCamp have worked together to bring you a learning path for R. Each section points you to relevant resources and tools to get you started and keep you engaged to continue learning. It’s a mix of materials ranging from documentation, online courses, books, and more.

Just like R, this learning path is a dynamic resource. We want to continually evolve and improve the resources to provide the best possible learning experience. So if you have suggestions for improvement please emailtal.galili@gmail.com with your feedback.

Learning Path

Getting started:  The basics of R

Setting up your machine

R packages

Importing your data into R

Data Manipulation

Data Visualization

Data Science & Machine Learning with R

Reporting Results in R

Next steps

Getting started:  The basics of R

The best way to learn R is by doing. In case you are just getting started with R, this free introduction to R tutorial by DataCamp is a great resource as well the successorIntermediate R programming (subscription required). Both courses teach you R programming and data science interactively, at your own pace, in the comfort of your browser. You get immediate feedback during exercises with helpful hints along the way so you don’t get stuck.

Another free online interactive learning tutorial for R is available by O’reilly’s code school website called try R. An offline interactive learning resource isswirl, an R package that makes if fun and easy to become an R programmer. You can take a swirl course by (i) installing the package in R, and (ii) selecting a course from the course library. If you want to start right away without needing to install anything you can also choose for the online version of Swirl.

There are also some very good MOOC’s available on edX and Coursera that teach you the basics of R programming. On edX you can find Introduction to R Programming by Microsoft, an 8 hour course that focuses on the fundamentals and basic syntax of R. At Coursera there is the very popular R Programming course by Johns Hopkins. Both are highly recommended!

If you instead prefer to learn R via a written tutorial or book there is plenty of choice. There is the introduction to R manual by CRAN, as well as some very accessible books like Jared Lander’s R for Everyone or R in Action by Robert Kabacoff.

Setting up your machine

You can download a copy of R from the Comprehensive R Archive Network (CRAN). There are binaries available for Linux, Mac and Windows.

Once R is installed you can choose to either work with the basic R console, or with an integrated development environment (IDE). RStudio is by far the most popular IDE for R and supports debugging, workspace management, plotting and much more (make sure to check out the RStudio shortcuts).

Next to RStudio you also have Architect, and Eclipse-based IDE for R. If you prefer to work with a graphical user interface you can have a look at R-commander  (aka as Rcmdr), or Deducer.

R packages

R packages are the fuel that drive the growth and popularity of R. R packages are bundles of code, data, documentation, and tests that are easy to share with others. Before you can use a package, you will first have to install it. Some packages, like the base package, are automatically installed when you install R. Other packages, like for example the ggplot2 package, won’t come with the bundled R installation but need to be installed.

Many (but not all) R packages are organized and available from CRAN, a network of servers around the world that store identical, up-to-date, versions of code and documentation for R. You can easily install these package from inside R, using the install.packages function. CRAN also maintains a set of Task Views that identify all the packages associated with a particular task such as for example TimeSeries.

Next to CRAN you also have bioconductor which has packages for the analysis of high-throughput genomic data, as well as for example the github andbitbucket repositories of R package developers. You can easily install packages from these repositories using the devtools package.

Finding a package can be hard, but luckily you can easily search packages from CRAN, github and bioconductor using Rdocumentation, inside-R, or you can have a look at this quick list of useful R packages.

To end, once you start working with R, you’ll quickly find out that R package dependencies can cause a lot of headaches. Once you get confronted with that issue, make sure to check out packrat (see video tutorial) or checkpoint. When you’d need to update R, if you are using Windows, you can use the updateR() function from the installr package.

Importing your data into R

The data you want to import into R can come in all sorts for formats: flat files, statistical software files, databases and web data.

Getting different types of data into R often requires a different approach to use. To learn more in general on how to get different data types into R you can check out this online Importing Data into R tutorial (subscription required), this post on data importing, or this webinar by RStudio.

• Flat files are typically simple text files that contain table data. The standard distribution of R provides functionality to import these flat files into R as a data frame with functions such as read.table() andread.csv() from the utils package. Specific R packages to import flat files data are readr, a fast and very easy to use package that is less verbose as utils and multiple times faster (more information), and data.table’sfread() function for importing and munging data into R (using the fread function).

• In case you want to get your excel files into R, it’s a good idea to have a look at thereadxl package. Alternatively, there is the gdata package which has function that supports the import of Excel data, and the XLConnectpackage. The latter acts as a real bridge between Excel and R meaning you can do any action you could do within Excel but you do it from inside R. Read more on importing your excel files into R.

• Software packages such as SAS, STATA and SPSS use and produce their own file types. The haven package by Hadley Wickham can deal with importing SAS, STATA and SPSS data files into R and is very easy to use. Alternatively there is the foreign package, which is able to import not only SAS, STATA and SPSS files but also more exotic formats like Systat and Weka for example. It’s also able to export data again to various formats. (Tip: if you’re switching from SAS,SPSS or STATA to R, check out Bob Muenchen’s tutorial (subscription required))

• The packages used to connect to and import from a relational database depend on the type of database you want to connect to. Suppose you want to connect to a MySQL database, you will need the RMySQL package. Others are for example theRpostgreSQL and ROracle package.The R functions you can then use to access and manipulate the database, is specified in another R package called DBI.

• If you want to harvest web data using R you need to connect R to resources online using API’s or through scraping with packages likervest. To get started with all of this, there is this great resource freely available on the blog of Rolf Fredheim.

Data Manipulation

Turning your raw data into well structured data is important for robust analysis, and to make data suitable for processing. R has many built-in functions for data processing, but they are not always that easy to use. Luckily, there are some great packages that can help you:

• The tidyr package allows you to “tidy” your data. Tidy data is data where each column is a variable and each row an observation. As such, it turns your data into data that is easy to work with. Check this excellent resource on how you can tidy your data using tidyr.
• If you want to do string manipulation, you should learn about thestringr package. The vignette is very understandable, and full of useful examples to get you started.
• dplyr is a great package when working with data frame like objects (in memory and out of memory). It combines speed with a very intuitive syntax. To learn more on dplyr you can take this data manipulation course (subscription required) and check out this handy cheat sheet.
• When performing heavy data wrangling tasks, the data.table package should be your “go-to”package. It’s blazingly fast, and once you get the hang of it’s syntax you will find yourself using data.table all the time.Check this data analysis course (subscription required) to discover the ins and outs of data.table, and use this cheat sheet as a reference.
• Chances are you find yourself working with times and dates at some point. This can be a painful process, but luckily lubridate makes it a bit easier to work with. Check it’s vignette to better understand how you can use lubridate in your day-to-day analysis.
• Base R has limited functionality to handle time series data. Fortunately, there are package like zoo, xts and quantmod. Take this tutorial by Eric Zivot to better understand how to use these packages, and how to work with time series data in R.

If you want to have a general overview of data manipulation with R, you can read more in the book Data Manipulation with R or see the Data Wrangling with R video by RStudio. In case you run into troubles with handling your data frames, check 15 easy solutions to your data frame problems.

Data Visualization

One of the things that make R such a great tool is its data visualizations capabilities. For performing visualizations in R, ggplot2 is probably the most well known package and a must learn for beginners! You can find all relevant information to get you started with ggplot2 onhttp://ggplot2.org/ and make sure to check out the cheatsheet and the upcomming book. Next to ggplot2, you also have packages such as ggvis for interactive web graphics (seetutorial (subscription required)), googleVis to interface with google charts (learn to re-create this TED talk), Plotly for R, and many more. See the task view for some hidden gems, and if you have some issues with plotting your datathis post might help you out.

In R there is a whole task view dedicated to handling spatial data that allow you to create beautiful maps such as this famous one:

To get started look at for example a package such as ggmap, which allows you to visualize spatial data and models on top of static maps from sources such as Google Maps and Open Street Maps. Alternatively you can start playing around with maptools, choroplethr, and the tmap package. If you need a great tutorial take this Introduction to visualising spatial data in R.

You’ll often see that visualizations in R make use of all these magnificent color schemes that fit like a glove on the graph/map/… If you want to achieve this for your visualizations as well, then deepen yourself into the RColorBrewer package and ColorBrewer.

One of the latest visualizations tools in R is HTML widgets. HTML widgets work just like R plots but they create interactive web visualizations such as  dynamic maps (leaflet), time-series data charting (dygraphs), and interactive tables (DataTables). There are some very nice examples of HTML widgets in the wild, and solid documentation on how to create your own one (not in a reading mode: just watch this video).

If you want to get some inspiration on what visualization to create next, you can have a look at blogs dedicated to visualizations such as FlowingData.

Data Science & Machine Learning with R

There are many beginner resources on how to do data science with R. A list of available online courses:

• Andrew Conway’s Introduction to statistics with R (subscription required)
• Data Analysis and Statistical Inference
• Data Analysis for life sciences
• Data Science Specialization by Johns Hopkins

Alternatively, if you prefer a good read:

• Practical Data Science With R
• R for Data Science (upcomming, see progress)
• A Survival Guide to Data Science with R

Once your start doing some machine learning with R, you will quickly find yourself using packages such as caret, rpart and randomForest. Luckily, there are some great learning resources for these packages and Machine Learning in general. If you are just getting started,this guide will get you going in no time. Alternatively, you can have a look at the booksMastering Machine Learning with R and Machine Learning with R. If you are looking for some step-by-step tutorials that guide you through a real life example there is the Kaggle Machine Learning course or you can have a look at Wiekvoet’s blog.

Reporting Results in R

R Markdown is an authoring format that enables easy creation of dynamic documents, presentations, and reports from R. It is a great tool for reporting your data analysis in a reproducible manner, thereby making the analysis more useful and understandable. R markdown is based on knitr and pandoc. With R markdown, R generates a final document that replaces the R code with its results. This document can be in an html, word, pfd, ioslides, etc. format. You can even create interactive R markdown documents using Shiny. This 4 hour tutorial on Reporting with R Markdown (subscription required) get’s you going with R markdown, and in addition you can use this nice cheat sheet for future reference.

Next to R markdown, you should also make sure to check out  Shiny. Shiny makes it incredibly easy to build interactive web applications with R. It allows you to turn your analysis into interactive web applications without needing to know HTML, CSS or Javascript. RStudio maintains a great learning portal to get you started with Shiny, including this set of video tutorials (click on the essentials of Shiny Learning Roadmap). More advanced topics are available, as well as a great set of examples.

Next steps

After spending some time writing R code (and you became an R-addict), you’ll reach a point that you want to start writing your own R package. Hilary Parker from Etsy has written a short tutorial on how to create your first package, and if you’re really serious about it you need to read R packages, an upcoming book by Hadley Wickham that is already available for free on the web.

Once you become more fluent in writing R syntax (and consequently addicted to R), you will want to unlock more of its power (read: do some really nifty stuff). In that case make sure to check out RCPP, an R package that makes it easier for integrating C++ code with R, or RevoScaleR (start the free tutorial).

Finally, if you want to start learning on the inner workings of R and improve your understanding of it, the best way to get you started is by reading Advanced R.

50+ free online resources to learn more about data science and analysis

Machine learning

I selected those resources that are more suitable for beginners together with the parts of machine learning that I like the most.

• You can start with this introduction to data mining by Saed Sayad (University of Toronto). I found the first diagram particularly interesting.
• This glossary of machine learning terms is the best that I’ve found so far.
• An introduction to machine learning in 10 pictures is a short still great article to start with.
• Xavier Amatriain, one of the minds behind Netflix’s famous recommendation system, explains what are the advantages of different classification algorithms.
• Don’t miss this list of machine learning podcasts.
• Introduction to Recommender Systems is a 4-hour lecture of the 2014 Machine Learning Summer School at CMU. You can find other interesting machine learning lectures from the same summer school and other programs in Alex Smola’s YouTube channel.
• The Elements of Statistical Learning is a classic book ideal to understand the foundations of many machine learning methods.
• Tex Mining with WEKA cookbook for those who prefer Java.
• “Machine Learning Gremlins” is a presentation on common machine learning mistakes by Ben Hamner (Kaggle).
• Because we don’t always need exact answers, this introduction to stream mining by Mikio Braun can be very useful to you.
• If you want a wider vision of artificial intelligence, these lectures from the AI course taught at MIT by Patrick Winston.
• The lectures of the course “CS273a: Introduction to Machine Learning” by Prof. Alex Ihler (UCI) are available on Youtube.
• Choosing a machine learning model can be a cumbersome task. That’s why we have automatic machine learning to assist model selection. These slides are a good entry point to it.
• For a good picture of the state of the art of neural networks and deep learning, you can find tutorials and workshops of the NIPS 2014 conference in this YouTube channel. You can also find this summary of the conference by John Platt (Microsoft Research).

Statistics

• Pretty handy resource to explain statistical significance: how to Assess Statistical Significance.
• Top 10 big ideas covered in the Probability course at Harvard by Joe Blitzstein. You can also watch on Youtube the lectures of this course.
• Learn more about errors in hypothesis testing (statistical significance and power) from this lecture on Data Collection and Statistical Inference by Aaron Gullickson.
• What to do when data is missing? Learn what statisticians working in clinical trial field do.
• Introduction to Time Series Analysis from the book Engineering Statistics.
• This article talks about how to optimize decisions beyond A/B testing, including an introduction to the multi-armed bandit problem and the epsilon-greedy strategy.
• Jeff Rajeck has a series of posts titled “using data science with A/B tests”. I particularly enjoyed the one covering Bayesian analysis.
• Brian Caffo is one of the lecturers of the Data Science specialization on Coursera and his YouTube channel is full of resources to learn statistics.
• Some statistical concepts that data scientists usually overlook by Chris Fonnesbeck at SciPy 2015.

Python

Once you are familiar with Python, the following resources for machine learning and data analysis can take your skills to the next level:

• Video tutorials to learn how to use Python’s scikit-learn library to perform machine learning by Kevin Markham.
• 3h+ in-depth introduction to machine learning with scikit-learn by Kyle Kastner (Université de Montréal) and Andreas Mueller (NYU Center for Data Science).
• Machine learning cheat sheet for scikit-learn by Andreas Mueller.
• If you are interested in using neural networks in Python, Daniel Nouri explains how to solve the Facial Keypoint Detection Kaggle challenge using L....
• If you don’t have a technical background, you’ll find very useful the scripts that you can find in Practical Business Python.
• Notebook Gallery: links to the best IPython and Jupyter notebooks submitted by users.
• Recipes of the IPython Cookbook include excellent examples of how to use NumPy, scikit-learn and many other packages.
• Code snippets of some of the most common operations with Pandas.
• Make your first machine learning predictions using Python with this Kaggle tutorial.
• NLTK is the most popular library for natural language processing in Python. This presentation can give you a good overview of what you can do with it and this 1 hour tutorial will show you what you can do with it.
• PyDataTV is the YouTube channel of the PyData conferences. You can find keynotes, talks and workshops on how to use the PyData stack.

R

I’ve been trying hard to like R. It’s been in fact more than 5 years of trying to like it and I just simply prefer Python. In any case, I still frequently launch an R prompt to use some fantastic packages that R has.

• Intro to R is a playlist by Google Developers that explains all the basics of the language.
• Kaggle top ranker Xavier Conort listed 10 R Packages to win Kaggle competitions. That’s a good way to discover some very prominent R packages.
• An Introduction to Statistical Learning with Applications in R is a terrific free book full of examples.
• “R: the good parts” is an article by Jose Quesada (Data Science Retreat) that lists the main advantages of R with links to other good resources.
• Archetypal analysis is not usually taught in introductory machine learning courses.This post explains how to apply it and shows that it outperforms kmeans in a number of cases. Plus, archetypal analysis is easier to interpret.
• AnomalyDetection and BreakoutDetection: open source R packages for time-series analysis by Twitter.
• qdap is not only one of the best packages for natural language processing in R, but also one of the best documented. Use the vignette to get started with it and later on the manual.
• I know from my own experience that R’s memory limitations can give you a headache. These tricks are sometimes an effective painkiller. Also the slides “Taking R to the Limit: Large Datasets” might help.
• statsTeachR is a repository of lessons for teaching statistics using R.
• Make your first machine learning predictions using R with any of these four tutorials.

Applying data science to your organization

To end with, some examples on how data science and machine learning can be used to add value to your organization:

• Jeff Leek (Johns Hopkins University) shared some interesting learnings in his post10 things statistics taught us about big data analysis.
• What Data Science can do for entrepreneurs? Growth, retention, product customization and marketing optimization
• How to start data science initiatives in a lean and cost-effective way
• This paper explains how Booking.com uses crowdsourced data and machine learning to suggest the destination of the next trip.
• Someone asked how Quora uses machine learning and answers are very representative of how a website can benefit from using it.
• Airbnb guest requests are 4% more likely to be accepted after they used collaborative filtering to predict host’s behavior. They also used data to understand what their users want and show more relevant results to ....
• This paper describes how to do customer segmentation for customer retention using decision trees.
• How Spotify uses deep learning to recommend music is well documented in this post.
• How Google transcribed house numbers from Street View using neural networks.
• Predicting consumer credit-risk performance at the beginning of the... (Paper).