Don't Copy The Seal¶

or¶

A Quick Look at the CIA World Factbook¶

The CIA World Factbook is an almanac-style document that "provides basic intelligence on the history, people, government, economy, energy, geography, environment, communications, transportation, military, terrorism, and transnational issues for 266 world entities" (1). It was first made available publicly in 1971 and has been online since 1994 (2). For it's purpose, it is a very thorough document. There are some notable entries like the World, five oceans, and some miscellaneous locations. Perhaps unsurprisingly, because of the nature of the agency that writes it, there is some controversy over entries for disputed locations. Factual errors, such as population discrepancies have also been found (3).

This notebook will use SQLite to take a quick look at a dataset for the entire document.

One last note, while the source material and information are in the public domain and downloadable, don't make a copy of the official seal of the CIA without permission! That is prohibited by U.S. federal law—specifically, the Central Intelligence Agency Act of 1949 (50 U.S.C. § 403m). ;)

Let's get started!¶

In [1]:

%%capture
%load_ext sql
%sql sqlite:///factbook.db

In [2]:

%%sql
SELECT *
  FROM sqlite_master
 WHERE type='table';

 * sqlite:///factbook.db
Done.

Out[2]:

type	name	tbl_name	rootpage	sql
table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)
table	facts	facts	47	CREATE TABLE "facts" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "code" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, "area" integer, "area_land" integer, "area_water" integer, "population" integer, "population_growth" float, "birth_rate" float, "death_rate" float, "migration_rate" float)

In [3]:

%%sql
SELECT *
  FROM facts
 LIMIT 5;

 * sqlite:///factbook.db
Done.

Out[3]:

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

In [4]:

%%sql
-- row count
SELECT COUNT (*)
  FROM facts;

 * sqlite:///factbook.db
Done.

Out[4]:

COUNT (*)
261

File Info
The file opens fine. There are 261 rows and 11 columns with pretty straightforward column titles. It would be helpful to know the units for area and how the rates are calculated.

Demographics¶

Just some basics.

In [5]:

%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
  FROM facts;

 * sqlite:///factbook.db
Done.

Out[5]:

MIN(population)	MAX(population)	MIN(population_growth)	MAX(population_growth)
0	7256490011	0.0	4.02

In [6]:

%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MIN(population)
                       FROM facts);

 * sqlite:///factbook.db
Done.

Out[6]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
250	ay	Antarctica	None	280000	None	0	None	None	None	None

In [7]:

%%sql
SELECT *
  FROM facts
 WHERE population < 100
 LIMIT 10;

 * sqlite:///factbook.db
Done.

Out[7]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
238	pc	Pitcairn Islands	47	47	0	48	0.0	None	None	None
250	ay	Antarctica	None	280000	None	0	None	None	None	None

In [8]:

%%sql
SELECT *
  FROM facts
 WHERE population = 'None'
 LIMIT 50;

 * sqlite:///factbook.db
Done.

Out[8]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate

In [9]:

%%sql
SELECT *
  FROM facts
 ORDER BY population
 LIMIT 45;

 * sqlite:///factbook.db
Done.

Out[9]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
198	at	Ashmore and Cartier Islands	5	5	0	None	None	None	None	None
201	cr	Coral Sea Islands	3	3	0	None	None	None	None	None
202	hm	Heard Island and McDonald Islands	412	412	0	None	None	None	None	None
208	ip	Clipperton Island	6	6	0	None	None	None	None	None
210	fs	French Southern and Antarctic Lands	None	None	None	None	None	None	None	None
222	bv	Bouvet Island	49	49	0	None	None	None	None	None
223	jn	Jan Mayen	377	377	0	None	None	None	None	None
228	io	British Indian Ocean Territory	54400	60	54340	None	None	None	None	None
240	sx	South Georgia and South Sandwich Islands	3903	3903	0	None	None	None	None	None
244	bq	Navassa Island	5	5	0	None	None	None	None	None
248	wq	Wake Island	6	6	0	None	None	None	None	None
249	um	United States Pacific Island Wildlife Refuges	None	None	None	None	None	None	None	None
252	pf	Paracel Islands	7	7	0	None	None	None	None	None
253	pg	Spratly Islands	5	5	0	None	None	None	None	None
256	xq	Arctic Ocean	None	None	None	None	None	None	None	None
257	zh	Atlantic Ocean	None	None	None	None	None	None	None	None
258	xo	Indian Ocean	None	None	None	None	None	None	None	None
259	zn	Pacific Ocean	None	None	None	None	None	None	None	None
260	oo	Southern Ocean	None	None	None	None	None	None	None	None
250	ay	Antarctica	None	280000	None	0	None	None	None	None
238	pc	Pitcairn Islands	47	47	0	48	0.0	None	None	None
200	ck	Cocos (Keeling) Islands	14	14	0	596	0.0	None	None	None
190	vt	Holy See (Vatican City)	0	0	0	842	0.0	None	None	None
220	ne	Niue	260	260	0	1190	0.03	None	None	None
221	tl	Tokelau	12	12	0	1337	0.01	None	None	None
199	kt	Christmas Island	135	135	0	1530	1.11	None	None	None
224	sv	Svalbard	62045	62045	0	1872	0.03	None	None	None
203	nf	Norfolk Island	36	36	0	2210	0.01	None	None	None
232	fk	Falkland Islands (Islas Malvinas)	12173	12173	0	3361	0.01	10.9	4.9	None
237	mh	Montserrat	102	102	0	5241	0.5	11.26	6.3	0.0
214	sb	Saint Pierre and Miquelon	242	242	0	5657	1.08	7.42	9.72	8.49
212	tb	Saint Barthelemy	None	None	None	7237	None	None	None	None
239	sh	Saint Helena, Ascension, and Tristan da Cunha	308	122	0	7795	0.24	9.88	7.44	0.0
123	nr	Nauru	21	21	0	9540	0.55	24.95	5.87	13.63
219	cw	Cook Islands	236	236	0	9838	2.95	14.33	8.03	None
181	tv	Tuvalu	26	26	0	10869	0.82	23.74	8.74	6.81
215	wf	Wallis and Futuna	142	142	0	15613	0.33	13.45	5.06	5.06
225	ax	Akrotiri	123	None	None	15700	None	None	None	None
231	dx	Dhekelia	130	None	None	15700	None	None	None	None
226	av	Anguilla	91	91	0	16418	2.03	12.67	4.57	12.18
133	ps	Palau	459	459	0	21265	0.38	11.05	7.99	0.71
233	gi	Gibraltar	6	6	0	29258	0.24	14.08	8.37	3.28
117	mn	Monaco	2	2	0	30535	0.12	6.65	9.24	3.83
213	rn	Saint Martin	54	54	None	31754	None	None	None	None
149	sm	San Marino	61	61	0	33020	0.82	8.63	8.45	8.03

