RETAIL PRICE EUR MAX EUR MIN 1689,45 1351,56 1653,91 1323,13 1009,91 807,93 991,09 792,87 1564,00 1251,20 1653,91 1323,13 846,82 677,46 865,64 692,51 1114,45 891,56 1239,91 991,93 1635,09 1308,07 1434,36 2153,64 940,91 2132,73 648,18 294,82 305,27 353,36 353,36 372,18 432,82 430,73 409,82 460,00 480,91 512,27 485,09 480,91 480,91 430,73 449,55 472,55 520,64 616,82 616,82 662,82 679,55 765,27 805,00 949,27 913,73 589,64 700,45 700,45 754,82 606,36 763,18 784,09 713,00 733,91 731,82 1350,73 1066,36 1147,49 1722,91 752,73 1706,18 518,54 235,86 244,22 282,69 282,69 297,74 346,26 344,58 327,86 368,00 384,73 409,82 388,07 384,73 384,73 344,58 359,64 378,04 416,51 493,46 493,46 530,26 543,64 612,22 644,00 759,42 730,98 471,71 560,36 560,36 603,86 485,09 610,54 627,27 570,40 587,13 585,46 1080,58 853,09

33 Built-in Peripherals

New New ORD New New

34 Built-in Hobs

34RSTFG400ERB 34RSTFG400EPN 34RSTC40RB 34RSTC40PN 34RSTC401IN 34RSRTF40FIN 34RSTFG400FIN 34RSTR400RBV 34RSTRS400RBV 34RSTT400BAV 34RSTTS400BAV 34RSTT400AV 34RSRTT640SAVF 34RSTT400VV 34RSTT40RIN 34RSDGF20GR 34RSDVF22GR 34RSDBF11GR 34RSDVG1CPN 34RSDVG2CPN 34RSDVE1CPN 34RSDVE2CPN 34RSDVB11CPN 34RSDVF11CPN 34RSVG40CPN 34RSVM31CPN 34RSRTV640FPN 34RSGTS761RU 34RSGTS761IN 34RSRTS742IN 34RSRVM640PN 34RSRVE6490PN 34RSRVE6490IN 34RSRVE6350PN 34RSRVE6350IN 34RSRVE6351IN 34RSRVI6370IN 34RSRCI637IN
New New New New New New New ORD ORD New New New New ORD New New New New New New New New New New New New New New New New New New New New New New New New

Page 3/3

STOCK CODE 34RSRVI740IN 34RSRVE7420IN 34RSRVE7410IN 34RSGS40RB 34RSGS41RB 34RSV46RB 34RSV50CPN 34RSV46PN 34RSV49RB 34RSVCE551CPN 34RSVE60CIN 34RSRVE6460IN 34RSVCE750CIN 35RSCGS4RB 35RSCS4RB 37RSRDR61VV 37RSRDR91AV 37RSRDR91VV 37RSRDR91BAV 37RSRHD11IN 37RSRHD6IN 37RSRHD7IN 37RSRHD8IN 37RSRSHD90IN 37RSRBS941IN 37RSRBS94IN 37RSRDM121IN 37RSRDM121RU 37RSRDM121RUV 37RSRBSI94IN 37RSRHI693SPIN 37RSHOC126RU 37RSRHD39PN 37RSHVM900IN
BRAND MODEL DESCRIPTION ROSIERES RVI740IN 4+3BZ INDUCTION ROSIERES RVE7420IN 5HL SS ROSIERES RVE7410IN 5HL SS ROSIERES GS40 RB 4G WH ROSIERES GS41 RB 3G1 WH ROSIERES V46 RB CER1H3 WH ROSIERES V50CPN CER1H3 BL ROSIERES V46 PN CER1H3 BL ROSIERES V49 RB CER2H2 WH ROSIERES VCE551CPN 4HL BL ROSIERES VE60CIN S 2H2 SS ROSIERES RVE6460IN 4HL SS ROSIERES VCE750CIN 5HL SS ROSIERES CGS4 RB (GS) WH ROSIERES CS4 RB (M) WH ROSIERES RDR61VV CHIM GREEN ROSIERES RDR91AV CHIM ANTIQUE ROSIERES RDR91VV CHIM GREEN ROSIERES RDR91BAV CHIM WH/ANTIQUE ROSIERES RHD1/1IN 90CH SS ROSIERES RHD6 IN WALL9 IN ROSIERES RHD7SIN WALL9 IN ROSIERES RHD8 IN WALL9 IN ROSIERES RSHD90IN SIGNATURE ROSIERES RBS94/1IN WALL INV-T Slim IN ROSIERES RBS94IN WALL INV-T Slim IN ROSIERES RDM121IN CHIM SS ROSIERES RDM121RU CHIM SS ROSIERES RDM121RUV CHIM GREEN ROSIERES RBSI94IN IS INV-T Slim IN ROSIERES RHI693SPIN IS/CARREE SS ROSIERES HOC126RU CHIM AN ROSIERES RHD39PN 90CH BL ROSIERES HVM900IN CHGL SS

