楽天モバイルの基地局フォームから定型フォーマット出力&写真テロップ

# !apt install -y fonts-ipafont
# !fc-cache -fv

font_path = "/usr/share/fonts/opentype/ipafont-gothic/ipagp.ttf"

from PIL import Image, ImageFont, ImageDraw
import cv2
import numpy as np

import datetime
import io
import imghdr
import pathlib
import time

import pandas as pd
import requests

headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko"
}


def fetch_file(url, filename, dir="."):

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

    # 画像種類判定
    suffix = imghdr.what(io.BytesIO(r.content))

    p = pathlib.Path(dir, filename)
    p.parent.mkdir(parents=True, exist_ok=True)

    # 拡張子指定
    p_suffix = p.with_suffix(f".{suffix}")

    with p_suffix.open(mode="wb") as fw:
        fw.write(r.content)

    return p_suffix


# 画像に文字を入れる関数
def telop(src, message, font_size=160):

    width = src.shape[1]
    height = src.shape[0]

    # 画像サイズの1/32

    margin = width >> 5
    size = height >> 5

    img = Image.fromarray(src)
    draw = ImageDraw.Draw(img)

    n = font_size

    while n > 10:

        font = ImageFont.truetype(font_path, n)
        w, h = draw.textsize(message, font)

        if h < size:
            break

        n -= 1

    position = (int(width - w - margin), int(height - (height >> 4)))

    # BGR
    draw.text(
        position, message, font=font, fill="#00f", stroke_width=6, stroke_fill="#fff"
    )

    # PIL型の画像をcv2(NumPy)型に変換
    img = np.array(img)

    return img


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

# フォームのスプレッドのURL
url = ""

df0 = pd.read_csv(
    url,
    parse_dates=["タイムスタンプ", "発見日"],
    dtype={
        "緯度・経度": str,
        "eNB": str,
        "LCID": str,
        "PCI": str,
        "4Gアンテナ": str,
        "5Gアンテナ(sub6)": str,
        "5Gアンテナ(ミリ波)": str,
    },
)
df0.tail(10)

# 直近7日

dt_7dy = dt_now - datetime.timedelta(days=7)

df1 = df0[(df0["タイムスタンプ"] > dt_7dy) & df0["確認日"].isna()].copy()

len(df1)

df1["場所"] = df1["市町村"].str.cat(df1["町名以降"])

df1[["緯度", "経度"]] = df1["緯度・経度"].str.split(",", expand=True)

df1["緯度"] = df1["緯度"].str.strip().astype(float)
df1["経度"] = df1["経度"].str.strip().astype(float)

df1["eNB-LCID"] = df1["eNB"].str.cat(df1["LCID"], "-")

df1["設置タイプ"] = df1["設置タイプ"].str.replace("コン柱", "")

df1["4Gアンテナ"] = df1["4Gアンテナ"].str.replace("なし", "-")
df1["5Gアンテナ(sub6)"] = df1["5Gアンテナ(sub6)"].str.replace("なし", "-")
df1["5Gアンテナ(ミリ波)"] = df1["5Gアンテナ(ミリ波)"].str.replace("なし", "-")

df1["photos"] = (
    df1["基地局写真"]
    .fillna("")
    .str.replace("open?", "uc?export=download&", regex=False)
    .apply(lambda s: [x.strip() for x in s.split(",") if x])
)
df1["densoku"] = (
    df1["電測結果"]
    .fillna("")
    .str.replace("open?", "uc?export=download&", regex=False)
    .apply(lambda s: [x.strip() for x in s.split(",") if x])
)
df1["Twitter"] = (
    df1["Twitter"].fillna("").apply(lambda s: [x.strip() for x in s.split(",") if x])
)

# 開局

convert = False

print("=" * 30)

for i, row in df1.iterrows():

    print("-" * 30)

    info = "開局" if row["開局情報"] == "あり" else "発見"
    correct = "○" if row["基地局写真"] else "●"

    print(f"{correct}新規{info}")
    print()
    print(f'【日付】\n{row["発見日"]:%Y/%m/%d}')

    print()
    print(f'【名前】\n{row["名前"]}')

    print()
    print("【場所】")
    print(f'{row["場所"]}\n({row["緯度"]}, {row["経度"]})')

    if row["開局情報"] == "あり":
        print()
        print("【基地局】")
        print("・eNB-LCID:", row["eNB-LCID"])
        print("・PCI:", row["PCI"])

    print()
    print("【地図】")
    print(f'http://maps.google.co.jp/maps?q={row["緯度"]},{row["経度"]}')

    print()
    print(f"#愛媛 #楽天モバイル #基地局 #{info}")
    print("-" * 30)

    if row["Twitter"]:

        print()
        print("【Twitter】")

        for url_twit in row["Twitter"]:

            print(url_twit)

    if row["photos"]:

        print()
        print("【写真】")

        for num, url_img in enumerate(row["photos"], 1):

            print(url_img)

            fn = f'{row["場所"]}_写真_{num:02}'

            p_img = fetch_file(url_img, fn, "image")

            src = cv2.imread(str(p_img), 1)

            img = telop(src, f'{row["発見日"]:%Y/%m/%d} {row["名前"]}')

            p_out = pathlib.Path("output", p_img.name)
            p_out.parent.mkdir(parents=True, exist_ok=True)

            cv2.imwrite(str(p_out), img)

    if row["densoku"]:

        print()
        print("【電測結果】")

        for num, url_img in enumerate(row["densoku"], 1):

            print(url_img)

            fn = f'{row["場所"]}_電測_{num:02}'

            fetch_file(url_img, fn, "output")

    print("=" * 30)

    time.sleep(1)

df1

# TSV作成

df2 = (
    df1.rename(
        columns={
            "名前": "情報提供",
            "4Gアンテナ": "sector",
            "5Gアンテナ(sub6)": "sub6",
            "5Gアンテナ(ミリ波)": "ミリ波",
        }
    )
    .reindex(
        columns=[
            "場所",
            "場所補足",
            "設置タイプ",
            "市町村",
            "説明",
            "情報提供",
            "状況",
            "sector",
            "sub6",
            "ミリ波",
            "eNB-LCID",
            "PCI",
            "緯度",
            "経度",
        ]
    )
    .copy()
)

df2["状況"] = "close"
df2["状況"].where(df2["eNB-LCID"].isnull(), "open", inplace=True)

df2

df2.to_csv("result.tsv", sep="\t", index=False)

# !zip -r output.zip output

from google.colab import files

files.download("output.zip")