import pandas as pd
import numpy as np

Process JMP sanitation data and add the treatment data

First read the JMP processed sanitation surveys

df_jmp = pd.read_csv("jmp_sanitation_surveys.csv")
df_jmp

df_jmp = df_jmp.rename(columns={'alpha.3':'iso3'}).drop(columns=["alpha.2","numeric"])
df_jmp['year'] = df_jmp['source'].str[4:8].apply(lambda x: int(x))

df_jmp = df_jmp.set_index(["source","context","classific_id"])
df_jmp.head()

The GloWPa model uses different sanitation categories. In the next steps the JPM sanitation categories are mapped to the GloWPa sanitation categories.

df_harmon = pd.DataFrame({
    'classific_id': range(1, 18),
    'san': ["flushSewer", "flushSeptic", "flushPit", "flushUnknown", "flushOpen",
            "pitSlab", "pitSlab", "pitSlab", "pitNoSlab", "hangingToilet", "bucketLatrine",
            "other", "compostingToilet", "pitSlab", "openDefecation", "other", "other"]
}).set_index("classific_id")

df_jmp = df_jmp.join(df_harmon, on = "classific_id", how = "left")
df_jmp.head(10)

Now, we need to check if the sanitation fractions from the surveys are complete for each survey (source) and at national, urban and rural level (context).

df_source_sums = df_jmp.groupby(["source","context"])["percentage"].sum()
df_source_sums.sort_values()

source          context 
KGZ_2008_WW     Rural       0.000
LVA_2015_PWH    Urban       0.000
LVA_2016_ES     Rural       0.000
                Urban       0.000
LVA_2016_ESDW   National    0.000
                            ...  
NGA_2006_GHS    Rural       1.344
MEX_1998_ENIGH  Rural       1.394
MEX_1996_ENIGH  Rural       1.402
EST_2006_HBS    Rural       1.429
MEX_1992_ENIGH  Rural       1.471
Name: percentage, Length: 23462, dtype: float64

We include all surveys when the total fraction is between 0.95 and 1.05. Later these fractions will be normalized.

complete_threshold_lower  = 0.95
complete_threshold_upper = 1.05
df_complete_sources = df_source_sums[(df_source_sums > complete_threshold_lower) & (df_source_sums < complete_threshold_upper)]
df_complete_sources.sort_values()

source           context 
MDG_2005_EP      National    0.950
TZA_1996_DHS     Urban       0.950
MRT_2000_EPCV    Rural       0.950
MNG_2010_HSES    Rural       0.951
RUS_2003_WHS     Rural       0.951
                             ...  
CHN_2003_NHSS    Rural       1.043
MNG_2015_HSES    Rural       1.046
DEU_2012_EUSILC  National    1.048
MEX_2008_ENIGH   Urban       1.048
BRA_2008_PNAD    Rural       1.049
Name: percentage, Length: 6896, dtype: float64

df_jmp_complete = df_jmp.reset_index("classific_id").loc[df_complete_sources.index]
df_jmp_complete.head()

Now for each country and context, we need the most recent year of complete data

recent_year_idx = df_jmp_complete.reset_index().groupby(["iso3","context"])["year"].idxmax()
idx_complete = df_jmp_complete.iloc[recent_year_idx].index
idx_complete

MultiIndex([(  'ABW_2020_CEN', 'National'),
            ('AFG_2020_IELFS', 'National'),
            ('AFG_2020_IELFS',    'Rural'),
            ('AFG_2020_IELFS',    'Urban'),
            ( 'AGO_2016_IIMS', 'National'),
            ( 'AGO_2016_IIMS',    'Rural'),
            ( 'AGO_2016_IIMS',    'Urban'),
            (  'AIA_2009_CPA', 'National'),
            (  'AIA_2009_CPA',    'Urban'),
            (  'ALB_2018_DHS', 'National'),
            ...
            (  'YEM_2019_WFP',    'Urban'),
            (  'ZAF_2021_GHS', 'National'),
            (  'ZAF_2021_GHS',    'Rural'),
            (  'ZAF_2021_GHS',    'Urban'),
            (  'ZMB_2018_DHS', 'National'),
            (  'ZMB_2018_DHS',    'Rural'),
            (  'ZMB_2018_DHS',    'Urban'),
            ( 'ZWE_2019_MICS', 'National'),
            ( 'ZWE_2019_MICS',    'Rural'),
            ( 'ZWE_2019_MICS',    'Urban')],
           names=['source', 'context'], length=619)

