0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yukitazono

[NOTE] Excelの緯度・経度データをインタラクティブな地図に表示

0
Posted at

はじめに

Excelに保存されている緯度・経度情報を、Plotlyを使って地図上に表示する。

今回の目的は、

  • Excelからデータを読み込む
  • 緯度・経度を取得する
  • 取引先名・支店名などを表示する
  • 地図上にマーカーを表示する
  • マーカーをクリックすると詳細情報を確認する
  • 一致件数などに応じてマーカーサイズを変更する
  • 一致件数などを色で表現する
  • ブラウザ上で拡大・縮小・移動する

というインタラクティブな地図を作成することです。

  1. 完成イメージ

例えばExcelに、

取引先名| 支店名| 緯度| 経度| 一致件数
A社| 東京支店| 35.6812| 139.7671| 3
B社| 大阪支店| 34.6937| 135.5023| 2
C社| 名古屋支店| 35.1815| 136.9066| 0

というデータがあるとします。

Plotlyで、

         ● A社 東京支店
         件数：3

● C社
件数：0

                ● B社
                件数：2

のように地図上へ表示できます。

さらに、

  • 件数が多い → マーカーを大きくする
  • 件数が少ない → マーカーを小さくする
  • 件数を色で表現する
  • マーカーをクリック → 詳細情報表示

といったことができます。

  1. 必要なライブラリ

PowerShellで、

python -m pip install pandas openpyxl plotly

を実行します。

  1. Excelの想定データ

例えば、

input.xlsx

の"管理表"シートに、

取引先名| 支店名| 緯度| 経度| 一致件数
A社| 東京支店| 35.6812| 139.7671| 3
B社| 大阪支店| 34.6937| 135.5023| 2
C社| 名古屋支店| 35.1815| 136.9066| 0

があるとします。

  1. 最もシンプルな地図表示

import pandas as pd
import plotly.express as px

Excel読み込み

df = pd.read_excel(
"input.xlsx",
sheet_name="管理表"
)

地図作成

fig = px.scatter_map(
df,
lat="緯度",
lon="経度",
hover_name="取引先名",
hover_data=[
"支店名",
"一致件数"
],
zoom=5,
height=700
)

地図表示

fig.show()

これだけでも、Excelの緯度・経度を地図上に表示できます。

  1. マーカーの大きさを一致件数にする

今回の用途では、こちらの表示が特に便利です。

fig = px.scatter_map(
df,
lat="緯度",
lon="経度",

size="一致件数",

hover_name="取引先名",

hover_data=[
    "支店名",
    "一致件数"
],

zoom=5,
height=700

)

fig.show()

例えば、

一致件数 = 0 → 小さい
一致件数 = 2 → 中くらい
一致件数 = 10 → 大きい

というように表示できます。

  1. 一致件数を色でも表示

マーカーのサイズだけでなく、色でも件数を表現できます。

fig = px.scatter_map(
df,
lat="緯度",
lon="経度",

size="一致件数",

color="一致件数",

hover_name="取引先名",

hover_data=[
    "支店名",
    "一致件数"
],

zoom=5,
height=700

)

fig.show()

これにより、

位置
＋
マーカーサイズ
＋
色

の3つでデータを表現できます。

  1. 表示する情報を増やす

例えばExcelに、

取引先名| 支店名| 都道府県| 住所| 緯度| 経度| 一致件数

がある場合、

fig = px.scatter_map(
df,
lat="緯度",
lon="経度",

size="一致件数",
color="一致件数",

hover_name="取引先名",

hover_data=[
    "支店名",
    "都道府県",
    "住所",
    "一致件数"
],

zoom=5,
height=700

)

fig.show()

とします。

マーカーにマウスカーソルを合わせると、

A社

支店名：東京支店
都道府県：東京都
住所：東京都○○区...
一致件数：3

のような情報を確認できます。

  1. 地図の中心を指定する

例えば東京周辺を中心にしたい場合、

fig.update_layout(
map_center={
"lat": 35.6812,
"lon": 139.7671
}
)

とします。

例えば日本全体なら、

fig.update_layout(
map_center={
"lat": 36.0,
"lon": 138.0
}
)

のようにできます。

  1. ズームレベルを調整

fig = px.scatter_map(
df,
lat="緯度",
lon="経度",
zoom=5
)

"zoom"の値を変更することで表示範囲を調整できます。

例えば、

zoom=3

→ 広い範囲

zoom=5

→ 日本全体程度

zoom=10

→ 都市レベル

zoom=13

→ 市街地レベル

というイメージです。

  1. HTMLファイルとして保存する

Plotlyはブラウザで動作するHTMLとして保存できます。

fig.write_html(
"map.html"
)

すると、

map.html

が作成されます。

ブラウザで開けば、

  • 拡大
  • 縮小
  • 地図移動
  • マーカー選択
  • データ確認

ができます。

  1. Excel処理と組み合わせる

先ほど作成した、

«「シートAの複数条件でシートBを検索して一致件数を取得する」»

処理と組み合わせることができます。

全体の流れは、

Excel
↓
シートA読み込み
↓
シートB読み込み
↓
複数条件で照合
↓
一致件数を計算
↓
シートAへ結果を書き込み
↓
緯度・経度を取得
↓
Plotlyで地図表示

となります。

  1. Excel照合＋Plotlyの統合例

import pandas as pd
import plotly.express as px

==================================================

設定

==================================================

PATH_EXCEL = "input.xlsx"

SHEET_A = "管理表"
SHEET_B = "検索データ"

MATCH_COLUMNS = [
"取引先名",
"支店名",
"緯度",
"経度",
]

