Mapping the UK's oldest trademarks // Part 1: Preparing data with SQLite and Python
Channel: Creating with Data
Date: 2023-04-19

The first of two parts, this video covers the data-gathering/prep side in creating this map of the UK's oldest trademarks (see here: https://creating-with-data.glitch.me/trademarks/index.html) 

How do you interrogate a 1M+ row CSV with (almost) all the trademarks filed for registration in the UK? 

In this video, we take a look at the data held by the Intellectual Property Office, in particular, trademark filings that date back 1870s. We walk through the dataset and use Python to create an SQLite database of the same data.

As a first data gathering and preparation step to creating a map visualisation, we use SQL to extract the oldest filing for each UK postcode area.

See here for code and notes: https://creatingwithdata.com/mapping-the-uks-oldest-trademarks-part-1-preparing-data-with-sqlite-and-python/

0:00 Introduction 
3:24 Downloading and understanding the trademarks dataset
8:53 Importing dataset into an SQLite db with Python
22:04 Exploring the data with the SQLite browser 
29:53 Adding postal code areas with Python
38:34 Extracting oldest trademark data by postal code area