Title:

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

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 1 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.

Verifying the data

I start by loading the excel sheet data from the DRC mission. 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, whilst giving the missions the chance to address more pressing humanitarian work in the field. 
 
The data below consists of 5 sheets: 
   1. ‘df_locations’ – all of the information we have IDPs and Returnees currently being hosted by people in their homes.
   2. ‘df_cm’ –  all of the IDPs in emergency camps which are managed by other UN actors.
   3. ‘df_ncm – all of the IDPs in emergency camps which are not managed by other UN actors.
   4. ‘df_grp_idp’ – all collected data on IDPs.
   5. ‘df_grp_ret’ – all collected data on Returnees.
   6. ‘df_overview’ – the main aspects of all data sheets aggregated into an overview, alongside the most recent population figures available.
 
I begin by doing some cleaning of my own and standardising my data for analysis by renaming columns and values across the sheets.

#Load in all the data

#Current Round's Data - adjust Sheet Names if needed
df_locations = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx', sheet_name='VILLAGES')
df_cm = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx', sheet_name='SITE_CCCM')
df_ncm = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx',sheet_name='SITE_NON_CCCM')
df_grp_idp = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx',sheet_name='GRP_IDP')
df_grp_ret = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx',sheet_name='GRP_RET')
df_overview = pd.read_excel(r'Real Test\Nord_Kivu_MT.xlsx',sheet_name='APERCU')
    

#Data Cleaning 

#Stripping leading and ending white space form titles

df_locations.columns = df_locations.columns.str.strip()
df_grp_idp.columns = df_grp_idp.columns.str.strip()
df_grp_ret.columns = df_grp_ret.columns.str.strip()
df_cm.columns = df_cm.columns.str.strip()
df_ncm.columns = df_ncm.columns.str.strip()
df_overview.columns = df_overview.columns.str.strip()

#Column Renaming
renaming_Map = { 
    'PROVINCE*': 'm0303_meta_adm1_label',  'PROVINCE': 'm0303_meta_adm1_label', 
    'Province': 'm0303_meta_adm1_label', 
    'CODE PROVINCE*': 'm0303_meta_adm1_pcode', 
    'CODE PROVINCE': 'm0303_meta_adm1_pcode',  
    'TERRITOIRE*': 'm0304_meta_adm2_label', 
    'TERRITOIRE': 'm0304_meta_adm2_label',
    'Territoire': 'm0304_meta_adm2_label',
    'CODE TERRITOIRE*': 'm0304_meta_adm2_pcode',
    'CODE TERRITOIRE': 'm0304_meta_adm2_pcode',
    'zone de santé': 'm0305_meta_adm3_label',
    'ZONE DE SANTE*': 'm0305_meta_adm3_label',
    'ZONE DE SANTE': 'm0305_meta_adm3_label',
    'CODE ZONE DE SANTE': 'm0305_meta_adm3_pcode',
    'code_ZS': 'm0305_meta_adm3_pcode',
    'INDIVIDUS': 'm0310_loc_num_idp_ind',
    'INDIVIDUS*': 'm0310_loc_num_idp_ind',
    'Individus - IDP': 'm0310_loc_num_idp_ind',
    'MENAGES' : 'm0309_loc_num_idp_hh',
    'MENAGES*': 'm0309_loc_num_idp_hh',
    'Menage - IDP': 'm0309_loc_num_idp_hh',
    'Type' : 'site_centre',
     'TYPE DE SITE*' : 'site_centre',
     'type1': 'site_centre',
     'TYPE DE SITE': 'site_centre',
     'Hommes IDP': 'm2968_loc_num_men_idp',
     'Hommes - IDP': 'm2968_loc_num_men_idp',
     'HOMME': 'm2968_loc_num_men_idp',
     'Femmes - IDP': 'm2969_loc_num_women_idp',
     'FEMME': 'm2969_loc_num_women_idp',
     'Enfants <5 - IDP': 'm2970_loc_num_children_idp',
     'ENFANT': 'm2970_loc_num_children_idp'
    } 

df_cm = df_cm.rename(columns=lambda x: renaming_Map.get(x, x))
df_ncm = df_ncm.rename(columns=lambda x: renaming_Map.get(x, x))


#Overview Renaming Map

overview_Renaming_Map = {
    'Population 2023 (Estimation de la DPS)' : 'Population 2023 (DPS)',
    'Population (DPS 2023)' : 'Population 2023 (DPS)',
    'Village connues' : 'Villages sur liste',
    'Villages connus (DTM)' : 'Villages sur liste',
    'Zone de santé' : 'Zone de sante'
    }
