Category: mapping

Google famously wants to organize all the world’s information—and there’s a lot out there. You could spend many hours exploring just Wikipedia, which is a treasure trove of facts, figures, and miscellany. It’s no surprise when you come across a data table that piques your interest. In fact, there is a lot of open data trapped in HTML tables.

Often, you’ll want to take your research a step further and save the table to an ideal place for storing, manipulating, and analyzing data: a spreadsheet. But copy and pasting each row or even entire tables can be time-consuming, especially if there are multiple tables on the same topic you want to capture.

Instead, you can make better use of your time when you opt for tools that all but automate the task of grabbing data from the web and transferring it to Excel or Google Sheets.

In this post, we’ll highlight a few ways to capture data from the web. You can import external web data right in Excel, or use a browser extension to save time when grabbing web data. So let’s get started.

Get External Data to Excel: Web Import

Excel is the favorite spreadsheet tool of many for data visualization and analysis. But what if there’s data you want to manipulate that’s not yet in your spreadsheet? Whether it’s a table from an external source like the web or any other external source, the spreadsheet tool makes it easy to automatically import data into Excel.

While this method doesn’t work, when the option is available, it’s one of the easiest ways of importing into Excel.

1. Navigate to the Data tab in Excel and click Get Data
2. Select the data source you wish to import (From Web for this tutorial)
3. Paste in the URL of your desired webpage and press the arrow near the specific table
   you want to import
4. Click Import and identify a new Excel workbook or existing one

Other than pulling from the web, you can do the same in Excel From Access, From Text, From Other Sources, From SQL Server, or From XML Data Import—depending on your device. But for those without this Excel option, you can still copy a table to Excel or another spreadsheet, too.

Browser Extensions Make It Easy to Copy a Table from Website to Excel

No one wants to manually copy and paste rows and columns of data to a spreadsheet. Even if you can do so with an entire table, there might be several related tables you also want.

To copy one or more web tables, you’ll want a web extension like Table Capture or plug in to help you export HTML table to Excel. There’s often a free version of the most helpful browser extensions.

Free:

• Copy table data to the clipboard
• Export table to Google Sheets
• Edit table data before exporting

We find the above free options suitable for our data collecting needs, most commonly copying the table data to the clipboard and pasting it into our preferred spreadsheet tool: Google Sheets vs Excel: 3 Reasons Google Is the Supreme Spreadsheet Tool.

But the availability of the Pro version is useful for those with the following use cases:

• Download table as an Excel (.xlsx) file
• Download table as a CSV file
• Screenshot table (saves as a .png image)
• Export table to Office 365
• Copy to clipboard as Markdown
• Publish and share this table

In addition to the varying gathering methods, Table Capture Chrome enables you to Select all of the tables on a given page or Clear selection. Preview helps assure you’ve chosen the right table. Here are step-by-step directions on how to use it:

1. Add the Table Capture Chrome extension
2. Navigate the webpage you wish to pull a data table from
3. Click the extension in the upper right-hand corner of your browser
4. Identify your desired table
5. Click the icon that represents the action you want to take
6. Paste to your sheet if necessary

And there you have it! Once your data is in your spreadsheet, you’ll want to adequately prepare it for a map by removing links and other data clean-up tasks.

Gain Access to Data Blocked from Capture

For the occasional website that prevents you from easily grabbing their data with a browser extension that copies tables, there are, of course, workarounds.

But before resorting to an add-on like Enable Copy or Absolute Enable Right Click & Copy, be sure to check the site’s terms and conditions. Many have their reasons for not making their data accessible.

If you ultimately decide to bypass them, it’s always better to do so when you’re working on a project for your personal use rather than marketing or other commercial use.

Map Your Newly Gathered Data

No matter the source, we’ve shown how easy it is to grab any desired information with the help of different tools. Now the only thing left to do is map your data, going from this:

To an easier to visualize map:

View Mapped List of Captive Orcas in a full screen map

Of course, there are several ways to do so (we listed popular methods in our Introduction to Map Making on the Web. But perhaps the easiest way to map your data is with a web-based geocoder like ours. So let’s take a look at the next steps.

1. Open your spreadsheet
2. Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) your data
3. Open your web browser and navigate to batchgeo.com
4. Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
5. Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
6. Select the proper location column from each drop-down
7. Click “Make Map” and watch as the geocoder performs its process

Get started mapping your data today with BatchGeo.

There’s not much that’s more essential than the air we breathe. But our constant inhaling can have negative health effects if we don’t live in one of the cleanest cities in America. While air pollution exists almost everywhere in this post-Industrial Revolution world, it’s often most widespread in well-populated metropolitan areas.

Individual pollutants, such as ozone levels (smog) and particle pollution—or soot—reach the highest levels in the air of the most polluted cities in the US, as the map below shows. In addition to the two most common pollutants, the map contains other types of air pollution like carbon monoxide and lead, along with nitrogen and sulfur dioxide.

View Outdoor Air Quality in the U.S. in a full screen map

