今週も特にありません

進捗どうですか?

等幅の棒グラフを描画する ggplot2::position_dodge2

ggplot2で可視化した時に棒グラフの幅が変わって、変な見た目になってしまうことにたまに出会す。それを position_dodge2 で調整するメモ。

2つのウェブサイトがあり、ある期間に訪れた頻度ごとのユーザ数が記録されたようなデータがあるとする。

> library(tidyverse)
> 
> set.seed(1357)
> log_tbl <- tibble(
+     site = c(rep("A", 10), rep("G", 7)),
+     freq = c(1:10, 1:7),
+     user = c(rpois(10, 10), rpois(7, 10))
+ )

サイトGの方は、8回以上の頻度で訪れたユーザが存在しない。

> log_tbl
# A tibble: 17 x 3
   site   freq  user
   <chr> <int> <int>
 1 A         1    11
 2 A         2    15
 3 A         3     6
 4 A         4     9
 5 A         5    10
 6 A         6     7
 7 A         7    13
 8 A         8    10
 9 A         9     6
10 A        10    10
11 G         1     6
12 G         2     7
13 G         3    15
14 G         4    11
15 G         5    12
16 G         6    13
17 G         7    11

これを普通に可視化すると、サイトAの頻度8回以上の棒幅が広くなってしまう。

> log_tbl %>%
+     ggplot(aes(x = freq, y = user, fill = site)) +
+     geom_bar(stat = "identity", position = "dodge") +
+     scale_x_discrete(limits = 1:10) +
+     theme(legend.position = "top",
+           legend.title = element_text(size = 15),
+           legend.text = element_text(size = 12),
+           axis.title = element_text(size = 15),
+           axis.text = element_text(size = 15))

f:id:masaqol:20200930231913p:plain

そこで、position_dodge2 を活用して、等幅の見た目に調整してやる。

> log_tbl %>%
+     ggplot(aes(x = freq, y = user, fill = site)) +
+     geom_bar(stat = "identity", 
+              position = position_dodge2(preserve = "single", padding = 0)) +
+     scale_x_discrete(limits = 1:10) +
+     theme(legend.position = "top",
+           legend.title = element_text(size = 15),
+           legend.text = element_text(size = 12),
+           axis.title = element_text(size = 15),
+           axis.text = element_text(size = 15))

f:id:masaqol:20200930232246p:plain

padding 設定も行うことで、見た目を変更することができる。 多少書くことが増えてしまうが、普段から position_dodge2 を利用しておくのも良さそうである。

ggplot2.tidyverse.org

住所を都道府県と市区町村に分割する

Hiveにおける処理を想定したメモ。

住所が書かれたカラムがあり、それを都道府県単位、市区町村単位でまとめ上げて集計などしたい場合がある。 都道府県は問題ないが、市区町村の中には、"市"、"町"、"村"が含まれる自治体名があるために単純に分割することができない。 そこで、地道に場合分けすることで対処する(2020年6月現在の市区町村名)。

WITH a AS (
  SELECT '東京都町田市原町田1丁目1−11' address
), b AS (
  SELECT
    address,
    REGEXP_EXTRACT(address, '^([^市区町村]{2}[都道府県]|[^市区町村]{3}県)') prefecture,
    REGEXP_PEPLACE(address, REGEXP_EXTRACT(address, '^([^市区町村]{2}[都道府県]|[^市区町村]{3}県)'), '') city
  FROM
    a
)

