WordPressのテーマCocoonでは記事ごとにアクセス数がわかります。
管理画面やログインしていると表示されます。
そのデータが、どこにあるか分かったので、
取り出してPythonで集計する方法をご紹介します。JupiterNotebookで実行します。
最終的にはStreamlitでグラフで公開しました。
イチゲブログ、イチゲブログ別館2階のアクセス数（Cocoon集計値）
どこにあるか
データベースにあります。サーバーによりますがphpmyadminが動くものであれば取り出せます。
無料のシンクラウド for Freeで実際にやってみました。
wp_cocoon_accesses
シンクラウド for Freeの場合
サーバーパネル→データベース→phpmyadmin(MariaDB10.5)
ユーザー名はMySQLユーザー設定タブのアクセス権所有データベース
パスワードは、詳細をクリックすると変更できます。
画面左でユーザー名のディレクトリを開いてwp_cocoon_accessesをクリック→エクスポートをクリック
→フォーマット：csv、行：すべての行をダンプする
→エクスポート
wp_cocoon_accesses.csvというファイルがダウンロードできます。
中身はこんな感じ。必要なのは赤色のデータです。
"id","post_id","post_type","date","count","last_ip"
"1","1","post","2023-10-07","5","14.8.96.194"
"2","12","post","2023-10-07","2","220.108.176.140"
"3","12","post","2023-10-08","3","45.250.255.9"お名前ドットコムの場合
レンタルサーバーコントロールパネル→データベース→phpmyadmin→ユーザー名、パスワードで後は同じです。
私のブログは開設してから3年、約200記事なのでエクスポートしたファイルサイズが
wp_cocoon_accesses.csvが3M、wp_posts.csvが280Mでした。
目次へ
wp_posts
wp_cocoon_accesses.csvを見ると投稿記事の判別がpost_idという数字で分かりにくいです。
post_idとタイトルの関係もデータベースにあるので、それもエクスポートします。
それはwp_postsです。同じ要領でエクスポートすると
wp_posts.csvというファイルがダウンロードできます。
IDがwp_cocoon_accesses.csvのpost_idに対応しています。
post_titleが記事のタイトルです。
しかしこのデータ、post_contentが投稿内容になるのでファイルサイズは大きくなります。
"ID","post_author","post_date","post_date_gmt","post_content","post_title",・・・pythonで加工
以下のコードと同じところに
wp_cocoon_accesses.csvとwp_posts.csvを置きます。
#必要なライブラリ
import pandas as pd
from datetime import date
#wp_cocoon_accesses.csvをデータフレーム化
df = pd.read_csv('wp_cocoon_accesses.csv', index_col=0)
df
結果
id post_id post_type date count last_ip
1 1 post 2023-10-07 5 *.*.*.194
2 12 post 2023-10-07 2 *.*.*.140
3 12 post 2023-10-08 3 *.*.*.9
#wp_posts.csvをデータフレーム化
df1 = pd.read_csv('wp_posts.csv', usecols=["ID","post_title"])
df1
結果
ID post_title
0 1 Hello world!
1 2 サンプルページ
2 3 プライバシーポリシー
# 項目名を揃えます
df1.rename(columns={'ID': 'post_id'}, inplace=True)
# post_idをキーにして内部結合
merged_df = pd.merge(df, df1, on='post_id', how='inner')
# df1だけにあるpost_idの行を削除
merged_df = merged_df[merged_df['post_id'].isin(df1['post_id'])]
# 不要な項目を削除
merged_df.drop(columns=['last_ip'], inplace=True)
# 結合後のデータフレームを表示
merged_df
結果
post_id post_type date count post_title
0 1 post 2023-10-07 5 Hello world!
1 1 post 2023-10-08 1 Hello world!
2 1 post 2023-10-09 1 Hello world!
# 文字列から数字に変換
df['count'] = df['count'].astype(int)
df['post_id'] = df['post_id'].astype(int)これで集計する準備ができました。
目次へ
全期間の合計
# post_idごとに全期間の合計を計算
aggregated_df = merged_df.groupby('post_id').agg({
'count': 'sum', # 合計
'post_title': 'first' #列の最初の値
}).reset_index()
# 昇順に並び替え
aggregated_df.sort_values(by='post_id')
結果
post_id count post_title
0 1 38 Hello world!
1 12 19 Bingに聞いてブログを書いてみた!
2 16 13 PageSpeed Insightsでシンクラウド for Freeを測定してみた。日、週、月ごとに集計
データフレームのgroupbyで集計すると
週と月の集計値はCocoonの集計期間の取り方の違いで値が違ってます。
# 日付型 (datetime) に変換
merged_df['date'] = pd.to_datetime(merged_df['date'])
# 日付ごとに集計
daily_aggregated = merged_df.groupby(['post_id', pd.Grouper(key='date', freq='D')])['count'].sum().reset_index()
# 週ごとに集計、集計期間が多分、月～日でCocoonと異なる。
#参考：https://hesma2.hatenablog.com/entry/2021/01/22/003526
#Cocoonは直近7日間で集計してるかも
weekly_aggregated = merged_df.groupby(['post_id', pd.Grouper(key='date', freq='W')])['count'].sum().reset_index()
# 月ごとに集計、集計期間が多分、月ごとでCocoonと異なる。Cocoonは直近１か月で集計してるかも
monthly_aggregated = merged_df.groupby(['post_id', pd.Grouper(key='date', freq='M')])['count'].sum().reset_index()
print("post_idに対する日付ごとの集計:")
print(daily_aggregated)
print("\npost_idに対する週ごとの集計:")
print(weekly_aggregated)
print("\npost_idに対する月ごとの集計:")
print(monthly_aggregated)csvへの出力
weekly_aggregated.to_csv("weekly.csv")
目次へStreamlitで週のアクセス数を公開
イチゲブログ、イチゲブログ別館2階のアクセス数（Cocoon集計値）
特徴は
- ２つのブログのアクセス数を切り替えられる。
- post_titleを選択して２個グラフ表示できる。
- 任意の期間で表示できる。
- y軸は大きい方のmaxカウント値をmaxに設定。デフォルト20。
- データ（weekly.csv、wp_posts.csv）をアップロードできる。
（パスワードで保護しているが、パスワードはソース自体をpublicでGitHubにあげているので丸見えです。）
(st.file_uploaderは200Mまでしかアップロードできないようなのでwp_posts.csvはJupiterNotebookで不要な部分を削除してサイズを小さくしないとダメです。)
(st.file_uploaderでアップロードしたファイルは、一時的なものなのでアプリがスリープ状態になってしまうと、GitHubでデプロイしたときのcsvファイルに戻ります。）
まとめ
Cocoonの週と月の集計の仕方が不明だが、恐らく直近7日と1月で算出してると思われる。
今回使ったPythonのpandasのgroupbyとは集計方法が違うので週と月の値が異なってくる。
目次へ
イチゲをOFUSEで応援する(御質問でもOKです）Vプリカでのお支払いがおすすめです。
MENTAやってます（ichige）
コメント