Student’s Map Becomes a Pandemic Resource

Nearly one of every five Philadelphia residents are unable to always afford sufficient food, according to Hunger Free America. With the COVID-19 pandemic, this problem could become a crisis. One graduate student found herself with a research project that took on a whole new importance. With the help of BatchGeo, she mapped free food resources in her neighborhood of South Philly.

View Free Food in South Philly in a full screen map

Laura Rathsmill is a graduate student at Widener University studying social work. Professor Marina Barnett assigned a project about food insecurity, so Rathsmill decided to research her neighborhood. “Then COVID-19 happened, so there became a special focus on food insecurity,” she told the South Philly Review.

Laura Rathsmill

Rathsmill intensified her research to create a comprehensive guide to free food in South Philly, covering its four zip codes. She organized the data in a spreadsheet with columns for special populations served, hours, and types of resources. Importantly, each location also includes an address.

Excel documents with location data can easily be converted into a map when you copy and paste them into BatchGeo. That’s exactly what Rathsmill did, making her map of food resources browsable by anyone in need.

There have been huge lines at locations that serve everyone. Rathsmill’s map can be used to only display sites serving children, for example. The color of map markers note the populations served. Map viewers can also click specific marker types to display only those that are selected.

Visualizing resources like this on a map helps people understand what options are closest. It might also aid social workers to determine areas currently being overlooked, based on where there are fewer locations.

You can bring important data to your community with a free BatchGeo map.

Bear Attack Statistics of North America

As humans take up more and more space in the world, they’re bound to come into contact with wildlife. Many in the animal kingdom are fierce predators—especially bears. Statistics suggest there have been over 180 fatal bear attacks in North America since 1784. While the majority of these fatal attacks have been carried out by wild bears, some are the result of bears held in captivity.

If humans could try their best to avoid certain types of bears (like the most dangerous type) and the locations bears frequent during their active months, perhaps fatal bear attacks in North America could decrease. Let’s make sure you’re aware of the #1 most dangerous bear, the fatal bear attack hotspots of the continent, and the months you’re most likely to come face-to-face with the deadly predator.

View Fatal bear attacks in North America in a full screen map

We mapped the bear attack statistics from Wikipedia’s List of fatal bear attacks in North America. The oldest person to be attacked and killed by a bear was 93. The youngest? Five months. However, people tend to be 37.5 years, on average, when they’re fatally attacked by one of the following types of bears.

The #1 Bear to Watch out For

Three types of bears are commonly involved in fatal North American attacks: polar bears, black bears, and brown bears. These bears have been responsible for over 180 fatalities over the years. They’ve killed cabin-goers, campers, and joggers, as well as miners, researchers, and children, among others. But which of these bears has killed the most people?

Fatal Polar Bear Attacks

Photo by Bao Menglong on Unsplash

There have been 11 fatal polar bear attacks in North America. Seven of these attacks were done by wild polar bears, all of which took place in Canada or Alaska.

No one in the continental United States has ever been fatally attacked by a wild polar bear. However, captive polar bears are responsible for four fatal North American attacks, which all occurred in Eastern U.S. zoos. Just years apart, the Toledo Zoo (1972), Baltimore Zoo (1976), Central Park Zoo (1982), and Prospect Park Zoo (1987) were the sites of captive polar bear attacks.

While 11 total lives lost to polar bears isn’t something to sniff at, other types of bears are certainly more dangerous. Black bears, for example, have fatally attacked 82 people in North America.

Fatal Black Bear Attacks

Of the 82 fatal black bear attacks in North America, 66 or just over 80% have been from wild bears. If you think the high number of attacks proves dangerous, note that those 66 fatal wild black bear attacks occurred all over North America. Unlike with polar bears, nearly everyone living in the U.S., Canada, and Mexico is susceptible to a wild black bear attack.

On the other hand, like polar bears, captive fatal black bear attacks have only occurred in the U.S. (and once in Ontario, Canada). There have been 16 of these fatal attacks, most taking place in the Northeast like when George Langley and James Virtue were attacked by the black bear they kept in a cage at their gas station in Ellsworth, Maine. Yet even with 82 total fatal attacks, black bears are still not the deadliest bear in North America.

Fatal Brown Bear Attacks

The #1 deadliest bear to watch out for is the brown bear. Brown bears are responsible for 90 fatal bear attacks in North America, 82 of which were done by wild bears. And every single one took place on the West side of the continent, where most brown bears live.

One recent victim of a brown bear attack was Kevin Kammer, who was killed in Montana’s Gallatin National Forest in 2010. Kammer was in his tent at Soda Butte Campground when a mother bear attacked and dragged him 25 feet away. Two other campers in separate campsites were also attacked: a teenager was bitten in the leg, and a woman was bitten in the arm and leg.

However, the Eastern side of the continent has seen a brown bear attack or eight, though they were all done by captive bears. Thomas Earl, a 56-year old zookeeper at the Cleveland Brookside Zoo in Ohio, was mauled by a brown bear when feeding it in its pen. So, while the East needn’t keep their eyes peeled for a wild brown bear attack, Easterners in the U.S. should beware of zoos.

