Skip To Content

Add malaria data

In the previous lesson, you calculated population estimates for the Democratic Republic of the Congo using Esri's Living Atlas World Population Estimate 2016 layer. Now, you need to add information about the malaria incidence rates. The Malaria Atlas Project (MAP) is a World Health Organization (WHO) partner for collecting malaria incidence data across the world. In this lesson, you'll work with tables containing data on national and subnational incidence counts from 2000 to 2014 for the area of interest. It is common to look at five-year trends, so you'll calculate rates for 2000, 2005, 2010, and 2015. Since the 2015 data is not yet added to the tables, you'll manually add the most recent data.

Load country data using the Excel To Table tool

The Malaria Atlas Project publishes incidence data in a table format. Before loading the data into ArcGIS Pro you need to delete unnecessary rows and columns. Then, you'll run the Excel to Table tool to import the data into ArcGIS Pro and proceed to join it to your feature classes.

Note:

The instructions use a Microsoft Office workflow, but the following steps can also be completed using other spreadsheet software such as LibreOffice. You can download LibreOffice for free.

  1. Download the MalariaIncidence.zip file and extract it.

    The .zip file contains two incidence files for country and administrative areas incidence counts and text documents containing related metadata.

  2. Double-click Country_Incidence_2000_2014 to open the file in Microsoft Excel.

    Microsoft Office Excel

    Microsoft Excel opens with attribute columns for the country name and 14 years of malaria incidence counts. Since you are only interested in five-year intervals for the Democratic Republic of the Congo, you'll remove the unnecessary columns and rows.

  3. Select the second row in the sheet and press Shift while clicking the fourteenth row to select all data in between.

    Rows 2 through 14 selected

    All rows from Africa to Côte d'Ivoire should be highlighted.

  4. Right-click anywhere in the highlighted area and choose Delete.

    Delete selected rows

  5. Repeat the previous steps to delete all rows from Djibouti to South Africa.

    Now only the row for the Democratic Republic of the Congo remains. You'll delete the unnecessary columns.

  6. Select the column for Gaul_Code.
  7. While pressing Ctrl click to select the columns for 2001, 2002, 2003, 2004, 2006, 2007, 2008, 2009, 2011, 2012, 2013, and 2014.
  8. Right-click anywhere in the highlighted areas and choose Delete.

    Now the table only includes a row with attribute names and a row with the country name and incidence counts for 2000, 2005, and 2010.

    Remaining rows

  9. Press F12 to open the Save As window.
  10. Browse to where your extracted files are located.
  11. Save the file with the name CountryIncidence and ensure you are saving it as anExcel Workbook, or (.xlsx) type.

    Saving file as Excel Workbook

  12. Exit Microsoft Excel and return to ArcGIS Pro.

    Now that your malaria incidence data is cleaned and in the correct format, you'll run the Excel to Table tool to load the data into ArcGIS Pro. By using the tool instead of the Add Data button, you ensure that the data is loaded and formatted correctly.

  13. On the ribbon, click the Analysis tab. In the Geoprocessing group, click Tools. In the Geoprocessing pane search for and choose the Excel to Table tool.
  14. For Input Excel File, click the browse button and browse to CountryIncidence.xlsx.
  15. For Output Table, type CountryIncidence.
  16. For Sheet, choose Country_Incidence_2000_2014.

    Importing CountryIncidence.xlsx

  17. Click Run.

    The CountryIncidence table is now visible in the Contents pane.

Update country incidence tables

You have successfully added the table of malaria incidence at the country level. You need to analyze incidents for five-year intervals; however, the data only contains information for 2000, 2005, and 2010. A separate report with data for 2015 is available, but you must manually update the tables.

  1. Right-click the CountryIncidence table and choose Open.

    Notice that the name of the country is spelled out as Democratic Republic of the Congo.

  2. Open the DRC_Country attribute table.

    Notice that the name of the country is spelled as Congo DRC. To append the malaria incidence data, you need to join the CountryIncidence table to the DRC_Country feature class, but to complete a join you need a single attribute in both tables that matches exactly. In this case, you would use the country name, but the naming conventions are different because the data is from different sources. Before performing the join, you'll resolve the naming conflict by editing the table in ArcGIS Pro. The appropriate naming convention depends on the end use, but for this lesson you'll use Congo DRC for brevity.

  3. In the CountryIncidence attribute table, double-click Democratic Republic of the Congo to edit the name.
  4. Rename the cell as Congo DRC and press Enter.
  5. On the Edit tab, in the Manage Edits group, click Save.
  6. In the Save Edits pop-up, click Yes.
  7. Close the CountryIncidence attribute table.

    Now you'll use the Join Field tool to append the CountryIncidence data to the DRC_Country feature class.

  8. In the Geoprocessing pane, search for and open the Join Field tool and enter the following parameters:
    • For Input Table, choose DRC_Country.
    • For Input Join Field, choose COUNTRY.
    • For Join Table, choose CountryIncidence.
    • For Output Join Field, choose Name.
    • For Join Fields, choose 2000, 2005, and 2010.
  9. Click Run.

    After running the tool, the incident data for 2000, 2005, and 2010 is appended to the DRC_Country attribute table. Next, you'll clean up the table a bit more and add the incidence data for 2015.

  10. Open the DRC_Country attribute table.
  11. On the Table View tab, in the Field group, click Add.

    Table View add new field

    The fields view for the table opens. You can see the field name, which is originally in the data, and the alias name, which is displayed instead of the field name when viewing the attribute table. At the bottom, a new row has been generated and needs to be configured.

  12. In the last row, for Field Name, type F2015.
    Note:

    Field names can never start with numbers.

  13. For Alias, type 2015.
  14. Ensure Data Type is set to Long.

    Table View add new field names

    You'll also change the name of the population estimate attribute from SUM to Pop in the CountryBorder Fields table.

  15. In the Fields table, for the SUM attribute, change the Alias name from SUM to Pop.
  16. On the Fields tab, in the Changes group, click Save.

    Save field edits

    The DRC_Country attribute table now includes a column to add the 2015 incidence data and a more appropriate name for the population estimate attribute.

  17. Close the Fields table.
  18. Open the DRC_Country attribute table.
  19. In the attribute table, in the first row for the 2015 attribute, double-click <Null> to edit the cell value.

    In 2015, there were 18,726,106 recorded malaria incidents.

  20. In the cell, type 18726106 and press Enter.
  21. On the Edit tab, in the Manage Edits group, click Save.
  22. In the Save Edits pop-up, click Yes.