df_overview = df_overview.rename(columns=lambda x: overview_Renaming_Map.get(x, x))


#CCCM and Non-CCCM Value Renaming Map

CCCM_Non_CCCM_value_renaming_Map = {
    'Centre collectif' : 'Collective Centre',
    'Site Spontané' : 'Site',
    'Site Spontané ' : 'Site',
    'Site Planifié' : 'Site', 
    'Centre Collectif' : 'Collective Centre',
    'CC/ecole' : 'Collective Centre',
    'CC/eglise' : 'Collective Centre',
    'CC/stade' : 'Collective Centre',
    'CC/terrain' : 'Collective Centre',
    'CC/autre' : 'Collective Centre'
}

df_cm['site_centre'] = df_cm['site_centre'].replace(CCCM_Non_CCCM_value_renaming_Map)
df_ncm['site_centre'] = df_ncm['site_centre'].replace(CCCM_Non_CCCM_value_renaming_Map)
Next is a common issue I see as a data analyst that I always need to check for: 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 (across the almost 100 missions our division has active around the globe) – below is an example from the cleaned excel data sheets we receive from the field.
 

P-Code Example for North Kivu

Here is the ‘Basic Checks’ code I use to verify the P-Codes and their labels. It starts with an initial block that requires a bit of input from the user to choose which part of the script activates (and some bits that will be used later on).

#User Input 

#Province
Province = 'North Kivu'
ProvinceFR = 'NORD-KIVU'
#Previous Total IDPs 
previous_Total_IDPs = 1011335

#Previous Total Returnees 
previous_Total_RETs = 2033653
Using a combination of the get() function, dictionaries and conditional logic, this block defines a function called Basic Checks that compares both codes with the appropriate labels and that the codes follow sequentially.
 

As the administrative level becomes smaller, each P-Code has a numerical addition added to it, so it’s possible to compare sections to see if they match i.e. CD61 is the province, the highest administrative level, followed by the territory CD61 XX (where X = a unique set of numbers), followed by the health zone (or ‘zone de santé’ in French) as CD61 XX ZS YY, where YY is another unique sequence of numbers. The code below makes sure that XX and YY are correctly sequenced and then that each sequence has the correct name attached to it.

As these reports cover the 4 eastern provinces in DRC, the dictionaries contain data for all the provinces, but the above code allows the user to select which dictionary is used.


#Basic Checks Function to Check if Pcodes and Labels are Correct

