So in the past week Police Brutality has been a major issue in Canada, South Africa and the United States. I thought that some might find it useful to see a data visualization showing Police Brutality by Country.

Police Brutality by Country

The Data Studio visualization below shows three measures;

• Population
• People Killed by Security Forces
• Rate per 10 Million people

In order to view the interactive Google Map you will need to open the report in Google Data Studio. To do this click on the ‘Google Data Studio’ logo in the bottom right corner of the screen.

You will then be able to view and interact with the Google Map. The Bubble size on the map shows the number of people killed by security forces, colour indicates the rate per 10 million people.

If you are unable to view the map it should look like this.

Getting the Data from Wikipedia

So this data is taken from the Wikipedia page List of killings by law enforcement officers by country.

In order to get the data into Google Data Studio, you first need to get it into Google Sheets.

Put the following formula into the top left corner of your spreadsheet; =ImportHtml(“https://en.wikipedia.org/wiki/List_of_killings_by_law_enforcement_officers_by_country”, “table”, 1)

If there are multiple tables on the page, you can simply change the ‘1’ to any other digit.

Below is what you should see pulled into your Google Sheet.

Before we can put it into Data Studio though, we need to pull the data into another sheet so that the formatting will be correct.

The problem is that the way that the table on Wikipedia is formatted means that the column headers aren’t all in the same row as seen below.

In a second new spreadsheet, just link to the relevant cell in the original spreadsheet so that the columns are all in the same row.

There is one more step we need to do however before it’s ready for Data Studio.

In the original table from Wikipedia some cells contain an integer and a ‘+’ sign. This means that it will not display correctly as a number in Data Studio.

In our new spreadsheet, we can simply create a new column and use this formula to pull in the numbers without the ‘+’ sign; =SPLIT(C2,”+”)

So as shown below the number 304+ is pulled into the next column as 304.

That’s enough to keep Data Studio happy so we can then make our visualization.

My Other Blog Posts

If you found this post interesting you find like my other blog posts.