Earthquake Damage In Gorkha🇳🇵

Part 1: Wrangling Data with SQL

__author__ = "Donald Ghazi"
__email__ = "donald@donaldghazi.com"
__website__ = "donaldghazi.com"

import sqlite3
import pandas as pd

GOALS

• Connect to a SQLITE server
• Explore the database and create dataset
• Import database query into DataFrame

Machine Learning Workflow

• Prepare Data
  • Connect: Connect to a SQLITE server
  • Explore: Table, row
  • Import

Prepare Data

Connect

# Connect to the nepal.sqlite database
%reload_ext sql
%sql sqlite:////home/jovyan/nepal.sqlite

'Connected: @/home/jovyan/nepal.sqlite'

Explore

Select all rows and columns from the sqlite_schema table, and examine the output.

%%sql
SELECT *
FROM sqlite_schema

 * sqlite:////home/jovyan/nepal.sqlite
Done.

type	name	tbl_name	rootpage	sql
table	id_map	id_map	2	CREATE TABLE "id_map" ( "household_id" INTEGER, "building_id" INTEGER, "vdcmun_id" INTEGER, "district_id" INTEGER )
index	ix_id_map_household_id	id_map	3	CREATE INDEX "ix_id_map_household_id"ON "id_map" ("household_id")
table	building_structure	building_structure	2032	CREATE TABLE "building_structure" ( "building_id" INTEGER, "count_floors_pre_eq" INTEGER, "count_floors_post_eq" INTEGER, "age_building" INTEGER, "plinth_area_sq_ft" INTEGER, "height_ft_pre_eq" INTEGER, "height_ft_post_eq" INTEGER, "land_surface_condition" TEXT, "foundation_type" TEXT, "roof_type" TEXT, "ground_floor_type" TEXT, "other_floor_type" TEXT, "position" TEXT, "plan_configuration" TEXT, "condition_post_eq" TEXT, "superstructure" TEXT )
index	ix_building_structure_building_id	building_structure	2033	CREATE INDEX "ix_building_structure_building_id"ON "building_structure" ("building_id")
table	building_damage	building_damage	12302	CREATE TABLE "building_damage" ( "building_id" INTEGER, "damage_overall_collapse" TEXT, "damage_overall_leaning" TEXT, "damage_overall_adjacent_building_risk" TEXT, "damage_foundation_severe" TEXT, "damage_foundation_moderate" TEXT, "damage_foundation_insignificant" TEXT, "damage_roof_severe" TEXT, "damage_roof_moderate" TEXT, "damage_roof_insignificant" TEXT, "damage_corner_separation_severe" TEXT, "damage_corner_separation_moderate" TEXT, "damage_corner_separation_insignificant" TEXT, "damage_diagonal_cracking_severe" TEXT, "damage_diagonal_cracking_moderate" TEXT, "damage_diagonal_cracking_insignificant" TEXT, "damage_in_plane_failure_severe" TEXT, "damage_in_plane_failure_moderate" TEXT, "damage_in_plane_failure_insignificant" TEXT, "damage_out_of_plane_failure_severe" TEXT, "damage_out_of_plane_failure_moderate" TEXT, "damage_out_of_plane_failure_insignificant" TEXT, "damage_out_of_plane_failure_walls_ncfr_severe" TEXT, "damage_out_of_plane_failure_walls_ncfr_moderate" TEXT, "damage_out_of_plane_failure_walls_ncfr_insignificant" TEXT, "damage_gable_failure_severe" TEXT, "damage_gable_failure_moderate" TEXT, "damage_gable_failure_insignificant" TEXT, "damage_delamination_failure_severe" TEXT, "damage_delamination_failure_moderate" TEXT, "damage_delamination_failure_insignificant" TEXT, "damage_column_failure_severe" TEXT, "damage_column_failure_moderate" TEXT, "damage_column_failure_insignificant" TEXT, "damage_beam_failure_severe" TEXT, "damage_beam_failure_moderate" TEXT, "damage_beam_failure_insignificant" TEXT, "damage_infill_partition_failure_severe" TEXT, "damage_infill_partition_failure_moderate" TEXT, "damage_infill_partition_failure_insignificant" TEXT, "damage_staircase_severe" TEXT, "damage_staircase_moderate" TEXT, "damage_staircase_insignificant" TEXT, "damage_parapet_severe" TEXT, "damage_parapet_moderate" TEXT, "damage_parapet_insignificant" TEXT, "damage_cladding_glazing_severe" TEXT, "damage_cladding_glazing_moderate" TEXT, "damage_cladding_glazing_insignificant" TEXT, "area_assesed" TEXT, "damage_grade" TEXT, "technical_solution_proposed" TEXT, "has_repair_started" REAL, "has_damage_foundation" REAL, "has_damage_roof" REAL, "has_damage_corner_separation" REAL, "has_damage_diagonal_cracking" REAL, "has_damage_in_plane_failure" REAL, "has_damage_out_of_plane_failure" REAL, "has_damage_out_of_plane_walls_ncfr_failure" REAL, "has_damage_gable_failure" REAL, "has_damage_delamination_failure" REAL, "has_damage_column_failure" REAL, "has_damage_beam_failure" REAL, "has_damage_infill_partition_failure" REAL, "has_damage_staircase" REAL, "has_damage_parapet" REAL, "has_damage_cladding_glazing" REAL, "has_geotechnical_risk" REAL, "has_geotechnical_risk_land_settlement" INTEGER, "has_geotechnical_risk_fault_crack" INTEGER, "has_geotechnical_risk_liquefaction" INTEGER, "has_geotechnical_risk_landslide" INTEGER, "has_geotechnical_risk_rock_fall" INTEGER, "has_geotechnical_risk_flood" INTEGER, "has_geotechnical_risk_other" INTEGER )
index	ix_building_damage_building_id	building_damage	12305	CREATE INDEX "ix_building_damage_building_id"ON "building_damage" ("building_id")
table	household_demographics	household_demographics	31601	CREATE TABLE "household_demographics" ( "household_id" INTEGER, "gender_household_head" TEXT, "age_household_head" REAL, "caste_household" TEXT, "education_level_household_head" TEXT, "income_level_household" TEXT, "size_household" REAL, "is_bank_account_present_in_household" REAL )
index	ix_household_demographics_household_id	household_demographics	31602	CREATE INDEX "ix_household_demographics_household_id"ON "household_demographics" ("household_id")

