Table des matières

• PARTIE 2 - BASE DE DONNES RELATIONNELLE
  • A/ Travail préparatoire
  • B/ Création de la bdd et choix des clés primaires
  • Question 15 à 18 : Proposez une clé primaire pertinente pour chacune des 4 tables demandées
  • C/ Création des tables et insert des données
  • Question 19 : Ecrivez les requêtes SQL permettant de connaître :
  • 19.1 - Les 10 pays ayant le plus haut ratio disponibilité alimentaire/habitant en termes de protéines (en kg) par habitant, puis en termes de kcal par habitant.
  • 19.2 - Les 10 pays ayant le plus faible ratio disponibilité alimentaire/habitant en termes de protéines (en kg) par habitant, puis en termes de kcal par habitant.
  • 19.3 - La quantité totale (en kg) de produits perdus par pays en 2013.
  • 19.4 - Les 10 pays pour lesquels la proportion de personnes sous-alimentées est la plus forte.
  • 19.5 - Les 10 produits pour lesquels le ratio Autres utilisations/Disponibilité intérieure est le plus élevé.
  • Question 20 : pour quelques uns des produits identifiés dans cette dernière requête SQL, supposez quelles sont ces "autres utilisations" possibles
• EXPORT DES DATA

PARTIE 2 - BASE DE DONNES RELATIONNELLE

A/ Travail préparatoire

• On commence par importer les df de la partie 1

import pandas as pd
pop_m = pd.read_csv("data/data_part1_questions/pop_m.csv")
table = pd.read_csv("data/data_part1_questions/table.csv")
ss_alim = pd.read_csv("data/data_part1_questions/ss_alim.csv")

• On va préparer nos données afin de les utiliser dans une Bdd :
  • pour chaque table demandée, on réorganise le df correspondant (même nom de colonnes, conversion des unités...)

1. Table population

pop_m.head(3)

	code_zone	zone	pop_(M)	symbole
0	2	Afghanistan	30552	NaN
1	202	Afrique du Sud	52776	NaN
2	3	Albanie	3173	NaN

• On crée une copie de notre df in initial pop_m

(la fonction .copy() permet de créer un nouveau df identique et les modifications effectuées sur celui-ci n'affecteront que lui :
-> le df original ne sera pas affecté)

population = pop_m.copy()

• On modifie le nom des colonnes

population.columns = ['code_pays', 'pays', 'population', 'symbole']

• On supprime les colonnes inutiles (ici symbole)

• On modifie l'unité de population (on passe de milliers de personnes à nombre de personnes)

population = population.drop(['symbole'], axis=1)
population['population'] = population['population'] * 1000

• On filtre les données en limitant l'année à 2013

population['annee'] = 2013

• On modifie l'ordre des colonnes (pas indispensable)

population_dbb = population.reindex(columns=['pays', 'code_pays', 'annee', 'population'])
population_dbb.head()

	pays	code_pays	annee	population
0	Afghanistan	2	2013	30552000
1	Afrique du Sud	202	2013	52776000
2	Albanie	3	2013	3173000
3	Algérie	4	2013	39208000
4	Allemagne	79	2013	82727000

2. Table dispo_alim

• On fait une copie du df d'origine table

• On ne sélectionne que les colonnes qui nous intéressent

• On renomme les colonnes

• On calcule dispo_alim_tonnes, la disponibilité alimentaire en tonnes de chaque produit :
  

  -> il faut multiplier la pop par 1000 et diviser la dispo_alimqte(kg-p-an) par 1000
  ---> ca s'annule

dispo_alim = table.copy()
dispo_alim = dispo_alim[['code_pays', 'pays', 'annee', 'origine', 'code_produit', 'produit', 'dispo_alim_qte_(kg-p-an)', 'dispo_alim_(kcal-p-j)', 'dispo_proteines_qte_(g-p-j)', 'dispo_mat_grasse_(g-p-j)']]
dispo_alim.columns = ['code_pays', 'pays', 'annee', 'origin', 'code_produit', 'produit',  'dispo_alim_tonnes', 'dispo_alim_kcal', 'dispo_prot_g', 'dispo_mat_gr_g']
dispo_alim['dispo_alim_tonnes'] = table['dispo_alim_qte_(kg-p-an)'] * table['pop_(M)']
dispo_alim['dispo_alim_tonnes'] = round(dispo_alim['dispo_alim_tonnes'], 2)
dispo_alim

	code_pays	pays	annee	origin	code_produit	produit	dispo_alim_tonnes	dispo_alim_kcal	dispo_prot_g	dispo_mat_gr_g
