Чтение Excel в R: как найти начальную ячейку в грязных таблицах

Я пытаюсь написать R-код для чтения данных из беспорядка старых электронных таблиц. Точное местоположение данных варьируется от листа к листу: единственная константа - это то, что первый столбец является датой, а второй столбец имеет "Ежемесячный возврат" в качестве заголовка. В этом примере данные начинаются в ячейке B5:

Как автоматизировать поиск ячеек Excel для моей строки "Monthly return" с помощью R?

На данный момент, лучшая идея, которую я могу придумать, - загрузить все в R, начиная с ячейки A1, и разобраться в беспорядке в получающихся (огромных) матрицах. Я надеюсь на более элегантное решение

Ответ 1

Я не нашел способ сделать это изящно, но я очень хорошо знаком с этой проблемой (получение данных из отчетов FactSet PA → Excel → R, правильно?). Я понимаю, что разные отчеты имеют разные форматы, и это может быть болью.

Для немного отличающейся версии форматированных таблиц с неудобными формами, я делаю следующее. Это не самый элегантный (требуется два чтения файла), но он работает. Мне нравится читать файл дважды, чтобы убедиться, что столбцы имеют правильный тип и хорошие заголовки. Легко испортить импорт столбцов, поэтому я бы предпочел, чтобы мой код дважды читал файл, а затем очищал столбцы, а значения по умолчанию read_excel, если вы начинаете с правой строки, довольно хороши.

Кроме того, стоит отметить, что на сегодняшний день (2017-04-20), readxl имеет обновление. Я установил новую версию, чтобы убедиться, что это будет очень легко, но я не верю этому случаю, хотя я мог ошибаться.

library(readxl)
library(stringr)
library(dplyr)

f_path <- file.path("whatever.xlsx")

if (!file.exists(f_path)) {
  f_path <- file.choose()
}

# I read this twice, temp_read to figure out where the data actually starts...

# Maybe you need something like this - 
#   excel_sheets <- readxl::excel_sheets(f_path)
#   desired_sheet <- which(stringr::str_detect(excel_sheets,"2 Factor Brinson Attribution"))
desired_sheet <- 1
temp_read <- readxl::read_excel(f_path,sheet = desired_sheet)

skip_rows <- NULL
col_skip <- 0
search_string <- "Monthly Returns"
max_cols_to_search <- 10
max_rows_to_search <- 10

# Note, for the - 0, you may need to add/subtract a row if you end up skipping too far later.
while (length(skip_rows) == 0) {
  col_skip <- col_skip + 1
  if (col_skip == max_cols_to_search) break
  skip_rows <- which(stringr::str_detect(temp_read[1:max_rows_to_search,col_skip][[1]],search_string)) - 0

}

# ... now we re-read from the known good starting point.
real_data <- readxl::read_excel(
  f_path,
  sheet = desired_sheet,
  skip = skip_rows
)

# You likely don't need this if you start at the right row
# But given that all weird spreadsheets are weird in their own way
# You may want to operate on the col_skip, maybe like so:
# real_data <- real_data %>%
#   select(-(1:col_skip))

Ответ 2

Хорошо, в формате был указан для xls, обновлен от csv до корректно предложенной загрузки xls.

library(readxl)
data <- readxl::read_excel(".../sampleData.xls", col_types = FALSE)

Вы получите нечто похожее на:

data <- structure(list(V1 = structure(c(6L, 5L, 3L, 7L, 1L, 4L, 2L), .Label = c("", 
"Apr 14", "GROSS PERFROANCE DETAILS", "Mar-14", "MC Pension Fund", 
"MY COMPANY PTY LTD", "updated by JS on 6/4/2017"), class = "factor"), 
    V2 = structure(c(1L, 1L, 1L, 1L, 4L, 3L, 2L), .Label = c("", 
    "0.069%", "0.907%", "Monthly return"), class = "factor")), .Names = c("V1", 
"V2"), class = "data.frame", row.names = c(NA, -7L))

то вы можете динамически фильтровать ячейку "Ежемесячный возврат" и идентифицировать вашу матрицу.

targetCell <- which(data == "Monthly return", arr.ind = T)
returns <- data[(targetCell[1] + 1):nrow(data), (targetCell[2] - 1):targetCell[2]]

Ответ 3

С пакетом общего назначения, например readxl, вам придется читать дважды, если вы хотите использовать автоматическое преобразование типов. Я предполагаю, что у вас есть какая-то верхняя граница по количеству стартовых рядов на фронте? Здесь я предположил, что это было 10. Я повторяю рабочие листы в одной книге, но код будет выглядеть очень похоже, если итерация над книгами. Я бы написал одну функцию для обработки одного рабочего листа или рабочей книги, а затем использовал lapply() или purrr::map(). Эта функция будет инкапсулировать прочитанное чтение пропусков и "реальное" чтение.

library(readxl)

two_passes <- function(path, sheet = NULL, n_max = 10) {
  first_pass <- read_excel(path = path, sheet = sheet, n_max = n_max)
  skip <- which(first_pass[[2]] == "Monthly return")
  message("For sheet '", if (is.null(sheet)) 1 else sheet,
          "' we'll skip ", skip, " rows.")  
  read_excel(path, sheet = sheet, skip = skip)
}

