はじめに
この記事では、Django + PostgreSQL + Matplotlib を使って、
YouTubeコメント分析の管理画面＆可視化ダッシュボードを構築する手順をまとめます。
Pythonベースで動作するため、AI分析やOpenAI APIとの連携にも発展しやすい構成です。
プロジェクト構成
django_ai_dashboard/
├── manage.py
├── requirements.txt
├── db.sqlite3
├── myproject/
│ ├── settings.py
│ ├── urls.py
│ ├── wsgi.py
└── myapp/
├── models.py
├── views.py
├── urls.py
├── admin.py
└── templates/
└── index.html
セットアップ手順
仮想環境と依存関係
python -m venv venv
venv\Scripts\activate
pip install -r requirements.txt
pip install psycopg2-binary
requirements.txt:
Django==5.0.6
pandas
matplotlib
psycopg2-binary
PostgreSQL設定
myproject/settings.py:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'ai_memory_utf8',
'USER': '???',
'PASSWORD': '???',
'HOST': 'localhost',
'PORT': '5432',
}
}
モデル定義
from django.db import models
class YouTubeComment(models.Model):
video_id = models.CharField(max_length=50)
comment_id = models.CharField(max_length=100, unique=True)
author = models.CharField(max_length=100)
comment_text = models.TextField()
like_count = models.IntegerField(default=0)
reply_count = models.IntegerField(default=0)
engagement_score = models.FloatField(default=0)
created_at = models.DateTimeField()
ai_reply = models.TextField(null=True, blank=True)
embedding = models.JSONField(null=True, blank=True)
class Meta:
db_table = "youtube_comments"
ordering = ['-created_at']
def __str__(self):
return f"{{self.author}}: {{self.comment_text[:40]}}..."
管理画面登録
from django.contrib import admin
from .models import YouTubeComment
@admin.register(YouTubeComment)
class YouTubeCommentAdmin(admin.ModelAdmin):
list_display = ('author', 'like_count', 'reply_count', 'created_at')
search_fields = ('author', 'comment_text')
マイグレーション
python manage.py makemigrations
python manage.py migrate
スーパーユーザー作成
python manage.py createsuperuser
ログイン:
http://127.0.0.1:8000/admin
ダッシュボード可視化
from django.shortcuts import render
from .models import YouTubeComment
import matplotlib.pyplot as plt
import io, base64
import pandas as pd
def index(request):
comments = YouTubeComment.objects.all()[:100]
df = pd.DataFrame(list(comments.values('like_count', 'reply_count', 'author')))
if not df.empty:
fig, ax = plt.subplots()
ax.scatter(df['like_count'], df['reply_count'], alpha=0.7)
ax.set_xlabel("Likes")
ax.set_ylabel("Replies")
ax.set_title("YouTube Comment Engagement")
buffer = io.BytesIO()
plt.savefig(buffer, format='png')
buffer.seek(0)
image_png = buffer.getvalue()
buffer.close()
graphic = base64.b64encode(image_png).decode('utf-8')
else:
graphic = None
return render(request, 'index.html', {
'graphic': graphic,
'comments': comments,
})
index.html
<!DOCTYPE html>
<html>
<head>
<title>AI Comment Analysis Dashboard</title>
</head>
<body>
<h1>AI Comment Analysis Dashboard</h1>
{% raw %}{% if graphic %}{% endraw %}
<img src="data:image/png;base64,{% raw %}{{ graphic }}{% endraw %}" alt="Chart">
{% raw %}{% endif %}{% endraw %}
<h2>Comment Data</h2>
<ul>
{% raw %}{% for c in comments %}{% endraw %}
<li>{% raw %}{{ c.author }}{% endraw %}: {% raw %}{{ c.comment_text|truncatechars:80 }}{% endraw %}</li>
{% raw %}{% endfor %}{% endraw %}
</ul>
</body>
</html>
実行
python manage.py runserver
アクセス:
http://127.0.0.1:8000/
今後の展望
- OpenAI Embedding で意味類似度クラスタリング
- pgvector 検索による関連コメント抽出
- Plotly / Chart.js によるインタラクティブ化
- Django REST Framework でAPI化