Python

Python で Excel の作業を自動化しようと思った際、openpyxl を使うと思います。
ただ、このopenpyxl のライブラリは .xlsbのファイルに対応していません。ただ、どうしてもxlsbのファイルを用いなければならない場合、どうすればよいのでしょうか。
それは、
Pythonで.xlsbから.xlsxに変換をし、保存をしてその.xlsxファイルを操作すればよいのです。

xlsbからxlsx にPythonで自動変換できるコードは以下になります。

from pyxlsb import open_workbook as open_xlsb
import pandas as pd

df = pd.read_excel('data/src/sample.xlsb', engine='pyxlsb') df.to_excel('data/src/sample.xlsx', index=False)

今回は、PandasとPyxlsbのライブラリを用います。ひとつひとつ解説していきます。

df = pd.read_excel('ファイルの位置', engine='pyxlsb')

df.to_excel('ファイルの位置', index=False)

pd.read_excelにおいては、はじめにファイルの位置を指定しその後エンジンとして pyxlsbというものを追加します。
もしシートの指定がしたければ sheet_name = を()の中に追加すればよいです。
このシートを指定しなければ、通常、はじめのSheet1のみ変換されます。すべてのxlsbのシートを変換したい場合、普通は sheet_name = None にすればよいのですが、やってみたところエラーが出てしまいます。複数のシートを変換したい場合は、dfを複数つくってみて、それぞれに異なるsheet_nameをつけてやれば良いでしょう。(他にいい方法があればコメントでお知らせください)

次にpandasで読み込んだエクセルの xlsb ファイルを今度は xlsx に変換してみましょう。.to_excelで変換することができます。この際 index = False というのを入れることを忘れないようにしましょう。というのも、もしこれを忘れると、変換したxlsxファイルのはじめの A 列にインデックス番号が表示されてしまいます。このインデックスナンバーによって、元の xlsb ファイルと次の変換された xlsx ファイルとの見た目が変わってしまうのです。それを防ぐためにFalseを入れています。

以上がxlsbファイルをxlsxファイルにPythonで変換する方法でした!



Pythonでエクセルの作業を自動化させましょう!
(最終更新:2020/11/27 コメントをいただき、返信・編集しました)

以前、エクセルを手動で作るのが手間に感じたため、Pythonでエクセル作成を自動化させるプログラムを作成しました。その際に、今回の「画像挿入、ポップアップ」の機能がネットを探しても見当たらなかったため、誰かの参考になればと思い、書き上げました。

○使用環境
Windows, Python, Openpyxl

今回は、エクセルシートに画像を挿入し、クリックすることで、拡大表示させてみたいと思います。


○最初に、結論!

画像選択

その画像がどこにあるのかを相対パスで表記

エクセルに自動でハイパーリンクを貼り付け

そのリンクをクリックすると、画像がポップアップで表示される


このような流れをPythonで表記していけばOKです。


単に、エクセルシートに画像を挿入するやりかたは、いろんなところで紹介されています(参考)。普通に画像を挿入し、貼り付けたいだけの場合は、他のサイトを参照してもらえれば簡単にできるかと思います。

しかし、大量にエクセルに画像を挿入すると、表をつくっている場合、見栄えが悪くなってしまいます。
そのため、画像を単に貼り付けるのではなく、画像をクリックすると、画像がポップアップ表示され、別画面で見られるようにしていきたいと思います。

考えられる方法としては、以下の2つあります。
①エクセルのコメント機能を利用し、そのコメント欄に写真を貼り付ける。
②ハイパーリンクでファイル場所を指定し、画像を表示。

Openpyxlでは、コメント機能を利用し、コメントを追加することはできますが、画像を自動で貼り付けることができるようなことは、現在のところできません。
そのため、今回は②のやり方で紹介していきます。

まずはじめは、ファイルをダイアルボックスから選択できるようにしましょう。Tkinterを利用します。このやり方は他のサイトでも紹介されています。
import tkinter
from tkinter import filedialog as tkFileDialog
root=tkinter.Tk()
root.withdraw()
filename = tkFileDialog.askopenfilename(filetypes=[("jpeg files","*.jpg"),("all files","*.*")])

これで、指定したファイルのfilenameを得ることができます。
ただし、Pythonの実行ファイルが存在するフォルダに、Picturesというフォルダを作る必要があります。表示したい画像は、そのPicturesのフォルダにすべて保存します。(リンクを作る上で、場所指定が必要なため)