def BasicChecks(df_grp_idp):
   if Province == 'North Kivu':
        CompD1 = {'NORD-KIVU' : 'CD61'}

        CompD2 = {'BENI TERRITOIRE (OICHA)': 'CD6107',
        'BENI (Ville)': 'CD6109',
        'BUTEMBO': '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': 'CD6109ZS01',
        'BUTEMBO': 'CD6110ZS01',
        'KATWA': 'CD6110ZS02',
        'GOMA': 'CD6101ZS01',
        'KARISIMBI': 'CD6101ZS02',
        'ALIMBONGO': 'CD6105ZS01',
        'BIENA': 'CD6105ZS02',
        'KAYINA': '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'}

    

   
 elif Province == 'Ituri':
        CompD1 = {'ITURI': 'CD54'}

        CompD2 = {'ARU': 'CD5409',
        'DJUGU': 'CD5405',
        'IRUMU': 'CD5402',
        'MAHAGI': 'CD5407',
        'MAMBASA': 'CD5403',
        'BUNIA': 'CD5401'}

        CompD3 = {'BUNIA': 'CD5401ZS01',
        'ADI': 'CD5409ZS01',
        'ADJA': 'CD5409ZS02',
        'ARIWARA': 'CD5409ZS03',
        'ARU': 'CD5409ZS04',
        'BIRINGI': 'CD5409ZS05',
        'LAYBO': 'CD5409ZS06',
        'BAMBU': 'CD5405ZS01',
        'DAMAS': 'CD5405ZS02',
        'DRODRO': 'CD5405ZS03',
        'FATAKI': 'CD5405ZS04',
        'JIBA': 'CD5405ZS05',
        'KILO': 'CD5405ZS06',
        'LINGA': 'CD5405ZS07',
        'LITA': 'CD5405ZS08',  
        'MANGALA': 'CD5405ZS09',
        'MONGBALU': 'CD5405ZS10',
        'NIZI': 'CD5405ZS11',
        'RETHY': 'CD5405ZS12',
        'TCHOMIA': 'CD5405ZS13',
        'BOGA': 'CD5402ZS01',
        'GETHY': 'CD5402ZS03',
        'KOMANDA': 'CD5402ZS04',
        'NYAKUNDE': 'CD5402ZS05',
        'RWAMPARA': 'CD5402ZS06',
        'ANGUMU': 'CD5407ZS01',
        'AUNGBA': 'CD5407ZS02',
        'KAMBALA': 'CD5407ZS03',
        'LOGO': 'CD5407ZS04',
        'MAHAGI': 'CD5407ZS05',
        'NYARAMBE': 'CD5407ZS06',
        'RIMBA': 'CD5407ZS07',
        'LOLWA': 'CD5403ZS01',
        'MAMBASA': 'CD5403ZS02',
        'MANDIMA': 'CD5403ZS03',
        'NIA-NIA': 'CD5403ZS04'}
        
        
        
    

   
   
   elif Province == 'South Kivu':
        CompD1 = {'SUD-KIVU': 'CD62'}

        CompD2 = {'BUKAVU': 'CD6201',
        'FIZI': 'CD6210',
        'IDJWI': 'CD6206',
        'KABARE': 'CD6202',
        'KALEHE': 'CD6205',
        'MWENGA': 'CD6212',
        'SHABUNDA': 'CD6203',
        'UVIRA': 'CD6208',
        'WALUNGU': 'CD6207'}

        CompD3 = {'BAGIRA': 'CD6201ZS01',
        'IBANDA': 'CD6201ZS02',
        'KADUTU': 'CD6201ZS03',
        'FIZI': 'CD6210ZS01',
        'KIMBI LULENGE': 'CD6210ZS02',
        'MINEMBWE': 'CD6210ZS03',
        'NUNDU': 'CD6210ZS04',
        'IDJWI': 'CD6206ZS01',
        'KABARE': 'CD6202ZS01',
        'KATANA': 'CD6202ZS03',
        'MITI MURHESA': 'CD6202ZS04',
        'NYANTENDE': 'CD6202ZS05',
        'BUNYAKIRI': 'CD6205ZS01',
        'KALEHE': 'CD6205ZS02',
        'KALONGE': 'CD6205ZS03',
        'MINOVA': 'CD6205ZS04',
        'ITOMBWE': 'CD6212ZS01',
        'KAMITUGA': 'CD6212ZS02',
        'KITUTU': 'CD6212ZS03',
        'MWANA': 'CD6212ZS04',
        'MWENGA': 'CD6212ZS05',
        'KALOLE': 'CD6203ZS01',
        'LULINGU': 'CD6203ZS02',
        'MULUNGU': 'CD6203ZS03',
        'SHABUNDA': 'CD6203ZS04',
        'HAUT-PLATEAU': 'CD6208ZS01',
        'LEMERA': 'CD6208ZS02',
        'RUZIZI': 'CD6208ZS03',
        'UVIRA': 'CD6208ZS04',
        'KANIOLA': 'CD6207ZS05',
        'KAZIBA': 'CD6207ZS01',
        'MUBUMBANO': 'CD6207ZS02',
        'NYANGEZI': 'CD6207ZS03',
        'WALUNGU': 'CD6207ZS04'}
        
        
        
    

   
elif Province == 'Tanganyika':
        CompD1 = {'TANGANYIKA': 'CD74'}

        CompD2 = {'KABALO': 'CD7407',
        'KALEMIE': 'CD7402',
        'KONGOLO': 'CD7409',
        'MANONO': 'CD7406',
        'MOBA': 'CD7404',
        'NYUNZU': 'CD7410'}

        CompD3 = {'KABALO': 'CD7407ZS01',
        'KALEMIE': 'CD7402ZS01',
        'NYEMBA': 'CD7402ZS02',
        'KONGOLO': 'CD7409ZS01',
        'MBULULA': 'CD7409ZS02',
        'ANKORO': 'CD7406ZS01',
        'KIAMBI': 'CD7406ZS02',
        'MANONO': 'CD7406ZS03',
        'KANSIMBA': 'CD7404ZS01',
        'MOBA': 'CD7404ZS02',
        'NYUNZU': 'CD7410ZS01'}
    

  Correct=True
   #Province matches Territory
   for index, row in df_grp_idp.iterrows():
        if str(row['m0303_meta_adm1_pcode'])[:4] != str(row['m0304_meta_adm2_pcode'])[:4]:
                print(f"Row {index} does not match: {row['m0303_meta_adm1_pcode']} -> {row['m0304_meta_adm2_pcode']}")
                Correct = False