RESULT_COLUMN = "一致件数"

==================================================

Excel読み込み

==================================================

df_a = pd.read_excel(
PATH_EXCEL,
sheet_name=SHEET_A
)

df_b = pd.read_excel(
PATH_EXCEL,
sheet_name=SHEET_B
)

==================================================

シートBを集計

==================================================

count_df = (
df_b
.groupby(MATCH_COLUMNS)
.size()
.reset_index(name=RESULT_COLUMN)
)

==================================================

シートAと結合

==================================================

df_a = df_a.merge(
count_df,
on=MATCH_COLUMNS,
how="left"
)

==================================================

一致しないものを0にする

==================================================

df_a[RESULT_COLUMN] = (
df_a[RESULT_COLUMN]
.fillna(0)
.astype(int)
)

==================================================

地図表示

==================================================

fig = px.scatter_map(
df_a,

lat="緯度",
lon="経度",

size=RESULT_COLUMN,
color=RESULT_COLUMN,

hover_name="取引先名",

hover_data=[
    "支店名",
    RESULT_COLUMN
],

zoom=5,

height=700

)

==================================================

地図のタイトル

==================================================

fig.update_layout(
title="取引先・支店別 一致件数マップ"
)

==================================================

HTML保存

==================================================

fig.write_html(
"map.html"
)

==================================================

表示

==================================================

fig.show()

  1. 「一致件数0」のデータも表示したい場合

今回のような業務では、

«一致したデータだけではなく、0件の取引先も確認したい»

というケースがあります。

今回のコードでは、

how="left"

を使用しているため、シートAに存在するデータは残ります。

df_a = df_a.merge(
count_df,
on=MATCH_COLUMNS,
how="left"
)

その後、

df_a[RESULT_COLUMN] = (
df_a[RESULT_COLUMN]
.fillna(0)
.astype(int)
)

とすることで、一致しなかったデータを0件として表示できます。

  1. マーカーをクリックしたときの情報

"hover_data"に指定した項目は、マーカーにカーソルを合わせたときに表示されます。

例えば、

hover_data=[
"支店名",
"住所",
"一致件数"
]

とすると、

取引先名
A社

支店名
東京支店

住所
東京都○○区...

一致件数
3

のように確認できます。

  1. 地図をHTMLで共有する

PlotlyのHTMLは、単なる画像ではなく、

«操作可能な地図»

として保存できます。

fig.write_html("map.html")

そのため、

Excel
↓
Python
↓
map.html

という流れで、ExcelデータをWebベースの地図として確認できます。

  1. CSVではなくExcelをそのまま使用

Excelの特定シートだけを読み込む場合、

df = pd.read_excel(
"input.xlsx",
sheet_name="管理表"
)

とします。

複数シートを扱う場合、

df_a = pd.read_excel(
"input.xlsx",
sheet_name="管理表"
)

df_b = pd.read_excel(
"input.xlsx",
sheet_name="検索データ"
)

とします。

  1. 実務でおすすめの構成

今回の用途では、最終的に、

project/
│
├─ .venv/
│
├─ src/
│ ├─ main.py
│ ├─ excel_processor.py
│ └─ map_generator.py
│
├─ input/
│ └─ input.xlsx
│
├─ output/
│ ├─ output.xlsx
│ └─ map.html
│
├─ requirements.txt
│
└─ .vscode/
└─ settings.json

のように分けると管理しやすくなります。

  1. requirements.txt

今回使用するライブラリは、

pandas
openpyxl
plotly

です。

インストール：

python -m pip install pandas openpyxl plotly

保存：

python -m pip freeze > requirements.txt

  1. さらに発展させる

この仕組みは、単純な地図表示だけでなく、かなり発展させることができます。

例えば、

① 件数による色分け

0件 → 少ない
1～5件 → 中
6件以上 → 多い

② 取引先ごとの色分け

A社 → 色A
B社 → 色B
C社 → 色C

③ 支店ごとの色分け

東京
大阪
名古屋
福岡

④ Google Mapsとの連携

緯度・経度から、

Google Maps

を開くリンクを作る。

⑤ 距離計算

基準地点から、

東京支店 → 5.2 km
大阪支店 → 12.4 km

のように距離を計算する。

⑥ エリア別集計

都道府県・市区町村などで集計する。

  1. 特におすすめの発展形

今回の「複数条件＋緯度経度＋一致件数」という構成なら、

         Excel
           ↓
   ┌─────────────┐
   │ データ照合   │
   └──────┬──────┘
          ↓
   一致件数を計算
          ↓
   ┌─────────────┐
   │ pandas      │
   └──────┬──────┘
          ↓
   ┌─────────────┐
   │ Plotly      │
   │ Interactive │
   │ Map         │
   └──────┬──────┘
          ↓
       map.html

という構成がおすすめです。

最終的には、

「Excelを更新 → Pythonを実行 → 自動的に地図が更新される」

という仕組みにできます。

さらにPlotlyの"Dash"まで発展させると、

┌───────────────────────────────────┐
│ 取引先・支店 分布ダッシュボード │
├───────────────────────────────────┤
│ │
│ [取引先▼] [支店▼] [件数▼] │
│ │
│ 🗺️ Interactive Map │
│ │
│ ● ● │
│ ● │
│ ● │
│ │
├───────────────────────────────────┤
│ 総取引先数：120 │
│ 総支店数　：350 │
│ 合計件数　：1,245 │
└───────────────────────────────────┘

のようなExcelデータを元にしたインタラクティブな業務ダッシュボードにもできます。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?