Skip To Content

Create a workbook

In this lesson, 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 Insights for ArcGIS 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 lesson, 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 lesson are based on federal government investigations.

Create a new workbook

First, you'll sign in to Insights for ArcGIS and begin a new workbook. You can access the Insights for ArcGIS 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.

  1. 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.
  2. Sign in to your Insights Online account.
    Note:

    To access Insights 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.

    If this is your first time using Insights, the Welcome to Insights window appears with a list of things you can do with Insights.

  3. If necessary, in the Welcome to Insights window, click Skip.
  4. Click the Workbooks tab.
  5. Click New workbook.

    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.

  6. In the Add To Page window, click Files.

    Files tab

  7. 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.

    Selected Data pane

  8. Click Add.

    Your data is added to the workbook, which opens for the first time on your screen.

  9. In the Data pane, point to Washington.Table.

    Washington state table

  10. Click Rename dataset and type Washington state.
  11. Click the Dataset options button and choose View Data Table.

    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.

  12. Close the table.
  13. At the top of the workbook, click Untitled Workbook, type Suspicious Lidocaine Prescriptions, and press Enter.

    Suspicious Lidocaine Prescriptions

  14. Click Save.

    Save button

    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.

  1. In the data pane, expand the Washington state table.

    Expanded 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.

  2. Click the Dataset options button and choose Enable Location.

    Enable Location option

    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.

  3. In the Spatial reference box, click the drop-down arrow. In the search box, type web.

    WGS 1984 Web Mercator Auxiliary Sphere

  4. 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.

  5. Check the check box to enable the Repeat identical features parameter.
  6. Click Run.

    A new Coordinates field is added to the top of the Washington table layer.

    Next, you'll create a map card to display the spatial distribution of prescriptions from the Washington state doctor.

  7. In the Washington state layer, point to Coordinates and click the check mark.

    Coordinates selection

    The blue check mark shows the selected field. Selecting multiple fields allows you to combine data to create charts and tables with multiple variables.

  8. Drag the Coordinates field onto the page and into the Map drop zone.

    Map drop zone

    A map of the western United States appears with orange dots reflecting where the lidocaine prescriptions were filled.

  9. Drag the right handle of the map so it shows the entire continental United States.

    Right handle on map of the 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.

  10. Click Basemaps and choose Dark Gray Canvas.

    Dark Gray Canvas basemap

  11. In the map legend, click the arrow to expand the Layer options pane.

    Map legend button

  12. In the Layer options pane, click the Options tab.

    Options button

  13. Under Symbol type, click the arrow and choose Heat Map.

    Heat Map selection

  14. 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.

    Heat map

    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.

  15. From the data pane, drag Type to the Chart drop zone and onto the Treemap pop-up.

    Treemap selection

    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.

    Treemap including lidocaine prescriptions

    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 Delete button in the upper right corner.

    Delete button

    Your next table will examine cost. This will be Card 3.

  16. In the data pane, select Type. (You may need to scroll down to see it.)
  17. 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.

  18. If necessary, click the Zoom out button on the lower right so you can see all three cards.

    Three cards aligned

    You're ready to create your fourth card.

  19. Select Date and Type in the data pane and drag them onto the Time Series drop zone for a chart card.
  20. Drag the right handle so that the time series graph matches the length of cards 1 and 2.

    Time series card

    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.

  21. 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.

  22. Click Card 3, the summary chart, and click Visualization type.

    Visualization button

  23. Click Bar Chart.
  24. 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.

    Bar chart featuring lidocaine

    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, in itself, 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.

  25. 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.

  26. At the top of the workbook, click Page 1 and type Washington state.

    Washington state tab

    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.

    Five cards displayed together

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.

  1. 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.)

    Enable cross filters button

    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.

  2. 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 connecting three cards

    Next, you'll examine your data and determine whether the lidocaine prescriptions warrant further investigating for the Washington state doctor.

  3. 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.

    Washington state heat map

    The yellow concentrations are centered on cities; this suggests lidocaine is prescribed in higher numbers in areas with higher populations. This makes sense.

  4. 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.

    Time Series by Type image

    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.

  5. In Treemap by Type, click Lidocaine again so all the data is displayed.
  6. 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.

  1. Click the Analysis view button.

    Analysis view button

    In the Analysis view, you can visualize your current workflow as a model and see how the cards are connected.

    Model view

  2. On the ribbon, on the Washington state tab, click the arrow and choose Share As Model.

    Share As Model button

  3. In the Share As Model window, complete the following parameters:
    • For Title, type Lidocaine analysis.
    • For Description, type Possible Part D prescription abuse.
    • For Tags, delete Insights and type Part D, HHS, and Lidocaine (press Enter after each tag).
    • For Share with, check the name of your organization.

    Share As Model window

  4. Click Share.
  5. If necessary, switch from Analysis view to Page view before continuing.

    Page view button

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 in the next lesson.