Author: Adam DuVander

You do millions of things to get ready for college, your wedding, a newborn baby, even natural disasters. When you’re prepared, the end result is usually better. Why not do the same for your data? While there are many map making tools on the web, BatchGeo users appreciate the customized Google Maps built right from your Excel or Google Sheets data.

BatchGeo takes its best guess with the additional fields in your spreadsheets, but this post will share some key preparations to get the most out of your map. You’ll learn how to customize your spreadsheet’s groups, remove any extraneous text from the numerical data in your spreadsheet, and separate your date field into individual columns, among other things. So let’s jump into how best to prepare your spreadsheet for a map, starting with your non-location fields.

Know How Many Groups Will Appear on Your Map

There are plenty of instances where you’ll find a data source with more information than just location. In these cases, BatchGeo will take the additional data columns in your spreadsheet and group the data together.

If you want more control over how they are displayed, it can be helpful to know ahead of time how many of the groups in your spreadsheet will appear on your map. BatchGeo will prominently display nine groups, while the rest are classified as Others until you click the “Others” group to view the data within. If you want all of the groups to appear, you’ll need to combined categories until just nine are in your spreadsheet.

To see how many groups you have, make a pivot table to count your data. Then, sort the table by descending count to isolate the lowest quantity categories. From here you can decide to combine categories together until you end up with nine or so.

View Famous Protests in American History in a full screen map

Current BatchGeo users might think of these as grouping best practices, though it all boils down to preparing your data beforehand. When your groups include more markers, your map users will better understand the data within. Of course, some grouping data is numeric, which means BatchGeo will create ranges—but only if the data can be interpreted as a number. The next section shows how to remove unnecessary text from your data.

Remove Extraneous Text From Numerical Data

The way BatchGeo groups your additional data like category or type also applies to your numerical data. Take, for example, data about the tallest lighthouses in the U.S. In addition to the lighthouse name and state, the Wikipedia table also contains information about the height, both in feet and in meters.

While these details are great, after you add the data into a spreadsheet, you’ll need to do a bit of formatting to get the most out of any metric data. Specifically, you’ll want to separate the two different measurements into their own columns by splitting “Text to Columns”.

Select the column you’d like to separate. In Excel’s “Data” menu, opt for “Text to Columns…” and select what you’d like to split the column based on: Delimited (characters such as commas or tabs separate each field) or Fixed width (fields are aligned in columns with spaces between each field) in the Text Wizard. Finish the Wizard.

But more importantly, you should remove the feet and m, also via “Text to Columns”. For more information about simplifying complicated data in Excel visit our post on the subject.

Instead, you can indicate the measurement in the heading like so:

This will enable the numerical data to automatically be detected. When you copy and paste it in BatchGeo to be mapped, the numbers will show as ranges. The map below demonstrates the benefit of removing extraneous text from numerical data to enable ranges:

View Tallest lighthouse in the United States in a full screen map

While this is what your map will look like if you keep the text in:

This applies to more than just the feet and meters in the lighthouse example. Among others, you should do this with numerical such as:

• Length
• Weight
• Volume
• Distance traveled
• Number of occurrences
• Light-years, parsecs, and other measurements

But removing any text from your numerical data isn’t the only thing you can do to prepare your data for a map.

Separate Your Date Data

Like types or categories and numerical data, dates also provide important context. For example, dates are included in the table of 550+ Major Plane Crashes. However, more often than not, their format isn’t ideal for a spreadsheet (or a map!). Indeed, you’ll find Pivot Tables and other Excel data analysis tricks used to combine dates for maximum insights. We can do something similar for our maps, but that requires a little data preparation.

Instead of complete dates, you’ll want the components of a date. For example, 1977-03-27 separates into individual columns for Year, Month, and Day. This way, you’re able to more easily sort your data by month or year (or both!) in your Excel spreadsheet. Furthermore, when you make your map, the months will be grouped together as displayed below when you opt for Month.

View 550+ Major Plane Crashes in a full screen map

The years, on the other hand, will automatically range (i.e. 1938 – 1923) as you can see above when you select Year.

