To convince officials of the homelessness problem in one of the most economically-successful states in the country, you'll need data. In this lesson, you'll download data on homelessness from a federal website. The data contains many variables, so you'll make a new table with only the most significant variables for your study: the number of homeless people, the state population, and the change in the number of homeless people from the previous year.
Download and explore the data
First, you'll download federal data on homelessness and familiarize yourself with its contents.
- Download the
2007 – 2013 PIT Counts by State file.
This data was originally published by the United States Department of Housing and Urban Development and is available on the HUD Exchange site. PIT stands for Point-in-Time, which refers to the annual practice of counting people experiencing homelessness on a single night in January. The National Alliance to End Homelessness offers an additional explanation of Point-in-Time counts.
- Locate the downloaded file on your computer and open it.
If the spreadsheet opens in protected mode, click the button to enable editing.
There are several spreadsheets in the document, but the 2013 sheet is the default. You can check which sheet you're currently using with the tabs at the bottom of your application.
Depending on the spreadsheet application you use, your table and tabs may differ from the example images.
The spreadsheet contains 23 columns and 54 rows. Each column has information about a different aspect of homelessness. The rows organize data by abbreviated state name.
- Click the number to the left of row 30 to highlight the row.
Row 30 contains data for North Dakota (abbreviated to ND). In 2013, North Dakota had 2,069 total homeless people.
What does this mean in a national context? North Carolina (NC) had nearly six times the number of homeless as North Dakota, and some states had even more. Many of these states have significantly higher population than North Dakota, which may explain their higher homeless totals. You’ll need to explore more data to get a better picture of the problem.
- At the bottom of the spreadsheet, click the Change tab.
The Change sheet has data on how homelessness has changed over time.
- Highlight row 30.
From 2012 to 2013, North Dakota’s homeless population increased by about 200 percent. That means the population tripled in the span of one year! Such a sharp increase indicates the problem may be more severe than the total count suggests.
You’ve downloaded the data and examined some of its variables. Next, you’ll create a new table with a few key variables, which you’ll later join to a map layer.
Create a table with homeless count and change
The 2007 – 2013 PIT Counts by State spreadsheet contains extensive data on homelessness—perhaps too much data. Since your goal is to convince state and federal officials of the homelessness problem in North Dakota, your maps must be compelling and concise. Busy officials don't have time to peruse 10 or 20 maps, each showing a different variable. Consequently, you'll limit yourself to only the three variables that give the best picture of the problem. In this section, you'll make a new table with only those variables.
- In Microsoft Excel 2013, click the File tab and click New. Click Blank workbook.
A new table opens, leaving the 2007 – 2013 PIT Counts by State table open as well.
If you’re using a spreadsheet application other than Excel 2013, you may have to follow a different procedure to open a new table. Other procedures described in this section may be different as well.
Before you decide which variables to add to your table, you should first add the State column.
- In the PIT Counts by State spreadsheet, right-click the letter A above the State column and choose Copy to copy the entire column.
- In the new table, paste the data into the first column.
The formatting of the data may change when you paste it.
Next, you need to decide which three variables are most important to your study. Total Homeless is necessary; government officials won't grant funding without knowing how many people will require it. However, North Dakota has a low total homeless population, so this variable could actually work against you. To turn the variable in your favor, a good second variable would be Total Population. By demonstrating that North Dakota's homeless count is high relative to its total population, you'll make a strong point while also providing a counterargument to naysayers unimpressed by the raw counts.
Change in Total Homeless 2012-13 is a strong third variable, as North Dakota's extremely high change in a short period of time makes a powerful statement on the severity of the problem and indicates that the problem could worsen if not dealt with quickly.
- In the PIT Counts by State spreadsheet, in the Change sheet, copy the Change in Total Homeless 2012-13 column and paste it into the next column of the new table.
- In the PIT Counts by State spreadsheet, in the 2013 sheet, copy both the Total Population 2013 column and the Total Homeless 2013 column and paste them into the new table.
The new table now has four columns.
Ultimately, you'll join this table to the attribute table of a layer of the United States. You must first remove all characters that are not allowed in ArcGIS attribute field names, such as spaces and dashes, from the column headers.
- Using the following header names, double-click the headers and change them directly, or click them and change them in the formula bar.
- Change in Total Homeless 2012-13: Change
- Total Population 2013: Pop13
Total Homeless 2013: Homeless13
- Create a new folder called Homeless Project in a location of your choice. In the folder, save your new table as Homeless Data.
- Close Microsoft Excel.
In this lesson, you downloaded a table of homelessness data. You then used the data to create a new table with only a few key variables. In the next lesson, you'll join your table to a map layer of the United States.