df_jmp_complete_recent = df_jmp_complete.loc[idx_complete]
df_jmp_complete_recent

Now normalize the fractions so all fractions will sum up to one

df_jmp_complete_recent = df_jmp_complete_recent.join(df_complete_sources.rename('sum_frac'))
df_jmp_complete_recent['frac'] = df_jmp_complete_recent['percentage'] / df_jmp_complete_recent['sum_frac']
df_jmp_complete_recent.head()

df_jmp_recent_agg = df_jmp_complete_recent.reset_index().groupby(["iso3","context","san"])[['frac','source']].agg({'frac':'sum','source':'first'})
df_jmp_recent_agg

Create a empty data.frame with all countries (iso3), sanitation types (san) and context. Update the rows with most complete and recent surveys. There will be rows with missing data.

iso3_vals = df_jmp['iso3'].unique()
context = ["Urban","Rural"]
sani_type = df_harmon['san'].unique()
df_jmp_glowpa = pd.DataFrame({"frac": np.nan, "source":None},index = pd.MultiIndex.from_product([iso3_vals,context,sani_type], names=["iso3","context","san"]))
df_jmp_glowpa.update(df_jmp_recent_agg, join="left")

df_jmp_glowpa.head()

Now, manipulate the national data and use these values when urban and rural sanitation fractions are missing.

# slice the most recent and complate national data
df_jmp_recent_agg_nat = df_jmp_recent_agg.xs("National", level = "context")
# manipulate the context, so we can use these indices to update the final data.frame.
df_jmp_recent_agg_nat['context'] = 'Urban'
df_jmp_recent_agg_nat2 = df_jmp_recent_agg_nat.copy()
# do same for rural
df_jmp_recent_agg_nat2['context'] = 'Rural'
df_jmp_recent_agg_nat = pd.concat([df_jmp_recent_agg_nat, df_jmp_recent_agg_nat2]).reset_index().set_index(['iso3','context','san'])
df_jmp_recent_agg_nat

/tmp/ipykernel_45892/252835684.py:4: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_jmp_recent_agg_nat['context'] = 'Urban'

count the number of NA

df_jmp_glowpa.isna().sum()

frac      816
source    816
dtype: int64

join national data when urban and/or rural values are missing

df_jmp_glowpa.update(df_jmp_recent_agg_nat, join="left", overwrite=False)

count the number of NA again

df_jmp_glowpa.isna().sum()

frac      132
source    132
dtype: int64

Check which indices have missing values.

df_jmp_glowpa[df_jmp_glowpa.isna().any(axis = 1)].reset_index('san').index.unique()

MultiIndex([('BWA', 'Rural'),
            ('COK', 'Urban'),
            ('COK', 'Rural'),
            ('GNQ', 'Urban'),
            ('GNQ', 'Rural'),
            ('NCL', 'Urban'),
            ('NCL', 'Rural'),
            ('SPM', 'Urban'),
            ('SPM', 'Rural'),
            ('TKL', 'Urban'),
            ('TKL', 'Rural')],
           names=['iso3', 'context'])

BWA and GNQ could adopt the values from either Rural or Urban. For we drop them. validate the final sums.

df_jmp_glowpa_cleaned = df_jmp_glowpa.dropna()
(df_jmp_glowpa_cleaned.groupby(["iso3","context"])["frac"].sum() > 1.01).sum()

0

(df_jmp_glowpa_cleaned.groupby(["iso3","context"])["frac"].sum() < 0.99).sum()

0

Read the treatment dataset.

df_treatment = pd.read_csv("jmp_treatment.csv", sep = ",")
df_treatment = df_treatment.rename(columns={"ISO3":"iso3","Residence Type":"context"}).set_index(["iso3","context","Service Level"])
df_treatment

Drop some headers which were read as data rows.

df_treatment = df_treatment.drop([("ISO3","Residence Type","Service Level")])
df_treatment

Transform percentages into fractions.

