Obtaining the Tutorial Data

Before beginning the tutorial, you will copy all of the required tutorial data onto your Desktop. Option 1 is best if you are completing this tutorial in one of our short courses or from the GIS/Data Center and Option 2 is best if you are completing the tutorial from your own computer.

OPTION 1: Accessing tutorial data from Fondren Library using the gistrain profile

If you are completing this tutorial from a public computer in Fondren Library and are logged on using the gistrain profile, follow the instructions below:

1. On the Desktop, double-click the Computer icon > gisdata (\\file-rnas.rice.edu) (R:) > Short_Courses  >  Mapping_Tabular_Data.
2. To create a personal copy of the tutorial data, drag the Tabular folder onto the Desktop.
3. Close all windows.

OPTION 2: Accessing tutorial data online using a personal computer

If you are completing this tutorial from a personal computer, you will need to download the tutorial data online by following the instructions below:

1. Click Tabular.zip above to download the tutorial data.
2. Open the Downloads folder.
3. Right-click Projections.zip and select Extract All....
4. In the 'Extract Compressed (Zipped) Folders' window, accept the default location into the Downloads folder and click Extract.
5. Drag the unzipped Tabular folder onto your Desktop.
6. Close all windows.

Joining Tabular Data in ArcGIS Pro

Opening an Existing Project

1. On the Desktop, double-click the Tabular folder.
2. Double-click the Tabular.aprx project file to open the project in ArcGIS Pro.

Adding Tabular Data

You will now add a feature class to a new map that depicts all of the land parcels in downtown Houston. This feature class was obtained from the Harris County Appraisal District (HCAD) Public Data GIS website at http://pdata.hcad.org/GIS/index.html. The original parcels shapefile contained all of the parcels in Harris County, but working with such a large file can be time consuming, so, for the purposes of this tutorial, the layer was clipped to contain only those parcels in downtown Houston.

1. In the Catalog pane on the right, expand the Databases folder.
2. Expand the Tabular.gdb geodatabase.
3. Right-click the DowntownParcels feature class and select Add To New Map.

You will now examine the DowntownParcels layer’s attribute table.

1. In the Contents pane on the left, right-click the DowntownParcels layer name and select Attribute Table.
2. Browse through all fields in the attribute table.

The HCAD_NUM field contains the unique 13-digit HCAD account number for each parcel. You are also provided with the owner name and address for each parcel, but not very much other information. Fortunately, a tremendous amount of other information about the parcels can be obtained from HCAD in stand-alone tabular format. In this case, you would like to know the particular land use for each parcel, such as residential, commercial, or industrial. This information is contained in a separate table, which you will now explore.

1. Close the DowntownParcels attribute table.
2. In the Catalog pane, within the TabularData geodatabase, right-click the Land table and select Add To Current Map.
3. In the Contents pane, right-click the Land table and select Open.
4. Scroll down and browse through all of the fields.

Notice that you are again provided with the unique HCAD account number, but this time the field is labeled ACCOUNT, rather than HCAD_NUM, as it was in the DowntownParcels feature class attribute table. You are also provided with the land use code and the land use description for each parcel. In order to symbolize the parcels by land use, you will first need to join the land use table to the parcels feature class.

You will now explore the various land use codes.

1. Right-click the LAND_USE_C field and select Sort Ascending.
2. Scroll down and browse through all of the land use codes and descriptions.

Notice how many different land use categories there are. For example, commercial land use is divided into very specific categories, such as Restaurant, Ice House, Bar/Lounge, Department Store, Medical Office, and Bank. While it is great to have this information available, symbolizing so many different categories on a single map can make the map difficult to interpret. Instead, you would prefer to symbolize the parcels using more generalized land use categories.

Fortunately, the City of Houston has created a lookup table that assigns each appraisal district land use code to one of ten generalized land use categories. You will now explore this lookup table.

1. In the Catalog pane, within the TabularData geodatabase, right-click the LandUseCode_LookupTable table and select Add To Current Map.
2. In the Contents pane, right-click the LandUseCode_LookupTable table and select Open.
3. Scroll down and browse through the land use descriptions and the associated group descriptions.

