27 febbraio 2008

Usare R in Access

L'utilizzo di R in Access mi ha permesso di sviluppare modelli statistici di notevole complessità in maniera abbastanza semplice, utilizzando un software diffusissimo quale appunto Access. Quest'ultimo lo uso in genere come interfaccia utente per "nascondere" un motore di calcolo statistico come R e, in alcuni casi, un motore database diverso da Jet (come ad esempio Firebird, in riferimento al quale ho scritto questo post). In pratica mi è capitato di utilizzare Access per accedere ad un database Firebird via odbc, crearmi delle tabelle temporanee da passare ad R, applicare il mio modello statistico (in questo caso direi di data mining) e visualizzare i risultati finali tramite tabelle create in Jet. In tal modo, quindi, Access funge esclusivamente da interfaccia, sfruttando quindi la possibilità di gestire i dati tramite vba e mediante un modello di riferimento molto semplice ed allo stesso tempo molto efficace.
Nell'esempio che riporto di seguito ho preferito fare riferimento ad una tabella generata tramite Jet e, quindi, ad un utilizzo intensivo di Access stesso. Per un utilizzo di quest'ultimo con un motore db differente (quale, ad esempio, Firebird), l'esempio di seguito riportato penso rimanga valido con opportuni accorgimenti legati a tabelle collegate tramite odbc.
La possibilità di richiamare R da Access, analogamente a quanto avviene per Excel così come spiegato qui, resta legata all'utilizzo di R(D)COM. Quindi, sul proprio calcolatore sarà necessario disporre di R, R(D)COM ed ovviamente Access (io lavoro con la versione del 2003). Prima di cominciare a scrivere il codice sarà necessario apire l'editor di Visual Basic (ALT+F11) > Strumenti > Riferimenti e spuntare le librerie StatConnectorClnt 1.0 Type library, StatConnectorCommon 1.1 Type library e StatConnectorSrv 1.1 Type library. Nel caso queste non fossero disponibili nell'elenco, saranno disponibili al seguente indirizzo: C:\Programmi\R\(D)COM Server\tlb con estensione .tlb. Ovviamente non le troverete se avrete installato R(D)COM in un percorso differente da quello standard, ed in tal caso ve le andrete a cercare :-)
Nell'esempio dispongo di una tabella in Access così composta:
tbPrezzi (mese, prezzoGpl, prezzoBenz)
ossia una tabella (tbPrezzi) con i prezzi industriali di gpl e benzina per i 12 mesi del 2007 . Si tratta di dati ufficiali presi dal sito del ministero.
In maniera molto semplice ho immaginato che l'utente sia interessato al coefficiente angolare della retta di regressione lineare del prezzo del gpl sul prezzo della benzina.
In R basterebbe utilizzare il seguente comando: >lm(y~x)$coeff[2]
con y ed x pari appunto ai due campi della tabella tbPrezzi, ossia prezzoGpl e prezzoBenz rispettivamente in luogo di y ed x. Quindi ho costruito una maschera che contiene un pulsante di comando al cui click si generano le seguenti operazioni (riportate passo per passo nel codice sotto):
  1. creo un recordset in vba coincidente con la tabella tbPrezzi e due vettori x ed y pari rispettivamente a prezzoGpl e prezzoBenz;
  2. avvio R dietro le quinte;
  3. passo questi vettori ad R;
  4. eseguo il calcolo del coefficiente b della retta di regressione e riporto il risultato del calcolo in Access;
  5. decido di visualizzare b tramite un messaggio a video (MsgBox), ma equivalentemente avrei potuto continuare ad usarlo in vba ed in Access per scopi differenti;
  6. chiudo R.
Ovviamente il mio esempio può essere generalizzato a modelli più complessi, come ad esempio l'applicazione di un modello CART generato in R (mediante la libreria tree) sulla base di un insieme di dati provenienti da un database diverso da Jet. Si riporta di seguito il codice associato all'evento "click" per un pulsante di comando:
'***************************************************************
Private Sub Comando0_Click()
Dim stc As StatConnector
Dim x(1 To 12) As Double
Dim y(1 To 12) As Double
Dim i As Integer
Dim b As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
'*********************PASSO 1********************
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tbPrezzi")
i = 1
Do Until rs.EOF
x(i) = rs.Fields(2)
y(i) = rs.Fields(1)
rs.MoveNext
i = i + 1
Loop
'*********************PASSO 2********************
Set stc = New StatConnector
stc.Init ("R")
'*********************PASSO 3********************
stc.SetSymbol "x_R", x
stc.SetSymbol "y_R", y
'*********************PASSO 4********************
b = stc.Evaluate("lm(y_R~x_R)$coeff[2]")
'*********************PASSO 5********************
MsgBox "Coefficiente angolare regressione prezzo gpl su prezzo benzina: " & b
'*********************PASSO 6********************
stc.Close
rs.Close
db.Close
End Sub
'***************************************************************
Si noti che ho preferito parlare di x e y per vettori gestiti in vba, mentre ho parlato di x_R e y_R per gli stessi in ambiente R. Infine bisogna precisare che il dialogo tra R ed Access avviene tramite l'oggetto StatConnector. Per maggiori informazioni è sufficiente consultare il sito ufficiale di R(D)COM........ampiamente linkato nel mio blog :-)

8 commenti:

Gianni ha detto...

