DATA MINING Desktop Survival Guide by Graham Williams
Desktop Survival Project Home List of Figures List of Tables Data Mining Data Mining Data Mining with Rattle Introduction Data Transform Explore A Model Building Framework Unsupervised Modelling Two Class Models Multi Class Models Regression Models Text Mining Evaluation and Deployment Moving into R Troubleshooting R for the Data Miner R Data Graphics in R Understanding Data Preparing Data Building Models Evaluating Models Algorithms Apriori Bagging Bayes Classifier Boosting Cluster Analysis Conditional Trees Hierarchical Clustering K-Means K-Nearest Neighbours Linear Models Logistic Regression Neural Networks Support Vector Machines Text Mining Open Products AlphaMiner Borgelt Data Mining Suite KNime R Rattle Weka Closed Products C4.5 Clementine Equbits Foresight GhostMiner InductionEngine ODM Enterprise Miner Statistica Data Miner TreeNet Virtual Predict Appendicies Glossary Bibliography Index

Excel

The simplest way to transfer data from Excel, or any spreadsheet in fact, is to save the data in CSV (Comma Separated Value) format, usually into a file with extension .csv. This is supported in all spreadsheet applications and is effective in that if we are fluent with data manipulation in Excel, then we can get our data into shape using Excel, and then load it into Rattle for data mining.

Alternatively, on MS/Windows Excel spreadsheetscan be directly accessed and manipulated through ODBCusing odbcConnectExcel. Available sheets can be listed with sqlTables and individual sheets can be queried through the sqlQuery function or else imported with sqlFetch. To use a spreadsheet as a database though, the first row of the spreadsheet must be the column names! If not, we will find that we end up reading from the second row of our data.

In this example we open a connection to a spreadsheet and then give a sample query:

> library(RODBC)
> channel <- odbcConnectExcel("h:/audit.xls")
> ds <- sqlQuery(channel, "SELECT * FROM `Sheet1$` 
                                  WHERE Type = "TOC" 
                                  AND   Valve="5010-05"")
> odbcClose(channel)

To simply fetch the full contents of a single sheet of a spreedsheet we can use the sqlFetch query:

library(RODBC)
channel <- odbcConnectExcel("h:/audit.xls")
ds <- sqlFetch(xlsConnect, "Sheet1")
odbcClose(xlsConnect)

On MS/Windows you can also use the xlsReadWrite package to directly access and manipulate an Excel spreadsheet. For example, to read a spreadsheet we can use read.xls:

library(xlsReadWrite)
ds <- read.xls("audit.xls", colNames=TRUE, sheet=6,
               colClasses=c("factor","integer","double"))

Support further development through the purchase of the PDF version of the book.
Brought to you by Togaware.