fb_2022/03_fb2022_post_process.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3d26599d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9d20119d",
   "metadata": {},
   "outputs": [],
   "source": [
    "issue = pd.read_csv(\"data/fb_22_output_multi.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6ee7d6e4",
   "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>ISSUE105</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>'</td>\n",
       "      <td>x_4971277182972491__ad_creative_link_descripti...</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>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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>' YOUR SOURCE FOR @GANDERNEWSROOM And I very m...</td>\n",
       "      <td>x_605605801303782__aws_ocr_text_vid</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>...</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>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                text  \\\n",
       "0                                                  '   \n",
       "1  ' YOUR SOURCE FOR @GANDERNEWSROOM And I very m...   \n",
       "\n",
       "                                                  id  ISSUE10  ISSUE11  \\\n",
       "0  x_4971277182972491__ad_creative_link_descripti...        1        0   \n",
       "1                x_605605801303782__aws_ocr_text_vid        0        0   \n",
       "\n",
       "   ISSUE12  ISSUE13  ISSUE14  ISSUE15  ISSUE16  ISSUE17  ...  ISSUE105  \\\n",
       "0        0        0        0        0        0        0  ...         0   \n",
       "1        0        0        0        0        0        0  ...         0   \n",
       "\n",
       "   ISSUE106  ISSUE111  ISSUE118  ISSUE200  ISSUE208  ISSUE210  ISSUE212  \\\n",
       "0         0         0         0         0         0         0         0   \n",
       "1         0         0         0         0         0         0         0   \n",
       "\n",
       "   ISSUE218  ISSUE221  \n",
       "0         0         0  \n",
       "1         0         0  \n",
       "\n",
       "[2 rows x 67 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issue.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "df1bdba9",
   "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": "72236351",
   "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": "effa95b8",
   "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": null,
   "id": "f3b8ea59",
   "metadata": {},
   "outputs": [],
   "source": [
    "issue.rename(columns={'field': 'issue_field', 'issue': 'issue_class'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3862bad1",
   "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'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1981f150",
   "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": null,
   "id": "98821bf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_issue.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cd79f6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_issue.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef4e1c27",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_issue.to_csv(\"data/fb2022_output_processed.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0aefa78e",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}