Political Science 138
Quantitative Reasoning   

Home

 

Homework Assignment 5: Construct a Time Series Chart of Voter Turnout Data

(THESE ARE OLD EXCEL INSTRUCTIONS)

Read JPDA Voting and Elections (especially pp86ff)
Times series chart tips

Standards for time series charts:

  • minimize the ink-to-data ratio

  • Clearly distinguish between lines representing different series.

  • beware of scaling distortions.

  • always display time on the X-axis, left to right.

  • use data labels rather than legends

Download an Excel file containing time series data from this Census Bureau site:
(use Tables A-1, A-2, A-6 or A-9) (scroll down to find the tables).
                       Census Bureau Voting and Registration

Notes:

  • Some of the files contain presidential and congressional election turnout data, in these cases deleting the Congressional election years and retaining the presidential years is best.

  • Most of the files contain both turnout and registration rates -- for the most part registration rates are not very meaningful.

Example of chart:

Save your charts as both an Excel file and copy the chart to a word file.

Write a 150 word summary of the data in the word file.

Link to both the Excel and Word file.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

OLd Material: Read: Measuring Poverty and Inequality

 When Congress passed the Welfare Reform Act of 1996 (PRWORA), many predicted that it would negatively affect the conditions of the nation’s poor families, particularly families with children who were the primary recipients of the nation’s AFDC (later renamed TANF) program.   In 1995, there were over 5 million families with children on AFDC, today the number is close to 2 million.  Among the most stringent changes in the law, were provisions that would deny public assistance to most non-citizen legal immigrants for five years.
 PRWORA Fact Sheet at HHS

Poverty rates have fallen since 1995, but for some groups more than others.   For this assignment, you are to find data on the poverty rates for at least two different demographic grouping (e.g., age, family status, race and ethnicity) to measure how their level of poverty has changed sine 1995.  The general question you will address is: Which groups have most improved their poverty status since 1995? 

Present the data in a table or chart and write a one page (250 word) summary of the information.

 Note:

  • After the 2001 Census, racial classifications have changed.  For Whites, use the “White alone” data.  For Blacks use the “Black or in combination” data.

  • Copy the data using Internet Explorer.  After you paste it into Excel, you will have to use the Data | Text to columns – fixed width   command to get the data into the proper cells.

 Save both the Excel and Word document files and set up home page links.


  •  

Step 1.  Find the data: The data are here (you can use either the data families or the persons).
 

Step 2.  Format the data

  • Copy and paste the data into an Excel spreadsheet. 
    If all of the data are in a single column, highlight the column and choose:

    DATA | Text to columns
     

  • Check for duplicate years and footnotes in the data, delete extraneous rows or footnotes.
  • The data to be graphed should be in adjacent columns (delete all extraneous columns from
    the dataset).
  • The year (the X-axis series) should be in the first column, with the most recent year sorted to the bottom.
  • For the Chart Wizard to correctly distinguish between the plotted series and the values on the X-axis, do not include a label above the X-axis (year) series (the cell G3 (below) is completely empty.
  • Sometimes the excel file that you downloaded will have merged cells in the header rows.  It may be necessary to unmerge these cells. (Format | Cells | Alignment).

Step 3. Run the Chart Wizard

(note make sure there is a blank sheet to save the chart to use Insert | Worksheet if there is only one worksheet)

Note: Jon Peltier's website provides a complete set of instruction on how to use the Chart Wizard.

Notes:

1.   On step one of the Chart Wizard, there are two different Chart types that can be used for times series plots"

  • line charts
  • XY scatter charts with data points connect by lines (better)

The line chart treats the x-axis as if it were text; the scatter chart treats it as numeric. The line charts places the labels of the x-axis between the markers; the scatter chart places them underneath the markers. The scatter chart almost always requires that the minimum and maximum values be re-scaled on the X-axis.  Usually, the scatter chart is better.

2.  On the last step of the Chart Wizard, specify that the chart will be stored on and empty worksheet.  Adjust the size of the chart so that it takes up columns A through I and approximately 25 rows.

3.   Sometimes the time series data will have missing data, to avoid having the missing data plotted as a zero:

  • Make sure that there is nothing in the missing data cells (even a blank space):
    • highlight the cells and do: Edit | Clear - all
  • Change the chart options:
    • Tools | Options | Chart  plot empty cells as zero (or interpolated)