Select the name column from the sqlite_schema table, showing only rows where the type is "table".

%%sql
SELECT name
FROM sqlite_schema

 * sqlite:////home/jovyan/nepal.sqlite
Done.

name
id_map
ix_id_map_household_id
building_structure
ix_building_structure_building_id
building_damage
ix_building_damage_building_id
household_demographics
ix_household_demographics_household_id

%%sql
SELECT name
FROM sqlite_schema
WHERE type = "table"

 * sqlite:////home/jovyan/nepal.sqlite
Done.

name
id_map
building_structure
building_damage
household_demographics

Select all columns from the id_map table, limiting my results to the first five rows.

%%sql
SELECT *
FROM id_map
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

household_id	building_id	vdcmun_id	district_id
5601	56	7	1
6301	63	7	1
9701	97	7	1
9901	99	7	1
11501	115	7	1

Count how many observations are in the id_map table using the count command.

%%sql
SELECT count(*)
FROM id_map

 * sqlite:////home/jovyan/nepal.sqlite
Done.

count(*)
249932

Find which districts are represented in the id_map table using the distinct command to determine the unique values in the district_id column.

%%sql
SELECT distinct(district_id)
FROM id_map

 * sqlite:////home/jovyan/nepal.sqlite
Done.

district_id
1
2
3
4

Find how many buildings there are in id_map table by combinining the count and distinct commands to calculate the number of unique values in building_id.

%%sql
SELECT count(distinct(building_id))
FROM id_map

 * sqlite:////home/jovyan/nepal.sqlite
Done.

count(distinct(building_id))
234835

For my model, I'll focus on Gorkha (district 4). Select all columns that from id_map, showing only rows where the district_id is 4 and limiting my results to the first five rows.

%%sql
SELECT *
FROM id_map
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

household_id	building_id	vdcmun_id	district_id
16400201	164002	38	4
16408101	164081	38	4
16408901	164089	38	4
16409801	164098	38	4
16410301	164103	38	4

Find how many observations in the id_map table come from Gorkha using the count and WHERE commands together to calculate the answer.

%%sql
SELECT count(*)
FROM id_map
WHERE district_id = 4

 * sqlite:////home/jovyan/nepal.sqlite
Done.

count(*)
75883

Find how many buildings in the id_map table are in Gorkha, combining the count and distinct commands to calculate the number of unique values in building_id, considering only rows where the district_id is 4.

%%sql
SELECT count(distinct(building_id)) AS unique_buildings_gorkha
FROM id_map
WHERE district_id = 4

 * sqlite:////home/jovyan/nepal.sqlite
Done.

unique_buildings_gorkha
70836

Select all the columns from the building_structure table, and limit my results to the first five rows.

%%sql
SELECT *
FROM building_structure
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

building_id	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure
1	1	1	9	288	9	9	Flat	Other	Bamboo/Timber-Light roof	Mud	Not applicable	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
2	1	1	15	364	9	9	Flat	Other	Bamboo/Timber-Light roof	Mud	Not applicable	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
3	1	1	20	384	9	9	Flat	Other	Bamboo/Timber-Light roof	Mud	Not applicable	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
4	1	1	20	312	9	9	Flat	Other	Bamboo/Timber-Light roof	Mud	Not applicable	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
5	1	1	30	308	9	9	Flat	Other	Bamboo/Timber-Light roof	Mud	Not applicable	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar

