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]
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)))
draw.text(
position, message, font=font, fill="#00f", stroke_width=6, stroke_fill="#fff"
)
img = np.array(img)
return img
JST = datetime.timezone(datetime.timedelta(hours=+9))
dt_now = datetime.datetime.now(JST).replace(tzinfo=None)
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)
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
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)
from google.colab import files
files.download("output.zip")