New New New -1 1

RETAIL PRICE EUR MAX EUR MIN 1538,91 1231,13 857,27 685,82 932,55 746,04 340,82 272,66 376,36 301,09 717,18 573,74 717,18 573,74 717,18 573,74 788,27 630,62 859,36 687,49 859,36 687,49 788,27 630,62 1003,64 802,91 69,00 96,18 554,09 610,55 610,55 610,55 897,00 867,73 1072,64 1430,18 1576,55 1024,55 1028,73 1363,27 1258,73 1327,73 1846,27 1421,82 1043,36 968,09 1237,82 677,45 1202,27 1097,73 1097,73 1097,73 1152,09 1225,27 1097,73 55,20 76,94 443,27 488,44 488,44 488,44 717,60 694,18 858,11 1144,14 1261,24 819,64 822,98 1090,62 1006,98 1062,18 1477,02 1137,46 834,69 774,47 990,26 541,96 961,82 878,18 878,18 878,18 921,67 980,22 878,18

35 Glass Covers for Hobs

37 Built-in Cooker Hoods
ORD New ORD ORD New New New New New New

New New ORD New New 1

48 Built-in Dish Washers
48RSLVI949 ROSIERES LVI949 T/INT W45cm Class A 48RSRLI920MIN ROSIERES RLI920MIN 15PL Mirror SS 48RSRLI71AV ROSIERES RLI71AV 15PL ANTIQUE 48RSRLI71BAV ROSIERES RLI71BAV 15PL WH/ANTIQUE 48RSRLI71VV ROSIERES RLI71VV 15PL GREEN 48RSRSIC777PN ROSIERES RSIC777PN AAA BL 48RSRSIC777IN ROSIERES RSIC777IN AAA SS 48RSRLI70AV ROSIERES RLI70AV 15PL ANTIQUE DOMESTICA Ltd, reserves the right to change prices at any time.
New New New ORD ORD New New 1

Combining Software of to Practice Statistics
Why all practicing statisticians should know a spreadsheet, an SQL database, and a statistical programming language.
Christian Ritter, Institut de Statistique, UCL, Louvain la Neuve and Ritter and Danielson Consulting, Brussels
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 1

Purpose

Convince you that simultaneous proficiency in using a spreadsheet, a database, and a statistical programming language gives the practicing statistician a competitive advantage in many professional settings. Give you useful starting points
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 2
Solo: Spreadsheet (here Excel) SQL database (here MySQL) Statistical programming language (here R) What is specific about each of them? Which advantages can a practicing statistician draw from each of them? Orchestra: How can the three tools be connected? What are the advantages for the practicing statistician?
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 3
A modern spreadsheet (here Excel2003)
The spreadsheet paradigm: Visual arrangement of calculations Combination of content and display features Automatic recalculation
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 4
Live demo 1: Least squares regression and robustness
Steps: 1. Create a frame 2. Use names 3. Parameterize 4. Simulate 5. Use Excel inbuilt functions for straight line regression 6. Program it directly
Aside: Important add-ins: XYChartLabeler NameManager ExcelUtilities
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 5
Immediate Advantages for the Statistician of knowing a spreadsheet (like Excel) well
obvious: between 200 and 500 million potential customers be seen as very helpful be able to help at the clients level get to see data quickly not so obvious advantages: immediate interface design ability to show by simulation/animation ability to warn about some of the deficiencies simply do good to a large number of people
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 6

An SQL Data Base (here MySQL)
The relational database paradigm Efficient storage Enforced field (column) types Indexing Fast processing of elementary operations (extraction/summaries) Connecting (relating) tables
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 7
Live Demo2: Traffic counts on E411
Example: Traffic counts on E411 Counting station: Rosires Counting direction: Brussels Bxl
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 8