In [10]:

%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MAX(population)
                       FROM facts);

 * sqlite:///factbook.db
Done.

Out[10]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
261	xx	World	None	None	None	7256490011	1.08	18.6	7.8	None

In [11]:

%%sql
SELECT *
  FROM facts
 ORDER BY population DESC
 LIMIT 5;

 * sqlite:///factbook.db
Done.

Out[11]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
261	xx	World	None	None	None	7256490011	1.08	18.6	7.8	None
37	ch	China	9596960	9326410	270550	1367485388	0.45	12.49	7.53	0.44
77	in	India	3287263	2973193	314070	1251695584	1.22	19.55	7.32	0.04
197	ee	European Union	4324782	None	None	513949445	0.25	10.2	10.2	2.5
186	us	United States	9826675	9161966	664709	321368864	0.78	12.49	8.15	3.86

Outliers
The only location with a population of 0 is Antarctica. There are a number of places with a value of None in this column, but I can't seem to capture that value in a query. The only row with a true outlier on the high end is World.

Population growth¶

Minimums and maximums.

In [12]:

%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
  FROM facts
 WHERE population != 0 AND name != 'World';

 * sqlite:///factbook.db
Done.

Out[12]:

MIN(population)	MAX(population)	MIN(population_growth)	MAX(population_growth)
48	1367485388	0.0	4.02

In [13]:

%%sql
SELECT *
  FROM facts
 WHERE population < 100;

 * sqlite:///factbook.db
Done.

Out[13]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
238	pc	Pitcairn Islands	47	47	0	48	0.0	None	None	None
250	ay	Antarctica	None	280000	None	0	None	None	None	None

In [14]:

%%sql
SELECT *
  FROM facts
 WHERE population > 1000000000;

 * sqlite:///factbook.db
Done.

Out[14]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
37	ch	China	9596960	9326410	270550	1367485388	0.45	12.49	7.53	0.44
77	in	India	3287263	2973193	314070	1251695584	1.22	19.55	7.32	0.04
261	xx	World	None	None	None	7256490011	1.08	18.6	7.8	None

In [15]:

%%sql
SELECT *
  FROM facts
 WHERE population_growth = (SELECT MIN(population_growth)
                       FROM facts);

 * sqlite:///factbook.db
Done.

Out[15]:

id	code	name	area	area_land	area_water	population	birth_rate	death_rate	migration_rate
190	vt	Holy See (Vatican City)	0	0	0	842	None	None	None
200	ck	Cocos (Keeling) Islands	14	14	0	596	None	None	None
207	gl	Greenland	2166086	2166086	None	57733	14.48	8.49	5.98
238	pc	Pitcairn Islands	47	47	0	48	None	None	None

In [16]:

%%sql
SELECT *
  FROM facts
 WHERE population_growth = (SELECT MAX(population_growth)
                       FROM facts);

 * sqlite:///factbook.db
Done.

Out[16]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
162	od	South Sudan	644329	None	None	12042910	4.02	36.91	8.18	11.47

In [17]:

%%sql
SELECT *
  FROM facts
 WHERE population_growth > (SELECT AVG(population_growth)
                       FROM facts)
 ORDER BY population_growth DESC;

 * sqlite:///factbook.db
Done.

