<jupyter_start><jupyter_text>データの読込  加盟店一覧のPDFをダウンロード<jupyter_code>!wget https://cashless.go.jp/assets/doc/kameiten_touroku_list.pdf -O list.pdf<jupyter_output>--2019-09-09 01:07:49--  https://cashless.go.jp/assets/doc/kameiten_touroku_list.pdf
Resolving cashless.go.jp (cashless.go.jp)... 172.16.31.10, 172.16.58.3, 192.168.3.11, ...
Connecting to cashless.go.jp (cashless.go.jp)|172.16.31.10|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 31809865 (30M) [application/pdf]
Saving to: ‘list.pdf’

list.pdf            100%[===================>]  30.34M  6.53MB/s    in 4.3s    

2019-09-09 01:07:53 (7.04 MB/s) - ‘list.pdf’ saved [31809865/31809865]<jupyter_text>popplerをインストール<jupyter_code>!apt install poppler-utils poppler-data<jupyter_output>Reading package lists... Done
Building dependency tree       
Reading state information... Done
poppler-data is already the newest version (0.4.8-2).
poppler-utils is already the newest version (0.62.0-2ubuntu2.10).
0 upgraded, 0 newly installed, 0 to remove and 8 not upgraded.<jupyter_text>PDFファイルの情報を確認<jupyter_code>!pdfinfo list.pdf<jupyter_output>Creator:        Microsoft® Excel® for Office 365
Producer:       Microsoft® Excel® for Office 365
CreationDate:   Fri Sep  6 02:04:52 2019 UTC
ModDate:        Fri Sep  6 03:07:03 2019 UTC
Tagged:         no
UserProperties: no
Suspects:       no
Form:           AcroForm
JavaScript:     no
Pages:          6360
Encrypted:      no
Page size:      595.2 x 841.92 pts (A4)
Page rot:       0
File size:      31809865 bytes
Optimized:      no
PDF version:    1.7<jupyter_text>ページ数確認<jupyter_code>max_page = !echo $(pdfinfo list.pdf | grep Pages | awk '{print $2}')
max_page<jupyter_output><empty_output><jupyter_text>tabula-pyをインストール<jupyter_code>!pip install tabula-py<jupyter_output>Requirement already satisfied: tabula-py in /usr/local/lib/python3.6/dist-packages (1.4.1)
Requirement already satisfied: numpy in /usr/local/lib/python3.6/dist-packages (from tabula-py) (1.16.5)
Requirement already satisfied: distro in /usr/local/lib/python3.6/dist-packages (from tabula-py) (1.4.0)
Requirement already satisfied: pandas in /usr/local/lib/python3.6/dist-packages (from tabula-py) (0.24.2)
Requirement already satisfied: python-dateutil>=2.5.0 in /usr/local/lib/python3.6/dist-packages (from pandas->tabula-py) (2.5.3)
Requirement already satisfied: pytz>=2011k in /usr/local/lib/python3.6/dist-packages (from pandas->tabula-py) (2018.9)
Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.6/dist-packages (from python-dateutil>=2.5.0->pandas->tabula-py) (1.12.0)<jupyter_text>PDFから表データ抽出<jupyter_code>from tabula import read_pdf

pages = f"3-{max_page[0]}"

df = read_pdf("list.pdf", lattice=True, pages=pages, java_options=["-Xmx2G"], pandas_options={"names": ["No.","都道府県","市区町村","事業所名(屋号)","業種","区分","還元率"]})

df<jupyter_output>/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py:2822: DtypeWarning: Columns (3,4,5,6) have mixed types. Specify dtype option on import or set low_memory=False.
  if self.run_code(code, result):<jupyter_text>データの前処理<jupyter_code># 表タイトル除去