#Province matches Zone de Sante
   for index, row in df_grp_idp.iterrows():
        if str(row['m0303_meta_adm1_pcode'])[:4] != str(row['m0305_meta_adm3_pcode'])[:4]:
                print(f"Row {index} does not match: {row['m0303_meta_adm1_pcode']} -> {row['m0305_meta_adm3_pcode']}")
                Correct = False
#Territory matches Zone de Sante
   for index, row in df_grp_idp.iterrows():
        if str(row['m0304_meta_adm2_pcode'])[:6] != str(row['m0305_meta_adm3_pcode'])[:6]:
                print(f"Row {index} does not match: {row['m0304_meta_adm2_pcode']} -> {row['m0305_meta_adm3_pcode']}")
                Correct = False 


#See if pcodes in the data match the correct forms in the dictionary
#Province Check
   for index, row in df_grp_idp.iterrows():
        label1 = row['m0303_meta_adm1_label']
        pcode1 = row['m0303_meta_adm1_pcode']
        if CompD1.get(label1) != (pcode1):
                print(f"This row {index} does not match Province dictionary: {label1} -> {pcode1}")
                Correct = False
#Territory Check
   for index, row in df_grp_idp.iterrows():
        label2 = row['m0304_meta_adm2_label']
        pcode2 = row['m0304_meta_adm2_pcode']
        if CompD2.get(label2) != (pcode2):
                print(f"This row {index} does not match Territory dictionary: {label2} -> {pcode2}")
                Correct = False
#ZS Check       
   for index, row in df_grp_idp.iterrows():
        label3 = row['m0305_meta_adm3_label']
        pcode3 = row['m0305_meta_adm3_pcode']
        if CompD3.get(label3) != (pcode3):
                print(f"This row {index} does not match ZS dictionary: {label3} -> {pcode3}")
                Correct = False
   if Correct:
        print("Everything is correct")

If the code flags an error, it will print the row where the mistake is and tell me what P-Code is not matching. I will then need to discuss with the operations team what data needs to be removed or replaced. 

If everything is good, then I get the below.

Finally, I need to check that the males and females in all the sheets equal the correct number of individuals. Below you can see the code I use for camps managed by other UN agencies. This data can be very difficult to collect, due to the constant flow of people and the difficult context on the ground. 

As a result, I use two different methods to find whether the total number of males and females is equal to the total number of individuals – both make use of the ‘try…except’ method with simple conditional logic. This is because sometimes only information on the number of males and females is available. Other times, we get a breakdown of age brackets and gender together, all of which need to be equal to the total number of individuals. The code below accounts for both eventualities. 


#Method 1 for checking demographics are correct for the CCCM

    total_Males_CCCM = df_cm[['0 - 5 H', '6 - 17 H', '18 - 59 H', '60 + H']].sum().sum()

    total_Females_CCCM = df_cm[['0 - 5 F', '6 - 17 F', '18 - 59 F', '60 + F']].sum().sum()

    if total_Males_CCCM + total_Females_CCCM == df_cm['m0310_loc_num_idp_ind'].sum():
        print('Correct Age Brackets Method 1: Males plus Females in "CCCM tab" is equal to the number of individuals.')
        age_Bracket_CCCM = True
    else:
        print('Incorrect Age Brackets Method 1: Males plus Females in "CCCM tab" is NOT equal to the number of individuals')
        age_Bracket_CCCM = False
    print('\n')
except KeyError:
    print('CCCM Tab is not in the Method 1 "age bracket" format')
    print('\n')


#Method 2 for checking demographics are correct for the CCCM
try: 
    if df_cm['m2968_loc_num_men_idp'].sum() + df_cm['m2969_loc_num_women_idp'].sum() == df_cm['m0310_loc_num_idp_ind'].sum():
        print('Correct Column Code Method 2: Males plus Females in "CCCM" is equal to the number of individuals.')
    else:
        print('Incorrect Column Code Method 2: Males plus Females in "CCCM" is NOT equal to the number of individuals')
    print('\n')

except KeyError:
    print('CCCM Tab is not in the Method 2 "column code" format')
    print('\n')

The output below tells me that the CCCM data sheet contains gender data in the form ‘Age Brackets’ but not in the simple male column and female column format (called the “column code” format). However, that data does correctly equal the number of individuals. The non-CCCM data sheet. however, does not meet either condition and requires further investigation (in this case, some data had been inputted incorrectly and the male and female columns actually represented men and women, but not children under 18 – adding all three together produced the correct number of total individuals).

End of Part 1

There you have it, some simple code for data verification and cleaning. In the next part, I’ll be showcasing a large part of the analysis script used for the Mobility Tracking reports.