{Progetto SQL}
Analisi sui dati forniti dalla Food and Agricolture Organization sullo spreco del cibo a livello globale.
Ho effettuato l'analisi operando in 3 fasi:
Importazione.
In questa fase ho importato i dati definendo le colonne e il contenuto del dataset.
1.
2.
Pulizia e esplorazione.
Questa senza dubbio è stata la fase più delicata: ho pulito i dati, avendo così anche modo di esplorarli e di creare una scaletta di domande a cui volevo dare risposta.
3.
Analisi dei dati.
Seguendo la scaletta che ho creato nello step 2, ho interrogato il dataset scrivendo delle Query mirate a estrarre le informazioni che mi servivano.
1. Importazione.
Per cominciare ho creato il database su pgAdmin, cliccando con il tasto destro su Databases-->Create-->Database. Mi è bastato dare un nome al database e premere "save".
Creazione della tabella e popolamento.
Lo step successivo mi ha impegnato nella creazione della tabella e l'effettiva importazione dei dati al suo interno.
La fase di import dati mi ha creato qualche difficoltà nel far combaciare i datatype: mi sono reso conto effettivamente della scarsa conformità dei dati rispetto alle mie aspettative.
Ho quindi modificato i datatype man mano che importavo fino a raggiungere la completa importazione del dataset.
/*
Pulisco il dataset filtrando le colonne che intendo includere
nella mia analisi in una nuova tabella,
in modo da non perdere i dati originali
*/
CREATE TABLE FAO_food_loss_waste as
SELECT country, reference_year, loss_percentage, commodity, activity, food_supply_stage, loss_quantity, cause_of_loss, sample_size
FROM food_loss_waste;
--aggiungo la primary key alla nuova tabella
ALTER TABLE FAO_food_loss_waste
ADD COLUMN id_ serial not null primary key;
2. Pulizia e ordinamento dei dati.
Attraverso alcune Query ho eliminato le colonne che non mi interessavano, snellito i dati da analizzare eliminando i doppioni, e impostato la tabella per la fase di analisi.
--Proseguo la mia pulizia identificando ed eliminando le righe duplicate
DELETE FROM FAO_food_loss_waste
WHERE id_ NOT IN (
SELECT MIN(id_)
FROM FAO_food_loss_waste
GROUP BY country,reference_year,loss_percentage,commodity,activity,food_supply_stage,loss_quantity,cause_of_loss,sample_size);
/*Mi rendo conto che nella lista dei paesi ci sono anche continenti e
per rendere il dataset più attendibile decido di eliminarli e concentrarmi sui
singoli stati*/
CREATE TABLE continent as
SELECT * FROM FAO_food_loss_waste
WHERE country LIKE '%Asia'
OR country LIKE '%Europe'
OR country = 'Africa';
...
/*
ora che ho raggruppato le righe che mi interessava eliminare
in un'altra tabella in modo da poterle, se voglio, analizzarle in seguito;
le elimino da FAO_food_loss_waste
*/
DELETE FROM FAO_food_loss_waste
WHERE country LIKE '%Asia'
OR country LIKE '%Europe'
OR country = 'Africa';
--fine pulizia dei dati
Ora i dati sono pronti per l'analisi.
3. Analisi dei dati.
Pulendo i dati ho avuto modo di pormi delle domande, che mi sono appuntato man mano che esploravo il dataset, alle quali tenterò di rispondere con le prossime Query.
--Paesi più virtuosi:
SELECT country, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric))
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY country
ORDER BY total_losswaste ASC NULLS LAST
LIMIT 30;
--Paesi meno virtuosi:
SELECT country, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric))
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY country
ORDER BY total_losswaste DESC NULLS LAST
LIMIT 30;
Input:
Output:
<-- Ecco i paesi più virtuosi
Ecco i paesi meno virtuosi -->
N.B. Ho analizzato la quantità di cibo sprecata non in relazione a dimensioni e popolazione dei paesi, perché non avendo a disposizione per tutti i paesi la stessa tipologia di dati ho preferito lavorare sulle quantità piuttosto che sulle proporzioni.
country | total_losswaste |
---|---|
Germany | 28797463 |
Russian Federation | 27769016 |
Ukraine | 22510050 |
Poland | 22175204 |
United States of America | 20195947.04.00 |
Peru | 18667082.01.00 |
France | 17100854 |
Egypt | 12921400 |
Japan | 12645024.4 |
Afghanistan | 10077000 |
Mexico | 8718811.13.00 |
Denmark | 7862825 |
Kazakhstan | 7067823 |
Venezuela (Bolivarian Republic of) | 6506335 |
Indonesia | 6255000 |
Austria | 6133406 |
Canada | 6050230 |
Pakistan | 4984504.19.00 |
Mali | 3844000 |
country | total_losswaste |
---|---|
Malawi | 0 |
Haiti | 0 |
Uganda | 0 |
United Republic of Tanzania | 0 |
Ethiopia | 0 |
India | 4.50 |
Rwanda | 37.35.00 |
Finland | 44 |
China | 60 |
Senegal | 150 |
Honduras | 151.58.00 |
Timor-Leste | 958 |
New Caledonia | 1149 |
Cambodia | 2166.0 |
Luxembourg | 5559 |
Portugal | 6000 |
Belarus | 8400 |
Zambia | 12144.05.00 |
China,Taiwan | 13300 |
--Tipologie di cibo maggiormente sprecate negli ultimi 20 anni:
SELECT commodity, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric))
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY commodity
ORDER BY total_losswaste DESC NULLS LAST
LIMIT 30;
--Qual'è il rapporto tra la fase di approvvigionamento e la quantità percentuale di cibo sprecata?
SELECT DISTINCT(food_supply_stage),avg(loss_percentage)
AS total
FROM fao_food_loss_waste
GROUP BY food_supply_stage
ORDER BY avg(loss_percentage) DESC NULLS LAST
LIMIT 50;
Input:
Output:
commodity | total_losswaste |
---|---|
Potatoes | 123271007.42.00 |
Wheat | 43968342.14.00 |
Maize (corn) | 25755742.43.00 |
Barley | 13471867 |
Cassava, fresh | 5442686 |
Rice | 5436982.36.00 |
Plantains and others | 5080979 |
Tomatoes | 4328473.02.00 |
Raw milk of cattle | 3836981.6 |
Meat of pig, fresh or chilled | 3819357 |
Sorghum | 3438571.53.00 |
Sugar cane | 2782193.30.00 |
Rye | 2751075 |
Soya beans | 2566599.8 |
Husked rice | 2507000 |
Triticale | 2344025 |
Grapes | 2265497 |
Rapeseed or colza seed | 2175972 |
Sweet potatoes | 2023708.4 |
<-- Tipologie di cibo maggiormente sprecate.
Rapporto fase di approvvigionamento-quantità di cibo sprecata.-->
Come si può notare patate, grano e mais sono i cibi maggiormente sprecati, mentre lo spreco maggiore avviene dopo il raccolto e all'interno delle mura domestiche, il che lascia intendere che oltre ai problemi nella catena di approvvigionamento del cibo, c'è una carenza di educazione allo spreco alimentare all'interno delle famiglie.
food_supply_stage | total |
---|---|
Post-harvest | 219.528.846.153.846.000 |
Households | 194.907.586.206.896.000 |
NULL | 178.542.553.191.489.000 |
Retail | 117.875.534.441.805.000 |
Food Services | 114.064.285.714.285.000 |
Export | 108.778.000.000.000.000 |
Market | 104.816.666.666.666.000 |
Pre-harvest | 99.937.500.000.000.000 |
Wholesale | 87.920.978.723.404.200 |
Grading | 82.272.727.272.727.200 |
Distribution | 81.600.000.000.000.000 |
Trader | 78.551.521.739.130.400 |
Whole supply chain | 73.224.816.461.191.100 |
Processing | 66.426.535.269.709.500 |
Packing | 50.176.470.588.235.200 |
Harvest | 50.084.924.955.773.700 |
Storage | 32.700.163.733.115.000 |
Farm | 27.393.339.177.507.000 |
Stacking | 21.000.000.000.000.000 |
Transport | 16.127.654.387.865.600 |
--Quali sono le 10 maggiori cause di spreco/perdita?
SELECT DISTINCT(cause_of_loss), sum((cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric)))
AS causes_total
FROM fao_food_loss_waste
GROUP BY DISTINCT(cause_of_loss)
ORDER BY causes_total DESC NULLS LAST
LIMIT 10;
--C'è stato un miglioramento nella quantità di cibo sprecato tra l'inizio e la fine dell'intervallo analizzato?
SELECT round(avg(CASE WHEN reference_year>='2016' AND reference_year <='2021' THEN loss_percentage END)) AS avg_now,
round(avg(CASE WHEN reference_year>='2000' AND reference_year <='2005' THEN loss_percentage END)) AS avg_pre
from fao_food_loss_waste;
Input:
Output:
cause_of_loss | causes_total |
---|---|
NULL | 279802147.35.00 |
Improper harvesting, handling, grading, packing, transportation and storage | 4984000 |
2668166 | |
Predators; Respiratory problems; digestive problems ; harsh winter | 636069 |
Insect damage, rotten loss, remain in the soil, cutting loss | 5757.08.00 |
This loss comprised the weight loss (57%), spoilage loss (34%) and other loss (9%) caused due to sprouting, shrinkage, cold injury etc | 2718 |
Lack Of Packing And Manual Method | 900 |
Hot Weather During Harvest And Post Packaging | 825 |
Machinery and handling tests | 422.04.00 |
Mirasi et al. (2013) measured grain losses of different wheat varieties with different models of combine during harvest stage. They observed that average pre harvest losses in all fields of study were 31.4 kg ha-1 accounting for 12.71 percent of total losses. | 31.4 |
Sopra abbiamo le 10 maggiori cause di spreco, dove (dati nulli a parte) si evince che ci sia un'inadeguata catena di gestione delle materie prime. A destra il dato (triste) sul fatto che negli ultimi 20 anni il rapporto tra produzione e cibo sprecato non sia migliorato.
avg_now | avg_pre |
---|---|
4 | 4 |
-- Quali paesi sono peggiorati in termini di spreco alimentare negli ultimi 5 anni?
SELECT DISTINCT(COUNTRY), SUM(loss_percentage) FROM fao_food_loss_waste
WHERE reference_year>'2016'
GROUP BY country
HAVING SUM(loss_percentage)>0
ORDER BY SUM(loss_percentage) DESC NULLS LAST;
--Fine query
Bonus Query:
Output (top ten):
country | sum |
---|---|
United States of America | 2435.07.00 |
Zimbabwe | 511.24.00 |
Uganda | 511.00.00 |
Ethiopia | 459.02.00 |
Burkina Faso | 392.34.00 |
Kenya | 368.16.00 |
Zambia | 361.29.00 |
Malawi | 350.20.00 |
United Republic of Tanzania | 342.26.00 |
Il tema dello spreco alimentare è un tema che mi sta particolarmente a cuore, penso che il rispetto e la capacità che abbiamo di valorizzare e maneggiare i prodotti che la terra ci da sia un'indicatore importante della direzione che stiamo prendendo come popolazione globale, e purtroppo i dati sull'andamento non sono incoraggianti.
Grazie per l'attenzione.
Angelo Tedesco
Copy of Progetto SQL
By angelo_ted
Copy of Progetto SQL
- 326