Read CSV in R

It is usual to find datasets in CSV (comma separated values) format. This type of data storage is a lightweight solution for the most use cases. In this tutorial you will learn how to read a CSV in R to work with.

Quick answer

To read a CSV file in R use read.csv("file.csv") and assign the result to a variable. If the file uses semicolons as separator and commas as decimal mark, use read.csv2 instead.

name,age,city,score
Ana,28,Madrid,7.5
Bob,35,London,8.1
Carla,41,Paris,6.9
David,23,Berlin,9.2

df <- read.csv("my_file.csv")   # Comma separated, dot as decimal mark
# df <- read.csv2("my_file.csv") # Semicolon separated, comma as decimal mark

head(df)

   name age   city score
1   Ana  28 Madrid   7.5
2   Bob  35 London   8.1
3 Carla  41  Paris   6.9
4 David  23 Berlin   9.2

The file must be in your working directory or you must write its full path. For big files, readr::read_csv and data.table::fread are much faster.

How to read a CSV file in R?

In this section you will learn how to import a CSV file in R with the read.csv and read.csv2 functions. You can see the basic syntax of the functions with the most common arguments in the following code block. For additional details remember to type ?read.csv or ?read.csv2.

# Comma as separator and dot as decimal point by default
read.csv(file,                 # File name or full path of the file
         header = TRUE,        # Whether to read the header or not
         sep = ",",            # Separator of the values
         quote = "\"",         # Quoting character
         dec = ".",            # Decimal point
         fill = TRUE,          # Whether to fill blanks or not
         comment.char = "",    # Character of the comments or empty string 
         encoding = "unknown", # Encoding of the file
         ...)                  # Additional arguments

# Semicolon as separator and comma as decimal point by default
read.csv2(file, header = TRUE, sep = ";", quote = "\"", dec = ",",
          fill = TRUE, comment.char = "", encoding = "unknown", ...)

You may have noticed that the only differences between the functions are the separator of the values and the decimal separator, because in some countries they use commas as decimal separator.

In the second case, in order to create CSV files the semicolon is needed if some numbers are decimals. As you may find datasets with both characteristics, you can use the corresponding function instead of changing the parameters of the arguments.

The following table summarizes the three main default arguments:

In order to load a CSV file in R with the default arguments, you can pass the file as string to the corresponding function. The output will be of class data.frame.

read.csv("my_file.csv")

If you just execute the previous code you will print the data frame but it will not be stored in memory, since you have not assigned it to any variable. If you save it in a variable called my_file, you will be able to access the variables or the data you want.

my_file <- read.csv("my_file.csv")

CSV file header

By default, the functions read the header of the files. In case you want to read the CSV without header you will need to set to FALSE the header argument.

read.csv("my_file.csv", header = FALSE)

CSV encoding

A common issue arises with bad encoding of the files. In case you are reading a file with rare characters you maybe need to specify the encoding. Setting the encoding to UTF-8 tends to solve the most of these problems.

read.csv("my_file.csv", encoding = "UTF-8")

The encoding argument only marks the strings as UTF-8. If accents or special characters still look wrong, use fileEncoding instead, which re-encodes the file while reading it. Files with accents saved on Windows are often encoded as "latin1".

read.csv("my_file.csv", fileEncoding = "UTF-8")
read.csv("my_file.csv", fileEncoding = "latin1")

The na.strings argument

Sometimes the files contain some character string that represents missing or omitted values. You will find more information about how missing values are handled in the source of the data set you are working with. In order to solve this issue you can convert them to NA values with the na.strings argument, specifying the character string that represents the missing value.

Consider, for instance, that in your CSV file the -9999 values represent missing data. In this scenario you could type:

read.csv("my_file.csv", na.strings = "-9999") 

Moreover, in case the file contains multiple na.strings you can specify all inside a vector.

read.csv("my_file.csv", na.strings = c("-9999" , "Na" )) 

However, if you need to remove NA values or the value specified as it after importing you will need to use the corresponding function depending on your data. The most common function to remove missing values is na.omit.

The stringsAsFactors argument

The stringsAsFactors argument of the function will transform the string (character) columns of the dataset into factors. Since R 4.0.0 its default value is FALSE, so text columns are read as character unless you set it to TRUE.

df <- read.csv("my_file.csv", stringsAsFactors = TRUE)
str(df)

