Dans la lignée de mon article sur  R & Excel, je développe ici une méthode pour interfacer R avec une base de données MySQL. Dans un premier temps, je montrerai comment le faire sous Windows, puis sous Mac OS X dans un second temps. C’est pas que j’aime enfoncer des portes ouvertes, mais vous comprendrez qu’il est nécessaire d’avoir installé au préalable votre base de données MySQL et R sur votre machine…

Sous Windows

Mes distributions : Windows 7 Professionnel / R.2.15.1

Configuration de la connexion

Dans un premier temps, il faut installer le pilote de connexion MySQL. Si vous ne l’avez pas déjà, vous le trouverez ici. Pour vérifier s’il est bien installé il faut aller dans le panneau « Administrateur de sources de données ODBC », en empruntant le chemin suivant : Panneau de configuration > Système et sécurité > Outils d’administration >Sources de données (ODBC) > Sources de données utilisateur Une fois que c’est fait,  on crée une source de données utilisateur. Dans l’écran ci-dessous, vous pouvez voir (deuxième capture) que ma source de données s’appelle « wax ». Je l’ai créé en appuyant sur le bouton « ajouter… » et en sélectionnant le driver MySQL.

Une fois que vous avez cliqué sur « Ajouter… » cette fenêtre doit apparaître :

Il vous suffit de compléter les informations demandées. Le « Data Source Name » est celui qui apparaîtra dans la fenêtre des sources de données utilisateur (dans mon exemple, c’est wax). Je n’ai pas mis de description ni TCP/IP, mais ce n’a pas vraiment d’importance, par contre il faut renseigner le « User » et le « Password » de votre base de données. Avec le bouton, vous pourrez rapidement voir si votre connexion fonctionne ou pas.

Configuration sur R

Il existe plusieurs packages permettant d’interfacer R avec une base de données MySQL (dbConnect, RMySQL et RODBC). Pour cet article, je vais utiliser le package RODBC. C’est une librairie de fonctions permettant d’envoyer des requêtes SQL à la base de données via R, et de renvoyer le résultats des requêtes directement dans R. C’est un package intéressant, car il vous permettra de vous connecter à plusieurs type des bases de donnnées (Access, PostgreSQL, SQLserver, SQLite). Dans ce schéma, R ne fait qu’envoyer les requêtes et recevoir les résultats, ce qui veut dire que votre base de données n’interprète pas le langage R, c’est seulement R qui lui sert la requête sur un plateau. Du coup, vous pouvez reprendre toutes les requêtes SQL que vous avez l’habitude d’utiliser, que ce soit pour la lecture ou l’écriture.

Comme d’habitude, on installe RODBC grâce à la commande suivante : install.packages(‘RODBC’)

Let’s the music play !

On charge les packages dont on a besoin :
library(RODBC)
library(quantmod)

Connexion à la base de données :
Connex=odbcConnect(dsn=’Wax’, uid=’root’, pwd=’votremotdepasse’)
sqlQuery(Connex,’USE worldindex;’)

Comme vous pouvez le voir, j’affecte à la variable Connex, mes identifiants pour accéder à la base de données. La fonction sqlQuery permet d’envoyer des requêtes à la base, c’est pourquoi la deuxième partie de l’expression comporte une requête en SQL (il vous faut donc connaître le langage SQL !). Cette ligne me permet de me connecter à ma base de données « worldindex » qui contient la dernière cotation journalières pour des indices actions du monde entier.

Dans cette exemple, je vais essayer de vous montrer comment je calcul les performances mensuelles de chaque indice, à partir des données journalières stockées dans ma base de données.

Dans un premier temps, je crée une fonction qui permet de télécharger la dernière cotation, de chaque mois, pour des années données :

sqlyear<-function(x,y){
sqlQuery(Connex,paste(sprintf(‘SELECT p.id_nom, tmp.Mois, p.dates_prix, p.prix
FROM cotations AS p
INNER JOIN
(
SELECT id_nom, MONTH(dates_prix) AS Mois, MAX(dates_prix) AS Dernier_jour
FROM cotations
WHERE id_nom =’%s’AND YEAR(dates_prix) = %s
GROUP BY id_nom, MONTH(dates_prix)
) AS tmp
ON tmp.id_nom = p.id_nom
AND tmp.Dernier_jour = p.dates_prix
ORDER BY tmp.Mois;’, x,y)))
}

Grâce à la fonction sprintf, on peut créer des requêtes « évolutives » plus complexes. Ici, je passe dans la variable x l’id_nom que je désire, c’est-à-dire, l’id de l’indice actions dans ma base dont je veux récupérer les cotations, et dans y, les années que je désire. Le script suivant me permet d’appeler la fonction juste au dessus, et d’arranger les données :

MonthByYear<-function(x,year){ # year = peut-être une ou plusieurs années
year=data.frame(year)
ny=nrow(year)
ress=data.frame()
for(i in 1:ny){
res=sqlyear(x,year[i,])
ress=rbind(ress,res)
}
result=data.frame(ress)
}

Au final, voilà le résultat :
cac<-MonthByYear(1,c(2012,2013))

id_nom	Mois	dates_prix	prix
1	1	2012-01-31	3298.55
1	2	2012-02-29	3452.45
1	3	2012-03-30	3423.81
1	4	2012-04-30	3212.80
1	5	2012-05-31	3017.01
1	6	2012-06-29	3196.65
1	7	2012-07-31	3291.66
1	8	2012-08-31	3413.07
1	9	2012-09-28	3354.82
1	10	2012-10-31	3429.27
1	11	2012-11-30	3557.28
1	12	2012-12-31	3641.07
1	1	2013-01-31	3732.60
1	2	2013-02-28	3723.00
1	3	2013-03-28	3731.42
1	4	2013-04-30	3856.75
1	5	2013-05-31	3948.59
1	6	2013-06-28	3738.91
1	7	2013-07-31	3992.69
1	8	2013-08-30	3933.78
1	9	2013-09-24	4195.61

Maintenant que nous avons les dernières cotations de chaque mois, nous pouvons calculer les performances mensuelles :
YearInMonth<-Labelize.Month(cac2013[,2],cac2013[,4]) # Une fonction que je me suis fabriqué
month.cac<-apply(YearInMonth,2,na.omit(ROC))
rownames(month.cac)<-rownames(YearInMonth)

# Graphique
colorVectorMonth <- ifelse(month.cac[1:length(month.cac)] > 0, 1, 2)
bp.month<-barplot(t(month.cac), beside=TRUE, col=colorVectorMonth,border = NA,
ylim=range(na.omit(month.cac[,1]))*1.2,main=’Rendements mensuels (%)’)
text(bp.month,month.cac,labels=as.character(round(month.cac*100,2)),pos=3, cex=0.7)

Cet exemple très simple a permit de mettre en lumière, de façon succincte, le potentiel que peut offrir cette solution (R & MySQL). A partir de là, vous pouvez construire des requêtes plus ou moins complexes, au grès de vos connaissances et de vos besoins.