cleaning FBI crime data

cleaning_data.R

# Loading readxl library
library(readxl)

clean_crime_data <- function(path) {
  # Load the Data
  crime_data <- read_xls(path)
  
  # Assigning colnames
  colnames(crime_data) <- crime_data[3,]
  
  # Create an auxiliary column that will help 
  # to filter the rows
  crime_data['converted_index'] = sapply(crime_data[,1], as.numeric)
  
  # Filter out the rows don't interest us
  crime_data_filter = ( 
   crime_data[!is.na( 
    crime_data[,'converted_index'] 
   ),]
  )
  
  # Apply substring to the year
  crime_data_filter$Year = substr(crime_data_filter$Year, 1,4)
  
  # Filter out columns that do not matter
  crime_data_filter = crime_data_filter[,!grepl( "rate" , names(crime_data_filter))]
  
  # Transform our table into numeric
  pipeline_table <- data.frame(sapply(crime_data_filter, as.numeric))
  
  # Return our pipeline_table
  pipeline_table
}