duplicate 'row.names' are not allowed error

70

I am trying to load a csv file that has 14 columns like this:

StartDate, var1, var2, var3, ..., var14

when I issue this command:

systems <- read.table("http://getfile.pl?test.csv", header = TRUE, sep = ",")

I get an error message.

duplicate row.names are not allowed

It seems to me that the first column name is causing the issue. When I manually download the file and remove the StartDate name from the file, R successfully reads the file and replaces the first column name with X. Can someone tell me what is going on? The file is a (comma separated) csv file.

This question is tagged with r csv r-faq

~ Asked on 2012-01-13 16:41:46

The Best Answer is


105

Then tell read.table not to use row.names:

systems <- read.table("http://getfile.pl?test.csv", 
                      header=TRUE, sep=",", row.names=NULL)

and now your rows will simply be numbered.

Also look at read.csv which is a wrapper for read.table which already sets the sep=',' and header=TRUE arguments so that your call simplifies to

systems <- read.csv("http://getfile.pl?test.csv", row.names=NULL)

~ Answered on 2012-01-13 17:11:34


41

This related question points out a part of the ?read.table documentation that explains your problem:

If there is a header and the first row contains one fewer field than the number of columns, the first column in the input is used for the row names. Otherwise if row.names is missing, the rows are numbered.

Your header row likely has 1 fewer column than the rest of the file and so read.table assumes that the first column is the row.names (which must all be unique), not a column (which can contain duplicated values). You can fix this by using one of the following two Solutions:

  1. adding a delimiter (ie \t or ,) to the front or end of your header row in the source file, or,
  2. removing any trailing delimiters in your data

The choice will depend on the structure of your data.

Example:
Here the header row is interpreted as having one fewer column than the data because the delimiters don't match:

v1,v2,v3   # 3 items!!
a1,a2,a3,  # 4 items
b1,b2,b3,  # 4 items

This is how it is interpreted by default:

   v1,v2,v3   # 3 items!!
a1,a2,a3,  # 4 items
b1,b2,b3,  # 4 items

The first column (with no header) values are interpreted as row.names: a1 and b1. If this column contains duplicates, which is entirely possible, then you get the duplicate 'row.names' are not allowed error.

If you set row.names = FALSE, the shift doesn't happen, but you still have a mismatching number of items in the header and in the data because the delimiters don't match.

Solution 1 Add trailing delimiter to header:

v1,v2,v3,  # 4 items!!
a1,a2,a3,  # 4 items
b1,b2,b3,  # 4 items

Solution 2 Remove excess trailing delimiter from non-header rows:

v1,v2,v3   # 3 items
a1,a2,a3   # 3 items!!
b1,b2,b3   # 3 items!!

~ Answered on 2013-09-23 19:07:53


Most Viewed Questions: