{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "84d74f45",
   "metadata": {},
   "outputs": [],
   "source": [
    "from suno_utils.utils.podcasts import load_podcast_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0459f2fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_df = load_podcast_db(\"/mnt/data-ssd-1/data/podcasts/meta/podcastindex_feeds.db\", english_only=False, anchor_only=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "30970e90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4061478"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40072aec",
   "metadata": {},
   "outputs": [],
   "source": [
    "avg episode ~0.5h"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "43a73c6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_df[\"episode_count\"] = full_df[\"episode_count\"].clip(upper=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "7df960c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_df[\"language\"] = full_df[\"language\"].str.split(\"-\").str[0].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1bd5432f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = full_df.groupby(\"language\")[\"episode_count\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e157e2a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_values(ascending=False)\n",
    "df = df * 0.5\n",
    "s = df[df >= 100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a7c0a9ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(172,)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6a33d2b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "language\n",
       "en     18814771.5\n",
       "es      3200138.0\n",
       "pt      1402866.0\n",
       "de       951184.5\n",
       "zh       689475.5\n",
       "          ...    \n",
       "din         110.0\n",
       "iu          101.5\n",
       "kw          100.5\n",
       "aii         100.0\n",
       "aus         100.0\n",
       "Name: episode_count, Length: 172, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "47cf52e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18759.5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.loc[\"af\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f72ba65a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(94,)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[s>=1000].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6b8b018",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5bcc34b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e77f6aa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0b3da53c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_podcast_db(\"/mnt/data-ssd-1/data/podcasts/meta/podcastindex_feeds.db\", english_only=True, anchor_only=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fe9ece1a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2172082"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f73f9ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "4.1M total podcasts\n",
    "2.2M english podcasts - 56M episodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8183447b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55750471"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"episode_count\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "47ec96f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37634531"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"episode_count\"].clip(upper=100).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "31b4dcb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                                                                      1\n",
       "url                               https://markalanwilliams.libsyn.com/rss\n",
       "title                                  Christianity Questions and Answers\n",
       "last_update                                           2021-11-11 04:08:49\n",
       "link                           http://markalanwilliams.libsyn.com/webpage\n",
       "last_http_status                                                      200\n",
       "dead                                                                    0\n",
       "content_type                           application/rss+xml; charset=utf-8\n",
       "itunes_id                                                      1435175190\n",
       "original_url                      https://markalanwilliams.libsyn.com/rss\n",
       "itunes_author                                      Dr. Mark Alan Williams\n",
       "itunes_owner_name                                           Mark Williams\n",
       "explicit                                                                0\n",
       "image_url               https://ssl-static.libsyn.com/p/assets/e/7/5/d...\n",
       "itunes_type                                                      episodic\n",
       "generator                                            Libsyn WebEngine 2.0\n",
       "newest_item_pubdate                                   2021-01-09 21:13:35\n",
       "language                                                               en\n",
       "oldest_item_pubdate                                   2015-05-28 18:25:32\n",
       "episode_count                                                          90\n",
       "popularity_score                                                        0\n",
       "priority                                                               -1\n",
       "created_on                                            2020-08-06 22:21:27\n",
       "update_frequency                                                        9\n",
       "chash                                    52ad7fcfa9cf5e7e2b5abf9c1ee6da85\n",
       "host                                                           libsyn.com\n",
       "newest_enclosure_url    https://traffic.libsyn.com/secure/markalanwill...\n",
       "uid                                  78e9f51b-0ac3-5711-a921-1f2c7b5dbb35\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7a540cd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bb67d24",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a2baddc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "484f279c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc415710",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}