Topic Analysis of US State Subreddits Using gpt-4o-mini

import csv
from datetime import datetime
import pprint
import time

import dataset
from dotenv import dotenv_values
import praw

config = dotenv_values(".env")
username = config["REDDIT_USERNAME"]
reddit = praw.Reddit(
    client_id=config["REDDIT_CLIENT_ID"],
    client_secret=config["REDDIT_CLIENT_SECRET"],
    password=config["REDDIT_PASSWORD"],
    username=username,
    user_agent=f"linux:script.s-hub:v0.0.1 (by /u/{username})",
)

db = dataset.connect("sqlite:///data.db")

# check that the reddit login worked
print(f"Logged Into Reddit As: {str(reddit.user.me())}")

states = []
with open("us_state_subs.csv") as f:
    for country in csv.DictReader(f):
        states.append(country)

for state in states:
    if not state["Subreddit"]:
        continue

    sub_name = state["Subreddit"].split("/r/")[-1].strip("/")
    subreddit = reddit.subreddit(sub_name)

    thread_ids = set()
    for thread in subreddit.top(time_filter="year", limit=None):
        thread_ids.add(thread.id)
        data = vars(thread)
        row = dict(
            thread_id = thread.id,
            title = thread.title,
            score = thread.score,
            subreddit = sub_name,
            created = datetime.utcfromtimestamp(thread.created_utc).isoformat(),
            comment_limit = data.get("comment_limit"),
            permalink = data.get("permalink"),
            selftext = data.get("selftext"),
            link_flair = data.get("link_flair_text")
        )
        db["threads"].insert(row)

from enum import Enum

from pydantic import BaseModel
import openai
from openai import OpenAI

import dataset
db = dataset.connect("sqlite:///data.db")

API_KEY = "<OPENAI_API_KEY_HERE>"
client = OpenAI(api_key=API_KEY)

class ThreadCategory(str, Enum):
    news_and_politics = "news and politics"
    classifieds = "classifieds"
    queries = "queries"
    opinions_and_rants = "opinions and rants"
    nature_and_culture = "nature and culture"
    sports = "sports"
    humor = "humor"
    others = "others"

class Result(BaseModel):
    category: ThreadCategory
    
def get_gpt_response(thread_title, flair):
    prompt = "Categorize the following reddit thread title into an appropriate category. You may use a flair as hint, if it is provided."
    prompt += "The title is: " + thread_title
    if flair:
        prompt += ". The flair is: " + flair

    completion = client.beta.chat.completions.parse(
        model="gpt-4o-mini",
        messages = [
            {
                "role": "system",
                "content": "You are a helpful assistant."
            },
            {
                "role": "user",
                "content": prompt
            }
        ],
        tools = [
            openai.pydantic_function_tool(Result)
        ],
    )
    return completion.choices[0].message.tool_calls[0].function.parsed_arguments.category

threads = list(db["threads"])
for row in threads:
    if row.get("gpt_response"):
        print("SKIPPING")
        continue

    resp_val = "others"
    if row["title"]!="...":
        response = get_gpt_response(row["title"], row.get("link_flair"))
        resp_val = str(response.value)

    db["threads"].update({"id": row["id"], "gpt_response": resp_val}, ["id"])

import dataset
import pandas as pd

db = dataset.connect("sqlite:///data.db")
states_list = pd.read_csv("us_state_subs.csv", dtype=str, na_filter=False)

# create a dict to lookup state names by subreddit
state_names = {}
for idx, s in states_list.iterrows():
    if s["Subreddit"]:
        key = s["Subreddit"].split("/r/")[-1].strip("/").lower()
        state_names[key] = s["State"]

state_wise_counters = {} 
for row in db["threads"]:
    key = row["subreddit"].lower()
    state_name = state_names[key]
    if state_name not in state_wise_counters:
        state_wise_counters[state_name] = {"state": state_name, "total_threads_analyzed": 0}

    category = row["gpt_response"]
    if category not in state_wise_counters[state_name]:
        state_wise_counters[state_name][category] = 0
    state_wise_counters[state_name][category] += 1
    state_wise_counters[state_name]["total_threads_analyzed"] += 1

data = []
for state_name in state_wise_counters:
    state = state_wise_counters[state_name]
    
    highest_val = 0
    highest_category = ""
    for key in state:
        if key not in ["state", "total_threads_analyzed"]:
            if state[key]>highest_val:
                highest_val = state[key]
                highest_category = key
            state[key] = round(100*state[key]/state["total_threads_analyzed"], 2)
    state["top_category"] = highest_category
    
    data.append(state)

df = pd.DataFrame.from_records(data, columns=[
    "state", "total_threads_analyzed",
    "news and politics", "opinions and rants", "nature and culture",
    "queries", "humor", "classifieds", "sports", "others",
    "top_category",
]).fillna(0)
df.to_csv("topic_percents.csv", index=False)

import dataset
import os
import spacy
from wordcloud import WordCloud

os.makedirs("results/", exist_ok=True)
db = dataset.connect("sqlite:///data.db")

nlp = spacy.load('en_core_web_sm')
def lemmatize(text):
    words = nlp(text)
    filtered_words = filter(lambda w: w.pos_ in ["NOUN", "PROPN"], words)
    return [word.lemma_.lower() for word in filtered_words]

def generate_wordcloud(category):
    if not category:
        return
    if category == "all":
        rows = db["threads"]
    else:
        rows = db["threads"].find(gpt_response=category)

    word_freq_dict = {}
    for row in list(rows):
        for word in lemmatize(row["title"]):
            if word not in word_freq_dict:
                word_freq_dict[word] = 0
            word_freq_dict[word] += 1
    wc = WordCloud(width=640, height=360).generate_from_frequencies(word_freq_dict)
    wc.to_file(f"results/wordcloud-{category}.png" )

