Create a workbook
First, you'll create a workbook to help you examine the data of a Washington state doctor, based in the Seattle area, who could be involved in writing illegitimate lidocaine prescriptions. You're conducting a first-time look at the data; if patterns appear suspicious, you'll report your findings to a supervisor.
Lidocaine is covered by Medicare Part D, a health care plan subsidized by the federal government. You'll upload spreadsheets tracking Part D prescriptions into ArcGIS Insights and use several visualizations to answer questions about patterns in the data. You'll link charts, graphs, and maps to see whether there are unusually high numbers in prescriptions, large changes over time, or a wide geographic area in which prescriptions are filled. In this tutorial, you'll create a workbook from scratch to analyze the data from Washington state. You'll then save the model to reuse in Florida.
The workflow and data presented in this tutorial are based on federal government investigations.
Create a new workbook
You'll sign in to ArcGIS Insights and begin a new workbook. You can access the ArcGIS Insights app through your ArcGIS Online organization. Then, you'll add prescription drug data from a doctor in Washington state to the workbook and visualize it using cards. Insights organizes and visualizes data using the concept of a workbook, which consists of pages. Each page can hold a collection of cards used to map, chart, and display tables of data.
- Download the washington state.xlsx and florida.xlsx files and save them to a location you can easily find on your computer, such as your desktop. If you have Microsoft Excel installed, spend a few minutes reviewing the files and note the columns in each table.
- Sign in to your Insights Online account or Insights in ArcGIS Enterprise account.
Note:
To access Insights in ArcGIS Online, your ArcGIS organization's administrator must grant you a license for it. If your organization does not have Insights licenses, you can sign up for a free trial.
To access Insights in ArcGIS Enterprise, licenses and privileges need to be granted to the necessary members of the organization. For more information, see Administer Insights in ArcGIS Enterprise and Insights in ArcGIS Online.
If this is your first time using Insights, the Welcome to Insights window appears with a list of things you can do with Insights.
- If necessary, in the Welcome to Insights window, click Skip.
- Click the Workbooks tab.
- Click New workbook.
The Add to Page window appears. In this window, you can choose a dataset to add to your new workbook. You can choose data hosted in your ArcGIS organization, in a file on your computer, or data from the ArcGIS Living Atlas of the World.
- In the Add to Page window, click Upload file.
- Drag or browse to the extracted washington state.xlsx file and add it to the workbook.
The table is added to the Selected Data pane.
- Click Add.
Your data is added to the workbook, which opens for the first time on your screen.
- In the Data pane, point to Washington.Table.
- Click Rename dataset and type Washington state.
- Click the Dataset options button and choose View Data Table.
This Excel file contains 1,308 records involving Part D drug prescriptions from the Seattle-based doctor. It provides a more detailed view of the table you added to your workbook. From the table, you can learn the following information:
- A total of 1,308 prescriptions from one doctor is not unusual, as doctor's offices often schedule several patients for the same hour.
- The City column represents the city where a prescription was filled and not necessarily where the doctor is located.
- The Date field represents prescriptions from 2015 to 2017.
- The Type field includes other types of Part D drugs. Lidocaine is just one.
- The X and Y fields represent the location of the cities where prescriptions were filled.
- Close the table.
- At the top of the workbook, click Untitled Workbook, type Suspicious Lidocaine Prescriptions, and press Enter.
- Click Save.
Now that you have a workbook, you can add pages and cards for analysis.
Add cards for analysis
Cards are visual representations, created from fields in your tabular data, that can be displayed as maps, charts, or tables. They can be altered to help you visualize the same data in different ways.
- In the data pane, expand the Washington state table.
Each attribute field has an icon that represents the data type. Data type is important because it determines what kind of analysis you can perform with the data. OID and Cost have number symbols, so they can be counted to determine the size of charts. City, State, and Type all show a string symbol (the miniature column chart) and can be shown as unique symbols on a map or as groups of data in charts. X and Y are shown as numbers, but they can be converted to coordinates once you enable location.
- Click the Dataset options button and choose Enable Location.
Note:
The Enable Location window automatically identified the X and Y columns in the Washington state table as containing the latitude and longitude where prescriptions were filled.
- In the Spatial reference box, click the drop-down arrow. In the search box, type web.
- Click WGS 1984 Web Mercator Auxiliary Sphere.
WGS 1984 Web Mercator Auxiliary Sphere is the default coordinate system for web maps in ArcGIS Online and other web apps. Other online map providers also use this coordinate system, as well as a few of its variants, as their standard web map.
- Check the check box to enable the Repeat identical features parameter.
- Click Run.
A new Coordinates field is added to the top of the Washington state layer.
Next, you'll create a map card to display the spatial distribution of prescriptions from the Washington state doctor.
- In the Washington state layer, point to Coordinates and click the check mark.
The blue check mark shows the selected field. Selecting multiple fields allows you to combine data to create charts and tables with multiple variables.
- Drag the Coordinates field onto the page and into the Map drop zone.
A map of the western United States appears with orange dots reflecting where the lidocaine prescriptions were filled.
- Drag the right handle of the map so it shows the entire continental United States.
The default basemap is Topographic, which shows a variety of data, including topography, water features, major roads, and more. To better visualize spatial patterns in the data, you'll change the basemap to a simple one that puts the focus on your data.
- Click Basemaps and choose Dark Gray Canvas.
- In the map legend, click the arrow to expand the Layer options pane.
- In the Layer options pane, click the Symbology tab.
- Under Symbol type, click the arrow and choose Heat Map.
- Close the Layer options pane.
Heat maps are used to visualize frequency or distribution. Areas with the highest number of points are the hottest and are shown in bright yellows and reds. Cooler blues represent lower levels of occurrence.
The doctor you are investigating is based in Seattle, so it makes sense that the heat map would show more intense colors in Washington state.
But what about the prescriptions filled in the Southwest, including California, Nevada, and Texas? Those concentrations can be explained by dual-state retirees, informally known as snowbirds. Dual-home retirees are attracted to areas with no state income tax but warm winter weather. Because they live in their home states part time, many of these same people prefer to keep the doctors they've known for years instead of establishing relationships with new doctors near their winter homes.
None of this data is immediately concerning, so you'll continue to break down the data to look at Part D drugs by type.
- From the data pane, drag Type to the Chart drop zone and onto the Treemap pop-up.
Treemaps represent a hierarchy of data, using square size to show relevance. Lidocaine is this doctor's most-prescribed Part D drug. The advantage of a treemap is that it allows you to easily distinguish between the different drug types and their quantity.
Note:
You can deactivate a card by clicking a different card or in the empty space. Each card is given a default title based on the order in which it was created, such as Card 1. If you add an incorrect table, chart, or map to the page, you can remove it by clicking the More button and selecting Delete.
Your next table will examine cost. This will be Card 3.
- In the data pane, select Type. (You may need to scroll down to see it.)
- Drag the selected field to a Table drop zone.
This creates a summary table. You can rearrange your cards by clicking the toolbar and dragging them to new locations on your page. Your pointer turns into a four-way arrow when you point to the toolbar of an active or inactive card. Arrange cards 1, 2, and 3 so they're adjacent along the top of your page.
- If necessary, click the Zoom out button on the lower right so you can see all three cards.
You're ready to create your fourth card.
- Select both Date and Type in the data pane and drag them onto the Time Series drop zone for a chart card.
- Drag the right handle so that the time series graph matches the length of cards 1 and 2.
Enlarging the card allows you to see the monthly summary of each drug prescription over a two-year period. Unexpected spikes in prescriptions could help you identify unusual and suspicious trends. In addition, pointing to the individual lines activates a pop-up, which details drug type, prescription number, and time range.
You're ready to create your fifth card.
- In the data pane, drag the State layer to the lower right corner, point to Chart, and drop the field on Donut Chart. (If necessary, zoom out so you can see all five cards.)
Card 5 shows the states where prescriptions were filled. You can point to each state to get an exact number. The donut chart shows you that the majority of prescriptions were filled in Washington state.
Once you've created cards, you can change their appearance to clarify the data.
- Click Card 3, the summary chart, and click Visualization type, then choose Bar Chart.
- On the bar chart, click the Sort button and choose Sort Ascending from the menu. If necessary, expand the new bar chart by dragging its lower right corner.
This chart shows that the number of lidocaine prescriptions in Washington state is double that of lidocaine/prilocaine combination, which is a topical and more expensive numbing medication not known as a cutting agent.
In the bar chart, you can point to lidocaine and see that there were 694 prescriptions in Washington state from 2015 to 2017. This number alone is not enough information to be flagged as suspicious. (It's still possible that these lidocaine prescriptions could fall within that doctor's normal range of prescriptions written for that drug.) You still need more context to gain more insight. Exploring the other cards could reveal more details.
Your workbook page currently has five cards. To make the page more meaningful, you can change card names.
- Rename the following cards:
- Card 1: Distribution Heat Map
- Card 2: Treemap by Type
- Card 3: Cost Bar Chart by Type
- Card 4: Time Series by Type
- Card 5: Distribution by State
You can also change the page name to something more descriptive.
- In the lower left of the workbook, click Page 1 and type Washington state.
Now your page contains five relevant cards, which are all labeled clearly to help you and others viewing your analysis. Using the model you've created, you'll give the data a more in-depth look to see whether this doctor is suspicious.
Explore your data
As an investigator, you're looking for anomalies in the data that could signal a shift in pattern that may indicate illegal activity. Now that you have a working model, you'll sort through each card looking for indications. (Some may reveal information and some may not.) First, you'll connect the cards using cross filters so you can look at one type of drug at a time. Then, you'll scan for suspicious-looking patterns.
- On the Treemap by Type card, click the toolbar to activate the card and select Enable cross filters.
You may need to expand the card if you cannot see the toolbar.
- Click Enable cross filters for Time Series by Type and Distribution Heat Map. (You may need to expand a table to see all the header buttons as well as zoom out so you can see all the cards.)
Cross filters allow you to connect data across cards by turning a selection into a filter. This filtering enables you to better compare and contrast your data so you can isolate relevant information and focus on your analysis.
Next, you'll examine your data and determine whether the lidocaine prescriptions warrant further investigating for the Washington state doctor.
- Click Distribution Heat Map and zoom to Washington state so the entire state fills the map.
Tip:
You can zoom in to an area by pressing the Shift key and dragging your mouse pointer around it.
The higher concentrations are centered on cities; this suggests lidocaine is prescribed in higher numbers in areas with higher populations. This makes sense.
- On the Treemap by Type card, click Lidocaine.
Because you enabled cross filters, the map and time series cards will change to show only data for lidocaine prescriptions.
- In Distribution Heat Map, lidocaine prescriptions generally appear in the same locations as other types of Part D drugs.
- In Cost Bar Chart by Type, lidocaine is reflected as being the most prescribed drug in terms of raw numbers. This confirms information you already learned—that lidocaine is an inexpensive and commonly prescribed drug.
- In Time Series by Type, the trend lines fall within the same ranges over a two-year span, indicating that there is no sudden suspicious spike in prescriptions over a short period of time as may be expected if the doctor were involved in illegally prescribing additional lidocaine.
This card could be your most important consideration because it provides you with the context showing that the lidocaine prescriptions from the Washington state doctor, as well as the other Part D drugs, did not have any unusual spikes.
- In Treemap by Type, click Lidocaine again so all the data is displayed.
- On the workbook ribbon, click Save.
Share the model
Next, you'll share your analysis as a model so that you can reuse the same workflow to investigate and explore prescriptions for the doctor in Florida. Your workbook is almost ready, but you need to add some descriptive data and share as a model, which is a visual recording of the steps in your analysis. Models are created automatically as you work, so you can focus on your analysis instead of creating the model itself.
- Click the Analysis view button.
In the Analysis view, you can visualize your current workflow as a model and see how the cards are connected.
- In the upper right of the workbook, click the Publish button.
- In the Publish window, for Type, select Model.
- In the Share as window, complete the following parameters:
- For Title, type Lidocaine analysis.
- For Tags, delete Insights and type Part D, HHS, and Lidocaine (press Enter after each tag).
- For Description, type Possible Part D prescription abuse.
- For Share with, check the name of your organization.
- Click Publish.
- If necessary, switch from Analysis view to Page view before continuing.
After reviewing all five cards, you decide that you don't have the justification needed to flag this doctor as suspicious. The most important card turned out to be Time Series by Type, which demonstrated that all the lidocaine prescriptions over a two-year span appear within normal ranges of prescriptions from this doctor. This amounts to expected behavior. What you do have, however, is a baseline that you'll use for comparison while investigating a suspicious Florida doctor.
Report anomalies
Previously, you constructed a workbook that allowed you to examine the prescription history of a Washington state doctor. Next, you'll examine the second tip you received, about a doctor in Florida. This doctor is also under suspicion for overprescribing lidocaine, which can be used in the illegal drug trade. You'll start by creating a second page and adding the model you created in the previous section. Then, you'll update it to show the data for the Florida doctor's prescriptions and analyze it for possible suspicious patterns.
Create a second page
Each workbook can have multiple pages that allow you to switch between models and analyses. To investigate the Florida doctor, you'll create another page and add the model to it. Then, you'll add the data for the Florida doctor's prescriptions.
- On the ribbon, next to the Washington state tab, click New page.
A new page is created and the Add to page window appears.
- Click Upload file.
- Click Browse my computer, browse to the florida.xlsx file, and click Open.
In the first section, you developed cards and shared a model that had automatically recorded your workflows. In this section, you're going to use that same model, and by adding the Florida data, you will create a valid comparison among the cards comparing the lidocaine behaviors of the Seattle and Orlando doctors.
- In the Add to Page window, click the Model button.
- In Results, search and select your Lidocaine analysis model.
- Click Add.
The Florida table is added to the data pane but is not yet associated to the cards on the page created by the model you are reusing. The page and card names match the Washington state page used to share as a model. You'll update the model with the data in the next section.
- On the Distribution Heat Map card, click <Washington state>.
- In the Dataset table, change the following parameters:
- Choose Dataset: Florida.Table
- Replacement field X: X_coordinate
- Replacement field Y: Y_coordinate
- Click Update.
The imported model updates with data from the Florida Excel file you just imported and all the cards now reference the Florida data.
- On the bottom tab, change the name Page 2 to Florida.
- In the Data pane, click Rename dataset and change Florida.Table to Florida.
- Click Dataset options and click View Data Table.
The data in the Florida table is organized in the same format as in the Washington state table. The difference is that this table contains 2,584 records, indicating a significantly higher number of prescriptions written by the Florida doctor. The doctor in Washington had written 1,308 prescriptions during the same period.
- Close the table.
You're now ready to explore the Florida data in greater detail as well as in the same manner you inspected the Washington state data.
Identify anomalies in Florida
As in the first section, you'll compare and contrast the cards so you can isolate suspicious lidocaine prescriptions.
The doctor is based in Orlando, so a yellow concentration over central Florida makes sense. Florida, also a warm-weather state with no income tax, is popular among retirees. The low concentrations of heat in the American Midwest can be explained because, like the dual-state residents from Washington state, some retired residents from Florida prefer to retain their home-state doctors and fill their prescriptions while living in the American Midwest during the summer months.
- Click the Distribution Heat Map card and zoom in to central Florida.
Unlike Washington state, the higher concentrations in Florida are not all centered on cities (in fact, many of these concentrations include some rural areas of central Florida dominated by farming, citrus, and cattle). In addition, central Florida has a history with drug trafficking.
- On the Treemap by Type card, click each of the Part D drugs and stop on Lidocaine.
The cards change relative to the selected drug. Once the other cards update, the following data patterns are revealed:
- In Distribution Heat Map, lidocaine prescription hot spots generally appear in irregular locations in comparison with other Part D drugs.
- In Cost Bar Chart by Type, the count of lidocaine prescriptions (1,395) is a little over double that of lidocaine/prilocaine (594), which is a more expensive drug that isn't used as a cutting agent with street narcotics. Again, as in the first section, this doesn't tell us anything we didn't already know; namely, lidocaine is a common and inexpensive drug.
- Distribution by State reflects the states where lidocaine prescriptions were filled. Florida is by far the state with the highest number of prescriptions filled for this doctor, with Oklahoma and Arkansas in second and third ranking. This is not concerning.
- Time Series by Type, however, reveals something you have not yet observed—namely, an unexpected and significant jump in lidocaine prescriptions from this doctor beginning in March 2017.
Pointing your cursor at the lidocaine line activates a pop-up with more specific numbers broken down by weeks instead of months.
- Beginning with 1/1/2017, drag your pointer over the line and stop on the dot after lidocaine makes its dramatic spike beginning on 3/5/2017.
Something happened involving this doctor and lidocaine prescriptions beginning on the week of March 5, 2017. You can see that they jumped from three to 31 prescriptions in one week; you don't know why.
- In Treemap by Type, click Lidocaine again to deselect it and show all the Part D drugs on the page.
The card clearly shows that this spike is significantly different from the normal pattern of Part D drug prescriptions from this doctor.
For context and possible confirmation of your finding involving the Florida doctor, you'll switch back to your Washington state page so you can make a valid comparison between the lidocaine histories of the Seattle and Orlando doctors.
- In the lower left of the workbook, click the Washington state tab.
- On the Treemap by Type card, click Lidocaine.
Again, as you learned during the first section, the Washington state doctor is showing no unexpected spikes in lidocaine prescriptions during the 2015 to 2017 time period as shown on the Time Series by Type card. This contrasts sharply with the Florida doctor, whose lidocaine prescriptions jumped dramatically, and unexpectedly, in March 2017.
Historically, Florida has been an entry point for importing cocaine because of its proximity to South and Central America, which is the major source or origin for this illicit drug. So it makes sense, in a larger context, that the likelihood is greater that a Florida doctor could be involved in prescribing drugs used in narcotics trafficking. Over time, the lethality of cocaine's mixtures has changed, but lidocaine does appear to endure as one of its primary cutting agents.
- Click Save.
Based on information revealed in Time Series by Type, you decide that you do have the justification to flag the Orlando doctor for suspicious lidocaine prescriptions. You feel additional investigation is warranted and you want to share your results with your supervisor.
Share your workbook
ArcGIS Insights allows you to share your data, page, analysis, and workbook. In your case, you want to share your workbook so you can show and explain your analysis and justification with your supervisor.
Note:
Users with Administrator privileges will have access to all workbooks created within their organization, regardless of whether the workbook is shared. When you create and save a workbook in Insights in ArcGIS Online, an Insights Workbook item is created within your ArcGIS Online content. When you share it from your home page, it becomes available to your organization. And when sharing, you're providing read-only access; you retain full editing control over your content.
- Save your workbook, if necessary.
- On the workbook header, click the Insights logo.
The workbook is closed and the Insights home page is displayed.
- Click the Workbooks tab.
Depending on your settings, the Workbooks page will display your workbooks, workbooks that were shared with you, or both.
- Locate your Suspicious Lidocaine Prescriptions workbook. The workbooks are automatically sorted newest to oldest, so your workbook should be at the top of the page.
- Click Share.
- In the Share with window, check the box for your organization and click Share.
A read-only version of your workbook will now be available for members of your organization.
In this tutorial, you relied on the information you created from your first page about Washington state to discover information about a Florida doctor who could be writing suspicious lidocaine prescriptions. You compared the cards so you could make an objective comparison between a doctor in Seattle and one in Florida. You believe you have discovered an indicator that could justify a law enforcement investigation involving the Florida doctor.
In completing this tutorial, you investigated prescription data for two doctors in different states and discovered information that prompted you to flag the Florida doctor's activities as suspicious. (In addition, you illustrated and analyzed data about a Washington state doctor who did not need another investigative look.) By using ArcGIS Insights, you successfully channeled the resources of your office toward a doctor deserving further scrutiny and away from a doctor who didn't. Knowing this information in advance could save your office effort, time, and money.
ArcGIS Insights could be applied to any data that lends itself to illustration. By geographically enabling data, and creating cards, you can create workbooks that can be shared with coworkers and supervisors, regardless of location.
You can find more tutorials in the tutorial gallery.
