今週も特にありません

進捗どうですか?

ggplot2 軸表記を変更する

ggplot2で可視化したグラフを資料等で貼り付ける時に、様々な人のことを考えて軸の表記を変えたいという場合がある。 見やすさやさしさ以外は本質的には変わらないが、変更の仕方をたまに調べていることがあるためメモ。

利用ユーザ数が大変多い2つのウェブサイトがあり、そのユーザ数が時系列で記録されたようなデータがあるとする。

> library(tidyverse)
> library(lubridate)
> library(scales)
> 
> set.seed(2345)
> log_tbl <- tibble(
+     log_date = rep(seq(ymd("20201201"), ymd("20201231"), by = "day"), 2),
+     site = c(rep("A", 31), rep("G", 31)),
+     uu = c(rpois(31, 777), rpois(31, 888)) * 1e05
+ )

数千万ユーザが毎日利用している状態を想定している。

> log_tbl
# A tibble: 62 x 3
   log_date   site        uu
   <date>     <chr>    <dbl>
 1 2020-12-01 A     74300000
 2 2020-12-02 A     72600000
 3 2020-12-03 A     78400000
 4 2020-12-04 A     77000000
 5 2020-12-05 A     76500000
 6 2020-12-06 A     77000000
 7 2020-12-07 A     77200000
 8 2020-12-08 A     83400000
 9 2020-12-09 A     80200000
10 2020-12-10 A     76600000
# … with 52 more rows

コンマ表記

大変大きい数を可視化する場合に起きる問題で、 そのままこのデータを可視化すると、e+07の表記になってしまい、表記に見慣れている人でもすぐに数を把握するのが難しい。

> log_tbl %>%
+     ggplot(aes(x = log_date, y = uu, colour = site)) +
+     geom_line(size = 1.5) +
+     geom_point(size = 2) + 
+     scale_x_date(breaks = "7 days", minor_breaks = "1 day", 
+                  date_labels = "%m/%d") +
+     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:20201231164325p:plain

そこで、コンマ表記にしたい場合は、scale_y_continuous(labels = comma)を追加すれば良い。 f:id:masaqol:20201231164951p:plain

パーセント表記

比率を可視化する場合に起きる問題で、ggplot2でfillを指定した場合には0から1の間で表記されてしまう。 こちらは大体見れば分かるが、パーセント表記でやさしくありたい。

> log_tbl %>%
+     ggplot(aes(x = log_date, y = uu, fill = site)) +
+     geom_bar(stat = "identity", position = "fill") +
+     scale_x_date(breaks = "7 days", minor_breaks = "1 day", 
+                  date_labels = "%m/%d") +
+     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:20201231165822p:plain

ここで、パーセント表記にしたい場合は、scale_y_continuous(labels = percent)を追加すれば良い。 f:id:masaqol:20201231170056p:plain

その他

commapercentも実態としては、scalesパッケージに含まれている関数で実現している。 他にもラベル表記を簡単に変更することができる。

label_bytes             Label bytes (1 kb, 2 MB, etc)
label_date              Label date/times
label_dollar            Label currencies ($100, $2.50, etc)
label_number            Label numbers in decimal format (e.g. 0.12, 1,234)
label_number_auto       Label numbers, avoiding scientific notation where possible
label_number_si         Label numbers with SI prefixes (2k, 1M, 5T etc)
label_ordinal           Label ordinal numbers (1st, 2nd, 3rd, etc)
label_parse             Label with mathematical annotations
label_percent           Label percentages (2.5%, 50%, etc)
label_pvalue            Label p-values (e.g. <0.001, 0.25, p >= 0.99)
label_scientific        Label numbers with scientific notation (e.g. 1e05, 1.5e-02)
label_wrap              Label strings by wrapping across multiple lines

複数段表記などは使いどころがあるかもしれない。

www.tidyverse.org

等幅の棒グラフを描画する 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

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

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

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