Home - PSC 4396 - International Studies Senior Seminar

General Links

Data Scholars Program
Baylor Libraries Data Scholar Program
Data and Digital Scholarship Information
Baylor Libraries' Data Scholar Program & Workshops, Data Research Fellows

What is a Scatterplot?

Scatterplots:

are graphs containing 2 numeric variables plotted against each other to allow you to see the relationship between these variables.
are often used in conjunction with a correlational analysis, which statistically tells you the strength of the relationship between the 2 variables.
visually display the relationship between 2 variables; they can show you what kind of relationship the variables have. For example, if one variables goes up, and so does the other - the variables have a positive relationship (+/+). Or if one variable goes up but the other goes down - the variables have a negative relationship (+/-).

Exercise #1 - Scatterplots in Excel

Bureau of Labor Statistics: Job Openings and Labor Turnover Survey (2013-2023)

Using data from the U.S. Bureau of Labor Statistics, we will build scatterplots in Microsoft Excel that compare and relationships between time and different labor statistics:

Job Openings
Layoffs and Discharges
Hires
Quits

We will cover how to create and edit scatterplots in Excel.

Exercise #1 Steps

BLS Data Series - Job Statistics.xlsx

We will use four data tables from the Bureau of Labor Statistics. The data tables are in Excel format and can be downloaded to your computer from the link above.

Download data from link above.

Tabs	Variables
Job Openings	Year, Month, Number of job openings
Layoffs and Discharges	Year, Month, Number of people laid-off and/or discharged from their job
Hires	Year, Month, Number of people hired
Quits	Year, Month, Number of people who quit their jobs

Instructions for Creating a Scatterplot

Decide which data you want to create the scatterplot of. You can select 2 columns or rows of data.
Click Insert on the top toolbar.
Then click on the Scatterplot icon in the middle, and click the top left option. This will create a blank scatterplot.
Click inside the blank scatterplot. Click on Select Data at the top.
Now select the 2 columns (or rows) you want to visualize in the scatterplot.
Edit the Legend Entries' name to be the variable name.
Click Ok.
Now you can edit the appearance of the scatterplot - add axis titles, a chart title, change the scaling, change the colors, etc.

Exercise #2 - Concatenate and VLOOKUP in Excel

1995-2018 Study: High Casualty Terrorist Bombings, State Fragility Index, and Democracy Score

Using data from the Center for Systemic Peace, we will build visualizations in Microsoft Excel that compare proportions and relationships between three factors:

Casualty counts from terrorist bombings
State Fragility Index (SFI)
Democracy Score

We will cover the following Excel functionality:

Concatenate (combine) text using the ampersand character (&)
Join columns form multiple tables using VLOOKUP
Restructure tables using PivotTable
Catching errors with IFERROR
Filter data using Filter.

Exercise #2 Steps

We will use three data tables from the Center for Systemic Peace. As the tables provided by the Center for Systemic Peace are served using an insecure connection, the three tables have been pre-downloaded and checked for viruses.

Download data here.

Table	Variable	Metadata Description
High Casualty Terrorist Bombings, 1989-2020	death	Casualties per event
Polity5 Annual Time-Series, 1946-2018	sfi	State fragility index Higher score represents higher fragility
State Fragility Index and Matrix, Time-Series Data, 1995-2018	polity	Provides a single regime score that ranges from +10 (full democracy) to -10 (full autocracy) Score of -66: Interruption Periods: Period of "interruption". Operationally, if a country is occupied by foreign powers during war, terminating the old polity, then reestablishes a polity after foreign occupation ends, Polity codes the intervening years as an interruption until an independent polity is reestablished. Score of -77: Interregnum Periods: indicates periods of “interregnum,” during which there is a complete collapse of central political authority.

Table

Variable

Metadata Description

High Casualty Terrorist Bombings, 1989-2020

death

Casualties per event

Polity5 Annual Time-Series, 1946-2018

sfi

State fragility index
Higher score represents higher fragility

State Fragility Index and Matrix, Time-Series Data, 1995-2018

polity

Provides a single regime score that ranges from +10 (full democracy) to -10 (full autocracy)
Score of -66: Interruption Periods: Period of "interruption". Operationally, if a country is occupied by foreign powers during war, terminating the old polity, then reestablishes a polity after foreign occupation ends, Polity codes the intervening years as an interruption until an independent polity is reestablished.
Score of -77: Interregnum Periods: indicates periods of “interregnum,” during which there is a complete collapse of central political authority.

