import pandas
import sqlite3

df=pandas.read_excel("b02_01.xlsx",skiprows=10)
fields=df.columns
# 人口総数と1歳ごとの人口のフィールド名を取り出しておきます。
fld_total=fields[4]
fld_ages=fields[5:-9]
# ラベルとして使うために、列名からコード部分を取り除いておきます。
lbl_ages=[l.split("_")[1] for l in fld_ages]
#lbl_ages

with sqlite3.connect("b02_01.db") as con:
  df.to_sql("population",con=con,if_exists="replace",schema="CENSUS",
            dtype=dict(((k, "INTEGER") for k in fields[5:-2])), 
  )

class JPN_Population(object):
  
  def __init__(self):
    with sqlite3.connect("b02_01.db") as con:
      q=con.execute("select * from population where 国籍総数か日本人 = '0_国籍総数' and 地域名 = '00000_全国'")
      self.total, self.male, self.female=q.fetchall()

  @property
  def population_total(self):
    return self.total[5:-9]

  @property
  def population_male(self):
    return self.male[5:-9]

  @property
  def population_female(self):
    return self.female[5:-9]

JPN=JPN_Population()

with sqlite3.connect("b02_01.db") as con:
  prefectures=[e[0] for e in con.execute("select distinct 地域名 from population where 地域識別コード == 'a' ;").fetchall()]
# prefectures

import matplotlib
import matplotlib.pyplot as pyplot
from matplotlib import rcParams
rcParams['font.sans-serif'].insert(0,"IPAexGothic") # for Japanese fonts in titles
rcParams['font.serif'].insert(0,"IPAPMincho") # for Japanese fonts in titles
rcParams['figure.max_open_warning']=48

def draw_graph(areaname):
  """
  arows : result of con.execute("select * from population where 地域名 = <地域名>'")
  """
  with sqlite3.connect("b02_01.db") as con:
    q=con.execute(f"select * from population where 国籍総数か日本人 = '0_国籍総数' and 地域名 = '{areaname}'")
    total, male, female=q.fetchall()
  
  fig,axes=pyplot.subplots(1,2)
  fig.set_size_inches(16,9)
  xlbl=lbl_ages
  xlabels=xlbl[:-2:10]+xlbl[-1:]
  xticks=[xlbl.index(l) for l in xlabels]
  axes[0].set_xticks(xticks, xlabels, rotation=-90)
  axes[1].set_xticks(xticks, xlabels, rotation=-90)
  
  axes[0].plot(
    xlbl, [0 if v=='-' else 100*v/total[5] for v in total[6:-9]],
    label=areaname.split("_")[1],
    ds="steps", color="violet", ls="-")
  axes[0].plot(
    xlbl, [0 if v=='-' else 100*v/JPN.population_total[0] for v in JPN.population_total[1:]],
    label="日本全国",
    ds="steps", color="green",ls=":")
  axes[0].legend()
  
  axes[1].plot(
    xlbl, [0 if v=='-' else 100*v/total[5] for v in male[6:-9] ],
    label="男性",ls="-",
    ds="steps", color="blue")
  axes[1].plot(
    xlbl, [0 if v=='-' else 100*v/total[5] for v in female[6:-9]],
    label="女性",  ls="--", ds="steps", color="red")
  
  axes[1].plot(
    xlbl, [0 if v=='-' else 100*v/JPN.population_total[0] for v in JPN.population_male[1:]],
    label="男性(全国)", ls="-",
    ds="steps", color="cyan")
  
  axes[1].plot(
    xlbl, [0 if v=='-' else 100*v/JPN.population_total[0] for v in JPN.population_female[1:]],
    label="女性(全国)", ls="--",
    ds="steps", color="pink")
  
  pyplot.legend()
  pyplot.draw()

for pref in prefectures[1:]:
  draw_graph(pref)

国勢調査の結果をグラフにしてみる。¶