マイナンバーカード交付状況のExcelファイルをデータラングリング（9月）

はじめに

総務省のマイナンバー制度とマイナンバーカード

• マイナンバーカード交付状況（令和2年9月1日現在）のExcel形式

マイナンバーカード普及状況ダッシュボードのCSV形式に変換

• mynumbercard_statistics
• スクレイピングしてきたデータの文字コードがおかしかったので修正した

※令和2年8月1日と令和2年9月1日Excelファイルはフォーマットが違うため令和2年9月1日のみ対応

• 基準日が8月は各表の最後、9月は列名の中（PDFと同じ）
• 各表の区切り

マイナンバーカード交付状況（令和2年8月1日現在）はこちら
https://qiita.com/barobaro/items/05efbb6aa2c759c80ff0

• 9月は基準日が列名の中にありそれぞれ抽出
• 各表の区切りは列名の2・3列目に時点があるのでそれを基準に抽出

データラングリング

import csv
import datetime
import re

import pandas as pd


def wareki2date(s):

    m = re.search("(H|R|平成|令和)([0-9元]{1,2})[.年]([0-9]{1,2})[.月]([0-9]{1,2})日?", s)

    year, month, day = [1 if i == "元" else int(i) for i in m.group(2, 3, 4)]

    if m.group(1) in ["平成", "H"]:
        year += 1988
    elif m.group(1) in ["令和", "R"]:
        year += 2018

    return datetime.date(year, month, day).strftime("%Y/%m/%d")


def df_conv(df, col_name, population_date, delivery_date):

    df.set_axis(col_name, axis=1, inplace=True)

    df["人口算出基準日"] = population_date
    df["交付枚数算出基準日"] = delivery_date
    df.insert(0, "算出基準日", delivery_date)

    return df


def my_round(s):
    return int(s * 1000 + 0.5) / 10


df = pd.read_excel(
    "https://www.soumu.go.jp/main_content/000707709.xlsx", sheet_name=1, header=None
)

df.dropna(thresh=3, inplace=True)

dfg = df.groupby(
    (df[1].str.contains("時点", na=False) | df[2].str.contains("時点", na=False)).cumsum()
)

dfs = [g.dropna(how="all", axis=1).reset_index(drop=True) for _, g in dfg]

print(len(dfs))

# 団体区分別

population_date = wareki2date(dfs[0].iat[0, 1])
delivery_date = wareki2date(dfs[0].iat[0, 2])

df0 = df_conv(
    dfs[0].iloc[1:].reset_index(drop=True),
    ["区分", "人口", "交付枚数", "人口に対する交付枚数率"],
    population_date,
    delivery_date,
)

df0["人口に対する交付枚数率"] = df0["人口に対する交付枚数率"].apply(my_round)

df0.to_csv(
    "summary_by_types.csv",
    index=False,
    quoting=csv.QUOTE_NONNUMERIC,
    encoding="utf_8_sig",
)

df0

# 都道府県一覧

population_date = wareki2date(dfs[3].iat[0, 1])
delivery_date = wareki2date(dfs[3].iat[0, 2])

df3 = df_conv(
    dfs[3].iloc[1:].reset_index(drop=True),
    ["都道府県名", "総数（人口）", "交付枚数", "人口に対する交付枚数率"],
    population_date,
    delivery_date,
)

df3["人口に対する交付枚数率"] = df3["人口に対する交付枚数率"].apply(my_round)

df3.to_csv(
    "all_prefectures.csv",
    index=False,
    quoting=csv.QUOTE_NONNUMERIC,
    encoding="utf_8_sig",
)

df3

# 男女・年齢別

population_date = wareki2date(dfs[4].iat[0, 1])
delivery_date = wareki2date(dfs[4].iat[0, 4])

df4 = df_conv(
    dfs[4].iloc[2:].reset_index(drop=True),
    [
        "年齢",
        "人口(男)",
        "人口(女)",
        "人口(計)",
        "交付件数(男)",
        "交付件数(女)",
        "交付件数(計)",
        "交付率(男)",
        "交付率(女)",
        "交付率(計)",
        "全体に対する交付件数割合(男)",
        "全体に対する交付件数割合(女)",
        "全体に対する交付件数割合(計)",
    ],
    population_date,
    delivery_date,
)

df4["交付率(男)"] = df4["交付率(男)"].apply(my_round)
df4["交付率(女)"] = df4["交付率(女)"].apply(my_round)
df4["交付率(計)"] = df4["交付率(計)"].apply(my_round)
df4["全体に対する交付件数割合(男)"] = df4["全体に対する交付件数割合(男)"].apply(my_round)
df4["全体に対する交付件数割合(女)"] = df4["全体に対する交付件数割合(女)"].apply(my_round)
df4["全体に対する交付件数割合(計)"] = df4["全体に対する交付件数割合(計)"].apply(my_round)

df4.to_csv(
    "demographics.csv", index=False, quoting=csv.QUOTE_NONNUMERIC, encoding="utf_8_sig",
)

df4

# 市区町村別

population_date = wareki2date(dfs[5].iat[0, 2])
delivery_date = wareki2date(dfs[5].iat[0, 3])

df5 = df_conv(
    dfs[5].iloc[2:].reset_index(drop=True),
    ["都道府県名", "市区町村名", "総数（人口）", "交付枚数", "人口に対する交付枚数率"],
    population_date,
    delivery_date,
)

df5["人口に対する交付枚数率"] = df5["人口に対する交付枚数率"].apply(my_round)

df5["市区町村名"] = df5["市区町村名"].replace(r"\s", "", regex=True)
df5["市区町村名"] = df5["市区町村名"].mask(df5["都道府県名"] + df5["市区町村名"] == "兵庫県篠山市", "丹波篠山市")
df5["市区町村名"] = df5["市区町村名"].mask(df5["都道府県名"] + df5["市区町村名"] == "高知県高岡郡梼原町", "高岡郡檮原町")
df5["市区町村名"] = df5["市区町村名"].mask(df5["都道府県名"] + df5["市区町村名"] == "福岡県糟屋郡須惠町", "糟屋郡須恵町")

if pd.Timestamp(df5.iloc[0]["算出基準日"]) < datetime.date(2018, 10, 1):
    df5["市区町村名"] = df5["市区町村名"].mask(
        df5["都道府県名"] + df5["市区町村名"] == "福岡県那珂川市", "筑紫郡那珂川町"
    )
else:
    df5["市区町村名"] = df5["市区町村名"].mask(
        df5["都道府県名"] + df5["市区町村名"] == "福岡県筑紫郡那珂川町", "那珂川市"
    )

df_code = pd.read_csv(
    "https://docs.google.com/spreadsheets/d/e/2PACX-1vSseDxB5f3nS-YQ1NOkuFKZ7rTNfPLHqTKaSag-qaK25EWLcSL0klbFBZm1b6JDKGtHTk6iMUxsXpxt/pub?gid=0&single=true&output=csv",
    dtype={"団体コード": int, "都道府県名": str, "郡名": str, "市区町村名": str},
)

df_code["市区町村名"] = df_code["郡名"].fillna("") + df_code["市区町村名"]
df_code.drop("郡名", axis=1, inplace=True)

df5 = pd.merge(df5, df_code, on=["都道府県名", "市区町村名"], how="left")
df5["団体コード"] = df5["団体コード"].astype("Int64")

df5.to_csv(
    "all_localgovs.csv",
    index=False,
    quoting=csv.QUOTE_NONNUMERIC,
    encoding="utf_8_sig",
)

df5

参考

• mynumbercard_statistics
• スクレイピングしてきたデータの文字コードがおかしかったので修正した