Function

コメント・参照等
  • 機械受注統計調査報告ファイル(内閣府)を読み込み整形する関数
  • https://www.esri.cao.go.jp/jp/stat/juchu/juchu.html
  • chouki-1.xls 月次シート:2,3
  • chouki-2.xls 月次シート:2,5
  • chouki-2p.xls 月次シート:2,5
  • chouki-3.xls 月次シート:2,3
  • chouki-4.xls 月次シート:2,3
  • chouki-5.xls 月次シート:2,3

FUN = f_read_machinery_orders.r
function (download_folder, xlsfile, sheet) 
{
    library(XLConnect)
    library(Nippon)
    path_to_folder <- paste0("C:/Users/", Sys.info()["user"], download_folder)
    setwd(path_to_folder)
    buf0 <- readWorksheetFromFile(xlsfile, sheet = sheet, check.names = F, header = F)
    title_row <- head(grep("月次", buf0[, 3]), 1)
    title_col <- head(grep("単位", buf0[title_row, ]), 1)
    colname_row <- (grep("^[A-Z]", buf0[, 3])[1] + 1):(grep("^[A-Z]", buf0[, 3])[2] - 1)
    sheet_title <- zen2han(paste0(buf0[title_row, c(3, title_col)], collapse = ""))
    buf1 <- f_fill_cells_column_direction(df = buf0, row = colname_row[1], below_check = T)
    buf1[, 1] <- paste0(buf1[, 1], "-", buf1[, 2], "-1")
    colnames(buf1) <- gsub("NA|\\s| |\\(.\\)$", "", sapply(apply(buf1[colname_row, ], 2, function(x) paste0(x, collapse = "")), zen2han))
    buf2 <- buf1[!is.na(as.numeric(buf0[, 1])), colnames(buf1) != ""]
    colnames(buf2)[1] <- "Date"
    buf2$Date <- as.Date(buf2$Date)
    buf2[, -1] <- apply(buf2[, -1], 2, function(x) as.numeric(gsub(",", "", x)))
    row.names(buf2) <- NULL
    colnames(buf2)[-1] <- paste0(sheet_title, ":", colnames(buf2)[-1])
    return(buf2)
}

Asset Management Consulting Corporation