Notice that the COUNTY field tells you which county uses the particular land use code listed. “HCAD” stands for Harris County. If you scroll down to the bottom of the table, you will notice “FBCAD” and “MCAD” for Fort Bend County and Montgomery County, respectively. The LANDUSE_CODE and LANDUSE_DESCRIPTION fields should look familiar, since they contain the same information as the Land table you looked at previously. The GROUP_CODE and GROUP_DSCR fields store one of ten general land use categories that correspond to each specific land use code. In order to symbolize the parcels using these generalized land use categories, you will also need to join the lookup table to the land use table and the parcels feature class. You will perform the join using the land use code field that is common to both your land use table and your lookup table.

1. Close all attribute tables.

Joining Tabular Data

Now that you have examined all of the data tables, you are ready to join them all together. It is possible to join tables together in any order, but you will want to join both land use tables to the back of your DowntownParcels attribute table, so that you can later symbolize the parcels based on their land use. You will first join the Land table to the DowntownParcels feature class using the unique HCAD account number as the common join field.

1. In the Contents pane, right-click the DowntownParcels layer name and select Joins and Relates > Add Join.

The Geoprocessing pane should open on the right in a new tab over the Catalog pane. In the Add Join tool, notice that 'Layer Name or Table View' is already set to the DowntownParcels layer, since that was the layer used to open the tool.

1. For ‘Input Join Field’, use the drop-down menu to select the HCAD_NUM field.
2. For ‘Join Table’, use the drop-down menu to select the Land table.
3. For ‘Output Join Field’, use the drop-down menu to select the ACCOUNT field.
4. Ensure that the ‘Add Join' tool settings match those shown below and click Run.
   
   
   
   
5. Right-click the DowntownParcels layer and select Attribute Table.
6. Scroll to the right and browse through all fields.

Notice the four new fields added to the end of the DowntownParcels attribute table showing the land use for every parcel. Because you want to symbolize the parcels using the generalized land use categories, you will now also join the LandUseCode_LookupTable table using the land use code as the common field.

1. Close the attribute table.
2. In the Contents pane, right-click the DowntownParcels layer and select Joins and Relates > Add Join.
3. For ‘Input Join Field’, select the Land.LAND_USE_C field.
4. For ‘Join Table’, select the LandUseCode_LookupTable table.

For ‘Output Join Field’, notice that the LANDUSE_CODE field that you want to base the join on cannot be selected. This is an indicator that the two join fields are probably not of the same type and cannot be joined as is.

1. Close the ‘Join Data’ pane without completing the join.

Troubleshooting Joins

You will now examine the two tables more closely to determine why the desired join field did not show up in the’ Join Data’ pane.

1. In the Contents pane, right-click the Land table and select Open.
2. Right-click the LandUseCode_LookupTable table and select Open.

Notice at the top left of the ‘Table’ window, you see one tab for each of the two tables that are open, but you want to be able to examine both tables simultaneously.

1. Select the title of your ‘Table’ window, drag it to the middle of your Map Window, and hover it over the left most of the four placement options until it changes color. Release. It should snap to the left side of the Map Window. Repeat for the other attribute table.

When looking at a table, any column that is left-aligned is treated as text and any column that is right-aligned is treated as a number. Examine the two fields that you were previously attempting to join. Notice that the LAND_USE_C field in the Land table is right-aligned as a number and the LANDUSE_CODE field in the LandUseCode_LookupTable table is left-aligned as a text. For further confirmation of this problem, you will examine the properties of each field.

1. In the Land table, right-click the LAND_USE_C field and select Fields. Notice that the Data Type is “Long”, which stands for long integer.
2. Close the ‘Fields’ window.
3. In the LandUseCode_LookupTable table, right-click the LANDUSE_CODE field and select Fields. Notice that the field type is “Text”.
4. Close the ‘Fields’ window.

