山形大学IR担当者向け実践プログラム第6期

1 課題の内容について

https://ir.yamagata-u.ac.jp/activity/program/no06/news20240419463/

配布した「２つのエクセルファイル」から「都道府県市町村別」に「国立、公立、私立」それぞれの学校数を集計してください。

例えば、山形県山形市には、国立大学が1校、公立大学が1校、私立大学が3校（短大を含む）あります。

なお、政令指定都市は「市」の代わりに「区」を集計単位とします。（例：札幌市→札幌市中央区）

Note

• d_大学リスト1.xlsxには、学校名と学校の住所データがあります。 このファイルに間違いはありません。

• d_大学リスト2.xlsxには、学校名と設置区分（国立、公立、私立）のデータがありますが、このファイルには間違っている所（例：学校名＋α）があります。

2 データの内容

ファイル名が長いので、d_大学リスト1.xlsxをdf1、d_大学リスト2.xlsxをdf2と呼びます。

df1 <- read_xlsx("d_大学リスト1.xlsx")
df2 <- read_xlsx("d_大学リスト2.xlsx")

df1とdf2の内容は以下の通り。

kable(head(df1))

学校名	学校所在地
北海道大学	北海道札幌市北区北８条西５丁目
北海道教育大学	北海道札幌市北区あいの里５条３－１－３
室蘭工業大学	北海道室蘭市水元町２７－１
小樽商科大学	北海道小樽市緑３－５－２１
帯広畜産大学	北海道帯広市稲田町西２線１１
北見工業大学	北海道北見市公園町１６５

kable(tail(df1))

学校名	学校所在地
沖縄国際大学	沖縄県宜野湾市宜野湾２－６－１
沖縄大学	沖縄県那覇市字国場５５５
沖縄キリスト教学院大学	沖縄県中頭郡西原町字翁長７７７番地
沖縄キリスト教短期大学	沖縄県中頭郡西原町字翁長７７７番地
沖縄女子短期大学	沖縄県島尻郡与那原町東浜１番地
沖縄工業高等専門学校	沖縄県名護市字辺野古９０５

kable(head(df2))

大学名	設置区分
いわき短期大	私立
お茶の水女子大	国立
かなざわ食マネジメント専門職大	私立
くらしき作陽大	私立
こども教育宝仙大	私立
せとうち観光専門職短期大	私立

kable(tail(df2))

大学名	設置区分
麗澤大学	私立
麻布大学	私立
龍谷大学	私立
龍谷大学短期大学	私立
ＬＥＣ東京リーガルマインド大学院大	私立
ＳＢＩ大学院大	私立

3 住所の分割

• Rで作業すると以下の数行で、df1にある住所の分割が完了します。分割された住所を含む新しいデータをdf1aと呼びます。

vec_addr <- df1 %>%
  select(学校所在地) %>%
  pull()

df_addr <-
  map_dfr(vec_addr, ~ separate_address(.))

df1a <- bind_cols(df1, df_addr)

kable(head(df1a))

学校名	学校所在地	prefecture	city	street
北海道大学	北海道札幌市北区北８条西５丁目	北海道	札幌市北区	北８条西５丁目
北海道教育大学	北海道札幌市北区あいの里５条３－１－３	北海道	札幌市北区	あいの里５条３－１－３
室蘭工業大学	北海道室蘭市水元町２７－１	北海道	室蘭市	水元町２７－１
小樽商科大学	北海道小樽市緑３－５－２１	北海道	小樽市	緑３－５－２１
帯広畜産大学	北海道帯広市稲田町西２線１１	北海道	帯広市	稲田町西２線１１
北見工業大学	北海道北見市公園町１６５	北海道	北見市	公園町１６５

kable(tail(df1a))

学校名	学校所在地	prefecture	city	street
沖縄国際大学	沖縄県宜野湾市宜野湾２－６－１	沖縄県	宜野湾市	宜野湾２－６－１
沖縄大学	沖縄県那覇市字国場５５５	沖縄県	那覇市	字国場５５５
沖縄キリスト教学院大学	沖縄県中頭郡西原町字翁長７７７番地	沖縄県	中頭郡西原町	字翁長７７７番地
沖縄キリスト教短期大学	沖縄県中頭郡西原町字翁長７７７番地	沖縄県	中頭郡西原町	字翁長７７７番地
沖縄女子短期大学	沖縄県島尻郡与那原町東浜１番地	沖縄県	島尻郡与那原町	東浜１番地
沖縄工業高等専門学校	沖縄県名護市字辺野古９０５	沖縄県	名護市	字辺野古９０５

