IOM DRC | Mobility Tracking | Data Visualisation (Part 3 – Excel)

Title:

Data cleaning for the DRC’s Mobility Tracking report (Part 2)

Objective:

Predominately working with the Democratic Republic of the Congo team as a Data and Reporting Analyst, it falls to me to provide all of the analysis in their regular reports. This is Part 2 of 3, where I will be showcasing some of the python code used for their Mobility Tracking reports.

Methods:

Python, Data Visualisation

Explanation

Predominately working with the Democratic Republic of the Congo Team as a Data and Reporting Analyst, it fell to me to provide all of the analysis in their regular reports. One of their most important reports is their Mobility Tracking Assessments, which track the intentions, needs and movements of internally displaced people and returnees for the four eastern provinces. This article is about how I use Python to verify and output the analysis needed for the North Kivu Mobility Tracking report – you can find a link to the full report and data here.

Analysis: Page 1

I started by loading the excel sheet data from the pre-cleaned dataset, available online in from the link above. Usually, the datasets we receive need extensive cleaning, or they have been cleaned in the field, but need to be verified before analysis. This is because as things in the field tend to be very rushed and unstable, we act as an extra layer of quality control for country teams. Although I cannot show you all the checks I do due to sensitivity, I can show you a common issue I see as an analyst: mismatching P-Codes and names. P-Codes are a way for assigning and organising location data into administrative levels that are standardised. These codes often form the basis of our analysis. In the below code, you’ll see that I also load in a dataset called ‘NK error’ this is to illustrate the kind of P-Code error I’m referring to. ‘NK P’ is the previous cycle’s dataset, used for later calculations, also available online from the IOM DTM website.

P-Code Example for North Kivu

Basic Checks


#Setting up the libraries and loading the sheet

import numpy as np
import pandas as pd

dfv = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK Error.xlsx', sheet_name='VILLAGES')

dfvc = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK.xlsx', sheet_name='VILLAGES')

dfac = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK.xlsx', sheet_name='APERCU')

dfcm = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK.xlsx', sheet_name='SITES_CCCM')

dfncm = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK.xlsx', sheet_name='SITES_NON_CCCM')

pdfv = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK P.xlsx', sheet_name='VILLAGES')

pdfcm = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK P.xlsx', sheet_name='SITES_CCCM')

pdfncm = pd.read_excel(r'C:\Users\miredale\OneDrive - International Organization for Migration - IOM\Desktop\DRC Python Script\NK P.xlsx', sheet_name='SITES_NON_CCCM')

#Define a function called Basic Checks that use a dictionary of the admin data and labels and verifies if the dataset is correct

def BasicChecks(dfv):
CompD1 = {'NORD-KIVU' : 'CD61'}

CompD2 = {'BENI (Territoire / Oicha)': 'CD6107',
'BENI (Ville)': 'CD6109',
'BUTEMBO (Ville)': 'CD6110',
'GOMA' : 'CD6101',
'LUBERO' : 'CD6105',
'MASISI' : 'CD6103',
'NYIRAGONGO' : 'CD6102',
'RUTSHURU' : 'CD6111',
'WALIKALE' : 'CD6104'}

CompD3 = {'KALUNGUTA': 'CD6107ZS01',
'KAMANGO': 'CD6107ZS02',
'KYONDO': 'CD6107ZS03',
'MABALAKO': 'CD6107ZS04',
'MUTWANGA': 'CD6107ZS05',
'OICHA': 'CD6107ZS06',
'VUHOVI': 'CD6107ZS07',
'BENI (ville)': 'CD6109ZS01',
'BUTEMBO': 'CD6110ZS01',
'KATWA': 'CD6110ZS02',
'GOMA': 'CD6101ZS01',
'KARISIMBI': 'CD6101ZS02',
'ALIMBONGO': 'CD6105ZS01',
'BIENA': 'CD6105ZS02',
'KAYNA': 'CD6105ZS03',
'LUBERO': 'CD6105ZS04',
'MANGUREDJIPA': 'CD6105ZS05',
'MASEREKA': 'CD6105ZS06',
'MUSIENENE': 'CD6105ZS07',
'KATOYI': 'CD6103ZS01',
'KIROTSHE': 'CD6103ZS02',
'MASISI': 'CD6103ZS03',
'MWESO': 'CD6103ZS04',
'NYIRAGONGO': 'CD6102ZS01',
'BAMBO': 'CD6111ZS01',
'BINZA': 'CD6111ZS02',
'BIRAMBIZO': 'CD6111ZS03',
'KIBIRIZI': 'CD6111ZS04',
'RUTSHURU': 'CD6111ZS05',
'RWANGUBA': 'CD6111ZS06',
'ITEBERO': 'CD6104ZS01',
'KIBUA': 'CD6104ZS02',
'PINGA': 'CD6104ZS03',
'WALIKALE': 'CD6104ZS04'}