Purposes of this step: (1) Clear unneeded columns from SFI table to avoid confusion with too many columns. (2) Add the POLITY variable to the SFI table.

Procedure	Visual View
Clean SFI Table Open the State Fragility Index table Delete all columns after sfi
Create a Unique Identifier for SFI Table Add a new column at the start of the table by right-clicking A and selecting Insert. Name the new column UNIQUE In the first cell under the UNIQUE column, enter the following formula: =B2&D2 On the bottom-right corner of the cell, which should now read AFG1995, double-click. This should copy the formula for all rows going down.
Create a Unique Identifier for Polity5 Table Repeat steps above for this table, but the formula should be: =E2&G2
Use VLOOKUP to Add the POLITY Column to the SFI Table In SFI table, on column G, name it polity. Enter the following formula into the first cell in this column: =VLOOKUP(A2,'Polity5 Annual Time-Series'!A:L,12,FALSE) Then copy this formula for this entire column.

Purposes of this step: The High Casualty Terrorist Bombing table organizes records (rows) by event, not by year. We need to restructure the table so that we can sum the casualties per year per country.

Procedure	Visual View
Create a Unique Identifier for Casualty Table =B2&G2
Insert a Pivot Table In the High Casualty Terrorist Bombing table, click Insert and then click PivotTable. In the popup, leave all defaults and Ok.
Setup Pivot Table Drag UNIQUE to the Rows box Drag DEATH to the Values box.

Procedure

Visual View

Create a Unique Identifier for Casualty Table

=B2&G2

Insert a Pivot Table

In the High Casualty Terrorist Bombing table, click Insert and then click PivotTable.
In the popup, leave all defaults and Ok.

Setup Pivot Table

Drag UNIQUE to the Rows box
Drag DEATH to the Values box.

Purpose of this step: Add the SUM OF DEATH variable to the SFI table.

Procedure	Visual View
Use VLOOKUP to Add the SUM OF DEATH Column to the SFI Table In SFI table, on column H, name it casualties. Enter the following formula into the first cell in this column: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Then copy this formula for this entire column.
Convert the #N/A Values to 0 Modify the expression to the following: =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),0)

Procedure

Visual View

Use VLOOKUP to Add the SUM OF DEATH Column to the SFI Table

In SFI table, on column H, name it casualties.
Enter the following formula into the first cell in this column:

=VLOOKUP(A2,Sheet1!A:B,2,FALSE)

Then copy this formula for this entire column.

Convert the #N/A Values to 0

Modify the expression to the following:

=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),0)

Procedure	Visual View
Add a Filter Dropdown to Each Row On the top ribbon, click Sort & Filter and then click Filter. This should result in a dropdown menu at the top of each column.
Filter the YEAR Column to 2001 Click the YEAR dropdown Uncheck Select All Check 2001
Create Map Select the Country field Hold Control (Command on Mac) and select the CASUALTIES column. On the top ribbon, click Insert Maps ad then Filled Map. Experiment with other years!

Procedure

Visual View

Add a Filter Dropdown to Each Row

On the top ribbon, click Sort & Filter and then click Filter.
This should result in a dropdown menu at the top of each column.

Filter the YEAR Column to 2001

Click the YEAR dropdown
Uncheck Select All
Check 2001

Create Map

Select the Country field
Hold Control (Command on Mac) and select the CASUALTIES column.
On the top ribbon, click Insert Maps ad then Filled Map.

Experiment with other years!

Procedure	Visual View
Create Scatterplot Filter the -88, -77, and -66 Values Out of POLITY Column Select the SFI and POLITY columns and click Insert and then Scatterplot
Improve Scatterplot Layout Select the chart and click Chart Design at the top bar Under Quick Layout, click Layout 9 Change the Year!
Difficulties working through this exercise?	Download Here

Procedure

Visual View

Create Scatterplot

Filter the -88, -77, and -66 Values Out of POLITY Column
Select the SFI and POLITY columns and click Insert and then Scatterplot

Improve Scatterplot Layout

Select the chart and click Chart Design at the top bar
Under Quick Layout, click Layout 9

Change the Year!

Difficulties working through this exercise?

Download Here

PSC 4396 - International Studies Senior Seminar

Statistics Help

Research Assistance

Digital Scholarship Assistance

General Links

What is a Scatterplot?

Exercise #1 - Scatterplots in Excel

Exercise #1 Steps

Exercise #2 - Concatenate and VLOOKUP in Excel

Exercise #2 Steps

University Libraries