import requests
from bs4 import BeautifulSoup

import re

from urllib.parse import urljoin

url = "https://www.kokusei2020.go.jp/internet/"

r = requests.get(url)
r.raise_for_status()

soup = BeautifulSoup(r.content, "html.parser")

links = {}

for i in soup.find_all("span", text="Excel"):

    link = urljoin(url, i.find_parent("a").get("href"))

    m = re.search("census_answers_(pref|city)_\d{6}.xlsx", link)

    if m:
        links[m.group(1)] = link

links

import pandas as pd

df_pref = pd.read_excel(
    links["pref"],
    index_col=[0, 1],
    header=None,
    skiprows=9,
    usecols=[1, 2, 3, 4, 5, 6, 7],
    names=["Code", "Präfekturen", "Anzahl der H27-Haushalte", "Netz", "Per Mail", "Netz率", "Per Mail率"],
)

df_pref["Anzahl der Antworten"] = df_pref["Netz"] + df_pref["Per Mail"]

df_pref["Nettorate"] *= 100
df_pref["Versandrate"] *= 100

df_pref["Rücklaufquote"] = df_pref["Nettorate"] + df_pref["Versandrate"]

df_pref.to_csv("pref.csv", encoding="utf_8_sig")

df_city = pd.read_excel(
    links["city"],
    index_col=[0, 1, 2],
    header=None,
    skiprows=9,
    usecols=[1, 2, 3, 4, 5, 6, 7, 8],
    names=["Code", "Präfekturen", "Gemeinde", "Anzahl der H27-Haushalte", "Netz", "Per Mail", "Netz率", "Per Mail率"],
)

df_city["Anzahl der Antworten"] = df_city["Netz"] + df_city["Per Mail"]

df_city["Nettorate"] *= 100
df_city["Versandrate"] *= 100

df_city["Rücklaufquote"] = df_city["Nettorate"] + df_city["Versandrate"]

df_city.to_csv("city.csv", encoding="utf_8_sig")

df_city

import matplotlib.pyplot as plt
import seaborn as sns

sns.set()

import japanize_matplotlib

#Auflösung
import matplotlib as mpl

mpl.rcParams["figure.dpi"] = 200

df1 = df_pref.sort_index(ascending=False).reset_index(level="Code", drop=True)

df1.loc[:, ["Nettorate", "Versandrate"]].plot.barh(stacked=True, figsize=(5, 10))

plt.legend(bbox_to_anchor=(1.05, 1), loc="upper left", borderaxespad=0, fontsize=8)

plt.savefig("01.png ", dpi=200, bbox_inches="tight")
plt.show()

Recommended Posts