df = df[df["No."] != "No."]<jupyter_output><empty_output><jupyter_text>データの整形<jupyter_code># グループ別に分ける
grouped_df = df.groupby((df["No."] == '1').cumsum())<jupyter_output><empty_output><jupyter_text>固定店舗（EC・通信販売を除く）<jupyter_code>df1 = grouped_df.get_group(1).copy()
df1.drop(columns="No.", inplace=True)
df1.reset_index(inplace=True, drop=True)
df1.index += 1
df1<jupyter_output><empty_output><jupyter_text>EC・通信販売（楽天市場）<jupyter_code>df2 = grouped_df.get_group(2).copy()
df2.drop(columns=["No.", "事業所名(屋号)", "業種", "区分", "還元率"], inplace=True)
df2.rename(columns={"都道府県": "事業所名(屋号)", "市区町村": "還元率"}, inplace=True)
df2["業種"] = "EC・通信販売"
df2["区分"] = "楽天市場"
df2.reset_index(inplace=True, drop=True)
df2.index += 1
df2<jupyter_output><empty_output><jupyter_text>EC・通信販売（Yahoo!ショッピング）<jupyter_code>df3 = grouped_df.get_group(3).copy()
df3.drop(columns=["No.", "事業所名(屋号)", "業種", "区分", "還元率"], inplace=True)
df3.rename(columns={"都道府県": "事業所名(屋号)", "市区町村": "還元率"}, inplace=True)
df3["業種"] = "EC・通信販売"
df3["区分"] = "Yahoo!ショッピング"
df3.reset_index(inplace=True, drop=True)
df3.index += 1
df3<jupyter_output><empty_output><jupyter_text>EC・通信販売（その他ECサイト）<jupyter_code>df4 = grouped_df.get_group(4).copy()
df4.drop(columns=["No.", "事業所名(屋号)", "業種", "区分", "還元率"], inplace=True)
df4.rename(columns={"都道府県": "事業所名(屋号)", "市区町村": "還元率"}, inplace=True)
df4["業種"] = "EC・通信販売"
df4["区分"] = "その他ECサイト"
df4.reset_index(inplace=True, drop=True)
df4.index += 1
df4<jupyter_output><empty_output><jupyter_text>データを連結<jupyter_code>import pandas as pd
df_all = pd.concat([df1, df2, df3, df4], sort = False)
df_all["還元率"] = df_all["還元率"].str.rstrip("%").astype(float)
df_all<jupyter_output><empty_output><jupyter_text>ファイルに保存<jupyter_code>df_all.to_csv('kameiten_touroku_list.tsv', sep='\t')<jupyter_output><empty_output><jupyter_text>データの分析<jupyter_code>!pip install japanize-matplotlib
import matplotlib.pyplot as plt
import seaborn as sns

import japanize_matplotlib
df_all.describe()<jupyter_output><empty_output><jupyter_text>都道府県別加盟店登録申請数  ランキング<jupyter_code>pref_rank = pd.DataFrame(df_all.groupby(by="都道府県").size(), columns=["申請数"]).reset_index()

pref_rank["順位"] = pref_rank["申請数"].rank(ascending=False, method="min").astype(int)

# 順位で昇順
pref_rank.sort_values("順位", inplace=True)


# 順位をインデックスに変更
pref_rank.set_index("順位", inplace=True)

pref_rank<jupyter_output><empty_output><jupyter_text>グラフ<jupyter_code>pref_rank.plot.barh(x="都道府県", y="申請数", figsize=(6, 12))<jupyter_output><empty_output><jupyter_text>区分別申請数  ランキング<jupyter_code>indust_rank = pd.DataFrame(df_all.groupby(by="区分").size(), columns=["申請数"]).reset_index()
indust_rank["順位"] = indust_rank["申請数"].rank(ascending=False, method="min").astype(int)

# 順位で昇順
indust_rank.sort_values("順位", inplace=True)


# 順位をインデックスに変更
indust_rank.set_index("順位", inplace=True)

indust_rank<jupyter_output><empty_output><jupyter_text>還元率別内訳<jupyter_code>df_indust = pd.pivot_table(df_all, index="区分", columns="還元率", values="事業所名(屋号)", aggfunc='count').fillna(0).astype(int)
df_indust<jupyter_output><empty_output><jupyter_text>グラフ<jupyter_code>df_indust.plot.barh(stacked=True, figsize=(9, 6))<jupyter_output><empty_output><jupyter_text>バイオリン図<jupyter_code>plt.figure(figsize=(10, 8))
p = sns.violinplot(x="区分", y="還元率", data=df_all)
p.set_xticklabels(p.get_xticklabels(), rotation=90)<jupyter_output><empty_output><jupyter_text>ダウンロード<jupyter_code>from google.colab import files

files.download('kameiten_touroku_list.tsv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17146319297182672, 'fraction_numerical': 0.04513783701908512, 'mean_word_length': 6.832740213523132, 'pattern_counts': {'":': 7, '<': 84, '<?xml version=': 0, '>': 93, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 4, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '640951', 'license': 'MIT', 'n_tokens_mistral': 3154, 'n_tokens_neox': 3082, 'n_words': 398, 'path': 'cashless-pdf2tsv.ipynb', 'repo_name': 'imabari/cashless-pdf2tsv', 'size': '6614'}