Global Terrorism Database

README.md

#Exploring the Global Terrorism Database

I would like to work with the Global Terrorism Database (GTD), an open-source database including information on terrorist events around the world from 1970 through 2013 provided by the National Consortium for the Study of Terrorism and Responses to Terrorism at the University of Maryland.

• Get the data
• Use CSVKIT to convert and examine the data
• Extract a subset of data for visualisation purposes
• What does the data tell us?
• Which is the deadliest terror group?
• Terror attacks over time

##Get the data

Download the entire GTD dataset from http://www.start.umd.edu/gtd/contact/. Provide contact information and then select the Entire GTD dataset excel file which should be about 61mb.

##Use CSVKIT to convert and examine the data

To inspect the data use CSVKIT "a suite of utilities for converting to and working with CSV, the king of tabular file formats."

Using Terminal on a mac install CSVKIT with the command:

pip install csvkit

Convert the provided excel file into the csv format. (This might take a while).

in2csv ~/Downloads/globalterrorismdb_0814dist.xlsx > gtd_full.csv

Create a new file containing a list of gtd_csv's headers. Not essential but useful for seeing what we've got to work with.

head -n 1 gtd_full.csv | tr , '\n' | nl -nrz -w3 -s ". " > headers.md

In sequence this command:

1. Grabs the first line of the CSV, in this case the column headers
2. Splits each column header into a new line
3. Adds line numbers followed by a period and a space to make a markdown ordered list
4. Saves this new text into a new markdown file

This could also be achieved using csvcut:

csvcut -n gtd_full.csv > headers.txt

There are 134 columns in this file. From these columns potentially the most interesting are:

line number	Column name	Description
1	eventid	Unique numeric id for each event.
2	iyear	Incident year.
3	imonth	Incident month as number. Zero if the month is unknown.
8	country	Numeric code.
9	country_txt	Country name as text.
10	region	Numeric code.
11	region_txt	Region name as text.
13	city	City name as text.
14	latitude	Latitude
15	longitude	Longitude
29	attacktype1	Type of attack as code.
30	attacktype1_txt	Type of attack as text.
35	targtype1	Type of target/victim as code.
35	targtype1_txt	Type of target/victim as text.
59	gname	Perpetrator group name
98	nkill	Total number of fatalities
100	nkillter	Number of perpetrator fatalities
101	nwound	Total number of injured
103	nwoundte	Number of perpetrators injured

Different attack and target types have numerical categories. These are:

Attack Type:

1. Assassination
2. Hijacking
3. Kidnapping
4. Barricade Incident
5. Bombing/Explosion
6. Unknown
7. Armed Assault
8. Unarmed Assault
9. Facility/Infrastructure Attack

Target type:

1. Business
2. Government (general)
3. Police
4. Military
5. Abortion related
6. Airports & aircraft
7. Government (diplomatic)
8. Educational institution
9. Food or water supply
10. Journalists & media
11. Maritime (includes ports and maritime facilities)
12. Ngo
13. Other
14. Private citizens & property
15. Religious figures/institutions
16. Telecommunication
17. Terrorists/non-state militias
18. Tourists
19. Transportation (other than aviation)
20. Unknown
21. Utilities
22. Violent political parties

##Extract a subset of data for visualisation purposes

Create a new CSV file containing just the year, country and total fatalities column. This saves us from uploading a 100mb file to Github. I can also add additional columns later as needed.

csvcut -c 'iyear, country_txt, nkill' gtd_full.csv > gtd_edit.csv

As well as many columns there are also many, many entries. I can count the number of lines in the CSV using the wc command:

wc -l gtd_full.csv

This tells me there are 125,088 lines in gtd_full.csv. Each line corresponds to one terrorist incident minus one line for the header.

How about just the attacks that are recorded for 2013 (the latest in this dataset):

csvgrep -c iyear -m 2013 gtd_edit.csv | wc -l

11,953 lines. Still a very big number. How about the just the entries that correspond to Great Britain (where I live)? Note that I need to put quotation marks around 'Great Britain' so that the computer knows that it is all one word.

csvgrep -c country_txt -m 'Great Britain' gtd_edit.csv | wc -l

638 much more manageable. I'd still like to examine the data for the whole world eventually but for now let's delete gtd_edit.csv with extract the data Great Britain, United States, Iraq and Pakistan entries and upload them to github.

