Demos
The reasonable allocation of the medical device companies in the US can allow the logistics of medical device inventory to be smooth and cost efficient. Low transportation cost and strong competitive advantage can allow us to keep the affordability of the devices and export it to other countries.
In this article, I would like to share you some methodologies to view the location of these companies in a map in 5 minutes using extremely simple methods on Excel, “drag and drop” on Tableau and a simple dataset. I will also show you device exporters and foreign label distributors’ location. If you are interested in logistics of medical equipment, it will be beneficial to you.
What’s more, according to Gartner’s report “Magic Quadrant for Business Intelligence and Analytics Platforms”, visualization expert “Microsoft” “Tableau”, “Qlik”, along with “Microsoft” are among the leaders in the industry. Learning tools like Tableau will give you an edge in generating analysis and making decisions. You don’t need much prior experience with the software and will achieve impressive results that are shown on the map images in the below content.
Firstly, I would like to transparently inform you how I got the cleaned data. John Snow Labs utilizes various open source technologies or commonly used software for text/data processing and visualization along with machine learning, homemade “Autobots” and proprietary “DataLabs” that put the parts and pieces together for our datasets to meet your needs. Yes, with these ingredients, we can make you a “beautiful soup”. In fact, our validation process is so strict that our process look like a lasagna in layers. These cleaned, and insightful data that are ready to be utilized for analytics and visualization. It took top tier medical professionals and best knowledgeable engineers of ours months and even more than a year to develop our DataLabs and the catalog, for the purpose of saving you time and providing you first class data ingestion and analysis experience. We started a year ago and are proud to keep our endeavour in providing data and software services. Here, we are peaking into some easy ways of utilizing the dataset Medical Devices Establishments for a big picture of these companies’s location.
Firstly, please install Tableau Public, a free visualization software where you can share your visualization results to the community. https://public.tableau.com/en-us/s/download
Excel:
According to the records in the FDA Medical Device list, there are about 3450 cities in the continental US that have medical device companies. If you would like to ask me how I got this number, I would tell you an Excel trick that can process this information quickly. Firstly, I do a “Filter” in the iso_Country_Code field to find rows that contain only “US” in this column.
Then, I copy the City column to a new spreadsheet in order to not impact the integrity of the original dataset.
After highlighting, I hit the Remove Duplicate button under the Data tab.
Then, hit OK.
This notification tells us what was removed and what remains. The unique values in this column are the number of cities that have medical devices in the United States. If we accidentally forgot to record the number of cities we want in the pop up window, we can go down to the bottom of Column A to find this number.
There are other ways to accomplish this task. Please see https://support.office.com/en-us/article/Count-unique-values-among-duplicates-7889942d-824e-4469-893c-191d1efde950
Tableau:
According to a graph from Statista, there are roughly 19500 cities, towns and incorporated places in the United States with population from “under 10,000” to “over 1,000,000” (Number of Cities, Towns and Villages in the United States in 2015, by Population Size). We can conclude that roughly 3450/19500=17.7% cities have direct access to medical device companies.
Another trick I would like to show you is to visualize the location of medical device companies in less than 5 minutes based on the Medical Device Registration data table in John Snow Labs catalog.
Firstly, open a new file in Tableau. Select Connect to Data and choose
Select Text file and bring in the dataset formatted in csv we want.
Go to Sheet One and right click.
Rename it Medical Device Establishment Map. This title will show in the main area we will be mapping on.
In the Data Pane on the left, under Measures, double click Latitude and Longitude respectively.
A transparent map shows on the screen.
Now, we are at the fun part. We would like to map the establishments.
We can see that under “Dimensions”, the most granular location information that has a globe icon is “city”. This means that Tableau can recognize city as a location dimension.
Drag city on top of Detail icon under “Marks” and release when the Detail icon is in grey.
Now, we have a map of the medical device establishments.
We can click the Plus button to zoom in.
When we hover over a dot, it shows the city the dot (Medical Device Organization location) is located at based on the dataset we inputted.
If we drag Name in “Dimensions” on top of “Tooltip” in “Marks”, we will see this in the shelf.
At first, it seems that there’s no difference in the map. Though, when we hover over a Medical Device Establishment, we will be able to see its name.
Please try yourself and add address to the tooltip window.
Lastly, we would like to have a rough idea which state has more accessible medical device establishments. We can drag “State Id” under “Dimensions” to “Color” under “Marks”.
Click “Add all members”.
And here’s the result.
We can roughly see that the state that has the most dots on the widest areas have the most amounts of Medical Establishments. This should be California.
We noticed that Illinois and Ohio have similar colors. We can adjust it.
Click on the down arrow on the card. Click “Edit Color”.
Double click “IN” to bring up a pallet.
Select a random color that is not the same as other states.
To illustrate the difference, I selected Black.
Notice that the color assignment changed IN to black.
Click Apply and OK.
Now, it is obvious to tell that California has the most medical devices establishments.
Note that at the bottom right corner, there are 10KUnknown. This is caused by misspelling, places that has a common name to multiple locations, or locations that are no longer valid etc. Let’s get US data first.
Drag the “Iso Country Code” into the Marks shelf, filter.
Select “US” on the right side to further identify only US data.
Click the bottom right xxx Unknown button to see the errors.
Click Edit Location.
We can see that the City names that are unrecognized are correctly spelled. They are there likely because the name match multiple locations. For example, the first one Abbeville:
It requires identification of each location in order to sort out the unidentified places. Our tutorial ends here. It is meant to give you a quick view of how Tableau works.
To further illustrate Tableau’s visualization capability, we can see the following illustration of further enhancement based on some other datasets in the Establishment Registration and Medical Device Listing Files. For example, we can see the full name and full address of the establishment owner operator in the tooltip.
For another example, we can filter the medical device establishment by its type. Here are all the contract manufacturer.
And here are all the foreign exporter and foreign private label distributor.
We can rank the number of establishment by State. As this below graph confirms, the state that has the most of medical device establishments is CA.
And we can rank the number of establishment by zip code.
From these visualizations, we can see that the medical device establishments is the most dense in California compared to other states. Though in general, there are more of them in the east than in the west. These images are all realized within 5 minutes once you know the whereabouts of the software.
With slightly more input datasets and analysis, we are also able to see foreign exporters and foreign label distributors for a preliminary logistic reference. Congratulations on your visualization!
Reference
Number of Cities, Towns and Villages in the United States in 2015, by Population Size. Statista. Retrieved 20 Feb, 2017 from
https://www.statista.com/statistics/241695/number-of-us-cities-towns-villages-by-population-size/