Load administrative data using the Excel to Table tool

Next, you'll add the incidence data at the administrative boundary level.

  1. Double-click Admin_Incidence_2000_2014 to open the file in Microsoft Excel.

    The table opens to show malaria incidence data at the administrative level for every country in Africa. You'll filter the data to only show data for the Democratic Republic of the Congo and only for 2000, 2005, and 2010.

  2. Select all data below row 1.
    Tip:

    To quickly select multiple rows of data, first select the second row and press Ctrl+Shift+Down Arrow.

    Rows 2 through 506 should be selected.

  3. On the ribbon, click the Data tab. In the Sort & Filter group, click Filter.

    Open the standard filter

    The filter option is now available at the top of each column. By clicking the small arrow button, you can select which data you are interested in viewing.

  4. In the Country column, click the arrow button and select Democratic Republic of the Congo.

    Open the filter and select Democratic Republic of the Congo

  5. Click OK to apply the filter.

    Only rows with data for the Democratic Republic of the Congo remain. Next, you'll delete unnecessary columns.

  6. Select the column for 2001.
  7. While pressing Ctrl, click to select the following columns: Country_ID, Gaul_Code, 2002, 2003, 2004, 2006, 2007, 2008, 2009, 2011, 2012, 2013, and 2014.
  8. Right-click anywhere in the highlighted areas and choose Delete.

    Edited spreadsheet with only administrative regions and malaria incidence year

    Five columns in the spreadsheet remain: the names for administrative regions, country, malaria incidence for 2000, 2005, and 2010. Before you add the data into ArcGIS Pro, you'll first add the data for 2015.

  9. Next to the 2010 column, type 2015.
  10. Copy the following incidence counts into the spreadsheet.

    Bandundu

    1278200.367

    Bas-Congo

    722036.3631

    Equateur

    1745605.954

    Kasai-Occidental

    1946129.214

    Kasai-Oriental

    2054728.181

    Katanga

    2989521.021

    Kinshasa

    1398453.358

    Maniema

    678064.6795

    Nord-Kivu

    479545.8178

    Province Orientale

    4910368.382

    Sud-Kivu

    516282.5578

    Spreadsheet updated with 2015 data

    There are a few naming conflicts you need to resolve. To reiterate, a join cannot be performed unless attributes match exactly. In this case, you need to correct an attribute with a naming conflict and an attribute with an accent conflict.

  11. Rename Equateur to Équateur.
    Note:

    If you are unable to type the accent using your keyboard, copy and paste the above text.

  12. Rename Province Orientale to Orientale.

    Now that you have finished editing the spreadsheet, copy only the Congo DRC data into a new spreadsheet.

  13. Select all rows except the filter row.

    Spreadsheet with only Congo DRC data selected

  14. Press Ctrl+C to copy the data.
  15. Add a new spreadsheet by pressing the plus button at the bottom of the Excel window.
  16. In the new spreadsheet, press Ctrl+V to paste the data.
  17. Double-click the sheet tab and change the spreadsheet name from Sheet2 to CongoDRC.
  18. Press F12 to open the Save As window. Browse to where your extracted files are located and save the file with the name AdminIncidence as an Excel Workbook type.
  19. Exit Microsoft Excel and return to ArcGIS Pro.

    Next, you'll use the Excel to Table tool to load the data into ArcGIS Pro.

  20. In the Analysis tab, click Tools. In the Geoprocessing pane, search for and choose the Excel to Table tool.
  21. For Input Excel File, click the browse button and browse to AdminIncidence.xlsx.
  22. For Output Table, type AdminIncidence.
  23. For Sheet, choose CongoDRC.

    Excel to Table tool loading the AdminIncidence spreadsheet

  24. Click Run.

    The AdminIncidence table is now visible in the Contents pane. You'll now proceed to join the table to the feature class.

  25. Open the Join Field tool and set the parameters as follows:
    • For Input Table, choose DRC_Admin.
    • For Input Join Field, choose NAME.
    • For Join Table, choose AdminIncidence.
    • For Output Join Field, choose AdministrativeRegions.
    • For Join Fields, choose 2000, 2005, 2010, and 2015.
  26. Run the tool.
  27. Remove the CountryIncidence and AdminIncidence tables.
  28. Press Ctrl+S to save your project.

In this lesson, you cleaned and formatted malaria incidence data and loaded it into ArcGIS Pro. In the next lesson, you'll calculate the malaria incidence rate from the population estimate and malaria incidence data you have added so far.