DB-Essentials

Data on server: need to connect Host name User name/credentials DB name Language: SQL (dialect). Typical statements: SHOW tables SHOW columns FROM Vitesse SELECT count(*) FROM Vitesse SELECT * FROM Debit LIMIT 0,10 SELECT typveh,Voie,Sens,avg(Vit) FROM Vitesse GROUP BY typveh,voie,sens SELECT Vitesse.typveh, Vitesse.Voie, Vitesse.Sens, avg( Vit ) , avg( Debit ) FROM Vitesse LEFT JOIN Debit USING ( Moment, voie, sens, pos, typveh ) GROUP BY typveh, voie, sens
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 9
A Statistical Programming Language (here R)
Features: Matrix language adapted for statistical use
Missing values Multiple data types
High quality graphics device Many inbuilt statistical functions Fully extensible De facto statistical reference language
Almost all new research is made into an R package Regular updates (2 per year) Free (GPL2 license) Platform independent (windows, MacOS, linux, other unix dialects)

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 10
Live Demo 3: Robust regression
Console mode: create prototype
Define data Calculate Create custom graphs Search for packages

Script mode: automate

Wrap work in functions Build applications
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 11

Live Demo 3 (code)

Console session x<-seq(-7,7,1) int<-0; slope<-1; curv<-0; sigma<-0 y<-int+slope*x+curv*x^2+rnorm(x) plot(x,y) abline(lm(y~x)) library(MASS) abline(lqs(y~x,method="lts"),col="red") Scripting
SimulFit<-function( shift=0, intercept=1, slope=1, curvature=0, sigma=1) { require(MASS) x<-seq(-7,7,1)+shift y<-intercept+slope*x+curvature*x^2+sigma*rnorm(x) plot(x,y,bty="l",xlim=c(-10,10),ylim=c(-10,10)) abline(lm(y~x),col=1) abline(lqs(y~x,method="lts"),col=2) }
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 12
Now, lets join concepts: Excel uses R
Add-in: RExcel (installed into Excel via the R-package RExcelInstaller Purpose: start an R process from Excel and communicate data and instructions between Excel and R here: add the robust fit to Excel R-function to do the lts fit from input vectors x and y:
robfit<-function(x,y){ require(MASS) dta<-data.frame(x=x,y=y) fit<-lqs(y~x,data=dta,method="lms") return(fit$coef) }
Excel worksheet function to execute robfit on the ranges xvar and yvar:
=Rapply(robfit,xvar,yvar)
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 13
Now Excel uses the MySQL Database
Step 1: Configure an ODBC data source (requires MyODBC driver) configure a MyODBC source using odbcad32.exe
name: Voitures server: iris.stat.ucl.ac.be user: AtelierUser password: Cars08 database: Voitures

Step 2: Access from Excel (via Data/Import External Data/New Database Query) Technique 1: use simplified Query Wizard to retrieve one table
example: extract the table Vitesse (youll see that it has too many rows)
Technique 2: Pivot table from query. Technique 3: use an SQL statement in MSQUERY (no Query Wizard)
example: SELECT Vitesse.typveh, Vitesse.Voie, Vitesse.Sens, avg( Vit ) , avg( Debit ) FROM Vitesse LEFT JOIN Debit USING ( Moment, voie, sens, pos, typveh ) GROUP BY typveh, voie, sens
Technique 4: use MSQUERY to build a query (this can even be a parameter query)
example: use both tables Debit and Vitesse, turn off auto extraction choose hour, joursem, voie, sens, pos, typveh, vit, debit add criteria as Like [something], return to excel set parameters from cells
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 14
The Example: E411 around LLN in Summer 2001

Hourly traffic:

Rosires and St. Trond

Two tables:

Debit: count per lane and vehicle type (VOIT,CAMI) Vitesse: Average velocity per lane and vehicle type
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 15
Looking at data tables in Excel
n 9361 moment heure joursem 2001-06-01 00:00:5VE 2001-06-01 00:00:5VE 2001-06-01 00:00:5VE 2001-06-01 01:00:5VE 2001-06-01 01:00:5VE 2001-06-01 01:00:5VE 2001-06-01 02:00:5VE 2001-06-01 02:00:5VE 2001-06-01 02:00:5VE 2001-06-01 03:00:5VE 2001-06-01 03:00:5VE 2001-06-01 03:00:5VE 2001-06-01 04:00:5VE 2001-06-01 04:00:5VE 2001-06-01 04:00:5VE 2001-06-01 05:00:5VE 2001-06-01 05:00:5VE 2001-06-01 05:00:5VE 2001-06-01 06:00:5VE 2001-06-01 06:00:5VE 2001-06-01 06:00:5VE 2001-06-01 07:00:5VE 2001-06-01 07:00:5VE voie pos 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres 3 Rosieres 1 Rosieres 2 Rosieres sens Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Bxl Debit typveh VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT VOIT

Tools Pivot table Advanced filter Matrix formulae
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 16
Example/Excel: Pivot table/chart
vehicletype month VOIT 6 weekday 1 hour Grand Total 1LU 2MA 3ME 4JE 5VE 6SA 7DI vehicletype VOIT month Average of Count 522 554

Average of Count lane

2 1LU 2MA 322 hour 900

3ME 849

4JE 941
5VE 6SA 3 - 7DI 3 - 6SA 2985VE107 315334JE 242 3318163ME408 315752MA- 1065 14561LU 2 - 7DI 13246SA 1096 212205VE 2 - 1065 12384JE 2 - - 1093 12053ME 2 - 1123 12322MA 213531LU - 7DI 14306SA 1 - 1165 15105VE 1 - 1349 15684JE 1 - - 1334 13913ME 19972MA- 1LU 966 752

7DI 763

3 1LU 2MA 3ME 1412 1374lane 962weekday 1220 2000-1750-1500-1250-1000-750-500-250-0-573 551

4JE 599

Grand Total 5VE 6SA 7DI 403 623
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 17
Example/Excel: Advanced Filtering
Fast data extraction Must be recorded as macro

Criteria joursem

Extractrange joursem Debit 5VE 5VE 5VE 5VE 5VE 5VE 5VE 5VE 5VE 5VE 5VE 5VE
moment 2001-06-01 00:00:00 2001-06-01 00:00:00 2001-06-01 00:00:00 2001-06-01 00:00:00 2001-06-01 00:00:00 2001-06-01 00:00:00 2001-06-01 01:00:00 2001-06-01 01:00:00 2001-06-01 01:00:00 2001-06-01 01:00:00 2001-06-01 01:00:00 2001-06-01 01:00:00
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 18
Example/Excel: Array formulae
Pivot tables by hand Quite powerful Can be slow Array formulas have to be entered by pressing CTRL-SHIFT-Enter
colonne hour vehicletype count weekday vehicle type VOIT 1LU 2MA 3ME 4JE 5VE 6SA 7DI 0 166.6 120.1 148.0 144.2 159.1 273.8 366.41.1 40.3 39.2 38.2 48.8 80.7 132.903.6 941.4 911.0 874.9 978.4 180.8 121.1140.5 1402.9 1243.8 1198.0 1360.6 266.1 154.638.2 691.3 696.4 676.8 712.8 701.7 616.768.5 763.8 714.0 756.1 908.9 753.0 827.996.4 1031.5 1028.1 1056.6 1053.2 1086.5 1293.418.0 417.1 414.7 439.3 481.7 526.5 856.5

=AVERAGE(IF((INDEX(Voitures,,$B$5)=$A17)+(INDEX(Voitures,,$B$3)=$B$7)+(INDEX(Voitures,,$B$2)=B$8)=3,INDEX(Voitures,,$B$4),FALSE))
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 19

(Relational) Data Bases

General
Large amounts of data Server based data keeping, (remote) client based extraction and work Facilities for finding data in large piles Facilities for extracting, sorting, summarizing data Usually: implementation of a dialect of Sequential Query Language (SQL)

Incarnations:

Jet-DB-engine+ODBC (MSQuery, single user, use of various data file formats). MS-Access: Client to Jet-DB-engine (for prototyping SQL server apps) MS SQLserver (multi user, extensive language, somewhat incompatible with other SQL dialects) Oracle (made for large applications) SAP/MySQL (made for enterprise information systems) MySQL (many web services run on it, double licensing model incl. GPL) Postgresql (very flexible and advanced, mostly Unix free?) Others: Paradox, Dbase, FoxPro, Filemaker, etc.

Here MySQL

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 20

Example in one DB: MySQL

SQL statements:
SELECT pos, voie, avg( vit ) FROM Vitesse GROUP BY pos, voie ORDER BY pos, voie LIMIT 0 , 30 SELECT Vitesse.pos, Vitesse.voie, avg( Vitesse.vit ) , avg( Debit.Debit ) FROM Vitesse LEFT JOIN Debit USING ( moment, pos, voie, typveh ) GROUP BY pos, voie ORDER BY pos, voie LIMIT 0 , 30
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 21
MySQL (Administrators view, no exercise)
Management via web interface possible (phpmyadmin)
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 22

A Query in MySQL and its Results
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 23

Data Relations

Functional for retrieving but not good for data analysis.
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks).
Setting up ODBC data sources (MS-Windows)