generate_wordcloud("all")
categories = ["news and politics", "classifieds", "queries", "opinions and rants", "nature and culture", "sports", "humor"]
for c in categories:
    generate_wordcloud(c)

import mapclassify as mc
import pandas as pd
import geopandas as gpd
import os

from matplotlib.colors import ListedColormap, BoundaryNorm, LinearSegmentedColormap
from matplotlib.ticker import FuncFormatter
import matplotlib.image as image
import matplotlib.pyplot as plt
import geoplot.crs as gcrs
import geoplot as gplt

plt.rcParams["font.size"] = 14

os.makedirs("results/", exist_ok=True)
df = pd.read_csv("topic_percents.csv", index_col=False)
map_df = gpd.read_file("states_map.geojson")
merged_df = map_df.merge(
    df,
    left_on=["NAME"],
    right_on=["state"],
)

# plotting a column needs it to have numerical values
plot_vals = {"news and politics":0, "nature and culture": 1, "queries": 2}
merged_df["top_category_plot_val"] = merged_df["top_category"].apply(lambda x: plot_vals[x])

scheme = mc.UserDefined(merged_df["top_category_plot_val"], bins=[0,1])
cmap = ListedColormap(["#c12f51", "#2fc14c"])

ax = gplt.choropleth(
    merged_df,
    extent=(-124.733174, 24.544701, -66.949895, 52.384358),
    projection=gcrs.PlateCarree(),
    hue='top_category_plot_val', # column to be used for color coding
    scheme=scheme,
    cmap=cmap,
    linewidth=0,
    edgecolor='black',
    figsize=(16, 9),
    legend=True,
    legend_kwargs=dict(
        loc='lower center',
        bbox_to_anchor = (0.5, -0.30)
    ),
    legend_labels=["News and Politics", "Nature and Culture"],
)

# adding state codes on the plot
# with adjustments for neatness
for _i, r in merged_df.iterrows():
    coords = r.geometry.centroid.coords[0]
    xy = [coords[0]+96, coords[1]]
    if r["SHORT_CODE"]=="CT":
        xy[0] -= 0.2
        xy[1] -= 0.5 
    if r["SHORT_CODE"]=="RI":
        xy[0] += 0.5
        xy[1] -= 0.5
    if r["SHORT_CODE"]=="DE":
        xy[0] += 0.5
        xy[1] -= 0.5
    if r["SHORT_CODE"]=="MD":
        xy[1] += 0.5
    if r["SHORT_CODE"]=="DC":
        xy[1] -= 0.5
    ax.annotate(r["SHORT_CODE"], xy=xy, fontsize=8, ha="center", va="center")

fig = ax.get_figure()
logo = image.imread("logo.png")
logo_ax = fig.add_axes([0.04, 0.78, 0.15, 0.15], anchor="NW", zorder=1)
logo_ax.imshow(logo)
logo_ax.axis("off")

plt.title(f"Most talked about topic in each State's Subreddit",  ha="right", x=6.1, y=-0.3, loc="right", fontsize=18)
plt.savefig(f'results/map-top-categories.png')

def dump_results_for_category(category):
    # the top 5 table
    df2 = df[["state", category]].sort_values(by=category, ascending=False)
    cutoff_value = df2[category].iloc[4]
    df2 = df2[df2[category] >= cutoff_value]
    df2.to_markdown(f'results/top-{category}.md', index=False)
    
    # choropleth map
    bounds = [df[category].min(), df[category].max()]
    colors = ["#ccffec", "#003d27"]
    cmap = LinearSegmentedColormap.from_list('custom_colormap', list(zip(bounds, colors)))

    legend_format = lambda val, pos: str(val)+"%"

    ax = gplt.choropleth(
        merged_df,
        extent=(-124.733174, 24.544701, -66.949895, 52.384358),
        projection=gcrs.PlateCarree(),
        hue=category, # column to be used for color coding
        linewidth=0,
        edgecolor="black",
        figsize=(16, 9),
        legend=True,
        legend_kwargs=dict(
            spacing="proportional",
            location="bottom",
            format=FuncFormatter(legend_format),
            aspect=40,
            shrink=0.75,
        ),
    )

    # adding two letter state codes
    # with minor adjustments for neatness
    for _i, r in merged_df.iterrows():
        coords = r.geometry.centroid.coords[0]
        xy = [coords[0]+96, coords[1]]
        if r["SHORT_CODE"]=="CT":
            xy[0] -= 0.2
            xy[1] -= 0.2
        if r["SHORT_CODE"]=="RI":
            xy[0] += 0.5
            xy[1] -= 0.5
        if r["SHORT_CODE"]=="DE":
            xy[0] += 0.5
            xy[1] -= 0.5
        if r["SHORT_CODE"]=="MD":
            xy[1] += 0.3
        if r["SHORT_CODE"]=="DC":
            xy[1] -= 0.5
        ax.annotate(r["SHORT_CODE"], xy=xy, fontsize=10, ha="center", va="center")    

    fig = ax.get_figure()
    logo = image.imread("logo.png")
    logo_ax = fig.add_axes([0.04, 0.78, 0.15, 0.15], anchor="NW", zorder=1)
    logo_ax.imshow(logo)
    logo_ax.axis("off")

    plt.title(f"States that talk about {category} the most", ha="right", x=6.1, y=0, loc="right", fontsize=18)
    plt.savefig(f'results/map-{category}.png')

categories = list(filter(lambda x: x not in ["state", "total_threads_analyzed", "top_category", "others"], df))
for category in categories:
    dump_results_for_category(category)