how to read a specific CSV file in R

Question

[Mo]

Hello,

Since I could not really find a solution for my previous questions related to protein and complexes. I am trying to work on i

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

Is there anyone know how many columns I should expect from this input ? how to fix this problem?

[Zhilong Jia]

As I saw in the linked file, it probably is not a csv file, but ";" sep file. Maybe using read.table and quote="", and comment.char = "", sep=";". Even you think it's a csv file, still add quote="", and comment.char = "" as they are the main reason of more columns than column names​.

[Mo]

@Zhilong Jia your comments pushed me toward a good answer. when I remove the header from the file and I get it with data.table, the loaded file seems to be alright , however, I dont know why when I get it with header it is not working well . I liked your answer anyway, thanks

[Daniel]

Check your .csv file in a text editor (gedit/notepad etc) to see if you have whitespace at the end of lines, or a missing header. That's often the problem in my experience.

[Mo]

@Daniel header should be fine, because it is a database. There is a problem with the rest that I don't know which section belongs to which header.

[Daniel]

Your data has a semi-colon separator your actual data has lots of comas in it. Change your input line to this and it should work:

dat = read.table("path to the data/allComplexes.csv", sep=";", header = TRUE)

[Mo]

@Daniel thanks a good answer was given below which I accepted.

[venu]

Your header is actually not comma separated. Do head -1 allComplxes.csv and check. If you do header=FALSE, it will load the data.

[Mo]

@venu If you turn off the header, it will load it, but it does not make any sense. Please look at it. it mixed the IDs with names

[venu]

I've only checked the header, didn't load into R. Edited. Open in google spreadsheet and you'll come to know how many fields are actually comma separated. Edit whole file with some bash commands then load it into R.

Answer 1

[Sukhi Singh]

# open the file in a text editor and remove the last ';' from the header line (this makes it 13 columns but data as 12 or its missing the complete information in that column.

# this is a helpful function to test that (before loading the file in)
table(count.fields('~/Downloads/allComplexes.csv',sep=';'))

 4  11  12  13 
 4   1 444   1 ​

Above tells you, you have 2 uneven rows, one with 11 and other with 13 columns.

# the read the file in normally, this should load it up and sort all problems, read about the parameters I used here
allComplexes=read.table('~/Downloads/allComplexes.csv',sep=';',header=T,fill=TRUE,quote='')

Cheers

[Mo]

@Sukhdeep Singh nice, fill=TRUE is the key!

[cbio]

@Sukhdeep Singh

Would you mind letting me know what is different between your output and my solution? Yours is obviously more elegant, but being new to R i'd really like to know if my solution does not work, and why that would be.

They look similar in R Studio, yours has some "'s in empty fields, but i'm having trouble seeing anything else.

Thank you!

[Sukhi Singh]

Hi Carlos, your solution is not wrong, its fine. Just don't use a package (external) if you don't need to. Secondly, the problem was an extra semicolon (which might or might not be a part of the header), scrutinizing the file gave me the hint, so we don't have to do all the extra work of renaming columns etc etc.

[cbio]

Excellent, thank you very much.

Answer 2

[cbio]

install.packages('data.table')
library('data.table')
dat <- fread("path/to/allComplexes/csv")

This will produce an error and remove the first line of the file (your headers), but the data should load fine.

Seems there are not enough headers so you will have to manually add them in some sort of text editor, or in R once you've read in the files using the names function (shouldn't be too bad since there aren't many columns, but I agree this is frustrating), but otherwise it doesn't really matter.

[Mo]

@Carlos Guzman there are many loading function such as read.table, read.delim, read.csv, fread etc. The problem is that the header is just fine and I cannot add manually any header, but must find a way to assign the correct subcell to the header

[cbio]

I've looked through the data a bit, and I agree with @venu that the header is not comma separated. It seems to be separated by semi-colons. Some of the columns have commas in them which is throwing off the read.csv function.

Give the above a try and then do something like names(dat) <- c("names", "go", "here") using either the naming from the csv file you downloaded or your own. This should work, or at least it did for me.

[Mo]

@Carlos Guzman Thanks for your comment but that does not give what it should. Once you want to load a data (it can be loaded easily) once you want to make sense out of it (cannot be that easy). The header is standard because this is a database and I must not change it (means add or remove). The other part should be fit but not messed up. if I remove the header, everything is missed up. (not from computer point of view but from a biology information)

[cbio]

We are not removing anything are we? We are adding in the same header names. When I write my file out it looks identical to the original. Have you tried my solution? I am able to re-create the exact file by doing my above steps and then running

names(dat) <- c("Complex id", 
                "Complex name", 
                "Synonyms", 
                "organism", 
                "subunits (unitprot id)", 
                "subunits (entrez id)", 
                "protein complex purification method", 
                "pubmed id", 
                "funcat categories", 
                "functional comment", 
                "disease comment", 
                "subunit comment")

I must definitely be mis-understanding what you mean by "everything is messed up". When I head(dat) everything seems to be in the correct column when compared to the original file.

[Mo]

@Carlos Guzman I give you an small hint that you get the point, look at the first cell after you load it into R , 1;BCL6-HDAC4 complex;;Human;P41182. This includes 3 cells + half of another cell. simply ,

Complex id is 1,
Complex name is BCL6-HDAC4 complex
Synonyms  does not have any
organism human
subunits (unitprot id) P41182

[cbio]

I'm not getting the hint. Are you saying there is something wrong with the data in these columns? Are you assuming that every column MUST have data?

[Mo]

@Carlos Guzman Please load the data with the accepted command. Then you will see how the data should look. the problem was that the format of data is not right , a external sign added to the file etc. However, the accepted answer gave the right one which I was looking for. Just for your knowledge, each of these columns means something in biology, once you have them separated, you can look for a question you have in mind. If you have all together, it is more difficult to find it. however, please look at the accepted answer , if you still did not get it, please let me know

[cbio]

I went ahead and looked at both, the outputs are identical. The only difference is that accepted answer has some "s in some of the fields to denote empty fields. They both have the exact same dimensions (2867 obs of 12 variables).

Either way, I'm glad you found a solution that worked!