Reading one column .csv file

[David Arnold]

My friend sent an Excel file:

http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls

I opened it in Excel, saved is as cyu01_iqscores.csv, then imported it into R with:

iqscores=read.csv('cyu01_iqscores.csv',header=TRUE)

The result was:

> head(iqscores)
  IQ.Scores  X
1       145 NA
2       101 NA
3       123 NA
4       106 NA
5       117 NA
6       102 NA

Now, I know I can cure this with:

iqscores=iqscores[,1]

But I am wondering about this weird behavior.

Suggestions?

David

[arun kirshna]

HI,

I am reading it fine.
iqscores<-read.csv("cyu01_iqscores.csv",header=TRUE)
> head(iqscores)
#  IQ.Scores
#1       145
#2       101
#3       123
#4       106
#5       117
#6       102

iqscores<-read.csv("cyu01_iqscores.csv",header=TRUE, sep=" ")
 head(iqscores)
#   IQ Scores
#1 145     NA
#2 101     NA
#3 123     NA
#4 106     NA
#5 117     NA
#6 102     NA

iqscores<-read.csv("cyu01_iqscores.csv",header=TRUE,sep=",")
 head(iqscores)
#  IQ.Scores
#1       145
#2       101
#3       123
#4       106
#5       117
#6       102
Check how you saved the .csv file.  If it is comma separated, sep="," works fine.
A.K.

[John Kane]

I cannot duplicate this using LibreOffice Calc.  It saves exactly as one would expect.

John Kane
Kingston ON Canada

> -----Original Message-----
> From: [hidden email]
> Sent: Wed, 15 Aug 2012 10:27:54 -0700 (PDT)
> To: [hidden email]
> Subject: [R] Reading one column .csv file
>
> My friend sent an Excel file:
>
> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
>
> I opened it in Excel, saved is as cyu01_iqscores.csv, then imported it
> into
> R with:
>
> iqscores=read.csv('cyu01_iqscores.csv',header=TRUE)
>
> The result was:
>
>> head(iqscores)
>   IQ.Scores  X
> 1       145 NA
> 2       101 NA
> 3       123 NA
> 4       106 NA
> 5       117 NA
> 6       102 NA
>
> Now, I know I can cure this with:
>
> iqscores=iqscores[,1]
>
> But I am wondering about this weird behavior.
>
> Suggestions?
>
> David
>
>
>
> --
> View this message in context:
> http://r.789695.n4.nabble.com/Reading-one-column-csv-file-tp4640396.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

____________________________________________________________
FREE 3D MARINE AQUARIUM SCREENSAVER - Watch dolphins, sharks & orcas on your desktop!

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[jholtman]

There is nothing weird about it if you look at the structure of the CSV file:

IQ Scores,
145,
101,
123,
106,
117,
102,
139,
142,
94,

Notice that there is an extra comma that EXCEL is inserting and
therefore when you are reading it in to R, it assumes there is an
unnamed column that it calls "X":

> x <- read.csv('clipboard')
> str(x)
'data.frame':   60 obs. of  2 variables:
 $ IQ.Scores: int  145 101 123 106 117 102 139 142 94 124 ...
 $ X        : logi  NA NA NA NA NA NA ...

So file a bug report with Microsoft on the way Excel writes CSV files;
R is interpreting them correctly.


On Wed, Aug 15, 2012 at 1:27 PM, darnold <[hidden email]> wrote:

> My friend sent an Excel file:
>
> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
>
> I opened it in Excel, saved is as cyu01_iqscores.csv, then imported it into
> R with:
>
> iqscores=read.csv('cyu01_iqscores.csv',header=TRUE)
>
> The result was:
>
>> head(iqscores)
>   IQ.Scores  X
> 1       145 NA
> 2       101 NA
> 3       123 NA
> 4       106 NA
> 5       117 NA
> 6       102 NA
>
> Now, I know I can cure this with:
>
> iqscores=iqscores[,1]
>
> But I am wondering about this weird behavior.
>
> Suggestions?
>
> David
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Reading-one-column-csv-file-tp4640396.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

--
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[Peter Dalgaard-2]

On Aug 15, 2012, at 22:11 , John Kane wrote:

> I cannot duplicate this using LibreOffice Calc.  It saves exactly as one would expect.
>

I can duplicate it with Excel on OSX. Each line of the CSV file ends with a comma!

Corresponding thing happens if you save as .txt (TAB-delimited) and read with read.delim().

However, select all followed by read.delim from the clipboard works fine. Go figure...

You can skip the empty column using

read.csv("~/Downloads/cyu01_iqscores.csv",colClasses=list("numeric",NULL))

but deleting the extra column is just as expedient.