csvcut -c 'iyear, country_txt, nkill' gtd_full.csv | csvgrep -c country_txt -m 'Great Britain' > gtd_gb.csv
csvcut -c 'iyear, country_txt, nkill' gtd_full.csv | csvgrep -c country_txt -m 'United States' > gtd_usa.csv
csvcut -c 'iyear, country_txt, nkill' gtd_full.csv | csvgrep -c country_txt -m 'Iraq' > gtd_i.csv
csvcut -c 'iyear, country_txt, nkill' gtd_full.csv | csvgrep -c country_txt -m 'Pakistan' > gtd_p.csv

Great Britain and USA because that's where I and most of my colleagues live. Iraq and Pakistan because they are the countries that suffered the most terrorist attacks in the database.

##What does the data tell us?

I now know that there are 637 terrorist incidents in the Global Terrorism Data base listed as taking place in Great Britain.

This smaller file is much more manageable and a quick visual scan tells me that the vast majority of incidents hand no fatalities, neither victims nor perpetrators. However there was one incident in 1988 in which 270 people died. Undoubtedly the Lockerbie bombing.

I can also see that there are some entries with no number recorded for the fatality count. Examining the GTD documentation tells me:

"Where there is evidence of fatalities, but a figure is not reported or it is too vague to be of use, this field remains blank".

I also note that some fatalities are listed as fractional values:

When several cases are linked together, sources sometimes provide a cumulative fatality total for all of the events rather than fatality figures for each incident. In such cases, the preservation of statistical accuracy is achieved by evenly distributing fatalities across the linked incidents. Depending on the number of linked events and the cumulative total of fatalities, it is possible for fractions to appear in the fatality field for individual events.

##Which is the deadliest terror group?

In order to compare the different terrorist groups I'll extract a new csv containing the names of the groups, the numbers of people killed or wounded in their attacks and the year:

csvcut -c 'iyear, gname, nkill, nwound' gtd_full.csv > gtd_gname.csv

I can now use the python library pandas http://pandas.pydata.org/ to create a new csv file containing the total number of attacks and fatalities for each group:

python data-wrangler.py

Check out the file for comments

There are 3,065 different groups.

wc -l gtd_gname_count.csv

• 3066 gtd_gname_count.csv

I'll put quotes around all of the entries to make sure the group names are interpreted as strings:

csvformat -U 2 gtd_gname_count.csv > temp.csv && mv temp.csv gtd_gname_count.csv

Let's see which groups carried out the most attacks:

csvsort -c "count" -r gtd_gname_count.csv | head | csvlook

gname	count	sum(nkill)
Unknown	56905	86758.87
Shining Path (SL)	4533	11581.0
Farabundo Marti National Liberation Front (FMLN)	3351	8065.0
Taliban	3204	10086.0
Irish Republican Army (IRA)	2673	1809.0
Revolutionary Armed Forces of Colombia (FARC)	2228	5513.0
Basque Fatherland and Freedom (ETA)	2027	818.0
New People's Army (NPA)	1629	3703.0
Liberation Tigers of Tamil Eelam (LTTE)	1606	10964.0

And let's see which was the most deadly:

csvsort -c "sum(nkill)" -r gtd_gname_count.csv | head | csvlook