SELECT
  address,
  prefecture,
  CASE
    -- 「区」
    WHEN city RLIKE '' THEN REGEXP_EXTRACT(city, '^(.+区)')
    -- 「市」の入った市
    WHEN city RLIKE '^市川市' THEN '市川市'
    WHEN city RLIKE '^市原市' THEN '市原市'
    WHEN city RLIKE '^野々市市' THEN '野々市市'
    WHEN city RLIKE '^四日市市' THEN '四日市市'
    WHEN city RLIKE '^廿日市市' THEN '廿日市市'
    -- 「町」の入った市
    WHEN city RLIKE '^町田市' THEN '町田市'
    WHEN city RLIKE '^十日町市' THEN '十日町市'
    WHEN city RLIKE '^大町市' THEN '大町市'
    -- 「村」の入った市
    WHEN city RLIKE '^田村市' THEN '田村市'
    WHEN city RLIKE '^東村山市' THEN '東村山市'
    WHEN city RLIKE '^武蔵村山市' THEN '武蔵村山市'
    WHEN city RLIKE '^村山市' THEN '村山市'
    WHEN city RLIKE '^羽村市' THEN '羽村市'
    WHEN city RLIKE '^村上市' THEN '村上市'
    WHEN city RLIKE '^大村市' THEN '大村市'
    -- 「市」を含む町村郡
    WHEN city RLIKE '^余市郡.+[町村]' THEN REGEXP_EXTRACT(city, '^余市郡.+[町村]')
    WHEN city RLIKE '^芳賀郡.+町' THEN REGEXP_EXTRACT(city, '^芳賀郡.+町')
    WHEN city RLIKE '^中新川郡.+[町村]' THEN REGEXP_EXTRACT(city, '^中新川郡.+[町村]')
    WHEN city RLIKE '^西八代郡市川三郷町' THEN '西八代郡市川三郷町' 
    WHEN city RLIKE '^神崎郡.+町' THEN REGEXP_EXTRACT(city, '^神崎郡.+町')
    WHEN city RLIKE '^高市郡.+[町村]' THEN REGEXP_EXTRACT(city, '^高市郡.+[町村]')
    WHEN city RLIKE '^吉野郡.+[町村]' THEN REGEXP_EXTRACT(city, '^吉野郡.+[町村]')
    -- 「町」を含む町村郡
    WHEN city RLIKE '^杵島郡.+町' THEN REGEXP_EXTRACT(city, '^杵島郡.+町')
    -- 「村」を含む町村郡
    WHEN city RLIKE '^柴田郡.+町' THEN REGEXP_EXTRACT(city, '^柴田郡.+町')
    WHEN city RLIKE '^東村山郡.+町' THEN REGEXP_EXTRACT(city, '^東村山郡.+町')
    WHEN city RLIKE '^西村山郡.+町' THEN REGEXP_EXTRACT(city, '^西村山郡.+町')
    WHEN city RLIKE '^北村山郡大石田町' THEN '北村山郡大石田町'
    WHEN city RLIKE '^田村郡.+町' THEN REGEXP_EXTRACT(city, '^田村郡.+町')
    WHEN city RLIKE '^佐波郡玉村町' THEN '佐波郡玉村町'
    -- その他
    WHEN city RLIKE '^([^市区町村]*[市区町村])' THEN REGEXP_EXTRACT(city, '^([^市区町村]*[市区町村])')
    ELSE NULL
  END city
FROM
  b
;

もっと効率的に書けそうではあります。

これを実行した結果は、以下のようになる。

address prefecture  city
東京都町田市原町田1丁目1-11 東京都  町田市

uub.jp

dplyr グループごとに指数化

グループごとにある時点における数値をもとにした指数化を行いたい。

何らかの売上データが時系列であるとする。

> library(tidyverse)
> library(lubridate)
> 
> sales_tbl <- tibble(
+     sales_date = rep(seq(ymd("2020-05-01"), ymd("2020-05-31"), by = "1 day"), 3),
+     category = c(rep("food", 31), rep("book", 31), rep("tool", 31)),
+     sales_cnt = rpois(31 * 3, 10)
+ )
> 
> sales_tbl
# A tibble: 93 x 3
   sales_date category sales_cnt
   <date>     <chr>        <int>
 1 2020-05-01 food            11
 2 2020-05-02 food             8
 3 2020-05-03 food             8
 4 2020-05-04 food            15
 5 2020-05-05 food            12
 6 2020-05-06 food            10
 7 2020-05-07 food             6
 8 2020-05-08 food            10
 9 2020-05-09 food            15
10 2020-05-10 food            11
# … with 83 more rows

特定の日付の売上を1として、グループごとにその後の売上を指数化する。

> sales_index_tbl <- sales_tbl %>%
+     group_by(category) %>%
+     mutate(sales_index = sales_cnt / sales_cnt[sales_date == ymd("2020-05-01")])
> 
> sales_index_tbl %>%
+     ggplot(aes(x = sales_date, y = sales_index)) +
+     geom_line(aes(colour = category), size = 2) +
+     scale_x_date(breaks = "1 week", minor_breaks = "1 day") +
+     theme(legend.position = "top")

f:id:masaqol:20200520180044p:plain

それぞれのグループごとに5月1日時点を1として、指数化されることがわかる。 []による条件絞り込みをmutateの中で効かせることができる。

Spark 集約関数 collect_list, collect_set

配列に集約する操作であるので、aggの中で関数を適用することになる。

scala> val df = Seq(
  ("p00001", "food", 200),
  ("p00002", "food", 500),
  ("p00003", "food", 800),
  ("p00004", "food", 500),
  ("p00005", "food", 700),
  ("p00006", "book", 900),
  ("p00007", "book", 1500),
  ("p00008", "book", 1200),
  ("p00009", "book", 2200),
  ("p00010", "book", 3000),
  ("p00011", "tool", 20000),
  ("p00012", "tool", 50000),
  ("p00013", "tool", 15000),
  ("p00014", "tool", 60000),
  ("p00015", "tool", 35000)
).toDF("id", "category", "sales")

