Data Cleaning from Global Terrorism Database (GTD)

by Maria Jane Poncardas, 06/14/19

Data preprocessing:

The data was collected from an open-source database from the GLOBAL TERRORISM DATABASE website (https://www.start.umd.edu/gtd/) which has annual updates of worldwide terrorist events from 1970 to 2017. The data file was obtained in the website by hovering over “USING GTD” option and then select the “Download GTD”. On the webpage, the “Action” field has a drop-down menu which contains general inquiries and acquisition of GTD file. It contains comprehensive information, news sources and among others. The researchers only selected the most relevant information for the study such as:



  1. Date

  2. City and Province

  3. Latitude and longitude

  4. Attack Type:

    1. Bombing/Explosion

    2. Armed Assault

    3. Hostage Taking (Kidnapping)

    4. Assassination

    5. Facility/Infrastructure Attack

    6. Hijacking

    7. Hostage Taking (Barricade Incident)

    8. Unarmed Assault

  5. Target type:

    1. Military

    2. Private Citizens & Property

    3. Government

    4. Business

    5. Police

    6. Transportation

    7. Utilities

    8. Educational Institution

    9. Religious Figures/Institutions

    10. Journalists & Media

    11. Maritime

    12. Telecommunication

    13. Terrorists/Non-State Militia

    14. NGO

    15. Food or Water Supply

    16. Tourists

    17. Airports and Aircraft

  6. Weapon details:

    1. Firearms

    2. Explosives

    3. Incendiary

    4. Melee

    5. Chemical

    6. Sabotage equipment

  7. Doubted terrorism

  8. Suicide

  9. Infiltrator group name

  10. Number of dead victims

  11. Number of injured



DATA CLEANING:


  1. The researchers filtered out data from non-Mindanao provinces or states such as Luzon, Visayas, and foreign localities.

  2. Using reverse geocoder module in python, the latitude and longitude was utilized to augment information about its localities, i.e., city/municipality names and provinces.

import reverse_geocoder as rg
coordinates = list(mindanao_terrorism['latitude_longitude'])
results = rg.search(coordinates)
city_municipality = pd.DataFrame(results)


  1. The augmented cities and municipalities in a data frame was then concatenated to the GTD file to retain each terrorist activity information.

  2. The Philippine shapefile was used as reference to extract capitalization errors, misspelled words, and typos of the cities and municipalities produced from the reverse geocoder.

(Cities not in shapefile indicates erroneous information generated)


gtd_cities = list(concat['city_municipality'].sort_values().unique())
shape_data = list(phil_shapefile['City'].sort_values().unique())
cities_not_in_shapefile = list(set(gtd_cities) - set(shape_data))
cities_not_in_shapefile.sort()

for i in cities_not_in_shapefile:
    lati = list(concat[concat['city_municipality']==i]['latitude'])[0]
    long = list(concat[concat['city_municipality']==i]['longitude'])[0]
   
    print(i,lati,long)

The for loop returns the incorrect cities/municipalities and its coordinates.


  1. Each incorrect cities/municipalities are manually verified through Google maps and Wikipedia since most of these municipalities and cities have been renamed or was carved out from another city/municipality

  2. Once verified, all the data that contains common erroneous city/municipality name will be automatically corrected through this code:

city_toedit = "Iligan"
city_correct = "Iligan City"

error_city = concat[concat['name (rg)'] == city_toedit]['name (rg)'].to_dict() #assign series into a dictionary such that it will assign keys() as their index numbers and values() as their incorrect province name                                                                        #showing only dictionary keys

for i in error_city.keys(): #for loop for the editing
    concat.at[i, 'name (rg)'] = city_correct
    print('changed from '+ city_toedit + ' to '+ city_correct)

Comments

Post a Comment

Popular posts from this blog

Using QGIS to convert .shp file to .geojson (GADM)

Image
by Maria Jane S. Poncardas, May 16, 2019      At the moment of writing, there is no direct method on how to utilize raw file from Database of Global Administrative Areas (GADM) into Python script to create charts in Bokeh package. GADM provides maps and spatial data for all countries and their sub-divisions. The goal here is to extract administrative areas in Mindanao (Southern Philippines) as it will be used for spatial and temporal analyses on terrorism (with which utilizes Bokeh for visualization). Sure, there are plenty of workarounds (such as using Plotly or simply Seaborn), but this article focuses on such as it deals with maps. I hope a few portion of this article may help you. CONVERT .SHP TO .GEOJSON USING QGIS Download shapefile from Database of Global Administrative Areas https://gadm.org/download_country_v3.html Extract downloaded file by right-clicking on the file then from the dropdown menu, select “Extract files to <filename>\ ” In the case of Ph...
Read more

LSTM pseudocode

Image
by Maria Jane Poncardas, July 19, 2019 Forecasting models are generated based on the training of historical data of six (6) <confidential hydroelectric company> plants, which is from January 1, 2011 to May 4, 2014. The author used Python Jupyter notebook to construct training codes for RNN-LSTM forecasting method. To perform such training, missing values need to be treated hence imputation methods were employed such as Kalman filtering, per plant’s maximum rated capacity and the triple exponential smoothing. The following are the steps resembling information of the code for the forecasting: PREPROCESSING : Import necessary packages and modules for the training: pandas - is used to extract power generated out from the consolidated dataset of excel file Tensorflow’s keras is utilized to build the architecture of RNN and is also used to initiate the model sklearn is for transforming features of data by scaling to a given range, in this case, between zero and one. The dataset being r...
Read more