【例】
Samplefile (folder)
  | - picturepop.py
  | - Pictures (folder)
      | - eg1.jpg 
      | - eg2.jpg

以下のコード、filepathでは、指定した画像ファイルがどの場所に存在しているのかを表記させています。たとえば、上記の【例】のファイル構成の場合、eg2.jpgを指定した場合、

./Pictures/eg2.jpg

となります。Samplefileフォルダがどの場所に置かれていても大丈夫なように、相対パスを利用しています。ここではWindowsで利用することを考え、Windows記法で書いています。MacであればMac記法で書けばいいでしょう。

import openpyxl as px
from openpyxl.styles import Alignment from openpyxl.styles import Font from openpyxl.styles.colors import Color

filename2 = os.path.basename(filename) filepath = os.path.join('./Pictures/', filename2)

以下のコードでは、Openpyxlにより、テンプレートのシートを読み込みます。(これらのやり方は多くの記事があるので調べてください。)そこで読み込んだシート(sheet)の、E列にHyperlinkをベタ貼りしています。A列に入れたい場合は、EをAに変えればいいです。
.format(s)のsは、for文における代入数字になります。今回、for文のコードは割愛しています。for文により、大量の画像を連続して挿入することも可能になります。
.fontでは、見やすいように、青色でハイパーリンクであることがわかりやすくしています。

sheet['E{}'.format(s)].value = '=HYPERLINK("{}", "Picture")'.format(filepath)
sheet['E{}'.format(s)].font=Font(name="Calibri", size=11, color=Color(rgb=None, indexed=None, auto=None, theme=10, tint=0.0, type="theme"))

【完成イメージ】
Screen Shot 2020-04-24 at 17.29.40

E列に、青色でPictureが表示されています。ここをクリックすると画像がポップアップします。上の灰色のPictureは、テンプレートでつくったものになります。

最後でwb.saveすれば、保存ができると思います。

ある程度、Openpyxlの使い方がわかっている方向けに最小限の説明で記載しました。コードをただコピーして貼り付けても動かないでしょう。自分の作りたいものに適する形で、アレンジしていただければと思います。この記事から学べることは、プログラムを作るための”アイデア”です。

かなり省略をした部分もありますので、わからない部分はコメントしていただければ幸いです。



【最終更新日】2019/2/12
今回は、イッテQの視聴率(ビデオリサーチ調べ,関東)をPythonを用いて予測してみようと思います。Python3、Jupyter Notebookを使用しています。

◯なぜイッテQ??
なぜイッテQを選んだかというと、
・常に高視聴率をキープしている
・通常放送が多く、データが取りやすいこと
・私自身、放送開始以来イッテQは見逃したことがほぼなく、視聴率も毎週チェックしているため、自分自身でも予測がしやすい
ことがあげられます。

◯参考にしたもの
TV朝日の視聴率推移をSARIMAモデルで予測してみる
→プログラミングはほぼこちらを参考にしました。エラーも出るため、多少の係数は変更し実行しました。

◯予測手法

今回は、SARIMAモデルと呼ばれる手法を用いて予測します。SARIMAモデルは、季節の要素やトレンドまで考慮に入れて予測します。視聴率はトレンドや季節に左右されることが多いため、有効な予測手段と言えます。

現在実用化されている視聴率予測には、電通のSHAREST_RTというものがあるようです。これは、過去の視聴率データ、番組ジャンル、出演者情報、ネット上のコンテンツ閲覧傾向が教師データになっているとのこと(参考)。今後は天候情報(雨の日のほうが在宅率が高く、高視聴率になりやすい)などを追加していき、精度向上を進めていくようです。

今回作成したプログラミングでは、過去の視聴率データのみを使用し、トレンド分析、将来予測をしていることになります。

◯手順
視聴率データは、ネットに転がっているのでそれらを集約しデータとしました。ちなみに、SP番組として「DASH・イッテQ交換留学」は本データに含めています。それ以外の特番(改編期の「DASHでイッテQ〜」や他の番組(DASHや行列)のSP)は抜いています。2017/1/8から、2019/1/27までのデータを入れています。

Screen Shot 2019-02-03 at 23.37.08

まず、データをプロットしてグラフ化してみます。
Screen Shot 2019-02-03 at 23.46.40

上がり下がりはあるものの、14%-22%の間を2年以上維持し続けているのはすごいです。
次に、Dickey-Fuller Testを行います。
Screen Shot 2019-02-03 at 23.56.41

