[PYTHON] Scraping PDF of the national list of minimum wages by region of the Ministry of Health, Labor and Welfare

Preparation

!apt install python3-tk ghostscript
!pip install camelot-py[cv]

#Download PDF
!wget https://www.mhlw.go.jp/content/11200000/000541154.pdf -O data.pdf

!pip install japanmap
!pip install japanize-matplotlib

Scraping

import re

import camelot
import pandas as pd

tables = camelot.read_pdf(
    "data.pdf", pages="all", split_text=True, strip_text=" \n", line_scale=40
)

dfs = []

for table in tables:

    df0 = table.df

    df_col = df0.head(2)

    #Complement blanks at cell merges
    idx = pd.MultiIndex.from_arrays(
        df_col.mask(df_col == "").fillna(method="ffill", axis=1).values
    )

    df1 = df0.iloc[2:].set_axis(idx, axis=1)
    df1.set_index(("year", "Name of prefectures"), inplace=True)
    df1.index.name = "Name of prefectures"

    #Convert from row to column
    df2 = df1.unstack().reset_index().set_axis(["year", "Classification", "Prefectures", "Contents"], axis=1)

    #Reconstruction by year / prefecture
    df3 = df2.pivot(index=["year", "Prefectures"], columns="Classification", values="Contents")

    dfs.append(df3)

df = pd.concat(dfs).reset_index()

df.to_csv("data.tsv", sep="\t")

I was able to copy a merged cell using "copy_text" from camelot h: horizontal v: vertical https://camelot-py.readthedocs.io/en/master/user/advanced.html#copy-text-in-spanning-cells

tables = camelot.read_pdf(
    "data.pdf",
    pages="all",
    split_text=True,
    strip_text=" \n",
    line_scale=40,
    copy_text=["h"],
)

dfs = []

for table in tables:

    data = table.data
    df1 = pd.DataFrame(data[2:], columns=data[:2])

    df1.set_index(("year", "Name of prefectures"), inplace=True)
    df1.index.name = "Name of prefectures"

    df2 = df1.unstack().reset_index().set_axis(["year", "Classification", "Prefectures", "Contents"], axis=1)
    df3 = df2.pivot(index=["year", "Prefectures"], columns="Classification", values="Contents")

    dfs.append(df3)

df = pd.concat(dfs).reset_index()

df.to_csv("data.tsv", sep="\t")

Year from the era

def era2year(s):

    m = re.search("(Heisei|Reiwa)([0-9 yuan]{1,2})year", s)

    if m:

        era = m.group(1)
        nen = m.group(2)

        year = 1 if nen == "Former" else int(nen)

        if era == "Heisei":
            year += 1988
        elif era == "Reiwa":
            year += 2018

        return year

    return None

Data wrangling

from japanmap import pref_code

#Give the prefecture code from the prefecture name
df["Prefecture code"] = df["Prefectures"].apply(pref_code)

#Convert from era to year
df["Year"] = df["year"].apply(era2year)

#Remove commas and convert to numbers
df["Revised amount(Circle)"] = df["Revised amount(Circle)"].str.replace(",", "").astype(int)

#Sort by prefecture
pv = df.pivot(index=["Prefecture code", "Prefectures"], columns="Year", values="Revised amount(Circle)").droplevel(
    "Prefecture code"
)

pv

Scraping the latest year

#Scraping

df = pd.read_html(
    "https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/koyou_roudou/roudoukijun/minimumichiran/",
    index_col=0,
    header=0,
)[0]

df.index = df.index.str.replace("\s", "")

df.rename(columns={"Minimum wage hour [yen]": 2020}, inplace=True)

#Combine with PDF list

pv1 = pd.concat([pv, df[2020]], axis=1)

pv1.to_csv("minimumichiran.csv")

data

Visualization

import japanize_matplotlib
import matplotlib as mpl
import matplotlib.pyplot as plt

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

pv1.T.plot(subplots=True, layout=(7, 7), figsize=(30, 30), ylim=(600, 1100))

#Save graph
plt.savefig("minimumichiran.png ", dpi=200, bbox_inches="tight")
plt.show()

Graph