Solve a spatial problem

In this exercise, you are a business analyst for a consortium of colleges that wants to run a marketing campaign in states with high-value colleges. It's up to you to find states with colleges that have a good return on investment (ROI) for students. You'll use ArcGIS Insights to analyze United States Department of Education College Scorecard data in the form of a feature layer to find relationships between the cost of college and earnings by graduates. In 45 minutes or less, you will do the following:

NoteNote:

To complete this exercise, your account must have the following specifications:

  • Creator, GIS Professional, or Insights Analyst user type
  • Publisher or Administrator role, or equivalent custom role
  • Insights license (not required with an Insights Analyst user type)

For more information, see Administer Insights in ArcGIS Enterprise.

Add data to your organization

The data for this analysis has been provided publicly on the ArcGIS website, where it can be downloaded to your machine. If you are not able to access the Internet from your machine, you can download the data on a different machine and then transfer it to the machine that is running Insights. Follow these steps to access and load the data into Insights:

NoteNote:

If you completed the Create your first workbook lesson, you can reopen your workbook, drag the CollegeScorecard.Table1 dataset to the New page tab New page, and create a map on the new page, then skip to the next section.

Steps:
  1. Follow the link to the CollegeScorecard item.
  2. Click the Download button to download the item to your machine.
  3. Unzip the folder and save the Microsoft Excel file on your computer in a location that you can find easily.
  4. Sign in to your organization.
    1. Sign in to your Portal for ArcGIS account using your organization's URL (for example, http://myserver.mycompany.com/portal/home). If you do not know the URL for your organization, contact your administrator.
    2. Click the App launcher button to display your app menu.
      Gallery of apps
    3. Click Insights.
    4. Enter your username and password again, if prompted.
  5. Click the Workbooks tab Workbooks.
  6. On the Workbooks page, click New workbook.
  7. In the Add to page window, click Upload file.
  8. Click Browse my computer and open the Excel file, or drag the file to the Add to page window. Click Add.
    9. The workbook opens with the CollegeScorecard.Table1 dataset in the data pane.
  9. Click Dataset options Dataset options for the Excel file and choose Enable Location from the menu.
    10. The default method of enabling location uses coordinates. The LONGITUDE and LATITUDE fields from the Excel file are already populated along with the spatial reference.
  10. Click the Repeat identical features check box.

    Checking Repeat identical features means that all of the colleges in the dataset will be added to the map individually, even if there are repeat features. It is important to add the features individually in this lesson so that the analysis tools can be run from the map and will include all of the features, rather than counts of features. For more information, see Enable location.

  11. Click Run.
  12. Expand the dataset in the data pane.
    A location field is added to the dataset
    13. A new location field named Coordinates has been added to the dataset.
  13. Drag the dataset to your page and drop it on the Map drop zone to create a map of United States colleges.
    Create a map
    14. TipTip:

    If you prefer to use buttons, you can select a field from the dataset and click the Map button Map above the data pane.

    If your map is styled by Count of CollegeScorecard.Table1, then you didn't check Repeat identical features when location was enabled. Click the Undo button Undo until the Coordinates field is removed from the dataset then enable location again, ensuring Repeat identical features is checked.

  14. Click Untitled Workbook and replace it with a unique and useful title, such as US Colleges - Your Name. Including your name in the title will make your workbook easier to find if you share your work. Click the Save button Save on the workbook toolbar.

Calculate return on investment

The return on investment for colleges is calculated using cost and earnings after graduation. In this section, you will begin your analysis by calculating ROI for all United States colleges.

Steps:
  1. Click the Action button Action on the map to open the Analytics pane.
  2. Click the Find answers tab and click How is it related? to display spatial and nonspatial analysis capabilities.
  3. Open Calculate Ratio. For the numerator, choose EARNINGS, and for the denominator, choose COST. Name the field ROI and click Run.
    4. A data table appears, providing a view of your raw data. The ROI field is the last column in the table.
  4. Close the data table.
  5. Save your workbook.

Find states with above average ROI

Now that your dataset has a field for return on investment, you can start to find more information about which areas have a high ROI. In this section, you will filter your data and determine which states have the highest ROI.