Use the map to review the cities with the highest and lowest pollutants, including the 4th highest daily max 8-hour average of ozone levels in the year or the mean PM2.5 weighted by calendar quarter for the year. Or, read on to see what we’ve discovered in the EPA’s data.

Smoggiest Cities in California & More

While there are many air pollutants (carbon monoxide, lead, nitrogen oxides, and sulfur oxides), two types are more common, especially in cities. One of the most widespread is ozone pollution, otherwise known as smog.

Breathing in smog irritates and inflames the lungs. Unfortunately, more smog or ozone can be found in the air of the following metropolitan areas than anywhere else in the U.S.

• Riverside-San Bernardino-Ontario, CA – 0.125 ppm
• Los Angeles-Long Beach-Anaheim, CA – 0.124
• Bakersfield, CA – 0.101
• Fresno, CA – 0.09
• Phoenix-Mesa-Scottsdale, AZ – 0.087
• Denver-Aurora-Lakewood, CO – 0.087
• Sacramento–Roseville–Arden-Arcade, CA – 0.086
• Madera, CA – 0.085
• Oxnard-Thousand Oaks-Ventura, CA – 0.085
• Hanford-Corcoran, CA – 0.084

The EPA maintains standards for ozone pollutants and measures a couple of different ways. The results are described in parts per million (ppm). To avoid anomalies, the EPA looks at the 4th highest daily max 8-hour average in the year and sets the standard at 0.07 ppm. All of the top 10 are well beyond that mark and a whopping 70 U.S. cities are above that EPA standard.

Riverside-San Bernardino-Ontario, California area is the dirtiest city in America, at least where smog is concerned. But this isn’t the only Golden State location with abysmally high ozone levels. Home to eight of the top locations, including the four highest, California is one of the most polluted states in the US—and it’s also in the country’s dirtiest region.

Continuing the trend in the western U.S., the Denver-Aurora-Lakewood area ranks high in smog. As does the more southern of the same region (the Phoenix-Mesa-Scottsdale area). Breathing in either smog or soot can increase the risk of lower newborn birth weight, so let’s move on to the second most common air pollutant.

US Cities with the Most Soot in the Air

When it comes to particle pollution (PM2.5, also known as soot), the EPA recommends no more than a 12 ug/m3 mean weighted by calendar quarter for the year. However, 23 cities across the U.S. surpass that—and ten cities’ particles are at far greater levels than the EPA advises, as you’ll see on the table below.

City	State	PM2.5 Weighted Annual Mean (ug/m3)
Eugene	OR	23.2
Bakersfield	CA	22.5
San Diego-Carlsbad	CA	20.5
Fresno	CA	20.3
Hanford-Corcoran	CA	19.9
Madera	CA	19.1
Phoenix-Mesa-Scottsdale	AZ	17.2
Sacramento–Roseville–Arden-Arcade	CA	16.1
Chico	CA	16
Los Angeles-Long Beach-Anaheim	CA	15.9

Once again, Californian cities dominate the charts. Eight of the sootiest are located in California, including many of the top smoggiest cities we mentioned earlier (Bakersfield, Fresno, Hanford-Corcoran, Madera, Sacramento–Roseville–Arden-Arcade, and Los Angeles-Long Beach-Anaheim), along with new additions like San Diego-Carlsbad and Chico.

But none of these cities are #1. That title belongs to Eugene, Oregon, which is nearly double the EPA’s standards.

Find Cities with the Best Air Quality

On a higher note, there are also cities with air quality levels far better than the EPA’s recommendations on the map. Take ozone, of which Urban Honolulu, Hawaii and Santa Cruz-Watsonville, California have the best levels, .045 and .048 ppm, respectively.

The ozone levels of Keene, New Hampshire, Bellingham, Washington, and Claremont-Lebanon, New Hampshire-Vermont are similarly high (.050 ppm). Meanwhile, Minot, North Dakota’s .051 ppm is nearly as good, along with eight cities of .052 ppm, which you can find on the map.

The same goes for particle pollution, which the EPA advises no more than 12 ug/m3:

• Manchester-Nashua, NH – 3.00 ug/m3
• Dickinson, ND – 3.60
• Rock Springs, WY – 3.60
• Wilmington, NC – 3.70
• Urban Honolulu, HI – 3.90
• Laconia, NH – 4.10
• Claremont-Lebanon, NH-VT – 4.30
• Corning, NY – 4.40
• Brainerd, MN – 4.40
• Cambridge, MD – 4.40
• Brookings, SD – 4.40

You’ll notice Honolulu, Hawaii and Claremont-Lebanon, New Hampshire have some of the better levels of both smog and soot.

---

That’s it for the cities with the best and worst air pollution in the U.S. Switching gears from air to recycling, be sure to check out the Most and Least Environmentally Friendly Countries.

Thousands of people travel to airports every day to catch their flights. It’s an incredibly convenient form of transportation with very little danger. One study from MIT found flying is 19 times safer than driving. Yet even with modern-day planes, disasters like the ones in this post still occur.