'data.frame':	4 obs. of  4 variables:
 $ name : Factor w/ 4 levels "Ana","Bob","Carla",..: 1 2 3 4
 $ age  : int  28 35 41 23
 $ city : Factor w/ 4 levels "Berlin","London",..: 3 2 4 1
 $ score: num  7.5 8.1 6.9 9.2

Read only some rows or columns: nrows, skip and colClasses

You don’t need to load the whole file if you only need a part of it. The nrows argument sets the maximum number of rows to read, which is useful to take a quick look at a big file:

read.csv("my_file.csv", nrows = 2)

  name age   city score
1  Ana  28 Madrid   7.5
2  Bob  35 London   8.1

The skip argument skips the first lines of the file, for instance notes written before the data. Note that the header is a line too, so if you skip it you will need to set header = FALSE (and optionally pass the names with col.names):

read.csv("my_file.csv", skip = 2, header = FALSE)

     V1 V2     V3  V4
1   Bob 35 London 8.1
2 Carla 41  Paris 6.9
3 David 23 Berlin 9.2

Finally, colClasses lets you set the class of each column. Use "NULL" to drop a column while reading, which saves memory with large files:

read.csv("my_file.csv",
         colClasses = c("character", "integer", "NULL", "numeric"))

   name age score
1   Ana  28   7.5
2   Bob  35   8.1
3 Carla  41   6.9
4 David  23   9.2

If you prefer to select the columns by name, fread has the select argument and read_csv the col_select argument, as you will see below.

Numbers read as character: decimal commas and thousands separators

A very common issue is a numeric column that R reads as character. This happens when the format of the numbers doesn’t match the sep and dec arguments. Consider the following file, which uses semicolons as separator and commas as decimal mark:

product;price;units
A;12,50;3
B;980,75;12
C;7,00;7

If you read it with read.csv all the data ends in a single column, because there are no commas separating the values. Use read.csv2 or set sep = ";" and dec = ",":

df <- read.csv2("european.csv")
# Equivalent to:
# df <- read.csv("european.csv", sep = ";", dec = ",")

str(df)

'data.frame':	3 obs. of  3 variables:
 $ product: chr  "A" "B" "C"
 $ price  : num  12.5 980.8 7
 $ units  : int  3 12 7

Thousands separators are another source of problems, as read.csv has no argument for them. If the file contains values such as "1,200", the column will be read as character:

id,amount
1,"1,200"
2,"15,000"
3,"850"

df <- read.csv("thousands.csv")
str(df)

'data.frame':	3 obs. of  2 variables:
 $ id    : int  1 2 3
 $ amount: chr  "1,200" "15,000" "850"

The solution is to remove the separator with gsub and convert the column to numeric:

df$amount <- as.numeric(gsub(",", "", df$amount))
str(df)

'data.frame':	3 obs. of  2 variables:
 $ id    : int  1 2 3
 $ amount: num  1200 15000 850

The read_csv function of readr handles this case automatically, and you can set any combination of marks with its locale argument, for instance locale = locale(decimal_mark = ",", grouping_mark = ".").

Choose the CSV file interactively with file.choose

If you don’t want to type the path, you can use file.choose() inside read.csv. It will open a window where you can browse and select the file:

df <- read.csv(file.choose())

This is handy for quick analyses, but avoid it in scripts that you will run again, as you will need to select the file every time and your code won’t be reproducible.

Read a CSV from a URL

The file argument also accepts a URL, so you can import a CSV file from the internet directly, without downloading it first:

url <- "https://raw.githubusercontent.com/tidyverse/readr/main/inst/extdata/mtcars.csv"

df <- read.csv(url)
head(df, 3)

   mpg cyl disp  hp drat    wt  qsec vs am gear carb
1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    4

read_csv and fread can read URLs in the same way. If you use GitHub, remember to use the “raw” link of the file and not the link of the web page.

read.csv vs read_csv (readr) vs fread (data.table)

Base R read.csv is enough for small and medium files, but there are two popular alternatives: read_csv from the readr package (part of the tidyverse) and fread from data.table. You will need to install the packages first.

The read_csv function returns a tibble and prints a summary of the type it guessed for each column:

# install.packages("readr")
library(readr)

df <- read_csv("my_file.csv")
df

Rows: 4 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): name, city
dbl (2): age, score

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# A tibble: 4 × 4
  name    age city   score
  <chr> <dbl> <chr>  <dbl>
