データ穴リストのブログ

すべては喰いっぱぐれないために

shinyにExcelの代わりをさせよう〜VLOOKUP編〜

こんにちは!

Excel関数大好き、R大好き中年ダメ人間だよ!

ボクとExcelとの出会いは、今の人生の半分より昔、
「生徒会室で校舎の略地図を罫線でかく」ために触れたのが初めてだよ!

そんなボクも、SUM、IF、INDEX、MID、SUMPRODUCT、OFFSETと、数々の関数と夜を共に過ごしてきたけど*1
やっぱりこれまでの人生で一番のExcelでの大仕事は、
Excelのセルサイズを細かな方眼紙状にして当時の職場の事務所引越しのレイアウト案をExcelで作ったことかな!

ちなみに自宅の引越しでも毎回間取り図書いてた!
Excelで!

しかし別れはやってくる

そんなExcel教の私も、Rの便利さに慣れ、なるべく業務のほとんどをRでこなしたくなってきた近年。
出会いがあれば、また別れも訪れるものですね。。。

ということで、なるべくRに触れてたいんだけど、
Excelで50万件のデータ捌けません〜」とか、
「計算中って出て1%進むのに1分くらいかかります〜」とか言われて、
その度にRでJOINさせてあげるなんて雑務をする羽目になったりするので、
「そんなもんRでやれば一瞬だよ☆」
「R、Excel関数とほとんど同じだよ☆慣れたらむしろ分かりやすいよ☆」

