{"id":7672,"date":"2018-08-27T17:18:45","date_gmt":"2018-08-27T15:18:45","guid":{"rendered":"http:\/\/ipisresearch.be\/?page_id=7672"},"modified":"2024-03-20T17:49:35","modified_gmt":"2024-03-20T16:49:35","slug":"open-data-getting-started-using-excel","status":"publish","type":"page","link":"https:\/\/ipisresearch.be\/nl\/home\/maps-data\/open-data\/open-data-getting-started-using-excel\/","title":{"rendered":"Open data – Getting started using Excel"},"content":{"rendered":"

Since 2009, the International Peace Information Service\u00a0manages a database on mining site visits in eastern DR Congo. Since January 2017, IPIS publishes the data it has collected over the last decade on its\u00a0Open Data page<\/a>. This tutorial explains how to get started with the IPIS Open Data if you are using Microsoft Excel.<\/p>\n

Other documentation to this data includes an Open Data FAQ & Dictionary page<\/a> to help\u00a0you understand the different columns and values present in our data, and an Open Data Tutorial<\/a> to help advanced data analysts getting started using our Open Data for their own research using the statistical programming language R.<\/p>\n

If you have questions or feedback on this page or our data, don’t hesitate to contact us<\/a><\/p>\n

Loading the data in Excel<\/strong><\/h4>\n

First, download the Open Data from our Open Data page<\/a>\u00a0as a Comma Separated Value (CSV) file, with a\u00a0.csv<\/em>\u00a0extension. Sadly, it is not possible to download the data directly as an Excel Spreadsheet file with a .xls<\/em> or .xlsx<\/em> extension, but following explanation below will allow you to open the CSV file using Excel.<\/p>\n

Open Excel and click\u00a0Data > From text<\/em>. Then localise the .csv<\/em> file you downloaded and confirm.<\/p>\n

\"\"<\/a><\/p>\n

Excel will start its\u00a0Text Import Wizard<\/em> and ask you how it should interpret this file. First, check if it is interpreted as a Delimited<\/em> file, and set the origin to Unicode (UTF-8) – t<\/em>his way all special characters are recognised. Then confirm.<\/p>\n

\"\"<\/a><\/p>\n

Now, set the delimiting character to Comma.<\/em> The preview field below should now be showing the file’s content in columns.<\/p>\n

\"\"<\/a><\/p>\n

On the next screen you are able to select the data format for each column. General<\/em> should work well as a default, except\u00a0on some systems for columns with decimal values, such as the\u00a0longitude<\/em> and latitude<\/em> column. Scroll to the right until you find these columns, click on them (they are selected in black), and select the data format\u00a0Text<\/em> (even though they actually contain numbers). The top of the column should now say Text <\/em>in stead of General<\/em>.<\/p>\n

\"\"<\/a><\/p>\n

Using the Advanced<\/em> button, set the decimal separator to . (a dot) and leave the box for the thousands separator empty.<\/p>\n

\"\"<\/a><\/p>\n

Confirm to load your data from the first cell A1<\/em> onwards.<\/p>\n

\"\"<\/a><\/p>\n

The result should be a nice table with all data in it. Check if all columns were correctly separated, if special characters were recognised and if columns containing decimal values were interpreted correctly. If so, you can now save this file as a\u00a0.xls<\/em> or .xlsx<\/em> file (e.g. data.xlsx<\/em>), such that it will be opened using Excel when you work on it later or share it.<\/p>\n

\"\"<\/a><\/p>\n

Creating a dataset containing only IPIS visits, and the most recent visit for each mine<\/h4>\n

The Open Dataset contains one line per research visit<\/strong> that was done to a mining site. Sites that were visited multiple times are thus described in multiple lines – one line for each visit.\u00a0Each visit is characterized by multiple columns. More information on these columns can be found in our\u00a0Open Data FAQ & Dictionary page<\/a>. There, you can learn that mines are uniquely identified using the pcode <\/em>column<\/strong>.<\/p>\n

Some quick data exploration will reveal that there are three main categories of visits<\/strong>:\u00a0visits by IPIS<\/em>\u00a0(and its partners),\u00a0visits by qualification mission<\/em>\u00a0and extra\u00a0iTSCi status info<\/em>.<\/p>\n