champagne anarchist | armchair activist

How to do fuzzy matching in Python

Statistics Netherlands (CBS) has an interesting dataset containing data at the city, district and neighbourhood levels. However, some names of neighbourhoods have changed, specifically between 2010 and 2011 for Amsterdam. For example, Bijlmer-Centrum D, F en H was renamed Bijlmer-Centrum (D, F, H).

In some of those cases the neighbourhood codes have changed as well, and CBS doesn’t have conversion tables. So this is one of those cases where you need fuzzy string matching.

There’s a good Python library for that job: Fuzzywuzzy. It was developed by SeatGeek, a company that scrapes event data from a variety of websites and needed a way to figure out which titles refer to the same event, even if the names have typos and other inconsistencies.

Fuzzywuzzy will compare two strings and compute a score between 0 and 100 reflecting how similar they are. It can use different methods to calculate that score (e.g. fuzz.ratio(string_1, string_2) or fuzz.partial_ratio(string_1, string_2). Some of those methods are described in this article, which is worth a read.

Alternatively, you can take a string and have Fuzzywuzzy pick the best match(es) from a list of options (e.g., process.extract(string, list_of_strings, limit=3) or process.extractOne(string, list_of_strings)). Here, too, you could specify the method to calculate the score, but you may want to first try the default option (WRatio), which will figure out which method to use. The default option seems to work pretty well.

Here’s the code I used to match the 2010 CBS Amsterdam neighbourhood names to those for 2011:

import pandas as pd
from fuzzywuzzy import process
# Prepare data
colnames = ['name', 'level', 'code']
data_2010 = pd.read_excel('../data/Kerncijfers_wijken_e_131017211256.xlsx', skiprows=4)
data_2010.columns = colnames
data_2010 = data_2010[data_2010.level == 'Buurt']
names_2010 = data_2010['name']
data_2011 = pd.read_excel('../data/Kerncijfers_wijken_e_131017211359.xlsx', skiprows=4)
data_2011.columns = colnames
data_2011 = data_2011[data_2011.level == 'Buurt']
names_2011 = data_2011['name']
# Actual matching
recode = {}
for name in names_10:
    best_match = process.extractOne(name, names_11)
    if best_match[1] < 100:
        print(name, best_match)
    recode[name] = best_match[0]

It prints all matches with a score below 100 so you can inspect them in case there are any incorrect matches (with larger datasets this may not be feasible). With the process option I didn’t get any incorrect matches, but with fuzz.partial_ratio, IJplein en Vogelbuurt was matched with Vondelbuurt instead of Ijplein/Vogelbuurt.

PS In case you’re actually going to work with the local CBS data, you should know that Amsterdam’s neighbourhoods (buurten) were reclassified as districts (wijken) in 2016, when a more detailed set of neighbourhoods was introduced. You can translate 2015 neighbourhood codes to 2016 district codes:

def convert_code(x):
    x = 'WK' + x[2:]
    x = x[:6] + x[-2:]
    return x

Wikinews is a great idea, but is it viable?

I’ve decided to start reposting relevant Dutch-language articles from Wikinews on News from Amsterdam. Wikinews is a sister project of Wikipedia. Its contributors describe themselves as follows:

We are a group of volunteers whose mission is to present reliable, unbiased and relevant news. All our content is released under a free license. By making our content perpetually available for free redistribution and use, we hope to contribute to a global digital commons. Wikinews stories are written from a neutral point of view to ensure fair and unbiased reporting.

It would seem a bit naive to simply claim that your writing is neutral and unbiased. The strength of Wikinews rather lies in providing a place were the merits of a story and its sourcing can be discussed on the basis of arguments. In times of clickbait, hoaxes and fake news, that’s an interesting concept.

Wikinews operates without ads (which matters). Their website may look a bit austere, but since it’s all open source, anyone can resuse the content with a different layout.

For now, Wikinews mainly consists of syntheses of news published by other media, but the site also invites other types of stories such as investigative reporting; (photo) reports and opinion articles. This could also provide room for another goal of Wikinews, which is to cover stories that are underreported in other media.

But is Wikinews viable? The consensus seems to be that it’s not. As Jonathan Dee of the New York Times put it in July 2007: «Wikinews … has sunk into a kind of torpor; lately it generates just 8 to 10 articles a day on a grab bag of topics that happen to capture the interest of its fewer than 26,000 users worldwide …».

That was ten years ago. Since, activity on the English-language version of Wikinews has further declined, as the chart below shows. Meanwhile, there’s a remarkable rise in the number of articles on Dutch-language Wikinews.

Perhaps this is a temporary boost of enthusiasm, that will fade out after a while. Then again, maybe it’ll last and Wikinews will reinvent itself. It’ll be interesting to see how this develops.


Charts on mobile screens - always tricky

The chart shown here is from Dutch national statistics office CBS and it was created with Highcharts (or at least that name appears a few dozen times in the source code). It shows working people with second jobs and it’s from this page.

There’s a problem with the labels on the x-axis: they have been abbreviated and only show the first digit of the years. This could easily have been avoided by showing fewer labels when the chart is displayed on a narrow screen. In fact, that solution is used in the other graphs in the same article. Apparently, Highcharts doesn’t adjust the number of labels automatically, or it doesn’t do so consistently.

It appears that CBS has chosen Highcharts because it’s an easy way to create charts with added functionality. And some of that functionality seems to make sense: I can imagine people using the option of downloading a PNG to use it in a report or share it on social media.

However, it may not always be a good idea to rely on standard solutions. Here’s another example where the labels have been abbreviated. It’s a bit of a challenge to figure out what they mean.

I know it can be a pain to get charts to work well on different types of screens (let alone network graphs). Apparently, you cannot simply rely on Highcharts to get it right. CBS should probably assign someone to edit each chart individually, making sure they are displayed properly on different screen types.

As for the contents of the charts: here’s why it’s not OK that more and more Dutch workers need to take a second job to make ends meet (in Dutch).


The network of Dutch firms

One of the ways in which firms are linked is through board members who also sit on the boards of other firms. Researchers use these board interlocks to determine which firms occupy a central position in the corporate network. This «is widely considered as an indication of a powerful or at least advantageous position», Frank Takes and Eelke Heemskerk explain in an interesting paper on the subject.

Two Dutch newspapers, de Volkskrant and NRC Handelsblad, have published visualisations of the Dutch (corporate) elite and their board memberships. You can use the data from those visualisations to create board interlock networks. Below is an example using data from NRC Handelsblad from 2017:

Darker nodes represent organisations with a more central position in the network, as measured by their betweenness centrality. Below is another example, using data from de Volkskrant from 2013:

The most obvious difference is that the second graph contains far more nodes (organisations) and edges (shared board members) than the 2017 chart. But there’s more. The 2017 dataset contains only nodes that have at least two edges - probably the result of a selection criterion used by NRC Handelsblad because of the type of visualisation they wanted to make. Further, the 2013 dataset consists of multiple components: three sets of organisations only share board members with each other; not with the rest of the network.

Given the differences between the datasets, would it still be meaningful to make comparisons between the two? The table below shows the top 10 of organisations with the highest centrality scores, for 2013 and 2017. The comparison is limited to organisations that are included in both datasets.

2013 2017
DNB Ahold
Concertgebouw Concertgebouw
ABN Amro Schiphol
Aegon NV FrieslandCampina
Concertgebouw Fonds Philips
Philips Rabobank Groep
Heineken NV Vopak

Organisations like employers’ organisation VNO-NCW, the Concertgebouw concert hall and airline KLM seem to occupy a pretty stable position at the centre of the network. VNO-NCW has a huge non-executive board with representatives from a wide range of industries. The Concertgebouw has been described years ago as the living room of the [Dutch] elite.

Aside from these stable elements, there are substantial differences between the two rankings. The rank correlation is only 0.33 and not statistically significant. This may be due to differences in the way the datasets were created; the small size of the overlap between them (only 34 organisations) and other data quality issues.

On the other hand, some changes in the ranking appear to reflect genuine changes in the position firms occupy. Two examples:

  • One of the fastest risers is Ahold. Ahold merged with Belgian retailer Delhaize in 2016. It would seem plausible that this has strengthened their position in the corporate network.
  • ABN Amro disappeared from the top 10. The bank used to have a board with well-connected members like Gerrit Zalm and Joop Wijn (both have gone through the revolving door between government and the corporate world), Peter Wakkie and Marjan Oudeman (one of the most influential Dutch women according to various rankings). In 2015, chairman Wakkie stepped down over a commotion caused by excessive executive board remunerations (the bank was still state-owned after having been bailed out with public money in 2008). Subsequently, Oudeman, Zalm and Wijn also left the bank, for reasons partly related to its upcoming flotation. It appears the current board has a lower profile.

This type of analyses could benefit enormously from having a larger dataset available. This is yet another reason why the Dutch Company Register should be opened up as open data: this will allow for better understanding of the networks of corporate control.

Method and data

Both de Volkskrant (2013, 2014) and NRC Handelsblad (2017) have published visualisations of the Dutch (corporate) elite and their board memberships. Note that these board memberships not only include companies, but also employers’ organisations, cultural institutions and other types of organisations the collectors of the data deemed relevant for analysing corporate elite networks.

Before comparisons can be made, the names of the organisations need to be cleaned up. Beyond correcting typos and dealing with additions like N.V. (plc) and B.V. (ltd), this involves deciding when to consider units as part of the same organisation. Pragmatically, I decided to treat businesses that are part of the same corporate structure as identical. This may not always be the ideal approach; on the other hand, it’s not always possible to determine what unit a name refers to (e.g. ING could refer to the holding or to one of its subsidiaries). I did treat foundations (e.g. charities linked to a company) as separate from the company.

There are different ways to measure the centrality of a node in a network. Taking my cue from Takes and Heemskerk, I used betweenness centrality, which is based on how often a node is on the shortest path between two other nodes. I calculated centrality for the entire network, that is, before taking a subgraph. I included endpoints to prevent many nodes having a score of zero.

I used the Python library networkx to analyse the graphs (here’s the code and here’s the accompanying text file for cleaning up organisation names). I used d3.js to visualise the network graphs - here’s a description of the problems I ran into and how I dealt with them.

Dutch rail passengers are observed by cameras in advertising columns

«What are those mini cameras doing in that advertising column!», a Twitter user asked today. In a response, Dutch Railways (NS) said that advertisers use the cameras to assess whether passengers look at advertising and for how long.

A spokesperson of Exterion, the owner of the advertising columns, told Dutch broadcaster NOS that as many as 35 such cameras are already observing passengers at Amsterdam Central Station. Reportedly, the cameras can determine the gender of people and estimate their age. In the future, facial recognition and emotion detection may be added, but that hasn’t been decided yet.

Exterion assures that images will never be stored, but doesn’t make that claim about (personal) data gleaned from the images. They told another broadcaster, RTLZ, the cameras only register «‘ones and zeros’, for example whether someone is male or female, so no sensitive data from a privacy perspective». Such a statement may not be the best way to convince people you take privacy seriously.

A spokesperson of the Dutch Privacy Authority told NOS that they will investigate the matter, but «in general, cameras in public spaces may not be used for advertising purposes». The party leader of the Green Party in Amsterdam, Rutger Groot Wassink, has called for direct action: «Who will join me tomorrow in taping up these bloody things».

UPDATE 11 September 2017 - According to media reports, Exterion has announced it will temporarily turn off the 35 cameras in advertising columns at Amsterdam Central Station, because of «the commotion» they caused. They ascribe the commotion to insufficient communication, which suggests they see nothing wrong in the cameras as such and may turn them back on in the future. Members of Parliament have asked questions about the cameras (SP, D66).