STAT2430 - Calcul statistique sur ordinateur

STAT2430 - Calcul statistique sur ordinateur
SÉANCE 1 - Manipulations de données sous Excel, SPSS et JMP
Objectifs de la séance
Utilisation d'Excel
Importer des données, nettoyage et mise en forme.
Formules pour des calculs simples (création / recodage de variables)
Création de graphiques. Amélioration de graphiques existants.
Assistant statistique.
Exportation des données dans un format lisible par d'autres logiciels.
Utilisation de JMP et SPSS
Importer des données. Utiliser les fonctionnalités des logiciels.
Réaliser quelques graphiques et statistiques de base.
Récupérer les sorties de logiciels pour préparer un rapport dans Word.
Présentation du contexte
Début 2001, une compagnie de jeux et jouets a effectué une campagne de publicité en Belgique, en
distribuant de nombreux bons de réduction dans les boîtes aux lettres. Le directeur des ventes aimerait avoir
une idée des résultats de cette campagne publicitaire et plus précisément sur la relation entre le montant
alloué à la publicité et le chiffre d'affaire réalisé, cela dans les trois régions de la Belgique: Wallonie, Flandres
et Bruxelles.
Pour cela, les données de quelques unes des filiales du groupe ont été récoltées et permettent de reconstituer
les trois variables suivantes:
Region: la région de Belgique où la filiale est installée.
Pub: la somme dépensée par la filiale pour la publicité locale.
Ventes: les ventes réalisées au premier semestre 2001.
Vous disposez de deux jeux de données:
• le fichier pub.dsv reprend les données pour les régions Bruxelles et Wallonie, respectivement codées
par 1 et 2. Note: l'extension dsv signifie "dollar separated values": le format est délimité, avec le
symbole $ comme séparateur. Les budgets en publicité (Pub) sont exprimés en Keuros. Les ventes
sont exprimées en Keuros pour Bruxelles, et en KBEF pour la Wallonie.
•
Le fichier flandres.xls reprend les données pour la Flandre. Y sont recensés le budget publicitaire, les
nombres d'articles vendus, et le prix unitaire de chaque article (en Keuros). Par une simple
multiplication, on peut donc connaître les ventes.
Les fichiers de données se trouvent sur le site web du cours STAT2430. Téléchargez les fichiers sur votre
disque en cliquant sur le bouton droit de la souris et sélectionnant « save target as ».
STAT2430 – Exercices Excel, SPSS, JMP
page 1/14
Travail demandé
Un rapport en Word doit être préparé pour répondre à la question du directeur des ventes par quelques
analyses statistiques et quelques graphiques. Le travail est découpé en plusieurs étapes précisées ci-dessous
qui respectent la méthodologie introduite au cours.
Afin que vous puissiez étudier les logiciels, le même travail est demandé sous Excel et un logiciel de statistique
générique (au choix SPSS ou JMP). Les manipulations à faire sont détaillées.
A titre indicatif, un rapport déjà préparé est joint en annexe. Votre travail doit comporter tous les éléments
inclus dans ce rapport, avec une mise en forme la plus proche possible pour les graphiques et tableaux.
Préparation des données sous Excel
Vous devez préparer 1 fichier qui reprend toutes les données et qui sera importé dans les autres logiciels (SPSS
ou JMP) afin de réaliser les analyses.
1. Ouvrir le fichier pub.dsv avec le Notepad ou Textpad pour visualiser sa structure puis le refermer.
2. Ouvrir les deux jeux de données dans Excel. Pour le fichier .dsv, utiliser l'assistant d'importation
(File…Open), qui permet d'importer tous les fichiers avec séparateurs. (Attention à ne pas choisir la virgule
comme séparateur des milliers.)
3. Mettre les deux jeux de données dans un format commun (et dans 1 fichier commun). Pour cela, les
manipulations suivantes sont à effectuer:
• Calculer le total des ventes dans le fichier flandre.xls
• Unifier le nom des variables
• Unifier les unités (tout exprimer en Keuros: 1 euro=40,3399 BEF). Créer pour cela une nouvelle
variable (appelée "ventes(Keuros)") grâce à la fonction "if" + étirer la formule sur toutes les lignes.
• Rendre cohérents les labels des variables
• S'assurer que les valeurs manquantes sont codées par une même valeur (une cellule vide car Excel ne
traite par les « . »).
• S'assurer que les mêmes séparateurs décimaux sont utilisés
• Si des valeurs sont clairement erronées, les remplacer soit par une valeur qui semble plus appropriée
soit par une valeur manquante. (Par exemple, les valeurs de ventes sont toujours plus élevées pour la
région 2 car elles sont exprimées en KBEF au lieu de Keuros.)
4. Visualiser rapidement la relation entre la publicité et les ventes dans Excel par un graphique pour repérer
d’éventuelles valeurs aberrantes. S’il y en a, les remplacer par des valeurs manquantes.
5. Une fois toutes ces étapes terminées, enregistrer, dans un répertoire que vous consacrerez au projet,
l'ensemble des données dans un nouveau fichier Excel appelé pub_pretraite.xls.
6. Enfin, exporter les données dans un fichier texte, en utilisant un format générique importable dans tout
logiciel de statistique. L'extension .txt avec tabulation comme délimiteur est recommandée.
Importation des données dans SPSS et JMP
Nous allons maintenant réaliser des analyses sur le fichier " pub_pretraite".
Importer les données dans les logiciels JMP et SPSS et les sauvegarder dans le format prédéfini du logiciel
(extension .SAV en SPSS et .JMP en JMP). Utiliser les fonctionnalités disponibles dans ces logiciels pour ajouter
des labels aux variables et aux modalités (exemple: 1="Bruxelles"…) et donner des types aux variables. Ces
améliorations sont pratiques lors de la constitution du rapport et du choix des analyses statistiques.
STAT2430 – Exercices Excel, SPSS, JMP
page 2/14
Visualisation de la relation entre les ventes et la publicité
1. Représenter graphiquement (en SPSS, JMP et Excel) la relation entre les ventes et la publicité au moyen
d'un graphique XY (nuage de points, aussi appelé scatterplot). Sous SPSS et JMP, utiliser des symboles
différents pour chaque région du pays. En Excel, faire un simple graphe XY car l’utilisation de symboles
est complexe.
2. Mettre chacun des graphiques en forme (titre, label pour les axes…) et les copier dans Word (voir
exemple dans le rapport joint).
Modélisation de la relation pour la région de Bruxelles
Manifestement, pour la région de Bruxelles, il existe un lien linéaire simple entre les ventes réalisées et le
budget publicitaire alloué. On vous demande dans chaque logiciel de :
1. Sélectionner de façon adéquate uniquement les données de Bruxelles
2. Effectuer une analyse de régression pour expliquer les ventes par la publicité.
3. Mettre en forme le tableau des coefficients du modèle (réduire le nombre de décimale des chiffres si
besoin, supprimer les colonnes peu intéressantes…).
4. Exporter le tableau vers Word
5. Interpréter les résultats
Les exemples de tableaux sont donnés dans le rapport joint
Indications :
• Sous SPSS sélectionner la région de Bruxelles (Data…Select cases) et puis réaliser un scatterplot. Ensuite,
ajouter la droite de régression (éditer le graphe, puis Chart…Options) et l'intervalle de confiance
(Chart…Options…Fit Options…Regression Prediction Line).
• Sous JMP, sélectionner les données par : rows…row selection…select pour sélectionner les lignes ≠1 puis
rows…exclude pour les exclure de l’analyse. Ensuite, lancer la régression par Analyze…Fit Y by X puis Fit
Line.
• Sous Excel, vous utiliserez l'assistant statistique (menu Tools… Data analysis) en n'utilisant que les
données de la région de Bruxelles. Pour cela, il est indispensable de commencer par trier l'ensemble des
données selon la région (menu Data… Sort).
Analyse du ratio (Ventes après pub - Ventes avant pub)/Pub pour les 3 régions
1. Créer la variable ratio =( Ventes après pub - Ventes avant pub)/pub. Vous pouvez soit le faire dans Excel
avec une formule, et copier/coller la colonne dans les autres logiciels soit le faire dans chaque logiciel mais
c’est plus long (SPSS: menu Transform… Compute, JMP : cols…new cols puis Formula après avoir
sélectionné la nouvelle colonne).
2. Créer un graphique permettant d'avoir un aperçu de la distribution de cette variable pour chaque région.
Lorsque disponible (SPSS et JMP), le graphique le mieux adapté est certainement le boxplot (dans SPSS :
Graph…Box-Plot, dans JMP Analyze…Fit Y by X puis, face au graphe X-Y, Diplay options…Box-plots) .
Sous Excel, on fera un graphique XY (scatterplot). Mettre ces graphiques en forme et les exporter dans
Word.
3. Enfin, pour comparer les valeurs de la variable RATIO selon les trois régions, l'outil statistique approprié
est l'analyse de la variance à 1 critère (ANOVA). Mettre en œuvre cette analyse de variance. Sous SPSS,
dans le menu Compare means… one Way ANOVA. Sous JMP, Analyze…Fit Y by X. Mettez en forme
puis recopier dans Word les tableaux des moyennes et tableau ANOVA obtenus avec les trois logiciels.
Sous Excel, utiliser l'assistant statistique (Tools… Data analysis, attention, il faut mettre les ratios des 3
régions en trois colonnes).
STAT2430 – Exercices Excel, SPSS, JMP
page 3/14
Exercice Spss supplémentaire
Cet exercice a pour but d'apprendre à jouer avec les options de mise en page de tableaux et de graphiques en
Spss. Vous trouverez dans le rapport deux tableaux et un graphe que vous devez être capable de réaliser
identiquement (au détail près!) en Spss.
Vous utiliserez pour cet exercice le fichier rongeur.sav. Ce fichier contient les renseignements concernant la
capture de rongeurs dans une portion de forêt. On y trouve:
• la variable season qui vaut "s" si l'animal a été capturé en été et "w" s'il a été capturé en hiver.
• la variable species qui donne l'espèce de l'animal. Il y a 2 espèces : les "Cg" et les "Ma".
• La variable sex qui vaut F si l'animal est une femelle et M si l'animal est un mâle.
• La variable body_mas qui donne le poids de l'animal en mg.
Les étapes à effectuer sont les suivantes:
1. Ouvrir le fichier rongeur.sav dans Spss.
2. Ajouter les labels suivants:
• season : saison – s=été et w=hiver
• species : espèce – Cg = espèce 1 et Ma = espèce 2
• sex : sex – F = femelles et M = mâles
Indications : travailler dans le "variable view". Définir d'abord "label" puis "values".
3. Créer la première table du rapport.
Indications : Utiliser le menu Analyse…Custom Table.
Il faut utiliser 2 statistiques (Count et Table%).
Ne pas oublier le titre ni la mention "cours stat2430 – séance 1" en bas de la table (option caption).
4. Modifier la première table afin d'obtenir la deuxième.
Indications : Il suffit de cliquer sur le tableau pour l'éditer. Modifier le nom des colonnes puis en
changer la longueur afin que le texte s'affiche de la même façon que dans le rapport.
5. Réaliser les boxplots se trouvant dans le rapport.
Indications : utiliser Graphs…Interactive. Une fois le graphe réalisé, il faut encore:
• Changer le type d’hachurage des boîtes
• Ajouter un titre
• Changer le titre de l'axe vertical ("body_mas" devient "poids (mg)")
• Changer les types de "moustaches" des boxplots
• Changer le type de marqueur des outliers (des ronds vide en ronds pleins)
STAT2430 – Exercices Excel, SPSS, JMP
page 4/14
RAPPORT 2003/09 - IS - 1
Résultats de la campagne publicitaire en Belgique
Visualisation de la relation (graphiques)
En global, pour toute la Belgique (Excel)
Relation entre budget publicitaire et ventes
2500
Ventes (en Keuros)
2000
Valeur aberrante
1500
1000
500
0
0
10
20
30
40
50
60
Budget publicitaure (en Keuros)
Le graphique permet de mettre en évidence une observation aberrante (budget publicitaire: 17,1 Keuros, CA:
2258 Keuros, localisé en Wallonie). Pour la suite, cette observation est retirée.
Finalement, on obtient :
Graphe des ventes en fonction de la publicité
500
Ventes (KEuros)
400
300
200
100
0
0
10
20
30
40
50
60
Publicité (KEuros)
STAT2430 – Exercices Excel, SPSS, JMP
page 5/14
Graphique Spss :
Relation entre budget publicitaire et ventes
500
400
300
Région
VENTES
200
Flandres
Wallonie
Bruxelles
100
0
10
20
30
40
50
60
PUB
Chaque région dispose de son symbole
Graphique JMP :
Bivariate Fit of ventes_n By PUB
400
350
Ventes
300
250
200
150
100
0
STAT2430 – Exercices Excel, SPSS, JMP
10
20
30
Publicité
40
50
page 6/14
Modélisation de la régression pour la région de Bruxelles
Sous SPSS
Coefficientsa
Model
1
Standardi
zed
Coefficie
nts
Beta
Unstandardized
Coefficients
B
Std. Error
223,230
12,851
5,742
,594
(Constant)
PUB
t
17,371
9,666
,924
Sig.
,000
,000
95% Confidence Interval for B
Lower Bound Upper Bound
195,988
250,472
4,482
7,001
a. Dependent Variable: VENTES
Linear Regression with
95,00% Mean Prediction Interval
ventes = 223,23 + 5,74 * pub
R-Square = 0,85
500,00
ventes
A
A
400,00
A
A
A
A
A
300,00
AA
A
200,00
A
A
A
A A
A
A
A
0,00
10,00
20,00
30,00
40,00
50,00
pub
Sous JMP
Parameter Estimates
Term
Intercept
PUB
Estimate
223.22969
5.7416636
STAT2430 – Exercices Excel, SPSS, JMP
Std Error
12.85062
0.593986
t Ratio
17.37
9.67
Prob>|t|
<.0001
<.0001
page 7/14
Sous Excel
PUB Line Fit Plot
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
500
450
400
350
0,92
0,85
0,84
26,58
18
VENTES
300
250
200
150
VENTES
100
Predicted VENTES
50
0
0
5
10
15
20
PUB
25
30
35
Coefficients Standard Error
223,2
12,9
5,7
0,6
Intercept
PUB
40
t Stat
17,4
9,7
P-value
0,0
0,0
Lower 95%
196,0
4,5
Upper 95%
250,5
7,0
Analyse des trois régions
Effectifs par région
Count of REGION
REGION
Total
1 18
2 29
3 30
(blank)
Grand Total
77
région
Valid
Bruxelles
Wallonie
Flandres
Total
Frequency
18
30
30
Percent
23,1
38,5
38,5
Valid Percent
23,1
38,5
38,5
78
100,0
100,0
Cumulative
Percent
23,1
61,5
100,0
Analyse de la variable ratio (globale)
STAT2430 – Exercices Excel, SPSS, JMP
page 8/14
Ratio
Mean
6,439228
Standard Error
0,888359
Median
4,180732
Mode
#N/A
Standard Deviation 7,795322
Sample Variance 60,76704
Kurtosis
13,66867
Skewness
3,599627
Range
42,6071
Minimum
1,270833
Maximum
43,87794
Sum
495,8206
Count
77
Descriptives
RATIO
Mean
95% Confidence
Interval for Mean
Lower Bound
Upper Bound
5% Trimmed Mean
Median
Variance
Std. Deviation
Minimum
Maximum
Statistic
6,4390
4,6696
Std. Error
,88838
8,2083
5,0432
4,1800
60,770
7,79549
1,27
43,88
Range
Interquartile Range
Skewness
Kurtosis
42,61
3,4450
3,600
13,669
,274
,541
Distribution de la variable ratio dans les trois régions
STAT2430 – Exercices Excel, SPSS, JMP
page 9/14
Graphique du ratio par région en Spss (box-plot)
50
42
16
40
58
30
27
20
11
65
10
RATIO
0
-10
N=
18
Bruxelles
29
Wallonie
30
Flandres
REGION
Graphique du ratio par région en JMP (box-plot)
Graphique du ratio par région en Excel
STAT2430 – Exercices Excel, SPSS, JMP
page 10/14
Ratio pour les trois régions
50
45
valeur du ratio
40
35
30
25
20
15
10
5
0
0
0,5
1
1,5
2
2,5
3
3,5
Région
Analyse de la variance à 1 critère (ANOVA)
En Spss :
Tableau des moyennes
RATIO
N
Bruxelles
Wallonie
Flandres
Total
18
29
30
77
Mean
8,6772
6,0945
5,4290
6,4390
Std. Deviation
9,02143
8,22769
6,47342
7,79549
Std. Error
2,12637
1,52784
1,18188
,88838
Minimum
1,27
1,84
1,75
1,27
Maximum
41,04
43,88
37,04
43,88
ANOVA
RATIO
Between Groups
Within Groups
Total
Sum of
Squares
124,219
4494,273
4618,492
STAT2430 – Exercices Excel, SPSS, JMP
df
2
74
76
Mean Square
62,109
60,733
F
1,023
Sig.
,365
page 11/14
9
8
Mean of RATIO
7
6
5
Bruxelles
Wallonie
Flandres
REGION
En JMP :
Oneway Analysis of ratio By REGION
Analysis of Variance
Source
REGION
Error
C. Total
DF
2
73
75
Sum of Squares
2221.206
26560.936
28782.142
Mean Square
1110.60
363.85
F Ratio
3.0524
Prob > F
0.0533
Means for Oneway Anova
Level
Number
Mean
Bruxelles
18
27.8309
Flandre
30
16.2715
Wallonie
28
14.2345
Std Error uses a pooled estimate of error variance
Std Error
4.4960
3.4826
3.6048
Lower 95%
18.870
9.331
7.050
Upper 95%
36.791
23.212
21.419
En Excel :
SUMMARY
Groups
Bruxelles
Wallonie
Flandres
ANOVA
Source of Variation
Count
Sum
Average Variance
18 156,2053 8,678073 81,37136
29 176,7486 6,094779 67,68623
30 162,8667 5,428889 41,91308
SS
STAT2430 – Exercices Excel, SPSS, JMP
df
MS
F
P-value
F crit
page 12/14
Between Groups
Within Groups
124,288
4494,007
2 62,14399 1,023286 0,364441 3,120348
74 60,72983
Total
4618,295
76
RAPPORT 2003/09 - IS - 1
Exercice supplémentaire en Spss : capture de rongeurs
STAT2430 – Exercices Excel, SPSS, JMP
page 13/14
Table du nombre de rongeurs capturés en fonction de la saison et de
l'espèce
saison
été
espèce
espèce 1
espèce 2
Count
46
137
hiver
Table %
13.3%
39.7%
Count
71
91
Table %
20.6%
26.4%
cours stat2430 - séance 1
Table du nombre de rongeurs capturés en fonction de la saison et de l'espèce
saison
été
nbre d'individus
capturés en été
espèce espèce 1
46
espèce 2
137
cours stat2430 - séance 1
hiver
pourcentage par rapport au
total des individus capturés
13.3%
39.7%
nbre d'individus
capturés en hiver
71
91
pourcentage par rapport au
total des individus capturés
20.6%
26.4%
Box plot du poids des 2 espèces en fonction
de la saison et du sexe
sexe
S
40.00
femelles
mâles
poids (mg)
saison
30.00
W
S
W
W
W
W
W
été
hiver
W
W
W
20.00
W
10.00
espèce 1
espèce 2
espèce
STAT2430 – Exercices Excel, SPSS, JMP
page 14/14