0	1	Arménie	2013	animale	2731	Viande de Bovins	58527.82	114.0	7.97	8.88
1	1	Arménie	2013	animale	2732	Viande d'Ovins/Caprins	8990.54	22.0	1.12	1.89
2	1	Arménie	2013	animale	2733	Viande de Suides	28787.59	72.0	2.53	6.72
3	1	Arménie	2013	animale	2734	Viande de Volailles	39742.95	45.0	4.51	2.82
4	1	Arménie	2013	animale	2735	Viande, Autre	0.00	0.0	0.00	0.00
...	...	...	...	...	...	...	...	...	...	...
15600	276	Soudan	2013	vegetale	2659	Alcool, non Comestible	NaN	NaN	NaN	NaN
15601	276	Soudan	2013	vegetale	2680	Aliments pour enfants	0.00	0.0	0.00	NaN
15602	276	Soudan	2013	vegetale	2745	Miel	759.28	0.0	NaN	NaN
15603	276	Soudan	2013	vegetale	2805	Riz (Eq Blanchi)	48214.28	14.0	0.28	0.05
15604	276	Soudan	2013	vegetale	2899	Miscellanees	NaN	7.0	1.47	0.01

15605 rows × 10 columns

3. Table equilibre_prod

• Même principe que pour les tables précédentes

equil_prod = table.copy()
equil_prod = equil_prod[['code_pays', 'pays', 'annee', 'code_produit', 'produit', 'dispo_interieure_(Mt)', 'alim_pour_animaux_(Mt)', 'semences_(Mt)', 'pertes_(Mt)', 'traitement_(Mt)', 'nourriture_(Mt)', 'autres_utilisations_(Mt)']]
equil_prod.columns = ['code_pays', 'pays', 'annee', 'code_produit', 'produit', 'dispo_int', 'alim_ani', 'semences', 'pertes', 'transfo', 'nourriture', 'autres_utilisations']
equil_prod.tail()

	code_pays	pays	annee	code_produit	produit	dispo_int	alim_ani	semences	pertes	transfo	nourriture	autres_utilisations
15600	276	Soudan	2013	2659	Alcool, non Comestible	NaN	NaN	NaN	NaN	NaN	NaN	0.0
15601	276	Soudan	2013	2680	Aliments pour enfants	NaN	NaN	NaN	NaN	NaN	0.0	NaN
15602	276	Soudan	2013	2745	Miel	1.0	NaN	NaN	NaN	NaN	1.0	NaN
15603	276	Soudan	2013	2805	Riz (Eq Blanchi)	49.0	NaN	1.0	NaN	NaN	48.0	NaN
15604	276	Soudan	2013	2899	Miscellanees	NaN	NaN	NaN	NaN	NaN	NaN	NaN

4. Table sous-nutition

ss_alim.head(3)

	code_pays	pays	annees	nb_sous_alim_en_m
0	2	Afghanistan	2013	7.9
1	2	Afghanistan	2014	8.8
2	2	Afghanistan	2015	9.6

• Même principe que pour les tables précédentes

• Particularités :
  • on ne sélectionne que les données de l'année 2013
  • on converti la colonne nb_sous_alim_en_m (on passe de millions de personnes à une personne)

ss_alim_bdd = ss_alim.copy()
ss_alim_bdd = ss_alim_bdd[ss_alim_bdd['annees'] == 2013]
ss_alim_bdd['nb_sous_alim_en_m'] = ss_alim_bdd['nb_sous_alim_en_m'] * 1000000
ss_alim_bdd.columns = ['code_pays', 'pays', 'annee', 'nb_personnes']
ss_alim_bdd['nb_personnes'] = ss_alim_bdd['nb_personnes'].astype(int)
ss_alim_bdd

	code_pays	pays	annee	nb_personnes
0	2	Afghanistan	2013	7900000
5	202	Afrique du Sud	2013	2600000
10	3	Albanie	2013	200000
15	4	Algérie	2013	1700000
20	7	Angola	2013	8100000
...	...	...	...	...
575	236	Venezuela (République bolivarienne du)	2013	1900000
580	237	Viet Nam	2013	10400000
585	249	Yémen	2013	7200000
590	251	Zambie	2013	7000000
595	181	Zimbabwe	2013	6600000

