Access to Drinking Water : Transforming the Data Into Shape
By the spreadsheet whisperer who didn’t sign up for this level of drama but made it through anyway.
Introduction: From Raw to Revealing
In Part 1 of this journey, we explored the big picture of global access to drinking water. But this time, we rolled up our sleeves and got into the messy middle: transforming the data.
I quickly discovered, this wasn’t going to be a simple copy-paste-and-chart situation. Oh no. This was going to be a full-blown data wrangling adventure.
Step 1: Understanding the Year Distribution
The first challenge? Figuring out how the data was spread across years. I created a histogram to visualize the distribution of years and immediately noticed something odd: a huge spike in 2015 and 2020, and barely anything between 2016 and 2019.
This revealed that most countries were only sampled twice, often five years apart. So instead of tracking yearly trends it meant focusing on long-term changes, the Annual Rate of Change (ARC).
Step 2: Calculating ARC
To calculate ARC, this was the formula used:
ARC_x = (wat_bas_x(n+1) - wat_bas_x(n)) / (year(n+1) - year(n))Where x could be national (n), rural (r), or urban (u) access. But Google Sheets wasn’t going to make this easy. ARC values only made sense when comparing two rows from the same country, and missing data (hello, “null” strings) caused all kinds of #VALUE! errors.
Step 3: Summarizing ARC Values
Once ARC values were calculated, a summary table was created to count:
- Countries with missing ARC
- Countries with full access (100% in both years)
- Countries with ARC = 0, < 0, or > 0
This helped reveal some surprising insights:
- Urban areas had the most countries with declining access.
- Rural areas showed the highest ARC values but that didn’t mean they were improving faster. Many urban countries already had 100% access, so their ARC was naturally zero.
Step 4: Visualizing ARC Differences
To compare rural and urban ARC values, a new column was created: ARC_diff. This showed the absolute difference between rural and urban ARC for each country.
Get Tech with Thelma’s stories in your inbox
Join Medium for free to get updates from this writer.
The histogram of ARC_diff showed that most countries had very small differences, with a few outliers.
Step 5: Adding Region Data
The original dataset didn’t include region info, so a Regions.csv file was imported and used VLOOKUP to match each country to its region:
=IFERROR(VLOOKUP(A2, Regions!A:B, 2, FALSE), "Unknown")Step 6: Pivot Tables and Regional Insights
Using pivot tables to calculate:
- Total population per region
- Average ARC_n and ARC_r per region
- Average AFR (Access to Functional Resources, another metric)
The results were eye-opening:
- Sub-Saharan Africa had the highest ARC_n and ARC_r values, but also the widest spread.
- North America had the lowest ARC values likely because many countries already had near-full access.
Step 7: Visualizing the Relationship
To tie it all together, a bubble chart was created:
- X-axis: National ARC
- Y-axis: Rural ARC
- Bubble size: Population
- Bubble color: Region
This chart revealed:
- Sub-Saharan countries were scattered across the chart — some improving rapidly, others declining.
- Larger populations tended to cluster around moderate ARC values (0–1%).
Final Thoughts
Transforming this dataset was technically possible, but emotionally complex.