Exercise starts here

Key: find the odbc administration manager
In Start/Settings/Administrative tools/odbc data sources Start/Run: odbcad32.exe From the R language: shell(odbcad32.exe) From (modern) excel via: Data/Get external data/data sources/advanced/New/connection string
Pre-requisite (for MySQL): MyODBC (installed on our machines, if not installed: get from h ttp://dev.mysql.com/downloads/connector/odbc/3.51.html)T
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 25

Configuration

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 26
Connecting from R-Language (1) Install (if not present) RODBC
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 27
Connecting from R-Language (2) establish connection
> library(RODBC) > dsn<-odbcConnect("NewCar",uid="AtelierUser") > sqlTables(dsn) TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS 1 Voitures Debit TABLE MySQL table 2 Voitures Vitesse TABLE MySQL table > sqlQuery(dsn,"select Vitesse.voie,Vitesse.pos,Vitesse.sens,avg(Vitesse.Vit),avg(Debit.Debit) from Vitesse left join Debit using(moment,voie,pos,sens,typveh) group by pos,voie order by pos,voie") voie pos sens avg(Vitesse.Vit) avg(Debit.Debit) Rosieres Bxl 80.55544 300.2 Rosieres Bxl 104.34537 436.3 Rosieres Bxl 139.21236 262.4 Rosieres Namur 146.35106 226.5 Rosieres Namur 110.01958 436.6 Rosieres Namur 90.59420 358.1 StTrond Bxl 85.66513 281.2 StTrond Bxl 102.59497 309.3 StTrond Bxl 144.80923 109.4 StTrond Namur 151.24627 103.5 StTrond Namur 112.97226 298.6 StTrond Namur 93.14306 280.3063

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 28

Base a Graph on a Query

library("lattice") lset(theme=col.whitebg()) contourplot(Vit~heure*voie,data= sqlQuery(dsn,"select \ Vitesse.heure,\ Vitesse.voie,\ avg(Vitesse.Vit) as Vit,\ avg(Debit.Debit) as Debit \ from \ Vitesse left join Debit using(moment,voie,pos,sens,typveh) \ where Vitesse.typveh='VOIT'\ group by \ Vitesse.heure,\ Vitesse.voie \ order by \ Vitesse.heure,\ Vitesse.voie"), at=seq(0,250,10), labels=FALSE, cuts=25, region=TRUE, col.regions=hsv(seq(.2,1,length=25)), colorkey=list(space="top")) )
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 29