4 データの修正（df2）

Caution

df1とdf2のデータ数が異なっていることには気づかれましたか？

重複データ一覧（df2）

学校名	設置区分
九州大学	公立
九州大学	国立
九州工業大学	公立
九州工業大学	国立
京都大学	公立
京都大学	国立
佐賀大学	公立
佐賀大学	国立
北海道大学	公立
北海道大学	国立
名古屋大学	公立
名古屋大学	国立
大分大学	公立
大分大学	国立
大阪大学	公立
大阪大学	国立
宮城教育大学	公立
宮城教育大学	国立
宮崎大学	公立
宮崎大学	国立
山形大学	公立
山形大学	国立
岩手大学	公立
岩手大学	国立
弘前大学	公立
弘前大学	国立
東京大学	公立
東京大学	国立
東北大学	公立
東北大学	国立
熊本大学	公立
熊本大学	国立
琉球大学	公立
琉球大学	国立
神戸大学	公立
神戸大学	国立
福岡教育大学	公立
福岡教育大学	国立
福島大学	公立
福島大学	国立
秋田大学	公立
秋田大学	国立
長崎大学	公立
長崎大学	国立
鹿児島大学	公立
鹿児島大学	国立
鹿屋体育大学	公立
鹿屋体育大学	国立

Note

上記にリストされている学校は、全て国立 です。公立として登録されている部分を削除しましょう。

Important

目視による確認が必要な場合もあります。（凄くイヤだけど…。）

5 名寄せ問題

• この課題は所謂「名寄せ問題」に近い問題です。df1とdf2にある「学校名」が「完全一致」もしくは「限りなく一致」している学校をピックアップしましょう。

Important

特定の課題にしか使えない解法ではなく、汎用性のあるアプローチ方法を考えましょう。後で楽ができるかもしれません。

df1b <- df1a %>% stringdist_left_join(
  df2b,
  by = "学校名",
  max_dist = 1,
  method = "jw",
  distance_col = "myout"
) %>%
  group_by(学校名.x) %>%
  slice_max(n = 1, order_by = -myout, with_ties = TRUE) %>%
  mutate(Prob_Matching = 1 - myout) %>%
  select(-myout) %>%
  ungroup()

df1b %>% 
  select(starts_with("学校名"), 設置区分, Prob_Matching) %>%
  head() %>% 
  kable()

学校名.x	学校名.y	設置区分	Prob_Matching
いわき短期大学	いわき短期大	私立	0.9523810
お茶の水女子大学	お茶の水女子大	国立	0.9583333
かなざわ食マネジメント専門職大学	かなざわ食マネジメント専門職大	私立	0.9791667
くらしき作陽大学	くらしき作陽大	私立	0.9583333
こども教育宝仙大学	こども教育宝仙大	私立	0.9629630
せとうち観光専門職短期大学	せとうち観光専門職短期大	私立	0.9743590

Note

• 学校名.Xはd_大学リスト1.xlsxから、学校名.Yと設置区分はd_大学リスト2.xlsxにあるデータです。

• しかし、この方法は完璧ではありません。「Prob_Matching（一致率）」が同じでも、異なる学校名を持つ組み合わせがある場合を否定できません。例えば、下記の表には正しくない組み合わせが含まれています。

学校名.x	学校名.y	Prob_Matching
大阪女学院大学	大阪女学院大	0.952381
大阪女学院大学	大阪学院大学	0.952381
常磐会短期大学	常磐会短期大	0.952381
常磐会短期大学	常磐短期大学	0.952381
神戸女学院大学	神戸女学院大	0.952381
神戸女学院大学	神戸学院大学	0.952381
西南女学院大学	西南女学院大	0.952381
西南女学院大学	西南学院大学	0.952381
西日本工業大学	日本工業大学	0.952381
西日本工業大学	西日本工業大	0.952381

どのように処理しましょうか？

学校名.xの中に「頭から連続した」学校名.yの文字列が含まれるかをチェックしましょう。例えば、「西南女学院大学」の中に「西南女学院大」は含まれますが、「西南学院大学」は含まれません。つまり、正しくない、削除すべき組み合わせは、以下の通りとなります。