Out[17]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
162	od	South Sudan	644329	None	None	12042910	4.02	36.91	8.18	11.47
106	mi	Malawi	118484	94080	24404	17964697	3.32	41.56	8.41	0.0
29	by	Burundi	27830	25680	2150	10742276	3.28	42.01	9.27	0.0
128	ng	Niger	None	1266700	300	18045729	3.25	45.45	12.42	0.56
182	ug	Uganda	241038	197100	43938	37101745	3.24	43.79	10.69	0.74
141	qa	Qatar	11586	11586	0	2194817	3.07	9.84	1.53	22.39
27	uv	Burkina Faso	274200	273800	400	18931686	3.03	42.03	11.72	0.0
109	ml	Mali	1240192	1220190	20002	16955536	2.98	44.99	12.89	2.26
219	cw	Cook Islands	236	236	0	9838	2.95	14.33	8.03	None
80	iz	Iraq	438317	437367	950	37056169	2.93	31.45	3.77	1.62
58	et	Ethiopia	1104300	None	104300	99465819	2.89	37.27	8.19	0.22
194	za	Zambia	752618	743398	9220	15066266	2.88	42.13	12.67	0.68
255	wi	Western Sahara	266000	266000	0	570866	2.82	30.24	8.34	None
251	gz	Gaza Strip	360	360	0	1869055	2.81	31.11	3.04	0.0
172	tz	Tanzania	947300	885800	61500	51045882	2.79	36.39	8.0	0.54
5	ao	Angola	1246700	1246700	0	19625353	2.78	38.78	11.49	0.46
19	bn	Benin	112622	110622	2000	10448647	2.78	36.02	8.21	0.0
175	to	Togo	56785	54385	2400	7552318	2.69	34.13	7.26	0.0
70	gv	Guinea	245857	245717	140	11780162	2.63	35.74	9.46	0.0
31	cm	Cameroon	475440	472710	2730	23739218	2.59	36.17	10.11	0.15
105	ma	Madagascar	587041	581540	5501	23812681	2.58	32.61	6.81	0.0
184	ae	United Arab Emirates	83600	83600	0	5779760	2.58	15.43	1.97	12.36
144	rw	Rwanda	26338	24668	1670	12661733	2.56	33.75	8.96	0.85
55	ek	Equatorial Guinea	28051	28051	0	740743	2.51	33.31	8.19	0.0
99	li	Liberia	111369	96320	15049	4195666	2.47	34.41	9.69	0.0
193	ym	Yemen	527968	527968	0	26737317	2.47	29.98	6.28	1.0
40	cg	Congo, Democratic Republic of the	2344858	2267048	77810	79375136	2.45	34.88	10.07	0.27
121	mz	Mozambique	799380	786380	13000	25303113	2.45	38.58	12.1	1.98
129	ni	Nigeria	923768	910768	13000	181562056	2.45	37.64	12.9	0.22
152	sg	Senegal	196722	192530	4192	13975834	2.45	34.52	8.46	1.59
174	tt	Timor-Leste	14874	14874	0	1231116	2.42	34.16	6.1	3.86
13	ba	Bahrain	760	760	0	1346613	2.41	13.66	2.69	13.09
155	sl	Sierra Leone	71740	71620	120	5879098	2.35	37.03	10.81	2.77
1	af	Afghanistan	652230	652230	0	32564342	2.32	38.57	13.89	1.51
229	vi	British Virgin Islands	151	151	0	33454	2.32	10.91	4.99	17.28
241	tk	Turks and Caicos Islands	948	948	0	50280	2.3	16.13	3.1	9.94
56	er	Eritrea	117600	101000	16600	6527689	2.25	30.0	7.52	0.0
100	ly	Libya	1759540	1759540	0	6411776	2.23	18.03	3.58	7.8
112	mr	Mauritania	1030700	1030700	0	3596702	2.23	31.34	8.2	0.83
195	zi	Zimbabwe	390757	386847	3910	14229541	2.21	32.26	10.13	0.0
49	dj	Djibouti	23200	23180	20	828324	2.2	23.65	7.73	6.06
66	gh	Ghana	238533	227533	11000	26327649	2.18	31.09	7.22	2.02
245	cq	Northern Mariana Islands	464	464	0	52344	2.18	18.32	3.71	7.16
63	ga	Gambia, The	11300	10120	1180	1967709	2.16	30.86	7.15	2.12
34	ct	Central African Republic	622984	622984	0	5391539	2.13	35.08	13.8	0.0
103	lu	Luxembourg	2586	2586	0	570252	2.13	11.37	7.24	17.16
230	cj	Cayman Islands	264	264	0	56092	2.1	12.11	5.53	14.4
131	mu	Oman	309500	309500	0	3286936	2.07	24.44	3.36	0.43
226	av	Anguilla	91	91	0	16418	2.03	12.67	4.57	12.18
159	bp	Solomon Islands	28896	27986	910	622469	2.02	25.77	3.85	1.75
41	cf	Congo, Republic of the	342000	341500	500	4755097	2.0	35.85	10.0	5.9
189	nh	Vanuatu	12189	12189	0	272264	1.95	25.04	4.09	1.47
254	we	West Bank	5860	5640	220	2785366	1.95	22.99	3.5	0.0
62	gb	Gabon	267667	257667	10000	1705336	1.93	34.49	13.12	2.03
88	ke	Kenya	580367	569140	11227	45925301	1.93	26.4	6.89	0.22
43	iv	Cote d'Ivoire	322463	318003	4460	23295302	1.91	28.67	9.55	0.0
71	pu	Guinea-Bissau	36125	28120	8005	1726170	1.91	33.38	14.33	0.0
35	cd	Chad	None	1259200	24800	11631456	1.89	36.6	14.28	3.45
156	sn	Singapore	697	687	10	5674472	1.89	8.27	3.43	14.05
18	bh	Belize	22966	22806	160	347369	1.87	24.68	5.97	0.0
3	ag	Algeria	2381741	2381741	0	39542166	1.84	23.67	4.31	0.92
150	tp	Sao Tome and Principe	964	964	0	194006	1.84	34.23	7.24	8.63
160	so	Somalia	637657	627337	10320	10616380	1.83	40.45	13.62	8.49
69	gt	Guatemala	108889	107159	1730	14918999	1.82	24.89	4.77	1.97
53	eg	Egypt	1001450	995450	6000	88487396	1.79	22.9	4.77	0.19
124	np	Nepal	147181	143351	3830	31551305	1.79	20.64	6.56	3.86
135	pp	Papua New Guinea	462840	452860	9980	6672429	1.78	24.38	6.53	0.0
39	cn	Comoros	2235	2235	0	780971	1.77	27.84	7.57	2.53
165	su	Sudan	1861484	None	None	36108853	1.72	29.19	7.66	4.29
171	ti	Tajikistan	144100	141510	2590	8191958	1.71	24.38	6.18	1.15
74	ho	Honduras	112090	111890	200	8746673	1.68	23.14	5.17	1.16
111	rm	Marshall Islands	181	181	0	72191	1.66	25.6	4.21	4.83
25	bx	Brunei	5765	5265	500	429646	1.62	17.32	3.52	2.43
93	ku	Kuwait	17818	17818	0	2788534	1.62	19.91	2.18	1.58
138	rp	Philippines	300000	298170	1830	100998376	1.61	24.27	6.11	2.09
14	bg	Bangladesh	148460	130170	18290	168957745	1.6	21.14	5.61	0.46
30	cb	Cambodia	181035	176515	4520	15708756	1.58	23.83	7.68	0.32
21	bl	Bolivia	1098581	1083301	15280	10800882	1.56	22.76	6.52	0.62
82	is	Israel	20770	20330	440	8049314	1.56	18.48	5.15	2.24
95	la	Laos	236800	230800	6000	6911544	1.55	24.25	7.63	1.09
218	sk	Sint Maarten	34	34	0	39689	1.51	13.0	4.51	6.63
132	pk	Pakistan	796095	770875	25220	199085847	1.46	22.58	6.49	1.54
151	sa	Saudi Arabia	2149690	2149690	0	27752316	1.46	18.51	3.33	0.55
107	my	Malaysia	329847	328657	1190	30513848	1.44	19.71	5.03	0.33
46	cy	Cyprus	9251	9241	10	1189197	1.43	11.41	6.62	9.48
191	ve	Venezuela	912050	882050	30000	29275460	1.39	19.16	5.31	0.0
211	nc	New Caledonia	18575	18275	300	271615	1.38	15.33	5.52	4.01
33	cv	Cabo Verde	4033	4033	0	545993	1.36	20.33	6.11	0.63
52	ec	Ecuador	283561	276841	6720	15868396	1.35	18.51	5.06	0.0
161	sf	South Africa	1219090	1214470	4620	53675563	1.33	20.75	9.91	2.42
216	aa	Aruba	180	180	0	112162	1.33	12.56	8.18	8.92
134	pm	Panama	75420	74340	1080	3657024	1.32	18.32	4.81	0.28
118	mg	Mongolia	1564116	1553556	10560	2992908	1.31	20.25	6.35	0.84
179	tu	Turkey	783562	769632	13930	79414269	1.26	16.33	5.88	2.16
81	ei	Ireland	70273	68883	1390	4892305	1.25	14.84	6.48	4.09
6	ac	Antigua and Barbuda	442	442	0	92436	1.24	15.85	5.69	2.21
51	dr	Dominican Republic	48670	48320	350	10478756	1.23	18.73	4.55	1.91
42	cs	Costa Rica	51100	51060	40	4814144	1.22	15.91	4.55	0.83
77	in	India	3287263	2973193	314070	1251695584	1.22	19.55	7.32	0.04
23	bc	Botswana	581730	566730	15000	2182719	1.21	20.96	13.39	4.56
76	ic	Iceland	103000	100250	2750	331918	1.21	13.91	6.28	4.43