In order to perform a join, the corresponding join fields in the respective tables must be of the same field type. Unlike in Excel, you cannot simply convert an existing field from one type to another. Instead, you must create a new field of the correct field type and copy the data over into the new field. In this case, you will convert the LAND_USE_C field in the Land table to a string field type to match the string field type used in the LandUseCode_LookupTable table.

1. In the Catalog pane, right-click the Land table > Design > Fields.
2. At the bottom, click ‘Click here to add a new field’.
3. For ‘Field Name’, type “LAND_USE_C_Text”.
4. For ‘Data Type’, select Text. On the Fields tab, click Save.

The new LAND_USE_C_Text field has now been added to the end of the Land table.

1. Close the ‘Fields’ window.
2. In the Catalog pane, right-click the Land table and select Open.
3. Right-click the LAND_USE_C_Text field and select Calculate Field.

Over the Catalog pane, the Calculate Field geoprocessing pane will appear.,

1. In the Fields menu, double-click the LAND_USE_C field.

Notice that the box at the bottom of the ‘Field Calculator’ pane now reads LAND_USE_C_Text = [LAND_USE_C], as shown below. This statement will copy all of the content from the original LAND_USE_C integer field to the new LAND_USE_C_Text text field.

1. Click Run.

Ensure that the land use codes have indeed been copied over into the new LAND_USE_C_Text field and that they are left-aligned as text.

1. Close the attribute tables.

You will now attempt to join the tables again using the edited fields, but you will also need to redo even your original join, since the Land table has been modified.

1. In the Contents pane, right-click the DowntownParcels layer and select Joins and Relates > Remove Join(s) > Remove All Joins.

You will now repeat the process of joining both tables.

1. In the Contents Pane, right-click the DowntownParcels layer and select Joins and Relates > Add Join.
2. For ‘Input Join Field’, select the HCAD_NUM field.
3. For ‘Join Table’, select the Land table.
4. For ‘Output Join Field’, select the ACCOUNT field.
5. Ensure that the ‘Add Join’ pane matches that shown below and click OK.

1. Right-click the DowntownParcels layer and select Attribute Table.
2. Scroll to the right and ensure that the four land use code fields were added.
3. Close the attribute table.
4. In the Contents pane, right-click the DowntownParcels layer and select Joins and Relates > Add Join.
5. For ‘Input Join Field’, select the new Land.LAND_USE_C_Text field.
6. For ‘Join Table’, select the LandUseCode_LookupTable table.
7. For ‘Output Join Field’, select the LANDUSE_CODE field.
8. Ensure that the ‘Add Join’ pane matches that shown below and click OK.

1. Right-click the DowntownParcels layer and select Attribute Table.
2. Scroll to the right and browse through all of the attributes.

Notice that the generalized group code and description fields have been added to the end of the attribute table.

1. Close the attribute table.
2. In the toolbar at the top of the document, click Save.

Exporting Tabular Data

The joins that you have created are only stored in this particular map document. In other words, none of the original tables have been modified. If you were to add the DowntownParcels layer to another new map document, you would no longer see the joined tables at the back of the layer’s attribute table. In addition, if your joined data tables were ever moved or deleted, the joined data would no longer be visible in this map document. For this reason, it is often a good idea to export data containing successful tabular joins, so that you do not have to worry about losing the joined data in the future.

1. Right-click the DowntownParcels layer and select Data > Export Features.
2. For ‘Output Feature Class’, select the Brows icon and navigate to the Tutorial geodatabase (C:\Users\gistrain\Desktop\TabularTutorialData\TabularData.gdb).
3. Rename the feature class “DowntownParcels_WithLandUse” and click Run.

1. Right-click the DowntownParcels_WithLandUse layer and select Attribute Table.

Notice that all of the joined attributes are now a permanent part of this new feature class attribute table. Since this permanent feature class contains everything you need in one layer, you may now remove your original parcels layer and both joined tables.

