This project deals with analysis of the CIA factbook data. The CIA factbook contains demographic information like the country's area, population, population growth, birth rate and so on.
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
Let's see how the first 5 rows of data looks like
%%sql
SELECT *
FROM facts
LIMIT 5;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
1 | af | Afghanistan | 652230 | 652230 | 0 | 32564342 | 2.32 | 38.57 | 13.89 | 1.51 |
2 | al | Albania | 28748 | 27398 | 1350 | 3029278 | 0.3 | 12.92 | 6.58 | 3.3 |
3 | ag | Algeria | 2381741 | 2381741 | 0 | 39542166 | 1.84 | 23.67 | 4.31 | 0.92 |
4 | an | Andorra | 468 | 468 | 0 | 85580 | 0.12 | 8.13 | 6.96 | 0.0 |
5 | ao | Angola | 1246700 | 1246700 | 0 | 19625353 | 2.78 | 38.78 | 11.49 | 0.46 |
Let's calculate the maximum and minimum of Population, population growth
%%sql
SELECT MAX(population) AS maximum_population,
MIN(population) AS minimum_population,
MAX(population_growth) AS maximum_population_growth,
MIN(population_growth) AS minimum_population_growth
FROM facts;
Done.
maximum_population | minimum_population | maximum_population_growth | minimum_population_growth |
---|---|---|---|
7256490011 | 0 | 4.02 | 0.0 |
Let's write a query to check the name of the country with maximum population
%%sql
SELECT name AS country, MAX(population) AS max_population
FROM facts;
Done.
country | max_population |
---|---|
World | 7256490011 |
The data consists of a row with the data of the World and that explains why there is 7.25 billion as maximum population. Let's write a query to figure out the country with 0 inhabitants.
%%sql
SELECT name AS country, MIN(population) AS minimum_population
FROM facts;
Done.
country | minimum_population |
---|---|
Antarctica | 0 |
The data contains Antarctica which explains the minimum number of population as 0. The image here shows the a screenshot taken from the CIA factbook website and also says that there are no indegeneous inhabitants. The relevant information can be found in people and society section of CIA FACTBOOK Antarctica section.
Let's calculate the summary statistics without the world row.
%%sql
SELECT MIN(population) AS minimum_population,
MAX(population) AS maximum_population,
MAX(population_growth) AS maximum_population_growth,
MIN(population_growth) AS minimum_population_growth
FROM facts
WHERE name <> "World";
Done.
minimum_population | maximum_population | maximum_population_growth | minimum_population_growth |
---|---|---|---|
0 | 1367485388 | 4.02 | 0.0 |
The maximum population turn outs to be 1.36 billion. Let's look in the country which this maximum population belongs to.
%%sql
SELECT name AS country,
MAX(population) AS maximum_population
FROM facts
WHERE country <> "World";
Done.
country | maximum_population |
---|---|
China | 1367485388 |
So, China is the country with maximum population.
Let's calculate the average area excluding the world
%%sql
SELECT ROUND(AVG(area),2) AS average_area
FROM facts
Where name <> "World";
Done.
average_area |
---|
555093.55 |
The data reveals that the average area is a bit greater than five hundred and fifty five thousand. Now, let's extract the list of the countries which has area greater than average area.
%%sql
SELECT *
FROM facts
WHERE area > (SELECT AVG(area)
FROM facts
WHERE name <> "World")
AND
name <> "World"
ORDER BY area DESC;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
143 | rs | Russia | 17098242 | 16377742 | 720500 | 142423773 | 0.04 | 11.6 | 13.69 | 1.69 |
32 | ca | Canada | 9984670 | 9093507 | 891163 | 35099836 | 0.75 | 10.28 | 8.42 | 5.66 |
186 | us | United States | 9826675 | 9161966 | 664709 | 321368864 | 0.78 | 12.49 | 8.15 | 3.86 |
37 | ch | China | 9596960 | 9326410 | 270550 | 1367485388 | 0.45 | 12.49 | 7.53 | 0.44 |
24 | br | Brazil | 8515770 | 8358140 | 157630 | 204259812 | 0.77 | 14.46 | 6.58 | 0.14 |
9 | as | Australia | 7741220 | 7682300 | 58920 | 22751014 | 1.07 | 12.15 | 7.14 | 5.65 |
197 | ee | European Union | 4324782 | None | None | 513949445 | 0.25 | 10.2 | 10.2 | 2.5 |
77 | in | India | 3287263 | 2973193 | 314070 | 1251695584 | 1.22 | 19.55 | 7.32 | 0.04 |
7 | ar | Argentina | 2780400 | 2736690 | 43710 | 43431886 | 0.93 | 16.64 | 7.33 | 0.0 |
87 | kz | Kazakhstan | 2724900 | 2699700 | 25200 | 18157122 | 1.14 | 19.15 | 8.21 | 0.41 |
3 | ag | Algeria | 2381741 | 2381741 | 0 | 39542166 | 1.84 | 23.67 | 4.31 | 0.92 |
40 | cg | Congo, Democratic Republic of the | 2344858 | 2267048 | 77810 | 79375136 | 2.45 | 34.88 | 10.07 | 0.27 |
207 | gl | Greenland | 2166086 | 2166086 | None | 57733 | 0.0 | 14.48 | 8.49 | 5.98 |
151 | sa | Saudi Arabia | 2149690 | 2149690 | 0 | 27752316 | 1.46 | 18.51 | 3.33 | 0.55 |
114 | mx | Mexico | 1964375 | 1943945 | 20430 | 121736809 | 1.18 | 18.78 | 5.26 | 1.68 |
78 | id | Indonesia | 1904569 | 1811569 | 93000 | 255993674 | 0.92 | 16.72 | 6.37 | 1.16 |
165 | su | Sudan | 1861484 | None | None | 36108853 | 1.72 | 29.19 | 7.66 | 4.29 |
100 | ly | Libya | 1759540 | 1759540 | 0 | 6411776 | 2.23 | 18.03 | 3.58 | 7.8 |
79 | ir | Iran | 1648195 | 1531595 | 116600 | 81824270 | 1.2 | 17.99 | 5.94 | 0.07 |
118 | mg | Mongolia | 1564116 | 1553556 | 10560 | 2992908 | 1.31 | 20.25 | 6.35 | 0.84 |
137 | pe | Peru | 1285216 | 1279996 | 5220 | 30444999 | 0.97 | 18.28 | 6.01 | 2.53 |
5 | ao | Angola | 1246700 | 1246700 | 0 | 19625353 | 2.78 | 38.78 | 11.49 | 0.46 |
109 | ml | Mali | 1240192 | 1220190 | 20002 | 16955536 | 2.98 | 44.99 | 12.89 | 2.26 |
161 | sf | South Africa | 1219090 | 1214470 | 4620 | 53675563 | 1.33 | 20.75 | 9.91 | 2.42 |
38 | co | Colombia | 1138910 | 1038700 | 100210 | 46736728 | 1.04 | 16.47 | 5.4 | 0.64 |
58 | et | Ethiopia | 1104300 | None | 104300 | 99465819 | 2.89 | 37.27 | 8.19 | 0.22 |
21 | bl | Bolivia | 1098581 | 1083301 | 15280 | 10800882 | 1.56 | 22.76 | 6.52 | 0.62 |
112 | mr | Mauritania | 1030700 | 1030700 | 0 | 3596702 | 2.23 | 31.34 | 8.2 | 0.83 |
53 | eg | Egypt | 1001450 | 995450 | 6000 | 88487396 | 1.79 | 22.9 | 4.77 | 0.19 |
172 | tz | Tanzania | 947300 | 885800 | 61500 | 51045882 | 2.79 | 36.39 | 8.0 | 0.54 |
129 | ni | Nigeria | 923768 | 910768 | 13000 | 181562056 | 2.45 | 37.64 | 12.9 | 0.22 |
191 | ve | Venezuela | 912050 | 882050 | 30000 | 29275460 | 1.39 | 19.16 | 5.31 | 0.0 |
122 | wa | Namibia | 824292 | 823290 | 1002 | 2212307 | 0.59 | 19.8 | 13.91 | 0.0 |
121 | mz | Mozambique | 799380 | 786380 | 13000 | 25303113 | 2.45 | 38.58 | 12.1 | 1.98 |
132 | pk | Pakistan | 796095 | 770875 | 25220 | 199085847 | 1.46 | 22.58 | 6.49 | 1.54 |
179 | tu | Turkey | 783562 | 769632 | 13930 | 79414269 | 1.26 | 16.33 | 5.88 | 2.16 |
36 | ci | Chile | 756102 | 743812 | 12290 | 17508260 | 0.82 | 13.83 | 6.0 | 0.34 |
194 | za | Zambia | 752618 | 743398 | 9220 | 15066266 | 2.88 | 42.13 | 12.67 | 0.68 |
28 | bm | Burma | 676578 | 653508 | 23070 | 56320206 | 1.01 | 18.39 | 7.96 | 0.28 |
1 | af | Afghanistan | 652230 | 652230 | 0 | 32564342 | 2.32 | 38.57 | 13.89 | 1.51 |
162 | od | South Sudan | 644329 | None | None | 12042910 | 4.02 | 36.91 | 8.18 | 11.47 |
61 | fr | France | 643801 | 640427 | 3374 | 66553766 | 0.43 | 12.38 | 9.16 | 1.09 |
160 | so | Somalia | 637657 | 627337 | 10320 | 10616380 | 1.83 | 40.45 | 13.62 | 8.49 |
34 | ct | Central African Republic | 622984 | 622984 | 0 | 5391539 | 2.13 | 35.08 | 13.8 | 0.0 |
183 | up | Ukraine | 603550 | 579330 | 24220 | 44429471 | 0.6 | 10.72 | 14.46 | 2.25 |
105 | ma | Madagascar | 587041 | 581540 | 5501 | 23812681 | 2.58 | 32.61 | 6.81 | 0.0 |
23 | bc | Botswana | 581730 | 566730 | 15000 | 2182719 | 1.21 | 20.96 | 13.39 | 4.56 |
88 | ke | Kenya | 580367 | 569140 | 11227 | 45925301 | 1.93 | 26.4 | 6.89 | 0.22 |
Russia is at the top of the list and Kenya is at the bottom. Let's count the number of countries which has area greater than average area.
%%sql
SELECT COUNT(*) greater_than_average_count
FROM facts
WHERE area > (SELECT AVG(area)
FROM facts
WHERE name <> "World")
AND
name <> "World";
Done.
greater_than_average_count |
---|
48 |
There are 48 countries which has area greater than the average area.
%%sql
SELECT name AS country, ROUND(CAST(area_water AS FLOAT)/area_land, 5) AS water_land_ratio
FROM facts
WHERE name <> "World"
AND
water_land_ratio > 1
ORDER BY water_land_ratio DESC;
Done.
country | water_land_ratio |
---|---|
British Indian Ocean Territory | 905.66667 |
Virgin Islands | 4.52023 |
The data shows British Indian Ocean Territory and Virgin Islands have more water area than land.
%%sql
SELECT name AS country,
MAX((population_growth/100) * population) AS highest_population_increase
FROM facts
where country <> "World";
Done.
country | highest_population_increase |
---|---|
India | 15270686.124799998 |
India will add the most population next year which is around 15.27 million.
%%sql
SELECT name AS country, birth_rate, death_rate
FROM facts
WHERE death_rate > birth_rate
AND
country <> "World"
ORDER BY death_rate DESC;
Done.
country | birth_rate | death_rate |
---|---|---|
Ukraine | 10.72 | 14.46 |
Bulgaria | 8.92 | 14.44 |
Latvia | 10.0 | 14.31 |
Lithuania | 10.1 | 14.27 |
Russia | 11.6 | 13.69 |
Serbia | 9.08 | 13.66 |
Belarus | 10.7 | 13.36 |
Hungary | 9.16 | 12.73 |
Moldova | 12.0 | 12.59 |
Estonia | 10.51 | 12.4 |
Croatia | 9.45 | 12.18 |
Romania | 9.14 | 11.9 |
Germany | 8.47 | 11.42 |
Slovenia | 8.42 | 11.37 |
Greece | 8.66 | 11.09 |
Portugal | 9.27 | 11.02 |
Czech Republic | 9.63 | 10.34 |
Italy | 8.74 | 10.19 |
Poland | 9.74 | 10.19 |
Bosnia and Herzegovina | 8.87 | 9.75 |
Saint Pierre and Miquelon | 7.42 | 9.72 |
Japan | 7.93 | 9.51 |
Austria | 9.41 | 9.42 |
Monaco | 6.65 | 9.24 |
%%sql
SELECT COUNT(*) AS country_count
FROM facts
WHERE death_rate > birth_rate
AND
name <> "World";
Done.
country_count |
---|
24 |
The total number of people per unit area defines the population density. Let's take a look at list of countries in accordance with population density
%%sql
SELECT name AS country,
ROUND(CAST(population AS FLOAT)/area, 3) AS population_density
FROM facts
WHERE country <> "World"
ORDER BY population_density DESC
LIMIT 10;
Done.
country | population_density |
---|---|
Macau | 21168.964 |
Monaco | 15267.5 |
Singapore | 8141.28 |
Hong Kong | 6445.042 |
Gaza Strip | 5191.819 |
Gibraltar | 4876.333 |
Bahrain | 1771.859 |
Maldives | 1319.641 |
Malta | 1310.016 |
Bermuda | 1299.926 |
Macau is the one with highest population density.