> John Kane
> Kingston ON Canada
>
>
>> -----Original Message-----
>> From: [hidden email]
>> Sent: Wed, 15 Aug 2012 10:27:54 -0700 (PDT)
>> To: [hidden email]
>> Subject: [R] Reading one column .csv file
>>
>> My friend sent an Excel file:
>>
>> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
>> http://msemac.redwoods.edu/~darnold/temp/cyu01_iqscores.xls
>>
>> I opened it in Excel, saved is as cyu01_iqscores.csv, then imported it
>> into
>> R with:
>>
>> iqscores=read.csv('cyu01_iqscores.csv',header=TRUE)
>>
>> The result was:
>>
>>> head(iqscores)
>>  IQ.Scores  X
>> 1       145 NA
>> 2       101 NA
>> 3       123 NA
>> 4       106 NA
>> 5       117 NA
>> 6       102 NA
>>
>> Now, I know I can cure this with:
>>
>> iqscores=iqscores[,1]
>>
>> But I am wondering about this weird behavior.
>>
>> Suggestions?
>>
>> David
>>
>>
>>
>> --
>> View this message in context:
>> http://r.789695.n4.nabble.com/Reading-one-column-csv-file-tp4640396.html
>> Sent from the R help mailing list archive at Nabble.com.
>>
>> ______________________________________________
>> [hidden email] mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
> ____________________________________________________________
> FREE 3D MARINE AQUARIUM SCREENSAVER - Watch dolphins, sharks & orcas on your desktop!
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

--
Peter Dalgaard, Professor,
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: [phone]
Email: [hidden email]  Priv: [hidden email]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[David Arnold]

Peter,

Interesting. Never heard of copying from the clipboard. I am also on a MacBook Pro, but I cannot get it to work.

1. I selected the column of data (including the header) in Excel.

2. Selected Edit->Copy.

3. In R, tried:

> a <- read.delim("clipboard")
Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") : clipboard cannot be opened or contains no text

4. I checked the clipboard. The data is there.

What am I missing?

D.

[David Carlson]

On the Mac try pipe("pbpaste") instead of "clipboard."

----------------------------------------------
David L Carlson
Associate Professor of Anthropology
Texas A&M University
College Station, TX 77843-4352

> -----Original Message-----
> From: [hidden email] [mailto:r-help-bounces@r-
> project.org] On Behalf Of darnold
> Sent: Wednesday, August 15, 2012 4:11 PM
> To: [hidden email]
> Subject: Re: [R] Reading one column .csv file
>
> Peter,
>
> Interesting. Never heard of copying from the clipboard. I am also on a
> MacBook Pro, but I cannot get it to work.
>
> 1. I selected the column of data (including the header) in Excel.
>
> 2. Selected Edit->Copy.
>
> 3. In R, tried:
>
> > a <- read.delim("clipboard")
> Error in file(file, "rt") : cannot open the connection
> In addition: Warning message:
> In file(file, "rt") : clipboard cannot be opened or contains no text
>
> 4. I checked the clipboard. The data is there.
>
> What am I missing?
>
> D.
>
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Reading-
> one-column-csv-file-tp4640396p4640429.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-
> guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[Berend Hasselman]

On 15-08-2012, at 23:11, darnold wrote:

> Peter,
>
> Interesting. Never heard of copying from the clipboard. I am also on a
> MacBook Pro, but I cannot get it to work.
>

So you are running Mac OS X?

> 1. I selected the column of data (including the header) in Excel.
>
> 2. Selected Edit->Copy.
>
> 3. In R, tried:
>
>> a <- read.delim("clipboard")
> Error in file(file, "rt") : cannot open the connection
> In addition: Warning message:
> In file(file, "rt") : clipboard cannot be opened or contains no text
>
> 4. I checked the clipboard. The data is there.

See ? connections  Section "Clipboard"

a <- read.delim(pipe("pbpaste"))

Berend

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[David Arnold]

Tried that already.

My clipboard:

x
2
3
4
5

My attempt:

> a <- read.delim(pipe("pbpaste"))
Warning message:
In read.table(file = file, header = header, sep = sep, quote = quote,  :
  incomplete final line found by readTableHeader on 'pbpaste'

And again:

> a <- read.delim(pipe("pbpaste"),header=TRUE)
Warning message:
In read.table(file = file, header = header, sep = sep, quote = quote,  :
  incomplete final line found by readTableHeader on 'pbpaste'

Suggestions?

David

[Berend Hasselman]

On 16-08-2012, at 06:06, darnold wrote:

> Tried that already.
>
> My clipboard:
>
> x
> 2
> 3
> 4
> 5
>
> My attempt:
>
>> a <- read.delim(pipe("pbpaste"))
> Warning message:
> In read.table(file = file, header = header, sep = sep, quote = quote,  :
>  incomplete final line found by readTableHeader on 'pbpaste'
>
> And again:
>
>> a <- read.delim(pipe("pbpaste"),header=TRUE)
> Warning message:
> In read.table(file = file, header = header, sep = sep, quote = quote,  :
>  incomplete final line found by readTableHeader on 'pbpaste'
>
> Suggestions?
>

It's a warning message not an error. It tells you that the final line does not end with a newline character. That's Excel's fault.
After a <- read.delim(....) just inspect a with

a

It's probably ok.

Berend

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[David Arnold]

Worked!

Thanks.

David.