1. Close the attribute table.
2. Right-click the DowntownParcels layer and select Remove.
3. Right-click the Land table and select Remove.
4. Right-click the LandUseCode_LookupTable table and select Remove.
5. In the toolbar at the top of the document, click Save.

You have successfully joined tabular data in ArcGIS Pro!

Symbolizing Tabular Data

You will now symbolize the parcels according to their generalized land use categories.

1. In the Contents pane, right-click the DowntownParcels_WithlandUse layer and select Symbology.
2. In the Symbology pane that appears over the Catalog pane, under the Symbology drop-down menu, select Unique Values.
3. In the ‘Value Field’ drop-down box, select the GROUP_DSCR field.
4. At the top right of the list of values, click the More drop-down menu and uncheck Show all other values, since there are none in this case.

You will want to group “Commercial” and “Office” into one land use category.

1. Click the Commercial value to select it.
2. Hold down Ctrl and click the Office value to select it too.
3. Right-click the Commercial value and select Group Values to combine the two categories into one.
4. Click on each of the symbol patches to change the colors to those commonly used by planners to indicate the various land uses, as displayed below. Once the ‘Format Polygon Symbol’ pane appears, click on Properties and change each color. Click Apply to apply each symbol formatting.

1. In the toolbar at the top of the document, click Save and minimize ArcGIS Pro.

Formatting Tabular Data in Excel for Joining

In the first exercise using HCAD data, both the layer attribute table and the join table were already formatted properly, so that you could bring them directly into ArcGIS. Unfortunately, such a situation rarely occurs and most data tables require some formatting to prepare them for import into ArcGIS.

In this exercise, you will be mapping land cover by state. The original data table was obtained from The 2011 Statistical Abstract published by the U.S. Census Bureau at http://www.census.gov/compendia/statab/. The particular table was accessed from the “Geography & Environment” section under the “Land, Land Use, and Earthquakes” category and was called table “265, Land Cover/Use by State: 2003”.  You will now format this table for import into ArcGIS.

1. Double-click the TabularTutorialData folder on the desktop.
2. Double-click LandCoverByState to open the file with Excel.

Along the left side of the spreadsheet are the row numbers. Along the top of the spreadsheet are the column letters. When you are asked to click on a row or column, you will be clicking directly on these numbers and letters.

Removing Extraneous Data from Excel Worksheets

Notice that the first three rows contain title information for the table, but do not contain any data, so you will delete these rows. Notice also that row 1 is barely visible, because its height has been greatly reduced.

1. Click and hold row 3 and drag the mouse up past the top of the spreadsheet to select rows 1 through 3.
2. Right-click row 3 and select Delete.

Sometimes data tables contain a lot more information than you actually need. While it is okay to import large tables into ArcGIS Pro, it can make future tasks more difficult for you. Every time you have to select a particular field for symbology, labels, or queries, you will have to scroll through a long list of fields, rather than a targeted list containing only your selected variables of interest. For this reason, it is ideal to remove all unnecessary data columns in Excel before importing into ArcGIS.

For this particular exercise, you will map the percent of all land that is developed land and the percent of all nonfederal land that is forest land. Such a task will require keeping the columns titled “State,” “Total surface area,” “Forest land,” “Developed Land,” and “Water Areas.” You will remove all of the rest of the columns.

1. Click column C to highlight the entire column.
2. Hold down Ctrl and click columns D, E, F, H, I, L, M, N, O, P.

Ensure that you are left with the five necessary columns previously mentioned.

1. Right-click column P and select Delete.

The selected columns should disappear and you should be left with the columns labeled “State,” “Total surface area,” “Forest land,” “Developed Land,” and “Water Areas.”

When you bring an Excel table into ArcGIS, it assumes that the first row contains the field name and then it assumes the data begins in the second row. Whatever data type it sees in that second row, it assumes is the data type for the entire column. If there is ever more than one row containing title information, ArcGIS will see that title text in the entire second row and assume that all the data contained in the entire table is text, when, in fact, the majority of the data is numerical. In short, all but one field name row must be removed, but, first, you must create an appropriate field name row.

1. Right-click row 4 and select Insert.

