<jupyter_start><jupyter_text>Extracting Vital Statistics from the CIA World Factbook with SQLite Executive SummaryIn this project, I utilized SQLite to extract demographic and geographic data from the CIA World Factbook. I identified China as the most populous locale tracked by the CIA, and the Pitcairn Islands as the least populous. Malawi had the highest rate of population growth, and Bulgaria had the highest rate of population contraction (both excluding immigration and emigration). Greenland had the lowest population density; Macau, the largest. Finally, Puerto Rico had the largest water-to-land-area ratio. IntroductionThe **['CIA World Factbook'](https://www.cia.gov/the-world-factbook/)** is a datasource that provides vital statistics of 266 world 'locales'. Locales can be:* Countries* Regions* Oceans* Unoccupied IslandsIn this project, I use SQLite to work with a ***small subset*** of the CIA factbook to identify the maximum and minimum values of:* Population* Population Growth* Population Density* Land-to-Water Ratio  Setup Jupyter to use SQLite<jupyter_code>%%capture
%load_ext sql
%sql sqlite:///DataSets/CIA_Factbook/factbook.db
%%sql
SELECT *
  FROM sqlite_master
 WHERE type = 'table';<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>Data FormatAs mentioned above, I will be working with a small subset of complete CIA World Factbook. Definitions for the columns in the factbook can be found [here](https://www.cia.gov/the-world-factbook/references/definitions-and-notes/).In this data subset, the following columns are used:1. code = a unique 2 letter locale code.2. name = the name of the locale.3. area = the total area of the locale in square km.4. area_land = the land area of the locale in square km.5. area_water = the area of sovreign water occupied by the locale in square km.6. population = the total population of the locale.7. population_growth = population growth rate in percent.8. birth_rate = the number of births per 1,000 persons.9. death_rate = the number of deaths per 1,000 persons.10. migration_rate = the net migration rate per 1,000 persons. In this dataset, I am unable to determine if there is a correct sign on the migration rate, since it is not possible for all countries to have a migration rate >= 0.  Analysis  PopulationA first glance at the maximum and minimum populations listed in the factbook show that the maximum population is over 7 billion people, and the miminum population is 0.<jupyter_code>%%sql
SELECT MIN(population) AS 'Lowest Population',
       MAX(population) AS 'Highest Population',
       MIN(population_growth) AS 'Fastest Growth',
       MAX(population_growth) AS 'Slowest Growth'
  FROM facts<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>Further examination shows that this region with the maximum population is 'The World', while the region(s) with the minimum populations are unoccupied islands and Antarctica.<jupyter_code>%%sql
SELECT name,
       population
  FROM facts
 ORDER BY population
 LIMIT 20;<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>When we exclude these non-country maximum and minimum locales, we can find the largest and smallest countries in the world.<jupyter_code>%%sql
SELECT name AS 'Most Populous Locale',
       MAX(population) AS 'Population'
  FROM facts
 WHERE population < (SELECT MAX(population)
                                FROM facts)
 LIMIT 1;
%%sql
SELECT name AS 'Least Populous Locale',
       MIN(population) AS 'Population'
  FROM facts
 WHERE population > 0
 LIMIT 1;<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>Population GrowthI define the population growth rate as the birth rate less the death rate in each locale. Due to the unknown sign of the migration rate within the dataset, I will ignore its contribution to population growth (or decline)<jupyter_code>%%sql
SELECT name AS 'Fastest Growing Locale',
       ROUND(MAX(birth_rate - death_rate),2) AS 'Net population change per 1,000 persons'
  FROM facts
%%sql
SELECT name AS 'Slowest Growing Locale',
       ROUND(MIN(birth_rate - death_rate),2) AS 'Net population change per 1,000 persons'
  FROM facts<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>Population DensityI define this as the reported locale population divided by the **land** area of the locale (different calculations choose to [include or exclude](https://en.wikipedia.org/wiki/Population_densityHuman_densities) water area)<jupyter_code>%%sql
SELECT name AS 'Highest population density',
       area_land AS 'Land Area (sq km)',
       population AS 'Population',
       ROUND(MAX(CAST(population AS FLOAT) / area_land),2) AS 'Persons per square km'
  FROM facts
%%sql
SELECT name AS 'Lowest population density',
       area_land AS 'Land Area (sq km)',
       population AS 'Population',
       ROUND((MIN(CAST(population AS FLOAT) / area_land)),2) AS 'Persons per square km'
  FROM facts
 WHERE (CAST(population AS FLOAT) / area_land) > 0
 ORDER BY (CAST(population AS FLOAT) / area_land)<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>Water to Land RatioThe ratio of water to land for a given locale can vary from 0 (a landlocked locale) to near infinite (a tiny island).An initial sampling of all locales with a water area of zero reveals that many island nations are listed as having a water area of zero. My assumption is that the water area reported in the Factbook is the sum of [Territorial Waters](https://en.wikipedia.org/wiki/Territorial_waters) and [Internal Waters](https://en.wikipedia.org/wiki/Internal_waters). Generally, *territorial waters* extend 12 nautical miles beyond a country's coast, so island nations should have roughly:$$a_{water} = 44\pi(11+r_{land})$$Where all variables are given in sq km, and an island is assumed to be circular.<jupyter_code>%%sql
SELECT name AS 'Locale',
       area_land AS 'Land Area (sq km)',
       area_water AS 'Water Area (sq km)'
  FROM facts
 WHERE area_water = 0<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.<jupyter_text>To circumvent this issue, I attempted to exclude these mislabeled small islands from the calculation by removing locales with area_water = 0 and area_land < 1000 sq km.**This filtering process also removed any truly landlocked countries from analysis.** The remaining entries in the list were countries with a land area > 1,000 sq km which had a non-zero water area.<jupyter_code>%%sql
SELECT name AS 'Locale',
       area_land AS 'Land Area (sq km)',
       area_water AS 'Water Area (sq km)',
       ROUND((CAST(area_water AS FLOAT) / area_land),5) AS 'Water-Land Ratio'
  FROM facts
 WHERE (ROUND((CAST(area_water AS FLOAT) / area_land),5) != 0)
       AND (area_land > 1000)
 ORDER BY ROUND((CAST(area_water AS FLOAT) / area_land),5) DESC
 LIMIT 5<jupyter_output>* sqlite:///DataSets/CIA_Factbook/factbook.db
Done.
{'fraction_non_alphanumeric': 0.07929011020466581, 'fraction_numerical': 0.010591097752969802, 'mean_word_length': 4.603849238171612, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 30, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '352624', 'license': 'MIT', 'n_tokens_mistral': 2071, 'n_tokens_neox': 1929, 'n_words': 847, 'path': 'CIA_Factbook_SQL.ipynb', 'repo_name': 'jack-edelberg/Exploring-CIA-Factbook-SQL', 'size': '6987'}