Add Decades For a More Interesting Story

One final way to add even more insight to your data when prepping it is by assigning decades to the information. This can be an interesting way to push together data that may otherwise be looked at separately.

The methods we’ve covered in this post are some of the ways we’ve altered spreadsheets before we make our maps. You can get creative and find more. The overarching theme of all these tips is to look for ways to manipulate the data to maximize how it will look on the map.

Look for the interesting pieces of your data before you map it. Alternatively, paste your spreadsheet in and see what BatchGeo does on the first try. You can always edit and adjust later. This way, you can be sure the most important parts of your spreadsheet are properly highlighted on your map.

Get started preparing and mapping your data at batchgeo.com today.

Shots, doses, and boosters are common topics of conversation these days. We may think primarily of the COVID vaccine, though that is simply the latest to be recommended through the United States. We can look to other public health vaccines to better understand what places tend to get vaccinated… and which abstain.

It was December 31, 2019 when the world saw the first cases of the latest novel coronavirus. The New Year was only a month old when the World Health Organization declared a global health emergency. COVID-19 took over headlines and hospitals worldwide throughout 2020, leaving devastating consequences in its wake.

However, scientists rapidly developed a COVID-19 vaccine. As doses rolled out, the United States wondered about compliance. How many would voluntarily receive the vaccine? While these numbers are still changing daily, we can look back at older vaccines to see which states, territories, and associated states are likely to be the most compliant. We’ll also be able to identify the parts of the country with less than ideal compliance, as shown on the map below.

View MMR, DTaP, and Varicella US Vaccination Rates in a full screen map

The map contains the CDC’s estimated vaccination coverage for MMR, DTaP, and varicella vaccines among U.S. kindergarteners from 2017–18. You can sort the map by each vaccine’s percentages or use multi-column grouping and filtering to view multiple vaccines’ top or bottom rates altogether. Then read on for more information.

States With Highest Vaccine Compliance

West Virginia often appears in the news for exceptional vaccine rates. But it’s not the only (or even the best!) state, U.S. territory, or other associated states with high compliance among three vaccines: MMR, DTaP, and Varicella.

Measles, Mumps, & Rubella Rates

Doctors recommend two doses of the vaccine for measles, mumps, and rubella (MMR) for best protection. The first is generally given to children between 9-15 months, with a second dose at 15 months-6 years, meaning most children should be vaccinated by the time they enter kindergarten.

A median rate of 94.3% of US kindergarteners received the vaccine, indicating some places have better MMR vaccine compliance than others. Acknowledging the CDC didn’t have MMR data for Wyoming, Puerto Rico, or the U.S. Virgin Islands, below are the locations at or above 96.7% compliance.

State	Kindergarten population	% surveyed	MMR 2 doses (%)
Palau	313	100	100
Mississippi	39,284	100	99.4
Maryland	68,528	98.9	98.6
West Virginia	19,519	77.5	98.4
New York	226,456	100	97.2
North Carolina	127,197	95	97
California	574,702	98.2	96.9
Tennessee	78,743	100	96.9
Texas	387,981	97.4	96.9
Pennsylvania	141,571	87.1	96.7
Delaware	10,988	9.6	96.7

Palau is an island country located east of the Philippines. The U.S. is responsible for the country’s defense until 2044, making it the only territory or associated state to make it in the top best, and the only U.S. location with a perfect MMR injection rate. Of course, these rates are samples. There’s likely someone in Palau who is not vaccinated, but the CDC doesn’t ask every single person.

Of the contiguous U.S., Mississippi has the best (and near-perfect) 99.4% rate of the MMR vaccination—and it’s not alone in the Southeast. West Virginia has a similarly high rate of compliance along with North Carolina and Tennessee. In the Southwest, Texas alone has high rates.

While Texas, Tennessee, and West Virginia, and other Southern states typically face many health struggles (as discovered in the Worst Statistic About Your State On a Map), it’s clear they excel at vaccinations. West Virginia and Mississippi have a logical reason—they’re the only two states in the U.S. that do not allow religious or philosophical exemptions for vaccines.

