HW6: Time Series Chart.
Note: Before doing this lab assignment, you should have read the "Good Charts" and the Budget section (especially the time series parts) of the Presenting Data website.
(in class example)
Goal: Construct a time-series chart displaying two or more two or more data series of US Federal Budget data.
Examples:
from the JPDA book:
Figure 4.3 Three Measures of Voter Turnout, 1952-2004
Figure 4.6 Turnout, % of Voting Age and Voting Eligible Populations
Figure 3.19. Times Series Trend with AnnotationsDATFigure 3.22. Trend with Fixed-unit Scale Figure 3.23. Trend with Log Scale
Figure 3.24. Time Series Chart with Second Y-axis Table 6.1 Millennium Development Goals, Selected Indicators*
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.
Step 1. Download budget data.
- Historical Tables Fiscal Year 2007:
http://www.gpoaccess.gov/usbudget/fy07/hist.html - ind the data you wish to chart.
- Pay attention to how the indicators are defined (Billions of dollars, % of GDP, % of the total budget)
- to divide the data by GDP, copy the data range (note: delete the
transition quarter year), and copy it into the
original excel file. GDP file
Step 2. Format the data
- Check for duplicate years and footnotes in the data, delete extraneous rows or footnotes.
- If your time series trend goes back to the 1970s, be sure to delete the transitional quarter after 1973 (when the start of fiscal year was moved from July to September).
- 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.
- In the Federal budget spreadsheets, some of the the data are in
rows, to convert to columns do this:
- copy the row of data you want
- go to a new worksheet (Insert | Worksheet)
- click on one cell near the top of a column
- use Edit | Paste special --- and select the transpose box
- The labels for the budget trend lines should be on the row above the data.
- For the Chart Wizard to correctly distinguish between the plotted series and the values on the X-axis the data should be not include a label above the X-axis 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
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)