# Cas d'usage d'utilisations de données en data-visualisation
## Sommaire
- [Introduction](#introduction)
- [Pré-requis](#1-pré-requis)
- [Configuration de docker-compose](#a-configuration-de-docker-compose)
- [Lancement des instances](#b-lancement-des-instances)
- [Insertion des données en python](#2-insertion-des-données-en-python)
- [Schéma de données cible](#a-schéma-de-données-cible)
- [Chargement et transformation des données](#b-chargement-et-transformation-des-données)
- [Création de la base de données et de la structure SQL](#c-création-de-la-base-de-données-et-de-la-structure-sql)
- [Insertion des données dans la base de données](#d-insertion-des-données-dans-la-base-de-données)
- [Visualisation des données avec Metabase](#3-visualisation-des-données-avec-metabase)
## Introduction
Le but de cette documentation est de fournir une marche à suivre pour importer des données Excel dans une base de données relationnelle, puis de lancer une instance de Metabase pour générer des visualisations de données à partir de cette base.
## 1. Pré-requis
#### A. Configuration de docker-compose
Ce fichier `docker-compose.yaml` permet de définir et de configurer les services postgres et metabase-app.
Le service postgres est une instance de PostgreSQL utilisée comme base de données pour Metabase, tandis que le service metabase-app est l'application Metabase elle-même. Les deux services sont liés, ce qui signifie que metabase-app dépend de postgres et peut accéder à la base de données postgres.
`docker-compose.yaml`
```yml
version: "3"
services:
postgres:
image: postgres
environment:
POSTGRES_DB: metabase
POSTGRES_USER: benin
POSTGRES_PASSWORD: benin
volumes:
- benin-pgdata:/var/lib/postgresql/data
ports:
- "5432:5432"
metabase-app:
image: metabase/metabase
restart: always
ports:
- 3001:3000
volumes:
- /home/app/metabase-data:/metabase-data
environment:
MB_DB_TYPE: postgres
MB_DB_DBNAME: benin
MB_DB_PORT: 5432
MB_DB_USER: benin
MB_DB_PASS: benin
MB_DB_HOST: postgres
depends_on:
- postgres
links:
- postgres
volumes:
benin-pgdata:
driver: local
```
#### B. Lancement des instances
1. Installez docker-compose en utilisant la commande suivante :
```bash
sudo curl -L "https://github.com/docker/compose/releases/download/1.26.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
```
2. Assurez vous d'être dans le dossier où se trouve le fichier docker-compose.yaml, puis lancez les instances en utilisant la commande :
```bash
docker-compose up -d
```
3. Vérifiez que les instances PostgreSQL et metabase sont bien lancées en utilisant la commande :
```bash
docker container ls
```
Le résultat devrait ressembler à ceci :
```bash
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c15a88621782 metabase/metabase "/app/run_metabase.sh" 2 seconds ago Up 1 second 0.0.0.0:3001->3000/tcp, :::3001->3000/tcp benin_metabase-app_1
e6beb116d39e postgres "docker-entrypoint.s…" 2 seconds ago Up 1 second 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp benin_postgres_1
```
Maintenant, vous êtes prêt à insérer des données et à afficher des visualisations de données !
## 2. Insertion des données en python
#### A. Schéma de données cible
Le script python sera utilisé pour lire le fichier excel en entrée et transformer les données en un format relationnel conformément au schéma fourni.
Une fois les données chargées et transformées, elles pourront être utilisées pour des analyses et des filtres précis par espèce, site et géolocalisation.
#### B. Chargement et transformation des données
Nous allons utiliser python pour charger les données, puis les transformer dans le format cible.
Les paquets requis sont les suivants :
- `pandas` pour lire le fichier excel en entrée
- `utm` pour transformer les coordonnées UTM WGS 84 en latitude / longitude
- `datetime` pour générer des dates au format iso
- `sqlalchemy` pour insérer ensuite nos données dans une base de données pgsql
Executez la commande suivantes pour installer les paquets requis :
```bash
pip install sqlalchemy pandas utm psycopg2-binary
```
Le script python commencera donc par ces imports :
```py
import pandas as pd
import sqlalchemy
from utm import to_latlon, OutOfRangeError
from datetime import datetime
```
Commençons par lire les feuilles du fichier excel en entrée en utilisant `pandas`.
```py
xls = pd.ExcelFile('chemin/vers/le/fichier.xls')
logs_sheet = pd.read_excel(xls, sheet_name='Comptes', usecols=['SITECODE', 'SITENAME', 'SPECIES', 'COUNT', 'LONGITUDE', 'LATITUDE', 'DAY', 'MONTH', 'YEAR'])
sites_sheet = pd.read_excel(xls, sheet_name='Sites', usecols=['SITECODE', 'REGION', 'X', 'Y'])
logs_lines = logs_sheet.to_dict(orient='records')[1:]
sites_lines = sites_sheet.to_dict(orient='records')
```
Conformément au schéma de données, nous allons vouloir, en sortie, récupérer des tableaux contenant les données formatées. Nous commençons donc par déclarer ces tableaux.
```py
departments = []
sites = []
species = []
logs = []
```
Puisque les coordonnées ont des formats différents dans le fichier excel en entrée, nous aurons besoin d'une fonction qui transforme les coordonnées au format UMT WGS 84 en latitude / longitude. Nous pourrons utiliser cette fonction pour formater les données en sortie de manière à ce qu'elles soient utilisables dans metabase.
```py
def getCoordinates(x, y):
try:
return to_latlon(x, y, 31, 'U')
except OutOfRangeError:
return (x, y)
```
Nous allons maintenant lire le fichier excel, formater les données et les insérer dans les tableaux.
```py
for item in logs_lines:
if(item['SITECODE'] not in map(lambda site: site['code'], sites)):
siteData = next((si for si in sites_lines if si["SITECODE"].casefold() == item['SITECODE'].casefold()), {})
department = {}
if ('REGION' in siteData):
department = next((de for de in departments if de["name"] == siteData["REGION"]), {})
if (siteData['REGION'] not in map(lambda department: department['name'], departments)):
departments.append({
'id': len(departments),
'name': siteData['REGION']
})
latitude, longitude = getCoordinates(siteData['X'] if 'X' in siteData else 0, siteData['Y'] if 'Y' in siteData else 0)
sites.append({
'id': len(sites),
'name': item['SITENAME'],
'code': item['SITECODE'],
'lat': latitude,
'lng': longitude,
'department_position': department["id"] if 'id' in department else None,
})
if(item['SPECIES'] not in map(lambda specie: specie['name'], species)):
species.append({
'id': len(species),
'name': item['SPECIES']
})
site = next(si for si in sites if si["code"].casefold() == item['SITECODE'].casefold())
specie = next(sp for sp in species if sp["name"].casefold() == item['SPECIES'].casefold())
latitude, longitude = getCoordinates(item['LONGITUDE'], item['LATITUDE'])
logs.append({
'id': len(logs),
'site_position': site['id'],
'specie_position': specie['id'],
'count': item['COUNT'],
'lat': latitude,
'lng': longitude,
'date': datetime(int(item['YEAR']), int(item['MONTH']), int(item['DAY'])),
})
```
Nos tableaux `departments`, `sites`, `species` et `logs` sont maintenant remplis de données formatées. Nous pouvons maintenant utiliser `sqlalchemy` pour insérer ces données dans notre base de données postgresql
### C. Création de la base de données et de la structure SQL
Commençons par se connecter à l'instance postgreSQL avec `sqlalchemy`.
```py
engine = sqlalchemy.create_engine('postgresql://benin:benin@localhost:5432')
```
Nous pouvons ensuite créer la base de donnée `birds` et se connecter à celle-ci.
```py
with engine.connect() as connection:
connection.execution_options(isolation_level="AUTOCOMMIT").execute('CREATE DATABASE birds')
engine = sqlalchemy.create_engine('postgresql://benin:benin@localhost:5432/birds')
```
Enfin, créons la structure de donnée adaptée à notre schéma cible.
```py
with engine.connect() as connection:
connection.execute(
'''
CREATE TABLE species (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
)
'''
)
connection.execute(
'''
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
)
'''
)
connection.execute(
'''
CREATE TABLE sites (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
code VARCHAR(255) NOT NULL,
lat NUMERIC,
lng NUMERIC,
department_id INTEGER REFERENCES departments(id)
)
'''
)
connection.execute(
'''
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
site_id INTEGER REFERENCES sites(id),
specie_id INTEGER REFERENCES species(id),
count INTEGER,
lat NUMERIC,
lng NUMERIC,
date DATE
)
'''
)
```
#### D. Insertion des données dans la base de données
Pour insérer les données dans la base de données, nous devons commencer par insérer les données des `departments`. Nous devrons ensuite utiliser les identifiants des `departments` insérés pour remplir les clés étrangères `department_id` des `sites`. Nous pourrons ensuite insérer les données des `sites` et des `species`, en utilisant les identifiants générés pour remplir les clés étrangères `site_id` et `species_id` des `logs`. Cela nous permettra de respecter les contraintes de clés étrangères du modèle de données.
```py
with engine.connect() as connection:
for specie in species:
cursor = connection.execute(
'INSERT INTO species (name) VALUES (%(name)s) RETURNING id',
name=specie['name']
)
row = cursor.fetchone()
specie['id'] = row['id']
# Insert the departments data into the departments table and get the IDs of the newly-inserted rows
for department in departments:
cursor = connection.execute(
'INSERT INTO departments (name) VALUES (%(name)s) RETURNING id',
name=department['name']
)
row = cursor.fetchone()
department['id'] = row['id']
# Insert the sites data into the sites table and get the IDs of the newly-inserted rows
for site in sites:
cursor = connection.execute(
'INSERT INTO sites (name, code, lat, lng, department_id) VALUES (%(name)s, %(code)s, %(lat)s, %(lng)s, %(department_id)s) RETURNING id',
name=site['name'],
code=site['code'],
lat=site['lat'],
lng=site['lng'],
department_id=departments[site['department_position']]['id'] if site['department_position'] is not None else None
)
row = cursor.fetchone()
site['id'] = row['id']
# Insert the logs data into the logs table and get the IDs of the newly-inserted rows
for log in logs:
connection.execute(
'INSERT INTO logs (site_id, specie_id, count, lat, lng, date) VALUES (%(site_id)s, %(specie_id)s, %(count)s, %(lat)s, %(lng)s, %(date)s) RETURNING id',
site_id=sites[log['site_position']]['id'] if log['site_position'] is not None else None,
specie_id=species[log['specie_position']]['id'] if log['specie_position'] is not None else None,
count=log['count'],
lat=log['lat'],
lng=log['lng'],
date=log['date'].isoformat()
)
```
**Youpi!** Les données sont insérées dans la base de données!
Nous pouvons maintenant fermer notre connexion à la base de données, et passer à la partie visualisation des données. Nous allons pouvoir utiliser des outils de visualisation pour représenter les données sous forme de graphiques et de tableaux, ce qui nous permettra de mieux comprendre les données et de découvrir des tendances intéressantes.
```py
connection.close()
```
## 3. Visualisation des données avec Metabase
Votre instance metabase est accessible à l'adresse `http://localhost:3001/`. Si ce n'est pas le cas, merci de retourner à [la première étape](#1-pré-requis).
### A. Configuration de metabase
Pour configurer votre compte Metabase administrateur, suivez ces étapes :
1. Choisissez votre langue préférée pour l'interface.
2. Renseignez les informations nécessaires pour votre compte administrateur, notamment votre adresse email et un mot de passe.
3. Connectez-vous à votre base de données PostgreSQL en indiquant les informations suivantes :
- Type de base de données : PostgreSQL
- Nom : Birds (ou un autre nom de votre choix)
- Hôte : benin (nom de l'instance postgresql docker)
- Port : 5432
- Nom de la base de données : birds
- Nom d'utilisateur : benin
- Mot de passe : benin
### B. Création d'une carte de chaleur
Nous allons maintenant créer une carte de chaleur à partir des données de notre base. Pour ce faire, cliquez en haut à droite sur "Nouveau" puis sur "Question".
Vous êtes maintenant dans l'interface de création de question de Metabase, qui vous permet de créer des requêtes SQL sans écrire de code.
Pour créer une carte de chaleur des zones de présence des oiseaux observés, sélectionnez d'abord la base "Birds" et la table "Logs", qui contient les lignes d'observation avec les coordonnées
Pour regrouper les données par zones, nous allons utiliser la section "Résumer" de la carte de chaleur metabase.
Pour ce faire, cliquons sur "Choisissez une colonne d'aggrégation", puis sélectionnons "Lat" dans la structure "Logs". Nous sélectionnons ensuite "Regroupement automatique" et choisissons "0.1 degrés" comme règle de groupement pour les coordonnées.
Nous répétons ces étapes pour la longitude. Les données seront alors regroupées par zones proches en termes de latitude et de longitude.
Il ne nous reste plus qu'à choisir la métrique que nous souhaitons utiliser. Dans notre cas, nous allons sélectionner la somme de la colonne "Count" à chaque fois, étant donné que chaque ligne d'observation correspond à un nombre fini d'oiseaux.
Pour visualiser les données, cliquez sur "Visualiser". Metabase devrait comprendre automatiquement que vous souhaitez afficher les données sous forme de carte de chaleur. Si ce n'est pas le cas et que vous voyez une autre visualisation, vous pouvez cliquer sur "Visualisation" en bas à gauche pour changer le type d'affichage. Une fois que vous avez choisi la carte de chaleur, vous pouvez la voir en cliquant à nouveau sur "Visualiser".
Nous avons réussi à créer une carte de chaleur des oiseaux de la région en quelques clics. Nous espérons que cette carte vous sera utile. Bonne découverte !
Sign in with Wallet
Connect another wallet