Study CIA World Factbook with sqlite3

In [5]:

import sqlite3
import pandas as pd
conn = sqlite3.connect("Data/factbook.db")
q = "SELECT * FROM sqlite_master WHERE type = 'table';"
pd.read_sql_query(q, conn)

Out[5]:

	type	name	tbl_name	rootpage	sql
0	table	facts	facts	2	CREATE TABLE "facts" ("id" INTEGER PRIMARY KEY...
1	table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)

In [6]:

q1 = "SELECT * FROM facts LIMIT 5;"
pd.read_sql_query(q1, conn)

Out[6]:

	id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate	created_at	updated_at
0	1	af	Afghanistan	652230	652230	0	32564342	2.32	38.57	13.89	1.51	2015-11-01 13:19:49.461734	2015-11-01 13:19:49.461734
1	2	al	Albania	28748	27398	1350	3029278	0.30	12.92	6.58	3.30	2015-11-01 13:19:54.431082	2015-11-01 13:19:54.431082
2	3	ag	Algeria	2381741	2381741	0	39542166	1.84	23.67	4.31	0.92	2015-11-01 13:19:59.961286	2015-11-01 13:19:59.961286
3	4	an	Andorra	468	468	0	85580	0.12	8.13	6.96	0.00	2015-11-01 13:20:03.659945	2015-11-01 13:20:03.659945
4	5	ao	Angola	1246700	1246700	0	19625353	2.78	38.78	11.49	0.46	2015-11-01 13:20:08.625072	2015-11-01 13:20:08.625072

In [8]:

q2 = "SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth) FROM facts;"
pd.read_sql_query(q2, conn)

Out[8]:

	name	MIN(population)	MAX(population)	MIN(population_growth)	MAX(population_growth)
0	South Sudan	0	7256490011	0.0	4.02

In [9]:

q3 = "SELECT name, MIN(population) FROM facts;"
pd.read_sql_query(q3, conn)

Out[9]:

	name	MIN(population)
0	Antarctica	0

In [10]:

q4 = "SELECT name, MAX(population) FROM facts;"
pd.read_sql_query(q4, conn)

Out[10]:

	name	MAX(population)
0	World	7256490011

Findings:¶

instead of listing the country names, the table also include continent name like Antarctica or group name "World" of all the people in the world which are not comparable to other data in the table

In [37]:

q5 = "SELECT population, population_growth, birth_rate, death_rate FROM facts WHERE (name != 'World' AND name !='Antarctica')"

import matplotlib.pyplot as plt

import seaborn as sns
%matplotlib inline

#Comment portion is my original way, below is an alternative way
#df = pd.read_sql_query(q5, conn)
# cols = df.columns.tolist()

# fig = plt.figure(figsize =(12, 12))
# for i in range(4):
#     ax = fig.add_subplot(2, 2, i+1)
#     data = df[cols[i]][df[cols[i]].isnull() == False]
#     ax.set_title(cols[i])
#     ax.hist(data)

fig = plt.figure(figsize = (12,12))
ax = fig.add_subplot(111) #same as add_subplot(1, 1, 1)
pd.read_sql_query(q5, conn).hist(ax = ax)

/Applications/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2862: UserWarning: To output multiple subplots, the figure containing the passed axes is being cleared
  exec(code_obj, self.user_global_ns, self.user_ns)

Out[37]:

array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a1e104e10>,
        <matplotlib.axes._subplots.AxesSubplot object at 0x11c0f7358>],
       [<matplotlib.axes._subplots.AxesSubplot object at 0x11c22add8>,
        <matplotlib.axes._subplots.AxesSubplot object at 0x11be39e80>]], dtype=object)

In [66]:

q6 = '''
SELECT name, population/area_land pop_density FROM facts 
WHERE  (name != 'World' AND name !='Antarctica')
ORDER BY pop_density DESC
LIMIT 30'''
fig, ax = plt.subplots(figsize = (12, 12))
df = pd.read_sql_query(q6, conn)
df = df.set_index('name')
print(df.head())
df.plot.barh(ax = ax)

            pop_density
name                   
Macau             21168
Monaco            15267
Singapore          8259
Hong Kong          6655
Gaza Strip         5191

Out[66]:

<matplotlib.axes._subplots.AxesSubplot at 0x1a2133c9b0>

social