2c:null 2d:["$","title",null,{"children":"8. Geographical Data, Part 1: Creating the Data | Kalle Westerling | Observable"}] 32:T4fa,## Explaining the Setup In the following examples, I draw on the script that is generating the dataset that currently is the bedrock of the digital component of my dissertation. It runs on a scheduled basis in this GitHub repository to generate easily accessible and open JSON data, which can be used in any data analysis projects. In the script, I first generate a “DataFrame” containing the dataset from Google Sheets, as it has been structured (in [Step 2](${nav.ingestion.url})) and cleaned (in [Step 3](${nav.cleaning.url})). A “DataFrame” is a particular object in Python programming that can be compared to a spreadsheet. It allows for me to access columns and rows in a similar manner, to filter and remove rows and columns based on certain conditions. In the scripts below, I use **`df`** as a shorthand for the DataFrame that contains all of the data from the Google Sheet.33:T606,## Getting a list of all cities in dataset In two lines of code, I generate a list of all the cities in the dataset: ```py cities = [city for city in df["City"] if not city == "—"] cities.extend([city for city in df["Normalized City"] if not city == "—"]) ``` The ```[city for city in ...]``` notation above is generally referred to as a list comprehension in Python programming terms. It is a speedy way to generate a list from another iterable object. In this case, I am generating a list of `cities`, and then extend the list with the normalized cities. In both cases, cities named "—" are ignored. In this next step, I create a set of unique cities from the the list of the cities, and then convert it back into a Python-recognized list object. In the same go, I use a list comprehension again, this time to replace any question marks in the names. ```py cities = list(set([x.replace("?", "") for x in cities])) ``` At this point, `cities` is a beautiful list of all the cities in the dataset, combining both the manually cleaned names of cities (in the `Normalized City` column in the dataset), and the more detailed data in the messy data column for city (see `City` in dataset).34:Tacf,## Geotagging Cities Now that I have a list of unique `cities` from the dataset, I can generate the geographical location of each of the cities. The `geopy` package for Python comes with a built-in ability to use the Nomatim API for finding geographical locations (if you are curious, [here](https://towardsdatascience.com/things-to-do-with-latitude-longitude-data-using-geopy-python-1d356ed1ae30) is a great tutorial on how to use this package in more detail). This step is laborious and requires some advanced knowledge of Python, so feel free to skip ahead here if it is not of particular interest to you.

