Accessing and Analyzing Data

Download the dataset

A common access point for large datasets is an online database. Often, an online database will provide a search interface that allows users to filter a dataset and access or download only the data they need for their research. 

The dataset you will download from the FAA Wildlife Strike Database is contained in a common tabular data file type known as a csv file, a type of file consisting of rows and columns of data separated by commas (comma separated values = csv).

1. Navigate to the FAA Wildlife Strike Database.

2. Click the “Search the Database” button to access the database search form.

3. Select “NC” from the “State” dropdown menu in the “Search Criteria” section.

4. Make sure the “Allow compression to .zip file” button is checked.

5. Click the “Submit” button, you will see a table containing the first 10 records of the dataset when the search is complete.

6. Click the “Export to Excel” button.

7. Navigate to the folder where this file was downloaded, the file should have a name like “Wildlife_Export_1272020.zip” where the numbers indicate the date on which the data was downloaded.

8. Extract the file from the downloaded zipped folder (see zip files for more information on this type of file).

On a Mac, double click on the zipped folder. On Windows, right-click the zipped folder and select "Extract All"

9. The extracted file is a csv file (what is a csv file?) where the numbers indicate the date on which the data was downloaded.

Download the data dictionary

A data dictionary explains the contents of a dataset. For the dataset you just downloaded, the data dictionary will provide full definitions of abbreviations used in field names (column headers) and coded values used in field observations (the value in a specific cell). 

1. In the “Search Criteria” section on the same page as the database search form find the “Note” text providing information about the dataset you just generated.

2. In the last sentence of that text click the “here” link to download the data dictionary for this dataset as a pdf file (Alternatively, you can download the data dictionary using this direct link to the data dictionary pdf file).

Learn more about data dictionaries and the importance of describing data on the Smithsonian Libraries’ Describing Your Data: Data Dictionaries page.

Analysis 1: How many unique species are there in the FAA Wildlife Strike dataset?

Summarize your data using pivot tables: 

1. Select the entire “SPECIES” column (Column CE)

2. Navigate to the menu bar > select “Data” > select “Pivot table.”

3. Select “Create” in the “Create pivot table” pop-up. (“Data range” refers to the Column you’ve selected)

4. Rename the new worksheet by double-clicking in the tab labelled “Pivot Table 1” and replacing it with “unique_species.” 

5. Open the Pivot Table editor on the right by clicking on any of the shaded cells.

6. In the Pivot Table editor, select “Add” next to “Rows” and select the header that you are summarizing (in this example, select “SPECIES”).

7. In the Pivot Table editor, select “Add” next to “Values” and select “SPECIES.” Make sure “Summarize by” has “COUNTA” selected. COUNTA returns the number of values (including letters, characters, and numbers) in a dataset, whereas COUNT returns only the number of NUMERIC values.

8. Navigate back to Rows, and under “Order,” select “Descending”, and under “Sort by,” select COUNTA of SPECIES. This will sort the table by the number of unique species, from greatest to least, instead of alphabetically. 

Calculate the number of unique species:

1. Select a cell a few columns after your pivot table. Insert the following calculation: 

=COUNTUNIQUE(Sheet1!CE:CE)

This calculation will return the number “204.” It is referencing column CE in the worksheet “Sheet1” and calculating the unique entries. Please note that it is counting the header “SPECIES” as part of this, so the accurate number of unique species is “203.”

Analysis 2: How many incidents are there per year?

Summarize the number of incidents per year by creating a pivot table. You will be repeating the steps above, but using the “INCIDENT_YEAR” column instead. As a reminder, the steps are: 

1. 1. Select the entire “INCIDENT_YEAR” column by selecting column D.

   2. Navigate to the menu bar > select “Data” > select “Pivot table.”

   3. Select “Create” in the “Create pivot table” pop-up. 

   4. Rename the new worksheet by double-clicking in the tab labelled “Pivot Table 1” and replacing it with “incident_year.”  

   5. Open the Pivot Table editor on the right by clicking on any of the shaded cells.

   6. In the Pivot Table editor, select “Add” next to “Rows” and select “INCIDENT_YEAR,” then select “Add” next to “Values” and select “INCIDENT_YEAR.” Make sure “Summarize by” has “COUNTA” selected. 

   7. Confirm that below “Rows”, the settings for “Order,” are “Descending”, and “Sort by” are “INCIDENT_YEAR.” 

Create a horizontal bar chart to visualize incidents per year:

1. 8. Select the entire pivot table, except for the last two rows. Notice that the last row has a grand total and the row before that is empty.

   9. Navigate to the menu bar > select “Insert” > select “Chart.”

   10. Use the ‘Chart Editor’ on the right to customize your chart:

       1. 1. Under the “Setup” tab, navigate to “Chart Type” and scroll down to “Bar” and select the first horizontal bar chart. 

          2. Under the “Customize” tab, you can select “Chart & axis titles” to change the labeling. You can also double-click into each label on the chart to edit it there.

Analysis 3: Compare wildlife strikes between flying animals and land animals

This analysis will use a regular expression to find species IDs that match land animals. Regular expressions ("regex" for short) are used in many data analysis applications, including Google Sheets and Python. They use specialized syntax to help you match patterns, or find other strings or sets of strings. Regex can be tricky, but a useful tool to learn about when you need to do more advanced data manipulation. To learn more about regex: https://regexone.com/

In this example, we will look at a column of species IDs. International Civil Aviation Organization (ICAO) codes tell us that land animals will have an ID that begins with 1 or 2, so we will write a regular expression that finds any string of characters that begins with 1 or 2:

1. Create a new column “IS_LAND” to identify whether the animal is a land or flying animal. To create a new column, navigate to the “SPECIES_ID” column, right-click in the cell, and select “Insert 1 right.” This will create a blank column on the right-side of “SPECIES_ID.” 

2. Rename the column “IS_LAND.”

3. Use a regular expression to evaluate whether the species is a land or flying animal. According to the International Civil Aviation Organization (ICAO) codes, animals have a SPECIES_ID that begins with the number 1 or the number 2. To evaluate whether the SPECIES_ID begins with 1 or 2 enter the following regular expression in the first cell under the new column “IS_LAND”:

=regexmatch(CD2, "^[1-2]")

"regexmatch" is the formula that indicates we will be using a regular expression. In Regex, ^ indicates the beginning of a string and [1-2] indicates a range of numbers.  

4. To perform this calculation on the rest of the column, double click on the right-bottom corner of the cell. It will populate the rest of the column with the calculation.

5. Use a pivot table to compare the number of land animals versus flying animals. If the cell says True, it is a land animal. If it says False, it is a flying animal. You will be repeating the steps above, but using the “IS_LAND” column.