Bear Attack Hotspots

Now that you know the types of bears to look out for, let’s understand the North American locations where fatal bear attacks typically occur. We can do so by turning on the heat map function of BatchGeo, which helps to identify the densest areas of fatal bear attacks (or any other marker. Read more about the heat map function for your Excel spreadsheet here).

Photo of Glacier National Park by Shane Stagner on Unsplash

The winner—or loser if you frequent these locations? According to bear attack statistics, it’s Montana’s Glacier National Park which is home to ten fatal bear attacks, all of which were brown bears. However, since all ten fatal bear attacks occurred between 1967 and 1998, we’ll also note a more recent fatal bear attack hotspot: the state of Wyoming.

Wyoming and specifically, Yellowstone National Park is another breeding ground for fatal bear attacks. Yellowstone has been the site of eight, including recent attacks in 2015 and 2011. Like Montana’s Glacier National Park, all of Yellowstone’s bear fatalities were caused by brown bears. Additionally, the NW corner of Wyoming is one big bear attack hotspot. Along with Yellowstone, that area contains both Bridge-Teton National Forest (where two fatal bear attacks took place) and Shoshone National Forest (home of one attack).

There are several additional bear attack hotspots in Alaska and also in the Algonquin Provincial Park in Ontario, Canada. Ultimately, fatal bear attack locations in North America can be summarized as follows for each country:

  • Mexico (1 attack)
  • Canada (70)
  • U.S. (112)

Broken down even further, it amounts to:

  • Mexico (1 wild, 0 captive)
  • Canada (69 wild, 1 captive)
  • U.S. (85 wild, 27 captive)

Seeing the U.S.’s double-digit captive fatal bear attacks side by side with those from other countries (zero or one) is certainly hard to bear.

Deadliest Months for Bear Attacks

We know that if we’re walking the Wyoming countryside we’ll probably spot a brown bear and be toast, but is there ever a time of year where that’s less likely? Luckily for us, bears hibernate. During those months, bears don’t attack at all… or at least less often. According to the table below, we’d feel safe hiking Yellowstone during the winter months (as long as we had mittens):

Month # of Attacks
January 3
February 1
March 1
April 4
May 17
June 22
July 28
August 32
September 28
October 28
November 12
December 4

There have only been a handful of fatal bear attacks in the winter months since 1784. We have hibernation to thank for this one. Yet if bears are supposed to be sleeping, why are there any deaths at all? Truth be told, if someone woke us up during our long winter nap, we’d also be pretty upset. But we’re even more upset that hiking bear hotspots is out of the question during the summer months.

The summer months are chock full of fatal bear attacks, though it does make sense that most attacks occur when both bears and humans are spending an increased amount of time outdoors. It’s also logical that attacks slow down in November as it nears the time for hibernation.

We learned brown bears cause the most fatalities, particularly those near Glacier National Park or Yellowstone that are out and about in August. And we saw those trends clearly when we looked at a map.

Plotting your location data on a map to your skills and improve your insight. Who knows? Maybe your newfound knowledge into bear attack statistics can help prevent future bear attacks or even worse, shark attacks. Either way, make a map of your data today. Get started for free with BatchGeo.

Simplify Complicated Data in Excel Spreadsheets

Excel spreadsheets are supposed to make your work easier. However, sometimes you find yourself saddled with a spreadsheet full of data in the wrong columns. Or maybe your spreadsheet contains unnecessary information mixed in with the important stuff. This often happens when copying and pasting or exporting large amounts of data from the web into Excel. You need to simplify your data—just not manually. Seeing as Excel offers hundreds of different features and formulas, you’re faced with another dilemma: how do you start simplifying?

In the spirit of simplification, we’ll demonstrate just three essential Excel skills:

  1. Separate data into appropriate columns
  2. Remove unnecessary data
  3. Join data together

These three Excel skills make simplifying complicated data easy. We find ourselves using them frequently as we prepare location data to make custom maps. Take a look at how they helped us simplify one of our Excel spreadsheets that we eventually turned into a map.

The spreadsheet above contains rainfall totals for 282 U.S. cities which we pulled from the National Centers for Environmental Information. It looks pretty straightforward, right? However, our spreadsheet didn’t start off looking like this. Had it not been for the three Excel skills described—which also work in Google Sheets—it would have taken us hours to manually simplify the data. Below is what we actually started out with.

As you can see, the precipitation data for each city is housed in one cell, in one column, and is full of links that we don’t need. Not very useful, huh? Manually separating each of the 282 rows of data into individual columns and deleting the links one by one wasn’t realistic, so what did we do?

Separate Data in Excel by Splitting “Text to Columns”

Excel’s “Text to Columns” feature can detect separations in data (think spaces or commas between data bits) housed in the same cell and will move each individual data piece into its own column. This feature can save you a lot of time in simplifying complicated data with just a few clicks of your mouse. Google Sheets also offers a similar feature.

