Odczyt pliku Excel bezpośrednio ze skryptu R
Jak mogę odczytać plik Excel bezpośrednio do R? Czy powinienem najpierw wyeksportować dane do pliku tekstowego lub CSV i zaimportować ten plik do R?
12 answers
Tak. Zobacz odpowiednią stronę na R wiki. Krótka odpowiedź: read.xls
z gdata
pakiet działa przez większość czasu (chociaż musisz mieć zainstalowany Perl na Twoim systemie-zwykle jest już prawdziwy na MacOS i Linuksie, ale robi dodatkowy krok na Windows, np. patrz http://strawberryperl.com/). istnieją różne zastrzeżenia i alternatywy wymienione na stronie r wiki.
Jedynym powodem, dla którego nie robię tego bezpośrednio, jest to, że możesz sprawdzić arkusz kalkulacyjny, aby zobaczyć, czy ma usterki( dziwne nagłówki, wiele arkuszy roboczych [możesz czytać tylko jeden na raz, chociaż możesz oczywiście zapętlić je wszystkie], zawarte wykresy itp.). Ale dla dobrze uformowanego, prostokątnego arkusza kalkulacyjnego z zwykłymi liczbami i danymi znakowymi (tj. nie sformatowanymi przecinkami liczbami, datami, formułami z błędami dzielenia przez zero, brakującymi wartościami itp. itd. ..) Generalnie nie mam problemu z tym procesem.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-03 23:56:56
Powtórzę, co @ Chase polecił: użyj XLConnect .
Powody korzystania z XLConnect są, moim zdaniem, następujące:
- Cross platform. XLConnect jest napisany w Javie i dlatego będzie działał na Win, Linux, Mac bez zmiany kodu R (z wyjątkiem prawdopodobnie ciągów ścieżek) Nic więcej do załadowania. Po prostu zainstaluj XLConnect i zacznij żyć dalej.
- wspomniałeś tylko o czytaniu plików Excel, ale XLConnect będzie również zapisywał pliki Excel, w tym zmieniając komórkę formatowanie. I zrobi to z Linuksa lub Maca, a nie tylko Win.
XLConnect jest nieco nowy w porównaniu do innych rozwiązań, więc jest rzadziej wspominany w blogach i dokumentach referencyjnych. Dla mnie to było bardzo przydatne.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-23 16:51:33
A teraz jest readxl:
Pakiet readxl ułatwia pobieranie danych z Excela i do R. W porównaniu z istniejącymi pakietami (np. gdata, xlsx, xlsReadWrite itp.) readxl nie ma zewnętrznych zależności, więc jest łatwy w instalacji i użyciu na wszystkie systemy operacyjne. Jest przeznaczony do pracy z danymi tabelarycznymi przechowywanymi w jednym arkuszu.
Readxl jest zbudowany na bazie biblioteki libxls C, która abstrahuje wiele złożoności podstawowy format binarny.
Wspiera zarówno dziedzictwo .format xls i .xlsx
Readxl jest dostępny z CRAN, lub możesz zainstalować go z github za pomocą:
# install.packages("devtools")
devtools::install_github("hadley/readxl")
Użycie
library(readxl)
# read_excel reads both xls and xlsx files
read_excel("my-old-spreadsheet.xls")
read_excel("my-new-spreadsheet.xlsx")
# Specify sheet with a number or name
read_excel("my-spreadsheet.xls", sheet = "data")
read_excel("my-spreadsheet.xls", sheet = 2)
# If NAs are represented by something other than blank cells,
# set the na argument
read_excel("my-spreadsheet.xls", na = "NA")
Zauważ, że chociaż opis mówi "brak zewnętrznych zależności", wymaga Rcpp
pakiet , który z kolei wymaga Rtools (Dla Windows) lub Xcode (dla OSX), które są zależnościami zewnętrznymi od R. chociaż wiele osób ma je zainstalowane dla inne powody.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-30 02:53:49
EDIT 2015-Październik: jak inni skomentowali tutaj openxlsx
oraz readxl
pakiety są znacznie szybsze niż pakiet xlsx
i w rzeczywistości potrafią otwierać większe pliki Excela (>1500 wierszy i > 120 kolumn). @MichaelChirico pokazuje, że readxl
jest lepsze, gdy preferowana jest szybkość i openxlsx
zastępuje funkcjonalność dostarczaną przez pakiet xlsx
. Jeśli szukasz pakietu do odczytu, zapisu i modyfikacji plików Excel w 2015 roku, Wybierz openxlsx
zamiast xlsx
.
Pre-2015: użyłem xlsx
Pakiet . To zmieniło mój przepływ pracy z Excel i R. nie więcej irytujące wyskakujące okienka z pytaniem, czy jestem pewien, że chcę zapisać mój arkusz Excel w .format txt. Pakiet zapisuje również pliki Excel.
Jednak funkcja {[10] } jest powolna przy otwieraniu dużych plików Excela. read.xlsx2
funkcja jest znacznie szybsza, ale nie questuje klasy wektorowej danych.kolumny ramek. Musisz użyć polecenia colClasses
, aby określić żądaną kolumnę klasy, jeśli używasz funkcji read.xlsx2
. Oto praktyczny przykład:
read.xlsx("filename.xlsx", 1)
odczytuje plik i tworzy dane.klasy kolumn frame są prawie przydatne, ale są bardzo wolne dla dużych zbiorów danych. Działa również dla plików .xls
.
read.xlsx2("filename.xlsx", 1)
jest szybszy, ale trzeba będzie ręcznie definiować klasy kolumn. Skrótem jest dwukrotne uruchomienie polecenia(patrz przykład poniżej). character
Specyfikacja konwertuje Twoje kolumny na czynniki. Użyj opcji Date
i POSIXct
dla czasu.
coln <- function(x){y <- rbind(seq(1,ncol(x))); colnames(y) <- colnames(x)
rownames(y) <- "col.number"; return(y)} # A function to see column numbers
data <- read.xlsx2("filename.xlsx", 1) # Open the file
coln(data) # Check the column numbers you want to have as factors
x <- 3 # Say you want columns 1-3 as factors, the rest numeric
data <- read.xlsx2("filename.xlsx", 1, colClasses= c(rep("character", x),
rep("numeric", ncol(data)-x+1)))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-26 16:07:12
Biorąc pod uwagę mnogość różnych sposobów odczytu pliku Excel w R
i mnogość odpowiedzi tutaj, pomyślałem, że spróbuję rzucić trochę światła na to, która z wymienionych tutaj opcji jest najlepsza (w kilku prostych sytuacjach).
Sam używam xlsx
odkąd zacząłem używać R
, dla inercji, jeśli nic innego, i ostatnio zauważyłem, że nie ma żadnych obiektywnych informacji o tym, który pakiet działa lepiej.
Każde ćwiczenie porównawcze jest obarczone z trudnościami, ponieważ niektóre pakiety z pewnością poradzą sobie z pewnymi sytuacjami lepiej niż inne, a Wodospad innych zastrzeżeń.
W związku z tym, używam (odtwarzalnego) zestawu danych, który moim zdaniem jest w dość powszechnym formacie (8 pól ciągowych, 3 numeryczne, 1 liczba całkowita, 3 daty):set.seed(51423)
data.frame(
str1 = sample(sprintf("%010d", 1:NN)), #ID field 1
str2 = sample(sprintf("%09d", 1:NN)), #ID field 2
#varying length string field--think names/addresses, etc.
str3 =
replicate(NN, paste0(sample(LETTERS, sample(10:30, 1L), TRUE),
collapse = "")),
#factor-like string field with 50 "levels"
str4 = sprintf("%05d", sample(sample(1e5, 50L), NN, TRUE)),
#factor-like string field with 17 levels, varying length
str5 =
sample(replicate(17L, paste0(sample(LETTERS, sample(15:25, 1L), TRUE),
collapse = "")), NN, TRUE),
#lognormally distributed numeric
num1 = round(exp(rnorm(NN, mean = 6.5, sd = 1.5)), 2L),
#3 binary strings
str6 = sample(c("Y","N"), NN, TRUE),
str7 = sample(c("M","F"), NN, TRUE),
str8 = sample(c("B","W"), NN, TRUE),
#right-skewed integer
int1 = ceiling(rexp(NN)),
#dates by month
dat1 =
sample(seq(from = as.Date("2005-12-31"),
to = as.Date("2015-12-31"), by = "month"),
NN, TRUE),
dat2 =
sample(seq(from = as.Date("2005-12-31"),
to = as.Date("2015-12-31"), by = "month"),
NN, TRUE),
num2 = round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L),
#date by day
dat3 =
sample(seq(from = as.Date("2015-06-01"),
to = as.Date("2015-07-15"), by = "day"),
NN, TRUE),
#lognormal numeric that can be positive or negative
num3 =
(-1) ^ sample(2, NN, TRUE) * round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L)
)
Napisałem to do csv i otworzyłem w LibreOffice i zapisałem jako .pliku xlsx, następnie benchmarked 4 z pakietów wymienionych w tym wątku: xlsx
, openxlsx
, readxl
, i gdata
, używając domyślnego opcje (próbowałem też wersji, czy określam typy kolumn, ale to nie zmieniło rankingów).
Wykluczam RODBC
ponieważ jestem na Linuksie; XLConnect
ponieważ wydaje się, że jego głównym celem nie jest czytanie w pojedynczych arkuszach Excela, ale importowanie całych skoroszytów Excela, więc umieszczenie konia w wyścigu tylko na jego możliwościach czytania wydaje się niesprawiedliwe; i xlsReadWrite
ponieważ nie jest już kompatybilny z moją wersją R
(wydaje się, że został wycofany).
Potem uciekłem benchmarki z NN=1000L
i NN=25000L
(Resetowanie materiału siewnego przed każdą deklaracją data.frame
powyżej), aby umożliwić różnice w odniesieniu do rozmiaru pliku Excel. gc
jest przede wszystkim dla xlsx
, które znalazłem czasami może tworzyć zapory pamięci. Bez zbędnych ceregieli, oto wyniki, które znalazłem:
1000-Wierszowy Plik Excel
benchmark1k <-
microbenchmark(times = 100L,
xlsx = {xlsx::read.xlsx2(fl, sheetIndex=1); invisible(gc())},
openxlsx = {openxlsx::read.xlsx(fl); invisible(gc())},
readxl = {readxl::read_excel(fl); invisible(gc())},
gdata = {gdata::read.xls(fl); invisible(gc())})
# Unit: milliseconds
# expr min lq mean median uq max neval
# xlsx 194.1958 199.2662 214.1512 201.9063 212.7563 354.0327 100
# openxlsx 142.2074 142.9028 151.9127 143.7239 148.0940 255.0124 100
# readxl 122.0238 122.8448 132.4021 123.6964 130.2881 214.5138 100
# gdata 2004.4745 2042.0732 2087.8724 2062.5259 2116.7795 2425.6345 100
Więc readxl
jest zwycięzcą, z openxlsx
konkurencyjnym i gdata
wyraźnym przegranym. Biorąc każdy środek względem minimum kolumny:
# expr min lq mean median uq max
# 1 xlsx 1.59 1.62 1.62 1.63 1.63 1.65
# 2 openxlsx 1.17 1.16 1.15 1.16 1.14 1.19
# 3 readxl 1.00 1.00 1.00 1.00 1.00 1.00
# 4 gdata 16.43 16.62 15.77 16.67 16.25 11.31
Widzimy mój ulubiony, xlsx
jest o 60% wolniejszy niż readxl
.
25 000-Wierszowy Plik Excel
Ze względu na ilość czasu, który zajmuje, zrobiłem tylko 20 powtórzeń na większym pliku, w przeciwnym razie polecenia były identyczne. Oto surowe dane:
# Unit: milliseconds
# expr min lq mean median uq max neval
# xlsx 4451.9553 4539.4599 4738.6366 4762.1768 4941.2331 5091.0057 20
# openxlsx 962.1579 981.0613 988.5006 986.1091 992.6017 1040.4158 20
# readxl 341.0006 344.8904 347.0779 346.4518 348.9273 360.1808 20
# gdata 43860.4013 44375.6340 44848.7797 44991.2208 45251.4441 45652.0826 20
Oto dane względne:
# expr min lq mean median uq max
# 1 xlsx 13.06 13.16 13.65 13.75 14.16 14.13
# 2 openxlsx 2.82 2.84 2.85 2.85 2.84 2.89
# 3 readxl 1.00 1.00 1.00 1.00 1.00 1.00
# 4 gdata 128.62 128.67 129.22 129.86 129.69 126.75
Więc readxl
jest zdecydowanym zwycięzcą jeśli chodzi o szybkość. gdata
lepiej mieć coś innego na to, ponieważ jest boleśnie wolne w czytaniu plików Excela, a ten problem jest tylko zaostrzony dla większe stoły.
Dwa losowania openxlsx
to 1) jego obszerne inne metody (readxl
jest zaprojektowany do wykonywania tylko jednej rzeczy, która prawdopodobnie jest częścią tego, dlaczego jest tak szybka), zwłaszcza jego write.xlsx
funkcja, i 2) (bardziej wadą dla readxl
) col_types
argument w readxl
only (jak na to pismo) akceptuje niektóre niestandardowe R
: "text"
zamiast "character"
i "date"
zamiast "Date"
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-24 14:40:33
Miałem szczęście z XLConnect
: http://cran.r-project.org/web/packages/XLConnect/index.html
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-23 15:39:56
library(RODBC)
file.name <- "file.xls"
sheet.name <- "Sheet Name"
## Connect to Excel File Pull and Format Data
excel.connect <- odbcConnectExcel(file.name)
dat <- sqlFetch(excel.connect, sheet.name, na.strings=c("","-"))
odbcClose(excel.connect)
Osobiście lubię RODBC i mogę go polecić.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-23 16:12:12
Właśnie dałem paczkę openxlsx
spróbować dzisiaj. To działało naprawdę dobrze (i szybko).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-05 19:49:28
Innym rozwiązaniem jest xlsReadWrite
pakiet, który nie wymaga dodatkowych instalacji, ale wymaga pobrania dodatkowego shlib przed pierwszym użyciem przez:
require(xlsReadWrite)
xls.getshlib()
Zapomnienie o tym może spowodować całkowitą frustrację. Byłem tam i w ogóle...
Na marginesie: warto rozważyć konwersję do formatu tekstowego (np. To z wielu powodów:
Niezależnie od rozwiązania (RODBC, gdata, xlsReadWrite) niektóre dziwne rzeczy mogą się zdarzyć, gdy Twoje dane zostaną przekonwertowane. Szczególnie daty mogą być dość uciążliwe. Pakiet
HFWutils
zawiera kilka narzędzi do obsługi dat Excela (za komentarz @Bena Bolkera).Jeśli masz duże arkusze, czytanie w plikach tekstowych jest szybsze niż czytanie z programu EXCEL.
Za .xls i .pliki xlsx, inne rozwiązania mogą być konieczne. Np. pakiet xlsreadwrite obecnie nie obsługuje .XLSX AFAIK.
gdata
wymaga od Ciebie zainstaluj dodatkowe biblioteki Perla dla .obsługa xlsx.xlsx
pakiet może obsługiwać rozszerzenia o tej samej nazwie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-17 08:22:39
Jak wspomniano powyżej w wielu innych odpowiedziach, istnieje wiele dobrych pakietów, które łączą się z plikiem XLS/X i pobierają dane w rozsądny sposób. Należy jednak ostrzec, że w żadnym wypadku nie należy korzystać ze schowka (lub .csv) plik do pobierania danych z Excela. Aby zobaczyć dlaczego, wprowadź =1/3
do komórki w programie excel. Teraz zmniejsz liczbę widocznych punktów dziesiętnych do dwóch. Następnie skopiuj i wklej DANE do R. teraz zapisz plik CSV. Zauważysz, że w obu przypadkach Excel ma pomocnie przechowywał tylko dane, które były widoczne dla ciebie przez interfejs i straciłeś całą precyzję w rzeczywistych danych źródłowych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-08 20:02:10
Rozszerzając odpowiedź podaną przez @ Mikko możesz użyć zgrabnej sztuczki, aby przyspieszyć sprawy bez konieczności" znajomości " klas kolumn z wyprzedzeniem. Wystarczy użyć read.xlsx
, Aby pobrać ograniczoną liczbę rekordów w celu określenia klas, a następnie użyć read.xlsx2
Przykład
# just the first 50 rows should do...
df.temp <- read.xlsx("filename.xlsx", 1, startRow=1, endRow=50)
df.real <- read.xlsx2("filename.xlsx", 1,
colClasses=as.vector(sapply(df.temp, mode)))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-28 13:31:36
Plik Excel można wczytać bezpośrednio do R w następujący sposób:
my_data <- read.table(file = "xxxxxx.xls", sep = "\t", header=TRUE)
Odczyt plików xls i xlxs za pomocą pakietu readxl
library("readxl")
my_data <- read_excel("xxxxx.xls")
my_data <- read_excel("xxxxx.xlsx")
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-30 12:45:40