Observations
The lowest population, other than zero or none, is 48 in the Pitcairn Islands. The highest population is 1,367,485,388 in China. India also has a population over one billion. The locations with no population growth are Vatican City, Cocos Island, Greenland, and the Pitcairn Islands. South Sudan has the highest population growth. Notably, it is the only place where this value is more than 4. A quick look at other locations with high population growth shows that some places, like Qatar, increase population primarily through migration rate. This may be worth further comparison.

Population density¶

Looking at population density two ways. First, using average population and average area (excluding the World and Antarctica) to determine a ratio, then looking at locations that have an above average ratio. Second, selecting locations that meet the double criteria of having above average population and above average area (i.e. skipping the ratio).

In [18]:

%%sql
SELECT *
  FROM facts
 WHERE (population/area) > (SELECT AVG(population)/AVG(area)
                              FROM facts
                             WHERE name != 'World' AND name != 'Antarctica')
 ORDER BY (population/area) DESC;

 * sqlite:///factbook.db
Done.

Out[18]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
205	mc	Macau	28	28	0	592731	0.8	8.88	4.22	3.37
117	mn	Monaco	2	2	0	30535	0.12	6.65	9.24	3.83
156	sn	Singapore	697	687	10	5674472	1.89	8.27	3.43	14.05
204	hk	Hong Kong	1108	1073	35	7141106	0.38	9.23	7.07	1.68
251	gz	Gaza Strip	360	360	0	1869055	2.81	31.11	3.04	0.0
233	gi	Gibraltar	6	6	0	29258	0.24	14.08	8.37	3.28
13	ba	Bahrain	760	760	0	1346613	2.41	13.66	2.69	13.09
108	mv	Maldives	298	298	0	393253	0.08	15.75	3.89	12.68
110	mt	Malta	316	316	0	413965	0.31	10.18	9.09	1.98
227	bd	Bermuda	54	54	0	70196	0.5	11.33	8.23	1.88
218	sk	Sint Maarten	34	34	0	39689	1.51	13.0	4.51	6.63
14	bg	Bangladesh	148460	130170	18290	168957745	1.6	21.14	5.61	0.46
234	gk	Guernsey	78	78	0	66080	0.34	9.84	8.78	2.32
235	je	Jersey	116	116	0	97294	0.8	11.91	7.68	3.76
15	bb	Barbados	430	430	0	290604	0.31	11.87	8.44	0.3
113	mp	Mauritius	2040	2030	10	1339827	0.64	13.29	6.91	0.0
196	tw	Taiwan	35980	32260	3720	23415126	0.23	8.47	7.11	0.89
216	aa	Aruba	180	180	0	112162	1.33	12.56	8.18	8.92
97	le	Lebanon	10400	10230	170	6184701	0.86	14.59	4.88	1.1
213	rn	Saint Martin	54	54	None	31754	None	None	None	None
149	sm	San Marino	61	61	0	33020	0.82	8.63	8.45	8.03
91	ks	Korea, South	99720	96920	2800	49115196	0.14	8.19	6.75	0.0
144	rw	Rwanda	26338	24668	1670	12661733	2.56	33.75	8.96	0.85
254	we	West Bank	5860	5640	220	2785366	1.95	22.99	3.5	0.0
123	nr	Nauru	21	21	0	9540	0.55	24.95	5.87	13.63
181	tv	Tuvalu	26	26	0	10869	0.82	23.74	8.74	6.81
125	nl	Netherlands	41543	33893	7650	16947904	0.41	10.83	8.66	1.95
111	rm	Marshall Islands	181	181	0	72191	1.66	25.6	4.21	4.83
82	is	Israel	20770	20330	440	8049314	1.56	18.48	5.15	2.24
29	by	Burundi	27830	25680	2150	10742276	3.28	42.01	9.27	0.0
77	in	India	3287263	2973193	314070	1251695584	1.22	19.55	7.32	0.04
17	be	Belgium	30528	30278	250	11323973	0.76	11.41	9.63	5.87
73	ha	Haiti	27750	27560	190	10110019	1.17	22.31	7.83	2.76
39	cn	Comoros	2235	2235	0	780971	1.77	27.84	7.57	2.53
138	rp	Philippines	300000	298170	1830	100998376	1.61	24.27	6.11	2.09
164	ce	Sri Lanka	65610	64630	980	22053488	0.84	15.85	6.11	1.35
85	ja	Japan	377915	364485	13430	126919659	0.16	7.93	9.51	0.0
217	cc	Curacao	444	444	0	148406	0.43	13.8	8.2	1.4
68	gj	Grenada	344	344	0	110694	0.48	16.03	8.08	3.13
243	gq	Guam	544	544	0	161785	0.54	16.82	5.12	6.34
54	es	El Salvador	21041	20721	320	6141350	0.25	16.46	5.69	8.28
192	vm	Vietnam	331210	310070	21140	94348835	0.97	15.96	5.93	0.3
242	aq	American Samoa	199	199	0	54343	0.3	22.89	4.75	21.13
84	jm	Jamaica	10991	10831	160	2950210	0.68	18.16	6.7	4.66
146	st	Saint Lucia	616	606	10	163922	0.34	13.7	7.42	2.93
147	vc	Saint Vincent and the Grenadines	389	389	0	102627	0.28	13.57	7.18	9.17
185	uk	United Kingdom	243610	241930	1680	64088222	0.54	12.17	9.35	2.54
246	rq	Puerto Rico	13791	8870	4921	3598357	0.6	10.86	8.67	8.15
132	pk	Pakistan	796095	770875	25220	199085847	1.46	22.58	6.49	1.54
177	td	Trinidad and Tobago	5128	5128	0	1222363	0.13	13.46	8.56	6.25
101	ls	Liechtenstein	160	160	0	37624	0.84	10.45	7.12	5.08
65	gm	Germany	357022	348672	8350	80854408	0.17	8.47	11.42	1.24
229	vi	British Virgin Islands	151	151	0	33454	2.32	10.91	4.99	17.28
103	lu	Luxembourg	2586	2586	0	570252	2.13	11.37	7.24	17.16
51	dr	Dominican Republic	48670	48320	350	10478756	1.23	18.73	4.55	1.91
124	np	Nepal	147181	143351	3830	31551305	1.79	20.64	6.56	3.86
230	cj	Cayman Islands	264	264	0	56092	2.1	12.11	5.53	14.4
6	ac	Antigua and Barbuda	442	442	0	92436	1.24	15.85	5.69	2.21
90	kn	Korea, North	120538	120408	130	24983205	0.53	14.52	9.21	0.04
83	it	Italy	301340	294140	7200	61855120	0.27	8.74	10.19	4.1
154	se	Seychelles	455	455	0	92430	0.83	14.19	6.89	1.0
150	tp	Sao Tome and Principe	964	964	0	194006	1.84	34.23	7.24	8.63
145	sc	Saint Kitts and Nevis	261	261	0	51936	0.76	13.5	7.09	1.21
129	ni	Nigeria	923768	910768	13000	181562056	2.45	37.64	12.9	0.22
169	sz	Switzerland	41277	39997	1280	8121830	0.71	10.5	8.13	4.74
141	qa	Qatar	11586	11586	0	2194817	3.07	9.84	1.53	22.39
4	an	Andorra	468	468	0	85580	0.12	8.13	6.96	0.0
226	av	Anguilla	91	91	0	16418	2.03	12.67	4.57	12.18
63	ga	Gambia, The	11300	10120	1180	1967709	2.16	30.86	7.15	2.12
92	kv	Kosovo	10887	10887	0	1870981	None	None	None	None
93	ku	Kuwait	17818	17818	0	2788534	1.62	19.91	2.18	1.58
182	ug	Uganda	241038	197100	43938	37101745	3.24	43.79	10.69	0.74
236	im	Isle of Man	572	572	0	87545	0.76	11.1	10.06	6.56
106	mi	Malawi	118484	94080	24404	17964697	3.32	41.56	8.41	0.0
115	fm	Micronesia, Federated States of	702	702	0	105216	0.46	20.54	4.23	20.93
37	ch	China	9596960	9326410	270550	1367485388	0.45	12.49	7.53	0.44
176	tn	Tonga	747	717	30	106501	0.03	23.0	4.85	17.84
69	gt	Guatemala	108889	107159	1730	14918999	1.82	24.89	4.77	1.97
33	cv	Cabo Verde	4033	4033	0	545993	1.36	20.33	6.11	0.63
47	ez	Czech Republic	78867	77247	1620	10644842	0.16	9.63	10.34	2.33
78	id	Indonesia	1904569	1811569	93000	255993674	0.92	16.72	6.37	1.16
173	th	Thailand	513120	510890	2230	67976405	0.34	11.19	7.8	0.0
175	to	Togo	56785	54385	2400	7552318	2.69	34.13	7.26	0.0
89	kr	Kiribati	811	811	0	105711	1.15	21.46	7.12	2.87
48	da	Denmark	43094	42434	660	5581503	0.22	10.27	10.25	2.2
46	cy	Cyprus	9251	9241	10	1189197	1.43	11.41	6.62	9.48
225	ax	Akrotiri	123	None	None	15700	None	None	None	None
139	pl	Poland	312685	304255	8430	38562189	0.09	9.74	10.19	0.46
231	dx	Dhekelia	130	None	None	15700	None	None	None	None
197	ee	European Union	4324782	None	None	513949445	0.25	10.2	10.2	2.5
140	po	Portugal	92090	91470	620	10825309	0.09	9.27	11.02	2.67
11	aj	Azerbaijan	86600	82629	3971	9780780	0.96	16.64	7.07	0.0
245	cq	Northern Mariana Islands	464	464	0	52344	2.18	18.32	3.71	7.16
157	lo	Slovakia	49035	48105	930	5445027	0.02	9.91	9.74	0.04
221	tl	Tokelau	12	12	0	1337	0.01	None	None	None
66	gh	Ghana	238533	227533	11000	26327649	2.18	31.09	7.22	2.02
215	wf	Wallis and Futuna	142	142	0	15613	0.33	13.45	5.06	5.06
75	hu	Hungary	93028	89608	3420	9897541	0.22	9.16	12.73	1.33
2	al	Albania	28748	27398	1350	3029278	0.3	12.92	6.58	3.3
116	md	Moldova	33851	32891	960	3546847	1.03	12.0	12.59	9.67
10	au	Austria	83871	82445	1426	8665550	0.55	9.41	9.42	5.56
61	fr	France	643801	640427	3374	66553766	0.43	12.38	9.16	1.09
8	am	Armenia	29743	28203	1540	3056382	0.15	13.61	9.34	5.8
179	tu	Turkey	783562	769632	13930	79414269	1.26	16.33	5.88	2.16
45	cu	Cuba	110860	109820	1040	11031433	0.15	9.9	7.72	3.66
50	do	Dominica	751	751	0	73607	0.21	15.41	7.91	5.38
158	si	Slovenia	20273	20151	122	1983412	0.26	8.42	11.37	0.37
163	sp	Spain	505370	498980	6390	48146134	0.89	9.64	9.04	8.31
42	cs	Costa Rica	51100	51060	40	4814144	1.22	15.91	4.55	0.83
19	bn	Benin	112622	110622	2000	10448647	2.78	36.02	8.21	0.0
107	my	Malaysia	329847	328657	1190	30513848	1.44	19.71	5.03	0.33
153	ri	Serbia	77474	77474	0	7176794	0.46	9.08	13.66	0.0
170	sy	Syria	185180	183630	1550	17064854	0.16	22.17	4.0	19.79
58	et	Ethiopia	1104300	None	104300	99465819	2.89	37.27	8.19	0.22
86	jo	Jordan	89342	88802	540	8117564	0.83	25.37	3.79	13.24
142	ro	Romania	238391	229891	8500	21666350	0.3	9.14	11.9	0.24
53	eg	Egypt	1001450	995450	6000	88487396	1.79	22.9	4.77	0.19
30	cb	Cambodia	181035	176515	4520	15708756	1.58	23.83	7.68	0.32
80	iz	Iraq	438317	437367	950	37056169	2.93	31.45	3.77	1.62
28	bm	Burma	676578	653508	23070	56320206	1.01	18.39	7.96	0.28
167	wz	Swaziland	17364	17204	160	1435613	1.11	24.67	13.56	0.0
174	tt	Timor-Leste	14874	14874	0	1231116	2.42	34.16	6.1	3.86
67	gr	Greece	131957	130647	1310	10775643	0.01	8.66	11.09	2.32
104	mk	Macedonia	25713	25433	280	2096015	0.2	11.55	9.08	0.48
155	sl	Sierra Leone	71740	71620	120	5879098	2.35	37.03	10.81	2.77
88	ke	Kenya	580367	569140	11227	45925301	1.93	26.4	6.89	0.22
44	hr	Croatia	56594	55974	620	4464844	0.13	9.45	12.18	1.39
74	ho	Honduras	112090	111890	200	8746673	1.68	23.14	5.17	1.16
22	bk	Bosnia and Herzegovina	51197	51187	10	3867055	0.13	8.87	9.75	0.38
25	bx	Brunei	5765	5265	500	429646	1.62	17.32	3.52	2.43
120	mo	Morocco	446550	446300	250	33322699	1.0	18.2	4.81	3.36
183	up	Ukraine	603550	579330	24220	44429471	0.6	10.72	14.46	2.25
43	iv	Cote d'Ivoire	322463	318003	4460	23295302	1.91	28.67	9.55	0.0
152	sg	Senegal	196722	192530	4192	13975834	2.45	34.52	8.46	1.59
64	gg	Georgia	69700	69700	0	4931226	0.08	12.74	10.82	2.7
27	uv	Burkina Faso	274200	273800	400	18931686	3.03	42.03	11.72	0.0
81	ei	Ireland	70273	68883	1390	4892305	1.25	14.84	6.48	4.09
148	ws	Samoa	2831	2821	10	197773	0.58	20.87	5.32	9.78
184	ae	United Arab Emirates	83600	83600	0	5779760	2.58	15.43	1.97	12.36
178	ts	Tunisia	163610	155360	8250	11037225	0.89	16.64	5.98	1.73
209	fp	French Polynesia	4167	3827	340	282703	0.94	15.22	5.02	0.84
188	uz	Uzbekistan	447400	425400	22000	29199942	0.93	17.0	5.3	2.37
26	bu	Bulgaria	110879	108489	2390	7186893	0.58	8.92	14.44	0.29
98	lt	Lesotho	30355	30355	0	1947701	0.32	25.47	14.89	7.36
114	mx	Mexico	1964375	1943945	20430	121736809	1.18	18.78	5.26	1.68
203	nf	Norfolk Island	36	36	0	2210	0.01	None	None	None