Steps:
  1. On the map card, click the Card filter button Card filter. The New Filter pane appears.
    2. TipTip:

    Using a card filter instead of a dataset filter will allow you to work with both the filtered and unfiltered data throughout your analysis.

  2. Click Advanced to open the Expression Filter pane.
    3. An advanced filter is an expression-based filter that allows you to create complicated queries or incorporate calculations into your filter.
  3. Enter the expression ROI>AVG(ROI) to query only the colleges with a greater than average return on investment. Click Apply.
  4. Close the Card Filters pane.
    5. A result dataset Advanced card filter result is added to the data pane with the same name as your original dataset. You can rename the result to distinguish it from the original.
  5. Hover over the result dataset and click the Rename dataset button Rename dataset.
  6. Rename the dataset Colleges_ROI and press Enter on the keyboard to set the changes.
    7. There are several ways to analyze ROI within states. In this case, you will use a stacked bar chart so that you can incorporate the state and type of college.
  7. Expand Colleges_ROI to display the fields. Select STATE and TYPE, drag them to the Chart drop zone, and drop them on Stacked Bar Chart.
    Stacked bar chart of STATE, TYPE, and COUNT
    8. A stacked bar chart is created showing the count of colleges with an above average ROI for each state and college type.
  8. Drag the bottom edge of the chart card down to make it easier to see all of the states.
  9. Click the Sort button Sort on the card toolbar and choose Sort Descending Sort descending.
    10. The chart now shows the state with the highest count of colleges with above average ROI at the top, and the rest of the states in descending order. Now you can change the axis labels to make the chart easier to understand.
  10. Click the More button More on the chart toolbar, then Edit Labels Edit Labels. Click the axis to rename it. Name the x-axis (horizontal) Count of colleges with above average ROI and rename the y-axis (vertical) State and college type.
    11. You now have a bar chart that shows the count of high-ROI colleges for each state and college type. In the next section, you will analyze the colleges spatially.

Analyze the ROI spatially

Steps:
    In the previous section, you used a chart to calculate the count of colleges with above average ROI. Another way to calculate the count is through a spatial aggregation. Doing a spatial aggregation will allow you to display the counts on a map and incorporate spatial patterns into your analysis. To do a spatial aggregation, you will need a spatial dataset with the appropriate boundaries.
  1. Click the Add button Add to open the Add to page pane.
  2. Click Living Atlas to display the available Living Atlas layers.
  3. Select the USA States (Generalized) dataset. Use the Search bar if necessary to reduce the number of datasets being displayed. Click Add.
    4. A map of U.S. states is added to your page. You do not need to keep this map on the page to use it in your analysis.
  4. On the map of U.S. states, click More More then Delete Delete.
  5. Drag the state dataset from the data pane onto the map of Colleges_ROI and drop it on Spatial aggregation.
    6. The Spatial Aggregation pane appears. By default, the aggregation will calculate the count of colleges in each state.
  6. Click Run.
    Count of colleges with above average return on investment, by state
    7. The Colleges_ROI result dataset is replaced with a new Spatial Aggregation 1 dataset. The map is updated to show the counts of high-ROI colleges for each state with graduated symbols. The map shows the same information as the chart, but without incorporating college type. Rather than using two methods of displaying the same information, you can create a map that shows the percentage of colleges with a high ROI. To do this, you will need a count of all colleges in each state.
  7. Drag the original College_Scorecard.Table1 dataset to the map and drop it in the Spatial aggregation drop zone. Click Run to calculate the count of colleges within each state.
    8. TipTip:

    The count is calculated by default.

    A second Spatial Aggregation dataset is added to the data pane and the map updates to show the new Count of CollegeScorecard.Table1 field.

  8. Expand the Spatial Aggregation 2 dataset. There are two count fields: Count of Colleges_ROI, which includes the count of colleges with above average ROI in each state, and Count of CollegeScorecard.Table1, which includes the total count of colleges in each state.
  9. Click the Rename dataset button Rename dataset next to Spatial Aggregation 2 and name the dataset College counts.
  10. Open the Dataset options menu Dataset options and click View Data Table.
    11. The data table appears, displaying the raw data for the dataset. The data table can be used to calculate the percentage of colleges that have an above average ROI.
  11. Click the + Field button to add a new field to the data table.
  12. Click New Field and update the field name to PercAboveAvgROI (Percent above average ROI).
  13. Click the Enter calculate function box and enter the equation (Count of Colleges_ROI/Count of CollegeScorecard.Table1)*100. Click Run and close the data table.
  14. Click the number field button Number field next to the PercAboveAvgROI field and choose Rate/Ratio to change the field type.
    15. The new field will now be treated as a proportional value, rather than a quantity.
  15. Drag PercAboveAvgROI to the map to update the style.
    Percent of colleges with above average return on investment
    16. A choropleth map (a map styled with graduated colors) is created.
  16. Resize the map so that it fits onto your page and all of the states are visible.
  17. Save your workbook.