(sheets <- excel_sheets("so.xlsx"))
#> [1] "sheet_one" "sheet_two"
sheets <- setNames(sheets, sheets)
lapply(sheets, two_passes, path = "so.xlsx")
#> For sheet 'sheet_one' we'll skip 4 rows.
#> For sheet 'sheet_two' we'll skip 6 rows.
#> $sheet_one
#> # A tibble: 6 × 2
#>         X__1 `Monthly return`
#>       <dttm>            <dbl>
#> 1 2017-03-14          0.00907
#> 2 2017-04-14          0.00069
#> 3 2017-05-14          0.01890
#> 4 2017-06-14          0.00803
#> 5 2017-07-14         -0.01998
#> 6 2017-08-14          0.00697
#> 
#> $sheet_two
#> # A tibble: 6 × 2
#>         X__1 `Monthly return`
#>       <dttm>            <dbl>
#> 1 2017-03-14          0.00907
#> 2 2017-04-14          0.00069
#> 3 2017-05-14          0.01890
#> 4 2017-06-14          0.00803
#> 5 2017-07-14         -0.01998
#> 6 2017-08-14          0.00697

Ответ 4

В этих случаях важно знать возможные условия ваших данных. Я предполагаю, что вы хотите удалить только столбцы и строки, которые не конфликтуют с вашей таблицей.

У меня есть эта книга Excel:

Я добавил 3 пустых столбца слева, потому что когда я загрузил R в один столбец, программа опускает их. То для подтверждения, что R опускает пустые столбцы слева.

Сначала: данные загрузки

library(xlsx)
dat <- read.xlsx('book.xlsx', sheetIndex = 1)
head(dat)

            MY.COMPANY.PTY.LTD            NA.
1             MC  Pension Fund           <NA>
2    GROSS PERFORMANCE DETAILS           <NA>
3 updated  by IG on 20/04/2017           <NA>
4                         <NA> Monthly return
5                       Mar-14         0.0097
6                       Apr-14          6e-04

Во-вторых: я добавил несколько столбцов с значениями NA и '' в том случае, если ваши данные содержат некоторые

dat$x2 <- NA
dat$x4 <- NA
head(dat)

            MY.COMPANY.PTY.LTD            NA. x2 x4
1             MC  Pension Fund           <NA> NA NA
2    GROSS PERFORMANCE DETAILS           <NA> NA NA
3 updated  by IG on 20/04/2017           <NA> NA NA
4                         <NA> Monthly return NA NA
5                       Mar-14         0.0097 NA NA
6                       Apr-14          6e-04 NA NA

В-третьих: удалите столбцы, когда все значения NA и ''. Мне приходится иметь дело с такими проблемами в прошлом

colSelect <- apply(dat, 2, function(x) !(length(x) == length(which(x == '' | is.na(x)))))
dat2 <- dat[, colSelect]
head(dat2)

            MY.COMPANY.PTY.LTD            NA.
1             MC  Pension Fund           <NA>
2    GROSS PERFORMANCE DETAILS           <NA>
3 updated  by IG on 20/04/2017           <NA>
4                         <NA> Monthly return
5                       Mar-14         0.0097
6                       Apr-14          6e-04

В-четвертых: сохраняйте только строки с полными наблюдениями (это то, что я полагаю из вашего примера)

rowSelect <- apply(dat2, 1, function(x) !any(is.na(x)))
dat3 <- dat2[rowSelect, ]
head(dat3)

   MY.COMPANY.PTY.LTD     NA.
5              Mar-14  0.0097
6              Apr-14   6e-04
7              May-14  0.0189
8              Jun-14   0.008
9              Jul-14 -0.0199
10             Ago-14 0.00697

Наконец, если вы хотите сохранить заголовок, вы можете сделать что-то вроде этого:

colnames(dat3) <- as.matrix(dat2[which(rowSelect)[1] - 1, ])

или

colnames(dat3) <- c('Month', as.character(dat2[which(rowSelect)[1] - 1, 2]))
dat3

    Month Monthly return
5  Mar-14         0.0097
6  Apr-14          6e-04
7  May-14         0.0189
8  Jun-14          0.008
9  Jul-14        -0.0199
10 Ago-14        0.00697

Ответ 5

Вот как я бы справился с этим.

ШАГ 1
Прочтите таблицу Excel в заголовках without.

ШАГ 2
Найти индекс строки для строки Monthly return в этом случае

ШАГ 3
Фильтр из идентифицированной строки (или столбца или и того и другого), немного уменьшите и сделайте.

Вот как выглядит примерная функция. Он работает для вашего примера независимо от того, где он находится в электронной таблице. Вы можете играть с regex, чтобы сделать его более надежным.

Определение функции:

library(xlsx)
extract_return <-  function(path = getwd(), filename = "Mysheet.xlsx", sheetnum = 1){
                       filepath = paste(path, "/", filename, sep = "")
                       input = read.xlsx(filepath, sheetnum, header = FALSE)
                       start_idx = which(input == "Monthly return", arr.ind = TRUE)[1]
                       output = input[start_idx:dim(input)[1],]
                       rownames(output) <- NULL
                       colnames(output) <- c("Date","Monthly Return")
                       output = output[-1, ]  
                       return(output)
                  }

Пример:

final_df <- extract_return(
                path = "~/Desktop", 
                filename = "Apr2017.xlsx", 
                sheetnum = 2)

Неважно, сколько строк или столбцов у вас есть, идея остается той же. Попробуйте и дайте мне знать.

Ответ 6

grep("2014",dat)[1]

Это дает вам первый столбец с годом. Или используйте "-14" или все, что у вас есть в течение многих лет. Подобным образом grep ( "Monthly", dat) [1] дает вам второй столбец