Up in the Northeast, Maryland touts the second-best MMR vaccination rate of U.S. states and the third-highest overall. Within the same region, New York, Pennsylvania, and Delaware also have rates far above other states. As only California represents the other side of the country, there’s not much about the West to discuss. Now let’s move on to rates of the multi-dose DTaP vaccine.

States Down with DTaP

With the DTaP or diphtheria and tetanus toxoids and acellular pertussis vaccine, four or five doses are required. Even with the hassle of the additional doses, DTaP compliance looks nearly identical to that of the MMR vaccine, as you’ll see below.

State	Kindergarten population	% surveyed	DTaP 4 or 5 doses (%)
Palau	313	100	100
Mississippi	39,284	100	99.4
Maryland	68,528	98.9	99
Virginia**	100,581	4.2	98.2
West Virginia	19,519	77.5	98
Louisiana**	58,277	100	97.7
Pennsylvania	141,571	87.1	97
New York	226,456	100	96.9
Delaware	10,988	9.6	96.9
North Carolina	127,197	95	96.8
Texas	387,981	97.4	96.8

The island of Palau’s rate remains the same as its MMR rate: 100%. Plus, note the familiar states of Mississippi, Maryland, West Virginia, Pennsylvania, New York, Delaware, North Carolina, and Texas. These highly compliant states are newly joined by Virginia and Louisiana with notably high DTaP vaccine rates.