This newly inserted row will contain your field names. For field names, you should not use any spaces or special characters and should not start with a numeral or use more than 13 characters. You may use underscores, mixed case letters, and numerals (in the middle or end of the field name only).

1. In row 4, type the following field names: “State” “Total” “Forest” “Developed” “Water”. After typing each word, press Delete (rather than Enter or the right arrow), so that the unwanted remainder of each phrase is not copied from the row above. Ensure that there is not an extra space after the word “State”.

Since you have created a row with appropriate field names, you may now delete all rows above it.

1. Click row 1.
2. Hold down Shift and click row 3.
3. Right-click row 3 and select Delete.

Because you are mapping data for each state, you do not need the total data for the entire United States contained in row 2 or the accidental repeat of the Alabama data in row 3.

1. Click row 2.
2. Hold down Shift and click row 3.
3. Right-click row 3 and select Delete.

Scroll to the bottom of the table and notice there are extra rows that do not contain data here, as well.

1. Click row 50 (FOOTNOTE:).
2. Hold down Shift and click row 56 (Internet release date:).
3. Right-click row 56 and select Delete.

Using Formulas in Excel

At this point, the table is mostly cleaned up, so you will add two new columns to calculate the percent of the total land area that is developed and forest.

1. In cell F1, type “Per_Forest”.
2. In cell G1, type “Per_Developed”.

Now you will calculate the formula where Per_Forest = Forest/(Total-Water).

1. In cell F2, type “=” to indicate the start of a formula.
2. Click cell C2 (Alabama Forest).
3. Type “/(” (divided by, open parenthesis).
4. Click cell B2 (Alabama Total).
5. Type “-” (minus sign).
6. Click cell E2 (Alabama Water).
7. Type “)*100” (close parenthesis, times 100).
8. Ensure your cell says “=C2/(B2-E2)*100” and press Enter to finish the formula.
9. Click cell F2 containing the formula you just entered.

Hover your cursor over the small black box in the bottom right corner of the highlighted cell as shown below. Notice that your cursor changes from a thick white cross to a thin black cross.

1. Double-click the black box in the bottom right of cell F2 to copy the formula down the table.

1. In cell G2, use the same formula building techniques to enter “=D2/(B2-E2)*100” and copy the formula down the table.

One last problem with this worksheet, which is difficult to detect, is that there is actually a space stored after every state name. This space will prevent the table from joining properly to another table whose state names do not have a trailing space. You will now perform a series of automated operations to remove this extra space.

1. Click column B.
2. Hold down Shift and click column D.
3. Right-click column D and select Insert to add three new columns in which to work.

First, you will use the Text to Columns tool, which splits the information contained in one column into multiple columns based on a delimiter. The delimiter is the character which is used to signal when to shift the data to the next column. As a result, the delimiter itself is removed and all characters following the delimiter are shifted to the next column. In this case you will use the space as the delimiter to remove it.

1. Click column A.
2. Click the Data tab at the top of the Excel window.
3. Under the Data Tools section, click the Text to Columns button.
4. In the ‘Convert Text to Columns Wizard’, click Next >.
5. In the Delimiters box, uncheck Tab and check Space and click Next >.
6. Click Finish.
7. When asked if you want to replace the contents of the destination cells, click OK.

The extra spaces have been removed, but the state names with two words have been separated into two columns. You will need to concatenate them back together into a single column separated by a space, but only for those states with two words. Performing a concatenation on all the states with an added space in between the two words would end up adding the trailing space again. The formula below basically says, if column B is blank, then copy the state name from column A; otherwise, if column B contains text, then concatenate the first part of the state name in column A with the second part of the state name in column B and add a space in between them.

1. In cell C2, use your formula building techniques to enter “=IF(B2="",A2,CONCATENATE(A2," ",B2))”. Ensure that there is no space between the first set of quotation marks and a single space between the second set of quotation marks.

In this instance you can’t double-click the black box in the bottom right of cell C2 to copy the formula down, because there is not consistent data in the column the left of it.