119 rows × 4 columns

B/ Création de la bdd et choix des clés primaires

Méthode utilisée

---

• Pour gérer une base de données avec Jupyter Notebook, je vais utiliser les éléments suivants :

  • le système de gestion de base de données MySQL
  • ipython-sql qui permet d'utiliser le langage SQL directement dans le notebook
  • le connecteur PyMysql qui permet de se connecter à une base de données MySQL avec le langage Python
  • le toolkit SQLAlchemy qui va nous permettre de remplir nos tables via nos dataframes

• Création de la bdd FAO

  • J'ai tout d'abord créer la base de donnée fao sur un serveur personnel (via phpMyAdmin)
  • Puis j'ai autorisé l'accès distant à Mysql, puis créé un utilisateur fao spécifique à cette base

# On charge l'extension ipython-sql
%reload_ext sql  
# (ou %load_ext sql lors de la première connexion)

# On se connecte à la database FAO
%sql mysql+pymysql://fao:fao@178.170.46.4:3306/fao

'Connected: fao@fao'

# On importe le module 'crate_engine' de sqlalchemy
from sqlalchemy import create_engine

# On configure la connexion à notre database FAO
hostname="178.170.46.4"
port="3306"
dbname="fao"
uname="fao"
pwd="fao"

engine = create_engine("mysql+pymysql://{user}:{pw}@{host}:{p}/{db}"
				.format(host=hostname, p=port, db=dbname, user=uname, pw=pwd))

• Il ne reste plus qu'à créer nos tables et à les remplir

• On va donc déterminer nos clés primaires afin de les spécifier lors de la création des tables

• Remarques concernant le choix de clés primaires pertinentes :
  

  • Il est demandé de proposer une clé primaire pertinente pour chacune des 4 tables.
    Précisons qu'on aurait également pu créer une clé artificielle, c'est-à-dire ajouter une colonne id auto-incrémentée de type integer
    
    

  • Nous n'avons qu'une seule année (2013 - correspond en réalité à 2012-2014) et nous supposons qu'il en sera ainsi pour la suite.
    C'est une donnée importante à prendre en compte car si nous avions plusieurs années, les clés primaires choisies ci-après ne seraient plus valables (il faudrait en fait ajouter à chacune d'elle la composante annee afin de repecter la contrainte d'unicité)

Question 15 à 18 : Proposez une clé primaire pertinente pour chacune des 4 tables demandées

1. Table population et table sous_nutrition

• 2 colonnes ne conviennent pas en tant que clé primaire potentielle :
  
  
  • annee :
    -> il n'y a qu'une valeur (2013), donc impossible d'identifier une ligne de manière unique;
    
    
  • population :
    -> 2 pays peuvent avoir le même nombre d'habitants (ou le même nombre de personnes en sous-alimentation)
    ---> en contradiction avec la contrainte d'unicité
    
    

• 2 clé primaires potentielles : code_pays et pays

code_pays parait la plus pertinente :
  
  

  • code_pays est composé de 3 chiffres au maximum :
    -> plus facile à saisir et moins de risque de commettre une erreur de saisie
    
    
  • en terme de performance, les recherches sont plus rapides sur des clés numériques que textuelles
    
    

  => On va choisir code_pays comme clé primaire des tables population et sous-nutrition

2. Table dispo_alim et table equilibre_prod