なーんて布教をしてみようと試みるものの、
「言語とかマヂ無理っす」とか
「えー面倒だからmrkさんがやってくださいよぉ〜」
とか言われて、
その度に涙を枕で濡らしていたものです。゚(゚´ω`゚)゚。

そこで奥さん、shinyですよ!

そんなことイチイチやってられるか!ってんで、
前々から進めたかったshinyアプリ作成のネタとして、「RでVLOOKUPをする」アプリを作ってみました。

まぁVLOOKUPといってもやるのはJOINです。
参照列数とかそういうのは無い。

まぁそこは

こまけぇこたぁ
    いいんだよ!!

の精神で、SEO対策的にもココはひとつ、「RでVLOOKUPをやります」と宣言します。いいね?

で、とりあえずやってみました

真面目にshinyの仕組みをまとめるのは今度にするとして(仕組みって言っても雰囲気ね?)、
まずはShiny内でやりたい処理を考えてみる。

# はじめに----

# やりたいことはコレ
left_join(iris, iris %>% gather(key, val, -Species), by = c("Species" = "Species")) %>% View()

# テスト用データ用意
write.csv(iris, file = "/home/rstudio/iris.csv", row.names = FALSE)

iris %>% gather(key, val, -Species) %>% write.csv(., file = "/home/rstudio/iris_ga.csv", row.names = FALSE)

非常にシンプル。

とりあえずアプリ化にあたり、以下の要素を盛り込んでみた

  • ファイルのアップロード(2種類)
  • アップロードした各テーブルの表示
  • 結合のキー変数の選択
  • ポチッとな(結合)
  • マージ後テーブルの表示
  • マージ後データのダウンロード

シンプルだけど、割とやることいっぱい。

で、結合にはdplyrを使ったんだけど、
dplyrの***_JOIN()は結合キーを文字列で指定する必要があって、これで苦戦しました。

結果教えていただいて出来たのがコチラ

require(shiny)
require(tidyverse)
require(readxl)
require(openxlsx)

# UI部分----
ui <-   
  fluidPage(
    titlePanel("RでVLOOKUPツール(csv編)"),
    sidebarLayout(
      sidebarPanel(
        h4("1.ファイルを選択↓"),
        # ファイル1選択
        fileInput("inputfile_1", "File1を選ぶ",
                  accept = c(
                    "text/csv",
                    "text/comma-separated-values,text/plain",
                    ".csv")
        ),
        tags$hr(),
        # ファイル2選択
        fileInput("inputfile_2", "File2を選ぶ",
                  accept = c(
                    "text/csv",
                    "text/comma-separated-values,text/plain",
                    ".csv")
        ),
        tags$hr(),
        # 変数選択
        h4("2.キーにする変数を選択"),
        htmlOutput("file1_col"),
        htmlOutput("file2_col"),
        tags$hr(),
        # マージ
        h4("3.マージ&DL"),
        radioButtons("join_type", label = "マージ方法を選択",
                     choices = list("left_join",
                                    "right_join",
                                    "inner_join",
                                    "full_join",
                                    "anti_join")),
        actionButton("submit", "マージ"),
        downloadButton('downloadData', 'ダウンロード')
      ),
      # メインパネルはテーブル出力のみ
      mainPanel(
        tabsetPanel(type = "tabs",
                    tabPanel("Table1", tableOutput('table1')),
                    tabPanel("Table2", tableOutput('table2')),
                    tabPanel("Merged_Table", tableOutput('merged_table'))
        )
      )
    )
  )

# server----

server <- function(input, output, session) {

  # ファイル1の読み込み
  observeEvent(input$inputfile_1, {
    
    table_file = reactive(read.csv(input$inputfile_1$datapath))
    output$table1 = renderTable(table_file())
    
    output$file1_col = renderUI({
      selectInput("x", "ファイル1", colnames(table_file()))
    })
  })
  
  # ファイル2の読み込み
  observeEvent(input$inputfile_2, {

    table_file = reactive(read.csv(input$inputfile_2$datapath))
    output$table2 = renderTable(table_file())

    output$file2_col = renderUI({
      selectInput("y", "ファイル2", colnames(table_file()))
    })
  })
  
  # テーブルのマージ
  observeEvent(input$submit, {
    # ファイル指定
    table_file_1 = reactive(read.csv(input$inputfile_1$datapath))
    table_file_2 = reactive(read.csv(input$inputfile_2$datapath))
    
    # by用変数名指定
    by_1 = input$x
    by_2 = input$y
    
    # マージ方法指定
    choise = input$join_type
    
    output$merged_table = renderTable({
      eval(
        parse(
          text = paste0(
            choise, "(table_file_1(), table_file_2(), by = setNames(by_1, by_2))"
          )
        )
      )
    })
  })

  output$downloadData <- downloadHandler(
    filename = "merged_data.csv",
    content = function(file) {
      # ファイル指定
      table_file_1 = reactive(read.csv(input$inputfile_1$datapath))
      table_file_2 = reactive(read.csv(input$inputfile_2$datapath))
      
      # by用変数名指定
      by_1 = input$x
      by_2 = input$y
      
      # マージ方法指定
      choise = input$join_type
      
      write.csv(x = eval(
                    parse(text = paste0(
                       choise, "(table_file_1(), table_file_2(), by = setNames(by_1, by_2))")
                       )        ),
                file, na = "", row.names = FALSE)
    }
  )
  }

  # Run the application ----
shinyApp(ui = ui, server = server)

出来たのでここまででQiitaにまとめました。
くどくど与太話を読みたくない場合はそちらへGO!

CSVだけでExcelは語れぬ

とりあえず作ったのはread.csv()を基本としたツールですが、それだけじゃ満足できないワガママボディがいっぱいいそうです。

ちなみにCSV、コンマ・セパレーテッド・バリューという言葉だけでなく、
クリエーティング・シェアド・バリュー(共通価値の創造)なる訳もあります。
ほざけ。

ということで、Excelファイルも対応させるよね。
Excelファイルはreadxlパッケージを使います。

ちなみにご存知Excel御大にはCSVとは違ってシートという概念がございます。
当然選べるようにしたい。

あと、せっかくdplyrでマージ後させるんならleft_join()だけではなく、各種JOINも選びたくなるよね!
…ってんで、そこも選べるようにしました。

文字列を繋げてRの処理として評価してやる、というもの。

こういうのがNSEっていうの?逆?
オジサンわかんない!

あ、あとあと忘れちゃいけない、日本語を使うんなら、文字コードも対応しないと。
とりあえず想定ユーザはWindowsの人だから、SJISのみです。

で、作ったのがコチラ

require(shiny)
require(tidyverse)
require(readxl)
require(openxlsx)

# UI部分----
ui <-   
  fluidPage(
    titlePanel("RでVLOOKUPツール"),
    sidebarLayout(
      sidebarPanel(
        helpText(paste0("説明:1~5の手順で2つのファイルをマージしてください。","\r\n","csvとExcelの両方対応してます。")),
        h4("1.ファイルを選択↓"),
        # ファイル1選択
        radioButtons("file_type_1", label = "ファイル1の形式を選択",
                     choices = list("csv",
                                    "Excel")),
        fileInput("inputfile_1", "File1を選ぶ",
                  accept = c(
                    "text/csv",
                    "text/comma-separated-values,text/plain",
                    ".csv",
                    ".xlsx")
        ),
        htmlOutput("file1_sheet"),
        tags$hr(),
        # ファイル2選択
        radioButtons("file_type_2", label = "ファイル2の形式を選択",
                     choices = list("csv",
                                    "Excel")),
        fileInput("inputfile_2", "File2を選ぶ",
                  accept = c(
                    "text/csv",
                    "text/comma-separated-values,text/plain",
                    ".csv",
                    ".xlsx")
        ),
        htmlOutput("file2_sheet"),
        tags$hr(),
        # ファイルゲット
        h4("2.両ファイルを取得↓"),
        actionButton("getfiles", "ファイル取得"),
        tags$hr(),
        # 変数選択
        h4("3.キーにする変数を選択"),
        htmlOutput("file1_col"),
        htmlOutput("file2_col"),
        tags$hr(),
        # マージ
        h4("4.マージ"),
        radioButtons("join_type", label = "マージ方法を選択",
                     choices = list("left_join",
                                    "right_join",
                                    "inner_join",
                                    "full_join",
                                    "anti_join")),
        actionButton("submit", "マージ"),
        tags$hr(),
        h4("5.DL↓"),
        downloadButton('downloadData', 'ダウンロード(SJIS)')
      ),
      # メインパネルはテーブル出力のみ
      mainPanel(
        tabsetPanel(type = "tabs",
                    tabPanel("Table1", tableOutput('table1')),
                    tabPanel("Table2", tableOutput('table2')),
                    tabPanel("Merged_Table", tableOutput('merged_table'))
        )
      )
    )
  )


# ファイルアップロード上限を100MBに変更 ----
options(shiny.maxRequestSize=100*1024^2) 

# server部分----

server <- function(input, output, session) {
  # ファイル1のシート情報選択
  observeEvent(input$inputfile_1, {
    
    if(input$file_type_1 == "Excel"){
      sheet1 = reactive(excel_sheets(path = input$inputfile_1$datapath)
      )
      
      output$file1_sheet = renderUI({ 
        selectInput("sheet_x", "シート名選択(ファイル1)", sheet1())
      }) 
    }})
  
  # ファイル2のシート情報選択
  observeEvent(input$inputfile_2, {
    
    if(input$file_type_2 == "Excel"){
      sheet2 = reactive(excel_sheets(path = input$inputfile_2$datapath)
      )
      
      output$file2_sheet = renderUI({ 
        selectInput("sheet_y", "シート名選択(ファイル2)", sheet2())
      }) 
    }})
  
  # ファイル1&2の読み込み
  observeEvent(input$getfiles, {
    # ファイル1
    if(input$file_type_1 == "Excel"){ # EXCELの場合
      table_file1 = reactive(read_excel(path = input$inputfile_1$datapath, 
                                        sheet = input$file1_sheet,
                                        na = "NULL"))
      output$table1 = renderTable(table_file1())
      output$file1_col = renderUI({ 
        selectInput("x", "ファイル1", colnames(table_file1()))
      })
    } else { # CSVの場合
      table_file1 = reactive(read.csv(input$inputfile_1$datapath, fileEncoding = "Shift-JIS"))
      output$table1 = renderTable(table_file1())
      
      output$file1_col = renderUI({ 
        selectInput("x", "ファイル1", colnames(table_file1()))
      })
    }
    
    # ファイル2
    if(input$file_type_2 == "Excel"){ # EXCELの場合
      table_file2 = reactive(read_excel(path = input$inputfile_2$datapath, 
                                        sheet = input$file2_sheet,
                                        na = "NULL"))
      output$table2 = renderTable(table_file2())
      output$file2_col = renderUI({ 
        selectInput("y", "ファイル2", colnames(table_file2()))
      })
    } else { # CSVの場合
      table_file2 = reactive(read.csv(input$inputfile_2$datapath, fileEncoding = "Shift-JIS"))
      output$table2 = renderTable(table_file2())
      
      output$file2_col = renderUI({ 
        selectInput("y", "ファイル2", colnames(table_file2()))
      })
    }
  }
  )
  
  
  # テーブルのマージ
  observeEvent(input$submit, {
    
    if(input$file_type_1 == "Excel"){
    # ファイル1指定
      table_file_1 = reactive(read_excel(path = input$inputfile_1$datapath, 
                                         sheet = input$file1_sheet,
                                         na = "NULL"))
    } else {
      table_file_1 = reactive(read.csv(input$inputfile_1$datapath))
    }
    
    if(input$file_type_2 == "Excel"){
    # ファイル2指定
      table_file_2 = reactive(read_excel(path = input$inputfile_2$datapath, 
                                         sheet = input$file2_sheet,
                                         na = "NULL"))
    } else {
      table_file_2 = reactive(read.csv(input$inputfile_2$datapath))
    }
    
    # by用変数名指定
    by_1 = input$x
    by_2 = input$y
    
    # マージ方法指定
    choise = input$join_type
    
    output$merged_table = renderTable({
      eval(
        parse(
          text = paste0(
            choise, "(table_file_1(), table_file_2(), by = setNames(by_1, by_2))"
          )
        )
      )
    })
  })
  
  output$downloadData <- downloadHandler(
    filename = "merged_data.csv",
    content = function(file) {
      if(input$file_type_1 == "Excel"){
        # ファイル1指定
        table_file_1 = reactive(read_excel(path = input$inputfile_1$datapath, 
                                           sheet = input$file1_sheet,
                                           na = "NULL"))
      } else {
        table_file_1 = reactive(read.csv(input$inputfile_1$datapath))
      }
      
      if(input$file_type_2 == "Excel"){
        # ファイル2指定
        table_file_2 = reactive(read_excel(path = input$inputfile_2$datapath, 
                                           sheet = input$file2_sheet,
                                           na = "NULL"))
      } else {
        table_file_2 = reactive(read.csv(input$inputfile_2$datapath))
      }
      
      # by用変数名指定
      by_1 = input$x
      by_2 = input$y
      
      # マージ方法指定
      choise = input$join_type
      
      write.csv(x = eval(
        parse(text = paste0(
          choise, "(table_file_1(), table_file_2(), by = setNames(by_1, by_2))")
        )        ),
        file, na = "", row.names = FALSE, fileEncoding = "Shift-JIS")
    }
  )
}

# Run the application ----
shinyApp(ui = ui, server = server)

最初のものから追加したのは以下

  • アップロードファイル形式をcsvExcelか選択(判断は内部でしてやらない)
  • Excelの場合はアップロードするシート名を選択
  • JOIN方式を選択
  • 文字コード対応(in-out)
  • ヘルプテキスト(改行上手くいってない)

出力自体はcsvのみ。面倒だから。

あ、も一つ忘れてた

  • アップロードファイルのサイズ上限をデフォルトの5MBから100MBに変更

そもそも(超)軽量なテーブルなんざVLOOKでいいので、
これが出来なきゃなんの意味もないガラクタになるところだった。。

ExcelVLOOKUPより超高速でJOIN!

ということで、何でもかんでもExcelでやらない、
ちょっとしたことでとても業務がラクになることを体験してもらうべく、
shinyでカンタンなデータマージツールを作りました。

shiny、RUNしてみて初めて抜け漏れが多々見つかってコレもかよ。。。と自分にげんなりしましたが、
非常にシンプルで初めてのアプリ作成でしたが比較的短時間でここまで出来ました!(byの変数指定は困ったけど)

でもいろんな方々の資料やr-wakalangコミュニティのお蔭です!Rおじさんに乾杯!!

次はやったことのない貴方も!

Enjoy, shiny!!!

*1:残業で。