Reads a simplified version of the INFORM Index output and finds top 20 countries with highest natural hazard risk and vulnerability, along with lowest coping capacity, all normalized against population

inform_ranker.py

# script reviews a simplified version of INFORM index data
# and harmonizes natural hazards, vulnerability, and coping capacity
# then normalizes with population from 2023

import pandas as pd

file_path = 'INFORM.xlsx'
xls = pd.ExcelFile(file_path)

ranks_df = pd.read_excel(xls, 'ranks')
population_df = pd.read_excel(xls, 'population')

# extract and sort data for each ranking category
top_20_hazard = ranks_df[['Country Hazard', 'ISO Hazard', 'Rank Hazard']].sort_values(by='Rank Hazard', ascending=False).head(20)
top_20_vulnerability = ranks_df[['Country Vulnerability', 'ISO Vulnerability', 'Rank Vulnerability']].sort_values(by='Rank Vulnerability', ascending=False).head(20)
top_20_coping = ranks_df[['Country Coping', 'ISO Coping', 'Rank Coping']].sort_values(by='Rank Coping', ascending=False).head(20)

# rename columns
top_20_hazard.columns = ['Country', 'ISO', 'Rank_Hazard']
top_20_vulnerability.columns = ['Country', 'ISO', 'Rank_Vulnerability']
top_20_coping.columns = ['Country', 'ISO', 'Rank_Coping']

# merge the top 20 lists into df
merged_df = pd.merge(top_20_hazard, top_20_vulnerability, on='Country', how='outer')
merged_df = pd.merge(merged_df, top_20_coping, on='Country', how='outer')

# calc combined score
merged_df['Combined_Rank'] = merged_df[['Rank_Hazard', 'Rank_Vulnerability', 'Rank_Coping']].sum(axis=1)

# join with pop data
population_df.columns = ['Country', 'ISO_Population', 'Population_Number']
normalized_df = pd.merge(merged_df, population_df[['Country', 'Population_Number']], on='Country', how='left')

# calc normalized ranks by dividing each rank by the corresponding population
normalized_df['Rank_Hazard_Normalized'] = normalized_df['Rank_Hazard'] / normalized_df['Population_Number']
normalized_df['Rank_Vulnerability_Normalized'] = normalized_df['Rank_Vulnerability'] / normalized_df['Population_Number']
normalized_df['Rank_Coping_Normalized'] = normalized_df['Rank_Coping'] / normalized_df['Population_Number']

# calc the combined normalized rank
normalized_df['Combined_Normalized_Rank'] = normalized_df[['Rank_Hazard_Normalized', 'Rank_Vulnerability_Normalized', 'Rank_Coping_Normalized']].sum(axis=1)

# sort on combined normalized rank
final_df = normalized_df.sort_values(by='Combined_Normalized_Rank', ascending=False).head(20)

print(final_df[['Country', 'Combined_Normalized_Rank']])