In [19]:

%%sql
SELECT COUNT(*)
  FROM facts
 WHERE (population/area) > (SELECT AVG(population)/AVG(area)
                              FROM facts
                             WHERE name != 'World' AND name != 'Antarctica');

 * sqlite:///factbook.db
Done.

Out[19]:

COUNT(*)
146

In [20]:

%%sql
SELECT *
  FROM facts
 WHERE (population/area) <= (SELECT AVG(population)/AVG(area)
                               FROM facts
                              WHERE name != 'World' AND name != 'Antarctica')
 ORDER BY population/area ASC;

 * sqlite:///factbook.db
Done.

Out[20]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
207	gl	Greenland	2166086	2166086	None	57733	0.0	14.48	8.49	5.98
224	sv	Svalbard	62045	62045	0	1872	0.03	None	None	None
232	fk	Falkland Islands (Islas Malvinas)	12173	12173	0	3361	0.01	10.9	4.9	None
118	mg	Mongolia	1564116	1553556	10560	2992908	1.31	20.25	6.35	0.84
238	pc	Pitcairn Islands	47	47	0	48	0.0	None	None	None
9	as	Australia	7741220	7682300	58920	22751014	1.07	12.15	7.14	5.65
122	wa	Namibia	824292	823290	1002	2212307	0.59	19.8	13.91	0.0
255	wi	Western Sahara	266000	266000	0	570866	2.82	30.24	8.34	None
23	bc	Botswana	581730	566730	15000	2182719	1.21	20.96	13.39	4.56
32	ca	Canada	9984670	9093507	891163	35099836	0.75	10.28	8.42	5.66
72	gy	Guyana	214969	196849	18120	735222	0.02	15.59	7.32	8.06
76	ic	Iceland	103000	100250	2750	331918	1.21	13.91	6.28	4.43
100	ly	Libya	1759540	1759540	0	6411776	2.23	18.03	3.58	7.8
112	mr	Mauritania	1030700	1030700	0	3596702	2.23	31.34	8.2	0.83
166	ns	Suriname	163820	156000	7820	579633	1.08	16.34	6.13	0.56
220	ne	Niue	260	260	0	1190	0.03	None	None	None
62	gb	Gabon	267667	257667	10000	1705336	1.93	34.49	13.12	2.03
87	kz	Kazakhstan	2724900	2699700	25200	18157122	1.14	19.15	8.21	0.41
34	ct	Central African Republic	622984	622984	0	5391539	2.13	35.08	13.8	0.0
143	rs	Russia	17098242	16377742	720500	142423773	0.04	11.6	13.69	1.69
21	bl	Bolivia	1098581	1083301	15280	10800882	1.56	22.76	6.52	0.62
131	mu	Oman	309500	309500	0	3286936	2.07	24.44	3.36	0.43
180	tx	Turkmenistan	488100	469930	18170	5231422	1.14	19.4	6.13	1.84
199	kt	Christmas Island	135	135	0	1530	1.11	None	None	None
151	sa	Saudi Arabia	2149690	2149690	0	27752316	1.46	18.51	3.33	0.55
41	cf	Congo, Republic of the	342000	341500	500	4755097	2.0	35.85	10.0	5.9
109	ml	Mali	1240192	1220190	20002	16955536	2.98	44.99	12.89	2.26
135	pp	Papua New Guinea	462840	452860	9980	6672429	1.78	24.38	6.53	0.0
211	nc	New Caledonia	18575	18275	300	271615	1.38	15.33	5.52	4.01
5	ao	Angola	1246700	1246700	0	19625353	2.78	38.78	11.49	0.46
7	ar	Argentina	2780400	2736690	43710	43431886	0.93	16.64	7.33	0.0
18	bh	Belize	22966	22806	160	347369	1.87	24.68	5.97	0.0
3	ag	Algeria	2381741	2381741	0	39542166	1.84	23.67	4.31	0.92
60	fi	Finland	338145	303815	34330	5476922	0.4	10.72	9.83	3.1
126	nz	New Zealand	267710	267710	None	4438393	0.82	13.33	7.36	2.21
130	no	Norway	323802	304282	19520	5207689	1.13	12.14	8.12	7.25
136	pa	Paraguay	406752	397302	9450	6783272	1.16	16.37	4.68	0.07
160	so	Somalia	637657	627337	10320	10616380	1.83	40.45	13.62	8.49
162	od	South Sudan	644329	None	None	12042910	4.02	36.91	8.18	11.47
187	uy	Uruguay	176215	175015	1200	3341893	0.27	13.07	9.45	0.9
20	bt	Bhutan	38394	38394	0	741919	1.11	17.78	6.69	0.0
165	su	Sudan	1861484	None	None	36108853	1.72	29.19	7.66	4.29
194	za	Zambia	752618	743398	9220	15066266	2.88	42.13	12.67	0.68
159	bp	Solomon Islands	28896	27986	910	622469	2.02	25.77	3.85	1.75
168	sw	Sweden	450295	410335	39960	9801616	0.8	11.99	9.4	5.42
189	nh	Vanuatu	12189	12189	0	272264	1.95	25.04	4.09	1.47
12	bf	Bahamas, The	13880	10010	3870	324597	0.85	15.5	7.05	0.0
24	br	Brazil	8515770	8358140	157630	204259812	0.77	14.46	6.58	0.14
36	ci	Chile	756102	743812	12290	17508260	0.82	13.83	6.0	0.34
137	pe	Peru	1285216	1279996	5220	30444999	0.97	18.28	6.01	2.53
214	sb	Saint Pierre and Miquelon	242	242	0	5657	1.08	7.42	9.72	8.49
239	sh	Saint Helena, Ascension, and Tristan da Cunha	308	122	0	7795	0.24	9.88	7.44	0.0
55	ek	Equatorial Guinea	28051	28051	0	740743	2.51	33.31	8.19	0.0
57	en	Estonia	45228	42388	2840	1265420	0.55	10.51	12.4	3.6
94	kg	Kyrgyzstan	199951	191801	8150	5664939	1.11	22.98	6.65	5.22
95	la	Laos	236800	230800	6000	6911544	1.55	24.25	7.63	1.09
96	lg	Latvia	64589	62249	2340	1986705	1.06	10.0	14.31	6.26
121	mz	Mozambique	799380	786380	13000	25303113	2.45	38.58	12.1	1.98
186	us	United States	9826675	9161966	664709	321368864	0.78	12.49	8.15	3.86
191	ve	Venezuela	912050	882050	30000	29275460	1.39	19.16	5.31	0.0
40	cg	Congo, Democratic Republic of the	2344858	2267048	77810	79375136	2.45	34.88	10.07	0.27
49	dj	Djibouti	23200	23180	20	828324	2.2	23.65	7.73	6.06
195	zi	Zimbabwe	390757	386847	3910	14229541	2.21	32.26	10.13	0.0
206	fo	Faroe Islands	1393	1393	0	50196	0.51	13.77	8.71	0.0
99	li	Liberia	111369	96320	15049	4195666	2.47	34.41	9.69	0.0
105	ma	Madagascar	587041	581540	5501	23812681	2.58	32.61	6.81	0.0
38	co	Colombia	1138910	1038700	100210	46736728	1.04	16.47	5.4	0.64
219	cw	Cook Islands	236	236	0	9838	2.95	14.33	8.03	None
200	ck	Cocos (Keeling) Islands	14	14	0	596	0.0	None	None	None
102	lh	Lithuania	65300	62680	2620	2884433	1.04	10.1	14.27	6.27
161	sf	South Africa	1219090	1214470	4620	53675563	1.33	20.75	9.91	2.42
127	nu	Nicaragua	130370	119990	10380	5907881	1.0	18.03	5.08	3.0
16	bo	Belarus	207600	202900	4700	9589689	0.2	10.7	13.36	0.7
119	mj	Montenegro	13812	13452	360	647073	0.42	10.42	9.43	None
133	ps	Palau	459	459	0	21265	0.38	11.05	7.99	0.71
70	gv	Guinea	245857	245717	140	11780162	2.63	35.74	9.46	0.0
71	pu	Guinea-Bissau	36125	28120	8005	1726170	1.91	33.38	14.33	0.0
134	pm	Panama	75420	74340	1080	3657024	1.32	18.32	4.81	0.28
1	af	Afghanistan	652230	652230	0	32564342	2.32	38.57	13.89	1.51
31	cm	Cameroon	475440	472710	2730	23739218	2.59	36.17	10.11	0.15
59	fj	Fiji	18274	18274	0	909389	0.67	19.43	6.04	6.75
79	ir	Iran	1648195	1531595	116600	81824270	1.2	17.99	5.94	0.07
193	ym	Yemen	527968	527968	0	26737317	2.47	29.98	6.28	1.0
237	mh	Montserrat	102	102	0	5241	0.5	11.26	6.3	0.0
172	tz	Tanzania	947300	885800	61500	51045882	2.79	36.39	8.0	0.54
241	tk	Turks and Caicos Islands	948	948	0	50280	2.3	16.13	3.1	9.94
247	vq	Virgin Islands	1910	346	1564	103574	0.59	10.31	8.54	7.67
52	ec	Ecuador	283561	276841	6720	15868396	1.35	18.51	5.06	0.0
56	er	Eritrea	117600	101000	16600	6527689	2.25	30.0	7.52	0.0
171	ti	Tajikistan	144100	141510	2590	8191958	1.71	24.38	6.18	1.15

