Home - PSC 4396 - International Studies Senior Seminar

General Links

RDM: Preparing Data with Excel
Baylor Libraries Data Scholar Program
Data and Digital Scholarship Information
Baylor Libraries' Data Scholar Program & Workshops, Data Research Fellows

Exercise

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 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

Exercise

Exercise Steps

University Libraries