[PYTHON] Scraping data wrangling of statistical information on new coronavirus infection in Yamanashi Prefecture

Scraping data wrangling of statistical information on new coronavirus infection in Yamanashi Prefecture

Introduction

• Scraping from Statistical information on new coronavirus infection (outbreak status, inspection status, number of consultations)
• It is organized every week, and the days are organized by day / case.

procedure

1. Divide the period into start and end, divide the breakdown by day, combine
2. Convert start and end to date
3. Organize by date
4. Divide into days and number
5. Combine start / end and date / number
6. Convert from day to date
7. Date and subtotal

Scraping

dfs = pd.read_html(
    "https://www.pref.yamanashi.jp/koucho/coronavirus/info_coronavirus_data.html"
)

df0 = df.iloc[1:].set_axis(["period", "number", "Breakdown"], axis=1)

Data wrangling

Divide the period into start and end, divide the breakdown by day, combine

df1 = pd.concat(
    [
        df0["period"].str.split("～", expand=True).rename(columns={0: "start", 1: "End"}),
        df0["Breakdown"].str.split("、", expand=True),
    ],
    axis=1,
)

Convert start and end to date

df1["start"] = df1["start"].str.normalize("NFKC").apply(my_parser)
df1["End"] = df1["End"].str.normalize("NFKC").apply(my_parser)

Organized by date

df2 = df1.melt(id_vars=["start", "End"]).dropna()

Divided into days and number

df3 = (
    df2["value"]
    .str.extract("([0-9,]+)[Daily]([0-9,]+)Case",expand=True)
    .rename(columns={0: "Day", 1: "subtotal"})
    .astype(int)
)

Combine start / end and date / number

df4 = pd.concat([df2, df3], axis=1)

Convert from day to date

• Compare the end date and day, replace the start date if it is larger, replace the end date if it is smaller, and create a date

df4["date"] = df4.apply(
    lambda x: x["start"].replace(day=x["Day"])
    if x["End"].day < x["Day"]
    else x["End"].replace(day=x["Day"]),
    axis=1,
)

Date and subtotal

df = pd.DataFrame(
    {"subtotal": df4.set_index("date")["subtotal"].sort_index().asfreq("D", fill_value=0)}
)

import datetime
import re

import pandas as pd


def my_parser(s):

    y = dt_now.year
    m, d = map(int, re.findall("(\d{1,2})", s))

    return pd.Timestamp(year=y, month=m, day=d)


def df_conv(df):

    df0 = df.iloc[1:].set_axis(["period", "number", "Breakdown"], axis=1)

    #Divide the period into start date and end date, divide the breakdown by day, combine
    df1 = pd.concat(
        [
            df0["period"].str.split("～", expand=True).rename(columns={0: "start", 1: "End"}),
            df0["Breakdown"].str.split("、", expand=True),
        ],
        axis=1,
    )

    #Convert to date
    df1["start"] = df1["start"].str.normalize("NFKC").apply(my_parser)
    df1["End"] = df1["End"].str.normalize("NFKC").apply(my_parser)

    #Organized by date
    df2 = df1.melt(id_vars=["start", "End"]).dropna()

    #Divided into days and number
    df3 = (
        df2["value"]
        .str.extract("([0-9,]+)[Daily]([0-9,]+)Case",expand=True)
        .rename(columns={0: "Day", 1: "subtotal"})
        .astype(int)
    )

    #Combine start / end and date / number
    df4 = pd.concat([df2, df3], axis=1)

    #Convert from day to date
    df4["date"] = df4.apply(
        lambda x: x["start"].replace(day=x["Day"])
        if x["End"].day < x["Day"]
        else x["End"].replace(day=x["Day"]),
        axis=1,
    )

    #Date and subtotal
    df = pd.DataFrame(
        {"subtotal": df4.set_index("date")["subtotal"].sort_index().asfreq("D", fill_value=0)}
    )

    return df


JST = datetime.timezone(datetime.timedelta(hours=+9))
dt_now = datetime.datetime.now(JST)


dfs = pd.read_html(
    "https://www.pref.yamanashi.jp/koucho/coronavirus/info_coronavirus_data.html"
)

len(dfs)


#Inspection status of pseudo cases at the Prefectural Institute of Health and Environment

df1 = df_conv(dfs[1])
df1.to_csv("pcr.csv", encoding="utf_8_sig")

#Returnee / Contact Consultation Center

df2 = df_conv(dfs[2])
df2.to_csv("soudan.csv", encoding="utf_8_sig")

#Consultation dial for new coronavirus infection

df3 = df_conv(dfs[3])
df3.to_csv("dial.csv", encoding="utf_8_sig")