content_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OCR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>frame_000000.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>frame_000012.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>frame_000024.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>frame_000036.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>frame_000048.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36903</th>\n",
       "      <td>frame_033996.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36904</th>\n",
       "      <td>frame_034008.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36905</th>\n",
       "      <td>frame_034020.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36906</th>\n",
       "      <td>frame_034032.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36907</th>\n",
       "      <td>frame_034044.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>36908 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name         dir_name speech  score  have_sub  left_top_x  \\\n",
       "0      frame_000000.jpg  assets\\frames\\1    NaN    0.0     False         0.0   \n",
       "1      frame_000012.jpg  assets\\frames\\1    NaN    0.0     False         0.0   \n",
       "2      frame_000024.jpg  assets\\frames\\1    NaN    0.0     False         0.0   \n",
       "3      frame_000036.jpg  assets\\frames\\1    NaN    0.0     False         0.0   \n",
       "4      frame_000048.jpg  assets\\frames\\1    NaN    0.0     False         0.0   \n",
       "...                 ...              ...    ...    ...       ...         ...   \n",
       "36903  frame_033996.jpg  assets\\frames\\9    NaN    0.0     False         0.0   \n",
       "36904  frame_034008.jpg  assets\\frames\\9    NaN    0.0     False         0.0   \n",
       "36905  frame_034020.jpg  assets\\frames\\9    NaN    0.0     False         0.0   \n",
       "36906  frame_034032.jpg  assets\\frames\\9    NaN    0.0     False         0.0   \n",
       "36907  frame_034044.jpg  assets\\frames\\9    NaN    0.0     False         0.0   \n",
       "\n",
       "       left_top_y  right_top_x  right_top_y  left_bottom_x  left_bottom_y  \\\n",
       "0             0.0          0.0          0.0            0.0            0.0   \n",
       "1             0.0          0.0          0.0            0.0            0.0   \n",
       "2             0.0          0.0          0.0            0.0            0.0   \n",
       "3             0.0          0.0          0.0            0.0            0.0   \n",
       "4             0.0          0.0          0.0            0.0            0.0   \n",
       "...           ...          ...          ...            ...            ...   \n",
       "36903         0.0          0.0          0.0            0.0            0.0   \n",
       "36904         0.0          0.0          0.0            0.0            0.0   \n",
       "36905         0.0          0.0          0.0            0.0            0.0   \n",
       "36906         0.0          0.0          0.0            0.0            0.0   \n",
       "36907         0.0          0.0          0.0            0.0            0.0   \n",
       "\n",
       "       right_bottom_x  right_bottom_y  \n",
       "0                 0.0             0.0  \n",
       "1                 0.0             0.0  \n",
       "2                 0.0             0.0  \n",
       "3                 0.0             0.0  \n",
       "4                 0.0             0.0  \n",
       "...               ...             ...  \n",
       "36903             0.0             0.0  \n",
       "36904             0.0             0.0  \n",
       "36905             0.0             0.0  \n",
       "36906             0.0             0.0  \n",
       "36907             0.0             0.0  \n",
       "\n",
       "[36908 rows x 13 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# Define the directory containing the CSV files\n",
    "csv_dir = r'assets/result/csv'\n",
    "\n",
    "# List all CSV files in the directory\n",
    "csv_files = [os.path.join(csv_dir, f) for f in os.listdir(csv_dir) if f.endswith('.csv')]\n",
    "\n",
    "# Read and merge all CSV files\n",
    "dataframes = [pd.read_csv(file) for file in csv_files]\n",
    "merged_df = pd.concat(dataframes, ignore_index=True)\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>frame_000756.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>小祥</td>\n",
       "      <td>0.999957</td>\n",
       "      <td>True</td>\n",
       "      <td>598.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>679.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>601.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>frame_000792.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>太好了</td>\n",
       "      <td>0.993245</td>\n",
       "      <td>True</td>\n",
       "      <td>481.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>481.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>frame_000804.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>太好了</td>\n",
       "      <td>0.992727</td>\n",
       "      <td>True</td>\n",
       "      <td>481.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>481.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>frame_000816.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>太好了</td>\n",
       "      <td>0.995613</td>\n",
       "      <td>True</td>\n",
       "      <td>480.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>frame_000840.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>怎麼濕成這樣</td>\n",
       "      <td>0.985218</td>\n",
       "      <td>True</td>\n",
       "      <td>448.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36816</th>\n",
       "      <td>frame_032952.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>李廆玉博</td>\n",
       "      <td>0.727673</td>\n",
       "      <td>True</td>\n",
       "      <td>459.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36817</th>\n",
       "      <td>frame_032964.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>李暠玉博</td>\n",
       "      <td>0.736070</td>\n",
       "      <td>True</td>\n",
       "      <td>459.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36818</th>\n",
       "      <td>frame_032976.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>李廆玉博</td>\n",
       "      <td>0.732361</td>\n",
       "      <td>True</td>\n",
       "      <td>459.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36819</th>\n",
       "      <td>frame_032988.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>李廆玉博</td>\n",
       "      <td>0.726971</td>\n",
       "      <td>True</td>\n",
       "      <td>459.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36820</th>\n",
       "      <td>frame_033000.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>李暠玉博</td>\n",
       "      <td>0.738637</td>\n",
       "      <td>True</td>\n",
       "      <td>459.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12570 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name         dir_name  speech     score  have_sub  \\\n",
       "63     frame_000756.jpg  assets\\frames\\1      小祥  0.999957      True   \n",
       "66     frame_000792.jpg  assets\\frames\\1     太好了  0.993245      True   \n",
       "67     frame_000804.jpg  assets\\frames\\1     太好了  0.992727      True   \n",
       "68     frame_000816.jpg  assets\\frames\\1     太好了  0.995613      True   \n",
       "70     frame_000840.jpg  assets\\frames\\1  怎麼濕成這樣  0.985218      True   \n",
       "...                 ...              ...     ...       ...       ...   \n",
       "36816  frame_032952.jpg  assets\\frames\\9    李廆玉博  0.727673      True   \n",
       "36817  frame_032964.jpg  assets\\frames\\9    李暠玉博  0.736070      True   \n",
       "36818  frame_032976.jpg  assets\\frames\\9    李廆玉博  0.732361      True   \n",
       "36819  frame_032988.jpg  assets\\frames\\9    李廆玉博  0.726971      True   \n",
       "36820  frame_033000.jpg  assets\\frames\\9    李暠玉博  0.738637      True   \n",
       "\n",
       "       left_top_x  left_top_y  right_top_x  right_top_y  left_bottom_x  \\\n",
       "63          598.0         5.0        679.0          0.0          601.0   \n",
       "66          481.0         6.0        596.0          6.0          481.0   \n",
       "67          481.0         6.0        596.0          6.0          481.0   \n",
       "68          480.0         6.0        596.0          6.0          480.0   \n",
       "70          448.0         9.0        677.0          5.0          448.0   \n",
       "...           ...         ...          ...          ...            ...   \n",
       "36816       459.0         4.0        556.0          4.0          459.0   \n",
       "36817       459.0         4.0        556.0          4.0          459.0   \n",
       "36818       459.0         4.0        556.0          4.0          459.0   \n",
       "36819       459.0         4.0        556.0          4.0          459.0   \n",
       "36820       459.0         4.0        556.0          4.0          459.0   \n",
       "\n",
       "       left_bottom_y  right_bottom_x  right_bottom_y  \n",
       "63              44.0           682.0            41.0  \n",
       "66              44.0           596.0            44.0  \n",
       "67              44.0           596.0            44.0  \n",
       "68              44.0           596.0            44.0  \n",
       "70              44.0           677.0            43.0  \n",
       "...              ...             ...             ...  \n",
       "36816           24.0           556.0            24.0  \n",
       "36817           24.0           556.0            24.0  \n",
       "36818           24.0           556.0            24.0  \n",
       "36819           24.0           556.0            24.0  \n",
       "36820           24.0           556.0            24.0  \n",
       "\n",
       "[12570 rows x 13 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "have_chat_data = merged_df[merged_df[\"have_sub\"]]\n",
    "have_chat_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = have_chat_data.groupby(['dir_name', 'image_name'])['speech'].nunique()\n",
    "multiple_speech = grouped[grouped > 1]\n",
    "count = multiple_speech.count()\n",
    "count\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2543"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = have_chat_data.groupby('speech').size()\n",
    "same_speech = grouped[grouped > 1]\n",
    "count = same_speech.count()\n",
    "count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vanish the same speech"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35070</th>\n",
       "      <td>frame_012000.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>!</td>\n",
       "      <td>0.588084</td>\n",
       "      <td>True</td>\n",
       "      <td>736.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>776.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>736.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>776.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1116</th>\n",
       "      <td>frame_013392.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>/雨</td>\n",
       "      <td>0.746893</td>\n",
       "      <td>True</td>\n",
       "      <td>424.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>577.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31799</th>\n",
       "      <td>frame_006732.jpg</td>\n",
       "      <td>assets\\frames\\8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.787030</td>\n",
       "      <td>True</td>\n",
       "      <td>643.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24509</th>\n",
       "      <td>frame_021420.jpg</td>\n",
       "      <td>assets\\frames\\5</td>\n",
       "      <td>000</td>\n",
       "      <td>0.722370</td>\n",
       "      <td>True</td>\n",
       "      <td>711.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>752.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>711.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>752.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15660</th>\n",
       "      <td>frame_017400.jpg</td>\n",
       "      <td>assets\\frames\\2</td>\n",
       "      <td>0B</td>\n",
       "      <td>0.571974</td>\n",
       "      <td>True</td>\n",
       "      <td>488.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>488.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29760</th>\n",
       "      <td>frame_016320.jpg</td>\n",
       "      <td>assets\\frames\\7</td>\n",
       "      <td>：</td>\n",
       "      <td>0.669332</td>\n",
       "      <td>True</td>\n",
       "      <td>759.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>788.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>788.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29223</th>\n",
       "      <td>frame_009876.jpg</td>\n",
       "      <td>assets\\frames\\7</td>\n",
       "      <td>：Ne</td>\n",
       "      <td>0.586199</td>\n",
       "      <td>True</td>\n",
       "      <td>900.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1115</th>\n",
       "      <td>frame_013380.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>：休育館</td>\n",
       "      <td>0.796444</td>\n",
       "      <td>True</td>\n",
       "      <td>534.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>537.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35887</th>\n",
       "      <td>frame_021804.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>］大家都不舒服呢</td>\n",
       "      <td>0.901069</td>\n",
       "      <td>True</td>\n",
       "      <td>657.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>658.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15118</th>\n",
       "      <td>frame_010896.jpg</td>\n",
       "      <td>assets\\frames\\2</td>\n",
       "      <td>￥600</td>\n",
       "      <td>0.916261</td>\n",
       "      <td>True</td>\n",
       "      <td>610.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>614.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>676.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4806 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name         dir_name    speech     score  have_sub  \\\n",
       "35070  frame_012000.jpg  assets\\frames\\9         !  0.588084      True   \n",
       "1116   frame_013392.jpg  assets\\frames\\1        /雨  0.746893      True   \n",
       "31799  frame_006732.jpg  assets\\frames\\8         0  0.787030      True   \n",
       "24509  frame_021420.jpg  assets\\frames\\5       000  0.722370      True   \n",
       "15660  frame_017400.jpg  assets\\frames\\2        0B  0.571974      True   \n",
       "...                 ...              ...       ...       ...       ...   \n",
       "29760  frame_016320.jpg  assets\\frames\\7         ：  0.669332      True   \n",
       "29223  frame_009876.jpg  assets\\frames\\7       ：Ne  0.586199      True   \n",
       "1115   frame_013380.jpg  assets\\frames\\1      ：休育館  0.796444      True   \n",
       "35887  frame_021804.jpg  assets\\frames\\9  ］大家都不舒服呢  0.901069      True   \n",
       "15118  frame_010896.jpg  assets\\frames\\2      ￥600  0.916261      True   \n",
       "\n",
       "       left_top_x  left_top_y  right_top_x  right_top_y  left_bottom_x  \\\n",
       "35070       736.0        15.0        776.0         15.0          736.0   \n",
       "1116        424.0        14.0        577.0         11.0          424.0   \n",
       "31799       643.0         8.0        697.0          8.0          643.0   \n",
       "24509       711.0        18.0        752.0         18.0          711.0   \n",
       "15660       488.0         8.0        561.0          8.0          488.0   \n",
       "...           ...         ...          ...          ...            ...   \n",
       "29760       759.0        20.0        788.0         20.0          759.0   \n",
       "29223       900.0        14.0       1012.0         14.0          900.0   \n",
       "1115        534.0        18.0        643.0          6.0          537.0   \n",
       "35887       657.0        11.0        944.0          8.0          658.0   \n",
       "15118       610.0         8.0        672.0          0.0          614.0   \n",
       "\n",
       "       left_bottom_y  right_bottom_x  right_bottom_y  \n",
       "35070           37.0           776.0            37.0  \n",
       "1116            44.0           578.0            41.0  \n",
       "31799           42.0           697.0            42.0  \n",
       "24509           34.0           752.0            34.0  \n",
       "15660           39.0           561.0            39.0  \n",
       "...              ...             ...             ...  \n",
       "29760           35.0           788.0            35.0  \n",
       "29223           39.0          1012.0            39.0  \n",
       "1115            44.0           646.0            37.0  \n",
       "35887           44.0           944.0            42.0  \n",
       "15118           39.0           676.0            30.0  \n",
       "\n",
       "[4806 rows x 13 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "have_chat_data_sorted = have_chat_data.sort_values(by=['speech', 'score'], ascending=[True, False])\n",
    "have_chat_data = have_chat_data_sorted.drop_duplicates(subset='speech', keep='first')\n",
    "have_chat_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29372</th>\n",
       "      <td>frame_011664.jpg</td>\n",
       "      <td>assets\\frames\\7</td>\n",
       "      <td>我知道</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>583.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>695.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>695.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12005</th>\n",
       "      <td>frame_007596.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>我想</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>444.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>444.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34232</th>\n",
       "      <td>frame_001944.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>是的</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>603.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>681.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>603.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>681.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34156</th>\n",
       "      <td>frame_001032.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>我的</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>580.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>580.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35048</th>\n",
       "      <td>frame_011736.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>不知道</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>583.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26902</th>\n",
       "      <td>frame_016080.jpg</td>\n",
       "      <td>assets\\frames\\6</td>\n",
       "      <td>鳳</td>\n",
       "      <td>0.502184</td>\n",
       "      <td>True</td>\n",
       "      <td>580.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>615.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>580.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>615.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18096</th>\n",
       "      <td>frame_012576.jpg</td>\n",
       "      <td>assets\\frames\\3</td>\n",
       "      <td>天為長</td>\n",
       "      <td>0.502062</td>\n",
       "      <td>True</td>\n",
       "      <td>604.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>819.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>604.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>819.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26412</th>\n",
       "      <td>frame_010200.jpg</td>\n",
       "      <td>assets\\frames\\6</td>\n",
       "      <td>GA</td>\n",
       "      <td>0.501459</td>\n",
       "      <td>True</td>\n",
       "      <td>507.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>529.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>507.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>529.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21287</th>\n",
       "      <td>frame_016812.jpg</td>\n",
       "      <td>assets\\frames\\4</td>\n",
       "      <td>傻訴年步</td>\n",
       "      <td>0.500909</td>\n",
       "      <td>True</td>\n",
       "      <td>360.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>360.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1499</th>\n",
       "      <td>frame_017988.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>壓</td>\n",
       "      <td>0.500671</td>\n",
       "      <td>True</td>\n",
       "      <td>650.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>712.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>655.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4806 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name          dir_name speech     score  have_sub  \\\n",
       "29372  frame_011664.jpg   assets\\frames\\7    我知道  1.000000      True   \n",
       "12005  frame_007596.jpg  assets\\frames\\13     我想  1.000000      True   \n",
       "34232  frame_001944.jpg   assets\\frames\\9     是的  1.000000      True   \n",
       "34156  frame_001032.jpg   assets\\frames\\9     我的  1.000000      True   \n",
       "35048  frame_011736.jpg   assets\\frames\\9    不知道  1.000000      True   \n",
       "...                 ...               ...    ...       ...       ...   \n",
       "26902  frame_016080.jpg   assets\\frames\\6      鳳  0.502184      True   \n",
       "18096  frame_012576.jpg   assets\\frames\\3    天為長  0.502062      True   \n",
       "26412  frame_010200.jpg   assets\\frames\\6     GA  0.501459      True   \n",
       "21287  frame_016812.jpg   assets\\frames\\4   傻訴年步  0.500909      True   \n",
       "1499   frame_017988.jpg   assets\\frames\\1      壓  0.500671      True   \n",
       "\n",
       "       left_top_x  left_top_y  right_top_x  right_top_y  left_bottom_x  \\\n",
       "29372       583.0         8.0        695.0          8.0          583.0   \n",
       "12005       444.0         4.0        527.0          4.0          444.0   \n",
       "34232       603.0         3.0        681.0          3.0          603.0   \n",
       "34156       580.0         3.0        660.0          3.0          580.0   \n",
       "35048       583.0         8.0        696.0          8.0          583.0   \n",
       "...           ...         ...          ...          ...            ...   \n",
       "26902       580.0        10.0        615.0         10.0          580.0   \n",
       "18096       604.0        14.0        819.0         14.0          604.0   \n",
       "26412       507.0         7.0        529.0          7.0          507.0   \n",
       "21287       360.0        13.0        468.0         13.0          360.0   \n",
       "1499        650.0        13.0        712.0          2.0          655.0   \n",
       "\n",
       "       left_bottom_y  right_bottom_x  right_bottom_y  \n",
       "29372           44.0           695.0            44.0  \n",
       "12005           44.0           527.0            44.0  \n",
       "34232           44.0           681.0            44.0  \n",
       "34156           44.0           660.0            44.0  \n",
       "35048           44.0           696.0            44.0  \n",
       "...              ...             ...             ...  \n",
       "26902           38.0           615.0            38.0  \n",
       "18096           38.0           819.0            38.0  \n",
       "26412           18.0           529.0            18.0  \n",
       "21287           38.0           468.0            38.0  \n",
       "1499            44.0           717.0            32.0  \n",
       "\n",
       "[4806 rows x 13 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "have_chat_data.sort_values(by='score', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "265"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "low_score_count = have_chat_data[have_chat_data['score'] < 0.6].shape[0]\n",
    "low_score_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1116</th>\n",
       "      <td>frame_013392.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>/雨</td>\n",
       "      <td>0.746893</td>\n",
       "      <td>True</td>\n",
       "      <td>424.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>577.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31799</th>\n",
       "      <td>frame_006732.jpg</td>\n",
       "      <td>assets\\frames\\8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.787030</td>\n",
       "      <td>True</td>\n",
       "      <td>643.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24509</th>\n",
       "      <td>frame_021420.jpg</td>\n",
       "      <td>assets\\frames\\5</td>\n",
       "      <td>000</td>\n",
       "      <td>0.722370</td>\n",
       "      <td>True</td>\n",
       "      <td>711.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>752.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>711.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>752.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1366</th>\n",
       "      <td>frame_016392.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>0U做RNG</td>\n",
       "      <td>0.804928</td>\n",
       "      <td>True</td>\n",
       "      <td>556.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>724.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>724.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27439</th>\n",
       "      <td>frame_022524.jpg</td>\n",
       "      <td>assets\\frames\\6</td>\n",
       "      <td>0呵</td>\n",
       "      <td>0.623214</td>\n",
       "      <td>True</td>\n",
       "      <td>774.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>825.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>778.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>829.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23342</th>\n",
       "      <td>frame_007416.jpg</td>\n",
       "      <td>assets\\frames\\5</td>\n",
       "      <td>（春日影》變了啊</td>\n",
       "      <td>0.911441</td>\n",
       "      <td>True</td>\n",
       "      <td>505.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>793.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>506.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>794.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29760</th>\n",
       "      <td>frame_016320.jpg</td>\n",
       "      <td>assets\\frames\\7</td>\n",
       "      <td>：</td>\n",
       "      <td>0.669332</td>\n",
       "      <td>True</td>\n",
       "      <td>759.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>788.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>788.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1115</th>\n",
       "      <td>frame_013380.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>：休育館</td>\n",
       "      <td>0.796444</td>\n",
       "      <td>True</td>\n",
       "      <td>534.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>537.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35887</th>\n",
       "      <td>frame_021804.jpg</td>\n",
       "      <td>assets\\frames\\9</td>\n",
       "      <td>］大家都不舒服呢</td>\n",
       "      <td>0.901069</td>\n",
       "      <td>True</td>\n",
       "      <td>657.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>658.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15118</th>\n",
       "      <td>frame_010896.jpg</td>\n",
       "      <td>assets\\frames\\2</td>\n",
       "      <td>￥600</td>\n",
       "      <td>0.916261</td>\n",
       "      <td>True</td>\n",
       "      <td>610.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>614.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>676.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4541 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name         dir_name    speech     score  have_sub  \\\n",
       "1116   frame_013392.jpg  assets\\frames\\1        /雨  0.746893      True   \n",
       "31799  frame_006732.jpg  assets\\frames\\8         0  0.787030      True   \n",
       "24509  frame_021420.jpg  assets\\frames\\5       000  0.722370      True   \n",
       "1366   frame_016392.jpg  assets\\frames\\1    0U做RNG  0.804928      True   \n",
       "27439  frame_022524.jpg  assets\\frames\\6        0呵  0.623214      True   \n",
       "...                 ...              ...       ...       ...       ...   \n",
       "23342  frame_007416.jpg  assets\\frames\\5  （春日影》變了啊  0.911441      True   \n",
       "29760  frame_016320.jpg  assets\\frames\\7         ：  0.669332      True   \n",
       "1115   frame_013380.jpg  assets\\frames\\1      ：休育館  0.796444      True   \n",
       "35887  frame_021804.jpg  assets\\frames\\9  ］大家都不舒服呢  0.901069      True   \n",
       "15118  frame_010896.jpg  assets\\frames\\2      ￥600  0.916261      True   \n",
       "\n",
       "       left_top_x  left_top_y  right_top_x  right_top_y  left_bottom_x  \\\n",
       "1116        424.0        14.0        577.0         11.0          424.0   \n",
       "31799       643.0         8.0        697.0          8.0          643.0   \n",
       "24509       711.0        18.0        752.0         18.0          711.0   \n",
       "1366        556.0         3.0        724.0          3.0          556.0   \n",
       "27439       774.0        14.0        825.0          6.0          778.0   \n",
       "...           ...         ...          ...          ...            ...   \n",
       "23342       505.0        10.0        793.0          5.0          506.0   \n",
       "29760       759.0        20.0        788.0         20.0          759.0   \n",
       "1115        534.0        18.0        643.0          6.0          537.0   \n",
       "35887       657.0        11.0        944.0          8.0          658.0   \n",
       "15118       610.0         8.0        672.0          0.0          614.0   \n",
       "\n",
       "       left_bottom_y  right_bottom_x  right_bottom_y  \n",
       "1116            44.0           578.0            41.0  \n",
       "31799           42.0           697.0            42.0  \n",
       "24509           34.0           752.0            34.0  \n",
       "1366            42.0           724.0            42.0  \n",
       "27439           42.0           829.0            34.0  \n",
       "...              ...             ...             ...  \n",
       "23342           44.0           794.0            43.0  \n",
       "29760           35.0           788.0            35.0  \n",
       "1115            44.0           646.0            37.0  \n",
       "35887           44.0           944.0            42.0  \n",
       "15118           39.0           676.0            30.0  \n",
       "\n",
       "[4541 rows x 13 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "have_chat_data = have_chat_data[have_chat_data['score'] > 0.6]\n",
    "have_chat_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\AORUS\\AppData\\Local\\Temp\\ipykernel_29432\\233217484.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  have_chat_data['frame_number'] = have_chat_data['image_name'].apply(lambda x: int(x.split('_')[1].split('.')[0]))\n",
      "C:\\Users\\AORUS\\AppData\\Local\\Temp\\ipykernel_29432\\233217484.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  have_chat_data['dir_number'] = have_chat_data['dir_name'].apply(lambda x: int(x.split('\\\\')[-1]))\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>image_name</th>\n",
       "      <th>dir_name</th>\n",
       "      <th>speech</th>\n",
       "      <th>score</th>\n",
       "      <th>have_sub</th>\n",
       "      <th>left_top_x</th>\n",
       "      <th>left_top_y</th>\n",
       "      <th>right_top_x</th>\n",
       "      <th>right_top_y</th>\n",
       "      <th>left_bottom_x</th>\n",
       "      <th>left_bottom_y</th>\n",
       "      <th>right_bottom_x</th>\n",
       "      <th>right_bottom_y</th>\n",
       "      <th>frame_number</th>\n",
       "      <th>dir_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>frame_000816.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>太好了</td>\n",
       "      <td>0.995613</td>\n",
       "      <td>True</td>\n",
       "      <td>480.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>816</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>frame_000840.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>怎麼濕成這樣</td>\n",
       "      <td>0.985218</td>\n",
       "      <td>True</td>\n",
       "      <td>448.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>840</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>frame_000912.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>你最近不來學校</td>\n",
       "      <td>0.995313</td>\n",
       "      <td>True</td>\n",
       "      <td>392.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>658.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>912</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>frame_000948.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>奶最近不來學校</td>\n",
       "      <td>0.953339</td>\n",
       "      <td>True</td>\n",
       "      <td>390.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>389.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>948</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>frame_001020.jpg</td>\n",
       "      <td>assets\\frames\\1</td>\n",
       "      <td>今天我來是有話要說</td>\n",
       "      <td>0.976497</td>\n",
       "      <td>True</td>\n",
       "      <td>472.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>809.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>472.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>809.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>1020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14172</th>\n",
       "      <td>frame_033600.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>校下山佳</td>\n",
       "      <td>0.818678</td>\n",
       "      <td>True</td>\n",
       "      <td>940.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1062.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1063.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>33600</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14174</th>\n",
       "      <td>frame_033624.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>拉下由佳</td>\n",
       "      <td>0.721245</td>\n",
       "      <td>True</td>\n",
       "      <td>941.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1061.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>942.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1061.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>33624</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14178</th>\n",
       "      <td>frame_033672.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>崧下由佳</td>\n",
       "      <td>0.791757</td>\n",
       "      <td>True</td>\n",
       "      <td>941.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1061.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>942.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1061.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>33672</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14200</th>\n",
       "      <td>frame_033936.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>我麵</td>\n",
       "      <td>0.600693</td>\n",
       "      <td>True</td>\n",
       "      <td>575.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>33936</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14205</th>\n",
       "      <td>frame_033996.jpg</td>\n",
       "      <td>assets\\frames\\13</td>\n",
       "      <td>混蛋老多</td>\n",
       "      <td>0.770650</td>\n",
       "      <td>True</td>\n",
       "      <td>571.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>704.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>570.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>704.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>33996</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4541 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             image_name          dir_name     speech     score  have_sub  \\\n",
       "68     frame_000816.jpg   assets\\frames\\1        太好了  0.995613      True   \n",
       "70     frame_000840.jpg   assets\\frames\\1     怎麼濕成這樣  0.985218      True   \n",
       "76     frame_000912.jpg   assets\\frames\\1    你最近不來學校  0.995313      True   \n",
       "79     frame_000948.jpg   assets\\frames\\1    奶最近不來學校  0.953339      True   \n",
       "85     frame_001020.jpg   assets\\frames\\1  今天我來是有話要說  0.976497      True   \n",
       "...                 ...               ...        ...       ...       ...   \n",
       "14172  frame_033600.jpg  assets\\frames\\13       校下山佳  0.818678      True   \n",
       "14174  frame_033624.jpg  assets\\frames\\13       拉下由佳  0.721245      True   \n",
       "14178  frame_033672.jpg  assets\\frames\\13       崧下由佳  0.791757      True   \n",
       "14200  frame_033936.jpg  assets\\frames\\13         我麵  0.600693      True   \n",
       "14205  frame_033996.jpg  assets\\frames\\13       混蛋老多  0.770650      True   \n",
       "\n",
       "       left_top_x  left_top_y  right_top_x  right_top_y  left_bottom_x  \\\n",
       "68          480.0         6.0        596.0          6.0          480.0   \n",
       "70          448.0         9.0        677.0          5.0          448.0   \n",
       "76          392.0         7.0        659.0         10.0          392.0   \n",
       "79          390.0         5.0        660.0          9.0          389.0   \n",
       "85          472.0         7.0        809.0          3.0          472.0   \n",
       "...           ...         ...          ...          ...            ...   \n",
       "14172       940.0         2.0       1062.0          0.0          940.0   \n",
       "14174       941.0         3.0       1061.0          1.0          942.0   \n",
       "14178       941.0         3.0       1061.0          1.0          942.0   \n",
       "14200       575.0        20.0        673.0         20.0          575.0   \n",
       "14205       571.0        15.0        704.0         18.0          570.0   \n",
       "\n",
       "       left_bottom_y  right_bottom_x  right_bottom_y  frame_number  dir_number  \n",
       "68              44.0           596.0            44.0           816           1  \n",
       "70              44.0           677.0            43.0           840           1  \n",
       "76              41.0           658.0            44.0           912           1  \n",
       "79              43.0           660.0            44.0           948           1  \n",
       "85              44.0           809.0            42.0          1020           1  \n",
       "...              ...             ...             ...           ...         ...  \n",
       "14172           31.0          1063.0            28.0         33600          13  \n",
       "14174           28.0          1061.0            27.0         33624          13  \n",
       "14178           28.0          1061.0            27.0         33672          13  \n",
       "14200           44.0           673.0            44.0         33936          13  \n",
       "14205           44.0           704.0            44.0         33996          13  \n",
       "\n",
       "[4541 rows x 15 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "have_chat_data['frame_number'] = have_chat_data['image_name'].apply(lambda x: int(x.split('_')[1].split('.')[0]))\n",
    "have_chat_data['dir_number'] = have_chat_data['dir_name'].apply(lambda x: int(x.split('\\\\')[-1]))\n",
    "\n",
    "sorted_df = have_chat_data.sort_values(by=['dir_number', 'frame_number'])\n",
    "\n",
    "sorted_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_df.to_csv(r\"assets/speech/cleaned_data.csv\", encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ASS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>speech</th>\n",
       "      <th>start_frame</th>\n",
       "      <th>end_frame</th>\n",
       "      <th>eps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>祥ちゃん</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>小祥</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>良かった　来てくれて</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>太好了 妳終於來了</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34650</td>\n",
       "      <td>36610</td>\n",
       "      <td>びしょぬれじゃない　大丈夫</td>\n",
       "      <td>830</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285425</th>\n",
       "      <td>1397480</td>\n",
       "      <td>1405140</td>\n",
       "      <td>か</td>\n",
       "      <td>33506</td>\n",
       "      <td>33689</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285426</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285427</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285428</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285429</th>\n",
       "      <td>1411100</td>\n",
       "      <td>1420070</td>\n",
       "      <td>一直迷茫</td>\n",
       "      <td>33832</td>\n",
       "      <td>34047</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>285430 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        start_time  end_time         speech  start_frame  end_frame eps\n",
       "0            31230     32000           祥ちゃん          748        767   1\n",
       "1            31230     32000             小祥          748        767   1\n",
       "2            32560     34250     良かった　来てくれて          780        821   1\n",
       "3            32560     34250      太好了 妳終於來了          780        821   1\n",
       "4            34650     36610  びしょぬれじゃない　大丈夫          830        877   1\n",
       "...            ...       ...            ...          ...        ...  ..\n",
       "285425     1397480   1405140              か        33506      33689   9\n",
       "285426     1397530   1405210              ら        33507      33691   9\n",
       "285427     1397530   1405210              ら        33507      33691   9\n",
       "285428     1397530   1405210              ら        33507      33691   9\n",
       "285429     1411100   1420070           一直迷茫        33832      34047   9\n",
       "\n",
       "[285430 rows x 6 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# Define the directory containing the CSV files\n",
    "csv_dir = r'assets/subtitle_result/csv'\n",
    "\n",
    "# List all CSV files in the directory\n",
    "csv_files = [os.path.join(csv_dir, f) for f in os.listdir(csv_dir) if f.endswith('.csv')]\n",
    "\n",
    "# Read and merge all CSV files\n",
    "dataframes = []\n",
    "\n",
    "for file in csv_files:\n",
    "    df = pd.read_csv(file)\n",
    "    # Add a new column to track the source file\n",
    "    df['eps'] = os.path.basename(file).split('.')[0]\n",
    "    dataframes.append(df)\n",
    "\n",
    "# Concatenate all DataFrames\n",
    "merged_df = pd.concat(dataframes, ignore_index=True)\n",
    "\n",
    "# Display the merged DataFrame\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>speech</th>\n",
       "      <th>start_frame</th>\n",
       "      <th>end_frame</th>\n",
       "      <th>eps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>祥ちゃん</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>小祥</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>良かった　来てくれて</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>太好了 妳終於來了</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34650</td>\n",
       "      <td>36610</td>\n",
       "      <td>びしょぬれじゃない　大丈夫</td>\n",
       "      <td>830</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285425</th>\n",
       "      <td>1397480</td>\n",
       "      <td>1405140</td>\n",
       "      <td>か</td>\n",
       "      <td>33506</td>\n",
       "      <td>33689</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285426</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285427</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285428</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285429</th>\n",
       "      <td>1411100</td>\n",
       "      <td>1420070</td>\n",
       "      <td>一直迷茫</td>\n",
       "      <td>33832</td>\n",
       "      <td>34047</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273748 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        start_time  end_time         speech  start_frame  end_frame eps\n",
       "0            31230     32000           祥ちゃん          748        767   1\n",
       "1            31230     32000             小祥          748        767   1\n",
       "2            32560     34250     良かった　来てくれて          780        821   1\n",
       "3            32560     34250      太好了 妳終於來了          780        821   1\n",
       "4            34650     36610  びしょぬれじゃない　大丈夫          830        877   1\n",
       "...            ...       ...            ...          ...        ...  ..\n",
       "285425     1397480   1405140              か        33506      33689   9\n",
       "285426     1397530   1405210              ら        33507      33691   9\n",
       "285427     1397530   1405210              ら        33507      33691   9\n",
       "285428     1397530   1405210              ら        33507      33691   9\n",
       "285429     1411100   1420070           一直迷茫        33832      34047   9\n",
       "\n",
       "[273748 rows x 6 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delete_speech = \"m 20 0 b 10 0 0 10 0 20 b 0 31 10 40 20 40 b 31 40 40 31 40 20 b 40 10 31 0 20 0\"\n",
    "\n",
    "df_cleaned = merged_df[merged_df['speech'] != delete_speech]\n",
    "df_cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\AORUS\\AppData\\Local\\Temp\\ipykernel_12604\\2023511776.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_cleaned['language'] = df_cleaned['speech'].apply(detect_language)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>speech</th>\n",
       "      <th>start_frame</th>\n",
       "      <th>end_frame</th>\n",
       "      <th>eps</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>祥ちゃん</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>小祥</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>良かった　来てくれて</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>太好了 妳終於來了</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34650</td>\n",
       "      <td>36610</td>\n",
       "      <td>びしょぬれじゃない　大丈夫</td>\n",
       "      <td>830</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285425</th>\n",
       "      <td>1397480</td>\n",
       "      <td>1405140</td>\n",
       "      <td>か</td>\n",
       "      <td>33506</td>\n",
       "      <td>33689</td>\n",
       "      <td>9</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285426</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285427</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285428</th>\n",
       "      <td>1397530</td>\n",
       "      <td>1405210</td>\n",
       "      <td>ら</td>\n",
       "      <td>33507</td>\n",
       "      <td>33691</td>\n",
       "      <td>9</td>\n",
       "      <td>Japanese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285429</th>\n",
       "      <td>1411100</td>\n",
       "      <td>1420070</td>\n",
       "      <td>一直迷茫</td>\n",
       "      <td>33832</td>\n",
       "      <td>34047</td>\n",
       "      <td>9</td>\n",
       "      <td>Chinese</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273748 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        start_time  end_time         speech  start_frame  end_frame eps  \\\n",
       "0            31230     32000           祥ちゃん          748        767   1   \n",
       "1            31230     32000             小祥          748        767   1   \n",
       "2            32560     34250     良かった　来てくれて          780        821   1   \n",
       "3            32560     34250      太好了 妳終於來了          780        821   1   \n",
       "4            34650     36610  びしょぬれじゃない　大丈夫          830        877   1   \n",
       "...            ...       ...            ...          ...        ...  ..   \n",
       "285425     1397480   1405140              か        33506      33689   9   \n",
       "285426     1397530   1405210              ら        33507      33691   9   \n",
       "285427     1397530   1405210              ら        33507      33691   9   \n",
       "285428     1397530   1405210              ら        33507      33691   9   \n",
       "285429     1411100   1420070           一直迷茫        33832      34047   9   \n",
       "\n",
       "        language  \n",
       "0       Japanese  \n",
       "1        Unknown  \n",
       "2       Japanese  \n",
       "3        Unknown  \n",
       "4       Japanese  \n",
       "...          ...  \n",
       "285425  Japanese  \n",
       "285426  Japanese  \n",
       "285427  Japanese  \n",
       "285428  Japanese  \n",
       "285429   Chinese  \n",
       "\n",
       "[273748 rows x 7 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from langdetect import detect, DetectorFactory\n",
    "\n",
    "# Set the seed to ensure consistent results\n",
    "DetectorFactory.seed = 0\n",
    "\n",
    "# Define regular expressions to detect Chinese, Japanese, and English text\n",
    "def detect_language(text: str) -> str:\n",
    "    text = str(text)\n",
    "    try:\n",
    "        # Use langdetect to detect the language\n",
    "        language = detect(text)\n",
    "        \n",
    "        # Map the detected language to your custom categories\n",
    "        if language == 'zh-cn' or language == 'zh-tw':\n",
    "            return \"Chinese\"\n",
    "        elif language == 'ja':\n",
    "            return \"Japanese\"\n",
    "        elif language == 'en':\n",
    "            return \"English\"\n",
    "        else:\n",
    "            return \"Unknown\"\n",
    "    except Exception as e:\n",
    "        # If an error occurs, return Unknown\n",
    "        return \"Unknown\"\n",
    "\n",
    "# Example: Applying this to your DataFrame\n",
    "df_cleaned['language'] = df_cleaned['speech'].apply(detect_language)\n",
    "\n",
    "df_cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_chinese = df_cleaned[df_cleaned['language'].isin(['Chinese', 'English', 'Unknown'])]\n",
    "df_japanese = df_cleaned[df_cleaned['language'].isin(['Japanese', 'English', 'Unknown'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 创建 DataFrame\n",
    "df = df_japanese\n",
    "result = []\n",
    "temp_group = []  # 用于暂时存储相同 speech 的连续行\n",
    "\n",
    "# 遍历 DataFrame\n",
    "for i, row in df.iterrows():\n",
    "    if not temp_group:\n",
    "        temp_group.append(row)\n",
    "    elif row[\"speech\"] == temp_group[-1][\"speech\"]:  # 如果 speech 相同，继续合并\n",
    "        temp_group.append(row)\n",
    "    else:  # 如果 speech 不同，处理当前 group\n",
    "        # 获取 min start_time 和 max end_time，合并成一行\n",
    "        merged_row = {\n",
    "            \"speech\": temp_group[0][\"speech\"],\n",
    "            \"start_time\": min([r[\"start_time\"] for r in temp_group]),\n",
    "            \"end_time\": max([r[\"end_time\"] for r in temp_group]),\n",
    "            \"start_frame\": min([r[\"start_frame\"] for r in temp_group]),  # 处理 start_frame\n",
    "            \"end_frame\": max([r[\"end_frame\"] for r in temp_group]),  # 处理 end_frame\n",
    "            \"eps\": int(temp_group[0][\"eps\"])\n",
    "        }\n",
    "        result.append(merged_row)\n",
    "        temp_group = [row]  # 重置为当前行继续合并\n",
    "\n",
    "# 处理最后一个 group\n",
    "if temp_group:\n",
    "    merged_row = {\n",
    "        \"speech\": temp_group[0][\"speech\"],\n",
    "        \"start_time\": min([r[\"start_time\"] for r in temp_group]),\n",
    "        \"end_time\": max([r[\"end_time\"] for r in temp_group]),\n",
    "        \"start_frame\": min([r[\"start_frame\"] for r in temp_group]),\n",
    "        \"end_frame\": max([r[\"end_frame\"] for r in temp_group]),\n",
    "        \"eps\": int(temp_group[0][\"eps\"])\n",
    "    }\n",
    "    result.append(merged_row)\n",
    "\n",
    "# 创建 DataFrame 保存最终结果\n",
    "merged_df = pd.DataFrame(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = merged_df.sort_values(by=['eps','start_time'], ascending=[True,True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>speech</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>start_frame</th>\n",
       "      <th>end_frame</th>\n",
       "      <th>eps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>祥ちゃん</td>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小祥</td>\n",
       "      <td>31230</td>\n",
       "      <td>32000</td>\n",
       "      <td>748</td>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>良かった　来てくれて</td>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>太好了 妳終於來了</td>\n",
       "      <td>32560</td>\n",
       "      <td>34250</td>\n",
       "      <td>780</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>びしょぬれじゃない　大丈夫</td>\n",
       "      <td>34650</td>\n",
       "      <td>36610</td>\n",
       "      <td>830</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7949</th>\n",
       "      <td>本字幕由喵萌Production製作  僅供交流試看之用  請勿用於商業用途\\N翻譯：Ron...</td>\n",
       "      <td>1410100</td>\n",
       "      <td>1413100</td>\n",
       "      <td>33808</td>\n",
       "      <td>33880</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7950</th>\n",
       "      <td>ただいま</td>\n",
       "      <td>1415100</td>\n",
       "      <td>1415970</td>\n",
       "      <td>33928</td>\n",
       "      <td>33949</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7951</th>\n",
       "      <td>我回來了</td>\n",
       "      <td>1415100</td>\n",
       "      <td>1415970</td>\n",
       "      <td>33928</td>\n",
       "      <td>33949</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7952</th>\n",
       "      <td>くそ親父</td>\n",
       "      <td>1417250</td>\n",
       "      <td>1418310</td>\n",
       "      <td>33980</td>\n",
       "      <td>34005</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7953</th>\n",
       "      <td>混蛋老爸</td>\n",
       "      <td>1417250</td>\n",
       "      <td>1418310</td>\n",
       "      <td>33980</td>\n",
       "      <td>34005</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14084 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 speech  start_time  end_time  \\\n",
       "0                                                  祥ちゃん       31230     32000   \n",
       "1                                                    小祥       31230     32000   \n",
       "2                                            良かった　来てくれて       32560     34250   \n",
       "3                                             太好了 妳終於來了       32560     34250   \n",
       "4                                         びしょぬれじゃない　大丈夫       34650     36610   \n",
       "...                                                 ...         ...       ...   \n",
       "7949  本字幕由喵萌Production製作  僅供交流試看之用  請勿用於商業用途\\N翻譯：Ron...     1410100   1413100   \n",
       "7950                                               ただいま     1415100   1415970   \n",
       "7951                                               我回來了     1415100   1415970   \n",
       "7952                                               くそ親父     1417250   1418310   \n",
       "7953                                               混蛋老爸     1417250   1418310   \n",
       "\n",
       "      start_frame  end_frame  eps  \n",
       "0             748        767    1  \n",
       "1             748        767    1  \n",
       "2             780        821    1  \n",
       "3             780        821    1  \n",
       "4             830        877    1  \n",
       "...           ...        ...  ...  \n",
       "7949        33808      33880   13  \n",
       "7950        33928      33949   13  \n",
       "7951        33928      33949   13  \n",
       "7952        33980      34005   13  \n",
       "7953        33980      34005   13  \n",
       "\n",
       "[14084 rows x 6 columns]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = merged_df[merged_df['speech'].str.len() > 1]\n",
    "\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_average_frame(row):\n",
    "    # Get the average of start and end frame\n",
    "    avg_frame = int((row['start_frame'] + row['end_frame']) / 2)\n",
    "    avg_frame = (avg_frame // 12) * 12 + 12\n",
    "    # Make the result a multiple of 12\n",
    "    return f\"frame_{avg_frame:06d}.jpg\"# Rounds up to the nearest multiple of 12\n",
    "\n",
    "def get_start_frame(frame):\n",
    "    frame_path = (frame // 12) * 12 + 12\n",
    "    return f\"frame_{frame_path:06d}.jpg\"\n",
    "\n",
    "def get_end_frame(frame):\n",
    "    frame_path = (frame // 12) * 12\n",
    "    return f\"frame_{frame_path:06d}.jpg\"\n",
    "\n",
    "# Apply the adjustment to the 'start_frame' and 'end_frame' columns\n",
    "merged_df['get_frame'] = merged_df.apply(get_average_frame, axis=1)\n",
    "merged_df['start_frame'] = merged_df['start_frame'].apply(get_start_frame)\n",
    "merged_df['end_frame'] = merged_df['end_frame'].apply(get_end_frame)\n",
    "\n",
    "# Save the result to an Excel file\n",
    "merged_df.to_excel(r\"assets\\speech\\ass_result_jp.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mygo",
   "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.12.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}