Not only does “Text to Columns” simplify data in instances like above, where all of the data from the web was exported into the same cell, but it also lends a helping hand in common situations where you almost always need to separate data. For example, when you have a city and state or latitude and longitude coordinates in the same cell.

Here’s how to use “Text to Columns” in Excel:

  1. Select all of the cells you’d like to separate in one column (in our case, we selected cells 1-282 in column A)
  2. Navigate to the “Data” menu
  3. Select “Text to Columns…”

A Text Wizard will appear. Select the type of data you have: either Delimited (characters such as commas or tabs separate each field) or Fixed width (fields are aligned in columns with spaces between each field). Click Next. Depending on your data type, you’ll either set the delimiters your data contains (tabs, semicolons, commas, spaces, or you can customize any other delimiter) or you’ll set the column breaks. Select Next and identify the columns and set the data format. Click Finish.

You’ll see from our example data above that while this feature may not immediately yield the perfect spreadsheet, it certainly gets you closer to the simplified spreadsheet you want without hours of manually moving your data into different columns.

Combine Data in Excel with CONCATENATE

The opposite of separating data with text to columns, Excel’s CONCATENATE function can combine data from separate cells into one cell.

Let’s use CONCATENATE to combine each month’s precipitation data into the same cell. This might be useful if we wanted to see all the data in one place or to provide an overview. We’ll leave the city, state, and annual precipitation data in individual columns. To do so:

  1. Insert a new column to the left of your data or scroll to the right to access an existing but empty column
  2. Double click the first cell that isn’t a header
  3. Type the following: =CONCATENATE(
  4. Add the first cell number you wish to combine (such as B2), followed by a comma
  5. Optional: to add a space or another separator between the data, type ", ", between each cell number
  6. Continue adding in the cell numbers you wish to combine

Alternatively, copy and paste our formula: =CONCATENATE(E2, ", ", F2, ", ", G2, ", ", H2, ", ", I2, ", ", J2, ", ", K2, , ", ", L2, ", ", M2, ", ", N2, ", ", O2, ", ", P2), making sure to replace our cell numbers with your own. Our formula includes commas and a space between each data point. Once you have just one cell CONCATENATED, simply drag the function down to the rest of your cells to combine all of your data.

You can also use CONCATENATE in Google Sheets and we cover more use cases for CONCATENATE here. But what other Excel skills could we need?

Remove Unwanted Data with Find and Replace Wildcards

Thanks to Excel’s “Text to Columns” feature, our spreadsheet is in a much more manageable state. However, moving data into separate columns won’t remove unwanted data, unless it was already formatted with natural separations. In that case, “Text to Columns” would move the data to a separate column which we could easily delete. This was the case with our precipitation data’s first column: NORMALS. We went ahead and deleted the whole column.

But we also wanted to get rid of the links within our data that weren’t moved to separate columns. If the links were all the same web address, we could have used Find and Replace to copy and paste the repetitive link into the Find what search bar, and replaced it with nothing, thus deleting it. But as you can see in our data, and as may be the case in yours, the links are different. Since we don’t want to manually delete each link, we need to get a little creative with Excel’s Find and Replace feature.

We’ll be using the feature with Excel’s Wildcards, which are similar to Regular Expressions (RegEx) in Google Sheets. Instead of finding and replacing only data that is explicitly written out, Wildcards allow you to remove different data based on criteria you outline. Let’s see when and how this works:

You have all sorts of different, unwanted links mixed in with data you want to keep; they all come after the data you wish to keep, and they all begin with “(”. Using find and replace with wildcards:

  1. Navigate to the Edit menu
  2. Select Replace…
  3. In the Find what search bar, type the following wildcard ,* replacing the comma with the first character of the data you wish to remove —in our case, (
  4. Select Replace All.

You can follow the same steps listed above if your unwanted data appears before the data you need to keep. Just use the following wildcard instead: *,

Replace the comma with the last character of the data you’d like to remove.

Take a look at our spreadsheet now. You’ll see that all of the unwanted data (in our case, links) that had appeared after the data we wished to keep have been removed. It looks pretty nice, but now what can we do with our data?

Make a Map with Your Simplified Data

Unsurprisingly, we make a lot of maps at BatchGeo. There are many location data sources, but they don’t all have the data in the perfect format. These Excel skills—separating your data with “Text to Columns”, combining data with CONCATENATE, and removing unwanted data with find and replace—help us simplify the otherwise complicated data. Then we make it even easier for others to understand by making a map!

Maps can highlight insights you may have otherwise missed if your data was stuck in a spreadsheet. And with features like map grouping and a heat map function for your Excel spreadsheet, BatchGeo provides even more insights than your average mapping software. Let’s see how our precipitation data looks when mapped:

View Rainfall Totals by City in a full screen map

By mapping our Excel spreadsheet data, we learned that the island of Pohnpei received the most precipitation over the past 30 years and June is the rainiest month of the year. There are even more insights in our post dedicated to the subject: Average Precipitation of 282 Cities.

You can create a map now from your own Excel data. Or, if you’re looking for even more Excel spreadsheet knowledge, here are some advanced Excel skills and formulas to impress your boss.