While Tennessee was included in the MMR list, The Volunteer State nearly missed the cutoff for the highest DTaP rates (it’s #12). Now let’s transition to the last vaccine we can compare: Varicella.

Varicella Vaccine

The varicella vaccine is a two-dose immunization that protects recipients from the highly contagious chickenpox. Aside from the Northern Mariana Islands, the vaccine is not required in most U.S. territories or associated states (American Samoa, Federated States of Micronesia, Guam, Marshall Islands, and Palau). The same goes for several U.S. states including Alabama, Arizona, California, Hawaii, Maine, New Jersey, Oklahoma, and Oregon.

Keeping that in mind, below are the states with the highest varicella compliance.

State	Kindergarten population	% surveyed	Varicella 2 doses (%)
Mississippi	39,284	100	99.4
Maryland	68,528	98.9	98.6
West Virginia	19,519	77.5	98.1
Pennsylvania	141,571	87.1	97
New York	226,456	100	96.9
Tennessee	78,743	100	96.8
North Carolina	127,197	95	96.8
Delaware	10,988	9.6	96.7
Texas	387,981	97.4	96.4
Connecticut	39,174	100	96.3

With Palau Island out of the equation, Mississippi takes the lead, though the state is just short of 100% compliance. Once again, there aren’t many new players when compared to the MMR or DTaP’s top states. In fact, there’s only one new addition, Connecticut. Now let’s take a look in the opposite direction: states with the lowest injection rates.

Lowest Complying States

As the anti-vaccination notion spreads across the country, vaccine compliance has gone down. While many states allow religious or philosophical vaccination exemptions, below are the 10 states with the lowest calculated average of all four immunizations.

• District of Columbia (80.5)
• Marshall Islands (82.15)
• Federated States of Micronesia (84.9)
• American Samoa (86.35)
• Northern Mariana Islands (87)
• Colorado (88.33)
• Guam (88.5)
• Kansas (88.96)
• Idaho (89.13)
• Washington (90.23)

Perhaps most surprising is that the nation’s capital has the absolute worst vaccination compliance rate in the entire U.S. (and territories plus associated states). We hope our Senators don’t get chickenpox while working in D.C.!

You can view the rest of the places on the lower end of compliance for all the vaccines when you add multiple groups and values to your filter, explained below.

Group & Filter By Multiple Columns

What could be better than filtering your maps by one spreadsheet column? Filtering by two or more, simultaneously. We explain the easy way you can do so for all of your maps in our post on the subject, Multi-Column Grouping and Filtering. A preview: it involves adding additional values at the bottom of your map. The filter increases as you add more values and you can also include values from different groups via the drop-down on the bottom left. Be sure to implement this in your next map with the help BatchGeo.

The Supreme Court of the United States (SCOTUS) presides at the highest level of America’s judicial system. It’s made up of a chief justice and eight associate justices who get confirmed by a Senate majority vote after a nomination by the president. While geographic diversity has been a key factor in presidential decisions, today’s justices can have ties to more than one state. For example, though the newly appointed Amy Coney Barrett was born in Louisiana, she identifies as being from Indiana, where she attended law school and lives now.

From the first Supreme Court Justice, John Jay in 1789, to the recent confirmation of #115, Justice Barrett, we’ll see which states have a lot of justices and which are without a single SCOTUS. Then we’ll investigate the shortest and longest tenures on the map below.

View Where Supreme Court Justices Are From in a full screen map

The data for the map came from Wikipedia’s list of the state of residence at the time of appointment. You can sort the map by a number of data points, perhaps most notably the president who nominated each justice, their confirmation year, when their tenure started, their position (whether chief justice or associate justice), and more. Let’s jump into it with the states with the most justices.

States Jammed With Justices

Thirty-one of the 50 United States have produced at least one Supreme Court justice. As is often the case, some states have more than others, with New York being the state with most. The Big Apple is where 15 SCOTUS members called home, though it’s not the only state from which multiple justices hail, as you’ll see below.

• New York: 15 justices
• Massachusetts: 9
• Ohio: 9
• Virginia: 8
• Kentucky: 6
• Maryland: 6
• Pennsylvania: 6
• Tennessee: 6
• New Jersey: 5
• California, Georgia & Illinois: 4

You’ll note from the map that five of these states are Northeastern, while four are located in the Southeast of the country. This may be because there were fewer states from which early justices could be appointed.

And while the other states may not measure up to those on the list above, Alabama, Connecticut, and South Carolina’s three justices aren’t anything to sniff at. Additionally, as the newest member of SCOTUS, Amy Coney Barrett is Indiana’s second confirmed judge. Other states with two include Arizona, Colorado, Iowa, Michigan, Minnesota, New Hampshire, and North Carolina. That leaves Kansas, Louisiana, Maine, Mississippi, Missouri, Texas, Utah, and Wyoming which have one each. Now that we know the states where multiple justices resided, let’s take a look at those sans Supreme Court justices.

States Without a Single SCOTUS

Now, not every state has had a Supreme Court justice who calls it home. Some don’t have a single one. Those 19 states are:

• Alaska
• Arkansas
• Delaware
• Florida
• Hawaii
• Idaho
• Montana
• North Dakota
• Nebraska
• New Mexico
• Nevada
• Oklahoma
• Oregon
• Rhode Island
• South Dakota
• Vermont
• Washington
• Wisconsin
• West Virginia

Seven (about 37%) of those are Western states, which becomes clear on the map. Even more specifically, the entire Pacific Northwest (Washington, Oregon, and Idaho) is without a single justice, along with the two non-contiguous states of Alaska and Hawaii. Now let’s move away from location towards the lengths of service.

Shortest & Longest Tenures

Supreme Court justices serve for life. From their confirmation until they die, resign, retire, or are impeached, SCOTUS members maintain their positions. How long that may be can differ from one justice to the next, though for the 106 non-incumbent justices, the average tenure was 16 years and 359 days.

The longest-serving justice was William O. Douglas. This 79th Supreme Court justice served from April 17, 1939 until he retired on November 12, 1975. In total, he served 36 years and 211 days.

The shortest-serving judge was John Rutledge. He served just 138 days between August 12, 1795 and December 28, 1795. Check out everything in between these two extreme tenure lengths by sorting the map. Speaking of, let’s see what more we can do with our maps.

Show More With Images in Your Map

Maps permit you to visualize your data geographically. But you can take that visualization one step further by incorporating photos or images into your maps. An example is our map of where Supreme Court justices are from in which we included portraits of each justice to better describe them.

Detailed steps are included in our blog post on how to Make a Map with Clickable Pop-up Infowindows and Images. It comes down to simply creating a column in your spreadsheet for the link of your images and selecting the proper options in the geocoder. Get started mapping your locations and your images today at batchgeo.com.