• Plusieurs colonnes ne conviennent pas en tant que clé primaire potentielle :
  

  • table dispo_alim :
    -> origin, dispo_alim_tonnes, dispo_alim_kcal_p_j, dispo_prot, dispo_mat_gr
    
    
  • population :
    -> dispo_int, alim_ani, semences, pertes, transfo, nourriture, autres_utilisations

  Ces clés ne sont pas candidates car elles peuvent toutes être nulles (sauf origin qui ne prend que 2 valeurs et qui donc ne satisfait pas le principe d'unicité).
  De plus, il est possible que des pays aient les mêmes valeurs pour certains produits.

• Il reste les colonnes suivantes : pays, code_pays, année, produit, code_produit
  

  • Aucune de ces colonnes ne peut à elle seule être une clé primaire car aucune ne satisfait la contrainte d'unicité :
    
    

    • un pays a plusieurs produits (et donc la même chose pour code_pays)
      
    • les mêmes produits sont présents dans les différents pays (et donc la même chose pour code_produit)
      
    • il y a une seule valeur comme 'annee'
      
      

  • Mais aucune de ces colonnes ne peut être nulle

    • Il est également possible de trouver des combinaisons de références de colonnes uniques comme possibles clés primaires composites :
      En effet, on peut utiliser plusieurs combinaisons, comme par exemple (pays, produit) ou (code_pays, code_produit), qui permettent d'identifier une ligne de manière unique.
      Or comme expliqué pour les tables population et sous_nutrition, on préfèrera utiliser des clés composées de chiffres que de texte (meilleure lisibilité et performance plus importante)
      

  => On va choisir (code_pays, code_produit) comme clé primaire composite des tables dispo_alim et equilibre_prod.

C/ Création des tables et insert des données

1. Table population

• On crée la table population :
  

  • clé primaire : code_pays
  • aucune colonne ne peut avoir de valeurs nulles

%%sql
SET FOREIGN_KEY_CHECKS = 0;
drop table if exists Population;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE Population
       (pays varchar(50) NOT NULL,
        code_pays int NOT NULL,
        annee int NOT NULL,
        population int NOT NULL,
        CONSTRAINT pk_population PRIMARY KEY (code_pays),
        UNIQUE INDEX index_pays (pays)
       );

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

[]

• On remplit notre table population avec les données de notre df 'population' :
  

  • con = engine : on utilise la connexion définie avec SQLAlchemy
  • if_exists="append" : comportement à adopter si la table existe déjà (ici on insère les nouvelles données dans la table existante si déjà créée)
  • method='multi' : permet d'accélérer l'insertion des données du df dans la database (plusieurs valeurs sont transmises à chaque INSERT)
  • index=False : permet de ne pas ajouter l'index du df comme nouvelle colonne à la table

population_dbb.to_sql('Population', con=engine, if_exists='append', method='multi', index=False)

• On vérifie que tout s'est bien déroulé avec un SELECT

%%sql 
SELECT * 
FROM Population
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	code_pays	annee	population
Arménie	1	2013	2977000
Afghanistan	2	2013	30552000
Albanie	3	2013	3173000
Algérie	4	2013	39208000
Angola	7	2013	21472000
Antigua-et-Barbuda	8	2013	90000
Argentine	9	2013	41446000
Australie	10	2013	23343000
Autriche	11	2013	8495000
Bahamas	12	2013	377000

2. Table dispo_alim

%%sql
SET FOREIGN_KEY_CHECKS = 0;
drop table if exists Dispo_Alim;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE Dispo_Alim
       (pays varchar(50) NOT NULL,
        code_pays int NOT NULL,
        annee int NOT NULL,
        origin ENUM ('animale', 'vegetale') NOT NULL,
        code_produit int NOT NULL,
        produit varchar(50) NOT NULL,
        dispo_alim_tonnes numeric,
        dispo_alim_kcal numeric,
        dispo_prot_g numeric,
        dispo_mat_gr_g numeric,
        CONSTRAINT pk_dispo_alim 
            PRIMARY KEY (code_pays, code_produit),
        CONSTRAINT fk_dispo_alim_code_pays 
            FOREIGN KEY (code_pays) 
            REFERENCES Population(code_pays),
        CONSTRAINT fk_dispo_alim_pays 
            FOREIGN KEY (pays) 
            REFERENCES Population(pays)
       );

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

[]

dispo_alim.to_sql('Dispo_Alim', con = engine, if_exists="append", index=False)

%%sql 
SELECT * 
FROM Dispo_Alim 
LIMIT 10 
OFFSET 8000

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	code_pays	annee	origin	code_produit	produit	dispo_alim_tonnes	dispo_alim_kcal	dispo_prot_g	dispo_mat_gr_g
Libéria	123	2013	vegetale	2570	Plantes Oleiferes, Autre	0	0	0	0
Libéria	123	2013	vegetale	2571	Huile de Soja	0	0	None	0
Libéria	123	2013	vegetale	2572	Huile d'Arachide	0	0	None	0
Libéria	123	2013	vegetale	2574	Huile de Colza&Moutarde	86	0	None	0
Libéria	123	2013	vegetale	2576	Huile de Palmistes	5196	29	None	3
Libéria	123	2013	vegetale	2577	Huile de Palme	62735	354	None	40
Libéria	123	2013	vegetale	2578	Huile de Coco	0	0	None	0
Libéria	123	2013	vegetale	2579	Huile de Sésame	0	0	None	0
Libéria	123	2013	vegetale	2580	Huile d'Olive	129	1	None	0
Libéria	123	2013	vegetale	2586	Huil Plantes Oleif Autr	3908	22	0	2

3. Table equilibre_prod

%%sql
SET FOREIGN_KEY_CHECKS = 0;
drop table if exists Equilibre_Prod;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE Equilibre_Prod
       (pays varchar(50) NOT NULL,
        code_pays int NOT NULL,
        annee int NOT NULL,
        code_produit int NOT NULL,
        produit varchar(50) NOT NULL,
        dispo_int numeric,
        alim_ani numeric,
        semences numeric,
        pertes numeric,
        transfo numeric,
        nourriture numeric,
        autres_utilisations numeric,
        CONSTRAINT pk_equilibre_prod 
            PRIMARY KEY (code_pays, code_produit),
        CONSTRAINT fk_equilibre_prod_code_pays 
            FOREIGN KEY (code_pays) 
            REFERENCES Population(code_pays),
        CONSTRAINT fk_equilibre_prod_pays 
            FOREIGN KEY (pays) 
            REFERENCES Population(pays)
       );

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

[]

equil_prod.to_sql('Equilibre_Prod', con = engine, if_exists="append", index=False)

%%sql 
SELECT * 
FROM Equilibre_Prod 
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	code_pays	annee	code_produit	produit	dispo_int	alim_ani	semences	pertes	transfo	nourriture	autres_utilisations
Arménie	1	2013	2511	Blé	554	93	30	32	10	389	0
Arménie	1	2013	2513	Orge	198	137	14	15	7	0	26
Arménie	1	2013	2514	Maïs	102	96	0	7	None	0	None
Arménie	1	2013	2515	Seigle	1	1	0	0	None	0	None
Arménie	1	2013	2516	Avoine	6	4	0	0	None	1	None
Arménie	1	2013	2517	Millet	None	0	None	None	None	None	None
Arménie	1	2013	2518	Sorgho	None	0	None	None	None	None	None
Arménie	1	2013	2520	Céréales, Autres	21	1	1	1	None	6	12
Arménie	1	2013	2531	Pommes de Terre	672	225	123	96	78	134	16
Arménie	1	2013	2532	Manioc	None	0	None	None	None	None	None

4. Table sous-nutition

%%sql
SET FOREIGN_KEY_CHECKS = 0;
drop table if exists Sous_Nutrition;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE Sous_Nutrition
       (pays varchar(50) NOT NULL,
        code_pays int NOT NULL,
        annee int NOT NULL,
        nb_personnes int NOT NULL,
        CONSTRAINT pk_sous_nutrition 
            PRIMARY KEY (code_pays),
        CONSTRAINT fk_sous_nutrition_code_pays 
            FOREIGN KEY (code_pays) 
            REFERENCES Population(code_pays),
        CONSTRAINT fk_sous_nutrition_pays 
            FOREIGN KEY (pays) 
            REFERENCES Population(pays)
       );

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

[]

ss_alim_bdd.to_sql('Sous_Nutrition', con = engine, if_exists="append", index=False)

%%sql 
SELECT * 
FROM Sous_Nutrition 
LIMIT 10 
OFFSET 50;

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	code_pays	annee	nb_personnes
Côte d'Ivoire	107	2013	4700000
Jamaïque	109	2013	300000
Jordanie	112	2013	900000
Kirghizistan	113	2013	400000
Kenya	114	2013	10000000
Cambodge	115	2013	2800000
République populaire démocratique de Corée	116	2013	10600000
République démocratique populaire lao	120	2013	1200000
Liban	121	2013	500000
Lesotho	122	2013	300000

Question 19 : Ecrivez les requêtes SQL permettant de connaître :

19.1 - Les 10 pays ayant le plus haut ratio disponibilité alimentaire/habitant en termes de protéines (en kg) par habitant, puis en termes de kcal par habitant.

Disponibilité alimentaire par habitant en termes de kg de protéines par habitant les plus élevées

• On suppose que l'on veut la quantité de protéines sur un an

%%sql
SELECT pays, 
       SUM(dispo_prot_g/1000)*365 AS proteine_hab_kg_an
FROM Dispo_Alim
GROUP BY pays
ORDER BY proteine_hab_kg_an DESC
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	proteine_hab_kg_an
Islande	47.0850
Chine - RAS de Hong-Kong	46.3550
Israël	45.6250
Lituanie	44.1650
Maldives	42.3400
Finlande	41.9750
Portugal	41.2450
Norvège	40.5150
Luxembourg	40.5150
Albanie	39.7850

############################################################################
#  Remarque :                                                              #
#    On peut aussi mettre la requête dans une variable :                   #
#    ---> intérêt pour l'affichage : permet de boucler sur les résultats   #
############################################################################

res_req_1 = %sql SELECT pays, SUM(dispo_prot_g/1000)*365 AS proteine_hab_kg_an FROM Dispo_Alim GROUP BY pays ORDER BY proteine_hab_kg_an DESC LIMIT 10
print()
print('La disponibilité alimentaire par habitant en termes de quantité de protéines est la plus élevé dans les pays suivants :')
for i in range(10):
    print(f'   - {res_req_1[i][0]} avec {res_req_1[i][1]} kg de protéines par habitant par an')

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

La disponibilité alimentaire par habitant en termes de quantité de protéines est la plus élevé dans les pays suivants :
   - Islande avec 47.0850 kg de protéines par habitant par an
   - Chine - RAS de Hong-Kong avec 46.3550 kg de protéines par habitant par an
   - Israël avec 45.6250 kg de protéines par habitant par an
   - Lituanie avec 44.1650 kg de protéines par habitant par an
   - Maldives avec 42.3400 kg de protéines par habitant par an
   - Finlande avec 41.9750 kg de protéines par habitant par an
   - Portugal avec 41.2450 kg de protéines par habitant par an
   - Norvège avec 40.5150 kg de protéines par habitant par an
   - Luxembourg avec 40.5150 kg de protéines par habitant par an
   - Albanie avec 39.7850 kg de protéines par habitant par an

Disponibilité alimentaire par habitant en termes de kcal par habitant les plus élevées

• On suppose que l'on veut la quantité de kcal par jour

%%sql
SELECT pays, 
       SUM(dispo_alim_kcal) AS kcal_hab_j
FROM Dispo_Alim
GROUP BY pays
ORDER BY kcal_hab_j desc
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	kcal_hab_j
Autriche	3770
Belgique	3737
Turquie	3708
États-Unis d'Amérique	3682
Israël	3610
Irlande	3602
Italie	3578
Luxembourg	3540
Égypte	3518
Allemagne	3503

19.2 - Les 10 pays ayant le plus faible ratio disponibilité alimentaire/habitant en termes de protéines (en kg) par habitant, puis en termes de kcal par habitant.

Disponibilité alimentaire par habitant en termes de kg de protéines par habitant les moins élevées

• On suppose que l'on veut la quantité de protéines sur un an

%%sql
SELECT pays, 
       SUM(dispo_prot_g/1000)*365 AS proteine_hab_kg_an
FROM Dispo_Alim
GROUP BY pays
ORDER BY proteine_hab_kg_an ASC
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	proteine_hab_kg_an
Libéria	12.7750
Mozambique	14.6000
Madagascar	15.6950
Guinée-Bissau	16.0600
République centrafricaine	16.0600
Haïti	16.0600
Zimbabwe	17.8850
Sao Tomé-et-Principe	18.2500
Congo	18.2500
République populaire démocratique de Corée	18.9800

Disponibilité alimentaire par habitant en termes de kcal par habitant les moins élevées

• On suppose que l'on veut la quantité de kcal par jour

%%sql
SELECT pays, 
       SUM(dispo_alim_kcal) AS kcal_hab_j
FROM Dispo_Alim
GROUP BY pays
ORDER BY kcal_hab_j
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	kcal_hab_j
République centrafricaine	1879
Zambie	1924
Madagascar	2056
Afghanistan	2087
Haïti	2089
République populaire démocratique de Corée	2093
Tchad	2109
Zimbabwe	2113
Ouganda	2126
Éthiopie	2129

19.3 - La quantité totale (en kg) de produits perdus par pays en 2013.

%%sql
SELECT pays, 
       SUM(pertes*1000000) AS total_pertes_kg
FROM Equilibre_Prod 
GROUP BY pays
ORDER BY total_pertes_kg DESC
LIMIT 15

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
15 rows affected.

pays	total_pertes_kg
Chine, continentale	89575000000
Brésil	75914000000
Inde	55930000000
Nigéria	19854000000
Indonésie	13081000000
Turquie	12036000000
Mexique	8289000000
Égypte	7608000000
Ghana	7442000000
États-Unis d'Amérique	7162000000
Viet Nam	6743000000
Pakistan	5897000000
Thaïlande	5749000000
Iran (République islamique d')	5450000000
Fédération de Russie	4997000000

19.4 - Les 10 pays pour lesquels la proportion de personnes sous-alimentées est la plus forte.

%%sql
SELECT Sous_Nutrition.pays, 
       Sous_Nutrition.annee, 
       Sous_Nutrition.nb_personnes / Population.population AS part_ss_nut 
FROM Sous_Nutrition 
LEFT JOIN Population on Sous_Nutrition.code_pays = Population.code_pays
ORDER BY part_ss_nut DESC
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

pays	annee	part_ss_nut
Dominique	2013	0.6944
Haïti	2013	0.5040
Kiribati	2013	0.4902
Zambie	2013	0.4815
Zimbabwe	2013	0.4664
Saint-Vincent-et-les Grenadines	2013	0.4587
République centrafricaine	2013	0.4333
République populaire démocratique de Corée	2013	0.4258
Congo	2013	0.4047
Tchad	2013	0.3821

19.5 - Les 10 produits pour lesquels le ratio Autres utilisations/Disponibilité intérieure est le plus élevé.

%%sql
SELECT produit, 
       avg(coalesce(autres_utilisations, 0) / dispo_int) AS r_autres_dispo_int
FROM Equilibre_Prod
WHERE dispo_int IS NOT NULL
GROUP BY produit
ORDER BY r_autres_dispo_int DESC
LIMIT 10

 * mysql+pymysql://fao:***@178.170.46.4:3306/fao
10 rows affected.

produit	r_autres_dispo_int
Alcool, non Comestible	0.96551724
Plantes Aquatiques	0.92066136
Huile de Palme	0.65252432
Huil Plantes Oleif Autr	0.54369236
Huile de Palmistes	0.53418095
Huile de Colza&Moutarde	0.46264783
Huiles de Poissons	0.40273617
Huile de Coco	0.36329495
Graisses Animales Crue	0.30480915
Manioc	0.23284628

Question 20 : pour quelques uns des produits identifiés dans cette dernière requête SQL, supposez quelles sont ces "autres utilisations" possibles

• L'Huile de Palme :
  
  

  • dans le domaine des cosmétiques
    La graisse de palme se retrouve, sous forme transformée, dans de nombreux produits cosmétiques industriels :
    --> savons, crèmes visage, huiles pour le corps ou les cheveux, produits de maquillage (rouge à lèvres) etc.
    Depuis de nombreuses années, le coût très faible de cette matière première encourage les industriels à l'utiliser
    source : Passeport Santé
    
    
  • dans le domaine des biocarburants
    Un biocarburant peut se définir comme un carburant issu de la transformation des matières végétales produites par l'agriculture, dont notamment l'huile de palme (mais aussi la betterave, le blé, le maïs, le colza, le tournesol, la pomme de terre…).
    Selon une étude de l'ONG 'Les amis de la Terre', 76 % de l’huile de palme que nous consommons en France le serait sous forme de carburant
    
    

• L'Huile de Coco :
  
  

  • dans le domaine des cosmétiques
    • L'huile de coco est fréquemment utilisée dans la composition du savon, dans une proportion de 15 à 20%, car elle a un fort pouvoir moussant.
      
    • L'huile de coco est également un des éléments de la fabrication du monoï.
      
      

  • dans le domaine de l'industrie chimique

    • L'huile de coco est l'une des principales sources d'acides gras saturés (elle est très utilisée pour les cuissons à hautes températures et/ou fritures)
      --> C'est ainsi qu'elle entre alors dans la composition de produits très divers dont principalement des détergents, des plastiques et des cosmétiques.

    sources : Wikipedia, Sahel.doc, Sauvons la Forêt

EXPORT DES DATA

• Export des des df en csv pour le fichier relatif au dossier

ss_alim_bdd.to_csv("data/data_part2_sql/ss_alim_bdd.csv", index = False, header = True)
population_dbb.to_csv("data/data_part2_sql/population_bdd.csv", index = False, header = True)