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 :-)