Political Science 138:
Quantitative Reasoning in Political Science

HW4: Create a bar chart with cross-national data for your data profile paper.

Note: Before doing this lab assignment, you should have read the "Good Charts" section (especially the bar charting part)  of the JPDA website

Goal: To create a nice bar chart to be used in your data profile paper.  

Examples of cross-national rotated bar charts:
Gun deaths  Health Care  Poverty Poverty2 Civic Participation

Standards for bar charts:

• Data should be sorted on the most significant variable (not alphabetically).
• Maximize the ratio of data-to-ink (avoid 3-D effects and extraneous elements).
• Precise labeling and definition of values.
• Multivariate displays of data are best (see the poverty chart, above)

Notes: In this lab exercise you will learn how to:

• Copy data to Excel from a website
• Use a variety of Chart options
• Sort data

Step 1.  Find some good data: Cross-National Data Sources.

Below are some links to data on each of the profile topics.  Note that several of these sites have other data on your topics.  Several of these sites will have more data (more countires and more variables) than you need for this exercise. It is your responsiblity to select meaningful data.

see also: project resources page.

(Note on downloading data: use Internet Explorer to copy data from a web page, be sure to del4te non-numeric data from cells)

International Data:

Sources of International data:  (See also the "Finding the Data" chapter in JPDA)

• Statistical Abstract Section 30,Comparative International Statistics
• NationMaster.com (not a good site)
• OECD
  • OECD in Figures 2003 (note: a pdf file, use column text select to copy)
  • OECD Society at a Glance: OECD Social Indicators - Underlying Data (View long abstract) 22-Dec-2002 provides key-information on 45 indicators covering a wide range of topics, amongst others: Lone-parent families, Divorce rates, foreigners and foreign born populations, (excel format).
  • OECD  Education at a Glance 2003
• IDEA (voter turnout data)
• World Bank data and statistics
• United Nations:  free access is provided to the Millennium Indicators Database, which presents 48 social and economic indicators and related series by country and year since 1985, and the Social Indicators data set.
• Transparency International Bribe Payers Index; Global Corruption
• Arab Human Development Report, 2003 (ISU login required)
• Arab Human Development Report, 2002
  (ISU login required)
• United Nations Social Indicators.   Data on Child-bearing  Education   Health  Illiteracy   Income and economic activity   Youth and elderly populations  Unemployment    Housing  Human settlements Population  Water supply and sanitation
• United Nations Millennium Indicators Database
• Other:
  • Eurostat (European Union)
  • International Statistics Institute (ISI)
  • Inter-American Development Bank
  • Interstate Statistical Committee of the Commonwealth of Independent States (CIS)
  • World Tourism Organization (WTO)

Abortion

·       Gutmacher report (choose two years) (see downloading data note above)

Voter Turnout:

·       Fair Vote (this has a good selection of countries, see downloading data note above)

·       International Institute for Democracy and Electoral Assistance  (turnout data)

Education Achievement 

·       TIMMS data

·       NCES, Education Indicators: An International Perspective (tables) (fewer countries, more data)

·       UNESCO

Women’s Representation

·       Global Database of Quotas for Women

·       Women in national parliaments

Crime and Punishment

·       BJS International statistics

·       Murder Rates (Handgun Murder rates)

·       Incarceration rates

·       Victimization survey (use recent year, replace commas with decimals points!)

Infant Mortality

·       March of Dimes website  (IM rates) (other statistics)

·       CDC data

Other International Data

Step 2:  Transferring data to Excel

select rows, then Data | Sort

Eventually, you will want the data to look something like the spreadsheet on the right (perhaps with just one data column).

If data are in a web page (.html) table, it's best to copy the data using Internet Explorer rather than Netscape.

If data are in a .pdf file, it is sometimes possible to copy individual columns from the .pdf file to paste into Excel.  (hold the cursor just to the left of the top of the column). 

Often when multi-column data are copied from a .pdf file and pasted into Excel, the entire row of data will appear in one column.  Highlight that column and use Data | Text-to-columns.

Sometimes the original data will have strings of decimals points or dashes after the row labels [e.g., Argentina .........].  Use Edit | Replace (replacing the decimal with nothing) to get rid of these -- but be careful to not do this to real decimal points in the data.

Step 3: Sorting the data:

Highlight the data matrix by dragging the mouse over the row numbers as shown in the first spreadsheet image.  Sort on column B, select descending order.

[Later, after you have created the chart you can change the labels at the top and resort the data, the chart will update automatically].

Step 4: Creating the Chart: (See Jon Peltier's instructions on how to use the chart wizard)

highlight matrix, then Insert | Chart

Highlight the data matrix and start the Chart Wizard:

Insert | Chart

As you go through the wizard steps, select “bar chart” and enter a title.  On step 4, select “as a new worksheet” for the location.

[if you miss this last step, right click on the chart and choose “location”]

Using either the Chart toolbar (View | Toolbar | Chart), or by right-clicking on the different elements in the chart.

Format Plot Area: Patterns: Area-none; border-none

Format Data Series: Patterns: border and area (shading); options - change gap width; data labels: show value.

Format Axis: change fonts, scale, number (decimal points).

Format gridlines: to minimize the lines.

Move legend (drag with mouse).

Tip:

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