I created a function that could generate the geo-data for each datapoint. I also wanted to ensure that I did not generate any excess traffic for the API to handle, so I also built a cache system: ```py def get_geodata(city): if geo_cache.exists(): geo_data = json.loads(geo_cache.read_text()) else: geo_data = {} if not city in geo_data: d = geolocator.geocode(city) if not d: raise RuntimeError(f"Error: Could not geocode {city}") geo_data[city] = { "box": d.raw.get("boundingbox"), "lat": d.raw.get("lat"), "lon": d.raw.get("lon"), } geo_cache.write_text(json.dumps(geo_data)) return geo_data[city] ``` This is a rather lengthy function, but the most essential part here is the line that uses the `geolocator`’s (Nomatim’s) `geocode` method on the name of the city. This data is then taken into a quick extraction of its resulting `boundingbox`, latitude (`lat`), and longitude (`lon`) data into a dictionary (`geo_data`) that is then written into the JSON file that acts as a cache. The resulting dictionary is then returned. By having the `get_geodata` function written, I can run it on a dictionary comprehension, which is similarly structured to the `[city for city in ...]` notation above: ```py cities = {city: get_geodata(city) for city in cities if city} ``` An option to this dictionary comprehension method, which I have opted to do in the dataset, is to add the data for each city into three new columns. The easiest way to run the `get_geodata` function on each row is to use the DataFrame’s built-in method `apply`, on which you can run a nameless function (also known as a “lambda” function in Python): ```py df["lat"] = df.apply(lambda row: get_geodata(row["City"])["lat"], axis=1) df["lon"] = df.apply(lambda row: get_geodata(row["City"])["lon"], axis=1) df["box"] = df.apply(lambda row: get_geodata(row["City"])["box"], axis=1) ``` The same was then performed on the Normalized City column, in three differently named columns: `norm-lat`, `norm-lon`, `norm-box`.35:T7d5,svg = { const margin = { top: 20, right: 10, bottom: 20, left: 10 }; const width = 975 - margin.left - margin.right, height = 610 - margin.top - margin.bottom; const projection = d3.geoAlbersUsa().scale(1300).translate([width / 2 + margin.left, height / 2 + + margin.top]), path = d3.geoPath(); const svg = d3 .create('svg') .attr('height', height + margin.top + margin.bottom * 3) // * 3 added for observablehq .attr('width', width + margin.left + margin.right) const g = svg .append('g') .attr("transform", "translate(" + margin.left + "," + margin.top + ")"); const statesBackground = g .append('path') .attr('fill', '#ddd') .attr('d', path(topojson.feature(us, us.objects.nation))); const statesBorders = g .append('path') .attr('fill', 'none') .attr('stroke', '#fff') .attr('stroke-linejoin', 'round') .attr('stroke-linecap', 'round') .attr('d', path(topojson.mesh(us, us.objects.states, (a, b) => a !== b))); const cityElements = g .selectAll('g') .data(mapData) .join('g'); const city = cityElements .append('g') .attr('transform', ({ longitude, latitude }) => { let value = projection([longitude, latitude]); if (value) return `translate(${value.join(",")})` console.error('Could not map', longitude, latitude) }); city .append('circle') .attr('r', 2) .attr('opacity', settings.opacity / 100); const textScale = d3.scaleSqrt() .domain(d3.extent(mapData.map(d=>d.count))) .range([12, 30]); const opacityScale = d3.scaleSqrt() .domain(d3.extent(mapData.map(d=>d.count))) .range([0.1, 1]); city .append('text') .attr('font-family', 'sans-serif') .attr('font-size', d => settings.textSize.length ? textScale(d.count) : 10) .attr('text-anchor', 'middle') .attr('y', -6) .attr('opacity', settings.opacity / 100) .text(({ name }) => name); return svg.node(); }36:T4ce,mapData = cities .filter(d => { const cityOccurrences = data.filter(c=>c.City === d.City || d['Normalized City'] === d.City); const [minYear, maxYear] = d3.extent(cityOccurrences.map(d=>+d.Date.slice(0,4))); const count = cityOccurrences.length; if (minYear < 1900) { return false; } return !( d.City.includes('*') || d.City.includes('Canada') || d.City.includes('Mexico') || d.City.includes('Havana') || count < settings.includeThreshold) }) .map(d => { const name = d.City.includes(',') ? d.City.slice(0,-4) : d.City; const cityOccurrences = data.filter(c=>c.City === d.City || d['Normalized City'] === d.City); const [minYear, maxYear] = d3.extent(cityOccurrences.map(d=>+d.Date.slice(0,4))); const count = cityOccurrences.length; return { name, count, latitude: d.Latitude, longitude: d.Longitude, minYear, maxYear } })37:[{"id":"0d8fa65dce2397df03b75fb4fabbc7d79e2794ef64f018bdd1dd43460bc3795743e69dbf1d7456791cbef424e272d5cd33f49e3e445ce78f9a53ef5e5755e16e","href":"https://static.observableusercontent.com/files/0d8fa65dce2397df03b75fb4fabbc7d79e2794ef64f018bdd1dd43460bc3795743e69dbf1d7456791cbef424e272d5cd33f49e3e445ce78f9a53ef5e5755e16e","name":"states-albers-10m.json","type":"application/json","lastModified":1641383858947.783,"size":82031}] 30:{"id":"d3e3f7a1ed7258ab","slug":"geographical-data-creating-the-data","title":"8. Geographical Data, Part 1: Creating the Data","thumbnail":"0c4dc240b318bf251643f7af4ceb101adf19c7e1e7e2818ae3f293d70a410e7a","access_level":"public","create_time":"$D2022-01-04T12:18:32.747Z","update_time":"$D2022-01-17T18:45:52.763Z","view_time":null,"first_public_time":"$D2022-01-05T11:49:17.626Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"32362545e9f3330d","login":"kallewesterling","name":"Kalle Westerling","avatar_url":"https://avatars-next.observableusercontent.com/f759a45dfbde6f2da561a90bcc8852bd0efd30d6a12ae3031f4d0bedc2464b65","tier":"starter_2024"},"version":614,"publish_version":614,"role":null,"authored":false,"authors":[{"type":"individual","id":"a4098a3e48d12fd5","login":"kallewesterling","name":"Kalle Westerling","avatar_url":"https://avatars-next.observableusercontent.com/f759a45dfbde6f2da561a90bcc8852bd0efd30d6a12ae3031f4d0bedc2464b65","tier":null}],"members":[{"type":"individual","id":"a4098a3e48d12fd5","login":"kallewesterling","name":"Kalle Westerling","avatar_url":"https://avatars-next.observableusercontent.com/f759a45dfbde6f2da561a90bcc8852bd0efd30d6a12ae3031f4d0bedc2464b65","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"f56ba7f1433fe336","slug":"dissertation-data","title":"Dissertation data","owner":{"id":"32362545e9f3330d","login":"kallewesterling","name":"Kalle Westerling"}},"type":"notebook","body":{"titling":"auto","title":"8. Geographical Data, Part 1: Creating the Data","cells":[{"id":0,"value":"# 8. Geographical Data, Part 1: Creating the Data\n\n

In this step, the process becomes a bit more technologically involved, and the discussion below will include some code examples (in the programming language Python). I have tried to temper the language as much as I have been able to in an attempt to make the explanation of this part of the process as legible as possible for non-coders.

\n\nThat said, much of the content below still requires a fairly advanced understanding of coding to fully grasp some of the concepts so feel free to move along if, at some point, the content becomes too complicated.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":118,"value":"## `Geopy`\n\nAlthough I have ended up not using the geographical data extensively in my research project, I wanted to ensure that the data was available in the dataset. Thus, I used the Python library `geopy` to automatically generate some geographical data for the dataset. The documentation for the entire package is available here.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":85,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":33,"value":"$32","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":82,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":70,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":88,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":74,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":125,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":127,"value":"## Result\n\nFinally, then, the dataset contains columns for all of this data, which is represented in this table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":149,"value":"Inputs.table(cities, tableSettings)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":229,"value":"Table that illustrates all the geographically located city names in the dataset, as latitude, longitude, and the bounding box surrounding the geographical location. A star (*) after the city name means that the city’s name was changed in the cleaning process.","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":219,"value":"This table is only for illustration purposes. In the dataset itself the geographical data for each city is matched with each row, which creates a larger datafile but one which is easier to work with, when it comes to the final result.\n\nBelow is a visual (and quite messy) representation of the data above, including only the cleaned-up city names in the dataset, allowing you to filter the cities by the minimum number of occurrences in the dataset.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":412,"value":"viewof settings = Inputs.form({\n \n includeThreshold: Object.assign(Inputs.range([1,d3.max(cities.map(d=>data.filter(c=>c.City === d.City || d['Normalized City'] === d.City).length))/2], {\n step: 1, \n value: 1, \n label: 'Filter by min. # occurrences in dataset',\n transform: Math.log,\n }), {style: '--label-width: 250px;'}),\n \n textSize: Object.assign(Inputs.checkbox(['Yes'], {\n label: 'Set text size by # occurrences',\n value: ['Yes']\n }), {style: '--label-width: 250px;padding-top:0.25em;'}),\n \n opacity: Object.assign(Inputs.range([1,100], {\n label: 'Text opacity',\n step: 1,\n value: 100\n }), {style: '--label-width: 250px;padding-top:0.25em;'}),\n \n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":242,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":612,"value":"smallNav({\n prev: {url: nav.network3.url},\n next: {url: nav.located.url}\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":139,"value":"scriptDisclaimer","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":488,"value":"smallNav({\n prev: {url: nav.network2.url},\n next: {url: nav.located.url}\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":151,"value":"tableSettings = ({\n sort: 'City', \n layout: 'auto', \n disable: true, \n rows: 21, \n multiple: false,\n /*\n format: {\n source: d => {\n const o = selectDataset.find(e=>e.source === d && e.link);\n if (o) {\n return html`${d}`;\n } else {\n return d;\n }\n }\n }\n */\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":146,"value":"import { rawData as data, cities, nav, navigate, scriptDisclaimer, fullWidth, smallNav, tachyons, ga } from \"@kallewesterling/drag-dataset\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":251,"value":"us = FileAttachment(\"states-albers-10m.json\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":267,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":533,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":548,"value":"fullWidth","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":560,"value":"tachyons","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":564,"value":"ga()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W37","stdlib":"1"}} 31:"$30:body"