gname	count	sum(nkill)
Unknown	56905	86758.87
Shining Path (SL)	4533	11581.0
Liberation Tigers of Tamil Eelam (LTTE)	1606	10964.0
Taliban	3204	10086.0
Farabundo Marti National Liberation Front (FMLN)	3351	8065.0
Nicaraguan Democratic Force (FDN)	895	6662.0
Revolutionary Armed Forces of Colombia (FARC)	2228	5513.0
Tehrik-i-Taliban Pakistan (TTP)	872	4115.0
Al-Qa`ida in Iraq	633	4101.8

The entry for Unknown is not relevant so I'll remove it:

csvgrep -c "gname" -m "Unknow" -i gtd_gname_count.csv > temp.csv && mv temp.csv gtd_gname_count.csv

Terror attacks over time

Extract a new file to show the number of attacks per-country for each year with the aim of building a stacked area chart. The new file should contain just the event ID, year and country name columns.

csvcut -c 'eventid, iyear, country_txt' gtd_full.csv > gtd_world_historic.csv

Use the IPython notbook Attacks per year per country.ipynb to create a new file gtd-country-year.csv containing the sum of attacks per country per year. The notebook can be viewed via nbviewer.

The resulting csv file needs a bit of cleaning up. Remove the useless opening line

cat gtd_country_year.csv | awk '!((NR==1)||(NR==3))' > temp.csv

Change the column header iyear to country.

cat temp.csv | sed 's/iyear/country/g' > gtd_country_year.csv
rm temp.csv

data-wrangler.py

import pandas
import pandasql

def aggregate_query(path_to_csv, path_to_new_csv):

    # load in the csv file as a pandas DataFrame
    terror_data = pandas.read_csv(path_to_csv, encoding='utf-8')
    # Remove any spaces or capital letters from the column names
    terror_data.rename(columns = lambda x: x.replace(' ', '_').lower(), inplace=True)

    # Count the number of entries for each group
    terror_attacks = terror_data['gname'].value_counts()
    # Load the returned pandas Series into a DataFrame
    terror_attacks_df = pandas.DataFrame(terror_attacks)
    # Name the values column 'count' and the index 'gname'
    terror_attacks_df.columns = ['count']
    terror_attacks_df.index.name = 'gname'

    # Prepare a SQL query to sum the fatalities for each group
    q = """
    SELECT
        gname, SUM(nkill)
    FROM
        terror_data
    GROUP BY
        gname
    """

    # Run the SQL query and save it in a new DataFrame
    terror_kills_df = pandasql.sqldf(q.lower(), locals())
    # Make a new DataFrame in which the 'gname' column is the index to match terror_attacks_df
    terror_kills_df2 = terror_kills_df.set_index('gname')

    # Now that the two DataFrames match they can easily be merged
    merged = terror_attacks_df.join(terror_kills_df2)

    # Print the new merged data frame out as a new csv file
    merged.to_csv(path_to_new_csv, encoding='utf-8')

    return


if __name__ == "__main__":
    path_to_csv = "gtd_gname.csv"
    path_to_new_csv = "gtd_gname_count.csv"
    aggregate_query(path_to_csv, path_to_new_csv)

gtd-attacks-per-year-per-country.ipynb

gtd_country_year.csv

gtd_gb.csv

gtd_gname_count.csv

gtd_i.csv

gtd_p.csv

gtd_usa.csv

headers.md

1. eventid
2. iyear
3. imonth
4. iday
5. approxdate
6. extended
7. resolution
8. country
9. country_txt
10. region
11. region_txt
12. provstate
13. city
14. latitude
15. longitude
16. specificity
17. vicinity
18. location
19. summary
20. crit1
21. crit2
22. crit3
23. doubtterr
24. alternative
25. alternative_txt
26. multiple
27. success
28. suicide
29. attacktype1
30. attacktype1_txt
31. attacktype2
32. attacktype2_txt
33. attacktype3
34. attacktype3_txt
35. targtype1
36. targtype1_txt
37. targsubtype1
38. targsubtype1_txt
39. corp1
40. target1
41. natlty1
42. natlty1_txt
43. targtype2
44. targtype2_txt
45. targsubtype2
46. targsubtype2_txt
47. corp2
48. target2
49. natlty2
50. natlty2_txt
51. targtype3
52. targtype3_txt
53. targsubtype3
54. targsubtype3_txt
55. corp3
56. target3
57. natlty3
58. natlty3_txt
59. gname
60. gsubname
61. gname2
62. gsubname2
63. gname3
64. gsubname3
65. motive
66. guncertain1
67. guncertain2
68. guncertain3
69. nperps
70. nperpcap
71. claimed
72. claimmode
73. claimmode_txt
74. claim2
75. claimmode2
76. claimmode2_txt
77. claim3
78. claimmode3
79. claimmode3_txt
80. compclaim
81. weaptype1
82. weaptype1_txt
83. weapsubtype1
84. weapsubtype1_txt
85. weaptype2
86. weaptype2_txt
87. weapsubtype2
88. weapsubtype2_txt
89. weaptype3
90. weaptype3_txt
91. weapsubtype3
92. weapsubtype3_txt
93. weaptype4
94. weaptype4_txt
95. weapsubtype4
96. weapsubtype4_txt
97. weapdetail
98. nkill
99. nkillus
100. nkillter
101. nwound
102. nwoundus
103. nwoundte
104. property
105. propextent
106. propextent_txt
107. propvalue
108. propcomment
109. ishostkid
110. nhostkid
111. nhostkidus
112. nhours
113. ndays
114. divert
115. kidhijcountry
116. ransom
117. ransomamt
118. ransomamtus
119. ransompaid
120. ransompaidus
121. ransomnote
122. hostkidoutcome
123. hostkidoutcome_txt
124. nreleased
125. addnotes
126. scite1
127. scite2
128. scite3
129. dbsource
130. INT_LOG
131. INT_IDEO
132. INT_MISC
133. INT_ANY
134. related