In [21]:

%%sql
SELECT COUNT(*)
  FROM facts
 WHERE (population/area) <= (SELECT AVG(population)/AVG(area)
                               FROM facts
                              WHERE name != 'World' AND name != 'Antarctica');

 * sqlite:///factbook.db
Done.

Out[21]:

COUNT(*)
90

In [22]:

%%sql
SELECT *
  FROM facts
 WHERE population > (SELECT AVG(population)
                       FROM facts
                      WHERE name != 'World' AND name != 'Antarctica')
   AND area < (SELECT AVG(area)
                 FROM facts
                WHERE name != 'World' AND name != 'Antarctica')
 ORDER BY population/area DESC;

 * sqlite:///factbook.db
Done.

Out[22]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
14	bg	Bangladesh	148460	130170	18290	168957745	1.6	21.14	5.61	0.46
91	ks	Korea, South	99720	96920	2800	49115196	0.14	8.19	6.75	0.0
138	rp	Philippines	300000	298170	1830	100998376	1.61	24.27	6.11	2.09
85	ja	Japan	377915	364485	13430	126919659	0.16	7.93	9.51	0.0
192	vm	Vietnam	331210	310070	21140	94348835	0.97	15.96	5.93	0.3
185	uk	United Kingdom	243610	241930	1680	64088222	0.54	12.17	9.35	2.54
65	gm	Germany	357022	348672	8350	80854408	0.17	8.47	11.42	1.24
83	it	Italy	301340	294140	7200	61855120	0.27	8.74	10.19	4.1
182	ug	Uganda	241038	197100	43938	37101745	3.24	43.79	10.69	0.74
173	th	Thailand	513120	510890	2230	67976405	0.34	11.19	7.8	0.0
139	pl	Poland	312685	304255	8430	38562189	0.09	9.74	10.19	0.46
163	sp	Spain	505370	498980	6390	48146134	0.89	9.64	9.04	8.31
80	iz	Iraq	438317	437367	950	37056169	2.93	31.45	3.77	1.62
120	mo	Morocco	446550	446300	250	33322699	1.0	18.2	4.81	3.36