1. Click and hold the black box in the bottom right of cell C2 and drag down to cell C49 to copy the formula down the table.

At this point it looks good, but the values contained in column C are actually formulas, not text, and ArcGIS will not be able to interpret the formulas.

1. Right-click column C and select Copy.
2. Right-click column D and select Paste Special….
3. In the ‘Paste Special’ window, select to Paste Values and click OK.

Now the states listed in column D are stored as text, rather than as formulas, so you can now delete columns A through C.

1. Click column A.
2. Hold down Shift and click column C.
3. Right-click column C and select Delete.
4. In cell A1, type “State”.

Removing all formatting in Excel

At this point your table has everything you need and nothing extra, but it is always recommended to clear all formatting and programming, including special fonts, background colors, borders, formulas, etc., before bringing the Excel file into ArcGIS.  To get rid of all formatting and programming, you will copy all of the data into a new blank worksheet.

1. Click the Select All button in the top left corner of the spreadsheet.

1. In the Home tab, click Copy.

1. Click the File tab and select New.
2. Ensure that Blank Workbook is selected and click Create.
3. In the new workbook, right-click cell A1 and select Paste Special….
4. Select to paste Values and click OK.

You should now have a clean version of your data with no special formatting. The last step is to expand the cells to ensure that all of their content is displayed.

1. Click column A.
2. Hold down Shift and click column G.

Hover your cursor over one of the lines between any two of the column letters. Notice that your cursor changes from a single arrow pointing down to a double arrow pointing left and right.

1. Double-click the dividing line between column A and B.

The width of all your columns should now be expanded to show all of the cells’ content. You are finished formatting your table.

1. Click the File tab and select Save As.
2. Navigate to the TabularTutorialData folder on the Desktop.
3. For ‘File name:’, type “US_LandCover”.
4. For ‘Save as type:’, use the drop-down box to select Excel 97-2003 Workbook.
5. Click Save.
6. Close Excel.

Setting Up a Map Document from an Existing Map Document

Now you will return to ArcGIS to map the land cover data by state.

1. Maximize ArcGIS Pro.

Because you already have the map document from the prior exercise set up with the correct default database, you will use this map document as a template, rather than starting a new one.

1. Click the Insert menu and select New Map.

This creates a new map in the project in which you will map your state land cover data.

You will now save this map with a different name so as to distinguish this exercise from the last one.

1. In the Catalog pane, double-click the Maps folder and right-click Map2.
2. Select Rename and change the File name to “USLandCover”.

You will now explore the state data.

1. In the Catalog pane, expand the Databases folder.
2. In the TabularData geodatabase, drag the US_States feature class into the Map view.
3. In the Contents pane, right-click the US_States layer and select Attribute Table.

Examine the attribute table for the US_States layer and notice that the field containing the state names is “NAME10”.

1. Close the attribute table.
2. In the Analysis tab, click the Tools icon.
3. In the Geoprocessing pane on the right, click within the Find Tools search bar and type ‘Excel to Table’.
4. Select ‘Excel to Table (Conversion Tools)’.
5. For the Input Excel file, click the Browse icon to the right and navigate to the TabularTutorialData. Select USLandCover.

Note that the Sheet drop-down menu has appeared, and the default sheet selected is 11s0365. Because a single Excel file can have multiple worksheets and each worksheet is treated in ArcGIS as a separate table, you must always specify which specific worksheet you want to use.

1. Rename the Output Table ‘US_LandCover.’
2. Make sure the Geoprocessing pane looks like the picture below and then select ‘Run’ at the bottom right corner of the pane.

1. In the Catalog pane, double-click the Folders folder to show the TabularTutorialData folder.
2. In the Contents pane, right-click the US_LandCover standalone table and select Open.

Examine the US_LandCover table and notice that the field containing the state names is “State”.

Using your knowledge from the first exercise, try to join the US_LandCover worksheet to the US_States feature class and symbolize the states by both the percent developed land and the percent forest land.  If you get stuck, ask for help.