赤色の線が移動平均を表しますが、あのイッテQ祭り疑惑以降(11月)、右肩下がりとなっていました。ゆるやかに下っています。

◯イッテQ!のトレンドと季節傾向
次が一番見ものの、トレンドと季節の傾向です。
Screen Shot 2019-02-03 at 23.59.18
トレンドは、2017年以降、緩やかに下っているのがわかります。祭り疑惑以降はさらに下がっています。これは、祭り疑惑の影響もありますが、大河ドラマとポツンと一軒家の視聴率好調もあったためと思われます。
季節の傾向では、4月と7月あたりで視聴率が下がる傾向にあるようです。一番組だけの視聴率ではなかなか季節感は読み取れないですね。日テレ全体のプライム視聴率で行えば、いつ上がり、いつ下がるのがよくわかるかもしれません。

◯予測結果
次に、SARIMAモデルで最適なパラメータを探し、モデルを作成。自己相関もほぼ問題ないことを確認し、予測を行いました。2018年から2019/2/10までを予測しました。以下がその結果とグラフです。
date
2018-01-07    16.963744
2018-01-28    19.132542
2018-02-04    17.856055
2018-02-11    19.639591
2018-02-18    17.340289
2018-02-25    18.048732
2018-03-04    19.975576
2018-03-11    17.000314
2018-03-18    18.424799
2018-03-25    17.869369
2018-04-08    18.322304
2018-04-22    18.440152
2018-05-06    16.409622
2018-05-13    19.879696
2018-05-20    18.933183
2018-05-27    17.889478
2018-06-03    19.500522
2018-06-10    18.424953
2018-06-17    18.186823
2018-06-24    20.271624
2018-07-01    17.813833
2018-07-08    17.901988
2018-07-15    20.061493
2018-07-22    16.831968
2018-07-29    19.877457
2018-08-05    19.444249
2018-08-12    17.486616
2018-08-19    19.855096
2018-09-02    17.239085
2018-09-09    18.154401
2018-09-16    17.930630
2018-09-23    19.115889
2018-10-21    17.219615
2018-10-28    17.962994
2018-11-04    18.526846
2018-11-11    17.181914
2018-11-18    18.285873
2018-12-02    16.342554
2018-12-09    17.279184
2018-12-16    16.623024
2018-12-23    17.008872
2019-01-06    16.385361
2019-01-20    16.347549
2019-01-27    18.579713
2019-02-03    16.610514
2019-02-10    16.777451
Screen Shot 2019-02-04 at 0.17.13
赤が予測値です。正確な数値は出ないものの、上がり下がりのおおまかな予測はできていることがわかります。そこまではずれは出ていません。一番差が顕著なのは、2018/12/2のとき。予測値は16.3%ですが、実際は14.0%でした。2.3%の誤差がありました。このときは、裏にM−1グランプリがあり、M-1が17.8%を獲得したのが影響しています。ただ、2017のときはイッテQ17.5%、M-1が15.4%でしたから、2018は16.3%が妥当と判断したのかもしれません。

◯AI vs 人間〜視聴率予測で正確なのはどっち?〜
これからは、実際に未来のデータを予測し、このプログラミング(AI)の予測が勝つのか、人間(イッテQスタッフ並に番組をチェックしてきた私)のどちらが勝つのかを検証していきます。

【検証1回目】2019/2/3
(AIの予測)16.6%
(私の予測)18.8%
(根拠)裏番組が基本先週と同じで、フジが新たな番組を始めるだけ。寒中水泳で多少はUPの可能性。ちなみに、過去2月第1週は、22.5%(2017), 20.2%(2018)と20%超えです。
【結果】18.4% 
AIの誤差1.8%、人間の誤差0.4%で人間の勝利

【検証2回目】2019/2/10
(AIの予測)17.5%
再度、最新の2/3の値をデータ入力した結果、AIの予測は以下のようになりました。
2019-02-10    17.512501
2019-02-17    17.278077
以前の予測値よりも、約1%程度UPし、17.5%という予測結果になりました。トレンドが少し上がってきていると判断したのでしょう。

(私の予測)18.2%

【結果】17.2%
AIの誤差0.3%、人間の誤差1.0%で、AIの勝利


まとめ
人間とAIの予測は互角の争いでした。AIの予測も十分に信頼できる値といえそうです。



↑このページのトップヘ

BACK
TO
TOP