Important

上記の処理方法は、この問題にのみ有効です。

学校名.x	学校名.y
大阪女学院大学	大阪学院大学
常磐会短期大学	常磐短期大学
神戸女学院大学	神戸学院大学
西南女学院大学	西南学院大学
西日本工業大学	日本工業大学

6 分析用データの完成

• 分析用データは以下のようになります。

df1f <- df1e %>%
  select(-学校名.y, -Prob_Matching, -学校所在地) %>%
  rename(学校名 = 学校名.x)

df1f %>%
  head() %>% 
  kable()

学校名	prefecture	city	street	設置区分
いわき短期大学	福島県	いわき市	平鎌田字寿金沢３７	私立
お茶の水女子大学	東京都	文京区	大塚２－１－１	国立
かなざわ食マネジメント専門職大学	石川県	白山市	横江町５２５０	私立
くらしき作陽大学	岡山県	倉敷市	玉島長尾３５１５	私立
こども教育宝仙大学	東京都	中野区	中央二丁目３３番２６号	私立
せとうち観光専門職短期大学	香川県	高松市	屋島西町２３６６－１	私立

df1f %>%
  tail() %>% 
  kable()

学校名	prefecture	city	street	設置区分
麗澤大学	千葉県	柏市	光ケ丘２－１－１	私立
麻布大学	神奈川県	相模原市中央区	淵野辺１－１７－７１	私立
龍谷大学	京都府	京都市伏見区	深草塚本町６７	私立
龍谷大学短期大学部	京都府	京都市伏見区	深草塚本町６７	私立
ＬＥＣ東京リーガルマインド大学院大学	東京都	千代田区	神田三崎町２－２－１５	私立
ＳＢＩ大学院大学	東京都	港区	六本木１－６－１　泉ガーデンタワー２１階	私立

df1f %>%
  tabyl(設置区分) %>% 
  adorn_totals() %>% 
  kable()

設置区分	n	percent
公立	120	0.1017812
国立	137	0.1162002
私立	922	0.7820187
Total	1179	1.0000000

7 例題

7.1 三重県にある大学等を市町村ごとにソートしてリストしてください。

df1f %>% 
  filter(prefecture == "三重県") %>% 
  arrange(city, 設置区分) %>% 
  kable()

学校名	prefecture	city	street	設置区分
皇學館大学	三重県	伊勢市	神田久志本町１７０４	私立
近畿大学工業高等専門学校	三重県	名張市	春日丘７番町１番地	私立
ユマニテク短期大学	三重県	四日市市	南浜田町４－２１	私立
四日市大学	三重県	四日市市	萱生町１２００	私立
四日市看護医療大学	三重県	四日市市	萱生町１２００番地	私立
三重県立看護大学	三重県	津市	夢が丘一丁目１－１	公立
三重短期大学	三重県	津市	一身田中野１５７	公立
三重大学	三重県	津市	栗真町屋町１５７７	国立
高田短期大学	三重県	津市	一身田豊野１９５	私立
鈴鹿工業高等専門学校	三重県	鈴鹿市	白子町	国立
鈴鹿医療科学大学	三重県	鈴鹿市	岸岡町１００１－１	私立
鈴鹿大学	三重県	鈴鹿市	郡山町６６３－２２２	私立
鈴鹿大学短期大学部	三重県	鈴鹿市	郡山町６６３－２２２	私立
鳥羽商船高等専門学校	三重県	鳥羽市	池上町１－１	国立

7.2 公立大学等が複数ある市町村＋区は、全国で幾つありますか？

df1f %>% 
  filter(設置区分 == "公立") %>% 
  count(city) %>% 
  filter(n > 1) %>% 
  arrange(desc(n)) %>% 
  mutate(行番号 = row_number()) %>% 
  relocate(行番号) %>% 
  gt()

行番号	city	n
1	神戸市西区	4
2	秋田市	3
3	会津若松市	2
4	前橋市	2
5	品川区	2
6	大分市	2
7	宮崎市	2
8	岐阜市	2
9	川崎市幸区	2
10	旭川市	2
11	津市	2
12	滝沢市	2
13	磐田市	2
14	米沢市	2
15	那覇市	2
16	長久手市	2
17	青森市	2
18	静岡市駿河区	2