1 Ana      28 Madrid   7.5
2 Bob      35 London   8.1
3 Carla    41 Paris    6.9
4 David    23 Berlin   9.2

The fread function returns a data.table and detects the separator automatically, so the same call works for comma, semicolon or tab separated files:

# install.packages("data.table")
library(data.table)

df <- fread("my_file.csv")
df

     name   age   city score
   <char> <int> <char> <num>
1:    Ana    28 Madrid   7.5
2:    Bob    35 London   8.1
3:  Carla    41  Paris   6.9
4:  David    23 Berlin   9.2

Both functions can read only some columns by name:

read_csv("my_file.csv", col_select = c(name, score))
fread("my_file.csv", select = c("name", "score"))

The main differences are summarized in the following table:

To give you an idea of the speed difference, this is the time (in seconds) that each function took to read a CSV file with one million rows and four columns on our computer:

system.time(read.csv("big.csv"))
system.time(read_csv("big.csv", show_col_types = FALSE))
system.time(fread("big.csv"))

   user  system elapsed
   3.49    0.22    3.79
   user  system elapsed
   1.14    0.76    0.23
   user  system elapsed
   0.13    0.08    0.03

The exact times will depend on your computer, but fread and read_csv are usually more than ten times faster than read.csv with large files.

Read multiple CSV files in R

It is worth mentioning that it is possible to import multiple CSV files at the same time instead of loading them into R one by one. For that purpose you can use the list.files function in order to look for all CSV files and then read them applying the read.csv (or read.csv2) function with the sapply function.

files <-  list.files(pattern = "*.csv")

multiple_csv <- sapply(files, read.csv)

If all the files have the same columns, you can read them with lapply and stack them into a single data frame with rbind:

files <- list.files(pattern = "\\.csv$")

all_data <- do.call(rbind, lapply(files, read.csv))

Common errors when reading a CSV in R

cannot open file ‘x.csv’: No such file or directory

This is the most common error. It means that R can’t find the file in the path you wrote:

read.csv("data.csv")

Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") :
  cannot open file 'data.csv': No such file or directory

To fix it, check the following:

• The file is in your working directory. You can see it with getwd() and the files on it with list.files().
• The name and the extension are correct. Windows hides extensions by default, so a file called data.csv may be data.csv.txt in reality. file.exists("data.csv") must return TRUE.
• If you write the full path on Windows, use forward slashes ("C:/Users/me/data.csv") or double backslashes ("C:\\Users\\me\\data.csv"), never single backslashes.

more columns than column names

This error appears when some rows have more values than the header:

name,age
Ana,28,Madrid,7.5
Bob,35,London,8.1

read.csv("bad.csv")

Error in read.table(file = file, header = header, sep = sep, quote = quote,  :
  more columns than column names

It is usually caused by an incomplete header, by a wrong separator or by text values with commas that are not quoted. If the header is wrong, skip it and set the names yourself:

read.csv("bad.csv", header = FALSE, skip = 1,
         col.names = c("name", "age", "city", "score"))

  name age   city score
1  Ana  28 Madrid   7.5
2  Bob  35 London   8.1

incomplete final line found by readTableHeader

This warning means that the last line of the file doesn’t end with a line break:

df <- read.csv("noeol.csv")

Warning message:
In read.table(file = file, header = header, sep = sep, quote = quote,  :
  incomplete final line found by readTableHeader on 'noeol.csv'

It is just a warning and the data is read correctly. You can ignore it or remove it by opening the file in a text editor and pressing Enter at the end of the last line.

duplicate ‘row.names’ are not allowed

Error in read.table(file = file, header = header, sep = sep, quote = quote,  :
  duplicate 'row.names' are not allowed

R uses the first column as row names in two cases: when you set row.names = 1 and when the data rows have one value more than the header, which typically happens when every line ends with a comma:

name,age,city
Ana,28,Madrid,
Ana,35,London,

As row names must be unique, the repeated "Ana" causes the error. If you used row.names = 1, just remove it. In the case of the trailing commas, set row.names = NULL and shift the column names:

df <- read.csv("trailing_comma.csv", row.names = NULL)
colnames(df) <- c(colnames(df)[-1], "empty")
df$empty <- NULL
df

  name age   city
1  Ana  28 Madrid
2  Ana  35 London