user_profiling_project

The project utilizes a simple system for profilizing users and their posts via sentiment analysis to provide features for predicting LoL matches outcomes.

Usage

Run Dashboard: bash streamlit run dashboard/dashboard.py

📄 Database Schema (sentiment.db)

All project data is stored in a single SQLite database:

./database/sentiment.db

The database contains three main tables: users, tweets, and profiles.

---

1. users

Stores unique Twitter/X users discovered during data collection, that have been anonymized.

Columns:

• id — INTEGER, primary key, autoincrement
• username — TEXT, unique, not null

DDL:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL
);

---

2. tweets

Stores tweet content, metadata, and sentiment scores.

Columns:

• id — INTEGER, primary key
• user_id — INTEGER, foreign key → users(id), not null
• timestamp — TEXT, ISO-8601 timestamp, not null
• text — TEXT, tweet content, not null
• location — TEXT, user location (nullable)
• like_count — INTEGER, number of likes (nullable)
• view_count — INTEGER, number of views (nullable)
• vader_compound — REAL, VADER sentiment score (nullable)
• roberta_compound — REAL, RoBERTa sentiment score (nullable)
• vader_label — TEXT, VADER sentiment label (positive/neutral/negative)
• roberta_label — TEXT, RoBERTa sentiment label (positive/neutral/negative)
• vader_pos — REAL, VADER positive score
• vader_neg — REAL, VADER negative score
• roberta_pos — REAL, RoBERTa positive probability
• roberta_neg — REAL, RoBERTa negative probability

DDL:

CREATE TABLE IF NOT EXISTS tweets (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    timestamp TEXT NOT NULL,
    text TEXT NOT NULL,
    location TEXT,
    like_count INTEGER,
    view_count INTEGER,
    vader_compound REAL,
    roberta_compound REAL,
    vader_label TEXT,
    roberta_label TEXT,
    vader_pos REAL,
    vader_neg REAL,
    roberta_pos REAL,
    roberta_neg REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

---

3. profiles

Stores aggregated sentiment per user.

Columns:

• user_id — INTEGER, primary key, foreign key → users(id)
• avg_vader — REAL, average VADER score (nullable)
• avg_roberta — REAL, average RoBERTa score (nullable)
• compound_sentiment — REAL, combined metric (nullable)
• label — TEXT, sentiment classification label (nullable)

DDL:

CREATE TABLE IF NOT EXISTS profiles (
    user_id INTEGER PRIMARY KEY,
    avg_vader REAL,
    avg_roberta REAL,
    compound_sentiment REAL,
    label TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);