scala > val idArrayDF = DF.
  group_by('category).
  agg(collect_list('id).as("id_array"), sum('sales).as("sales_sum"))

scala > idArrayDF.show()
+--------+--------------------+---------+
|category|            id_array|sales_sum|
+--------+--------------------+---------+
|    food|[p00001, p00002, ...|     2700|
|    tool|[p00011, p00012, ...|   180000|
|    book|[p00006, p00007, ...|     8800|
+--------+--------------------+---------+

重複を削除する場合には、collect_setを使うことになる。

さらに、配列になったものを分割して抽出する場合には、explodeを適用すればよい。

scala > idArrayDF.
  select(
    'category,
    explode('id_array).as("id")
  ).show()
+--------+------+
|category|    id|
+--------+------+
|    food|p00003|
|    food|p00005|
|    food|p00002|
|    food|p00004|
|    food|p00001|
|    tool|p00014|
|    tool|p00012|
|    tool|p00015|
|    tool|p00011|
|    tool|p00013|
|    book|p00010|
|    book|p00009|
|    book|p00007|
|    book|p00008|
|    book|p00006|
+--------+------+

jaceklaskowski.gitbooks.io

日次データの欠損を埋める tidyr::complete

tidyr::completeに関するメモ。

日次データが欠損している(ある日に一件も事象が観測されていない)場合のデータに出会すことがよくある。

> library(tidyverse)
> 
> stopcovid19_tbl_tmp <- read_csv("https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv")
> stopcovid19_tbl <- stopcovid19_tbl_tmp %>%
+     select("公表_年月日", "患者_年代", "患者_性別") %>%
+     rename(公表日 = 公表_年月日,
+            年代 = 患者_年代,
+            性別 = 患者_性別)
> stopcovid19_tbl
# A tibble: 1,116 x 3
   公表日     年代  性別 
   <date>     <chr> <chr>
 1 2020-01-24 40代  男性 
 2 2020-01-25 30代  女性 
 3 2020-01-30 30代  女性 
 4 2020-02-13 70代  男性 
 5 2020-02-14 50代  女性 
 6 2020-02-14 70代  男性 
 7 2020-02-15 80代  男性 
 8 2020-02-15 50代  女性 
 9 2020-02-15 50代  男性 
10 2020-02-15 70代  男性 
# … with 1,106 more rows

何かと時系列で日次でデータがしっかり埋まっていて欲しいという場合も多くある。公表日ごとにカウントした人数と累積人数のデータを集計する場合には、以下のようになる。

> stopcovid19_tbl %>%
+   group_by(公表日) %>%
+   summarise(人数 = n()) %>%
+   complete(公表日 = seq(min(公表日), max(公表日), by = "day"),
+            fill = list(人数 = 0)) %>%
+   mutate(累積 = cumsum(人数))
# A tibble: 74 x 3
   公表日      人数  累積
   <date>     <dbl> <dbl>
 1 2020-01-24     1     1
 2 2020-01-25     1     2
 3 2020-01-26     0     2
 4 2020-01-27     0     2
 5 2020-01-28     0     2
 6 2020-01-29     0     2
 7 2020-01-30     1     3
 8 2020-01-31     0     3
 9 2020-02-01     0     3
10 2020-02-02     0     3
# … with 64 more rows

さらに、ここで、公表日、年代、性別ごとのように、複数の変数ごとにカウントした人数と累積人数を集計するには、以下のようになる。

> stopcovid19_tbl %>%
+   group_by(公表日, 年代, 性別) %>%
+   summarise(人数 = n()) %>%
+   ungroup() %>%
+   complete(公表日 = seq(min(公表日), max(公表日), by = "day"),
+            fill = list(人数 = 0)) %>%
+   mutate(累積 = cumsum(人数))
# A tibble: 339 x 5
   公表日     年代  性別   人数  累積
   <date>     <chr> <chr> <dbl> <dbl>
 1 2020-01-24 40代  男性      1     1
 2 2020-01-25 30代  女性      1     2
 3 2020-01-26 NA    NA        0     2
 4 2020-01-27 NA    NA        0     2
 5 2020-01-28 NA    NA        0     2
 6 2020-01-29 NA    NA        0     2
 7 2020-01-30 30代  女性      1     3
 8 2020-01-31 NA    NA        0     3
 9 2020-02-01 NA    NA        0     3
10 2020-02-02 NA    NA        0     3
# … with 329 more rows

この場合は、しっかりungroupを入れることが必要となる。

blog.exploratory.io

tidyr.tidyverse.org

Spark ランク関数 rank, dense_rank, percent_rank

Sparkのランク関数メモ。商品IDとその商品カテゴリ、売り上げのようなデータがあったとする。

scala> val df = Seq(
  ("p00001", "food", 200),
  ("p00002", "food", 500),
  ("p00003", "food", 800),
  ("p00004", "food", 500),
  ("p00005", "food", 700),
  ("p00006", "book", 900),
  ("p00007", "book", 1500),
  ("p00008", "book", 1200),
  ("p00009", "book", 2200),
  ("p00010", "book", 3000),
  ("p00011", "tool", 20000),
  ("p00012", "tool", 50000),
  ("p00013", "tool", 15000),
  ("p00014", "tool", 60000),
  ("p00015", "tool", 35000)
).toDF("id", "category", "sales")

商品カテゴリごとに売上の降順でランク付けする。

scala> import org.apache.spark.sql.expressions.Window

scala> df.
  withColumn("rnk", rank().over(Window.partitionBy('category).orderBy('sales.desc))).
  withColumn("dense_rnk", dense_rank().over(Window.partitionBy('category).orderBy('sales.desc))).
  withColumn("percent_rnk", percent_rank().over(Window.partitionBy('category).orderBy('sales.desc))).
  show()
+------+--------+-----+---+---------+-----------+
|    id|category|sales|rnk|dense_rnk|percent_rnk|
+------+--------+-----+---+---------+-----------+
|p00003|    food|  800|  1|        1|        0.0|
|p00005|    food|  700|  2|        2|       0.25|
|p00002|    food|  500|  3|        3|        0.5|
|p00004|    food|  500|  3|        3|        0.5|
|p00001|    food|  200|  5|        4|        1.0|
|p00014|    tool|60000|  1|        1|        0.0|
|p00012|    tool|50000|  2|        2|       0.25|
|p00015|    tool|35000|  3|        3|        0.5|
|p00011|    tool|20000|  4|        4|       0.75|
|p00013|    tool|15000|  5|        5|        1.0|
|p00010|    book| 3000|  1|        1|        0.0|
|p00009|    book| 2200|  2|        2|       0.25|
|p00007|    book| 1500|  3|        3|        0.5|
|p00008|    book| 1200|  4|        4|       0.75|
|p00006|    book|  900|  5|        5|        1.0|
+------+--------+-----+---+---------+-----------+

ここから、.filter('percent_rnk <= 0.3)で絞り込むことで、売り上げ上位30%のレコードが抽出できる。

spark.apache.org

highcharterで日本地図へのマッピング

都道府県名や都道府県コードなどの地理的情報、そして、それらのユーザ数や売上げなどのデータがあり、その濃淡を日本地図へマッピングして可視化したいという場合がある。ggplot2でやってしまえるところではあるが、highcharterパッケージを利用すれば意外と簡単に可視化することができる。

Googleトレンドから、あるキーワード(検索クエリ)の1年間の都道府県ごとの検索スコアデータを日本地図にマッピングしてみる。

> library(tidyverse)
> library(gtrendsR)
> library(highcharter)
> 
> keyword <- "タピオカ"
> gt <- gtrends(keyword = keyword, geo = "JP", gprop = "web", time = "today 12-m")
> gt$interest_by_region %>% head()
             location hits  keyword geo gprop
1               Tokyo  100 タピオカ  JP   web
2    Osaka Prefecture   95 タピオカ  JP   web
3   Miyagi Prefecture   85 タピオカ  JP   web
4 Kanagawa Prefecture   84 タピオカ  JP   web
5  Tochigi Prefecture   81 タピオカ  JP   web
6  Saitama Prefecture   79 タピオカ  JP   web

interest_by_region都道府県ごとの検索スコアのデータになっている。Googleトレンドの仕様で一番検索が多い都道府県の値を100とした相対スコア値になっている。地図上にマッピングするために都道府県名と検索スコアのみのデータに加工する。

> map_data <- gt$interest_by_region %>%
+   mutate(location = stringr::str_replace(location, " Prefecture", "")) %>%
+   select(location, hits)

あとは、地図情報を合わせることでマッピングすることができる。

> hcmap("countries/jp/jp-all", data = map_data, name = keyword,
+       value = "hits", joinBy = c("name", "location"), borderColor = "#f0f0f0")

f:id:masaqol:20191231181433p:plain 神奈川県で「タピオカ」は、東京と比較してどれほど検索されているかが分かる。人口比では、宮城県や栃木県が意外と検索されている地域?どの地域でよく検索されているかなどが可視化することがわかりやすくなる。

JSのHighchartsベースなので、R MarkdownやShinyなどでHTML形式で出力すれば、マウスオーバーで動的に変化する。商用でShinyアプリを利用する場合には、Highchartsのライセンスに注意して利用する必要がある。

https://github.com/jbkunst/highcharter jkunst.com