Find how many building are there in the building_structure table using the count command.

%%sql
SELECT count(*)
FROM building_structure

 * sqlite:////home/jovyan/nepal.sqlite
Done.

count(*)
234835

There are over 200,000 buildings in the building_structure table, and I want to retrieve only buildings that are in Gorkha. Here I use the JOIN command to join the id_map and building_structure tables, showing only buildings where district_id is 4 and limiting my results to the first five rows of the new table.

%%sql
SELECT *
FROM id_map
JOIN building_structure ON id_map.building_id = building_structure.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

household_id	building_id	vdcmun_id	district_id	building_id_1	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure
16400201	164002	38	4	164002	3	3	20	560	18	18	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
16408101	164081	38	4	164081	2	2	21	200	12	12	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16408901	164089	38	4	164089	3	3	18	315	20	20	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16409801	164098	38	4	164098	2	2	45	290	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16410301	164103	38	4	164103	2	2	21	230	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar

%%sql
SELECT *
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

household_id	building_id	vdcmun_id	district_id	building_id_1	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure
16400201	164002	38	4	164002	3	3	20	560	18	18	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
16408101	164081	38	4	164081	2	2	21	200	12	12	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16408901	164089	38	4	164089	3	3	18	315	20	20	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16409801	164098	38	4	164098	2	2	45	290	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
16410301	164103	38	4	164103	2	2	21	230	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar

In the table I just made, each row represents a unique household in Gorkha. Now I can create a table where each row represents a unique building.

Use the distinct command to create a column with all unique building IDs in the id_map table. JOIN this column with all the columns from the building_structure table, showing only buildings where district_id is 4 and limiting my results to the first five rows of the new table.

%%sql
SELECT distinct(i.building_id)
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

building_id
164002
164081
164089
164098
164103

%%sql
SELECT count(distinct(i.building_id))
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

count(distinct(i.building_id))
70836

%%sql
SELECT distinct(i.building_id),
       s.*
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

building_id	building_id_1	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure
164002	164002	3	3	20	560	18	18	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar
164081	164081	2	2	21	200	12	12	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
164089	164089	3	3	18	315	20	20	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
164098	164098	2	2	45	290	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar
164103	164103	2	2	21	230	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar

I've combined the id_map and building_structure tables to create a table with all the buildings in Gorkha, but the final piece of data needed for my model, the damage that each building sustained in the earthquake, is in the building_damage table.

%%sql
select * from building_damage limit 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