Change the classification on your map

Now that you have your map, you can start making conclusions about return on investment in different states. However, it's important to remember that your conclusions are going to be affected by the classification on the map. Therefore, you should check what classification is being used before you finish your analysis.

Steps:
  1. Open the Layer options pane by expanding the layer name on the map.
  2. Click the Options tab Options.
    3. The default classification is natural breaks with five classes. Natural breaks is a useful classification for seeing natural groupings inherent in the data, but it may not be the best classification for this scenario.
  3. Click Natural Breaks to expand the Classification type menu.

    4. There are six classification options available: Natural Breaks, Equal Interval, Quantile, Standard Deviation, Unclassed, and Manual. An unclassed classification gives the map a continuous color ramp rather than discrete classes. In this case, it is probably best to have discrete classes so that the states can be analyzed in groups. An equal interval classification is good for data with a known range, such as percentages, because it allows you to group your data into set ranges (for example, a dataset with percentages could be divided into five intervals with ranges of 20 percent). A quantile classification divides the data into groups with an equal number of features, which makes it good for data that you want to display by rank. In this case, a ranked classification could be useful. A standard deviation classification is useful when you want to focus on the distance from the mean. While knowing the average could be helpful in this scenario, it doesn't need to be the focus of your analysis. Finally, a manual classification can be used to create a custom classification scheme. Manually changing the classification can be useful for data with specific values that need to be taken into account or for standardizing the classification between multiple maps.

    On the surface, quantile and equal interval seem like the best options. However, this dataset has 51 features (50 states plus the District of Columbia), which makes it difficult to divide the data equally into a quantile classification. It may be best to try equal interval.

  4. In the Classification type menu, click Equal Interval.
    5. The classification updates. You can click the dividers to see the ranges of the intervals. The divisions are at 21, 34, 47, and 60. It makes sense to have an equal interval, but these values are not intuitive. It would make more sense to apply an equal interval to a full percentage range from 0 to 100. You will use intervals of 10.
  5. Change the number of classes to 8.
  6. Click the first slider and change the value from 16 to 10. Press Enter.
    7. The divider moves to 10 and the Classification type updates to Manual.
  7. Change the other dividers to 20, 30, 40, 50, 60, and 70.
    Percent of colleges with above average return on investment, classified by return on investment
  8. Click the Legend tab Legend to see the values. Click values in the legend to select the features in each class.
    9. TipTip:

    Change the Display field setting Display field for the Location field so that the state names will be displayed when you hover over them.

  9. Save your workbook.
    10. The highest percentage of above average ROI colleges is in Wyoming. The next-highest percentages are in South Dakota and New Mexico. These states are all in the central part of the continental United States. As discussed in Create your first workbook, the central states, Hawaii, and Alaska had relatively few private for-profit and nonprofit schools, and a relatively large proportion of public colleges. You also see in the bar chart that the vast majority of high-ROI colleges are public. It makes sense that states with a high proportion of public colleges would have a large percentage of colleges with a high ROI.

Next steps

Now that you have finished your analysis, it is time to share your results with your colleagues. Use the quick exercise Share your analysis to continue the college scorecard scenario and share the results as a model and an interactive page.

Copyright © 1995-2019 Esri. All rights reserved.