Correct=True
#Province matches Territory
for index, row in dfv.iterrows():
if row['admin_1_code'][:4] != row['admin_2_code'][:4]:
print(f"Row {index} does not match: {row['admin_1_code']} -> {row['admin_2_code']}")
Correct = False

#Province matches Zone de Sante
for index, row in dfv.iterrows():
if row['admin_1_code'][:4] != row['admin_3_code'][:4]:
print(f"Row {index} does not match: {row['admin_1_code']} -> {row['admin_3_code']}")
Correct = False

#Territory matches Zone de Sante
for index, row in dfv.iterrows():
if row['admin_2_code'][:6] != row['admin_3_code'][:6]:
print(f"Row {index} does not match: {row['admin_2_code']} -> {row['admin_3_code']}")
Correct = False

#Province Check
for index, row in dfv.iterrows():
label1 = row['admin_1_label']
pcode1 = row['admin_1_code']
if CompD1.get(label1) != (pcode1):
print(f"Row {index} does not match: {label1} -> {pcode1}")
Correct = False

#Territory Checks
for index, row in dfv.iterrows():
label2 = row['admin_2_label']
pcode2 = row['admin_2_code']
if CompD2.get(label2) != (pcode2):
print(f"Row {index} does not match: {label2} -> {pcode2}")
Correct = False

#ZS Checks
for index, row in dfv.iterrows():
label3 = row['admin_3_label']
pcode3 = row['admin_3_code']
if CompD3.get(label3) != (pcode3):
print(f"Row {index} does not match: {label3} -> {pcode3}")
Correct = False
if Correct:
print("All good")

Output of the Code (from VSC)

Reasons for Return

This next piece of python code sort of acts like a SUMIF calculation in excel and it is used to assess the reasons that a returning IDP may return back to province, measured over a 4-year period. The output of the code is for Figures 10 and 11. Each block of code assess a particular reason for return. As you’ll notice the output of the code is French, as is the report. We rarely do English versions of the reports for DRC as it is a French-speaking country.


#Raisons de Retour
Security21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'meilleur_secu']['actual_RET_ind_arrived_2021_total'].sum()
Security22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'meilleur_secu']['actual_RET_ind_arrived_2022_total'].sum()
Security23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'meilleur_secu']['actual_RET_ind_arrived_2023_total'].sum()
Security24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'meilleur_secu']['actual_RET_ind_arrived_2024_total'].sum()
Security = Security21 + Security22 + Security23 + Security24

AL21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'meilleur_sit_alimenitaire']['actual_RET_ind_arrived_2021_total'].sum()
AL22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'meilleur_sit_alimenitaire']['actual_RET_ind_arrived_2022_total'].sum()
AL23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'meilleur_sit_alimenitaire']['actual_RET_ind_arrived_2023_total'].sum()
AL24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'meilleur_sit_alimenitaire']['actual_RET_ind_arrived_2024_total'].sum()
AL = AL21+ AL22 + AL23 + AL24

Regroup21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'regroupement']['actual_RET_ind_arrived_2021_total'].sum()
Regroup22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'regroupement']['actual_RET_ind_arrived_2022_total'].sum()
Regroup23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'regroupement']['actual_RET_ind_arrived_2023_total'].sum()
Regroup24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'regroupement']['actual_RET_ind_arrived_2024_total'].sum()
Regroup = Regroup21 + Regroup22 + Regroup23 + Regroup24