building_id	damage_overall_collapse	damage_overall_leaning	damage_overall_adjacent_building_risk	damage_foundation_severe	damage_foundation_moderate	damage_foundation_insignificant	damage_roof_severe	damage_roof_moderate	damage_roof_insignificant	damage_corner_separation_severe	damage_corner_separation_moderate	damage_corner_separation_insignificant	damage_diagonal_cracking_severe	damage_diagonal_cracking_moderate	damage_diagonal_cracking_insignificant	damage_in_plane_failure_severe	damage_in_plane_failure_moderate	damage_in_plane_failure_insignificant	damage_out_of_plane_failure_severe	damage_out_of_plane_failure_moderate	damage_out_of_plane_failure_insignificant	damage_out_of_plane_failure_walls_ncfr_severe	damage_out_of_plane_failure_walls_ncfr_moderate	damage_out_of_plane_failure_walls_ncfr_insignificant	damage_gable_failure_severe	damage_gable_failure_moderate	damage_gable_failure_insignificant	damage_delamination_failure_severe	damage_delamination_failure_moderate	damage_delamination_failure_insignificant	damage_column_failure_severe	damage_column_failure_moderate	damage_column_failure_insignificant	damage_beam_failure_severe	damage_beam_failure_moderate	damage_beam_failure_insignificant	damage_infill_partition_failure_severe	damage_infill_partition_failure_moderate	damage_infill_partition_failure_insignificant	damage_staircase_severe	damage_staircase_moderate	damage_staircase_insignificant	damage_parapet_severe	damage_parapet_moderate	damage_parapet_insignificant	damage_cladding_glazing_severe	damage_cladding_glazing_moderate	damage_cladding_glazing_insignificant	area_assesed	damage_grade	technical_solution_proposed	has_repair_started	has_damage_foundation	has_damage_roof	has_damage_corner_separation	has_damage_diagonal_cracking	has_damage_in_plane_failure	has_damage_out_of_plane_failure	has_damage_out_of_plane_walls_ncfr_failure	has_damage_gable_failure	has_damage_delamination_failure	has_damage_column_failure	has_damage_beam_failure	has_damage_infill_partition_failure	has_damage_staircase	has_damage_parapet	has_damage_cladding_glazing	has_geotechnical_risk	has_geotechnical_risk_land_settlement	has_geotechnical_risk_fault_crack	has_geotechnical_risk_liquefaction	has_geotechnical_risk_landslide	has_geotechnical_risk_rock_fall	has_geotechnical_risk_flood	has_geotechnical_risk_other
1	Moderate-Heavy	Insignificant/light	None	None	Moderate-Heavy-(<1/3)	Insignificant/light-(<1/3)	Severe-Extreme-(<1/3)	None	Insignificant/light-(>2/3)	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(<1/3)	None	None	Severe-Extreme-(>2/3)	None	None	None	Moderate-Heavy-(>2/3)	None	Severe-Extreme-(>2/3)	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	Both	Grade 3	Major repair	0.0	None	1.0	1.0	1.0	1.0	None	1.0	0.0	0.0	None	None	None	0.0	0.0	0.0	0.0	0	0	0	0	0	0	0
2	Severe-Extreme	Severe-Extreme	Insignificant/light	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	Severe-Extreme-(>2/3)	None	None	None	None	None	Severe-Extreme-(>2/3)	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	Exterior	Grade 5	Reconstruction	1.0	1.0	1.0	1.0	1.0	1.0	1.0	1.0	0.0	1.0	None	None	None	0.0	0.0	0.0	0.0	0	0	0	0	0	0	0
3	Moderate-Heavy	Moderate-Heavy	Moderate-Heavy	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	None	Insignificant/light-(1/3-2/3)	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	Both	Grade 2	Minor repair	1.0	None	None	None	None	None	None	None	None	None	None	None	None	1.0	0.0	0.0	0.0	0	0	0	0	0	0	0
4	Moderate-Heavy	Moderate-Heavy	Moderate-Heavy	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	Moderate-Heavy-(>2/3)	None	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(1/3-2/3)	None	None	None	None	None	None	None	None	None	None	Moderate-Heavy-(>2/3)	None	None	None	None	None	None	None	Both	Grade 2	Minor repair	1.0	None	None	None	None	None	None	None	None	None	None	None	None	None	0.0	0.0	0.0	0	0	0	0	0	0	0
5	Insignificant/light	None	None	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	Insignificant/light-(<1/3)	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	Exterior	Grade 1	Minor repair	1.0	None	None	None	None	None	None	None	0.0	0.0	None	None	None	0.0	0.0	0.0	0.0	0	0	0	0	0	0	0

%%sql
SELECT distinct(i.building_id) AS b_id,
       s.*,
       d.damage_grade
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
JOIN building_damage AS d ON i.building_id = d.building_id
WHERE district_id = 4
LIMIT 5

 * sqlite:////home/jovyan/nepal.sqlite
Done.

b_id	building_id	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure	damage_grade
164002	164002	3	3	20	560	18	18	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar	Grade 2
164081	164081	2	2	21	200	12	12	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 2
164089	164089	3	3	18	315	20	20	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 2
164098	164098	2	2	45	290	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 3
164103	164103	2	2	21	230	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 3

Import

Use the connect method from the sqlite3 library to connect to the database. Remember that the database is located at "/home/jovyan/nepal.sqlite".

conn = sqlite3.connect("/home/jovyan/nepal.sqlite")

Put my last SQL query into a string and assign it to the variable query.

query = """
SELECT distinct(i.building_id) AS b_id,
       s.*,
       d.damage_grade
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
JOIN building_damage AS d ON i.building_id = d.building_id
WHERE district_id = 4
"""
print(query)

SELECT distinct(i.building_id) AS b_id,
       s.*,
       d.damage_grade
FROM id_map AS i
JOIN building_structure AS s ON i.building_id = s.building_id
JOIN building_damage AS d ON i.building_id = d.building_id
WHERE district_id = 4

Use the read_sql from the pandas library to create a DataFrame from my query. The building_id is set as my index column.

df = pd.read_sql(query, conn, index_col="b_id")
df.head()

	building_id	count_floors_pre_eq	count_floors_post_eq	age_building	plinth_area_sq_ft	height_ft_pre_eq	height_ft_post_eq	land_surface_condition	foundation_type	roof_type	ground_floor_type	other_floor_type	position	plan_configuration	condition_post_eq	superstructure	damage_grade
b_id
164002	164002	3	3	20	560	18	18	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Repaired and used	Stone, mud mortar	Grade 2
164081	164081	2	2	21	200	12	12	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 2
164089	164089	3	3	18	315	20	20	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 2
164098	164098	2	2	45	290	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 3
164103	164103	2	2	21	230	13	13	Flat	Mud mortar-Stone/Brick	Bamboo/Timber-Light roof	Mud	TImber/Bamboo-Mud	Not attached	Rectangular	Damaged-Used in risk	Stone, mud mortar	Grade 3