{"cells":[{"metadata":{},"cell_type":"markdown","source":"## References\n* [Automate Entity Extraction of Reddit Subgroup using BERT Model | by Manmohan Singh | Towards Data Science](https://towardsdatascience.com/automate-entity-extraction-of-reddit-subgroup-using-bert-model-336f9edb176e)\n* [langdetect · PyPI](https://pypi.org/project/langdetect/)\n* [TextBlob: Simplified Text Processing — TextBlob 0.16.0 documentation](https://textblob.readthedocs.io/en/dev/)\n* [Transformers — transformers 4.0.0 documentation](https://huggingface.co/transformers/)\n* [How to Compute Sentence Similarity Using BERT and Word2Vec | by Pedram Ataee, PhD | Oct, 2020 | Towards Data Science](https://towardsdatascience.com/how-to-compute-sentence-similarity-using-bert-and-word2vec-ab0663a5d64)\n"},{"metadata":{},"cell_type":"markdown","source":"\n## Methodology\nThis Notebook is the fruit of the hard work of team: [Ausberto Escorcia](ausbertoescorcia@think-it.io), [Ghada Louil](ghada@think-it.io) and [Mustapha Sahli](mustaphasahli@think-it.io).\n\nWe collaborated as a team to solve [the CDP - Unlocking climate solutions challenge on Kaggle](https://www.kaggle.com/c/cdp-unlocking-climate-solutions) on a race speed during the last month or so. \n\nWhat is particularly interesting and challenging in this Kaggle competition is that the output is not concisely defined. As a team, we collaborated first of all, on defining the deliverables of the competition, which are `the methodology` and the resulting `KPIs`. \n\nAs a first step, we had to define the KPIs, so then we have a clear understanding of how to proceed to calculate them and generally to describe the methodology. For that, we refered to many resources mentioned accross this notebook. We have as well noticed, that once defined, and with the large amount of data, our efforts would be disparsed, so we aimed at looking only to one important domain, `Water` (combined with `Water security`), and focus on delivering the KPIs for this domain. Once that is done, we have a methodology that is applyable on the other domains and that delivers the rest of the KPIs.\n\nOur approach is decribed by the following figure."},{"metadata":{"trusted":true},"cell_type":"code","source":"%pylab inline\nimport matplotlib.pyplot as plt\nimport matplotlib.image as mpimg\nplt.figure(figsize=(20,10))\nimg = mpimg.imread('/kaggle/input/cdp-kpis/Water KPIs Tree.png')\nimgplot = plt.imshow(img)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As the figure shows, our methodology is quite simple and should be efficient. We intend to combine our knowledge of NLP with CDP data to extract the KPIs stated. \n\nBUT, before we dig into details of the KPIs we wanted to take a quick look at the data and introduce some of the state-of-the-art models in the NLP world that we would like to use."},{"metadata":{},"cell_type":"markdown","source":"## Dependencies"},{"metadata":{"trusted":true},"cell_type":"code","source":"!pip install langdetect > /dev/null\n!pip install --upgrade pip > /dev/null  # updating the package-management system pip\n!pip install PyDictionary > /dev/null   # installing PyDictionary (https://pypi.org/project/PyDictionary/)\n!pip install sent2vec > /dev/null","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Imports"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nimport warnings; warnings.filterwarnings(\"ignore\")  # to ignore \"wandb\"'s warning\n\nfrom sent2vec.vectorizer import Vectorizer  # used to compute the embedding vectors\nfrom scipy import spatial                   # used to compute the distance between these embedding vectors\nfrom PyDictionary import PyDictionary\nfrom collections import Counter\nfrom pprint import pprint          # pretty prints \nfrom langdetect import detect      # a Language detection library ported from Google's language-detection.\nfrom textblob import TextBlob      # a library for processing textual data\nfrom glob import glob              # usef for filename pattern matching\nfrom transformers import pipeline  # state-of-the-art natural language processing","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Globals"},{"metadata":{"trusted":true},"cell_type":"code","source":"CDP_PATH = \"/kaggle/input/cdp-unlocking-climate-solutions/\"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# import BERT question-answering pipeline\nquestion_answerer = pipeline('question-answering')\n\n# import the BERT summarization pipeline\nsummary = pipeline('summarization')\n\n# import the BERT named entity recognition pipeline\nner = pipeline('ner')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Helper functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"def load_data(filepath):\n    \"\"\"Reads in the CSV datasets (of the given file path) as Dataframes then return their concatenation\"\"\"\n    files = glob(filepath+\"*.csv\")\n    data = pd.DataFrame()\n    for file in files:\n        if \"Data_Dictionary\" not in file:\n            df = pd.read_csv(file)\n            data = pd.concat([data, df])\n    data.reset_index(drop=True, inplace=True)\n    return data\n\ndef get_csv_data(path, folder, subfolder=None):\n    \"\"\"A helper function providing an easier way to access the different dataset files\"\"\"\n    if path.lower() == \"cities\":\n        if folder.lower() == \"disclosing\":\n            data_path = \"Cities/Cities Disclosing/\"\n        elif folder.lower() == \"responses\":\n            data_path = \"Cities/Cities Responses/\"\n        else:\n            data_path = \"Cities/Cities Questionnaires/\"\n        \n    elif path.lower() == \"corporations\":\n        if not subfolder:\n            print(\"Subfolder needs to be specified\")\n            return None\n        if folder.lower() == \"disclosing\":\n            if subfolder.lower() == \"climate change\":\n                data_path = \"Corporations/Corporations Disclosing/Climate Change/\"\n            elif subfolder.lower() == \"water security\":\n                data_path = \"Corporations/Corporations Disclosing/Water Security/\"\n            else:\n                print(\"No such folder\")\n                return None\n        elif folder.lower() == \"responses\":\n            if subfolder.lower() == \"climate change\":\n                data_path = \"Corporations/Corporations Responses/Climate Change/\"\n            elif subfolder.lower() == \"water security\":\n                data_path = \"Corporations/Corporations Responses/Water Security/\"\n            else:\n                print(\"No such folder\")\n                return None\n        else:\n            data_path = \"Cities/Cities Questionnaires/\"\n            \n    else:\n        print(\"open the file directly\")\n        return None\n    \n    try:\n        return load_data(CDP_PATH + data_path)\n    except:\n        print(\"No such file in directory\")\n        return None\n    \ndef translate(text):\n    \"\"\"Translates the given text to English\"\"\"\n    if detect(text) == \"en\":\n        return text\n    blob = TextBlob(text)\n    translated = blob.translate(to='en')\n    return str(translated)\n\ndef get_questions(module_id):\n    \"\"\"Given the module ID, returns the questions belonging to that module\"\"\"\n    res = list()\n    for ques_id in list(questions['2019 Question number'].unique()):\n        if module_id == ques_id.split('.')[0]:\n            res.append(ques_id)\n    return res\n\ndef get_city_name(account_number):\n    \"\"\"Given an account number, returns the city name\"\"\"\n    df = cities_disc[cities_disc['Account Number'] == account_number]\n    values = list(df['City'].unique())\n    if len(values) > 1 or values == [\"\"]:\n        values = cities_resp[cities_resp['Account Number'] == account_number]['Organization'].values\n        translated_org_name = translate(values[0])\n        return translated_org_name\n    else:\n        return values[0]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Pipeline"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time \n\n# loading data\ncities_resp = get_csv_data(\"cities\", \"responses\")\ncities_disc = get_csv_data(\"cities\", \"disclosing\")\ncorpos_resp = get_csv_data(\"corporations\", \"responses\", \"water security\")\ncorpos_disc = get_csv_data(\"corporations\", \"disclosing\", \"water security\")\nprint(cities_resp.shape, cities_disc.shape, corpos_resp.shape, corpos_disc.shape)\n\n# replace NA/NaN values with an empty string from cities and corporations dataframes\ncities_resp.fillna(\"\", inplace = True)\ncities_disc.fillna(\"\", inplace = True)\ncorpos_resp.fillna(\"\", inplace = True)\ncorpos_disc.fillna(\"\", inplace = True)\n\n# read and process questions\nquestions = pd.read_excel(CDP_PATH + \"Supplementary Data/Recommendations from CDP/CDP_recommendations_for_questions_to_focus_on.xlsx\")\nquestions.drop(columns=[\"Unnamed: 4\", \"Unnamed: 5\", \"Unnamed: 6\", \"Unnamed: 7\"], axis=1, inplace= True)  # drop unamed columns\nquestions.columns=questions.iloc[3]                               # setting the dataframe's column names\nquestions.drop(index=[0,1,2,3], axis=0, inplace=True)             # remove unwanted rows\nquestions.reset_index(drop=True, inplace=True)                    # resets the index to the default integer index\nquestions.iloc[0].index.name = \"index\"                            # setting the \"indexes\" column's name\nquestions.fillna(\"\", inplace=True)                                # replace NA/NaN values with an empty string\nquestions = questions.apply(lambda x: x.astype(str).str.lower())  # set the questions to lowercase\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.options.display.max_rows = 200  # sets the maximum number of rows displayed when a frame is printed\nquestions","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"q = questions['2019 Question number'].iloc[0]              # select the first row (containing the first question)\nprint('Question', q, sep='\\t')\ndf = cities_resp[cities_resp[\"Question Number\"] == q[1:]]  # select the rows related to that question (the first character, referring to the module, is omitted)\nprint('Dataframe Shape', df.shape, sep='\\t')\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"answers = list(df[\"Response Answer\"].unique())  # get a list of unique answers to that question\nprint(len(answers), 'Answers Found')\npprint(answers)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"languages = []\nfor answer in answers:\n    try:\n        languages.append(detect(answer))\n    except:\n        print(answer)\n        continue\ncounter = Counter(languages)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"labels = list(counter.keys())\nlang_counter = list(counter.values())\n\nexplode = [0,0.1,0,0,0,0,0,0,0,0,0,0]\nfig = plt.figure(figsize =(10,8))\nplt.title('Distribution of languages across water response data')\nplt.pie(lang_counter, explode=explode, startangle=90) \nplt.legend(labels = labels,loc=[1.1, 0.5])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Translation"},{"metadata":{},"cell_type":"markdown","source":"One of our interesting criteria in our approach is that we take into consideration all languages; If the response is not written in English, we start by translating them then pass them through the same pipeline for the answers originally written in English."},{"metadata":{"trusted":true},"cell_type":"code","source":"a = \"La ciudad de Barrancabermeja es el mayor centro urbano de la Región del Magdalena Medio,  Ubicado en el departamento de Santander en Colombia, es la Capital de la Provincia de Mares, Localizada a 120 Km de distancia de la Capital del Departamento Bucaramanga, se encuentra en la margen derecha del Rio Magdalena conocida como la Capital Petrolera de Colombia, porque desde sus inicios ésta actividad contribuyó al desarrollo de lo que hoy es la ciudad. Con la llegada del petróleo a este territorio se generó una fuerte migración de pobladores de diferentes zonas del país, y junto con la explotación del petróleo originada por la concesión de Mares, llamada así por Roberto de Mares, quien dirigió las primeras actividades de explotación, la ciudad empezó a generar un proceso de crecimiento y expansión urbanística hasta el punto que en 1.922 se dio la ordenanza para convertir al caserío en un municipio, y en ese mismo año entró en funcionamiento la refinería, la cual era administrada por la Tropical Oil Company, hoy en día ExxonMobil de Colombia S.A.Gracias al desarrollo de la industria petrolera, Barrancabermeja se ha convertido en un fuerte complejo empresarial para el departamento de Santander donde diferentes sectores económicos tales como la construcción y los servicios, registraron un crecimiento de sus actividades económicas en los últimos años, contribuyendo así en la generación de oportunidades de empleo para los habitantesy el crecimiento urbano de la misma ciudad.\"\ntranslated = translate(a)\nprint('Original Language', detect(a), sep='\\t')\nprint('English Translation', translated, sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Text Summarization"},{"metadata":{},"cell_type":"markdown","source":"One more idea is to summarize the long answers and assess the speed/accuracy tradeoff introduced with this decision."},{"metadata":{"trusted":true},"cell_type":"code","source":"s = summary(translated)[0]['summary_text']\ns","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Question Answering"},{"metadata":{},"cell_type":"markdown","source":"Once translated, we can use question-answering algorithms to extract the particular information we need from the whole answer."},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"what is the name of the city?\", 'context': s})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Methodology\n\n*Idea*\nAs we explained before, the KPIs were not concisely defined and the methodology aims at calculating them. We first tried to explore resources and came up with a bunch of KPIs for water security, climate change, cities governance and all others stated in our data. Our method consists of processing the response data and on extracting meaningful information from it, so then we have clear understanding about the existance of a particular KPI in a specific location. \n\nHere, we like to state three remarks:\n1. We worked on water and water security KPIs only and therefore, we extracted the cities responses data for both sections and made the rest of the processing apply on them\n2. We also only considered water KPIs including water security as helpful resources to build the methodology\n3. We will work on a city level, and prioritize cities that have a focus on water issues and solutions\n\nKindly find below the considered KPIs for water and water security, kudos to Ausberto Escorcia:\n* safe and affordable\n* end open defecation and provide access to sanitation and hygiene\n* improve water quality, wastewater treatment and safe reuse\n* increase water use efficiency and ensure freshwater supplies\n* implement integrated water resources management\n* protect and restore water-related ecosystems\n* expand water and sanitation support to developing countries\n* support local engagement in water and sanitation management\n\nIn the following section, we detail the steps of our methodology along with some testing of methods and models for text processing and feature extraction.\n\nWe used the state-of-the-art model BERT tokenizer and pipelines for question-answering, summarization and named-entity-recognition.\n\nPs. Some of the tests on the data failed as more preprocessing was needed or simply for inadequacy, and therefore they are deleted from this notebook and may re-occur in future versions."},{"metadata":{},"cell_type":"markdown","source":"### In-depth data exploration"},{"metadata":{"trusted":true},"cell_type":"code","source":"# water security questions\nwater_quests_ids = get_questions(\"c14\")\nwater_questions = [list(questions[questions['2019 Question number'] == q]['Question text'].values)[0] for q in water_quests_ids]\nwater_questions","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"water_security_df = cities_resp[cities_resp[\"Parent Section\"] == \"Water Security\"]  # get all the questions related to \"Water Security\"\nprint('Dataframe Shape', water_security_df.shape, sep='\\t')\nwater_security_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's consider one city, then identify water issues, city governance, KPIs accomplished.  \nIn the same country, we may find different answers for the same question, so, it would be better if we focus on one country at a time.\nWe randomly selected `Canada` as a large country that contains multiple cities and a fair amount of data we can work on."},{"metadata":{"trusted":true},"cell_type":"code","source":"\ncanada_resp = water_security_df[water_security_df[\"Country\"] == \"Canada\"]\nprint(canada_resp.shape)\ncanada_resp.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"get_city_name(848408)  # find out to what city belongs this organization","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Another idea we had is to apply [NER (Named Entity Recognition)](https://en.wikipedia.org/wiki/Named-entity_recognition) on an answer to extract other pieces of information."},{"metadata":{"trusted":true},"cell_type":"code","source":"text = 'Municipality of Cajamarca'\nwords = text.split()\nfor word in words:\n    print(ner(word))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Next Steps\nThis is only a first iteration, in which we relied on the power of the algorithms we used (i.e. Their resilience against raw data); We can improve the results of our approach by focussing more on the data. We noticed that it would be better if some pieces of information were put together not in different datasets."},{"metadata":{},"cell_type":"markdown","source":"### Using Synonyms"},{"metadata":{},"cell_type":"markdown","source":"Another idea we can try is to search, at a first level, for a list of keywords, and on a second level, for their synonyms. Example: clean water --> potable, affordable -> cheap, ... this way, we'll get the information we needed even if it wasn't written as we expected. As we all know, if you give the same text to different translators, you won't get exactly the same result, and this idea will solve the inherited behavior of natural languages. As follows an example (using the [PyDictionary](https://pypi.org/project/PyDictionary/) library) of applying this method."},{"metadata":{"trusted":true},"cell_type":"code","source":"dictionary = PyDictionary()\nkeywords = ['clean', 'affordable', 'accessible']\nfor keyword in keywords:\n    print (f'{keyword:10}', ', '.join(dictionary.synonym(keyword)[:5]) + ', ...', sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Sentence Similarity"},{"metadata":{},"cell_type":"markdown","source":"Another idea is to check whether an answer can be related to more than one KPI. To find out if this is the case, we need to compute the similarity of that answer with the keywords related to that KPI. As follows, an example of applying sentence similarity in this use case."},{"metadata":{"trusted":true},"cell_type":"code","source":"question = 'Rate the importance (current and future) of water quality and water quantity to the success of your business.'\nanswer = 'We use water for drinking, sanitary purposes and some industrial processes at our plants. We strive to create a positive impact on our environment, by providing products and services that enable our customers to use less water. For example, we produce a thermosyphon cooler hybrid system, which can create substantial savings in the water used by power plants. Our district energy solutions that include both equipment and controls, such as that deployed at Stanford University, have the benefit of reducing both energy and water use. We also seek to continuously improve in our water management in our operations. Our facility siting and facility acquisitions undergo a due diligence process that we believe helps avoid situations where we would face significant water risks. Given our business changes, we are in the process of conducting additional analysis on our water use.'\nkeywords = [ 'Water Security', 'Climate Change' ]\nsentences = [ answer ] + keywords","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('question', question, sep='\\t')\nprint('answer', answer, sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# string --> vector\nvectorizer = Vectorizer()\nvectorizer.bert(sentences)\nvectors_bert = vectorizer.vectors\nvectors_bert","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"answer_vector = vectors_bert[0]\nws_keyword_vector, cc_keyword_vector = vectors_bert[1], vectors_bert[2]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"answer_ws_distance = spatial.distance.cosine(answer_vector, ws_keyword_vector)\nanswer_cc_distance = spatial.distance.cosine(answer_vector, cc_keyword_vector)\nprint('answer_ws_distance', answer_ws_distance, sep='\\t')\nprint('answer_cc_distance', answer_cc_distance, sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# helper function\ndef semantic_distance(string_a, string_b):\n    \"\"\"Computes the semantic distance between the given strings\"\"\"\n    sentences = [ string_a, string_b ]\n    vectorizer = Vectorizer()\n    vectorizer.bert(sentences)\n    vectors_bert = vectorizer.vectors\n    a_vector, b_vector = vectors_bert\n    return spatial.distance.cosine(a_vector, b_vector)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"answer = '''\nWarming temperatures and increased run off in a changing climate could lead to algal blooms, higher levels of bacterial activity, and a potential increase in the current very low levels of naturally-occurring disease-causing organisms (such as Giardia) in water supply reservoirs.   Algal blooms can cause taste and odour issues and interfere with disinfection.  The low levels of bacterial activity and disease-causing organisms in the reservoirs at present are able to be deactivated by existing water disinfection processes – ultraviolet light, chlorine, and ammonia.  It is unlikely that these organisms would increase beyond the capability of the disinfection system.  The nutrient poor status and large volume of water in supply reservoirs will greatly buffer any effects of climate change.  There is regular testing and monitoring in place to ensure a safe drinking water supply and detect any changes that would require an adjustment to processes.'\n'''","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A practical example is to compute the distance between the answer against one keyword and all of its synonyms.  \nThe distance with the concept is the minimum distance found."},{"metadata":{"trusted":true},"cell_type":"code","source":"for keyword in ['clean'] + dictionary.synonym('clean'):\n    distance = semantic_distance(answer, keyword)\n    print(keyword, distance, sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def answer_concept_distance(answer, concept, threshold=0.5):\n    assert 0 <= threshold <= 1, \"The threshold should be between 0 and 1!\"\n    distance_with_concept = float('inf')\n    for keyword in [concept] + dictionary.synonym(concept):\n        distance = semantic_distance(answer, keyword)\n        distance_with_concept = min(distance_with_concept, distance)\n    return { 'distance_with_concept': distance_with_concept, 'is_relevant': distance_with_concept < threshold }","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"answers = [\n    'City water supply is secure',\n     'The City of Toronto has a Source Protection Plan that contains a series of policies that, when implemented, will protect its drinking water sources from current and future threats. None of the threats identified in the Plan are considered to be substantive.  More information is available at http://www.ctcswp.ca/ctc-source-protection-plan/',\n     'Other: Water supply is secure but being monitored and managed',\n     \"The City of Toronto is part of a larger watershed under the umbrella of the Credit Valley-Toronto and Region-Central Lake Ontario (CTC) Source Protection Plan.  This source protection plan  contains a series of policies intended to protect the watershed's drinking water sources from current and future threats.  See also   https://ctcswp.ca/protecting-our-water/the-ctc-source-protection-plan/\",\n     'Other, please specify: Water supply is secure but being monitored and managed'\n]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nfor idx, answer in enumerate(answers):\n    print(idx, answer_concept_distance(answer, 'clean'), sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see above, the algorithm loops over answers to detect if any of them includes a hint of a `clear water` (or any paraphrase of it) which is directly associated with the KPI `Water safety`. \n\nThe result printed is a list of the distances between couples (answers, \"clean\"). The closer the distance value to 0, the closer the meaning is between the two sentences. We defined a relevance value based on a 50% threshold, that states whether the closness is relevant or not."},{"metadata":{},"cell_type":"markdown","source":"## Intention Recognition"},{"metadata":{"trusted":true},"cell_type":"code","source":"sa = pipeline(\"sentiment-analysis\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for answer in answers:\n    print(\"\\n\", answer, sa(answer))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# # !python -m deeppavlov install tfidf_logreg_en_faq\n# # !python -m deeppavlov interact tfidf_logreg_en_faq -d\n# !pip install deeppavlov","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# %load https://raw.githubusercontent.com/deepmipt/DeepPavlov/master/deeppavlov/configs/faq/tfidf_logreg_en_faq.json\n'''\n{\n  \"dataset_reader\": {\n    \"class_name\": \"faq_reader\",\n    \"x_col_name\": \"Question\",\n    \"y_col_name\": \"Answer\",\n    \"data_url\": \"http://files.deeppavlov.ai/faq/school/faq_school_en.csv\"\n  },\n  \"dataset_iterator\": {\n    \"class_name\": \"data_learning_iterator\"\n  },\n  \"chainer\": {\n    \"in\": \"q\",\n    \"in_y\": \"y\",\n    \"pipe\": [\n      {\n        \"class_name\": \"stream_spacy_tokenizer\",\n        \"in\": \"q\",\n        \"id\": \"my_tokenizer\",\n        \"lemmas\": true,\n        \"out\": \"q_token_lemmas\"\n      },\n      {\n        \"ref\": \"my_tokenizer\",\n        \"in\": \"q_token_lemmas\",\n        \"out\": \"q_lem\"\n      },\n      {\n        \"in\": [\n          \"q_lem\"\n        ],\n        \"out\": [\n          \"q_vect\"\n        ],\n        \"fit_on\": [\n          \"q_lem\"\n        ],\n        \"id\": \"tfidf_vec\",\n        \"class_name\": \"sklearn_component\",\n        \"save_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/tfidf.pkl\",\n        \"load_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/tfidf.pkl\",\n        \"model_class\": \"sklearn.feature_extraction.text:TfidfVectorizer\",\n        \"infer_method\": \"transform\"\n      },\n      {\n        \"id\": \"answers_vocab\",\n        \"class_name\": \"simple_vocab\",\n        \"fit_on\": [\n          \"y\"\n        ],\n        \"save_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/en_mipt_answers.dict\",\n        \"load_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/en_mipt_answers.dict\",\n        \"in\": \"y\",\n        \"out\": \"y_ids\"\n      },\n      {\n        \"in\": \"q_vect\",\n        \"fit_on\": [\n          \"q_vect\",\n          \"y_ids\"\n        ],\n        \"out\": [\n          \"y_pred_proba\"\n        ],\n        \"class_name\": \"sklearn_component\",\n        \"main\": true,\n        \"save_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/logreg.pkl\",\n        \"load_path\": \"{MODELS_PATH}/faq/mipt/en_mipt_faq_v4/logreg.pkl\",\n        \"model_class\": \"sklearn.linear_model:LogisticRegression\",\n        \"infer_method\": \"predict_proba\",\n        \"C\": 1000,\n        \"penalty\": \"l2\"\n      },\n      {\n        \"in\": \"y_pred_proba\",\n        \"out\": \"y_pred_ids\",\n        \"class_name\": \"proba2labels\",\n        \"max_proba\": true\n      },\n      {\n        \"in\": \"y_pred_ids\",\n        \"out\": \"y_pred_answers\",\n        \"ref\": \"answers_vocab\"\n      }\n    ],\n    \"out\": [\n      \"y_pred_answers\",\n      \"y_pred_proba\"\n    ]\n  },\n  \"train\": {\n    \"evaluation_targets\": [],\n    \"class_name\": \"fit_trainer\"\n  },\n  \"metadata\": {\n    \"variables\": {\n      \"ROOT_PATH\": \"~/.deeppavlov\",\n      \"DOWNLOADS_PATH\": \"{ROOT_PATH}/downloads\",\n      \"MODELS_PATH\": \"{ROOT_PATH}/models\"\n    },\n    \"requirements\": [\n      \"{DEEPPAVLOV_PATH}/requirements/spacy.txt\",\n      \"{DEEPPAVLOV_PATH}/requirements/en_core_web_sm.txt\"\n    ],\n    \"download\": [\n      {\n        \"url\": \"http://files.deeppavlov.ai/faq/mipt/en_mipt_faq_v4.tar.gz\",\n        \"subdir\": \"{MODELS_PATH}/faq/mipt\"\n      }\n    ]\n  }\n}\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# !python -m deeppavlov install tfidf_logreg_en_faq\n# !python -m deeppavlov interact tfidf_logreg_en_faq -d\n# !python -m deeppavlov train tfidf_logreg_en_faq","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# import csv\n\n# with open(\"answers.csv\", \"w\") as f:\n#     writer = csv.writer(f)\n#     writer.writerows(answers)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# %%bash\n# wget -q http://files.deeppavlov.ai/faq/school/faq_school_en.csv -O faq.csv\n# echo \"What's DeepPavlov?, DeepPavlov is an open-source conversational AI library\" >> faq.csv","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# # https://colab.research.google.com/github/deepmipt/dp_notebooks/blob/master/DP_autoFAQ.ipynb#scrollTo=wMZqyzBYc2eV&uniqifier=1\n# from deeppavlov import configs\n# from deeppavlov.core.common.file import read_json\n# from deeppavlov.core.commands.infer import build_model\n# import sklearn.model_selection\n\n# from deeppavlov import configs, train_model\n\n\n# faq = build_model(configs.faq.tfidf_logreg_en_faq, download = True)\n# a = faq([\"I need help\"])\n# a\n\n\n# model_config = read_json(configs.faq.tfidf_logreg_en_faq)\n# model_config[\"dataset_reader\"][\"data_path\"] = \"/kaggle/working/answers.csv\"\n# model_config[\"dataset_reader\"][\"data_url\"] = None\n# faq = train_model(model_config)\n# a = faq([\"tell me about water\"])\n# a","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Deeppavlov installation problem has a solution that can be found here:http://docs.deeppavlov.ai/en/master/features/models/classifiers.html\nHowever, it does not seem that the intention recognition will improve the accuracy of detecting a KPI, or add any refinement to the methodology. That is because while attemtping to extract KPIs, the sentiment of the author has no impact on facts mentioned in his/her answer. \n\nBetter processing can be done using text summarization for long answers and more accurate questions to extract the exact information needed."},{"metadata":{"trusted":true},"cell_type":"code","source":"answers","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"a","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## given a city, detect KPIs"},{"metadata":{"trusted":true},"cell_type":"code","source":"# testing with chicago\n\nchicago_account_numbers = list(set(cities_disc[cities_disc['City'] == \"Chicago\"][\"Account Number\"]))\nchicago_resp_data = cities_resp[cities_resp['Account Number'] == chicago_account_numbers[0]]\nprint(chicago_resp_data.shape)\nchicago_resp_data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sections = chicago_resp_data['Section'].unique()\nsections","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"maxi = (\"\", 0)\nfor section in sections:\n    answers_count = chicago_resp_data[chicago_resp_data['Section'] == section].shape[0]\n    print(section, answers_count)\n    if answers_count > maxi[1]:\n        maxi = (section, answers_count)\nprint(\"\\n\", maxi)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.DataFrame(columns={'section_name', 'answers_number'}, index=range(len(list(sections))))\ndf['section_name'] = list(sections)\nfor index, row in df.iterrows():\n    row['answers_number'] = chicago_resp_data[chicago_resp_data['Section'] == row['section_name']].shape[0]\ndf = df.sort_values(by=['answers_number'])\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# import matplotlib.pyplot as plt\n\nfig = plt.figure(figsize=[10,5])\nax = fig.add_axes([0,0,1,1])\nsections = list(df['section_name'].values)\nsection_count = list(df['answers_number'].values)\nax.bar(sections, section_count)\nplt.xticks(rotation=90)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The three issues, most talked about in the city of Chicago are related to Climate Change KPIs.\nLet's see what are KPIs related to Climate change and explore what are the ones that are activated in Chicago?\n\nClimate Change KPIs:\n* Hazards and Vulnerability\n    * Natural disaster risk management\n    * Hazardous waste generation\n* Emissions\n    * Emissions policy\n    * Emissions measurments\n    * Emissions planning\n* Energy\n    * Access to energy\n    * renewable energy\n    \nFor the proof of concept sake, we will not go through all the questions, but rather we will only select the three first sections: `City-wide GHG Emissions Data`, `Climate Hazards`, and `Transport`"},{"metadata":{"trusted":true},"cell_type":"code","source":"sec = ['City-wide GHG Emissions Data', 'Climate Hazards','Transport']\nclimate_answers_chicago_df = pd.DataFrame()\nfor s in sec:\n    climate_answers_chicago_df = pd.concat([climate_answers_chicago_df, chicago_resp_data[chicago_resp_data['Section'] == s ]])\nprint(climate_answers_chicago_df.shape)\ncc_chicago_answers = list(climate_answers_chicago_df['Response Answer'].unique())\ncc_chicago_answers","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc_answers = ' '\ncc_answers = cc_answers.join(cc_chicago_answers)\ncc_answers","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Using the question-answerer from BERT"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Using question-answering of BERT\nquestion_answerer({'question': \"what is the name of the city?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"what are climate change issues?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"how is climate change being fought?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"what are the plans to fight climate change?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"what are the plans to fight disaster risks?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"what is being done to reduce emissions?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"business and emission?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"question_answerer({'question': \"business and energy?\", 'context': cc_answers})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc_answers[9098:10000]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Using the sematic similarity "},{"metadata":{"trusted":true},"cell_type":"code","source":"for keyword in ['Hazards and Vulnerability'] + dictionary.synonym('Hazards and Vulnerability'):\n    print(keyword)\n#     distance = semantic_distance(answer, keyword)\n#     print(keyword, distance, sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Ideas:\n    * we can categorize according to our own searched KPIs and see where the biggest focus is?\n    * we can leave it as it is and learn that information by applying NLP to answers\n    * we can move forward to extract more information and then understand what are the KPIs that are activated or need to be activated from the answers\n    * do same proccessing with parent sections can give more perspective about the focus area of the region/city"},{"metadata":{"trusted":true},"cell_type":"code","source":"parent_sections = chicago_resp_data['Parent Section'].unique()\nmaxi = (\"\", 0)\nfor section in parent_sections:\n    answers_count = chicago_resp_data[chicago_resp_data['Parent Section'] == section].shape[0]\n    print(section, answers_count)\n    if answers_count > maxi[1]:\n        maxi = (section, answers_count)\nprint(\"\\n\", maxi)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Parent section has missing values that apparently are not making this method easier to move forward with. Let's see though the data with no parent section."},{"metadata":{"trusted":true},"cell_type":"code","source":"chicago_resp_data[chicago_resp_data['Parent Section'] == \"\"]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can use sections to figure out parent sections ?"},{"metadata":{"trusted":true},"cell_type":"code","source":"ques_num = chicago_resp_data[chicago_resp_data['Parent Section'] == \"\"]['Question Number'].unique()\nques_num","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def fill_empty_section(df):\n    ques_num = df[df['Parent Section'] == '']\n    df = df[df['Question Number'] == 'Response Language']\n    df = df[df['Question Number'] == 'Amendments_question']\n    for index, row in df.iterrows():\n        print(index)\n        module_code = row['Question Number'].split('.')[0]\n        print(module_code)\n        df.iloc[index]['Parent Section'] = dic[module_code]\n\nfill_empty_section(chicago_resp_data)\nchicago_resp_data['Parent Section'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ques_num = df[df['Parent Section'] == '']\nques_num.set(0, 'Parent Section', Intro) #df.set_value('C', 'x', 10)\nques_num.iloc[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dic = {}\n\nmodules = questions.Module.unique()\nfor module in modules:\n    if module:\n        code, module_name = module.split(\".\")\n        dic[code[1:]] = module_name\ndic","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"look at column `Question Number` to extract parent section name, also could use sup data, recommended questions file\n\nTODO\n- display a figure that shows areas of focus for chicago \n- gather all answers related to chicago in one text variable\n- use answers to extract active KPIs\n- Understand strategies implemented or under planning\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"chicago_modules = chicago_resp_data","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}