In this post, I am cleaning data available in very messy .txt file and saving clean data as .csv file in local directory for the future use. This post is partially inspired by a request from a student to help in cleaning their research project data.To accomplish objective metioned above, I am using .txt file from 2022 Release – North American Breeding Bird Survey Dataset (1996-2021). This data was published on 2022-07-05 and accessed on May 19, 2023. You can fild “SpeciesList.txt” data by scrolling through the midway of the webpage. Open this file in new tab by clicking file icon in “view” column to see the data structure.
First few lines in text file provide information about data followed by variable names in a single row, dotted lines in next row, and rows of data underneath dotted line. There is white space before variable “Seq” in each row. Data points of variables such as “English_Common_Name”, “French_Common_Name” and Spanish_Common_Name have more than one words seperated by spaces. Our goal is to remove decriptions of data from the top of .txt file, remove white space infront of “Seq”, enter variables as seperate column, name variables appropriately, and remove or keep white spaces whenever necessary.
Before starting coding, download “SpeciesList.txt” file in your computer in download folder from above website. Line 1 starting with “#” is R comment which is often used as notes for code writer describing code briefly and frequently used throughout this post. This is a good coding habit because codes started getting messy after writing several lines of code without such notes. Line 2 keeps R environment clean. I recommend keeping this code at the top of each R code file before writing any other codes. Next few lines of code sets working directory, confirm working directory is properly set, provides data information, load necessary package, and import “SpeciesList.txt” file. In this example, I set working directory to “Downloads” folder. Code “file.choose()” opens pop-up window (Window OS only) and allows to navigate to data folder and choose necessary data file.
# House Keeping:
rm(list = ls())
setwd("C:\\Users\\username\\Downloads")#WOS
#setwd("/Users/username/Downloads") #MAC
get.wd()# Check working directory.
# Data:
# Data Source (Accessed: May 19, 2023):
# 2022 Release - North American Breeding Bird Survey Dataset (1966-2021)
# https://www.sciencebase.gov/catalog/item/625f151ed34e85fa62b7f926
# Packages:
library(readr, warn.conflicts = FALSE, quietly = TRUE)
# Import Data File:
species = file.choose() # Pop-up Window
Next step is to clean data using “readr” package. The function read_fwf removes first few lines of information about the data, import clean data, removes or retain spaces in each variables as necessary, and save each variable as a column of bird dataframe. The information about the data is removed by skipping first 14 lines in .txt file that was imported earlier and each variable was seperated and named using fwf_widths(). Numbers 7, 6, and 51s represent number of columns/characters each variable holds in .txt file. For example, First 7 characters in 15th line and beyond in .txt file were assigned as Seq, next 6 characters in 15th line and beyond in .txt file were assigned as “AOU”, next 51 characters in 15th line and beyond .txt file were assigned as “EnglishName”. In this way we can manually input how many characters belongs to each variable (9 variables in this file) which remove or maintain spaces as necessary for all variables in .txt file. Each variables were manually named using their respective names such as “EnglishName”, “FrenchName”, “SpanishName” and so on. There are 9 names and 9 numbers for 9 variables inside fwf_widths(). Verify data is properly cleaned using heads() or View().
# Data cleaning:
bird = read_fwf(file = species,
skip = 14,
skip_empty_rows = TRUE,
show_col_types = FALSE,
trim_ws = TRUE,
fwf_widths(c(7, 6, 51, 51, 51, 51, 51, 51, 51),
c("Seq", "AOU",
"EnglishName","FrenchName", "SpanishName",
"ORDER", "Family", "Genus", "Species")))
head(bird)
View(bird)
Clean data can be exported into working directory using write.csv() and .csv datafile name can be defined using file = “bird.csv”. Other parameters can be modified as necessary. Verify that you properly saved your cleaned data in working directory.
# Save bird data as CSV file:
write.csv(bird,
file = "bird.csv",
quote = TRUE,
eol = "\r",
na = "NA",
row.names = FALSE,
fileEncoding = "")
I hope this is helpful. You should be able to replicate the entire process without error if you set working directory, saved downloaded data in working directory, and imported saved .txt file in R properly. Feel free to provide comment and share your thoughts about this code and post. Let me know what you want to learn in the future.