We will look at the locations and data from more than 550 of the worst airplane crashes on record. You’ll learn which were the deadliest and what causes were discovered. You needn’t locate each plane’s black box for a flight recording. You’ll find the answers on the map below.

View 550+ Major Plane Crashes in a full screen map

The data comes from the list of plane crashes with 50+ fatalities on Wikipedia. You can sort the map by the total number of deaths, or get more specific with your inquiry about the deaths of crew members, passengers, or people on the ground. Additional sortable groups include type and date, which we’ll also cover below.

10 Deadliest Plane Crashes

While the map displays aircraft accidents resulting in 50 or more fatalities, many go beyond that (529 of the 553 total disasters fall into the “or more” category). Some even resulted in hundreds or thousands of deaths. For instance, the Japan Airlines Flight 123 is often mentioned when discussing the deadliest aviation accident (520 people died). But three incidents fared worse, as you’ll see below.

1. American Airlines Flight 11
2. United Airlines Flight 175
3. Pan Am Flight 1736 and KLM Flight 4805
4. Japan Airlines Flight 123
5. Saudi Arabian Flight 763 and Kazakhstan Airlines Flight 1907
6. Turkish Airlines Flight 981
7. Air India Flight 182
8. Saudia Flight 163
9. Malaysia Airlines Flight 17
10. Iran Air Flight 655

Two of the deadliest plane collisions in world history took place on September 11th, 2001. The hijacking of American Airlines Flight 11 resulted in 1,700 deaths, most of which were people inside the North Tower of the World Trade Center. All 81 of the passengers and 11 members of the crew aboard the plane also passed away. The United Airlines Flight 175 hijacking on the same day saw 1,000 people lose their lives. The plane hit the South Tower of the World Trade Center, killing the 56 passengers, nine crew, and countless folks inside the building. These two 9/11 incidents are the only ones with death tolls in the thousands.

After 9/11, the deadliest air crashes include the two flights of Pan Am 1736 and KLM4805 in 1977 along with Japan Airlines Flight 123 (JAL 123) in 1985. While both accidents had death counts in the 500s, the KLM Pan Am crash (also known as the Tenerife airport disaster) was unique in that it involved two planes in a single collision. The two Boeing 747 planes collided on a runway in one of the Canary Islands, resulting in 583 lives lost. As for Japan123, 520 people died aboard the Boeing 747 when, 44 minutes into the flight, it went down near Mount Osutaka in Ueno.

The rest of the 10 deadliest plane crashes saw deaths ranging from 290-349. Notably, Air India Flight 182 (329 deaths) failed due to a bombing. Both Malaysia Airlines Flight 17 (298) and Iran Air Flight 655 (290) were attacked using ground-based weapons. What were some other reasons these jets fell? Let’s find out.

Types of Aviation Accidents

The reasons behind these plane crashes can be divided into six categories or “Types” on the map. Most were accidents or incidents, though 42 were attacks. Let’s break down the specifics of each.

There have been 442 commercial accidents or incidents that brought planes down. The Pan Am 1736 and KLM4805 flights along with Japan Airlines Flight 123 are the two deadliest examples of this type of crash. There have been 44,757 deaths from this most common accident type. There have been 69 accidents or incidents of the military variety, which have combined for 5,989 fatalities. The most deaths in one instance (275) occurred with the Iranian Air Force (15-2280) in 2003.

While less common, attacks such as bombings, ground-based weapon attacks, hijackings, or by other aircrafts can be equally, if not more devastating. Of these attacks, internal bombings, are the most frequent—15 have occurred. The deadliest was the 1985 Air India Flight 182 in which 329 lives were lost. The second-most common aviation attack uses ground-based weapons like ground-to-air missiles or the destruction of the aircraft while on the runway. Thirteen have taken place throughout the years, perhaps most notably were the 298 deaths of Malaysia Airlines Flight 17. To learn more about the 10 hijackings and four attacks by other aircrafts, check out those groups on the map.

More Map Groups

Along with total deaths and the type of incidents that brought them down, you can sort the map by the following:

• Deaths of crew members, passengers, or ground members
• Survivor count
• Incident
• Aircraft info
• Phase of fall
• Airport
• Dates

These are called groups, as when you map a spreadsheet with BatchGeo, you often have more information than just locations. With additional data, we find the best home. You and users of your map will be able to select only the markers that meet certain requirements, filtering out the rest. Groups can be combined to zero in on very specific results, giving you insight into the story behind the map.

What can you learn from grouping your BatchGeo maps? In addition to understanding the 9/11 attacks were the deadliest in history and that most crashes are commercial, we also discovered the time period(s) that saw the most attacks. 1973 had the most frequent plane collisions, 18 to be exact. Perhaps most relevant today is the month: 58 in December, though July is a close second with 57 accidents.

We also learned most of the planes fell while en route (251 crashes). See the statistics of other phases of flights by grouping the map by that additional data. And for fewer sky-bound accidents, check out 569 Shipwrecks in International Waters.