Variable renaming and reformatting¶
The variable renaming exercise was carried out to ensure variable names are representative of the data that they contain. The following guiding principles were used for the same:
- Flag variables have been prefixed with ‘has’ or ‘is’. All flag variables cant take one of the following states:
- 0, which is indicative of a FALSE state.
- 1, which is indicative of a TRUE state.
- NA, in case of Null values.
- The data type for all categorical variables have been enforced to INTEGER, i.e. NUMERIC.
- The data type for all numeric variables has been enforced to DOUBLE PRECISION, i.e. FLOAT.
- Unique IDs identifying each row in a table has been created, these are suffixed with “_id” for clarity. For eg, household_id = record_id (which is equal to dist + vdcmun + ward + EA + howner_sn) + house_sn + hhd_sn
- All variable names are in lowercase.
The below section contains information on the new names of columns across all eight tables.
Common Variables¶
Location variables¶
Variables that identify locations across all tables have been refirmatted to also contain information about their parent location. Based on this, following are the new location columns that identify locations at different levels:
- district_id : “dist” column has been renamed to district_id and is of CHARACTER TYPE of length 2.
- vdcmun_id : “vdcmun” column has been updated to also contain information about parent district in order to make queries at a vdc level more efficient. The type has been set to CHARACTER, and the variable has a length of four characters. The first two letters correspond to the VDC’s parent district (eg. 2302 stands for VDC 02 in District 23).
- ward_id : “ward” column has been updated to also contain information about parent vdc and district in order to make queries at a ward level more efficient. The type has been set to CHARACTER, and the variable has a length of six characters. The first four letters correspond to the ward’s parent vdc and district (eg. 230201 stands for Ward no 2, in VDC 02 of District 23).
Unique identifier (ID) variables¶
To facilitate faster table join operations the following primary and composite keys will be generated across all eight tables:
- record_id: This ID will uniquely identify each record that was captured in the survey.
- building_id: This ID will uniquely identify each building for which data has been recorded in the survey.
- household_id: This ID will uniquely identify each household for which data has been recorded in the survey.
- individual_id: This ID will uniquely identify every individual for whom data has been recorded in the survey.
- injured_missing_individual_id: This ID will uniquely identify every injured/missing individual for whom data has been recorded in the survey.
- deceased_individual_id: This ID will uniquely identify every deceased individual for whom data has been recorded in the survey.
Note: As was observed in the data restructuring exercise, there are instances in the individual table where the there are instances of multiple records for individuals with the same district, vdcmun, ward, EA, howners_sn, house_sn, hhd_sn and mem_sn. Because of this, even the composite IDs mentioned above will not be unique for every individual. To counter this, a unique ‘id’ column of the type AUTO_INCREMENT has been generated through PostgreSQL for each and every table.
Table specific variables¶
Main Table¶
Variables in the main table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| howner_num | count_house_owners |
| own_res_sme | is_owner_respondent_same |
| gender | respondent_gender |
| relation | respondent_relation_to_house_owner |
| reason | reason_house_owner_is_absent |
| rhouse_sa | count_residential_houses_same_area |
| rhouse_da | count_residential_houses_different_area |
| ndam_c | count_non_residential_house_no_damage |
| pdam_c | count_non_residential_house_partial_damage |
| cdam_c | count_non_residential_house_complete_damage |
| soc_mob | is_social_mobilizer_present |
House owner¶
Variables in the house owner table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| gender | house_owner_gender |
House other place¶
Variables in the house other place table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| oth_dist | district_house_other_place |
| conditn | condition_house_other_place |
Building¶
Variables in the building table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| legl_own | legal_ownership |
| house_cd | condition_post_eq |
| floor_pre | count_floors_pre_eq |
| floor_pos | count_floors_post_eq |
| age | age_building |
| pl_area | plinth_area_sq_ft |
| hgt_pre | height_ft_pre_eq |
| hgt_pos | height_ft_post_eq |
| surf_con | land_surface_condition |
| foundatn | foundation_type |
| roof | roof_type |
| gd_floor | ground_floor_type |
| up_floor | other_floor_type |
| positn | position |
| pln_conf | plan_configuration |
| collapse | damage_overall_collapse |
| leaning | damage_overall_leaning |
| adjbld_rsk | damage_overall_adjacent_building_risk |
| dm_fndtn1 | damage_foundation_severe |
| dm_fndtn2 | damage_foundation_moderate |
| dm_fndtn3 | damage_foundation_insignificant |
| dm_roof1 | damage_roof_severe |
| dm_roof2 | damage_roof_moderate |
| dm_roof3 | damage_roof_insignificant |
| corn_sep1 | damage_corner_separation_severe |
| corn_sep2 | damage_corner_separation_moderate |
| corn_sep3 | damage_corner_separation_insignificant |
| diag_cr1 | damage_diagonal_cracking_severe |
| diag_cr2 | damage_diagonal_cracking_moderate |
| diag_cr3 | damage_diagonal_cracking_insignificant |
| pl_fail1 | damage_in_plane_failure_severe |
| pl_fail2 | damage_in_plane_failure_moderate |
| pl_fail3 | damage_in_plane_failure_insignificant |
| op_fail1 | damage_out_of_plane_failure_severe |
| op_fail2 | damage_out_of_plane_failure_moderate |
| op_fail3 | damage_out_of_plane_failure_insignificant |
| op_fl_nl1 | damage_out_of_plane_failure_walls_ncfr_severe |
| op_fl_nl2 | damage_out_of_plane_failure_walls_ncfr_moderate |
| op_fl_nl3 | damage_out_of_plane_failure_walls_ncfr_insignificant |
| dm_gabl1 | damage_gable_failure_severe |
| dm_gabl2 | damage_gable_failure_moderate |
| dm_gabl3 | damage_gable_failure_insignificant |
| delam1 | damage_delamination_failure_severe |
| delam2 | damage_delamination_failure_moderate |
| delam3 | damage_delamination_failure_insignificant |
| col_fail1 | damage_column_failure_severe |
| col_fail2 | damage_column_failure_moderate |
| col_fail3 | damage_column_failure_insignificant |
| beam_fl1 | damage_beam_failure_severe |
| beam_fl2 | damage_beam_failure_moderate |
| beam_fl3 | damage_beam_failure_insignificant |
| part_col1 | damage_infill_partition_failure_severe |
| part_col2 | damage_infill_partition_failure_moderate |
| part_col3 | damage_infill_partition_failure_insignificant |
| str_case1 | damage_staircase_severe |
| str_case2 | damage_staircase_moderate |
| str_case3 | damage_staircase_insignificant |
| parapet1 | damage_parapet_severe |
| parapet2 | damage_parapet_moderate |
| parapet3 | damage_parapet_insignificant |
| clad_glz1 | damage_cladding_glazing_severe |
| clad_glz2 | damage_cladding_glazing_moderate |
| clad_glz3 | damage_cladding_glazing_insignificant |
| gersk | has_geotechnical_risk |
| assd_ar | area_assessed |
| dm_grade | damage_grade |
| tech_sol | technical_solution_proposed |
| repair | has_repair_started |
| sec_use | has_secondary_use |
| fam_cn | count_families |
In addition, the following new variables have been added to the building table.
| Variable | Definition |
| has_damage_foundation | Flag variable that indicates if there was any damage to foundation. |
| has_damage_roof | Flag variable that indicates if there was any damage to the roof. |
| has_damage_corner_separation | Flag variable that indicates if there was any corner separation. |
| has_damage_diagonal_cracking | Flag variable that indicates if there was any diagonal cracking |
| has_damage_in_plane_failure | Flag variable that indicates if there was any in plane failure |
| has_damage_out_of_plane_failure | Flag variable that indicates if there was any out of plane failure |
| has_damage_out_of_plane_walls_ncfr_failure | Flag variable that indicates if there was any out of plane failure in walls nt carrying floor or roof |
| has_damage_gable_failure | Flag variable that indicates if there was any gable failure |
| has_damage_delamination_failure | Flag variable that indicates if there was any delamination failure |
| has_damage_column_failure | Flag variable that indicates if there was any column failure |
| has_damage_beam_failure | Flag variable that indicates if there was any beam failure |
| has_damage_infill_partition_failure | Flag variable that indicates if thre was any infill/partition failure |
| has_damage_staircase | Flag variable that indicates if building has damage to staircase |
| has_damage_parapet | Flag variable that indicates if building has damage to parapet |
| has_damage_cladding_glazing | Flag variable that indicates if building has any cladding/glazing related damage. |
| has_superstructure_adobe_mud | Flag variable that indicates if building has an adobe/mud superstructure |
| has_superstructure_mud_mortar_stone | Flag variable that indicates if building has mud mortar – stone superstructure |
| has_superstructure_stone | Flag variable that indicates if building has superstructure |
| has_superstructure_cement_mortar_stone | Flag variable that indicates if building has cement mortar – stone superstructure |
| has_superstructure_mud_mortar_brick | Flag variable that indicates if building has mud mortar – brick superstructure |
| has_superstructure_cement_mortar_brick | Flag variable that indicates if building has cement mortar – brick superstructure |
| has_superstructure_timber | Flag variable that indicates if building has timber superstructure |
| has_superstructure_bamboo | Flag variable that indicates if building has bamboo superstructure |
| has_superstructure_rc_non_engineered | Flag variable that indicates if building has cement RC-non engineered superstructure |
| has_superstructure_rc_engineered | Flag variable that indicates if building has cement RC-engineered superstructure |
| has_superstructure_other | Flag variable that indicates if building has any other superstructure |
| has_geotechnical_risk_land_settlement | Flag variable that indicates if building has geotechnical risk of type land settlement |
| has_geotechnical_risk_fault_crack | Flag variable that indicates if building has geotechnical risk of fault cracking |
| has_geotechnical_risk_liquefaction | Flag variable that indicates if building has geotechnical risk of type liquefaction |
| has_geotechnical_risk_landslide | Flag variable that indicates if building has geotechnical risk of type landslide |
| has_geotechnical_risk_rock_fall | Flag variable that indicates if building has geotechnical risk of type rockfall |
| has_geotechnical_risk_flood | Flag variable that indicates if building has geotechnical risk of type flood |
| has_geotechnical_risk_other | Flag variable that indicates if building has geotechnical risk of any other type |
| has_secondary_use_agriculture | Flag variable that indicates if building has been secondarily used for agriculture |
| has_secondary_use_hotel | Flag variable that indicates if building has been secondarily used for a hotel |
| has_secondary_use_rental | Flag variable that indicates if building has been secondarily used for rental |
| has_secondary_use_institution | Flag variable that indicates if building has been secondarily used for institutions |
| has_secondary_use_school | Flag variable that indicates if building has been secondarily used for school |
| has_secondary_use_industry | Flag variable that indicates if building has been secondarily used for industry |
| has_secondary_use_health_post | Flag variable that indicates if building has been secondarily used for health post |
| has_secondary_use_government_office | Flag variable that indicates if building has been secondarily used for a government office |
| has_secondary_use_police | Flag variable that indicates if building has been secondarily used for police work |
| has_secondary_use_other | Flag variable that indicates if building has any other secondary use |
Household¶
Variables in the household table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| gender | gender_household_head |
| age | age_household_head |
| Ishhd | is_respondent_household_head |
| rel_hres | respondent_relation_to_hhd_head |
| id_type | household_head_id_card_type |
| cast | caste_household |
| edu_levl | education_level_household_head |
| bank_acc | is_bank_account_present_in_household |
| poseq_shel | shelter_condition_household_post_eq |
| respreq | residence_household_pre_eq |
| resposq | residence_household_post_eq |
| eqid_typ | household_eq_id_type |
| income | income_level_household |
| hhd_size | size_household |
| death | has_death_occurred_last_12_months |
| death_cn | count_death_last_12_months |
| loss | has_injury_loss_occurred_last_12_months |
| loss_cn | count_injury_loss_last_12_months |
| edrop_tf | has_education_drop_occurred_last_12_months |
| edrop_cn | count_education_drop_last_12_months |
| pdrop_tf | has_pregnancy_treatment_drop_occurred_last_12_months |
| pdrop_cn | count_pregnancy_treatment_drop_last_12_months |
| vdrop_tf | has_vaccination_drop_occurred_last_12_months |
| vdrop_cn | count_vaccination_drop_last_12_months |
| oc_ch_tf | has_occupation_change_occurred_last_12_months |
| oc_ch_cn | count_occupation_change_last_12_months |
| h20_pre | source_water_pre_eq |
| h20_pos | source_water_post_eq |
| fir_pre | source_cooking_fuel_pre_eq |
| fir_pos | source_cooking_fuel_post_eq |
| lit_pre | source_light_pre_eq |
| lit_pos | source_light_post_eq |
| toilet_pre | type_toilet_pre_eq |
| toilet_pos | type_toilet_post_eq |
| respreqd | residence_district_household_head_pre_eq |
| resposqd | residence_district_household_head_post_eq |
In addition, the following new variables have been added to the household table.
| Variable | Definition |
| is_recipient_rahat_15k | Flag variable that indicates if household is a recipient of Rahat – 15K |
| is_recipient_rahat_10k | Flag variable that indicates if household is a recipient of Rahat – 10K |
| is_recipient_rahat_200k | Flag variable that indicates if household is a recipient of Rahat – 200K |
| is_recipient_rahat_social_security_3k | Flag variable that indicates if household is a recipient of Rahat – Social Security 3K |
| is_recipient_rahat_none | Flag variable that indicates if household is a not a recipient of Rahat |
| is_ineligible_rahat | Flag variable that indicates if household is ineligible for Rahat |
| has_asset_land_pre_eq | Flag variable that indicates if the household had land before the earthquake |
| has_asset_tv_pre_eq | Flag variable that indicates if the household had TV before the earthquake |
| has_asset_cable_pre_eq | Flag variable that indicates if the household had a cable connection before the earthquake |
| has_asset_computer_pre_eq | Flag variable that indicates if the household had computer before the earthquake |
| has_asset_internet_pre_eq | Flag variable that indicates if the household had an internet connection before the earthquake |
| has_asset_telephone_pre_eq | Flag variable that indicates if the household had a telephone before the earthquake |
| has_asset_mobile_phone_pre_eq | Flag variable that indicates if the household had a mobile phone before the earthquake |
| has_asset_fridge_pre_eq | Flag variable that indicates if the household had a fridge before the earthquake |
| has_asset_motorcycle_pre_eq | Flag variable that indicates if the household had a motorcycle before the earthquake |
| has_asset_four_wheeler_family_use_pre_eq | Flag variable that indicates if the household had a four wheeler for family use before the earthquake |
| has_asset_four_wheeler_commercial_use_pre_eq | Flag variable that indicates if the household had a four wheeler for commercial use before the earthquake |
| has_asset_none_pre_eq | Flag variable that indicates if the household had none of the above assets before the earthquake |
| has_asset_land_post_eq | Flag variable that indicates if the household had land after the earthquake |
| has_asset_tv_post_eq | Flag variable that indicates if the household had TV after the earthquake |
| has_asset_cable_post_eq | Flag variable that indicates if the household had a cable connection after the earthquake |
| has_asset_computer_post_eq | Flag variable that indicates if the household had computer after the earthquake |
| has_asset_internet_post_eq | Flag variable that indicates if the household had an internet connection after the earthquake |
| has_asset_telephone_post_eq | Flag variable that indicates if the household had a telephone after the earthquake |
| has_asset_mobile_phone_post_eq | Flag variable that indicates if the household had a mobile phone after the earthquake |
| has_asset_fridge_post_eq | Flag variable that indicates if the household had a fridge after the earthquake |
| has_asset_motorcycle_post_eq | Flag variable that indicates if the household had a motorcycle after the earthquake |
| has_asset_four_wheeler_family_use_post_eq | Flag variable that indicates if the household had a four wheeler for family use after the earthquake |
| has_asset_four_wheeler_commercial_post_eq | Flag variable that indicates if the household had a four wheeler for commercial use after the earthquake |
| has_asset_none_post_eq | Flag variable that indicates if the household had none of the above assets after the earthquake |
Individual¶
Variables in the individual table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| gender | gender_individual |
| hh_rel | relation_to_household_head_individual |
| present | presence_in_househod |
| age | age_individual |
| disablty | disability_individual |
| brth_reg | has_registered_birth |
| edu_levl | individual_education_level |
| marital | individual_marital_status |
In addition, the following new variables have been added to the individual table.
| Variable | Definition |
| has_social_security_elder_citizen | Flag variable that indicates if individual has received social security as an elder citizen |
| has_social_security_single_woman | Flag variable that indicates if individual has received social security as a single woman |
| has_social_security_handicap | Flag variable that indicates if individual has received handicap related social securit |
| has_social_security_extinct_caste | Flag variable that indicates if individual has received social security for being part of an extinct caste |
| has_social_security_child_security | Flag variable that indicates if individual has received social security on behalf of child security |
| has_social_security_pension | Flag variable that indicates if individual has received social security in the form of pension |
| has_social_security_other | Flag variable that indicates if individual has received any other social security benefits |
| has_social_security_none | Flag variable that indicates if individual has received no social security |
| has_social_security_not_applicable | Flag variable that indicates if individual is not applicable for social security benefits |
Injured Missing¶
Variables in the injured missing table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| gender | gender_injured_missing_individual |
| age | age_injured_missing_individual |
| loss_typ | loss_type_injured_missing_individual |
Death¶
Variables in the death table (apart from location and ID variables) have been renamed as per the table below.
| Old Name | New Name |
| gender | gender_deceased_individual |
| age | age_deceased_individual |
| register | has_registered_death |
| reason | reason_of_death |