Organize data in Excel to open as table in R?

Question

Organize data in Excel to open as table in R?

Navigation

#1 by (5 votes)
#2 by (2 votes)
#3 by (0 votes)

10

I have a file with 50 comments and 3 variables in Excel (.xlsx) file, and I made it into .csv. I used banco1<- read.csv("teste1.csv") to create the table in R, but my 3 variables appear in the same column. How do I make them appear separate?

r excel xls csv rstudio

asked by anonymous 29.09.2014 / 05:19

3 answers

upload without refresh with FormData, jquery ASP.NET Identity and Windows Identity Foundation (WIF)?

score 5 · Answer 1

From the result you noted, I'll assume you're using a Portuguese version of Excel from Windows. Generally, when we save an Excel worksheet from Windows to CSV, it is saved with ; as the separator. This happens mostly in Brazilian versions (some European too) of Excel, because our decimal separator is the comma, not the dot, as it is in the United States.

In R this is already somewhat anticipated by the read.csv2 function (see here ), which has the headers, decimals, and tabs setting that Windows Excel uses by default. See in function definition, making ?read.csv

read.csv(file, header = TRUE, sep = ",", quote = "\"",
         dec = ".", fill = TRUE, comment.char = "", ...)

read.csv2(file, header = TRUE, sep = ";", quote = "\"",
          dec = ",", fill = TRUE, comment.char = "", ...)

In addition to this possible solution, as Anthony said, there are packages that make it easy to read Excel files, such as xlsx and gdata . See here and here .

score 2 · Answer 2

Check the tabs in the .csv file by opening it in text mode. In the read.csv function, you must set the sep parameter. For a table with the character "&" as the separator:, you should use

banco1 <- read.csv("teste.csv", sep="&")

Also use some of these options for reading xlsx that work well is the package "XLConnect" ( install.packages("XLConnect") ), which is better:

library(XLConnect)
banco1 <- readWorksheet(loadWorkbook("teste.xlsx"),sheet=1)

Or, using the "xlsx" package ( install.packages("xlsx") ):

library(xlsx)
banco1 <- read.xlsx("teste.xlsx", 1 , stringsAsFactors=F)

score 0 · Answer 3

Another alternative is to create a delimited file of type " Text (tab separated) " in Excel. Excel generates a file with the .txt extension which, from experience, is most easily imported by R Studio.

In other words, you will no longer use a comma separated values, and will use a tab separeted value. You can also use a .csv file, but you will need to spend some time setting the read.csv parameters.

To later export an object called "mydata" to a tab-separated text file called mydata.txt, use the command:

write.table(mydata, "mydata.txt", sep="\t")