Problems loading .csv file into RStudio. EOF within quoted string

Question

when I load this file Chicago_Crimes_2005_to_2007.csv (Link https://www.kaggle.com/currie32/crimes-in-chicago) into RStudio I am always getting an error (Warnmeldung: In scan(file = file, what = what, sep = sep, quote = quote, dec = dec,: EOF in Zeichenkette / English: EOF within quoted string) , and not all obervations are included. Do you know how to solve the problem? With the other 3 files I had no problems. I am using this code:

c2 = read.csv("Chicago_Crimes_2005_to_2007.csv", header = TRUE)

I tried to fix it with this code:

c2 = read.csv("Chicago_Crimes_2005_to_2007.csv", header = TRUE, quote = "", row.names = NULL, stringsAsFactors = FALSE). 

Didnt work out. I tried all answers here in stackoverflow with the same error. Nothing helped. Since 1 week no success. Hope somebody can help me. Working with R in RStudio.

Answer 1

Here you go:

require(tidyverse)
df <- readr::read_csv("Chicago_Crimes_2005_to_2007.csv")

You may decide to clean up the column names as some have spaces in them, if so:

colnames(df) <- c("rowNo",
                   "ID",
                   "Case.Number",
                   "Date",
                   "Block",
                   "IUCR",
                   "Primary.Type",
                   "Description",
                   "Location.Description",
                   "Arrest",
                   "Domestic",
                   "Beat",
                   "District",
                   "Ward",
                   "Community.Area",
                   "FBI.Code",
                   "X.Coordinate",
                   "Y.Coordinate",
                   "Year",
                   "Updated.On",
                   "Latitude",
                   "Longitude",
                   "Location")

Answer 2

Here is a version of a read script that parses the column names from the first row of the file, cleans them with a combination of tidyr::gather() and gsub(), and uses them as the input to read::read_csv(). It then summarizes the Row.Number field to confirm that its maximum value, 6254267 matches the row number from the last row in the file.

library(readr)
library(tidyr)
# read first row and clean column names
colNamesData <- read_csv("./data/Chicago_Crimes_2005_to_2007.csv",col_names=FALSE,n_max=1)
# set NA to Row Number
colNamesData[1,1] <- "Row Number"
# use tidyr::gather() to turn rows into columns
xColNames <- gather(colNamesData)
# use gsub() to replace blanks with periods so data can be used as column names
xColNames$value <- gsub(" ",".",xColNames$value)
# read with readr::read_csv() and set column names to data extracted from first row
# skip first row because it contains bad column names and is missing the first column name 
crimeData <- read_csv("./data/Chicago_Crimes_2005_to_2007.csv",col_names=xColNames$value,skip=1)
# last row in file is row number 6254267
summary(crimeData$Row.Number)

...and the output:

> summary(crimeData$Row.Number)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
      0  235792  471370 1944429 5601310 6254267 
>

NOTE: The file does not read all records correctly because at row 533,719 the record appears to end with a redundant list of variable names.

To correct this, one must either hand edit the data to delete the redundant list of variable names or code around the error.

Interestingly, the row number count restarts at 0 in row 533,720 of the raw data file, which indicates that the people who created this data concatenated multiple files incorrectly to create this data file.