In [23]:

%%sql
SELECT COUNT(*)
  FROM facts
 WHERE (population > (SELECT AVG(population)
                        FROM facts
                       WHERE name != 'World' AND name != 'Antarctica'))
   AND (area < (SELECT AVG(area)
                  FROM facts
                 WHERE name != 'World' AND name != 'Antarctica'));

 * sqlite:///factbook.db
Done.

Out[23]:

COUNT(*)
14

In [24]:

%%sql
SELECT *
  FROM facts
 WHERE population <= (SELECT AVG(population)
                       FROM facts
                      WHERE name != 'World' AND name != 'Antarctica')
   AND area >= (SELECT AVG(area)
                 FROM facts
                WHERE name != 'World' AND name != 'Antarctica')
 ORDER BY population/area ASC;

 * sqlite:///factbook.db
Done.

Out[24]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
207	gl	Greenland	2166086	2166086	None	57733	0.0	14.48	8.49	5.98
118	mg	Mongolia	1564116	1553556	10560	2992908	1.31	20.25	6.35	0.84
9	as	Australia	7741220	7682300	58920	22751014	1.07	12.15	7.14	5.65
122	wa	Namibia	824292	823290	1002	2212307	0.59	19.8	13.91	0.0
23	bc	Botswana	581730	566730	15000	2182719	1.21	20.96	13.39	4.56
100	ly	Libya	1759540	1759540	0	6411776	2.23	18.03	3.58	7.8
112	mr	Mauritania	1030700	1030700	0	3596702	2.23	31.34	8.2	0.83
87	kz	Kazakhstan	2724900	2699700	25200	18157122	1.14	19.15	8.21	0.41
34	ct	Central African Republic	622984	622984	0	5391539	2.13	35.08	13.8	0.0
21	bl	Bolivia	1098581	1083301	15280	10800882	1.56	22.76	6.52	0.62
151	sa	Saudi Arabia	2149690	2149690	0	27752316	1.46	18.51	3.33	0.55
109	ml	Mali	1240192	1220190	20002	16955536	2.98	44.99	12.89	2.26
5	ao	Angola	1246700	1246700	0	19625353	2.78	38.78	11.49	0.46
160	so	Somalia	637657	627337	10320	10616380	1.83	40.45	13.62	8.49
162	od	South Sudan	644329	None	None	12042910	4.02	36.91	8.18	11.47
194	za	Zambia	752618	743398	9220	15066266	2.88	42.13	12.67	0.68
36	ci	Chile	756102	743812	12290	17508260	0.82	13.83	6.0	0.34
137	pe	Peru	1285216	1279996	5220	30444999	0.97	18.28	6.01	2.53
121	mz	Mozambique	799380	786380	13000	25303113	2.45	38.58	12.1	1.98
191	ve	Venezuela	912050	882050	30000	29275460	1.39	19.16	5.31	0.0
105	ma	Madagascar	587041	581540	5501	23812681	2.58	32.61	6.81	0.0

