create_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nThis file is a temp file, which combines all the data that we have into a unified dataset.\\nImportant is to contain sent, claim, row_index, column_index, formula\\n'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "This file is a temp file, which combines all the data that we have into a unified dataset.\n",
    "Important is to contain sent, claim, row_index, column_index, formula\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/george/.pyenv/versions/3.7.0/lib/python3.7/site-packages/pandas/compat/__init__.py:85: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n",
      "/home/george/.pyenv/versions/3.7.0/lib/python3.7/site-packages/pandas/compat/__init__.py:85: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "CLAIMS_EXTRACTED_BY_US_DIR = \"data/claims_extracted_by_us/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "OUT_FILE = \"data/main_annotated_dataset_12-16-2019.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_files_from_dir(basepath):\n",
    "    files = []\n",
    "    for entry in os.listdir(basepath):\n",
    "        if os.path.isfile(os.path.join(basepath, entry)):\n",
    "            files.append(os.path.join(basepath, entry))\n",
    "    return files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_files = get_files_from_dir(CLAIMS_EXTRACTED_BY_US_DIR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/claims_extracted_by_us/Ch8_Demand_India_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch10_Part_2_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch9_Ya_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch6_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch9.4_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch2_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch9_PH_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch3_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch8.4_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch5_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch9_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch10_Valentina_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch4_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch11_Claims_ExtractedByUs.csv',\n",
       " 'data/claims_extracted_by_us/Ch8_Outlook_Claims_ExtractedByUs.csv']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_df = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data/claims_extracted_by_us/Ch8_Demand_India_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch10_Part_2_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch9_Ya_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch6_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch9.4_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch2_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch9_PH_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch3_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch8.4_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch5_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch9_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch10_Valentina_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch4_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch11_Claims_ExtractedByUs.csv\n",
      "data/claims_extracted_by_us/Ch8_Outlook_Claims_ExtractedByUs.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/george/.pyenv/versions/3.7.0/lib/python3.7/site-packages/ipykernel_launcher.py:6: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "for file in us_files:\n",
    "    print(file)\n",
    "    temp_df = pd.read_csv(file)\n",
    "    temp_df[\"file\"] = file\n",
    "    # remove unwanted rows\n",
    "    us_df = pd.concat([us_df, temp_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_df = us_df.rename(columns={\"Text\": \"sent\", \"Published value\": \"claim\", \"Fixed_Formulas\": \"formula\",\n",
    "                       \"Look-up value\": \"row_index\", \"Look-up year\": \"column\", \"Scenario\": \"scenario\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_df = us_df[[\"sent\", \"claim\", \"formula\", \"row_index\", \"column\", \"scenario\", \"file\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "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>sent</th>\n",
       "      <th>claim</th>\n",
       "      <th>formula</th>\n",
       "      <th>row_index</th>\n",
       "      <th>column</th>\n",
       "      <th>scenario</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The role of electricity expands in the New Pol...</td>\n",
       "      <td>expand</td>\n",
       "      <td>'8.1'!G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>13972</td>\n",
       "      <td>VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20000</th>\n",
       "      <td>406.82498</td>\n",
       "      <td>414.89447</td>\n",
       "      <td>415.08081</td>\n",
       "      <td>=B26287&amp;C26287</td>\n",
       "      <td>EU8785</td>\n",
       "      <td>hourly demand to satisfy[FH8758]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001</th>\n",
       "      <td>385.52603</td>\n",
       "      <td>393.20648</td>\n",
       "      <td>393.58511</td>\n",
       "      <td>=B26288&amp;C26288</td>\n",
       "      <td>EU8786</td>\n",
       "      <td>hourly demand to satisfy[FH8759]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20002</th>\n",
       "      <td>359.07626</td>\n",
       "      <td>366.4129</td>\n",
       "      <td>367.01529</td>\n",
       "      <td>=B26289&amp;C26289</td>\n",
       "      <td>EU8787</td>\n",
       "      <td>hourly demand to satisfy[FH8760]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20003</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>STDEV</td>\n",
       "      <td>VOL</td>\n",
       "      <td>Price</td>\n",
       "      <td>Time (Year)</td>\n",
       "      <td>2017</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20004</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>=STDEV(E3:E26281)</td>\n",
       "      <td>+F2^2</td>\n",
       "      <td>EU28Marginal SRMC by full load hour</td>\n",
       "      <td>Marginal SRMC by full load hour[FH1]</td>\n",
       "      <td>37.0375</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39495 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    sent              claim  \\\n",
       "0      The role of electricity expands in the New Pol...             expand   \n",
       "1      Overall energy demand rises from 13 972 millio...              13972   \n",
       "2      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "3      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "4      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "...                                                  ...                ...   \n",
       "20000                                          406.82498          414.89447   \n",
       "20001                                          385.52603          393.20648   \n",
       "20002                                          359.07626           366.4129   \n",
       "20003                                              Yelds              STDEV   \n",
       "20004                                              Yelds  =STDEV(E3:E26281)   \n",
       "\n",
       "                                                 formula  \\\n",
       "0                                               '8.1'!G4   \n",
       "1      VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "2      VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "3      VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "4      VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...   \n",
       "...                                                  ...   \n",
       "20000                                          415.08081   \n",
       "20001                                          393.58511   \n",
       "20002                                          367.01529   \n",
       "20003                                                VOL   \n",
       "20004                                              +F2^2   \n",
       "\n",
       "                                 row_index  \\\n",
       "0                                      NaN   \n",
       "1                                TPEDtotal   \n",
       "2                                TPEDtotal   \n",
       "3                                 TFCtotal   \n",
       "4                                 TFCtotal   \n",
       "...                                    ...   \n",
       "20000                       =B26287&C26287   \n",
       "20001                       =B26288&C26288   \n",
       "20002                       =B26289&C26289   \n",
       "20003                               Price    \n",
       "20004  EU28Marginal SRMC by full load hour   \n",
       "\n",
       "                                     column                          scenario  \\\n",
       "0                                       NaN                               NaN   \n",
       "1                                      2017                               NPS   \n",
       "2                                      2040                               NPS   \n",
       "3                                      2017                               NPS   \n",
       "4                                      2040                               NPS   \n",
       "...                                     ...                               ...   \n",
       "20000                                EU8785  hourly demand to satisfy[FH8758]   \n",
       "20001                                EU8786  hourly demand to satisfy[FH8759]   \n",
       "20002                                EU8787  hourly demand to satisfy[FH8760]   \n",
       "20003                           Time (Year)                              2017   \n",
       "20004  Marginal SRMC by full load hour[FH1]                           37.0375   \n",
       "\n",
       "                                                    file  \n",
       "0      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "1      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "2      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "3      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "4      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "...                                                  ...  \n",
       "20000  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20001  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20002  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20003  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20004  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "\n",
       "[39495 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# main_df = pd.concat((us_df, chapters_df))\n",
    "main_df = us_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "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>sent</th>\n",
       "      <th>claim</th>\n",
       "      <th>formula</th>\n",
       "      <th>row_index</th>\n",
       "      <th>column</th>\n",
       "      <th>scenario</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The role of electricity expands in the New Pol...</td>\n",
       "      <td>expand</td>\n",
       "      <td>'8.1'!G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>13972</td>\n",
       "      <td>VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20000</th>\n",
       "      <td>406.82498</td>\n",
       "      <td>414.89447</td>\n",
       "      <td>415.08081</td>\n",
       "      <td>=B26287&amp;C26287</td>\n",
       "      <td>EU8785</td>\n",
       "      <td>hourly demand to satisfy[FH8758]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001</th>\n",
       "      <td>385.52603</td>\n",
       "      <td>393.20648</td>\n",
       "      <td>393.58511</td>\n",
       "      <td>=B26288&amp;C26288</td>\n",
       "      <td>EU8786</td>\n",
       "      <td>hourly demand to satisfy[FH8759]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20002</th>\n",
       "      <td>359.07626</td>\n",
       "      <td>366.4129</td>\n",
       "      <td>367.01529</td>\n",
       "      <td>=B26289&amp;C26289</td>\n",
       "      <td>EU8787</td>\n",
       "      <td>hourly demand to satisfy[FH8760]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20003</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>STDEV</td>\n",
       "      <td>VOL</td>\n",
       "      <td>Price</td>\n",
       "      <td>Time (Year)</td>\n",
       "      <td>2017</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20004</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>=STDEV(E3:E26281)</td>\n",
       "      <td>+F2^2</td>\n",
       "      <td>EU28Marginal SRMC by full load hour</td>\n",
       "      <td>Marginal SRMC by full load hour[FH1]</td>\n",
       "      <td>37.0375</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39495 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    sent              claim  \\\n",
       "0      The role of electricity expands in the New Pol...             expand   \n",
       "1      Overall energy demand rises from 13 972 millio...              13972   \n",
       "2      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "3      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "4      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "...                                                  ...                ...   \n",
       "20000                                          406.82498          414.89447   \n",
       "20001                                          385.52603          393.20648   \n",
       "20002                                          359.07626           366.4129   \n",
       "20003                                              Yelds              STDEV   \n",
       "20004                                              Yelds  =STDEV(E3:E26281)   \n",
       "\n",
       "                                                 formula  \\\n",
       "0                                               '8.1'!G4   \n",
       "1      VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "2      VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "3      VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "4      VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...   \n",
       "...                                                  ...   \n",
       "20000                                          415.08081   \n",
       "20001                                          393.58511   \n",
       "20002                                          367.01529   \n",
       "20003                                                VOL   \n",
       "20004                                              +F2^2   \n",
       "\n",
       "                                 row_index  \\\n",
       "0                                      NaN   \n",
       "1                                TPEDtotal   \n",
       "2                                TPEDtotal   \n",
       "3                                 TFCtotal   \n",
       "4                                 TFCtotal   \n",
       "...                                    ...   \n",
       "20000                       =B26287&C26287   \n",
       "20001                       =B26288&C26288   \n",
       "20002                       =B26289&C26289   \n",
       "20003                               Price    \n",
       "20004  EU28Marginal SRMC by full load hour   \n",
       "\n",
       "                                     column                          scenario  \\\n",
       "0                                       NaN                               NaN   \n",
       "1                                      2017                               NPS   \n",
       "2                                      2040                               NPS   \n",
       "3                                      2017                               NPS   \n",
       "4                                      2040                               NPS   \n",
       "...                                     ...                               ...   \n",
       "20000                                EU8785  hourly demand to satisfy[FH8758]   \n",
       "20001                                EU8786  hourly demand to satisfy[FH8759]   \n",
       "20002                                EU8787  hourly demand to satisfy[FH8760]   \n",
       "20003                           Time (Year)                              2017   \n",
       "20004  Marginal SRMC by full load hour[FH1]                           37.0375   \n",
       "\n",
       "                                                    file  \n",
       "0      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "1      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "2      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "3      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "4      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "...                                                  ...  \n",
       "20000  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20001  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20002  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20003  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "20004  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "\n",
       "[39495 rows x 7 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31478"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(main_df.drop_duplicates(subset=[\"sent\", \"claim\", \"formula\", \"row_index\", \"column\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df = main_df.drop_duplicates(subset=[\"sent\", \"claim\", \"formula\", \"row_index\", \"column\"]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "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>sent</th>\n",
       "      <th>claim</th>\n",
       "      <th>formula</th>\n",
       "      <th>row_index</th>\n",
       "      <th>column</th>\n",
       "      <th>scenario</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The role of electricity expands in the New Pol...</td>\n",
       "      <td>expand</td>\n",
       "      <td>'8.1'!G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>13972</td>\n",
       "      <td>VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TPEDtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2017</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Overall energy demand rises from 13 972 millio...</td>\n",
       "      <td>over 17 700</td>\n",
       "      <td>VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...</td>\n",
       "      <td>TFCtotal</td>\n",
       "      <td>2040</td>\n",
       "      <td>NPS</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Demand_India_C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31473</th>\n",
       "      <td>406.82498</td>\n",
       "      <td>414.89447</td>\n",
       "      <td>415.08081</td>\n",
       "      <td>=B26287&amp;C26287</td>\n",
       "      <td>EU8785</td>\n",
       "      <td>hourly demand to satisfy[FH8758]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31474</th>\n",
       "      <td>385.52603</td>\n",
       "      <td>393.20648</td>\n",
       "      <td>393.58511</td>\n",
       "      <td>=B26288&amp;C26288</td>\n",
       "      <td>EU8786</td>\n",
       "      <td>hourly demand to satisfy[FH8759]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31475</th>\n",
       "      <td>359.07626</td>\n",
       "      <td>366.4129</td>\n",
       "      <td>367.01529</td>\n",
       "      <td>=B26289&amp;C26289</td>\n",
       "      <td>EU8787</td>\n",
       "      <td>hourly demand to satisfy[FH8760]</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31476</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>STDEV</td>\n",
       "      <td>VOL</td>\n",
       "      <td>Price</td>\n",
       "      <td>Time (Year)</td>\n",
       "      <td>2017</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31477</th>\n",
       "      <td>Yelds</td>\n",
       "      <td>=STDEV(E3:E26281)</td>\n",
       "      <td>+F2^2</td>\n",
       "      <td>EU28Marginal SRMC by full load hour</td>\n",
       "      <td>Marginal SRMC by full load hour[FH1]</td>\n",
       "      <td>37.0375</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>31478 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    sent              claim  \\\n",
       "0      The role of electricity expands in the New Pol...             expand   \n",
       "1      Overall energy demand rises from 13 972 millio...              13972   \n",
       "2      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "3      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "4      Overall energy demand rises from 13 972 millio...        over 17 700   \n",
       "...                                                  ...                ...   \n",
       "31473                                          406.82498          414.89447   \n",
       "31474                                          385.52603          393.20648   \n",
       "31475                                          359.07626           366.4129   \n",
       "31476                                              Yelds              STDEV   \n",
       "31477                                              Yelds  =STDEV(E3:E26281)   \n",
       "\n",
       "                                                 formula  \\\n",
       "0                                               '8.1'!G4   \n",
       "1      VLOOKUP($A6,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "2      VLOOKUP($A7,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "3      VLOOKUP($A9,C:\\EO2018\\FrozenResults\\Demand\\_Ag...   \n",
       "4      VLOOKUP($A10,C:\\EO2018\\FrozenResults\\Demand\\_A...   \n",
       "...                                                  ...   \n",
       "31473                                          415.08081   \n",
       "31474                                          393.58511   \n",
       "31475                                          367.01529   \n",
       "31476                                                VOL   \n",
       "31477                                              +F2^2   \n",
       "\n",
       "                                 row_index  \\\n",
       "0                                      NaN   \n",
       "1                                TPEDtotal   \n",
       "2                                TPEDtotal   \n",
       "3                                 TFCtotal   \n",
       "4                                 TFCtotal   \n",
       "...                                    ...   \n",
       "31473                       =B26287&C26287   \n",
       "31474                       =B26288&C26288   \n",
       "31475                       =B26289&C26289   \n",
       "31476                               Price    \n",
       "31477  EU28Marginal SRMC by full load hour   \n",
       "\n",
       "                                     column                          scenario  \\\n",
       "0                                       NaN                               NaN   \n",
       "1                                      2017                               NPS   \n",
       "2                                      2040                               NPS   \n",
       "3                                      2017                               NPS   \n",
       "4                                      2040                               NPS   \n",
       "...                                     ...                               ...   \n",
       "31473                                EU8785  hourly demand to satisfy[FH8758]   \n",
       "31474                                EU8786  hourly demand to satisfy[FH8759]   \n",
       "31475                                EU8787  hourly demand to satisfy[FH8760]   \n",
       "31476                           Time (Year)                              2017   \n",
       "31477  Marginal SRMC by full load hour[FH1]                           37.0375   \n",
       "\n",
       "                                                    file  \n",
       "0      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "1      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "2      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "3      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "4      data/claims_extracted_by_us/Ch8_Demand_India_C...  \n",
       "...                                                  ...  \n",
       "31473  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "31474  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "31475  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "31476  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "31477  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "\n",
       "[31478 rows x 7 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df.to_csv(OUT_FILE, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12904"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(main_df.drop_duplicates(subset = [\"sent\", \"claim\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "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>sent</th>\n",
       "      <th>claim</th>\n",
       "      <th>formula</th>\n",
       "      <th>row_index</th>\n",
       "      <th>column</th>\n",
       "      <th>scenario</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1748</th>\n",
       "      <td>Spending on energy imports is set to grow in m...</td>\n",
       "      <td>grow</td>\n",
       "      <td>AVERAGEIF(D173:G186,\"import\",G173:G186)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch10_Part_2_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1876</th>\n",
       "      <td>In the Sustainable Development Scenario, incre...</td>\n",
       "      <td>higher</td>\n",
       "      <td>COUNTIF(K348:AJ348,\"\"&amp;\"TRUE\")21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch10_Part_2_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2196</th>\n",
       "      <td>Despite declining costs in all regions, solar ...</td>\n",
       "      <td>declining</td>\n",
       "      <td>COUNTIF(E366:E390,\"TRUE\")</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch10_Part_2_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2202</th>\n",
       "      <td>Projected investment in wind power continues t...</td>\n",
       "      <td>increase</td>\n",
       "      <td>COUNTIF(E476:E500,\"TRUE\")</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch10_Part_2_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2639</th>\n",
       "      <td>In the United States, the share of total gener...</td>\n",
       "      <td>low</td>\n",
       "      <td>IF(G184&lt;G185,TRUE,FALSE)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch10_Part_2_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15721</th>\n",
       "      <td>Most advanced and developing economies alike e...</td>\n",
       "      <td>SO2_PP_Total</td>\n",
       "      <td>COUNTIF(RegionalOutput_pollutants!$F$4:$F$45,T...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15722</th>\n",
       "      <td>Most advanced and developing economies alike e...</td>\n",
       "      <td>NOX_PP_Total</td>\n",
       "      <td>COUNTIF(RegionalOutput_pollutants!$K$4:$K$45,T...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15723</th>\n",
       "      <td>Most advanced and developing economies alike e...</td>\n",
       "      <td>PM_PP_Total</td>\n",
       "      <td>COUNTIF(RegionalOutput_pollutants!$P$4:$P$45,T...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16135</th>\n",
       "      <td>The policies adopted to support the climate ob...</td>\n",
       "      <td>high levels</td>\n",
       "      <td>COUNTIF(F507:F512,\"&lt;30%\")0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16142</th>\n",
       "      <td>Large additions in wind power electricity gene...</td>\n",
       "      <td>Large additions</td>\n",
       "      <td>COUNTIF(F525:F527,\"&lt;50%\")0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>data/claims_extracted_by_us/Ch8_Outlook_Claims...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>180 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    sent            claim  \\\n",
       "1748   Spending on energy imports is set to grow in m...             grow   \n",
       "1876   In the Sustainable Development Scenario, incre...           higher   \n",
       "2196   Despite declining costs in all regions, solar ...        declining   \n",
       "2202   Projected investment in wind power continues t...         increase   \n",
       "2639   In the United States, the share of total gener...              low   \n",
       "...                                                  ...              ...   \n",
       "15721  Most advanced and developing economies alike e...     SO2_PP_Total   \n",
       "15722  Most advanced and developing economies alike e...     NOX_PP_Total   \n",
       "15723  Most advanced and developing economies alike e...      PM_PP_Total   \n",
       "16135  The policies adopted to support the climate ob...      high levels   \n",
       "16142  Large additions in wind power electricity gene...  Large additions   \n",
       "\n",
       "                                                 formula row_index column  \\\n",
       "1748             AVERAGEIF(D173:G186,\"import\",G173:G186)       NaN    NaN   \n",
       "1876                     COUNTIF(K348:AJ348,\"\"&\"TRUE\")21       NaN    NaN   \n",
       "2196                           COUNTIF(E366:E390,\"TRUE\")       NaN    NaN   \n",
       "2202                           COUNTIF(E476:E500,\"TRUE\")       NaN    NaN   \n",
       "2639                            IF(G184<G185,TRUE,FALSE)       NaN    NaN   \n",
       "...                                                  ...       ...    ...   \n",
       "15721  COUNTIF(RegionalOutput_pollutants!$F$4:$F$45,T...       NaN    NaN   \n",
       "15722  COUNTIF(RegionalOutput_pollutants!$K$4:$K$45,T...       NaN    NaN   \n",
       "15723  COUNTIF(RegionalOutput_pollutants!$P$4:$P$45,T...       NaN    NaN   \n",
       "16135                         COUNTIF(F507:F512,\"<30%\")0       NaN    NaN   \n",
       "16142                         COUNTIF(F525:F527,\"<50%\")0       NaN    NaN   \n",
       "\n",
       "      scenario                                               file  \n",
       "1748       NaN  data/claims_extracted_by_us/Ch10_Part_2_Claims...  \n",
       "1876       NaN  data/claims_extracted_by_us/Ch10_Part_2_Claims...  \n",
       "2196       NaN  data/claims_extracted_by_us/Ch10_Part_2_Claims...  \n",
       "2202       NaN  data/claims_extracted_by_us/Ch10_Part_2_Claims...  \n",
       "2639       NaN  data/claims_extracted_by_us/Ch10_Part_2_Claims...  \n",
       "...        ...                                                ...  \n",
       "15721      NaN  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "15722      NaN  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "15723      NaN  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "16135      NaN  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "16142      NaN  data/claims_extracted_by_us/Ch8_Outlook_Claims...  \n",
       "\n",
       "[180 rows x 7 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_df[main_df.formula.str.contains(\"IF\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "179"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(main_df[main_df.formula.str.contains(\"IF\")].drop_duplicates(subset=\"formula\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}