A more elaborate example

contourplot(y~heure*voie | joursem*paste(pos,typveh), data= sqlQuery(dsn, "select avg(Debit) as y, joursem, voie, pos, sens, moment, heure, typveh from Debit where moment > '2001-06-01 00:00:00' and moment < '2001-07-01 00:00:00' group by heure, joursem, voie, pos, typveh order by pos, heure, voie"), pretty=TRUE, cuts=10, region=TRUE, col.regions=hsv(seq(0.4,.9,length=12)), colorkey=list(space="top"), add=TRUE, label=FALSE, at=seq(-100,2500,200))
Notes: Enter query text (select ) as a single line or (if you have to break it, enter \ before the line break). this will create a collection of contour plots for the traffic load patterns (cars/trucks per hour) There will be a white line for lane 6 at St Trond, this is not an artefact but a series of missing values in the original data file
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 30

In Excel (1)

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 31

In Excel (2)

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 32
In Excel (3): MS-Query Interface
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 33
In Excel (4): Pivot Table
Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 34

In Excel (4)

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 35

Excel (5)

Christian Ritter, UCL, Louvain-la-Neuve, Belgium. Email: ritter@stat.ucl.ac.be or Ritter and Danielson Consulting, Brussels, Belgium. Email: christian.ritter@ridaco.be Combining Software to Practice Statistics, Presentation at Atelier IS-UCL 03 October 2008 (based on earlier talks). 36