This week I had to learn about Secure Multi-Party Computation and write an article about it. Talking about next-level data-analysis!
In December I paid some money for two online courses (or rather paid for video series) on data visualization at Domestika. This week I finally had the time and headspace to watch the course by Sonja Kuijpers, a Dutch data illustrator. In the videos Sonja shares her work process from start to finish. From hand collecting data from a well known children’s book to a beautifully designed poster with a visual representation of the data she collected. She shares how she searches for visual inspiration and she admits how messy the process can be at times. It is not a course where you learn about handling data or how to use Illustrator, but for me, just watching someone else going through the process and recognizing that every single step in that process is within my own capabilities, is well worth it.
My previous data project was based on data about houses in The Netherlands. That data was already collected and I tried to figure out whether I could find proof in that data on why prices for houses are going through the roof. For my next data project, inspired by Sonja’s course, I’m going to collect data myself and try my hand at creating my own data illustration based on that data.
Topic of my research will be the picture books we own and read to Daughter. When picture books started entering our home we both noticed how lame a lot of the stories are and how we sometimes read ‘she’ instead of ‘he’ or laugh with Daughter about all the princesses waiting for a prince to come and rescue them instead of rescuing themselves. Man and I try very much to read Daughter stories that portray girls in a positive and smart way. But how are the characters actually portrayed in the books in our possession?
I’m going to create a data set in which I count the main characters in the book and whether they are male or female, what character descriptions are used and how they are portrayed, what skin colour is used in the illustrations, whether the story is written and drawn by a man or a woman. I’m very curious what the collective data will tell me. Or rather, what the stories tell my Daughter.
Every now and then I ask myself what the added value is of my skills in storytelling. I mean, in terms of fighting of the spread of a virus, or mending those who get sick, telling stories is not exactly an essential skill. Nor is data analyses, my newly acquired skill. A wonderful graph doesn’t cure any disease.
But then I read an article like this, and I get reminded why I wanted to know more about handling data in the first place.
“If readers don’t relate to the information, they are less likely to act and use it,” said Slovic, a founder and president of Decision Research, a collection of scientists who study the human psyche.From datajournalism.com: Humanising data: Connecting numbers and people
In other words it’s essential to give data a voice or a face. Place the data in a context that people can relate to. Only then people are willing to act upon the story data tells.
The Robert Koch Institute launched an app in April 2020, which makes it possible to donate health data to the institutes’ scientists. All in the light of de covid-19 pandemic.
Since they started collecting they published several analyses. Very worthwile to browse through. For instance, last week they published a graph showing how the average heart-rate of those testing positive in their data sample is higher for up to three months. Fascinating.
Yesterday I published a data story on this blog. That was a first of its kind for me. Of course I’ve used graphs before in posts, but that was always reusing other people’s work. This time I did the data work myself. Here is an unstructured list of the things that I learned while doing it.
- You start with downloading one dataset, but you’ll always need more data. My starting point was to find data on total houses in the country. The institute CBS has plenty of data available on their Statline website. I quickly found a data set with exactly what I needed: ‘Voorraad woningen; standen en mutaties vanaf 1921’. But of course, when you’re trying to find an answer to the question why housing is so expensive, you’ll need to compare it to population size. Therefore you need to download other data sets as well. For instance population growth;
- Statline doesn’t always give you all the data available. In my exploration I first downloaded a dataset with numbers on population size starting in 1950. I used this mostly for compiling the graphs, only to find out later that there is another data set available that provides population data starting in 1900. My lesson here is to always dig for more when it comes to using CBS’s data;
- Exploring data becomes messy rather quickly. I downloaded several data sets and used PowerBI to create a dimension table for ‘year’ and added this column to all tables, so that I could use all data across the tables. This phase is needed to discover what’s happening, but it gets more difficult to keep track of which columns you used from which table with each data set you add;
- PowerBI is a very handy tool for exploring and combining data sets;
- After the exploration phase, when I discovered the story the data was telling me, I created a new data set only containing the data that I needed. This way I couldn’t pick the wrong column when making the visuals;
- To create relationships between the tables I used a ‘Year’ dimension table but only used it as a whole number column. I should have created a proper date dimension table to make it even easier to create relationships between the tables (as my teacher already told me to do with every new data model);
- PowerBI Desktop is not the best tool for creating output outside the Microsoft PowerBI sphere. PowerBI is mainly meant for building ‘live’ dashboards used inside companies via PowerBI service, the online platform accompanying PowerBI. You can publish a report to service so that others inside your company can look at it. However, I want to publish the visuals on my blog. The only thing I can use from PowerBI Desktop is a PDF export. Luckily I know how to use Photoshop and was able to transform each PDF page in a PNG rather quickly, but that means extra steps between producing and publishing. Rather annoying when you have many graphs;
- It’s easier to create new columns using a simple calculation in a spreadsheet than to use PowerBI’s DAX formulas to get the same result. In PowerBI I only succeeded doing calculations on columns within the same table, not across tables;
- You need reflection time on what you’re doing with the data. I started exploring the data more than two weeks ago and only after I showed someone my unpublished post I discovered a flaw in my thinking. In one of my graphs I plotted three lines, two of which were a cumulation of population and houses and the third line was a yearly count of migrant surplus. I was comparing apples and pears to make a point. I corrected this and created a new graph comparing births, deaths and migrants, all accumulative since 1950.
- I want to learn how I can create interactive SVG-plots on my website so readers can see the actual data behind the graphs.
One of the recurring themes of the past few months, while we’re waiting for a new cabinet to form (since March 2021, the longest formation in Dutch history), is the rising housing prices. Many people who want to move house simply can’t find a place to live unless they’re willing to pay more than they can afford. Too few houses are for sale compared to the number of people looking for a new home, resulting in people bidding well beyond asking price. Rents are going through the roof, resulting in ridiculous prices in the bigger cities for a ‘house’ that includes a kitchen, a bedroom, a bathroom and living area all on less than 20m2. It’s like living in an Ikea cubicle and you pay half your starter salary for it.
Many reasons for rising prices are given. Professional and amateur real state investors are blamed. They take houses off the market to redo them, break them up in smaller units (rooms even) and then rent them for ridiculous prices. And then there are more and more migrants coming into our country who occupy our homes. Or it’s the ridiculous low interest rate for mortgages, so people can lend more and thus pay more for a house. All these reasons certainly contribute to rising prices. But I also know that no sane 25 year old envisions themselves paying half their salary for a room no bigger than they had in their parents’ home. So why is it that those types of tiny apartments still get tenants? That can only happen when there is a huge shortage on housing. But why are there too few houses built?
I wanted to understand the supply of houses better and turned to the Dutch institute CBS for data. I will write a more polished version of what I found on my other (Dutch) website, so I used Dutch in the graphics. I hope you can forgive me for that.
Here’s a summary.
I first wanted to know how many houses are built every year. There is a dataset available with the total number of houses built and demolished each year, starting in 1920. It also has a total of houses available at the start of a year. I plotted this against the total population.
When you look at the yellow line, you clearly see a decline during the Second World War. Many houses got destroyed. The 1950’s are known for ‘Woningnood’. People had to take in others when owning a large home and many cheap houses were built to accommodate as many as people as quickly as possible. In this graph you can clearly see that the number of households in the fifties is much higher than the number of houses available.
That can only mean several households living together under one roof. As you can see the two lines are much closer together in 2020 than in 1950.
Then I got thinking. Who actually needs a house? Adults. Not kids. So I plotted the same graph, but then using the numbers for the adult (20 and older) population since 1950.
I saw something interesting happening. Starting in 1966 the number of adults rises more quickly than the number of houses. Why is that? Twenty years earlier WWII ended, resulting in the well-known baby boom. Mid-sixties the first of that group became adults. You can also see that the trend line for growth in the number of adults is slightly steeper than that for growth of house supply. I zoomed in on this further. I calculated year-on-year growth of the population and housing supply.
Based on this graph it seems that growth of newly built houses keeps up with population growth. But adults need homes. Therefore I included the adult population in the graph as well.
From this graph you could conclude that enough houses were being built. But you have to remember that the market was already lacking enough homes in 1950, the start date of this graph. Then there was a baby boom and although more houses were being built, it didn’t really make up for the existing shortage. Also notice that since 2007 the number of adults is growing again. The third generation baby boomers (the grand children of the baby boomers) are entering adulthood.
Then there is a totally different trend to add pressure on the housing market. Look at the average number of people living together in a household.
The number of singletons living in a house rose quickly since 1980
You can clearly see the added pressure when you look at the year-on-year-growth of single households.
Year-on-year growth of single people looking for a home far exceeds the growth of extra homes on the market.
A shortage of houses to begin with, a baby boom generation, more single households, a new generation of adults looking for a home to start a family in. That seems to be the cocktail that drives prices up right now.
I also wanted to know how big the shortage could be based on the available data. I therefore calculated the difference each year between the number of new homes available and the number of extra people each year.
As you can see most years less houses were built than new people were added to the total population. A rough calculation of the built-up shortage since 1950 using the most recent number of people forming a household (2,14) rounds up to about 776.000 houses.
The actual shortage will be bigger as there already was a shortage before 1950. Recent numbers shared in reports talk of more than 900.000 homes that need to be built in the coming years to make sure homes become affordable and accessible again. My crude calculation comes close to that number and only takes into account the years starting in 1950 and doesn’t project future population growth nor an even further decline of number of people in one household.
I also discovered something interesting. In 2020 almost exactly the same number of people died as were born. There was a steep increase in the number of deaths in 2020 causing this parity earlier than expected. The result of a pandemic.
The consequence is that for the first time in history the total population growth in 2020 can be credited to a migration surplus.
But migration surplus is not as big as some politicians want us to believe.
Clearly too few homes were built over a long period to keep up with population growth and declining household size in The Netherlands. When there is more demand for a product than supply, prices will go up. Therefore, investing money in the housing market makes a lot of sense, especially in a situation where having large sums of money (more than €100.000,-) costs money when left on a bank account. And every new inhabitant, either by being born or by moving from another country, adds pressure to the market.
Building, building, building is the only solution. For every ten houses one extra needs to be built, at least. But that’s easier said than done in the complex world of permits, land owners, borders between municipalities and provinces, and a huge shortage of technically skilled personnel to build us those homes (whom, ironically, we already need to ‘import’ from Poland and further to the East). And then I’m not even thinking about future implications of rising sea levels. The majority of inhabitants live below sea level already…
Isn’t there any hope for the under thirties currently longing for a proper place to live and start a family in? There is. Those born just after the war, the baby boom generation are all older than 70. As much as we want our (grand)parents to live forever, they won’t. So be prepared to compromise on where you are going to live for the next decade at least, keep the pressure on politics to reduce carbon emissions AND build more houses (in a sustainable manner of course), be welcoming to migrants who can build houses and take care of your (grand)parents while you are working your ass off to pay your current rent/mortgage. And don’t forget to make babies along the way. They’re a lot of work, but also adorable and great teachers of living in the moment. By the time they become smelly teenagers you’ll be able to afford that big home with a separate floor for them.