PDFの中に埋め込まれているテーブルをデータフレームで取り出す、Rで

saito.hiroki

PDFの中に埋め込まれているテーブルからデータを抜き出したいという状況って頻繁にあると思います。まぁもしもそんな状況は一度もなかったとしても、それが簡単にできるということでせっかくなのでちょっと試してみようと思います。Rを使います。

 

きっかけは、Exploratory西田さんのこのツイートです。tabulizerというライブラリを使うようです。

 

この記事も参考になります。

 

WHOの新型コロナウィルスのレポート(https://www.who.int/docs/default-source/coronaviruse/situation-reports/20200424-sitrep-95-covid-19.pdf)からデータを抜き出してみようと思います。f:id:gri-blog:20200428214542p:plain

pdfからtableの部分を抜き出し、データフレームで格納するまでは、もうこれだけです。

library(tabulizer)
library(purrr)
df_combined <- tabulizer::extract_tables("20200424-sitrep-95-covid-19.pdf") %>%
                   purrr::map_dfr(as.data.frame)

すると、データフレームはこんな感じ。 f:id:gri-blog:20200428215504p:plain

せっかくなんできれいにしていきます。 まずは改行コードやフランス語？を置換。

library(dplyr)
library(stringr)
df_combined %>%
  mutate(V1 = str_replace(V1, "\r", " ")) %>%
  mutate(V1 = str_replace(V1, "ã", "a")) %>%
  mutate(V1 = str_replace(V1, "í", "i")) %>%
  mutate(V1 = str_replace(V1, "é", "e")) %>%
  mutate(V1 = str_replace(V1, "ô", "o")) %>%
  mutate(V1 = str_replace(V1, "ç", "c")) %>%

ラオスのように国名が改行されてしまっている場合は、念の為１つ前の行を参照しながら修正。ラオス以外も修正。

  mutate(V1_prev = lag(V1, n = 1)) %>%  # １つ前の行に入っていた文字列
  mutate(V1 = ifelse(V1_prev == "Lao People's" & V1 == "Democratic Republic", "Lao People's Democratic Republic", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "(Commonwealth of" & V1 == "the)", "Northern Mariana Islands (Commonwealth of the)", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "United Republic of" & V1 == "Tanzania", "United Republic of Tanzania", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "Central African" & V1 == "Republic", "Central African Republic", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "Sao Tome and" & V1 == "Principe", "Sao Tome and Principe", V1)) %>%

簡単な修正は国名の一部を元に、正規表現の置換で対応。

  mutate(V1 = gsub("Kosovo.*", "Kosovo", V1)) %>%
  mutate(V1 = gsub(".*d’Ivoire", "Cote d'Ivoire", V1)) %>%
  mutate(V1 = gsub(".*conveyance \\(Diamond.*", "International conveyance (Diamond Princess)", V1)) %>%

国名の列だけでなくTransmission classificationの列も修正。

  mutate(V6 = str_replace(V6, "\r", " ")) %>%
  mutate(V6 = ifelse(V6 == "transmission", "Community transmission", V6)) %>%

見出しや総計の不要な行を削除。

  filter(V1 != "") %>%
  filter(V1 != "Grand total") %>%
  filter(V1 != "Territory/Area  †") %>%
  filter(V2 != "") %>%

作業用に作成した列は不要なので、元々の列のみを選択。

  select(c("V1", "V2", "V3", "V4", "V5", "V6", "V7")) %>%

列名を改めて正しく設定。

  rename("Reporting Country/Territory/Area"=V1
        ,"Total confirmed cases"=V2
        ,"Total confirmed new cases"=V3
        ,"Total deaths"=V4
        ,"Total new deaths"=V5
        ,"Transmission classification"=V6
        ,"Days since last reported case"=V7)

これでデータフレームはなかなかきれいになり、 f:id:gri-blog:20200428214548p:plain

最終的にコードはこうなりました。

library(tabulizer)
library(purrr)
library(dplyr)
library(stringr)

df_combined <- tabulizer::extract_tables("1_downloadFiles/20200424-sitrep-95-covid-19.pdf") %>%
                   purrr::map_dfr(as.data.frame)

df_combined <- df_combined %>%
  mutate(V1 = str_replace(V1, "\r", " ")) %>%
  mutate(V1 = str_replace(V1, "ã", "a")) %>%
  mutate(V1 = str_replace(V1, "í", "i")) %>%
  mutate(V1 = str_replace(V1, "é", "e")) %>%
  mutate(V1 = str_replace(V1, "ô", "o")) %>%
  mutate(V1 = str_replace(V1, "ç", "c")) %>%
  mutate(V1_prev = lag(V1, n = 1)) %>%
  mutate(V1 = ifelse(V1_prev == "Lao People's" & V1 == "Democratic Republic", "Lao People's Democratic Republic", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "(Commonwealth of" & V1 == "the)", "Northern Mariana Islands (Commonwealth of the)", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "United Republic of" & V1 == "Tanzania", "United Republic of Tanzania", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "Central African" & V1 == "Republic", "Central African Republic", V1)) %>%
  mutate(V1 = ifelse(V1_prev == "Sao Tome and" & V1 == "Principe", "Sao Tome and Principe", V1)) %>%
  mutate(V1 = gsub("Kosovo.*", "Kosovo", V1)) %>%
  mutate(V1 = gsub(".*d’Ivoire", "Cote d'Ivoire", V1)) %>%
  mutate(V1 = gsub(".*conveyance \\(Diamond.*", "International conveyance (Diamond Princess)", V1)) %>%
  mutate(V6 = str_replace(V6, "\r", " ")) %>%
  mutate(V6 = ifelse(V6 == "transmission", "Community transmission", V6)) %>%
  filter(V1 != "") %>%
  filter(V1 != "Grand total") %>%
  filter(V1 != "Territory/Area  †") %>%
  filter(V2 != "") %>%
  select(c("V1", "V2", "V3", "V4", "V5", "V6", "V7")) %>%
  rename("Reporting Country/Territory/Area"=V1
        ,"Total confirmed cases"=V2
        ,"Total confirmed new cases"=V3
        ,"Total deaths"=V4
        ,"Total new deaths"=V5
        ,"Transmission classification"=V6
        ,"Days since last reported case"=V7)

write.csv(df_combined, "20200424-sitrep-95-covid-19.csv", row.names = FALSE)

Congoが２つあって、あれWHO間違っている？と思ったが、コンゴってコンゴ共和国とコンゴ民主共和国と２つあるんですね。。。知らなかったです。