Импорт Excel-файлов с помощью readxl

Почему readxl для файлов Excel?

library(readxl)

# readxl can read:
# .xlsx  - modern Excel format (XML-based)
# .xls   - older Excel format (binary)
# .xlsm  - Excel with macros (reads data, ignores macros)

# Core functions:
# read_excel()   - auto-detects .xls vs .xlsx
# read_xlsx()    - always reads as .xlsx
# read_xls()     - always reads as .xls
# excel_sheets() - lists all sheet names

cat('readxl requires no Java, no Excel installation!')

excel_sheets() — список всех листов

library(readxl)

# Using readxl's built-in example file
path <- readxl_example('datasets.xlsx')

# List all sheets in the workbook
sheets <- excel_sheets(path)
print(sheets)

cat('\nNumber of sheets:', length(sheets))

read_excel() — базовое использование

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read the first sheet (default)
df <- read_excel(path)
print(head(df, 4))
cat('\nDimensions:', nrow(df), 'rows x', ncol(df), 'cols')

Аргумент sheet — выбор по имени или индексу

library(readxl)

path <- readxl_example('datasets.xlsx')
sheets <- excel_sheets(path)
print(sheets)

# Read by name
df_name <- read_excel(path, sheet = 'iris')
cat('iris sheet rows:', nrow(df_name), '\n')

# Read by index
df_idx <- read_excel(path, sheet = 2)
cat('Sheet 2 rows:', nrow(df_idx))

range — считывание диапазона ячеек

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read only the first 5 data rows of columns A-D
df <- read_excel(path, sheet='iris', range='A1:D6')
print(df)
cat('\nShape:', nrow(df), 'rows,', ncol(df), 'cols')

col_names — пользовательские имена столбцов

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read with custom column names (skip original header)
df <- read_excel(
  path,
  sheet = 'iris',
  col_names = c('sepal_l','sepal_w','petal_l','petal_w','species'),
  skip = 1  # Skip the original header row
)

print(head(df, 3))
print(names(df))

Аргумент skip — пропуск строк метаданных

library(readxl)

# Simulating a sheet with metadata rows (using a range instead)
path <- readxl_example('datasets.xlsx')

# Skip first row (pretend it has a title)
# and read only first 5 data rows
df <- read_excel(
  path,
  sheet = 'chickwts',
  skip = 1,       # Skip first data row
  col_names = FALSE  # Row 2 has no header now
)

print(head(df, 4))

col_types — указание типов столбцов

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read with explicit column types
# iris sheet: 4 numeric + 1 text
df <- read_excel(
  path,
  sheet = 'iris',
  col_types = c('numeric','numeric','numeric','numeric','text')
)

print(sapply(df, class))

Считывание всех листов с помощью map()

library(readxl)
library(purrr)

path <- readxl_example('datasets.xlsx')
sheets <- excel_sheets(path)

# Read all sheets into a named list
all_data <- map(sheets, ~read_excel(path, sheet=.x))
names(all_data) <- sheets

# Show dimensions of each sheet
map_df(all_data, function(df) {
  data.frame(rows=nrow(df), cols=ncol(df))
}, .id='sheet')

n_max — ограничение числа считываемых строк

library(readxl)

path <- readxl_example('datasets.xlsx')

# Preview just the first 5 rows
preview <- read_excel(path, sheet='iris', n_max=5)
print(preview)
cat('\nPreviewed', nrow(preview), 'of', nrow(read_excel(path, sheet='iris')), 'rows')

Аргумент na — строки для пропущенных значений

library(readxl)

path <- readxl_example('datasets.xlsx')

# Handle Excel error strings and custom NA markers
# In real files these might be '#N/A', '#VALUE!', 'N/A', '-'
df <- read_excel(
  path,
  sheet = 'iris',
  na = c('', 'NA', 'N/A', '#N/A', '-')
)

cat('Missing values per column:\n')
print(colSums(is.na(df)))

Быстрая проверка

Итоги: импорт файлов Excel

library(readxl)
library(purrr)

path <- readxl_example('datasets.xlsx')

# Full workflow: discover, select, read
cat('Sheets available:', paste(excel_sheets(path), collapse=', '), '\n\n')

# Read a specific sheet with explicit types
df <- read_excel(
  path,
  sheet = 'iris',
  col_types = c('numeric','numeric','numeric','numeric','text'),
  n_max = 5
)

print(df)