Data cleaning, P-codes, service analysis and situation map

Rules: (1) one site = one commune P-code + one standardised name + coordinates less than 100 m apart; (2) between two valid values, keep the most recent; (3) 0 and 9999 are missing values, never zeros; (4) consistency check people = households × 5 (± 10%); (5) every correction is logged and reported back to the source.

Errors: rows 1-2: same site (spelling, about 55 m apart), keep row 2 (28/09). Rows 3-4: duplicate; ML050107 does not exist in the reference list, corrected to ML050103. Row 5: latitude and longitude swapped (the point would fall in Central Africa); corrected to 14.3501 / -3.6110. Row 6: 0 = missing. Row 7: coordinates in degrees-minutes-seconds, converted: 14 + 31/60 + 36/3600 = 14.5267; -(4 + 5/60 + 50/3600) = -4.0972; this is the same site as row 6. Row 8: 9999 = missing. Row 9: 520 × 5 = 2,600: 26,000 is a data entry error, corrected to 2,600 and flagged "to be confirmed".

The naive sum of the raw rows (49,249 people, 9999 included) overestimated the population by almost 4 times.

Priorities: Socoura Est and Sévaré Gare are below both thresholds; Djenné Route lacks water; Bandiagara Nord lacks latrines.

Requirements: water: Socoura 2,150 × 15 = 32.25 m³, i.e. + 11.25; Sévaré 46.35 m³, i.e. + 11.35; Djenné 22.5 m³, i.e. + 6.5; total + 29.1 m³/day. Latrines: Socoura 108 needed, i.e. + 48; Sévaré 155, i.e. + 60; Bandiagara 95, i.e. + 15; total + 123.

Raw data: with 26,000 people in Konna, we would have calculated 1.5 l/person/day and launched an unnecessary emergency response; with the 0 for Sévaré, the most populated site would have disappeared from the analysis. A data error becomes an allocation error.

Elements: precise title ("Displacement sites and water and sanitation gaps, Mopti region, as of 28 September"), production date, data sources and dates, scale, north arrow, legend, location inset, administrative boundaries from the COD with their names, agency logo, disclaimer on boundaries and names, contact and version number.

Symbology: circles proportional to population; colour according to the gap against thresholds (none, one, two); no colour gradient for absolute numbers; 4 to 5 classes at most; palette readable in black and white.

Coordinates: WGS 84 (EPSG:4326) in decimal degrees for exchange; suitable projection for display and distance measurement (UTM zone).

Metadata: cleaning rules, corrected values, confidence level per site, sensitivity level.

HXL: #loc+name; #adm3+name; #adm3+code; #geo+lat; #geo+lon; #affected+idps; #org; #date. One tag per column, below the human-readable header.

Refusal of personal data and ethnic data: they are not necessary for the stated purpose, they expose people to reprisals and violate the principles of data minimisation and "do no harm". The exact coordinates of displacement sites in a conflict area are sensitive data: classification according to the operation's sensitivity matrix.

I do not decide alone: I pass the request to my supervisor and to civil-military coordination (CMCoord), which manages dialogue with armed forces. The humanitarian channel may share, where appropriate, aggregated information (number of sites and people per commune) or use a humanitarian site notification mechanism designed for this purpose. I document the request and the reply.

• Mobile form: cascading drop-down lists for region, cercle, commune linked to P-codes; automatic GPS capture; no free-text entry of coordinates.
• Entry constraints: people between 2 and 12 times households; missing value coded as blank and not 0 or 9999; mandatory fields.
• Master site list with a unique identifier shared among the three sources; any new site is validated by the IMO.
• Governance: common collection calendar, correction log, monthly feedback to sources, data-sharing agreement.