In [25]:

%%sql
SELECT COUNT(*)
  FROM facts
 WHERE population <= (SELECT AVG(population)
                       FROM facts
                      WHERE name != 'World' AND name != 'Antarctica')
   AND area >= (SELECT AVG(area)
                 FROM facts
                WHERE name != 'World' AND name != 'Antarctica');

 * sqlite:///factbook.db
Done.

Out[25]:

COUNT(*)
21

Observations
The two methods produce fairly different results, with some overlap. The largest difference is the capture size of the two groups, which subsequently gives different countries.

The first method, comparing a country specific density ratio to average density ratio, yields 146 countries above average and 90 countries below or equal to average for a total of 236.
Method two, using a double criteria match, yields 14 countries with high population and low area and 21 countries with low population and high area. This gives a total of 35 countries, a much smaller selection.

Taking a closer look at the whole dataset shows 20 countries that list population as zero or none, leaving only five countries out of the group produced by the first method. These most likely have an area value of none, like the entry for Saint Bathelemy. Which incidentally looks like an awesome place to go visit! Here is a table with the results. The upper and lower bounds for each subgroup are provided.

Method 1			Method 2
City	population : area	ratio	City	population : area	ratio
Macau	592,731 : 28	21,168.96	Bangladesh	168,957,745 : 148,460	1138.07
Norfolk Island	2,210 : 36	61.38	Morocco	33,322,699 : 446,300	74.66
Greenland	57,733 : 2,166,086	0.03	Greenland	57,733 : 2,166,086	0.03
Tajikistan	8,191,958 : 144,100	56.85	Madagascar	2,381,268 : 587,041	4.06

Conclusions¶

This is just an quick overview of the information contained in this dataset. Overall it seems like an interesting product that could be combined with other data to yield results with increased depth. That being said, the context of the source needs to be taken into account.

Citations¶

Central Intelligence Agency. (n.d.). The World Factbook. Central Intelligence Agency. Retrieved August 30, 2022, from https://www.cia.gov/the-world-factbook/
Wikimedia Foundation. (2022, August 10). The World Factbook. Wikipedia. Retrieved August 30, 2022, from https://en.wikipedia.org/wiki/The_World_Factbook
Shepard, A. C. (2010, June 2). CIA gets numbers wrong on Jewish settlers. NPR. Retrieved August 30, 2022, from https://www.npr.org/sections/publiceditor/2010/06/02/127349281/cia-gets-numbers-wrong-on-jewish-settlers