Salve,
volevo farle una domanda sull'utilizzo di R, ed in particolare sull'esportazione di dati presenti su un foglio Excel in R (mi perdoni quindi se commento il post sbagliato ma, non sapendo come funziona il meccanismo del blog, ho pensato di inviarle il commento sul post più recente nella speranza che possa accorgersene per prima). In particolare ho seguito tutte le sue (chiarissime) istruzioni; tuttavia sto letteralmente impazzendo nel tentativo di importare in R delle date in formato mese-anno, dove mese è un'abbreviazione (ad esempio "giu") e dove anno si riferisce solo alle ultime due cifre (quindi ad esempio "04"); in pratica un esempio di data su cui sto lavorando è giu-04. La tabella che ho in Excel è quindi estremamente semplice dato che è composta solo da 2 colonne e una sessantina di osservazioni. Giunto a questo punto, il mio problema riguarda proprio la conversione del mio formato Data, nel formato riconosciuto da R. A tal proposito ho provato ad re-impiegare la sua risposta che già ha fornito ad un altra persona facendo come segue: se il comando x[,3]<-as.Date(x[,3], format="%d/%m/%y") viene utilizzato per convertire date nel formato gg/mm/aaaa, ho pensato che con l'analogo comando x[,1]<-as.Date(x[,1], format="%b-%y") potesse convertirmi in un formato comprensibile a R, la data del tipo giu-04 che si riferisce al mio file.
Ecco, colgo l'occasione per ringraziarla, e per chiederle se può consigliarmi una valida guida, in italiano se esiste, per la realizzazione di grafici tramite R partendo da oggetti creati con il comando read.table (quindi non solo a partire da vettori immessi manualmente dato che ho necessità di lavorare su grandi dataset..).
Grazie ancora, saluti.
Gianni

michele de meo ha detto...

Prima di tutto, ti consiglio di postare e/o cercare sul newsgroup di R.
Poi direi che il formato "mese - anno" non è propriamente un formato data che, appunto, comprende anche il giorno!!!! Penso sia questo il motivo per cui R non riconosce i dati. Per lavorare con le date in R è sempre consigliabile farlo con il formato "%Y-%m-%d". E poi scusami non capisco quali siano le tue necessità di avere la data in formato "m-a". Se è solo una questione di "estetica", puoi tranquillamente lavorare con un formato Factor, altrimenti se hai necessità di fare operazioni sulle date ti conviene lavorare, appunto, anche con i giorni. Pensandoci, infatti, anche in Excel il formato che usi tu è solo "estetico", infatti il dato è "registrato" come data (impostando il primo del mese se non specifichi il giorno).
Per quanto riguarda un manuale per la produzione di grafici, direi che in italiano non ne conosco, ma c'è un bel capitolo nel volume di Iacus-Masarotto, Laboratorio di statistica in R (il capitolo si chiama "Produrre grafici meravigliosi!", il volume si compra in libreria :-). Di materiale in inglese ne trovi parecchio, basta cercare sul sito di R o su google (parti, ad esempio, da qui ).

Gianni ha detto...

Il formato che ho utilizzato è quello che "rilascia" finance.yahoo.com quando vengono richiesti i dati storici relativamente ad un indice o un titolo su base mensile; in questo modo si ha ad esempio ago-03 che si riferisce quindi al mese di agosto dell'anno 2003. Importando poi in Excel c'è la possibilità di scegliere come formato quello sopraccitato (da formato cella -> numero). Infine, già possiedo il libro che mi ha consigliato, come pure una serie di documenti in italiano ed inglese sull'utilizzo di R; tuttavia, non ho trovato da nessuna parte (se non appunto nel libro di Iacus e Masarotto) degli approfondimenti chiari (per carità, il giudizio è assolutamente soggettivo!).
In ogni caso ho seguito la procedura che lei ha proposto utilizzando dei dati giornalieri ottenendo, senza alcun problema, i risultati sperati (ecco devo solo fare un pò di pratica su comando par).
Comunque la ringrazio molto per la risposta; dato che d'ora in poi dovrò impiegare R, spero che potrò ottenere nuovamente qualche prezioso consiglio attraverso il suo blog (chiaramente facendo più attenzione a dove andrò a postare i commenti)!
Grazie ancora, saluti

Anonimo ha detto...

Ciao, ho trovato molto interessante il tuo articolo e ho iniziato a lavorare su access utilizzando R per analizzare una grossa quantità di dati. Purtroppo mi sono ancorato sul fatto che non riesco a specificare alcuni parametri delle funzioni che contengono "". Ti faccio un banale esempio:

acf(data.mp3xy$output,lag=50,type="correlation",main="Andamento Autocorrelazioni Dati Principali",ylab="Coefficienti di Autocorrelazione")

Ovviamente il comando in R non da nessun problema ma nel codice visual basic (alt+F11) mi da errore di sintassi riconoscendo ovviamente le "" come codice. Saresti cosi gentile da darmi un aiuto.
Ti ringrazio in anticipo.
Distinti Saluti
Mattia

michele de meo ha detto...

Prova con ' invece di "

Pino M ha detto...

ciao,
ottimi i tuoi post, volevo chiederti se potevi fare un post anche sull'integrazione tra R e mysql che è il db (open source) che utilizzo più di frequente. grazie

michele de meo ha detto...

Ciao pino,
mi fa piacere che questo post ti sia stato utile.
Per quanto riguarda l'integrazione in MySql la "logica" è sostanzialmente differente da access.
Non mi risulta possibile gestire R in un db in mysql (magari qualcuno mi smentisce), ma dovresti importare via odbc in R il tuo dataset. Per l'uso integrato di R in un db, ti consiglerei di lavorare con postgres. Esistono un paio di progetti interessanti:
questo e quest'altro.
Comunque presto (spero) vorrei scrivere qualcosa sull'integrazione di R nei db.

Pino M ha detto...

grazie Michele, aspetterò con piacere questo interessantissimo post.