google_2022/03_g2022_post_process.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63550dfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "10d0f6cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "issue = pd.read_csv(\"data/google_22_output_multi.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "76608abb",
   "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>text</th>\n",
       "      <th>id</th>\n",
       "      <th>ISSUE10</th>\n",
       "      <th>ISSUE11</th>\n",
       "      <th>ISSUE12</th>\n",
       "      <th>ISSUE13</th>\n",
       "      <th>ISSUE14</th>\n",
       "      <th>ISSUE15</th>\n",
       "      <th>ISSUE16</th>\n",
       "      <th>ISSUE17</th>\n",
       "      <th>...</th>\n",
       "      <th>ISSUE106</th>\n",
       "      <th>ISSUE111</th>\n",
       "      <th>ISSUE118</th>\n",
       "      <th>ISSUE200</th>\n",
       "      <th>ISSUE208</th>\n",
       "      <th>ISSUE210</th>\n",
       "      <th>ISSUE212</th>\n",
       "      <th>ISSUE218</th>\n",
       "      <th>ISSUE221</th>\n",
       "      <th>issue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Demand, I will say for computer coding...</td>\n",
       "      <td>CR18032445206145531905__google_asr_text</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>ISSUE14,ISSUE16,ISSUE18,ISSUE50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Good afternoon.  Good afternoon. We're ...</td>\n",
       "      <td>CR02245745215467945985__google_asr_text</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>ISSUE10,ISSUE12,ISSUE32,ISSUE95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 68 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                text  \\\n",
       "0          Demand, I will say for computer coding...   \n",
       "1         Good afternoon.  Good afternoon. We're ...   \n",
       "\n",
       "                                        id  ISSUE10  ISSUE11  ISSUE12  \\\n",
       "0  CR18032445206145531905__google_asr_text        0        0        0   \n",
       "1  CR02245745215467945985__google_asr_text        1        0        1   \n",
       "\n",
       "   ISSUE13  ISSUE14  ISSUE15  ISSUE16  ISSUE17  ...  ISSUE106  ISSUE111  \\\n",
       "0        0        1        0        1        0  ...         0         0   \n",
       "1        0        0        0        0        0  ...         0         0   \n",
       "\n",
       "   ISSUE118  ISSUE200  ISSUE208  ISSUE210  ISSUE212  ISSUE218  ISSUE221  \\\n",
       "0         0         0         0         0         0         0         0   \n",
       "1         0         0         0         0         0         0         0   \n",
       "\n",
       "                             issue  \n",
       "0  ISSUE14,ISSUE16,ISSUE18,ISSUE50  \n",
       "1  ISSUE10,ISSUE12,ISSUE32,ISSUE95  \n",
       "\n",
       "[2 rows x 68 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issue.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "26107dc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to get column names where value is 1 for the last 65 columns\n",
    "def get_active_features(row):\n",
    "    active_features = issue.columns[-65:][row.iloc[-65:] == 1]\n",
    "    return ','.join(active_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e9818229",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the function to each row\n",
    "issue['issue'] = issue.apply(get_active_features, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5c931e25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split ids\n",
    "issue['id'] = issue['id'].str.split('|')\n",
    "# \"Un-deduplicate\", or \"Re-hydrate\", in WMP lingo\n",
    "issue = issue.explode('id')\n",
    "# Split into ad id and field\n",
    "issue_ids = issue['id'].str.split('__', expand = True)\n",
    "issue_ids.columns = ['ad_id', 'field']\n",
    "issue = pd.concat([issue, issue_ids], axis = 1)\n",
    "issue = issue.drop(labels = ['id'], axis = 1)\n",
    "issue = issue[['ad_id', 'field', 'issue']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8fd55800",
   "metadata": {},
   "outputs": [],
   "source": [
    "issue.rename(columns={'field': 'issue_field', 'issue': 'issue_class'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "446ba0ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace various representations of missing values with NaN\n",
    "missing_values = ['', None, 'NA', 'missing']\n",
    "issue['issue_class'] = issue['issue_class'].replace(missing_values, np.nan)\n",
    "issue = issue.dropna(subset=['issue_class'])\n",
    "issue['issue_field'] = issue.issue_field.str.rstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c53f8e79",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_issue = issue.groupby('ad_id').agg({'issue_field': lambda x: '|'.join(x),\n",
    "                                       'issue_class': lambda x: '|'.join(x)}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ae7b4024",
   "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>ad_id</th>\n",
       "      <th>issue_field</th>\n",
       "      <th>issue_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CR00004220544102694913</td>\n",
       "      <td>ad_text</td>\n",
       "      <td>ISSUE43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CR00006144689451302913</td>\n",
       "      <td>aws_ocr_img_text</td>\n",
       "      <td>ISSUE18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     ad_id       issue_field issue_class\n",
       "0   CR00004220544102694913           ad_text     ISSUE43\n",
       "1   CR00006144689451302913  aws_ocr_img_text     ISSUE18"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_issue.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b18fdb1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_issue.to_csv(\"data/g2022_output_processed.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1193ca5a-093a-4752-a23f-d7d893ba9d86",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}