PCZD21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'pire_conditions_zone_deplacement']['actual_RET_ind_arrived_2021_total'].sum()
PCZD22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'pire_conditions_zone_deplacement']['actual_RET_ind_arrived_2022_total'].sum()
PCZD23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'pire_conditions_zone_deplacement']['actual_RET_ind_arrived_2023_total'].sum()
PCZD24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'pire_conditions_zone_deplacement']['actual_RET_ind_arrived_2024_total'].sum()
PCZD = PCZD21 + PCZD22 + PCZD23 + PCZD24

Econ21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'opportunite_economique']['actual_RET_ind_arrived_2021_total'].sum()
Econ22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'opportunite_economique']['actual_RET_ind_arrived_2022_total'].sum()
Econ23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'opportunite_economique']['actual_RET_ind_arrived_2023_total'].sum()
Econ24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'opportunite_economique']['actual_RET_ind_arrived_2024_total'].sum()
Econ = Econ21+ Econ22 + Econ23 + Econ24

Service21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'meilleur_services']['actual_RET_ind_arrived_2021_total'].sum()
Service22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'meilleur_services']['actual_RET_ind_arrived_2022_total'].sum()
Service23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'meilleur_services']['actual_RET_ind_arrived_2023_total'].sum()
Service24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'meilleur_services']['actual_RET_ind_arrived_2024_total'].sum()
Service = Service21 + Service22 + Service23 + Service24

San21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'meilleur_sit_sanitaire']['actual_RET_ind_arrived_2021_total'].sum()
San22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'meilleur_sit_sanitaire']['actual_RET_ind_arrived_2022_total'].sum()
San23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'meilleur_sit_sanitaire']['actual_RET_ind_arrived_2023_total'].sum()
San24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'meilleur_sit_sanitaire']['actual_RET_ind_arrived_2024_total'].sum()
San = San21 + San22 + San23 + San24

ISZD21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'insecurite_zone_deplacement']['actual_RET_ind_arrived_2021_total'].sum()
ISZD22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'insecurite_zone_deplacement']['actual_RET_ind_arrived_2022_total'].sum()
ISZD23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'insecurite_zone_deplacement']['actual_RET_ind_arrived_2023_total'].sum()
ISZD24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'insecurite_zone_deplacement']['actual_RET_ind_arrived_2024_total'].sum()
ISZD = ISZD21+ ISZD22 + ISZD23 + ISZD24

Reso21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'resolution_litiges']['actual_RET_ind_arrived_2021_total'].sum()
Reso22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'resolution_litiges']['actual_RET_ind_arrived_2022_total'].sum()
Reso23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'resolution_litiges']['actual_RET_ind_arrived_2023_total'].sum()
Reso24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'resolution_litiges']['actual_RET_ind_arrived_2024_total'].sum()
Reso = Reso21 + Reso22 + Reso23 + Reso24

RScol21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'raison_scolaire']['actual_RET_ind_arrived_2021_total'].sum()
RScol22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'raison_scolaire']['actual_RET_ind_arrived_2022_total'].sum()
RScol23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'raison_scolaire']['actual_RET_ind_arrived_2023_total'].sum()
RScol24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'raison_scolaire']['actual_RET_ind_arrived_2024_total'].sum()
RScol = RScol21 + RScol22 + RScol23 + RScol24

CZD21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'catastrophe_zone_deplacement']['actual_RET_ind_arrived_2021_total'].sum()
CZD22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'catastrophe_zone_deplacement']['actual_RET_ind_arrived_2022_total'].sum()
CZD23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'catastrophe_zone_deplacement']['actual_RET_ind_arrived_2023_total'].sum()
CZD24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'catastrophe_zone_deplacement']['actual_RET_ind_arrived_2024_total'].sum()
CZD = CZD21+ CZD22 + CZD23 + CZD24

Autre21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'autre']['actual_RET_ind_arrived_2021_total'].sum()
Autre22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'autre']['actual_RET_ind_arrived_2022_total'].sum()
Autre23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'autre']['actual_RET_ind_arrived_2023_total'].sum()
Autre24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'autre']['actual_RET_ind_arrived_2024_total'].sum()
Autre = Autre21 + Autre22 + Autre23 + Autre24

