0a_get_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "565085"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os.path\n",
    "import sys\n",
    "import django\n",
    "sys.path.append('/home/galm/software/django/tmv/BasicBrowser')\n",
    "sys.path.append('/home/max/software/django-tmv/tmv_mcc-apsis/BasicBrowser')\n",
    "os.environ.setdefault(\"DJANGO_SETTINGS_MODULE\", \"BasicBrowser.settings\")\n",
    "django.setup()\n",
    "import scoping\n",
    "from scoping.models import *\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "p = Project.objects.get(pk=260)\n",
    "qs = p.query_set.all()\n",
    "q = Query.objects.get(pk=8843)\n",
    "\n",
    "dids = set(Doc.objects.filter(query__in=qs).values_list('pk', flat=True))\n",
    "len(dids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "regenerate_docinfo = False\n",
    "if regenerate_docinfo:\n",
    "    df = pd.DataFrame.from_dict(Doc.objects.filter(pk__in=dids).values('id','PY','title','wosarticle__dt'))\n",
    "    df.columns = [\"id\",\"Publication Year\",\"Title\",\"Keywords\"]\n",
    "    df.head()\n",
    "    df.to_csv('data/0c_doc_info.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>doc__content</th>\n",
       "      <th>category__name</th>\n",
       "      <th>category__level</th>\n",
       "      <th>user__username</th>\n",
       "      <th>countries__name</th>\n",
       "      <th>texts__name</th>\n",
       "      <th>selection_tier</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3212104</td>\n",
       "      <td>Carbon emission quota allocating on coal and e...</td>\n",
       "      <td>This paper considers the fair and efficient pr...</td>\n",
       "      <td>1. Economic instruments</td>\n",
       "      <td>4</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>222862</td>\n",
       "      <td>A COMPREHENSIVE SOLUTION FOR DEVELOPMENT OF LA...</td>\n",
       "      <td>To combat climate change and fulfill the natio...</td>\n",
       "      <td>1. Economic instruments</td>\n",
       "      <td>4</td>\n",
       "      <td>galm</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1894344</td>\n",
       "      <td>Algerian Renewable Energy Projects for Clean E...</td>\n",
       "      <td>Renewable energy sources RES (solar, wind reso...</td>\n",
       "      <td>1. Economic instruments</td>\n",
       "      <td>4</td>\n",
       "      <td>doebbeling@mcc-berlin.net</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3775854</td>\n",
       "      <td>China's Carbon Pricing Based on Heterogeneous ...</td>\n",
       "      <td>To address climate change, the carbon emission...</td>\n",
       "      <td>1. Economic instruments</td>\n",
       "      <td>4</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51374</td>\n",
       "      <td>THE CHOICE BETWEEN AUCTIONING AND GRANDFATHERI...</td>\n",
       "      <td>Based on Hillman's (1982) political support fu...</td>\n",
       "      <td>1. Economic instruments</td>\n",
       "      <td>4</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   doc__id                                         doc__title  \\\n",
       "0  3212104  Carbon emission quota allocating on coal and e...   \n",
       "1   222862  A COMPREHENSIVE SOLUTION FOR DEVELOPMENT OF LA...   \n",
       "2  1894344  Algerian Renewable Energy Projects for Clean E...   \n",
       "3  3775854  China's Carbon Pricing Based on Heterogeneous ...   \n",
       "4    51374  THE CHOICE BETWEEN AUCTIONING AND GRANDFATHERI...   \n",
       "\n",
       "                                        doc__content           category__name  \\\n",
       "0  This paper considers the fair and efficient pr...  1. Economic instruments   \n",
       "1  To combat climate change and fulfill the natio...  1. Economic instruments   \n",
       "2  Renewable energy sources RES (solar, wind reso...  1. Economic instruments   \n",
       "3  To address climate change, the carbon emission...  1. Economic instruments   \n",
       "4  Based on Hillman's (1982) political support fu...  1. Economic instruments   \n",
       "\n",
       "  category__level               user__username countries__name texts__name  \\\n",
       "0               4     vasudevan@mcc-berlin.net            None        None   \n",
       "1               4                         galm            None        None   \n",
       "2               4    doebbeling@mcc-berlin.net            None        None   \n",
       "3               4  edmondson@hertie-school.org            None        None   \n",
       "4               4     vasudevan@mcc-berlin.net            None        None   \n",
       "\n",
       "   selection_tier                Category Name val  \n",
       "0               1  4 - 1. Economic instruments   1  \n",
       "1               1  4 - 1. Economic instruments   1  \n",
       "2               2  4 - 1. Economic instruments   2  \n",
       "3               1  4 - 1. Economic instruments   1  \n",
       "4               1  4 - 1. Economic instruments   1  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "django.db.connection.close()\n",
    "\n",
    "dos = DocOwnership.objects.filter(tag__query__project=p, relevant__gt=0)\n",
    "\n",
    "doc_ids = set(dos.values_list('doc__id',flat=True))\n",
    "\n",
    "# Get relevant / not relevant decisions\n",
    "do_df = pd.DataFrame.from_dict(\n",
    "    dos.values('doc__id','doc__title','doc__content','user__username','relevant')\n",
    ")\n",
    "do_df.loc[do_df['relevant']==0,'val'] = np.NaN\n",
    "do_df.loc[do_df['relevant']==1,'val'] = 1\n",
    "do_df.loc[do_df['relevant']==2,'val'] = 0\n",
    "do_df.loc[do_df['relevant']==3,'val'] = np.NaN\n",
    "do_df.head()\n",
    "\n",
    "# Get category assignment\n",
    "cats = Category.objects.filter(project=p).exclude(name__icontains=\"hidden>\")\n",
    "df = pd.DataFrame.from_dict(\n",
    "    DocUserCat.objects.filter(\n",
    "        category__in=cats,\n",
    "        doc__in=doc_ids\n",
    "    ).values(\n",
    "        'doc__id',\n",
    "        'doc__title',\n",
    "        'doc__content',\n",
    "        'category__name',\n",
    "        'category__level',\n",
    "        'user__username',\n",
    "        'countries__name',\n",
    "        'texts__name',\n",
    "        'selection_tier'\n",
    "    )\n",
    ")\n",
    "# Filter so that it matches the relevant set\n",
    "df = df[\n",
    "    (df[['doc__id','user__username']].apply(tuple, axis=1).isin(do_df[['doc__id','user__username']].apply(tuple, axis=1))) | \n",
    "    (df['user__username']==\"Auto\")\n",
    "]\n",
    "\n",
    "# Rename category to capture level\n",
    "df['category__level'] = df['category__level'].astype(str)\n",
    "df['Category Name'] = df[['category__level','category__name']].apply(lambda x: ' - '.join(x), axis=1)\n",
    "#df['cat'] = df[['category__level','category__name','user__username']].apply(lambda x: ' - '.join(x), axis=1)\n",
    "\n",
    "# Capture the primary/secondary value (1 for primary, 2 for secondary)\n",
    "df['val'] = df['selection_tier']\n",
    "\n",
    "# Set the value to the country / text where appropriate and j\n",
    "df.loc[~pd.isna(df['countries__name']),'val'] = df.loc[~pd.isna(df['countries__name']),'countries__name']\n",
    "df.loc[~pd.isna(df['texts__name']),'val'] = df.loc[~pd.isna(df['texts__name']),'texts__name']\n",
    "\n",
    "cats = df['Category Name'].unique()\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<QuerySet [{'tag__title': 'Probably relevant random sample 9', 'tag__id': 6540, 'user__username': 'vasudevan@mcc-berlin.net'}, {'tag__title': 'Probably relevant random sample 9', 'tag__id': 6540, 'user__username': 'lucy.banisch@icloud.com'}]>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DocOwnership.objects.filter(tag__query__project=p,doc=3556).values('tag__title','tag__id','user__username')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>doc__content</th>\n",
       "      <th>user__username</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5630</th>\n",
       "      <td>1838661</td>\n",
       "      <td>Cultural and Social Elements in the Developmen...</td>\n",
       "      <td>In the context of climate change and energy cr...</td>\n",
       "      <td>doebbeling@mcc-berlin.net</td>\n",
       "      <td>0 - relevant</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5631</th>\n",
       "      <td>315555</td>\n",
       "      <td>GLOBAL EXPERIMENTALIST GOVERNANCE, INTERNATION...</td>\n",
       "      <td>Global experimentalist governance has emerged ...</td>\n",
       "      <td>galm</td>\n",
       "      <td>0 - relevant</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5632</th>\n",
       "      <td>1799426</td>\n",
       "      <td>Underdetermination, Model-ensembles and Surpri...</td>\n",
       "      <td>As climate policy decisions are decisions unde...</td>\n",
       "      <td>galm</td>\n",
       "      <td>0 - relevant</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5633</th>\n",
       "      <td>3827028</td>\n",
       "      <td>Establishment a recycled green building materi...</td>\n",
       "      <td>This study modifies a dynamic computable gener...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>0 - relevant</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5634</th>\n",
       "      <td>3827028</td>\n",
       "      <td>Establishment a recycled green building materi...</td>\n",
       "      <td>This study modifies a dynamic computable gener...</td>\n",
       "      <td>galm</td>\n",
       "      <td>0 - relevant</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      doc__id                                         doc__title  \\\n",
       "5630  1838661  Cultural and Social Elements in the Developmen...   \n",
       "5631   315555  GLOBAL EXPERIMENTALIST GOVERNANCE, INTERNATION...   \n",
       "5632  1799426  Underdetermination, Model-ensembles and Surpri...   \n",
       "5633  3827028  Establishment a recycled green building materi...   \n",
       "5634  3827028  Establishment a recycled green building materi...   \n",
       "\n",
       "                                           doc__content  \\\n",
       "5630  In the context of climate change and energy cr...   \n",
       "5631  Global experimentalist governance has emerged ...   \n",
       "5632  As climate policy decisions are decisions unde...   \n",
       "5633  This study modifies a dynamic computable gener...   \n",
       "5634  This study modifies a dynamic computable gener...   \n",
       "\n",
       "                 user__username Category Name val  \n",
       "5630  doebbeling@mcc-berlin.net  0 - relevant   0  \n",
       "5631                       galm  0 - relevant   1  \n",
       "5632                       galm  0 - relevant   1  \n",
       "5633   vasudevan@mcc-berlin.net  0 - relevant   1  \n",
       "5634                       galm  0 - relevant   1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge relevant/not relevant with categories\n",
    "do_df['Category Name'] = \"0 - relevant\"\n",
    "\n",
    "merged_df = pd.concat([\n",
    "    df[['doc__id','doc__title','doc__content','user__username','Category Name','val']],\n",
    "    do_df[['doc__id','doc__title','doc__content','user__username','Category Name','val']]\n",
    "])\n",
    "merged_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['3 - 0. Not policy related', '3 - 1. Analysis of an existing policy / concrete proposal', '3 - 2. Analysis of a generic policy', '3 - 3. Broader governance and policy']\n",
      "['3 - 1. Analysis of an existing policy / concrete proposal', '3 - 2. Analysis of a generic policy']\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>Category Name</th>\n",
       "      <th>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>doc__content</th>\n",
       "      <th>user__username</th>\n",
       "      <th>0 - relevant</th>\n",
       "      <th>10 - 3. Quantitative</th>\n",
       "      <th>10 - 4. Qualitative</th>\n",
       "      <th>11 - countries</th>\n",
       "      <th>12 - policy name</th>\n",
       "      <th>2 - 0. Mitigation</th>\n",
       "      <th>...</th>\n",
       "      <th>8 - 10. Coastal systems and low-lying areas</th>\n",
       "      <th>8 - 11. Ocean systems</th>\n",
       "      <th>8 - 12. Food</th>\n",
       "      <th>8 - 13. Human settlements, industry and infrastructure</th>\n",
       "      <th>8 - 14. Human health, well-being and security</th>\n",
       "      <th>8 - 15. Cross-sectoral</th>\n",
       "      <th>9 - 0. Ex-post</th>\n",
       "      <th>9 - 1. Ex-ante</th>\n",
       "      <th>9 - 2. Ex-ante (IAM)</th>\n",
       "      <th>INCLUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5530</th>\n",
       "      <td>3878364</td>\n",
       "      <td>Occupants' Awareness of and Satisfaction with ...</td>\n",
       "      <td>With growing awareness of the need to reduce g...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</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",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5531</th>\n",
       "      <td>3878440</td>\n",
       "      <td>Urban consumers' willingness to pay for higher...</td>\n",
       "      <td>Rapid urbanization poses accelerating pressure...</td>\n",
       "      <td>ceposadap@gmail.com</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>China</td>\n",
       "      <td>Energy Efficiency Labeling System (EELS)</td>\n",
       "      <td>1</td>\n",
       "      <td>...</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>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5532</th>\n",
       "      <td>3878440</td>\n",
       "      <td>Urban consumers' willingness to pay for higher...</td>\n",
       "      <td>Rapid urbanization poses accelerating pressure...</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5533</th>\n",
       "      <td>3878516</td>\n",
       "      <td>Life cycle greenhouse gas impacts of a connect...</td>\n",
       "      <td>As technological advancements progress, the au...</td>\n",
       "      <td>ceposadap@gmail.com</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5534</th>\n",
       "      <td>3878516</td>\n",
       "      <td>Life cycle greenhouse gas impacts of a connect...</td>\n",
       "      <td>As technological advancements progress, the au...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 106 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Category Name  doc__id                                         doc__title  \\\n",
       "5530           3878364  Occupants' Awareness of and Satisfaction with ...   \n",
       "5531           3878440  Urban consumers' willingness to pay for higher...   \n",
       "5532           3878440  Urban consumers' willingness to pay for higher...   \n",
       "5533           3878516  Life cycle greenhouse gas impacts of a connect...   \n",
       "5534           3878516  Life cycle greenhouse gas impacts of a connect...   \n",
       "\n",
       "Category Name                                       doc__content  \\\n",
       "5530           With growing awareness of the need to reduce g...   \n",
       "5531           Rapid urbanization poses accelerating pressure...   \n",
       "5532           Rapid urbanization poses accelerating pressure...   \n",
       "5533           As technological advancements progress, the au...   \n",
       "5534           As technological advancements progress, the au...   \n",
       "\n",
       "Category Name               user__username 0 - relevant  10 - 3. Quantitative  \\\n",
       "5530              vasudevan@mcc-berlin.net            1                   0.0   \n",
       "5531                   ceposadap@gmail.com            1                   1.0   \n",
       "5532           edmondson@hertie-school.org            1                   0.0   \n",
       "5533                   ceposadap@gmail.com            1                   0.0   \n",
       "5534              vasudevan@mcc-berlin.net            1                   0.0   \n",
       "\n",
       "Category Name  10 - 4. Qualitative 11 - countries  \\\n",
       "5530                           0.0              0   \n",
       "5531                           0.0          China   \n",
       "5532                           0.0              0   \n",
       "5533                           0.0              0   \n",
       "5534                           0.0              0   \n",
       "\n",
       "Category Name                          12 - policy name  2 - 0. Mitigation  \\\n",
       "5530                                                  0                  1   \n",
       "5531           Energy Efficiency Labeling System (EELS)                  1   \n",
       "5532                                                  0                  1   \n",
       "5533                                                  0                  1   \n",
       "5534                                                  0                  1   \n",
       "\n",
       "Category Name  ...  8 - 10. Coastal systems and low-lying areas  \\\n",
       "5530           ...                                          0.0   \n",
       "5531           ...                                          0.0   \n",
       "5532           ...                                          0.0   \n",
       "5533           ...                                          0.0   \n",
       "5534           ...                                          0.0   \n",
       "\n",
       "Category Name  8 - 11. Ocean systems  8 - 12. Food  \\\n",
       "5530                             0.0           0.0   \n",
       "5531                             0.0           0.0   \n",
       "5532                             0.0           0.0   \n",
       "5533                             0.0           0.0   \n",
       "5534                             0.0           0.0   \n",
       "\n",
       "Category Name  8 - 13. Human settlements, industry and infrastructure  \\\n",
       "5530                                                         0.0        \n",
       "5531                                                         0.0        \n",
       "5532                                                         0.0        \n",
       "5533                                                         0.0        \n",
       "5534                                                         0.0        \n",
       "\n",
       "Category Name  8 - 14. Human health, well-being and security  \\\n",
       "5530                                                     0.0   \n",
       "5531                                                     0.0   \n",
       "5532                                                     0.0   \n",
       "5533                                                     0.0   \n",
       "5534                                                     0.0   \n",
       "\n",
       "Category Name  8 - 15. Cross-sectoral  9 - 0. Ex-post  9 - 1. Ex-ante  \\\n",
       "5530                              0.0             0.0             0.0   \n",
       "5531                              0.0             1.0             0.0   \n",
       "5532                              0.0             0.0             0.0   \n",
       "5533                              0.0             0.0             0.0   \n",
       "5534                              0.0             0.0             0.0   \n",
       "\n",
       "Category Name  9 - 2. Ex-ante (IAM)  INCLUDE  \n",
       "5530                            0.0      1.0  \n",
       "5531                            0.0      1.0  \n",
       "5532                            0.0      0.0  \n",
       "5533                            0.0      0.0  \n",
       "5534                            0.0      0.0  \n",
       "\n",
       "[5 rows x 106 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fill tag values with 0s where they have been rated\n",
    "dudf_wide = (merged_df[['doc__id','doc__title','doc__content','user__username','Category Name','val']]\n",
    "             .pivot_table(index=['doc__id','doc__title','doc__content','user__username'],columns=\"Category Name\", values=\"val\", aggfunc=np.sum)\n",
    "             #.fillna(0)\n",
    "            ).reset_index()\n",
    "\n",
    "cats = [x for x in cats if x in dudf_wide.columns]\n",
    "\n",
    "dudf_wide[cats] = dudf_wide[cats].fillna(0)\n",
    "\n",
    "policy_columns=[x for x in dudf_wide.columns if re.match(\"^3\",x)]\n",
    "include_policy_columns = [x for x in policy_columns if \"not policy related\" not in x.lower() and \"broader\" not in x.lower()]\n",
    "print(policy_columns)\n",
    "print(include_policy_columns)\n",
    "post_policy_columns=[x for x in dudf_wide.columns if re.match(\"^[4-9]\",x) or re.match(\"^[0-9]{2}\",x)]\n",
    "#dudf_wide.loc[dudf_wide['3 - 0. Not policy related']==1,post_policy_columns] = np.NaN\n",
    "dudf_wide.loc[dudf_wide[policy_columns].sum(axis=1,min_count=1)==0,post_policy_columns] = np.NaN\n",
    "dudf_wide.loc[dudf_wide[include_policy_columns].sum(axis=1,min_count=1)>0,\"INCLUDE\"] = 1\n",
    "dudf_wide.loc[dudf_wide[include_policy_columns].sum(axis=1,min_count=1)==0,\"INCLUDE\"] = 0\n",
    "\n",
    "dudf_wide.to_csv('data/0_all_labels.csv',index=False)\n",
    "\n",
    "dudf_wide.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2580, 103)\n",
      "(0, 103)\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>doc__id</th>\n",
       "      <th>4 - 1. Economic instruments</th>\n",
       "      <th>4 - 2. Regulatory Instruments</th>\n",
       "      <th>4 - 3. Information, education and training</th>\n",
       "      <th>4 - 4. Governance, strategies and targets</th>\n",
       "      <th>4 - 5. Agreements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1777</th>\n",
       "      <td>1828017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      doc__id  4 - 1. Economic instruments  4 - 2. Regulatory Instruments  \\\n",
       "1777  1828017                          0.0                            0.0   \n",
       "\n",
       "      4 - 3. Information, education and training  \\\n",
       "1777                                         0.0   \n",
       "\n",
       "      4 - 4. Governance, strategies and targets  4 - 5. Agreements  \n",
       "1777                                        1.0                0.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now get a single value for each document by choosing the most common selection\n",
    "import statistics\n",
    "def resolve_conflicts(x):\n",
    "    valid_codes = [y for y in x if pd.notna(y)]\n",
    "    l = len(set(valid_codes))\n",
    "    if l==0: # If no valid codes return NA\n",
    "        return np.NaN\n",
    "    elif l==1: # If there is only one unique code, return the first\n",
    "        return valid_codes[0]\n",
    "    else: # Otherwise find the majority decision\n",
    "        rm = np.mean(rounded_codes)\n",
    "        if rm > 0.5: # overall, a majority coded positive (primary or secondary)\n",
    "            return 0.66\n",
    "        elif rm==0.5: # even split, can't decide\n",
    "            return np.NaN\n",
    "        else: # mostly negative\n",
    "            return 0\n",
    "    \n",
    "# First make it long, so that each row is a doc-category-user rating\n",
    "xdf = dudf_wide.melt(id_vars=['doc__id','doc__title','doc__content','user__username'])\n",
    "\n",
    "# Remove categories 11...+ as they are strings not int and need to be dealt with separately\n",
    "xdf = xdf[~xdf['Category Name'].isin(['11 - countries','12 - policy name'])]\n",
    "\n",
    "xdf['value'] = xdf['value'].replace(2,0.5)\n",
    "\n",
    "# Now group by doc and category, and resolve conflicts\n",
    "xdf = xdf.groupby(['doc__id','doc__title','doc__content','Category Name'])['value'].agg(lambda x: resolve_conflicts(x)).reset_index()\n",
    "\n",
    "# weight samples according to the level of conflict and the value\n",
    "# Unanimous 1s get a weight of 1\n",
    "xdf.loc[xdf['value'].isin([0,1]),\"sample_weight\"] = 1\n",
    "# Majority 1s, or mixture of 1s and 2s get a weight of 0.66\n",
    "xdf.loc[xdf['value']==0.66,\"sample_weight\"] = 0.66\n",
    "# Unanimous 2s get a weight of 0.5\n",
    "xdf.loc[xdf['value']==0.5,\"sample_weight\"] = 0.5\n",
    "\n",
    "# recode back to integer values\n",
    "xdf['value'] = xdf['value'].replace(0.66,1)\n",
    "xdf['value'] = xdf['value'].replace(0.5,1)\n",
    "\n",
    "\n",
    "# Now pivot so that each category is a column\n",
    "seen_df = xdf.drop(columns=\"sample_weight\").pivot(index=[\"doc__id\",\"doc__title\",\"doc__content\"],columns=\"Category Name\").reset_index()\n",
    "# xdf[\"Category Name\"] = xdf[\"Category Name\"] + \"_sample_weight\"\n",
    "# weights_df = xdf.drop(columns=\"value\").pivot(index=[\"doc__id\",\"doc__title\",\"doc__content\"],columns=\"Category Name\").reset_index()\n",
    "print(seen_df.shape)\n",
    "\n",
    "seen_df.columns = [col[1] if col[1] !=\"\" else col[0] for col in seen_df.columns]\n",
    "\n",
    "print(seen_df[pd.isna(seen_df['0 - relevant'])].shape)\n",
    "\n",
    "seen_df.head()\n",
    "\n",
    "cols_4 = [x for x in seen_df.columns if re.match(\"^4 -\",x)]\n",
    "seen_df[seen_df['doc__id']==1828017][[\"doc__id\"]+cols_4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>doc__id</th>\n",
       "      <th>3 - 0. Not policy related</th>\n",
       "      <th>3 - 1. Analysis of an existing policy / concrete proposal</th>\n",
       "      <th>3 - 2. Analysis of a generic policy</th>\n",
       "      <th>3 - 3. Broader governance and policy</th>\n",
       "      <th>4 - 1. Economic instruments</th>\n",
       "      <th>4 - 2. Regulatory Instruments</th>\n",
       "      <th>4 - 3. Information, education and training</th>\n",
       "      <th>4 - 4. Governance, strategies and targets</th>\n",
       "      <th>4 - 5. Agreements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1767</th>\n",
       "      <td>1824928</td>\n",
       "      <td>1.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",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      doc__id  3 - 0. Not policy related  \\\n",
       "1767  1824928                        1.0   \n",
       "\n",
       "      3 - 1. Analysis of an existing policy / concrete proposal  \\\n",
       "1767                                                0.0           \n",
       "\n",
       "      3 - 2. Analysis of a generic policy  \\\n",
       "1767                                  0.0   \n",
       "\n",
       "      3 - 3. Broader governance and policy  4 - 1. Economic instruments  \\\n",
       "1767                                   0.0                          0.0   \n",
       "\n",
       "      4 - 2. Regulatory Instruments  \\\n",
       "1767                            0.0   \n",
       "\n",
       "      4 - 3. Information, education and training  \\\n",
       "1767                                         0.0   \n",
       "\n",
       "      4 - 4. Governance, strategies and targets  4 - 5. Agreements  \n",
       "1767                                        0.0                0.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_3 = [x for x in seen_df.columns if re.match(\"^3 -\",x)]\n",
    "seen_df[seen_df['doc__id']==1824928][[\"doc__id\"]+cols_3 + cols_4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>doc__content</th>\n",
       "      <th>user__username</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42509</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>3 - 0. Not policy related</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42510</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>3 - 0. Not policy related</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48044</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>3 - 1. Analysis of an existing policy / concre...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48045</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>3 - 1. Analysis of an existing policy / concre...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53579</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>3 - 2. Analysis of a generic policy</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53580</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>3 - 2. Analysis of a generic policy</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59114</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>edmondson@hertie-school.org</td>\n",
       "      <td>3 - 3. Broader governance and policy</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59115</th>\n",
       "      <td>1824928</td>\n",
       "      <td>Forest fires and air pollution: A local and a ...</td>\n",
       "      <td>A numerical system developed to simulate the e...</td>\n",
       "      <td>vasudevan@mcc-berlin.net</td>\n",
       "      <td>3 - 3. Broader governance and policy</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       doc__id                                         doc__title  \\\n",
       "42509  1824928  Forest fires and air pollution: A local and a ...   \n",
       "42510  1824928  Forest fires and air pollution: A local and a ...   \n",
       "48044  1824928  Forest fires and air pollution: A local and a ...   \n",
       "48045  1824928  Forest fires and air pollution: A local and a ...   \n",
       "53579  1824928  Forest fires and air pollution: A local and a ...   \n",
       "53580  1824928  Forest fires and air pollution: A local and a ...   \n",
       "59114  1824928  Forest fires and air pollution: A local and a ...   \n",
       "59115  1824928  Forest fires and air pollution: A local and a ...   \n",
       "\n",
       "                                            doc__content  \\\n",
       "42509  A numerical system developed to simulate the e...   \n",
       "42510  A numerical system developed to simulate the e...   \n",
       "48044  A numerical system developed to simulate the e...   \n",
       "48045  A numerical system developed to simulate the e...   \n",
       "53579  A numerical system developed to simulate the e...   \n",
       "53580  A numerical system developed to simulate the e...   \n",
       "59114  A numerical system developed to simulate the e...   \n",
       "59115  A numerical system developed to simulate the e...   \n",
       "\n",
       "                    user__username  \\\n",
       "42509  edmondson@hertie-school.org   \n",
       "42510     vasudevan@mcc-berlin.net   \n",
       "48044  edmondson@hertie-school.org   \n",
       "48045     vasudevan@mcc-berlin.net   \n",
       "53579  edmondson@hertie-school.org   \n",
       "53580     vasudevan@mcc-berlin.net   \n",
       "59114  edmondson@hertie-school.org   \n",
       "59115     vasudevan@mcc-berlin.net   \n",
       "\n",
       "                                           Category Name value  \n",
       "42509                          3 - 0. Not policy related     1  \n",
       "42510                          3 - 0. Not policy related     1  \n",
       "48044  3 - 1. Analysis of an existing policy / concre...     0  \n",
       "48045  3 - 1. Analysis of an existing policy / concre...     0  \n",
       "53579                3 - 2. Analysis of a generic policy     0  \n",
       "53580                3 - 2. Analysis of a generic policy     0  \n",
       "59114               3 - 3. Broader governance and policy     0  \n",
       "59115               3 - 3. Broader governance and policy     0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First make it long, so that each row is a doc-category-user rating\n",
    "xdf = dudf_wide.melt(id_vars=['doc__id','doc__title','doc__content','user__username'])\n",
    "\n",
    "# Remove categories 11...+ as they are strings not int and need to be dealt with separately\n",
    "xdf = xdf[(xdf['Category Name'].str.match(\"^3{1}\")) & (xdf['doc__id']==1824928)]\n",
    "xdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "renamings = [\n",
    "    (\"5 - 2.07. Spatial and land-use planning\",\"5 - 2.09. Spatial and land-use planning\"),\n",
    "    (\"5 - 2.07. Spatial and land-use planning\",\"6 - 2.09. Spatial and land-use planning\"),\n",
    "    (\"5 - 5.17. Inter/transnational agreements\",\"5 - 5.20. Inter/transnational agreements\"),\n",
    "    (\"5 - 5.17. Inter/transnational agreements\",\"6 - 5.20. Inter/transnational agreements\"),\n",
    "    (\"Analysis of a specific policy\",\"Analysis of an existing policy / concrete proposal\"),\n",
    "    (\"Governance and planning\", \"Governance, strategies and targets\"),\n",
    "    (\"5 - 2.08. Other regulation\", \"5 - 2.10. Other regulation\"),\n",
    "    (\"5 - 2.08. Other regulation\", \"6 - 2.10. Other regulation\"),\n",
    "    (\"Other institutions or agencies\", \"Other institutional arrangements\"),\n",
    "    (\"5 - 3.11. Behavioural interventions\", \"5 - 3.13. Behavioural interventions\"),\n",
    "    (\"5 - 3.11. Behavioural interventions\", \"6 - 3.02. Behavioural interventions\"),\n",
    "    (\"5 - 3.12. Education and training\",\"5 - 3.14. Education and training\"),  \n",
    "    \n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "could not parse generic, strategies\n",
      "could not parse New Category\n",
      "could not parse Demonstration \n",
      "could not parse expert advice \n",
      "could not parse ?\n",
      "could not parse infrastructure investment + new category \n",
      "could not parse NEW CATEGORY\n",
      "could not parse NEW CATEGORY\n",
      "could not parse NEW CATEGORY\n",
      "could not parse NEW CATEGORY\n",
      "could not parse Tradable Quotas\n",
      "could not parse new category\n",
      "could not parse carbon payments\n",
      "Cannot read .~lock.Probably relevant random sample 17_Shraddha_Lucy.xlsx#\n",
      "(7702, 21)\n"
     ]
    }
   ],
   "source": [
    "# Loop through the comparison/resolution files and resolve labelled dataset\n",
    "resolutions = pd.DataFrame()\n",
    "\n",
    "for fname in os.listdir(\"decision_resolutions\"):\n",
    "    if \".xlsx\" in fname:\n",
    "        try:\n",
    "            cdf = pd.read_excel(f'decision_resolutions/{fname}',sheet_name=\"comparison\")\n",
    "        except:\n",
    "            print(f\"Cannot read {fname}\")\n",
    "    dcolumn = [x for x in cdf.columns if \"decision\" in x.lower()][0]\n",
    "    cdf = cdf.rename(columns={dcolumn: \"decision\"})\n",
    "    cdf = cdf[pd.notna(cdf[\"decision\"])]\n",
    "    cdf = cdf[cdf['Category Name'].str.match(\"^[0-9]{1}(?![1-2])\")]\n",
    "    \n",
    "    cdf['fname'] = fname\n",
    "    \n",
    "    resolutions = pd.concat([resolutions, cdf])\n",
    "    \n",
    "    for i, row in cdf.iterrows():\n",
    "        try:\n",
    "            x = int(row[\"decision\"])\n",
    "        except:\n",
    "            print(f\"could not parse {row['decision']}\")\n",
    "            continue\n",
    "        cname = row['Category Name']\n",
    "        \n",
    "        for r1, r2 in renamings:\n",
    "            cname = cname.replace(r1,r2)\n",
    "        if cname not in seen_df.columns:\n",
    "            matches = [x for x in seen_df.columns if cname.split('. ')[1] in x]\n",
    "            if len(matches) == 1:\n",
    "                cname = matches[0]\n",
    "            else:\n",
    "                seen_df[cname] = 0\n",
    "                continue\n",
    "            \n",
    "        seen_df.loc[(seen_df['doc__id']==row['doc__id']),cname] = row[\"decision\"]\n",
    "        \n",
    "print(resolutions.shape)\n",
    "resolutions.to_csv('data/0_conflict_resolutions.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>doc__content</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>ceposadap@gmail.com</th>\n",
       "      <th>edmondson@hertie-school.org</th>\n",
       "      <th>galm</th>\n",
       "      <th>lucy.banisch@icloud.com</th>\n",
       "      <th>agreement</th>\n",
       "      <th>decision</th>\n",
       "      <th>...</th>\n",
       "      <th>doebbeling@mcc-berlin.net</th>\n",
       "      <th>vasudevan@mcc-berlin.net</th>\n",
       "      <th>muef</th>\n",
       "      <th>Spalte1</th>\n",
       "      <th>doebbeling@posteo.net</th>\n",
       "      <th>flac</th>\n",
       "      <th>lamw</th>\n",
       "      <th>Unnamed: 10</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11842</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>3 - 1. Analysis of an existing policy / concre...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11843</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>3 - 2. Analysis of a generic policy</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11845</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>4 - 1. Economic instruments</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11846</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>4 - 2. Regulatory Instruments</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11847</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>4 - 3. Information, education and training</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11848</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>4 - 4. Governance, strategies and targets</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11849</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>4 - 5. Agreements</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11861</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>5 - 4.16. Planning</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11883</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>6 - 4.01. Targets &amp; goals</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11888</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>7 - 0. Supranational and international</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11895</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>8 - 04. Energy</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11898</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>8 - 15. Cross-sectoral</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11899</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>9 - 0. Ex-post</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11900</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>Recently many events occurred in the energy sc...</td>\n",
       "      <td>9 - 1. Ex-ante</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       doc__id                                         doc__title  \\\n",
       "11842  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11843  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11845  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11846  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11847  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11848  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11849  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11861  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11883  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11888  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11895  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11898  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11899  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "11900  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "\n",
       "                                            doc__content  \\\n",
       "11842  Recently many events occurred in the energy sc...   \n",
       "11843  Recently many events occurred in the energy sc...   \n",
       "11845  Recently many events occurred in the energy sc...   \n",
       "11846  Recently many events occurred in the energy sc...   \n",
       "11847  Recently many events occurred in the energy sc...   \n",
       "11848  Recently many events occurred in the energy sc...   \n",
       "11849  Recently many events occurred in the energy sc...   \n",
       "11861  Recently many events occurred in the energy sc...   \n",
       "11883  Recently many events occurred in the energy sc...   \n",
       "11888  Recently many events occurred in the energy sc...   \n",
       "11895  Recently many events occurred in the energy sc...   \n",
       "11898  Recently many events occurred in the energy sc...   \n",
       "11899  Recently many events occurred in the energy sc...   \n",
       "11900  Recently many events occurred in the energy sc...   \n",
       "\n",
       "                                           Category Name ceposadap@gmail.com  \\\n",
       "11842  3 - 1. Analysis of an existing policy / concre...                   0   \n",
       "11843                3 - 2. Analysis of a generic policy                   2   \n",
       "11845                        4 - 1. Economic instruments                   0   \n",
       "11846                      4 - 2. Regulatory Instruments                   0   \n",
       "11847         4 - 3. Information, education and training                   0   \n",
       "11848          4 - 4. Governance, strategies and targets                   0   \n",
       "11849                                  4 - 5. Agreements                   2   \n",
       "11861                                 5 - 4.16. Planning                   0   \n",
       "11883                          6 - 4.01. Targets & goals                   0   \n",
       "11888             7 - 0. Supranational and international                   1   \n",
       "11895                                     8 - 04. Energy                   0   \n",
       "11898                             8 - 15. Cross-sectoral                   1   \n",
       "11899                                     9 - 0. Ex-post                   0   \n",
       "11900                                     9 - 1. Ex-ante                   1   \n",
       "\n",
       "      edmondson@hertie-school.org galm lucy.banisch@icloud.com  agreement  \\\n",
       "11842                         NaN  NaN                       2          0   \n",
       "11843                         NaN  NaN                       0          0   \n",
       "11845                         NaN  NaN                       0          1   \n",
       "11846                         NaN  NaN                       0          1   \n",
       "11847                         NaN  NaN                       0          1   \n",
       "11848                         NaN  NaN                       2          0   \n",
       "11849                         NaN  NaN                       2          1   \n",
       "11861                         NaN  NaN                       2          0   \n",
       "11883                         NaN  NaN                       2          0   \n",
       "11888                         NaN  NaN                       2          0   \n",
       "11895                         NaN  NaN                       2          0   \n",
       "11898                         NaN  NaN                       0          0   \n",
       "11899                         NaN  NaN                       1          0   \n",
       "11900                         NaN  NaN                       0          0   \n",
       "\n",
       "      decision  ... doebbeling@mcc-berlin.net vasudevan@mcc-berlin.net muef  \\\n",
       "11842        2  ...                       NaN                      NaN  NaN   \n",
       "11843        0  ...                       NaN                      NaN  NaN   \n",
       "11845        0  ...                       NaN                      NaN  NaN   \n",
       "11846        0  ...                       NaN                      NaN  NaN   \n",
       "11847        0  ...                       NaN                      NaN  NaN   \n",
       "11848        2  ...                       NaN                      NaN  NaN   \n",
       "11849        2  ...                       NaN                      NaN  NaN   \n",
       "11861        2  ...                       NaN                      NaN  NaN   \n",
       "11883        2  ...                       NaN                      NaN  NaN   \n",
       "11888        1  ...                       NaN                      NaN  NaN   \n",
       "11895        0  ...                       NaN                      NaN  NaN   \n",
       "11898        1  ...                       NaN                      NaN  NaN   \n",
       "11899        1  ...                       NaN                      NaN  NaN   \n",
       "11900        0  ...                       NaN                      NaN  NaN   \n",
       "\n",
       "      Spalte1 doebbeling@posteo.net flac  lamw Unnamed: 10 Unnamed: 8  \\\n",
       "11842     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11843     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11845     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11846     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11847     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11848     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11849     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11861     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11883     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11888     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11895     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11898     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11899     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "11900     NaN                   NaN  NaN   NaN         NaN        NaN   \n",
       "\n",
       "      Unnamed: 9  \n",
       "11842        NaN  \n",
       "11843        NaN  \n",
       "11845        NaN  \n",
       "11846        NaN  \n",
       "11847        NaN  \n",
       "11848        NaN  \n",
       "11849        NaN  \n",
       "11861        NaN  \n",
       "11883        NaN  \n",
       "11888        NaN  \n",
       "11895        NaN  \n",
       "11898        NaN  \n",
       "11899        NaN  \n",
       "11900        NaN  \n",
       "\n",
       "[14 rows x 21 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resolutions[resolutions['doc__id']==1865162]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 - 0. Ex-post - prediction\n"
     ]
    }
   ],
   "source": [
    "corrections = pd.read_csv('decision_resolutions/master/corrections.csv', quotechar=\"'\")\n",
    "\n",
    "for i, row in corrections.iterrows():\n",
    "    try:\n",
    "        x = int(row[dcolumn])\n",
    "    except:\n",
    "        print(f\"could not parse {row[dcolumn]}\")\n",
    "        continue\n",
    "    cname = row['Category Name']\n",
    "\n",
    "    for r1, r2 in renamings:\n",
    "        cname = cname.replace(r1,r2)\n",
    "    if cname not in seen_df.columns:\n",
    "        matches = [x for x in seen_df.columns if cname.split('. ')[1] in x]\n",
    "        if len(matches) == 1:\n",
    "            cname = matches[0]\n",
    "        else:\n",
    "            print(cname)\n",
    "            continue\n",
    "\n",
    "    seen_df.loc[(seen_df['doc__id']==row['doc__id']),cname] = row[dcolumn]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>doc__id</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>decision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1824928</td>\n",
       "      <td>3 - 1. Analysis of an existing policy / concre...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   doc__id                                      Category Name  decision\n",
       "4  1824928  3 - 1. Analysis of an existing policy / concre...         2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corrections[corrections['doc__id']==1824928]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>0 - relevant</th>\n",
       "      <th>3 - 0. Not policy related</th>\n",
       "      <th>3 - 1. Analysis of an existing policy / concrete proposal</th>\n",
       "      <th>3 - 2. Analysis of a generic policy</th>\n",
       "      <th>3 - 3. Broader governance and policy</th>\n",
       "      <th>INCLUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>156</td>\n",
       "      <td>THE EXPECTED GREENHOUSE BENEFITS FROM DEVELOPI...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>337</td>\n",
       "      <td>Energy forecast till 2020 for the German Gover...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>921</td>\n",
       "      <td>Ontology engineering in provenance enablement ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>1448</td>\n",
       "      <td>Planning, governance and rural futures in Aust...</td>\n",
       "      <td>1.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>3556</td>\n",
       "      <td>Will economic restructuring in China reduce tr...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3679</td>\n",
       "      <td>Forcing and response in simulated 20th and 21s...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>6</th>\n",
       "      <td>3683</td>\n",
       "      <td>Egeson's (George's) transtridecadal weather cy...</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>7</th>\n",
       "      <td>4793</td>\n",
       "      <td>An emerging equilibrium in the EU emissions tr...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4929</td>\n",
       "      <td>Adapting production forests in southern Sweden...</td>\n",
       "      <td>1.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>9</th>\n",
       "      <td>5220</td>\n",
       "      <td>Dual climate change responsibility: on moral d...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5459</td>\n",
       "      <td>Vegetation structure and biodiversity along th...</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>11</th>\n",
       "      <td>6490</td>\n",
       "      <td>Analysing co-benefits of the energy conservati...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.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>12</th>\n",
       "      <td>7165</td>\n",
       "      <td>Carbon Leakage from the Clean Development Mech...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8160</td>\n",
       "      <td>The economics of tidal energy</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>14</th>\n",
       "      <td>8932</td>\n",
       "      <td>Out of the energy box</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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",
       "</div>"
      ],
      "text/plain": [
       "    doc__id                                         doc__title  0 - relevant  \\\n",
       "0       156  THE EXPECTED GREENHOUSE BENEFITS FROM DEVELOPI...           1.0   \n",
       "1       337  Energy forecast till 2020 for the German Gover...           1.0   \n",
       "2       921  Ontology engineering in provenance enablement ...           1.0   \n",
       "3      1448  Planning, governance and rural futures in Aust...           1.0   \n",
       "4      3556  Will economic restructuring in China reduce tr...           1.0   \n",
       "5      3679  Forcing and response in simulated 20th and 21s...           1.0   \n",
       "6      3683  Egeson's (George's) transtridecadal weather cy...           0.0   \n",
       "7      4793  An emerging equilibrium in the EU emissions tr...           1.0   \n",
       "8      4929  Adapting production forests in southern Sweden...           1.0   \n",
       "9      5220  Dual climate change responsibility: on moral d...           1.0   \n",
       "10     5459  Vegetation structure and biodiversity along th...           0.0   \n",
       "11     6490  Analysing co-benefits of the energy conservati...           0.0   \n",
       "12     7165  Carbon Leakage from the Clean Development Mech...           1.0   \n",
       "13     8160                      The economics of tidal energy           1.0   \n",
       "14     8932                              Out of the energy box           1.0   \n",
       "\n",
       "    3 - 0. Not policy related  \\\n",
       "0                         1.0   \n",
       "1                         1.0   \n",
       "2                         1.0   \n",
       "3                         0.0   \n",
       "4                         0.0   \n",
       "5                         1.0   \n",
       "6                         0.0   \n",
       "7                         0.0   \n",
       "8                         0.0   \n",
       "9                         1.0   \n",
       "10                        0.0   \n",
       "11                        1.0   \n",
       "12                        0.0   \n",
       "13                        1.0   \n",
       "14                        1.0   \n",
       "\n",
       "    3 - 1. Analysis of an existing policy / concrete proposal  \\\n",
       "0                                                 0.0           \n",
       "1                                                 0.0           \n",
       "2                                                 0.0           \n",
       "3                                                 0.0           \n",
       "4                                                 2.0           \n",
       "5                                                 0.0           \n",
       "6                                                 0.0           \n",
       "7                                                 1.0           \n",
       "8                                                 0.0           \n",
       "9                                                 0.0           \n",
       "10                                                0.0           \n",
       "11                                                0.0           \n",
       "12                                                1.0           \n",
       "13                                                0.0           \n",
       "14                                                0.0           \n",
       "\n",
       "    3 - 2. Analysis of a generic policy  3 - 3. Broader governance and policy  \\\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",
       "5                                   0.0                                   0.0   \n",
       "6                                   0.0                                   0.0   \n",
       "7                                   0.0                                   0.0   \n",
       "8                                   0.0                                   0.0   \n",
       "9                                   1.0                                   0.0   \n",
       "10                                  0.0                                   0.0   \n",
       "11                                  0.0                                   0.0   \n",
       "12                                  0.0                                   0.0   \n",
       "13                                  0.0                                   0.0   \n",
       "14                                  0.0                                   0.0   \n",
       "\n",
       "    INCLUDE  \n",
       "0       0.0  \n",
       "1       0.0  \n",
       "2       0.0  \n",
       "3       0.0  \n",
       "4       1.0  \n",
       "5       0.0  \n",
       "6       0.0  \n",
       "7       1.0  \n",
       "8       0.0  \n",
       "9       1.0  \n",
       "10      0.0  \n",
       "11      0.0  \n",
       "12      1.0  \n",
       "13      0.0  \n",
       "14      0.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now label Inclusion (simply the presence of any policy type)\n",
    "include_policy_columns = [x for x in policy_columns if \"not policy related\" not in x.lower() and \"3 - 3.\" not in x.lower()]\n",
    "\n",
    "seen_df.loc[seen_df[include_policy_columns].max(axis=1).isin([1,2,np.NaN]),\"INCLUDE\"] = 1\n",
    "seen_df.loc[seen_df[include_policy_columns].max(axis=1)==0,\"INCLUDE\"] = 0\n",
    "seen_df.loc[seen_df[\"2 - 0. Mitigation\"]==0,\"INCLUDE\"] = 0\n",
    "#seen_df.loc[pd.isna(seen_df[include_policy_columns]).any(axis=1),\"INCLUDE\"] = 1\n",
    "seen_df[[\"doc__id\",\"doc__title\",\"0 - relevant\"] + policy_columns + [\"INCLUDE\"]].head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>doc__id</th>\n",
       "      <th>doc__title</th>\n",
       "      <th>0 - relevant</th>\n",
       "      <th>3 - 0. Not policy related</th>\n",
       "      <th>3 - 1. Analysis of an existing policy / concrete proposal</th>\n",
       "      <th>3 - 2. Analysis of a generic policy</th>\n",
       "      <th>3 - 3. Broader governance and policy</th>\n",
       "      <th>4 - 1. Economic instruments</th>\n",
       "      <th>4 - 2. Regulatory Instruments</th>\n",
       "      <th>4 - 3. Information, education and training</th>\n",
       "      <th>4 - 4. Governance, strategies and targets</th>\n",
       "      <th>4 - 5. Agreements</th>\n",
       "      <th>INCLUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1882</th>\n",
       "      <td>1865162</td>\n",
       "      <td>NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.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>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      doc__id                                         doc__title  \\\n",
       "1882  1865162  NEW ROLE FOR SENSORS IN ENERGY MANAGEMENT SYSTEMS   \n",
       "\n",
       "      0 - relevant  3 - 0. Not policy related  \\\n",
       "1882           1.0                        1.0   \n",
       "\n",
       "      3 - 1. Analysis of an existing policy / concrete proposal  \\\n",
       "1882                                                2.0           \n",
       "\n",
       "      3 - 2. Analysis of a generic policy  \\\n",
       "1882                                  0.0   \n",
       "\n",
       "      3 - 3. Broader governance and policy  4 - 1. Economic instruments  \\\n",
       "1882                                   0.0                          0.0   \n",
       "\n",
       "      4 - 2. Regulatory Instruments  \\\n",
       "1882                            0.0   \n",
       "\n",
       "      4 - 3. Information, education and training  \\\n",
       "1882                                         0.0   \n",
       "\n",
       "      4 - 4. Governance, strategies and targets  4 - 5. Agreements  INCLUDE  \n",
       "1882                                        2.0                2.0      1.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seen_df[seen_df['doc__id']==1865162][[\"doc__id\",\"doc__title\",\"0 - relevant\"] + policy_columns + cols_4 + [\"INCLUDE\"]].head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tag whether the document is part of a representative sample\n",
    "django.db.connection.close()\n",
    "seen_df[\"representative_sample\"] = 0\n",
    "random_tags = Tag.objects.filter(query__project=p, title__icontains=\"representative\")\n",
    "random_ids = set(DocOwnership.objects.filter(tag__in=random_tags, relevant__gt=0).values_list('doc__id',flat=True))\n",
    "seen_df.loc[seen_df['doc__id'].isin(random_ids),\"representative_sample\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "seen_df['representative_relevant'] = 1\n",
    "kw_tags = Tag.objects.filter(query__project=p, title__icontains=\"keyword\")\n",
    "kw_ids = set(DocOwnership.objects.filter(tag__in=kw_tags, relevant__gt=0).values_list('doc__id',flat=True))\n",
    "seen_df.loc[seen_df['doc__id'].isin(kw_ids),\"representative_relevant\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "fig, axs = plt.subplots(1,2)\n",
    "labels = [\"Non-representative sample\",\"Representative sample\"]\n",
    "for name, group in seen_df.groupby(['representative_sample']):\n",
    "    ax = axs[name]\n",
    "    ax.set_title(labels[name])\n",
    "    \n",
    "    group.groupby('INCLUDE').size().plot.bar(ax=ax)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "seen_df.rename(\n",
    "    columns={\"doc__id\":\"id\",\"doc__content\":\"content\",\"doc__title\":\"title\"}\n",
    ").to_csv('data/0_labelled_documents.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "rewrite_unseen = False\n",
    "\n",
    "if rewrite_unseen:\n",
    "\n",
    "    columns = [\n",
    "        'id',\n",
    "        'content',\n",
    "        'title',\n",
    "        'wosarticle__de',\n",
    "        'wosarticle__wc'\n",
    "    ]\n",
    "\n",
    "    unseen_docs = Doc.objects.filter(\n",
    "        id__in=dids,\n",
    "        content__iregex='\\w',\n",
    "    ).exclude(pk__in=seen_df['doc__id'])\n",
    "\n",
    "    unseen_df = pd.DataFrame.from_dict(list(unseen_docs.values(*columns)))\n",
    "    unseen_df = unseen_df[columns]\n",
    "    unseen_df['relevant'] = 0\n",
    "    unseen_df['seen'] = 0\n",
    "    unseen_df['random'] = 0\n",
    "\n",
    "    print(unseen_df.shape)\n",
    "    unseen_df.to_csv('data/0_unlabelled_documents.csv', index=False)\n",
    "    unseen_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tmv",
   "language": "python",
   "name": "tmv"
  },
  "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}