df_service = df_treatment.reset_index().pivot(index=["iso3","context"],columns="Service Level", values = "Coverage")
df_service.rename(index={"total":"National","urban":"Urban","rural": "Rural"}, level="context", inplace=True)
df_service.rename(columns={"Disposed insitu":"coverBury","Faecal sludge treated":"fecalSludgeTreated","Sewage treated":"sewageTreated"}, inplace = True)
df_service["coverBury"] = df_service["coverBury"].astype(float) / 100
df_service["fecalSludgeTreated"] = df_service["fecalSludgeTreated"].astype(float) / 100
df_service["sewageTreated"] = df_service["sewageTreated"].astype(float) / 100

df_service_subnat = df_service.loc[(slice(None) ,["Urban","Rural"]),:]
df_service_subnat

Update missing data for urban and rural areas with national data if present.

df_service_nat = df_service.loc[(slice(None) ,["National"]),:]
df_service_subnat.update(df_service_nat.rename(index={"National":"Rural"}, level="context"), overwrite=False)
df_service_subnat.update(df_service_nat.rename(index={"National":"Urban"}, level="context"), overwrite=False)

df_service_subnat

Here we add some required constants to the sanitation data.

df_jmp_frac_glowpa_table = df_jmp_glowpa_cleaned.reset_index().pivot(index=['iso3','context'], columns='san', values='frac')
# The GloWPa model expects the containerBased sanitation type, but it is not provided by the JMP surveys.
df_jmp_frac_glowpa_table["containerBased"] = np.nan
df_jmp_frac_glowpa_table["onsiteDumpedland"] = 0.1
df_jmp_frac_glowpa_table["emptyFrequency"] = 3
df_jmp_frac_glowpa_table["pitAdditive"] = 0
df_jmp_frac_glowpa_table["urine"] = 0
df_jmp_frac_glowpa_table["twinPits"] = 0
df_jmp_frac_glowpa_table.head()

# merge treatment
df_jmp_frac_treatment_glowpa_table = df_jmp_frac_glowpa_table.join(df_service_subnat, on=["iso3","context"], how="left")
df_jmp_frac_treatment_glowpa_table

# manipulate nan
df_jmp_frac_treatment_glowpa_table[["coverBury","fecalSludgeTreated"]] = df_jmp_frac_treatment_glowpa_table[["coverBury","fecalSludgeTreated"]].fillna(value = 0)
df_jmp_frac_treatment_glowpa_table["isWatertight"] = df_jmp_frac_treatment_glowpa_table["fecalSludgeTreated"]
df_jmp_frac_treatment_glowpa_table["hasLeach"] = df_jmp_frac_treatment_glowpa_table["fecalSludgeTreated"]
df_jmp_frac_treatment_glowpa_table

df_jmp_frac_glowpa_table_rur = df_jmp_frac_treatment_glowpa_table.xs("Rural", level = "context")
df_jmp_frac_glowpa_table_urb = df_jmp_frac_treatment_glowpa_table.xs("Urban", level = "context")
df_jmp_frac_glowpa_table_urb.head()

df_jmp_frac_glowpa_table_all = df_jmp_frac_glowpa_table_urb.merge(df_jmp_frac_glowpa_table_rur, on = "iso3" ,suffixes = ['_urb','_rur'])
df_jmp_frac_glowpa_table_all

store the sources used for each iso3 and context

df_jmp_source_glowpa_table = df_jmp_glowpa_cleaned.reset_index().groupby(["iso3","context"])["source"].first().to_frame().reset_index().pivot(index = ["iso3"], columns = ["context"], values = "source")
df_jmp_source_glowpa_table.head()

df_jmp_frac_glowpa_table_all.to_csv("glowpa_jmp_frac_level0_global.csv", float_format='%.3f')
df_jmp_source_glowpa_table.to_csv("glowpa_jmp_source_level0_global.csv")

df_jmp_frac_glowpa_table_all.index

Index(['ABW', 'AFG', 'AGO', 'AIA', 'ALB', 'AND', 'ARE', 'ARG', 'ARM', 'ASM',
       ...
       'VGB', 'VIR', 'VNM', 'VUT', 'WLF', 'WSM', 'YEM', 'ZAF', 'ZMB', 'ZWE'],
      dtype='object', name='iso3', length=225)

In total 225 countries are covered with jmp sanitation data.