SL21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'SL']['actual_RET_ind_arrived_2021_total'].sum()
SL22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'SL']['actual_RET_ind_arrived_2022_total'].sum()
SL23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'SLt']['actual_RET_ind_arrived_2023_total'].sum()
SL24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'SL']['actual_RET_ind_arrived_2024_total'].sum()
SL = SL21+ SL22 + SL23 + SL24

Epi21 = dfvc[dfvc['actual_RET_arrived_2021_reason']== 'epidemie']['actual_RET_ind_arrived_2021_total'].sum()
Epi22 = dfvc[dfvc['actual_RET_arrived_2022_reason']== 'epidemie']['actual_RET_ind_arrived_2022_total'].sum()
Epi23 = dfvc[dfvc['actual_RET_arrived_2023_reason']== 'epidemie']['actual_RET_ind_arrived_2023_total'].sum()
Epi24 = dfvc[dfvc['actual_RET_arrived_2024_reason']== 'epidemie']['actual_RET_ind_arrived_2024_total'].sum()
Epi = Epi21 + Epi22 + Epi23 + Epi24

TotalRR = Security + AL + Regroup + PCZD + Econ + Service + San + ISZD + Reso + RScol + CZD + Autre + SL + Epi

#Printing out the individual results

print(f'Amélioration de la sit. Sécuritaire: {((Security/TotalRR)*100).round(0)}')
print(f'Amélioration de la sit. Alimentaire: {((AL/TotalRR)*100).round(0)}')
print(f'Regroupement: {((Regroup/TotalRR)*100).round(0)}')
print(f'Pire condition dans la zone de déplacement: {((PCZD/TotalRR)*100).round(0)}')
print(f'Opportunité économique: {((Econ/TotalRR)*100).round(0)}')
print(f'Amélioration de services: {((Service/TotalRR)*100).round(0)}')
print(f'Amélioration de la sit. Sanitaire: {((San/TotalRR)*100).round(0)}')
print(f'Insécurité dans la zone de déplacement: {((ISZD/TotalRR)*100).round(0)}')
print(f'Resolution de litiges: {((Reso/TotalRR)*100).round(0)}')
print(f'Raison scolaire: {((RScol/TotalRR)*100).round(0)}')
print(f'Catastrophe dans la zone de déplacement: {((CZD/TotalRR)*100).round(0)}')
print(f'Autre: {((Autre/TotalRR)*100).round(0)}')
print(f'SL: {((SL/TotalRR)*100).round(0)}')
print(f'Epidemie: {((Epi/TotalRR)*100).round(0)}')

SecT = ((Security/TotalRR)*100).round(0)
AlimenT = ((AL/TotalRR)*100).round(0)
RegT = ((Regroup/TotalRR)*100).round(0)
PCZDT = ((PCZD/TotalRR)*100).round(0)
OET = ((Econ/TotalRR)*100).round(0)
ServT = ((Service/TotalRR)*100).round(0)
SanT = ((San/TotalRR)*100).round(0)
InsT = ((ISZD/TotalRR)*100).round(0)
ResT = ((Reso/TotalRR)*100).round(0)
ScolT = ((RScol/TotalRR)*100).round(0)
CataT = ((CZD/TotalRR)*100).round(0)
AutT = ((Autre/TotalRR)*100).round(0)
SLT = ((SL/TotalRR)*100).round(0)
EpiT = ((Epi/TotalRR)*100).round(0)

#New frame of results
data = {
'Category' : ['Amélioration de la sit. Sécuritaire', 'Amélioration de la sit. Alimentaire',
'Regroupement','Pire condition dans la zone de déplacement','Opportunité économique', 'Amélioration de services',
'Amélioration de la sit. Sanitaire', 'Insécurité dans la zone de déplacement', 'Resolution de litiges', 'Raison scolaire',
'Catastrophe dans la zone de déplacement', 'Autre', 'SL', 'Epidemie'],
'Value' : [SecT, AlimenT,RegT,PCZDT,OET,ServT,SanT,InsT,ResT,ScolT,CataT,AutT,SLT,EpiT] }

#Sorting and printing the top 3 results 
ReasonR = pd.DataFrame(data)
ReasonR = ReasonR.sort_values('Value', ascending=False)
ReasonR = ReasonR[0:3]
print(ReasonR)

Output of the Code: Reasons for Return (%)

End of Part 1

There you have it, some useful code for some simple and insightful analysis.